Conjunctions are features in SQLAlchemy module that put into effect relational operators used in WHERE clause of SQL expressions. The operators AND, OR, NOT, and so forth., are used to shape a compound expression combining two person logical expressions. A simple example of the use of AND in SELECT declaration is as follows −
SELECT * from EMPLOYEE WHERE salary>10000 AND age>30
SQLAlchemy features and_(), or_() and not_() respectively enforce AND, OR and NOT operators.
and_() function
It produces a conjunction of expressions joined by using AND. An instance is given below for better know-how −
from sqlalchemy import and_
print(
and_(
students.c.name == 'Ravi',
students.c.id <3
)
)
This interprets to −
students.name = :name_1 AND students.id < :id_1
To use and_() in a pick out() assemble on a students table, use the following line of code −
stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))
SELECT announcement of the following nature can be built −
SELECT students.id,
students.name,
students.lastname
FROM students
WHERE students.name = :name_1 AND students.id < :id_1
The whole 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 decided on assuming that scholars desk is populated with facts used in preceding instance −
[(1, 'Ravi', 'Kapoor')]
or_() function
It produces conjunction of expressions joined via OR. We shall update the stmt item inside the above example with the following one the usage of or_()
stmt = select([students]).where(or_(students.c.name == 'Ravi', students.c.id <3))
Which may be efficaciously equal 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 are making the substitution and run the above code, the result could be rows falling inside the OR condition −
[(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Khanna')]
asc() function
It produces an ascending ORDER BY clause. The function takes the column to use the characteristic as a parameter.
from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))
The announcement implements following SQL expression −
SELECT students.id,
students.name,
students.lastname
FROM students
ORDER BY students.name ASC
Following code lists out all statistics in students desk in ascending order of call 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 equal SQL expression is −
SELECT students.id,
students.name,
students.lastname
FROM students
ORDER BY students.lastname DESC
And the output for the above traces 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 typically used to validate if fee of a certain column falls between a range. For example, following code selects rows for which identification column is between 2 and four −
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 end result is as follows −
(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')