SQLAlchemy ORM – операторы фильтров
Теперь мы изучим операции фильтрации с соответствующими кодами и выводом.
Равно
Обычно используется оператор ==, и он применяет критерии для проверки равенства.
result = session.query(Customers).filter(Customers.id == 2) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)
SQLAlchemy отправит следующее выражение SQL –
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.id = ?
Выход для вышеуказанного кода выглядит следующим образом:
ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: komal@gmail.com
Не равно
Оператор, используемый для не равно, – это! =, И он предоставляет критерии не равно.
result = session.query(Customers).filter(Customers.id! = 2) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)
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.id != ?
Вывод для приведенных выше строк кода выглядит следующим образом:
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com
подобно
Сам метод like () создает критерии LIKE для предложения WHERE в выражении SELECT.
result = session.query(Customers).filter(Customers.name.like('Ra%')) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)
Выше SQLAlchemy код эквивалентен следующему выражению SQL –
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 LIKE ?
И вывод для вышеуказанного кода –
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
В
Этот оператор проверяет, принадлежит ли значение столбца к коллекции элементов в списке. Это обеспечивается методом in_ ().
result = session.query(Customers).filter(Customers.id.in_([1,3])) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)
Здесь выражение SQL, вычисляемое механизмом SQLite, будет выглядеть следующим образом:
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.id IN (?, ?)
Выход для вышеуказанного кода выглядит следующим образом:
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
А ТАКЖЕ
Это соединение создается путем помещения нескольких критериев, разделенных запятыми, в фильтре или использования метода and_ (), как указано ниже –
result = session.query(Customers).filter(Customers.id>2, Customers.name.like('Ra%')) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)
from sqlalchemy import and_ result = session.query(Customers).filter(and_(Customers.id>2, Customers.name.like('Ra%'))) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)
Оба вышеуказанных подхода приводят к похожему выражению SQL –
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.id > ? AND customers.name LIKE ?
Выход для вышеуказанных строк кода –
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
ИЛИ ЖЕ
Это соединение реализуется методом or_ () .
from sqlalchemy import or_ result = session.query(Customers).filter(or_(Customers.id>2, Customers.name.like('Ra%'))) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)
В результате движок SQLite получает следующее эквивалентное выражение SQL –
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.id > ? OR customers.name LIKE ?
Выход для вышеуказанного кода выглядит следующим образом:
SQLAlchemy ORM — Filter Operators
Now, we will learn the filter operations with their respective codes and output.
Equals
The usual operator used is == and it applies the criteria to check equality.
result = session.query(Customers).filter(Customers.id == 2) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email) SQLAlchemy will send 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 WHERE customers.id = ?
The output for the above code is as follows −
ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: komal@gmail.com
Not Equals
The operator used for not equals is != and it provides not equals criteria.
result = session.query(Customers).filter(Customers.id! = 2) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email) The resulting SQL expression is −
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.id != ?
The output for the above lines of code is as follows −
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com
Like
like() method itself produces the LIKE criteria for WHERE clause in the SELECT expression.
result = session.query(Customers).filter(Customers.name.like('Ra%')) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email) Above SQLAlchemy code is 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 WHERE customers.name LIKE ?
And the output for the above code is −
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
IN
This operator checks whether the column value belongs to a collection of items in a list. It is provided by in_() method.
result = session.query(Customers).filter(Customers.id.in_([1,3])) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email) Here, the SQL expression evaluated by SQLite engine will be as follows −
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.id IN (?, ?)
The output for the above code is as follows −
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
AND
This conjunction is generated by either putting multiple commas separated criteria in the filter or using and_() method as given below −
result = session.query(Customers).filter(Customers.id>2, Customers.name.like('Ra%')) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email) from sqlalchemy import and_ result = session.query(Customers).filter(and_(Customers.id>2, Customers.name.like('Ra%'))) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email) Both the above approaches result in similar 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 WHERE customers.id > ? AND customers.name LIKE ?
The output for the above lines of code is −
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
OR
This conjunction is implemented by or_() method.
from sqlalchemy import or_ result = session.query(Customers).filter(or_(Customers.id>2, Customers.name.like('Ra%'))) for row in result: print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email) As a result, SQLite engine gets following equivalent 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 WHERE customers.id > ? OR customers.name LIKE ?
The output for the above code is as follows −
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com