One of the important features of RDBMS is establishing relation between tables. SQL operations like SELECT, UPDATE and DELETE can be performed on related tables. This section describes these operations using SQLAlchemy.

For this purpose, two tables are created in our SQLite database (college.db). The students table has the same structure as given in the previous section; whereas the addresses table has st_id column which is mapped to id column in students table using foreign key constraint.

The following code will create two tables in college.db −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
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),
)

addresses = Table(
   ''addresses'', meta,
   Column(''id'', Integer, primary_key = True),
   Column(''st_id'', Integer, ForeignKey(''students.id'')),
   Column(''postal_add'', String),
   Column(''email_add'', String))

meta.create_all(engine)

Above code will translate to CREATE TABLE queries for students and addresses table as below −

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

CREATE TABLE addresses (
   id INTEGER NOT NULL,
   st_id INTEGER,
   postal_add VARCHAR,
   email_add VARCHAR,
   PRIMARY KEY (id),
   FOREIGN KEY(st_id) REFERENCES students (id)
)

The following screenshots present the above code very clearly −

These tables are populated with data by executing insert() method of table objects. To insert 5 rows in students table, you can use the code given below −

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

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

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

Rows are added in addresses table with the help of the following code −

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

addresses = Table(
   ''addresses'', meta,
   Column(''id'', Integer, primary_key = True),
   Column(''st_id'', Integer),
   Column(''postal_add'', String),
   Column(''email_add'', String)
)

conn.execute(addresses.insert(), [
   {''st_id'':1, ''postal_add'':''Shivajinagar Pune'', ''email_add'':''ravi@gmail.com''},
   {''st_id'':1, ''postal_add'':''ChurchGate Mumbai'', ''email_add'':''kapoor@gmail.com''},
   {''st_id'':3, ''postal_add'':''Jubilee Hills Hyderabad'', ''email_add'':''komal@gmail.com''},
   {''st_id'':5, ''postal_add'':''MG Road Bangaluru'', ''email_add'':''as@yahoo.com''},
   {''st_id'':2, ''postal_add'':''Cannought Place new Delhi'', ''email_add'':''admin@khanna.com''},
])

Note that the st_id column in addresses table refers to id column in students table. We can now use this relation to fetch data from both the tables. We want to fetch name and lastname from students table corresponding to st_id in the addresses table.

from sqlalchemy.sql import select
s = select([students, addresses]).where(students.c.id == addresses.c.st_id)
result = conn.execute(s)

for row in result:
   print (row)

The select objects will effectively translate into following SQL expression joining two tables on common relation −

SELECT students.id,
   students.name,
   students.lastname,
   addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM students, addresses
WHERE students.id = addresses.st_id

This will produce output extracting corresponding data from both tables as follows −

(1, ''Ravi'', ''Kapoor'', 1, 1, ''Shivajinagar Pune'', ''ravi@gmail.com'')
(1, ''Ravi'', ''Kapoor'', 2, 1, ''ChurchGate Mumbai'', ''kapoor@gmail.com'')
(3, ''Komal'', ''Bhandari'', 3, 3, ''Jubilee Hills Hyderabad'', ''komal@gmail.com'')
(5, ''Priya'', ''Rajhans'', 4, 5, ''MG Road Bangaluru'', ''as@yahoo.com'')
(2, ''Rajiv'', ''Khanna'', 5, 2, ''Cannought Place new Delhi'', ''admin@khanna.com'')

In the previous chapter, we have discussed about how to use multiple tables. So we proceed a step further and learn multiple table updates in this chapter.

Using SQLAlchemy’s table object, more than one table can be specified in WHERE clause of update() method. The PostgreSQL and Microsoft SQL Server support UPDATE statements that refer to multiple tables. This implements “UPDATE FROM” syntax, which updates one table at a time. However, additional tables can be referenced in an additional “FROM” clause in the WHERE clause directly. The following lines of codes explain the concept of multiple table updates clearly.

stmt = students.update().
values({
   students.c.name:''xyz'',
   addresses.c.email_add:''abc@xyz.com''
}).
where(students.c.id == addresses.c.id)

The update object is equivalent to the following UPDATE query −

UPDATE students
SET email_add = :addresses_email_add, name = :name
FROM addresses
WHERE students.id = addresses.id

As far as MySQL dialect is concerned, multiple tables can be embedded into a single UPDATE statement separated by a comma as given below −

stmt = students.update().
   values(name = ''xyz'').
   where(students.c.id == addresses.c.id)

The following code depicts the resulting UPDATE query −

''UPDATE students SET name = :name
FROM addresses
WHERE students.id = addresses.id''

SQLite dialect however doesn’t support multiple-table criteria within UPDATE and shows following error −

NotImplementedError: This backend does not support multiple-table criteria within UPDATE

In the previous chapter, we have understood what an Update expression does. The next expression that we are going to learn is Delete.

The delete operation can be achieved by running delete() method on target table object as given in the following statement −

stmt = students.delete()

In case of students table, the above line of code constructs a SQL expression as following −

''DELETE FROM students''

However, this will delete all rows in students table. Usually DELETE query is associated with a logical expression specified by WHERE clause. The following statement shows where parameter −

stmt = students.delete().where(students.c.id > 2)

The resultant SQL expression will have a bound parameter which will be substituted at runtime when the statement is executed.

''DELETE FROM students WHERE students.id > :id_1''

Following code example will delete those rows from students table having lastname as ‘Khanna’ −

from sqlalchemy.sql.expression import update
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),
)

conn = engine.connect()
stmt = students.delete().where(students.c.lastname == ''Khanna'')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

To verify the result, refresh the data view of students table in SQLiteStudio.

The update() method on target table object constructs equivalent UPDATE SQL expression.

table.update().where(conditions).values(SET expressions)

The values() method on the resultant update object is used to specify the SET conditions of the UPDATE. If left as None, the SET conditions are determined from those parameters passed to the statement during the execution and/or compilation of the statement.

The where clause is an Optional expression describing the WHERE condition of the UPDATE statement.

Following code snippet changes value of ‘lastname’ column from ‘Khanna’ to ‘Kapoor’ in students table −

stmt = students.update().where(students.c.lastname == ''Khanna'').values(lastname = ''Kapoor'')

The stmt object is an update object that translates to −

''UPDATE students SET lastname = :lastname WHERE students.lastname = :lastname_1''

The bound parameter lastname_1 will be substituted when execute() method is invoked. The complete update code is given 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),
)

conn = engine.connect()
stmt=students.update().where(students.c.lastname==''Khanna'').values(lastname=''Kapoor'')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

The above code displays following output with second row showing effect of update operation as in the screenshot given −

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

Note that similar functionality can also be achieved by using update() function in sqlalchemy.sql.expression module as shown below −

from sqlalchemy.sql.expression import update
stmt = update(students).where(students.c.lastname == ''Khanna'').values(lastname = ''Kapoor'')

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'')]

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 −

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 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 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)

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'')]