The UPDATE query of raw SQL has SET clause. It is rendered by the update() construct using the column ordering given in the originating Table object. Therefore, a particular UPDATE statement with particular columns will be rendered the same each time. Since the parameters themselves are passed to the Update.values() method as Python dictionary keys, there is no other fixed ordering available.

In some cases, the order of parameters rendered in the SET clause are significant. In MySQL, providing updates to column values is based on that of other column values.

Following statement’s result −

UPDATE table1 SET x = y + 10, y = 20

will have a different result than −

UPDATE table1 SET y = 20, x = y + 10

SET clause in MySQL is evaluated on a per-value basis and not on per-row basis. For this purpose, the preserve_parameter_order is used. Python list of 2-tuples is given as argument to the Update.values() method −

stmt = table1.update(preserve_parameter_order = True).
   values([(table1.c.y, 20), (table1.c.x, table1.c.y + 10)])

The List object is similar to dictionary except that it is ordered. This ensures that the “y” column’s SET clause will render first, then the “x” column’s SET clause.

In this chapter, we will learn how to use Joins in SQLAlchemy.

Effect of joining is achieved by just placing two tables in either the columns clause or the where clause of the select() construct. Now we use the join() and outerjoin() methods.

The join() method returns a join object from one table object to another.

join(right, onclause = None, isouter = False, full = False)

The functions of the parameters mentioned in the above code are as follows −

• right − the right side of the join; this is any Table object

• onclause − a SQL expression representing the ON clause of the join. If left at None, it attempts to join the two tables based on a foreign key relationship

• isouter − if True, renders a LEFT OUTER JOIN, instead of JOIN

• full − if True, renders a FULL OUTER JOIN, instead of LEFT OUTER JOIN

For example, following use of join() method will automatically result in join based on the foreign key.

>>> print(students.join(addresses))

This is equivalent to following SQL expression −

students JOIN addresses ON students.id = addresses.st_id

You can explicitly mention joining criteria as follows −

j = students.join(addresses, students.c.id == addresses.c.st_id)

If we now build the below select construct using this join as −

stmt = select([students]).select_from(j)

This will result in following SQL expression −

SELECT students.id, students.name, students.lastname
FROM students JOIN addresses ON students.id = addresses.st_id

If this statement is executed using the connection representing engine, data belonging to selected columns will be displayed. The complete code is as follows −

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

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

from sqlalchemy import join
from sqlalchemy.sql import select
j = students.join(addresses, students.c.id == addresses.c.st_id)
stmt = select([students]).select_from(j)
result = conn.execute(stmt)
result.fetchall()

The following is the output of the above code −

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

In this chapter, we will look into the Multiple Table Deletes expression which is similar to using Multiple Table Updates function.

More than one table can be referred in WHERE clause of DELETE statement in many DBMS dialects. For PG and MySQL, “DELETE USING” syntax is used; and for SQL Server, using “DELETE FROM” expression refers to more than one table. The SQLAlchemy delete() construct supports both of these modes implicitly, by specifying multiple tables in the WHERE clause as follows −

stmt = users.delete().
   where(users.c.id == addresses.c.id).
   where(addresses.c.email_address.startswith(''xyz%''))
conn.execute(stmt)

On a PostgreSQL backend, the resulting SQL from the above statement would render as −

DELETE FROM users USING addresses
WHERE users.id = addresses.id
AND (addresses.email_address LIKE %(email_address_1)s || ''%%'')

If this method is used with a database that doesn’t support this behaviour, the compiler will raise NotImplementedError.

Some of the important functions used in SQLAlchemy are discussed in this chapter.

Standard SQL has recommended many functions which are implemented by most dialects. They return a single value based on the arguments passed to it. Some SQL functions take columns as arguments whereas some are generic. Thefunc keyword in SQLAlchemy API is used to generate these functions.

In SQL, now() is a generic function. Following statements renders the now() function using func −

from sqlalchemy.sql import func
result = conn.execute(select([func.now()]))
print (result.fetchone())

Sample result of above code may be as shown below −

(datetime.datetime(2018, 6, 16, 6, 4, 40),)

On the other hand, count() function which returns number of rows selected from a table, is rendered by following usage of func −

from sqlalchemy.sql import func
result = conn.execute(select([func.count(students.c.id)]))
print (result.fetchone())

From the above code, count of number of rows in students table will be fetched.

Some built-in SQL functions are demonstrated using Employee table with following data −

