SQLAlchemy ORM - Filteroperatoren

Jetzt lernen wir die Filteroperationen mit ihren jeweiligen Codes und Ausgaben.

Gleich

Der übliche verwendete Operator ist == und er wendet die Kriterien an, um die Gleichheit zu überprüfen.

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 sendet folgenden SQL-Ausdruck:

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 = ?

Die Ausgabe für den obigen Code lautet wie folgt:

ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: [email protected]

Nicht gleich

Der Operator, der für ungleich verwendet wird, ist! = Und liefert nicht gleich Kriterien.

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)

Der resultierende SQL-Ausdruck lautet -

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 != ?

Die Ausgabe für die obigen Codezeilen lautet wie folgt:

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]

Mögen

Die Methode like () selbst erzeugt die LIKE-Kriterien für die WHERE-Klausel im SELECT-Ausdruck.

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)

Der obige SQLAlchemy-Code entspricht dem folgenden SQL-Ausdruck:

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 ?

Und die Ausgabe für den obigen Code ist -

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]

IM

Dieser Operator prüft, ob der Spaltenwert zu einer Sammlung von Elementen in einer Liste gehört. Es wird von der Methode in_ () bereitgestellt.

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)

Hier lautet der von der SQLite-Engine ausgewertete SQL-Ausdruck wie folgt:

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 (?, ?)

Die Ausgabe für den obigen Code lautet wie folgt:

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]

UND

Diese Konjunktion wird von beiden generiert putting multiple commas separated criteria in the filter or using and_() method wie unten angegeben -

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)

Beide oben genannten Ansätze führen zu einem ähnlichen SQL-Ausdruck -

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 ?

Die Ausgabe für die obigen Codezeilen ist -

ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]

ODER

Diese Konjunktion wird implementiert von 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)

Infolgedessen erhält die SQLite-Engine den folgenden äquivalenten SQL-Ausdruck:

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 ?

Die Ausgabe für den obigen Code lautet wie folgt:

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]