Earlier, textual SQL using text() function has been explained from the perspective of core expression language of SQLAlchemy. Now we shall discuss it from ORM point of view.

Literal strings can be used flexibly with Query object by specifying their use with the text() construct. Most applicable methods accept it. For example, filter() and order_by().

In the example given below, the filter() method translates the string “id<3” to the WHERE id<3

from sqlalchemy import text
for cust in session.query(Customers).filter(text("id<3")):
   print(cust.name)

The raw SQL expression generated shows conversion of filter to WHERE clause with the code illustrated below −

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

From our sample data in Customers table, two rows will be selected and name column will be printed as follows −

Ravi Kumar
Komal Pande

To specify bind parameters with string-based SQL, use a colon,and to specify the values, use the params() method.

cust = session.query(Customers).filter(text("id = :value")).params(value = 1).one()

The effective SQL displayed on Python console will be as given below −

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

To use an entirely string-based statement, a text() construct representing a complete statement can be passed to from_statement().

session.query(Customers).from_statement(text("SELECT * FROM customers")).all()

The result of above code will be a basic SELECT statement as given below −

SELECT * FROM customers

Obviously, all records in customers table will be selected.

The text() construct allows us to link its textual SQL to Core or ORM-mapped column expressions positionally. We can achieve this by passing column expressions as positional arguments to the TextClause.columns() method.

stmt = text("SELECT name, id, name, address, email FROM customers")
stmt = stmt.columns(Customers.id, Customers.name)
session.query(Customers.id, Customers.name).from_statement(stmt).all()

The id and name columns of all rows will be selected even though the SQLite engine executes following expression generated by above code shows all columns in text() method −

SELECT name, id, name, address, email FROM customers

This session describes creation of another table which is related to already existing one in our database. The customers table contains master data of customers. We now need to create invoices table which may have any number of invoices belonging to a customer. This is a case of one to many relationships.

Using declarative, we define this table along with its mapped class, Invoices as given below −

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

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

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

class Invoice(Base):
   __tablename__ = ''invoices''

   id = Column(Integer, primary_key = True)
   custid = Column(Integer, ForeignKey(''customers.id''))
   invno = Column(Integer)
   amount = Column(Integer)
   customer = relationship("Customer", back_populates = "invoices")

Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")
Base.metadata.create_all(engine)

This will send a CREATE TABLE query to SQLite engine as below −

CREATE TABLE invoices (
   id INTEGER NOT NULL,
   custid INTEGER,
   invno INTEGER,
   amount INTEGER,
   PRIMARY KEY (id),
   FOREIGN KEY(custid) REFERENCES customers (id)
)

We can check that new table is created in sales.db with the help of SQLiteStudio tool.

Invoices class applies ForeignKey construct on custid attribute. This directive indicates that values in this column should be constrained to be values present in id column in customers table. This is a core feature of relational databases, and is the “glue” that transforms unconnected collection of tables to have rich overlapping relationships.

A second directive, known as relationship(), tells the ORM that the Invoice class should be linked to the Customer class using the attribute Invoice.customer. The relationship() uses the foreign key relationships between the two tables to determine the nature of this linkage, determining that it is many to one.

An additional relationship() directive is placed on the Customer mapped class under the attribute Customer.invoices. The parameter relationship.back_populates is assigned to refer to the complementary attribute names, so that each relationship() can make intelligent decision about the same relationship as expressed in reverse. On one side, Invoices.customer refers to Invoices instance, and on the other side, Customer.invoices refers to a list of Customers instances.

The relationship function is a part of Relationship API of SQLAlchemy ORM package. It provides a relationship between two mapped classes. This corresponds to a parent-child or associative table relationship.

Following are the basic Relationship Patterns found −

One To Many

A One to Many relationship refers to parent with the help of a foreign key on the child table. relationship() is then specified on the parent, as referencing a collection of items represented by the child. The relationship.back_populates parameter is used to establish a bidirectional relationship in one-to-many, where the “reverse” side is a many to one.

Many To One

On the other hand, Many to One relationship places a foreign key in the parent table to refer to the child. relationship() is declared on the parent, where a new scalar-holding attribute will be created. Here again the relationship.back_populates parameter is used for Bidirectionalbehaviour.

One To One

One To One relationship is essentially a bidirectional relationship in nature. The uselist flag indicates the placement of a scalar attribute instead of a collection on the “many” side of the relationship. To convert one-to-many into one-to-one type of relation, set uselist parameter to false.

Many To Many

