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 inspanidual 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 pne 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( sqpte:///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 falpng 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 psts 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( sqpte:///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 pnes 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 vapdate 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 −