Let us now discuss how to use the create table function.

The SQL Expression Language constructs its expressions against table columns. SQLAlchemy Column object represents a column in a database table which is in turn represented by a Tableobject. Metadata contains definitions of tables and associated objects such as index, view, triggers, etc.

Hence an object of MetaData class from SQLAlchemy Metadata is a collection of Table objects and their associated schema constructs. It holds a collection of Table objects as well as an optional binding to an Engine or Connection.

from sqlalchemy import MetaData
meta = MetaData()

Constructor of MetaData class can have bind and schema parameters which are by default None.

Next, we define our tables all within above metadata catalog, using the Table construct, which resembles regular SQL CREATE TABLE statement.

An object of Table class represents corresponding table in a database. The constructor takes the following parameters −

Column object represents a column in a database table. Constructor takes name, type and other parameters such as primary_key, autoincrement and other constraints.

SQLAlchemy matches Python data to the best possible generic column data types defined in it. Some of the generic data types are −

• BigInteger
• Boolean
• Date
• DateTime
• Float
• Integer
• Numeric
• SmallInteger
• String
• Text
• Time

To create a students table in college database, use the following snippet −

from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()

students = Table(
   ''students'', meta,
   Column(''id'', Integer, primary_key = True),
   Column(''name'', String),
   Column(''lastname'', String),
)

The create_all() function uses the engine object to create all the defined table objects and stores the information in metadata.

meta.create_all(engine)