Many to Many relationship is established by adding an association table related to two classes by defining attributes with their foreign keys. It is indicated by the secondary argument to relationship(). Usually, the Table uses the MetaData object associated with the declarative base class, so that the ForeignKey directives can locate the remote tables with which to link. The relationship.back_populates parameter for each relationship() establishes a bidirectional relationship. Both sides of the relationship contain a collection.

In this chapter, we will focus on the related objects in SQLAlchemy ORM.

Now when we create a Customer object, a blank invoice collection will be present in the form of Python List.

c1 = Customer(name = "Gopal Krishna", address = "Bank Street Hydarebad", email = "gk@gmail.com")

The invoices attribute of c1.invoices will be an empty list. We can assign items in the list as −

c1.invoices = [Invoice(invno = 10, amount = 15000), Invoice(invno = 14, amount = 3850)]

Let us commit this object to the database using Session object as follows −

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

This will automatically generate INSERT queries for customers and invoices tables −

INSERT INTO customers (name, address, email) VALUES (?, ?, ?)
(''Gopal Krishna'', ''Bank Street Hydarebad'', ''gk@gmail.com'')
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 10, 15000)
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 14, 3850)

Let us now look at contents of customers table and invoices table in the table view of SQLiteStudio −

You can construct Customer object by providing mapped attribute of invoices in the constructor itself by using the below command −

c2 = [
   Customer(
      name = "Govind Pant",
      address = "Gulmandi Aurangabad",
      email = "gpant@gmail.com",
      invoices = [Invoice(invno = 3, amount = 10000),
      Invoice(invno = 4, amount = 5000)]
   )
]

Or a list of objects to be added using add_all() function of session object as shown below −

rows = [
   Customer(
      name = "Govind Kala",
      address = "Gulmandi Aurangabad",
      email = "kala@gmail.com",
      invoices = [Invoice(invno = 7, amount = 12000), Invoice(invno = 8, amount = 18500)]),

   Customer(
      name = "Abdul Rahman",
      address = "Rohtak",
      email = "abdulr@gmail.com",
      invoices = [Invoice(invno = 9, amount = 15000),
      Invoice(invno = 11, amount = 6000)
   ])
]

session.add_all(rows)
session.commit()

Now that we have two tables, we will see how to create queries on both tables at the same time. To construct a simple implicit join between Customer and Invoice, we can use Query.filter() to equate their related columns together. Below, we load the Customer and Invoice entities at once using this method −

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

for c, i in session.query(Customer, Invoice).filter(Customer.id == Invoice.custid).all():
   print ("ID: {} Name: {} Invoice No: {} Amount: {}".format(c.id,c.name, i.invno, i.amount))

The SQL expression emitted by SQLAlchemy is as follows −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email, invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM customers, invoices
WHERE customers.id = invoices.custid

And the result of the above lines of code is as follows −

ID: 2 Name: Gopal Krishna Invoice No: 10 Amount: 15000
ID: 2 Name: Gopal Krishna Invoice No: 14 Amount: 3850
ID: 3 Name: Govind Pant Invoice No: 3 Amount: 10000
ID: 3 Name: Govind Pant Invoice No: 4 Amount: 5000
ID: 4 Name: Govind Kala Invoice No: 7 Amount: 12000
ID: 4 Name: Govind Kala Invoice No: 8 Amount: 8500
ID: 5 Name: Abdul Rahman Invoice No: 9 Amount: 15000
ID: 5 Name: Abdul Rahman Invoice No: 11 Amount: 6000

The actual SQL JOIN syntax is easily achieved using the Query.join() method as follows −

session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()

The SQL expression for join will be displayed on the console −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers JOIN invoices ON customers.id = invoices.custid
WHERE invoices.amount = ?

We can iterate through the result using for loop −

result = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500)
for row in result:
   for inv in row.invoices:
      print (row.id, row.name, inv.invno, inv.amount)

With 8500 as the bind parameter, following output is displayed −

4 Govind Kala 8 8500

Query.join() knows how to join between these tables because there’s only one foreign key between them. If there were no foreign keys, or more foreign keys, Query.join() works better when one of the following forms are used −

Similarly outerjoin() function is available to achieve left outer join.

query.outerjoin(Customer.invoices)

The subquery() method produces a SQL expression representing SELECT statement embedded within an alias.

from sqlalchemy.sql import func

stmt = session.query(
   Invoice.custid, func.count(''*'').label(''invoice_count'')
).group_by(Invoice.custid).subquery()

The stmt object will contain a SQL statement as below −

SELECT invoices.custid, count(:count_1) AS invoice_count FROM invoices GROUP BY invoices.custid