The max() function is implemented by following usage of func from SQLAlchemy which will result in 85, the total maximum marks obtained −

from sqlalchemy.sql import func
result = conn.execute(select([func.max(employee.c.marks)]))
print (result.fetchone())

Similarly, min() function that will return 56, minimum marks, will be rendered by following code −

from sqlalchemy.sql import func
result = conn.execute(select([func.min(employee.c.marks)]))
print (result.fetchone())

So, the AVG() function can also be implemented by using the below code −

from sqlalchemy.sql import func
result = conn.execute(select([func.avg(employee.c.marks)]))
print (result.fetchone())

Functions are normally used in the columns clause of a select statement.
They can also be given label as well as a type. A label to function allows the result
to be targeted in a result row based on a string name, and a type is required when
you need result-set processing to occur.from sqlalchemy.sql import func

result = conn.execute(select([func.max(students.c.lastname).label(''Name'')]))

print (result.fetchone())

Conjunctions are functions in SQLAlchemy module that implement relational operators used in WHERE clause of SQL expressions. The operators AND, OR, NOT, etc., are used to form a compound expression combining two individual logical expressions. A simple example of using AND in SELECT statement is as follows −

SELECT * from EMPLOYEE WHERE salary>10000 AND age>30

SQLAlchemy functions and_(), or_() and not_() respectively implement AND, OR and NOT operators.

and_() function

It produces a conjunction of expressions joined by AND. An example is given below for better understanding −

from sqlalchemy import and_

print(
   and_(
      students.c.name == ''Ravi'',
      students.c.id <3
   )
)

This translates to −

students.name = :name_1 AND students.id < :id_1

To use and_() in a select() construct on a students table, use the following line of code −

stmt = select([students]).where(and_(students.c.name == ''Ravi'', students.c.id <3))

SELECT statement of the following nature will be constructed −

SELECT students.id,
   students.name,
   students.lastname
FROM students
WHERE students.name = :name_1 AND students.id < :id_1

The complete code that displays output of the above SELECT query is as follows −

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

from sqlalchemy import and_, or_
stmt = select([students]).where(and_(students.c.name == ''Ravi'', students.c.id <3))
result = conn.execute(stmt)
print (result.fetchall())

Following row will be selected assuming that students table is populated with data used in previous example −

[(1, ''Ravi'', ''Kapoor'')]

or_() function

It produces conjunction of expressions joined by OR. We shall replace the stmt object in the above example with the following one using or_()

stmt = select([students]).where(or_(students.c.name == ''Ravi'', students.c.id <3))

Which will be effectively equivalent to following SELECT query −

SELECT students.id,
   students.name,
   students.lastname
FROM students
WHERE students.name = :name_1
OR students.id < :id_1

Once you make the substitution and run the above code, the result will be two rows falling in the OR condition −

[(1, ''Ravi'', ''Kapoor''),
(2, ''Rajiv'', ''Khanna'')]

asc() function

It produces an ascending ORDER BY clause. The function takes the column to apply the function as a parameter.

from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))

The statement implements following SQL expression −

SELECT students.id,
   students.name,
   students.lastname
FROM students
ORDER BY students.name ASC

Following code lists out all records in students table in ascending order of name column −

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

from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))
result = conn.execute(stmt)

for row in result:
   print (row)

Above code produces following output −

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

desc() function

Similarly desc() function produces descending ORDER BY clause as follows −

from sqlalchemy import desc
stmt = select([students]).order_by(desc(students.c.lastname))

The equivalent SQL expression is −

SELECT students.id,
   students.name,
   students.lastname
FROM students
ORDER BY students.lastname DESC

And the output for the above lines of code is −

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

between() function

It produces a BETWEEN predicate clause. This is generally used to validate if value of a certain column falls between a range. For example, following code selects rows for which id column is between 2 and 4 −

from sqlalchemy import between
stmt = select([students]).where(between(students.c.id,2,4))
print (stmt)

The resulting SQL expression resembles −

SELECT students.id,
   students.name,
   students.lastname
FROM students
WHERE students.id
BETWEEN :id_1 AND :id_2

and the result is as follows −

(2, ''Rajiv'', ''Khanna'')
(3, ''Komal'', ''Bhandari'')
(4, ''Abdul'', ''Sattar'')

In the last chapter, we have learnt about various functions such as max(), min(), count(), etc., here, we will learn about set operations and their uses.

Set operations such as UNION and INTERSECT are supported by standard SQL and most of its dialect. SQLAlchemy implements them with the help of following functions −

