Ahora examinaremos las operaciones de filtrado con sus respectivos códigos y salidas.
Normalmente, se utiliza el operador ==, que aplica criterios para probar la igualdad.
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 enviará la siguiente expresión 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 = ?
El resultado del código anterior se ve asÃ:
ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: [email protected]
El operador utilizado para la desigualdad es! =, Y proporciona una prueba de desigualdad.
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)
La expresión SQL resultante es:
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 != ?
El resultado de las lÃneas de código anteriores se ve asÃ:
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected] ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected] ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]
El método like () en sà mismo crea criterios LIKE para la cláusula WHERE en la expresión 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)
El código SQLAlchemy anterior es equivalente a la siguiente declaración 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 ?
Y la salida para el código anterior es:
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected] ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
Este operador comprueba si el valor de la columna pertenece a un conjunto de elementos de la lista. Esto lo proporciona el método 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)
AquÃ, la expresión SQL evaluada por el motor SQLite serÃa la siguiente:
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 (?, ?)
El resultado del código anterior se ve asÃ:
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected] ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
Esta conjunción se genera ya sea agregar varios criterios separados por comas a un filtro o usar el método and_ () como se indica a continuación:
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)
Ambos enfoques anteriores dan como resultado una declaración SQL similar:
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 ?
El resultado de las lÃneas de código anteriores es:
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
Esta conexión está implementada método 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)
Como resultado, el motor SQLite obtiene la siguiente expresión SQL equivalente:
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 ?
El resultado del código anterior se ve asÃ:
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected] ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected] ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]
🚫