Once we have our statement, it behaves like a Table construct. The columns on the statement are accessible through an attribute called c as shown in the below code −

for u, count in session.query(Customer, stmt.c.invoice_count).outerjoin(stmt, Customer.id == stmt.c.custid).order_by(Customer.id):
   print(u.name, count)

The above for loop displays name-wise count of invoices as follows −

Arjun Pandit None
Gopal Krishna 2
Govind Pant 2
Govind Kala 2
Abdul Rahman 2

In this chapter, we will discuss about the operators which build on relationships.

__eq__()

The above operator is a many-to-one “equals” comparison. The line of code for this operator is as shown below −

s = session.query(Customer).filter(Invoice.invno.__eq__(12))

The equivalent SQL query for the above line of code is −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers, invoices
WHERE invoices.invno = ?

__ne__()

This operator is a many-to-one “not equals” comparison. The line of code for this operator is as shown below −

s = session.query(Customer).filter(Invoice.custid.__ne__(2))

The equivalent SQL query for the above line of code is given below −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers, invoices
WHERE invoices.custid != ?

contains()

This operator is used for one-to-many collections and given below is the code for contains() −

s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5]))

The equivalent SQL query for the above line of code is −

SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE (invoices.invno LIKE ''%'' + ? || ''%'')

any()

any() operator is used for collections as shown below −

s = session.query(Customer).filter(Customer.invoices.any(Invoice.invno==11))

The equivalent SQL query for the above line of code is shown below −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE EXISTS (
   SELECT 1
   FROM invoices
   WHERE customers.id = invoices.custid
   AND invoices.invno = ?)

has()

This operator is used for scalar references as follows −

s = session.query(Invoice).filter(Invoice.customer.has(name = ''Arjun Pandit''))

The equivalent SQL query for the above line of code is −

SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE EXISTS (
   SELECT 1
   FROM customers
   WHERE customers.id = invoices.custid
   AND customers.name = ?)

Eager load reduces the number of queries. SQLAlchemy offers eager loading functions invoked via query options which give additional instructions to the Query. These options determine how to load various attributes via the Query.options() method.

Subquery Load

We want that Customer.invoices should load eagerly. The orm.subqueryload() option gives a second SELECT statement that fully loads the collections associated with the results just loaded. The name “subquery” causes the SELECT statement to be constructed directly via the Query re-used and embedded as a subquery into a SELECT against the related table.

from sqlalchemy.orm import subqueryload
c1 = session.query(Customer).options(subqueryload(Customer.invoices)).filter_by(name = ''Govind Pant'').one()

This results in the following two SQL expressions −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.name = ?
(''Govind Pant'',)

SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount, anon_1.customers_id
AS anon_1_customers_id
FROM (
   SELECT customers.id
   AS customers_id
   FROM customers
   WHERE customers.name = ?)

AS anon_1
JOIN invoices
ON anon_1.customers_id = invoices.custid
ORDER BY anon_1.customers_id, invoices.id 2018-06-25 18:24:47,479
INFO sqlalchemy.engine.base.Engine (''Govind Pant'',)

To access the data from two tables, we can use the below program −

print (c1.name, c1.address, c1.email)

for x in c1.invoices:
   print ("Invoice no : {}, Amount : {}".format(x.invno, x.amount))

The output of the above program is as follows −

Govind Pant Gulmandi Aurangabad gpant@gmail.com
Invoice no : 3, Amount : 10000
Invoice no : 4, Amount : 5000

Joined Load

The other function is called orm.joinedload(). This emits a LEFT OUTER JOIN. Lead object as well as the related object or collection is loaded in one step.

from sqlalchemy.orm import joinedload
c1 = session.query(Customer).options(joinedload(Customer.invoices)).filter_by(name=''Govind Pant'').one()

This emits following expression giving same output as above −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email, invoices_1.id
AS invoices_1_id, invoices_1.custid
AS invoices_1_custid, invoices_1.invno
AS invoices_1_invno, invoices_1.amount
AS invoices_1_amount

FROM customers
LEFT OUTER JOIN invoices
AS invoices_1
ON customers.id = invoices_1.custid

WHERE customers.name = ? ORDER BY invoices_1.id
(''Govind Pant'',)

The OUTER JOIN resulted in two rows, but it gives one instance of Customer back. This is because Query applies a “uniquing” strategy, based on object identity, to the returned entities. Joined eager loading can be applied without affecting the query results.

The subqueryload() is more appropriate for loading related collections while joinedload() is better suited for many-to-one relationship.

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 −