union()

While combining results of two or more SELECT statements, UNION eliminates duplicates from the resultset. The number of columns and datatype must be same in both the tables.

The union() function returns a CompoundSelect object from multiple tables. Following example demonstrates its use −

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

u = union(addresses.select().where(addresses.c.email_add.like(''%@gmail.com addresses.select().where(addresses.c.email_add.like(''%@yahoo.com''))))

result = conn.execute(u)
result.fetchall()

The union construct translates to following SQL expression −

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

From our addresses table, following rows represent the union operation −

[
   (1, 1, ''Shivajinagar Pune'', ''ravi@gmail.com''),
   (2, 1, ''ChurchGate Mumbai'', ''kapoor@gmail.com''),
   (3, 3, ''Jubilee Hills Hyderabad'', ''komal@gmail.com''),
   (4, 5, ''MG Road Bangaluru'', ''as@yahoo.com'')
]

union_all()

UNION ALL operation cannot remove the duplicates and cannot sort the data in the resultset. For example, in above query, UNION is replaced by UNION ALL to see the effect.

u = union_all(addresses.select().where(addresses.c.email_add.like(''%@gmail.com'')), addresses.select().where(addresses.c.email_add.like(''%@yahoo.com'')))

The corresponding SQL expression is as follows −

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION ALL SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

except_()

The SQL EXCEPT clause/operator is used to combine two SELECT statements and return rows from the first SELECT statement that are not returned by the second SELECT statement. The except_() function generates a SELECT expression with EXCEPT clause.

In the following example, the except_() function returns only those records from addresses table that have ‘gmail.com’ in email_add field but excludes those which have ‘Pune’ as part of postal_add field.

u = except_(addresses.select().where(addresses.c.email_add.like(''%@gmail.com'')), addresses.select().where(addresses.c.postal_add.like(''%Pune'')))

Result of the above code is the following SQL expression −

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? EXCEPT SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

Assuming that addresses table contains data used in earlier examples, it will display following output −

[(2, 1, ''ChurchGate Mumbai'', ''kapoor@gmail.com''),
   (3, 3, ''Jubilee Hills Hyderabad'', ''komal@gmail.com'')]

intersect()

Using INTERSECT operator, SQL displays common rows from both the SELECT statements. The intersect() function implements this behaviour.

In following examples, two SELECT constructs are parameters to intersect() function. One returns rows containing ‘gmail.com’ as part of email_add column, and other returns rows having ‘Pune’ as part of postal_add column. The result will be common rows from both resultsets.

u = intersect(addresses.select().where(addresses.c.email_add.like(''%@gmail.com'')), addresses.select().where(addresses.c.postal_add.like(''%Pune'')))

In effect, this is equivalent to following SQL statement −

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? INTERSECT SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

The two bound parameters ‘%gmail.com’ and ‘%Pune’ generate a single row from original data in addresses table as shown below −

[(1, 1, ''Shivajinagar Pune'', ''ravi@gmail.com'')]

The main objective of the Object Relational Mapper API of SQLAlchemy is to facilitate associating user-defined Python classes with database tables, and objects of those classes with rows in their corresponding tables. Changes in states of objects and rows are synchronously matched with each other. SQLAlchemy enables expressing database queries in terms of user defined classes and their defined relationships.

The ORM is constructed on top of the SQL Expression Language. It is a high level and abstracted pattern of usage. In fact, ORM is an applied usage of the Expression Language.

Although a successful application may be constructed using the Object Relational Mapper exclusively, sometimes an application constructed with the ORM may use the Expression Language directly where specific database interactions are required.

Declare Mapping

First of all, create_engine() function is called to set up an engine object which is subsequently used to perform SQL operations. The function has two arguments, one is the name of database and other is an echo parameter when set to True will generate the activity log. If it doesn’t exist, the database will be created. In the following example, a SQLite database is created.

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

The Engine establishes a real DBAPI connection to the database when a method like Engine.execute() or Engine.connect() is called. It is then used to emit the SQLORM which does not use the Engine directly; instead, it is used behind the scenes by the ORM.

In case of ORM, the configurational process starts by describing the database tables and then by defining classes which will be mapped to those tables. In SQLAlchemy, these two tasks are performed together. This is done by using Declarative system; the classes created include directives to describe the actual database table they are mapped to.

A base class stores a catlog of classes and mapped tables in the Declarative system. This is called as the declarative base class. There will be usually just one instance of this base in a commonly imported module. The declarative_base() function is used to create base class. This function is defined in sqlalchemy.ext.declarative module.

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

Once base classis declared, any number of mapped classes can be defined in terms of it. Following code defines a Customer’s class. It contains the table to be mapped to, and names and datatypes of columns in it.

class Customers(Base):
   __tablename__ = ''customers''

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

A class in Declarative must have a __tablename__ attribute, and at least one Column which is part of a primary key. Declarative replaces all the Column objects with special Python accessors known as descriptors. This process is known as instrumentation which provides the means to refer to the table in a SQL context and enables persisting and loading the values of columns from the database.

This mapped class like a normal Python class has attributes and methods as per the requirement.

The information about class in Declarative system, is called as table metadata. SQLAlchemy uses Table object to represent this information for a specific table created by Declarative. The Table object is created according to the specifications, and is associated with the class by constructing a Mapper object. This mapper object is not directly used but is used internally as interface between mapped class and table.

Each Table object is a member of larger collection known as MetaData and this object is available using the .metadata attribute of declarative base class. The MetaData.create_all() method is, passing in our Engine as a source of database connectivity. For all tables that haven’t been created yet, it issues CREATE TABLE statements to the database.

Base.metadata.create_all(engine)

The complete script to create a database and a table, and to map Python class is given below −

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine(''sqlite:///sales.db'', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = ''customers''
   id = Column(Integer, primary_key=True)

   name = Column(String)
   address = Column(String)
   email = Column(String)
Base.metadata.create_all(engine)

When executed, Python console will echo following SQL expression being executed −

CREATE TABLE customers (
   id INTEGER NOT NULL,
   name VARCHAR,
   address VARCHAR,
   email VARCHAR,
   PRIMARY KEY (id)
)

If we open the Sales.db using SQLiteStudio graphic tool, it shows customers table inside it with above mentioned structure.

In the previous chapters of SQLAlchemy ORM, we have learnt how to declare mapping and create sessions. In this chapter, we will learn how to add objects to the table.

We have declared Customer class that has been mapped to customers table. We have to declare an object of this class and persistently add it to the table by add() method of session object.

c1 = Sales(name = ''Ravi Kumar'', address = ''Station Road Nanded'', email = ''ravi@gmail.com'')
session.add(c1)

Note that this transaction is pending until the same is flushed using commit() method.

session.commit()

Following is the complete script to add a record in customers table −

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine(''sqlite:///sales.db'', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = ''customers''

   id = Column(Integer, primary_key=True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

c1 = Customers(name = ''Ravi Kumar'', address = ''Station Road Nanded'', email = ''ravi@gmail.com'')

session.add(c1)
session.commit()

To add multiple records, we can use add_all() method of the session class.

session.add_all([
   Customers(name = ''Komal Pande'', address = ''Koti, Hyderabad'', email = ''komal@gmail.com''),
   Customers(name = ''Rajender Nath'', address = ''Sector 40, Gurgaon'', email = ''nath@gmail.com''),
   Customers(name = ''S.M.Krishna'', address = ''Budhwar Peth, Pune'', email = ''smk@gmail.com'')]
)

session.commit()

Table view of SQLiteStudio shows that the records are persistently added in customers table. The following image shows the result −

In order to interact with the database, we need to obtain its handle. A session object is the handle to database. Session class is defined using sessionmaker() – a configurable session factory method which is bound to the engine object created earlier.

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)

The session object is then set up using its default constructor as follows −

session = Session()

Some of the frequently required methods of session class are listed below −

All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.

Query objects are initially generated using the query() method of the Session as follows −

q = session.query(mapped class)

Following statement is also equivalent to the above given statement −

q = Query(mappedClass, session)

The query object has all() method which returns a resultset in the form of list of objects. If we execute it on our customers table −

result = session.query(Customers).all()

This statement is effectively equivalent to following SQL expression −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers

The result object can be traversed using For loop as below to obtain all records in underlying customers table. Here is the complete code to display all records in Customers table −

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine(''sqlite:///sales.db'', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = ''customers''
   id = Column(Integer, primary_key =  True)
   name = Column(String)

   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).all()

for row in result:
   print ("Name: ",row.name, "Address:",row.address, "Email:",row.email)

Python console shows list of records as below −

Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
Name: Komal Pande Address: Koti, Hyderabad Email: komal@gmail.com
Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com

The Query object also has following useful methods −