SQLAlchemy ORM - Operator Filter
Sekarang, kita akan mempelajari operasi filter dengan kode dan keluarannya masing-masing.
Sama
Operator yang biasa digunakan adalah == dan menerapkan kriteria untuk mengecek kesetaraan.
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 akan mengirimkan ekspresi SQL berikut -
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 = ?
Output untuk kode di atas adalah sebagai berikut -
ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: [email protected]
Tidak Sama
Operator yang digunakan untuk not sama adalah! = Dan ini memberikan kriteria tidak sama.
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)
Ekspresi SQL yang dihasilkan adalah -
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 != ?
Output untuk baris kode di atas adalah sebagai berikut -
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]
Suka
like () metode itu sendiri menghasilkan kriteria LIKE untuk klausa WHERE dalam ekspresi 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)
Di atas kode SQLAlchemy sama dengan ekspresi SQL berikut -
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 ?
Dan output dari kode di atas adalah -
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
DI
Operator ini memeriksa apakah nilai kolom milik kumpulan item dalam daftar. Ini disediakan oleh metode 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)
Di sini, ekspresi SQL yang dievaluasi oleh mesin SQLite adalah sebagai berikut -
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 (?, ?)
Output untuk kode di atas adalah sebagai berikut -
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
DAN
Hubungan ini dihasilkan oleh keduanya putting multiple commas separated criteria in the filter or using and_() method seperti yang diberikan di bawah ini -
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)
Kedua pendekatan di atas menghasilkan ekspresi SQL yang serupa -
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 ?
Output untuk baris kode di atas adalah -
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
ATAU
Hubungan ini diimplementasikan oleh 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)
Akibatnya, mesin SQLite mengikuti ekspresi SQL yang setara -
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 ?
Output untuk kode di atas adalah sebagai berikut -
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]