Complete code is given below which will create a SQLite database college.db with a students table in it.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine(''sqlite:///college.db'', echo = True)
meta = MetaData()

students = Table(
   ''students'', meta,
   Column(''id'', Integer, primary_key = True),
   Column(''name'', String),
   Column(''lastname'', String),
)
meta.create_all(engine)

Because echo attribute of create_engine() function is set to True, the console will display the actual SQL query for table creation as follows −

CREATE TABLE students (
   id INTEGER NOT NULL,
   name VARCHAR,
   lastname VARCHAR,
   PRIMARY KEY (id)
)

The college.db will be created in current working directory. To check if the students table is created, you can open the database using any SQLite GUI tool such as SQLiteStudio.

The below image shows the students table that is created in the database −

SQLAlchemy lets you just use strings, for those cases when the SQL is already known and there isn’t a strong need for the statement to support dynamic features. The text() construct is used to compose a textual statement that is passed to the database mostly unchanged.

It constructs a new TextClause, representing a textual SQL string directly as shown in the below code −

from sqlalchemy import text
t = text("SELECT * FROM students")
result = connection.execute(t)

The advantages text() provides over a plain string are −

• backend-neutral support for bind parameters
• per-statement execution options
• result-column typing behaviour

The text()function requires Bound parameters in the named colon format. They are consistent regardless of database backend. To send values in for the parameters, we pass them into the execute() method as additional arguments.

The following example uses bound parameters in textual SQL −

from sqlalchemy.sql import text
s = text("select students.name, students.lastname from students where students.name between :x and :y")
conn.execute(s, x = ''A'', y = ''L'').fetchall()

The text() function constructs SQL expression as follows −

select students.name, students.lastname from students where students.name between ? and ?

The values of x = ’A’ and y = ’L’ are passed as parameters. Result is a list of rows with names between ‘A’ and ‘L’ −

[(''Komal'', ''Bhandari''), (''Abdul'', ''Sattar'')]

The text() construct supports pre-established bound values using the TextClause.bindparams() method. The parameters can also be explicitly typed as follows −

stmt = text("SELECT * FROM students WHERE students.name BETWEEN :x AND :y")

stmt = stmt.bindparams(
   bindparam("x", type_= String),
   bindparam("y", type_= String)
)

result = conn.execute(stmt, {"x": "A", "y": "L"})

The text() function also be produces fragments of SQL within a select() object that
accepts text() objects as an arguments. The “geometry” of the statement is provided by
select() construct , and the textual content by text() construct. We can build a statement
without the need to refer to any pre-established Table metadata.

from sqlalchemy.sql import select
s = select([text("students.name, students.lastname from students")]).where(text("students.name between :x and :y"))
conn.execute(s, x = ''A'', y = ''L'').fetchall()

You can also use and_() function to combine multiple conditions in WHERE clause created with the help of text() function.

from sqlalchemy import and_
from sqlalchemy.sql import select
s = select([text("* from students")])
.where(
   and_(
      text("students.name between :x and :y"),
      text("students.id>2")
   )
)
conn.execute(s, x = ''A'', y = ''L'').fetchall()

Above code fetches rows with names between “A” and “L” with id greater than 2. The output of the code is given below −

[(3, ''Komal'', ''Bhandari''), (4, ''Abdul'', ''Sattar'')]

In this chapter, we will discuss about the concept of selecting rows in the table object.

The select() method of table object enables us to construct SELECT expression.

s = students.select()

The select object translates to SELECT query by str(s) function as shown below −

''SELECT students.id, students.name, students.lastname FROM students''

We can use this select object as a parameter to execute() method of connection object as shown in the code below −

result = conn.execute(s)

When the above statement is executed, Python shell echoes following equivalent SQL expression −

SELECT students.id, students.name, students.lastname
FROM students

The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method.

row = result.fetchone()

All selected rows in the table can be printed by a for loop as given below −

for row in result:
   print (row)

The complete code to print all rows from students table is shown below −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine(''sqlite:///college.db'', echo = True)
meta = MetaData()

students = Table(
   ''students'', meta,
   Column(''id'', Integer, primary_key = True),
   Column(''name'', String),
   Column(''lastname'', String),
)

s = students.select()
conn = engine.connect()
result = conn.execute(s)

for row in result:
   print (row)

The output shown in Python shell is as follows −

(1, ''Ravi'', ''Kapoor'')
(2, ''Rajiv'', ''Khanna'')
(3, ''Komal'', ''Bhandari'')
(4, ''Abdul'', ''Sattar'')
(5, ''Priya'', ''Rajhans'')

The WHERE clause of SELECT query can be applied by using Select.where(). For example, if we want to display rows with id >2

s = students.select().where(students.c.id>2)
result = conn.execute(s)

for row in result:
   print (row)

Here c attribute is an alias for column. Following output will be displayed on the shell −

(3, ''Komal'', ''Bhandari'')
(4, ''Abdul'', ''Sattar'')
(5, ''Priya'', ''Rajhans'')

Here, we have to note that select object can also be obtained by select() function in sqlalchemy.sql module. The select() function requires the table object as argument.

from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)

In the previous chapter, we have discussed about expression Language in SQLAlchemy. Now let us proceed towards the steps involved in connecting to a database.

Engine class connects a Pool and Dialect together to provide a source of database connectivity and behavior. An object of Engine class is instantiated using the create_engine() function.

The create_engine() function takes the database as one argument. The database is not needed to be defined anywhere. The standard calling form has to send the URL as the first positional argument, usually a string that indicates database dialect and connection arguments. Using the code given below, we can create a database.

>>> from sqlalchemy import create_engine
>>> engine = create_engine(''sqlite:///college.db'', echo = True)

For a MySQL database, use the below command −

engine = create_engine("mysql://user:pwd@localhost/college",echo = True)

To specifically mention DB-API to be used for connection, the URL string takes the form as follows −

dialect[+driver]://user:password@host/dbname

For example, if you are using PyMySQL driver with MySQL, use the following command −

mysql+pymysql://<username>:<password>@<host>/<dbname>

The echo flag is a shortcut to set up SQLAlchemy logging, which is accomplished via Python’s standard logging module. In the subsequent chapters, we will learn all the generated SQLs. To hide the verbose output, set echo attribute to None. Other arguments to create_engine() function may be dialect specific.

The create_engine() function returns an Engine object. Some important methods of Engine class are −

In this chapter, we will briefly focus on the SQL Expressions and their functions.

SQL expressions are constructed using corresponding methods relative to target table object. For example, the INSERT statement is created by executing insert() method as follows −

ins = students.insert()

The result of above method is an insert object that can be verified by using str() function. The below code inserts details like student id, name, lastname.

''INSERT INTO students (id, name, lastname) VALUES (:id, :name, :lastname)''

It is possible to insert value in a specific field by values() method to insert object. The code for the same is given below −

>>> ins = users.insert().values(name = ''Karan'')
>>> str(ins)
''INSERT INTO users (name) VALUES (:name)''

The SQL echoed on Python console doesn’t show the actual value (‘Karan’ in this case). Instead, SQLALchemy generates a bind parameter which is visible in compiled form of the statement.

ins.compile().params
{''name'': ''Karan''}

Similarly, methods like update(), delete() and select() create UPDATE, DELETE and SELECT expressions respectively. We shall learn about them in later chapters.

In the previous chapter, we have learnt SQL Expressions. In this chapter, we shall look into the execution of these expressions.

In order to execute the resulting SQL expressions, we have to obtain a connection object representing an actively checked out DBAPI connection resource and then feed the expression object as shown in the code below.

conn = engine.connect()

The following insert() object can be used for execute() method −

ins = students.insert().values(name = ''Ravi'', lastname = ''Kapoor'')
result = conn.execute(ins)

The console shows the result of execution of SQL expression as below −

INSERT INTO students (name, lastname) VALUES (?, ?)
(''Ravi'', ''Kapoor'')
COMMIT

Following is the entire snippet that shows the execution of INSERT query using SQLAlchemy’s core technique −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine(''sqlite:///college.db'', echo = True)
meta = MetaData()

students = Table(
   ''students'', meta,
   Column(''id'', Integer, primary_key = True),
   Column(''name'', String),
   Column(''lastname'', String),
)

ins = students.insert()
ins = students.insert().values(name = ''Ravi'', lastname = ''Kapoor'')
conn = engine.connect()
result = conn.execute(ins)

The result can be verified by opening the database using SQLite Studio as shown in the below screenshot −

The result variable is known as a object. It is analogous to the DBAPI cursor object. We can acquire information about the primary key values which were generated from our statement using ResultProxy.inserted_primary_key as shown below −

result.inserted_primary_key
[1]

To issue many inserts using DBAPI’s execute many() method, we can send in a list of dictionaries each containing a distinct set of parameters to be inserted.

conn.execute(students.insert(), [
   {''name'':''Rajiv'', ''lastname'' : ''Khanna''},
   {''name'':''Komal'',''lastname'' : ''Bhandari''},
   {''name'':''Abdul'',''lastname'' : ''Sattar''},
   {''name'':''Priya'',''lastname'' : ''Rajhans''},
])

This is reflected in the data view of the table as shown in the following figure −

The alias in SQL corresponds to a “renamed” version of a table or SELECT statement, which occurs anytime you say “SELECT * FROM table1 AS a”. The AS creates a new name for the table. Aliases allow any table or subquery to be referenced by a unique name.

In case of a table, this allows the same table to be named in the FROM clause multiple times. It provides a parent name for the columns represented by the statement, allowing them to be referenced relative to this name.

In SQLAlchemy, any Table, select() construct, or other selectable object can be turned into an alias using the From Clause.alias() method, which produces an Alias construct. The alias() function in sqlalchemy.sql module represents an alias, as typically applied to any table or sub-select within a SQL statement using the AS keyword.

from sqlalchemy.sql import alias
st = students.alias("a")

This alias can now be used in select() construct to refer to students table −

s = select([st]).where(st.c.id>2)

This translates to SQL expression as follows −

SELECT a.id, a.name, a.lastname FROM students AS a WHERE a.id > 2

We can now execute this SQL query with the execute() method of connection object. The complete code is as follows −

from sqlalchemy.sql import alias, select
st = students.alias("a")
s = select([st]).where(st.c.id > 2)
conn.execute(s).fetchall()

When above line of code is executed, it generates the following output −

[(3, ''Komal'', ''Bhandari''), (4, ''Abdul'', ''Sattar''), (5, ''Priya'', ''Rajhans'')]