SQLAlchemy Core - Using Conjunctions

---

---

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