SQLAlchemy - Panduan Cepat
SQLAlchemy adalah toolkit SQL dan Object Relational Mapper. Itu tertulis dalamPythondan memberikan kekuatan penuh dan fleksibilitas SQL kepada pengembang aplikasi. Ini adalah sebuahopen source dan cross-platform software dirilis di bawah lisensi MIT.
SQLAlchemy terkenal dengan object-relational mapper (ORM), yang dengannya, kelas dapat dipetakan ke database, sehingga memungkinkan model objek dan skema database untuk berkembang dengan cara yang dipisahkan dengan rapi dari awal.
Karena ukuran dan kinerja database SQL mulai menjadi penting, mereka berperilaku kurang seperti kumpulan objek. Di sisi lain, ketika abstraksi dalam koleksi objek mulai menjadi penting, mereka berperilaku kurang seperti tabel dan baris. SQLAlchemy bertujuan untuk mengakomodasi kedua prinsip ini.
Untuk alasan ini, telah mengadopsi data mapper pattern (like Hibernate) rather than the active record pattern used by a number of other ORMs. Database dan SQL akan dilihat dalam perspektif yang berbeda menggunakan SQLAlchemy.
Michael Bayer adalah penulis asli SQLAlchemy. Versi awalnya dirilis pada Februari 2006. Versi terbaru diberi nomor 1.2.7, dirilis paling baru pada April 2018.
Apa itu ORM?
ORM (Object Relational Mapping) adalah teknik pemrograman untuk mengubah data antara sistem tipe yang tidak kompatibel dalam bahasa pemrograman berorientasi objek. Biasanya, sistem tipe yang digunakan dalam bahasa Berorientasi Objek (OO) seperti Python berisi tipe non-skalar. Ini tidak dapat diekspresikan sebagai tipe primitif seperti integer dan string. Oleh karena itu, programmer OO harus mengkonversi objek dalam data skalar untuk berinteraksi dengan database backend. Namun, tipe data di sebagian besar produk database seperti Oracle, MySQL, dll., Bersifat primer.
Dalam sistem ORM, setiap kelas dipetakan ke tabel di database yang mendasarinya. Alih-alih menulis sendiri kode antarmuka database yang membosankan, ORM menangani masalah ini untuk Anda sementara Anda dapat fokus pada pemrograman logika sistem.
SQLAlchemy - Pengaturan lingkungan
Mari kita bahas pengaturan lingkungan yang diperlukan untuk menggunakan SQLAlchemy.
Versi Python apa pun yang lebih tinggi dari 2.7 diperlukan untuk menginstal SQLAlchemy. Cara termudah untuk menginstal adalah dengan menggunakan Python Package Manager,pip. Utilitas ini dibundel dengan distribusi standar Python.
pip install sqlalchemyDengan menggunakan perintah di atas, kita dapat mengunduh file latest released versionSQLAlchemy dari python.org dan menginstalnya ke sistem Anda.
Dalam kasus distribusi anakonda dari Python, SQLAlchemy dapat diinstal dari conda terminal menggunakan perintah di bawah ini -
conda install -c anaconda sqlalchemyAnda juga dapat menginstal SQLAlchemy dari kode sumber di bawah ini -
python setup.py installSQLAlchemy dirancang untuk beroperasi dengan implementasi DBAPI yang dibuat untuk database tertentu. Ia menggunakan sistem dialek untuk berkomunikasi dengan berbagai jenis implementasi dan database DBAPI. Semua dialek mengharuskan driver DBAPI yang sesuai diinstal.
Berikut ini adalah dialek yang disertakan -
- Firebird
- Microsoft SQL Server
- MySQL
- Oracle
- PostgreSQL
- SQLite
- Sybase
Untuk memeriksa apakah SQLAlchemy diinstal dengan benar dan untuk mengetahui versinya, masukkan perintah berikut di prompt Python -
>>> import sqlalchemy
>>>sqlalchemy.__version__
'1.2.7'Termasuk inti SQLAlchemy SQL rendering engine, DBAPI integration, transaction integration, dan schema description services. Inti SQLAlchemy menggunakan SQL Expression Language yang menyediakan fileschema-centric usage paradigma sedangkan SQLAlchemy ORM adalah a domain-centric mode of usage.
SQL Expression Language menyajikan sistem yang merepresentasikan struktur dan ekspresi database relasional menggunakan konstruksi Python. Ini menyajikan sistem yang merepresentasikan konstruksi primitif dari database relasional secara langsung tanpa opini, yang berbeda dengan ORM yang menyajikan pola penggunaan tingkat tinggi dan abstrak, yang dengan sendirinya merupakan contoh penggunaan terapan dari Expression Language.
Expression Language adalah salah satu komponen inti SQLAlchemy. Ini memungkinkan programmer untuk menentukan pernyataan SQL dalam kode Python dan menggunakannya secara langsung dalam kueri yang lebih kompleks. Bahasa ekspresi tidak bergantung pada backend dan secara komprehensif mencakup setiap aspek SQL mentah. Ini lebih dekat ke SQL mentah daripada komponen lain di SQLAlchemy.
Expression Language merepresentasikan konstruksi primitif dari database relasional secara langsung. Karena ORM didasarkan pada bahasa Expression, aplikasi database Python yang khas mungkin memiliki penggunaan keduanya yang tumpang tindih. Aplikasi dapat menggunakan bahasa ekspresi saja, meskipun harus mendefinisikan sistemnya sendiri untuk menerjemahkan konsep aplikasi ke dalam query database individual.
Pernyataan bahasa Expression akan diterjemahkan ke dalam kueri SQL mentah yang sesuai oleh mesin SQLAlchemy. Sekarang kita akan belajar bagaimana membuat mesin dan menjalankan berbagai query SQL dengan bantuannya.
Pada bab sebelumnya, kita telah membahas tentang bahasa ekspresi di SQLAlchemy. Sekarang mari kita lanjutkan ke langkah-langkah yang terlibat dalam menghubungkan ke database.
Kelas mesin menghubungkan a Pool and Dialect together untuk menyediakan sumber database connectivity and behavior. Objek kelas Engine dibuat menggunakancreate_engine() fungsi.
Fungsi create_engine () mengambil database sebagai satu argumen. Database tidak perlu didefinisikan di mana pun. Bentuk panggilan standar harus mengirimkan URL sebagai argumen posisi pertama, biasanya string yang menunjukkan dialek database dan argumen koneksi. Dengan menggunakan kode yang diberikan di bawah ini, kita dapat membuat database.
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///college.db', echo = True)Untuk sebuah MySQL database, gunakan perintah di bawah ini -
engine = create_engine("mysql://user:pwd@localhost/college",echo = True)Untuk menyebutkan secara khusus DB-API digunakan untuk koneksi, file URL string mengambil bentuk sebagai berikut -
dialect[+driver]://user:password@host/dbnameMisalnya, jika Anda menggunakan PyMySQL driver with MySQL, gunakan perintah berikut -
mysql+pymysql://<username>:<password>@<host>/<dbname>Itu echo flagadalah jalan pintas untuk menyiapkan logging SQLAlchemy, yang dilakukan melalui modul logging standar Python. Pada bab-bab selanjutnya, kita akan mempelajari semua SQL yang dihasilkan. Untuk menyembunyikan output verbose, setel atribut echo keNone. Argumen lain untuk fungsi create_engine () mungkin dialek khusus.
Fungsi create_engine () mengembalikan file Engine object. Beberapa metode penting dari kelas Engine adalah -
| Sr.No. | Metode & Deskripsi |
|---|---|
| 1 | connect() Mengembalikan objek koneksi |
| 2 | execute() Menjalankan konstruksi pernyataan SQL |
| 3 | begin() Mengembalikan manajer konteks yang memberikan Koneksi dengan Transaksi yang dibuat. Setelah operasi berhasil, Transaksi dilakukan, jika tidak maka dibatalkan |
| 4 | dispose() Membuang kumpulan koneksi yang digunakan oleh Mesin |
| 5 | driver() Nama driver dari Dialek yang digunakan oleh Mesin |
| 6 | table_names() Menampilkan daftar semua nama tabel yang tersedia di database |
| 7 | transaction() Menjalankan fungsi yang diberikan dalam batas transaksi |
Sekarang mari kita bahas cara menggunakan fungsi buat tabel.
Bahasa Ekspresi SQL menyusun ekspresinya terhadap kolom tabel. Objek Kolom SQLAlchemy mewakili acolumn dalam tabel database yang diwakili oleh a Tableobject. Metadata berisi definisi tabel dan objek terkait seperti indeks, tampilan, pemicu, dll.
Oleh karena itu, objek kelas MetaData dari SQLAlchemy Metadata adalah kumpulan objek Tabel dan konstruksi skema yang terkait. Ini menyimpan koleksi objek Tabel serta pengikatan opsional ke Mesin atau Koneksi.
from sqlalchemy import MetaData
meta = MetaData()Pembuat kelas MetaData dapat memiliki parameter bind dan skema yang secara default None.
Selanjutnya, kami mendefinisikan tabel kami semua dalam katalog metadata di atas, menggunakan the Table construct, yang menyerupai pernyataan SQL CREATE TABLE biasa.
Objek kelas Tabel mewakili tabel terkait dalam database. Konstruktor mengambil parameter berikut -
| Nama | Nama tabel |
|---|---|
| Metadata | Objek MetaData yang akan menampung tabel ini |
| Kolom | Satu atau lebih objek dari kelas kolom |
Objek kolom mewakili a column di sebuah database table. Pembuat mengambil nama, jenis dan parameter lain seperti kunci_rimer, autoincrement dan batasan lainnya.
SQLAlchemy mencocokkan data Python dengan tipe data kolom generik terbaik yang didefinisikan di dalamnya. Beberapa tipe data generik adalah -
- BigInteger
- Boolean
- Date
- DateTime
- Float
- Integer
- Numeric
- SmallInteger
- String
- Text
- Time
Untuk membuat file students table dalam database perguruan tinggi, gunakan cuplikan berikut -
from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)Fungsi create_all () menggunakan objek mesin untuk membuat semua objek tabel yang ditentukan dan menyimpan informasi dalam metadata.
meta.create_all(engine)Kode lengkap diberikan di bawah ini yang akan membuat database SQLite college.db dengan tabel siswa di dalamnya.
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)
meta.create_all(engine)Karena atribut echo dari fungsi create_engine () diatur ke True, konsol akan menampilkan kueri SQL sebenarnya untuk pembuatan tabel sebagai berikut -
CREATE TABLE students (
id INTEGER NOT NULL,
name VARCHAR,
lastname VARCHAR,
PRIMARY KEY (id)
)College.db akan dibuat di direktori kerja saat ini. Untuk memeriksa apakah tabel siswa dibuat, Anda dapat membuka database menggunakan alat GUI SQLite sepertiSQLiteStudio.
Gambar di bawah ini menunjukkan tabel siswa yang dibuat di database -
Dalam bab ini, kita akan fokus secara singkat pada SQL Expressions dan fungsinya.
Ekspresi SQL dibangun menggunakan metode yang sesuai relatif terhadap objek tabel target. Misalnya, pernyataan INSERT dibuat dengan menjalankan metode insert () sebagai berikut -
ins = students.insert()Hasil dari metode di atas adalah sebuah objek sisipan yang dapat diverifikasi dengan menggunakan str()fungsi. Kode di bawah ini memasukkan detail seperti id siswa, nama, nama belakang.
'INSERT INTO students (id, name, lastname) VALUES (:id, :name, :lastname)'Dimungkinkan untuk memasukkan nilai dalam bidang tertentu dengan values()metode untuk memasukkan objek. Kode yang sama diberikan di bawah ini -
>>> ins = users.insert().values(name = 'Karan')
>>> str(ins)
'INSERT INTO users (name) VALUES (:name)'SQL yang di-echo-kan pada konsol Python tidak menunjukkan nilai sebenarnya ('Karan' dalam kasus ini). Sebaliknya, SQLALchemy menghasilkan parameter bind yang terlihat dalam bentuk pernyataan yang dikompilasi.
ins.compile().params
{'name': 'Karan'}Demikian pula, metode seperti update(), delete() dan select()buat ekspresi UPDATE, DELETE dan SELECT masing-masing. Kita akan mempelajarinya di bab-bab selanjutnya.
Di bab sebelumnya, kita telah mempelajari Ekspresi SQL. Dalam bab ini, kita akan melihat eksekusi dari ekspresi ini.
Untuk mengeksekusi ekspresi SQL yang dihasilkan, kita harus obtain a connection object representing an actively checked out DBAPI connection resource lalu feed the expression object seperti yang ditunjukkan pada kode di bawah ini.
conn = engine.connect()Objek insert () berikut dapat digunakan untuk metode execute () -
ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
result = conn.execute(ins)Konsol menunjukkan hasil eksekusi ekspresi SQL seperti di bawah ini -
INSERT INTO students (name, lastname) VALUES (?, ?)
('Ravi', 'Kapoor')
COMMITBerikut ini adalah keseluruhan cuplikan yang menunjukkan eksekusi kueri INSERT menggunakan teknik inti SQLAlchemy -
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)
ins = students.insert()
ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
conn = engine.connect()
result = conn.execute(ins)Hasilnya dapat diverifikasi dengan membuka database menggunakan SQLite Studio seperti yang ditunjukkan pada gambar di bawah ini -
Variabel hasil dikenal sebagai ResultProxy object. Ini serupa dengan objek kursor DBAPI. Kami dapat memperoleh informasi tentang nilai kunci utama yang dihasilkan dari pernyataan kami menggunakanResultProxy.inserted_primary_key seperti yang ditunjukkan di bawah ini -
result.inserted_primary_key
[1]Untuk menerbitkan banyak sisipan menggunakan metode execute many () DBAPI, kita dapat mengirimkan daftar kamus yang masing-masing berisi seperangkat parameter berbeda untuk disisipkan.
conn.execute(students.insert(), [
{'name':'Rajiv', 'lastname' : 'Khanna'},
{'name':'Komal','lastname' : 'Bhandari'},
{'name':'Abdul','lastname' : 'Sattar'},
{'name':'Priya','lastname' : 'Rajhans'},
])Ini tercermin dalam tampilan data tabel seperti yang ditunjukkan pada gambar berikut -
Pada bab ini, kita akan membahas tentang konsep pemilihan baris pada objek tabel.
Metode select () dari objek tabel memungkinkan kita melakukannya construct SELECT expression.
s = students.select()Objek yang dipilih diterjemahkan menjadi SELECT query by str(s) function seperti yang ditunjukkan di bawah ini -
'SELECT students.id, students.name, students.lastname FROM students'Kita dapat menggunakan objek pilihan ini sebagai parameter untuk mengeksekusi () metode objek koneksi seperti yang ditunjukkan pada kode di bawah ini -
result = conn.execute(s)Ketika pernyataan di atas dijalankan, shell Python menggema mengikuti ekspresi SQL yang setara -
SELECT students.id, students.name, students.lastname
FROM studentsVariabel yang dihasilkan setara dengan kursor di DBAPI. Kami sekarang dapat mengambil catatan menggunakanfetchone() method.
row = result.fetchone()Semua baris yang dipilih dalam tabel dapat dicetak dengan a for loop seperti yang diberikan di bawah ini -
for row in result:
print (row)Kode lengkap untuk mencetak semua baris dari tabel siswa ditunjukkan di bawah ini -
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)
s = students.select()
conn = engine.connect()
result = conn.execute(s)
for row in result:
print (row)Output yang ditampilkan dalam shell Python adalah sebagai berikut -
(1, 'Ravi', 'Kapoor')
(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')Klausa WHERE dari kueri SELECT dapat diterapkan dengan menggunakan Select.where(). Misalnya jika kita ingin menampilkan baris dengan id> 2
s = students.select().where(students.c.id>2)
result = conn.execute(s)
for row in result:
print (row)Sini c attribute is an alias for column. Output berikut akan ditampilkan di shell -
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')Di sini, kita harus mencatat bahwa objek tertentu juga dapat diperoleh dengan fungsi select () dalam modul sqlalchemy.sql. Fungsi select () membutuhkan objek tabel sebagai argumen.
from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)SQLAlchemy memungkinkan Anda hanya menggunakan string, untuk kasus-kasus ketika SQL sudah dikenal dan tidak ada kebutuhan kuat akan pernyataan untuk mendukung fitur dinamis. Konstruksi text () digunakan untuk membuat pernyataan tekstual yang diteruskan ke database sebagian besar tidak berubah.
Ini membangun yang baru TextClause, mewakili string SQL tekstual secara langsung seperti yang ditunjukkan pada kode di bawah ini -
from sqlalchemy import text
t = text("SELECT * FROM students")
result = connection.execute(t)Keuntungannya text() menyediakan lebih dari string biasa adalah -
- dukungan backend-netral untuk parameter mengikat
- opsi eksekusi per pernyataan
- perilaku pengetikan kolom-hasil
Fungsi text () membutuhkan parameter Terikat dalam format titik dua bernama. Mereka konsisten terlepas dari backend database. Untuk mengirim nilai untuk parameter, kami meneruskannya ke metode execute () sebagai argumen tambahan.
Contoh berikut menggunakan parameter terikat dalam SQL tekstual -
from sqlalchemy.sql import text
s = text("select students.name, students.lastname from students where students.name between :x and :y")
conn.execute(s, x = 'A', y = 'L').fetchall()Fungsi text () menyusun ekspresi SQL sebagai berikut -
select students.name, students.lastname from students where students.name between ? and ?Nilai x = 'A' dan y = 'L' dilewatkan sebagai parameter. Hasilnya adalah daftar baris dengan nama antara 'A' dan 'L' -
[('Komal', 'Bhandari'), ('Abdul', 'Sattar')]Konstruksi text () mendukung nilai terikat yang telah ditetapkan sebelumnya menggunakan metode TextClause.bindparams (). Parameter juga dapat diketik secara eksplisit sebagai berikut -
stmt = text("SELECT * FROM students WHERE students.name BETWEEN :x AND :y")
stmt = stmt.bindparams(
bindparam("x", type_= String),
bindparam("y", type_= String)
)
result = conn.execute(stmt, {"x": "A", "y": "L"})
The text() function also be produces fragments of SQL within a select() object that
accepts text() objects as an arguments. The “geometry” of the statement is provided by
select() construct , and the textual content by text() construct. We can build a statement
without the need to refer to any pre-established Table metadata.
from sqlalchemy.sql import select
s = select([text("students.name, students.lastname from students")]).where(text("students.name between :x and :y"))
conn.execute(s, x = 'A', y = 'L').fetchall()Anda juga bisa menggunakan and_() fungsi untuk menggabungkan beberapa kondisi di klausa WHERE dibuat dengan bantuan fungsi text ().
from sqlalchemy import and_
from sqlalchemy.sql import select
s = select([text("* from students")]) \
.where(
and_(
text("students.name between :x and :y"),
text("students.id>2")
)
)
conn.execute(s, x = 'A', y = 'L').fetchall()Kode di atas mengambil baris dengan nama antara "A" dan "L" dengan id lebih besar dari 2. Output kode diberikan di bawah ini -
[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar')]Alias dalam SQL sesuai dengan versi "berganti nama" dari tabel atau pernyataan SELECT, yang terjadi setiap kali Anda mengatakan "SELECT * FROM table1 AS a". AS membuat nama baru untuk tabel tersebut. Alias memungkinkan tabel atau subkueri apa pun untuk direferensikan dengan nama unik.
Dalam kasus tabel, ini memungkinkan tabel yang sama diberi nama dalam klausa FROM beberapa kali. Ini memberikan nama induk untuk kolom yang diwakili oleh pernyataan, memungkinkan mereka untuk direferensikan relatif terhadap nama ini.
Di SQLAlchemy, tabel apa pun, konstruksi select (), atau objek lain yang dapat dipilih dapat diubah menjadi alias menggunakan From Clause.alias()metode, yang menghasilkan konstruksi Alias. Fungsi alias () dalam modul sqlalchemy.sql mewakili alias, seperti yang biasanya diterapkan ke tabel atau sub-pilih apa pun dalam pernyataan SQL menggunakan kata kunci AS.
from sqlalchemy.sql import alias
st = students.alias("a")Alias ini sekarang dapat digunakan dalam konstruksi select () untuk merujuk ke tabel siswa -
s = select([st]).where(st.c.id>2)Ini diterjemahkan menjadi ekspresi SQL sebagai berikut -
SELECT a.id, a.name, a.lastname FROM students AS a WHERE a.id > 2Sekarang kita bisa mengeksekusi query SQL ini dengan metode execution () dari objek koneksi. Kode lengkapnya adalah sebagai berikut -
from sqlalchemy.sql import alias, select
st = students.alias("a")
s = select([st]).where(st.c.id > 2)
conn.execute(s).fetchall()Ketika baris kode di atas dijalankan, itu menghasilkan output berikut -
[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar'), (5, 'Priya', 'Rajhans')]Itu update() metode pada objek tabel target membangun ekspresi SQL UPDATE yang setara.
table.update().where(conditions).values(SET expressions)Itu values()metode pada objek pembaruan yang dihasilkan digunakan untuk menentukan kondisi SET UPDATE. Jika dibiarkan sebagai Tidak ada, kondisi SET ditentukan dari parameter yang diteruskan ke pernyataan selama eksekusi dan / atau kompilasi pernyataan.
Klausa where adalah ekspresi opsional yang menjelaskan kondisi WHERE dari pernyataan UPDATE.
Potongan kode berikut mengubah nilai kolom 'lastname' dari 'Khanna' menjadi 'Kapoor' dalam tabel siswa -
stmt = students.update().where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')Objek stmt adalah objek pembaruan yang diterjemahkan menjadi -
'UPDATE students SET lastname = :lastname WHERE students.lastname = :lastname_1'Parameter terikat lastname_1 akan diganti kapan execute()metode dipanggil. Kode pembaruan lengkap diberikan di bawah ini -
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
students = Table(
'students',
meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)
conn = engine.connect()
stmt=students.update().where(students.c.lastname=='Khanna').values(lastname='Kapoor')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()Kode di atas menampilkan output berikut dengan baris kedua menunjukkan efek operasi pembaruan seperti pada tangkapan layar yang diberikan -
[
(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Kapoor'),
(3, 'Komal', 'Bhandari'),
(4, 'Abdul', 'Sattar'),
(5, 'Priya', 'Rajhans')
]
Perhatikan bahwa fungsionalitas serupa juga dapat dicapai dengan menggunakan update() fungsi dalam modul sqlalchemy.sql.expression seperti yang ditunjukkan di bawah ini -
from sqlalchemy.sql.expression import update
stmt = update(students).where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')Pada bab sebelumnya, kita telah memahami apa itu Updateekspresi tidak. Ekspresi selanjutnya yang akan kita pelajari adalahDelete.
Operasi delete dapat dilakukan dengan menjalankan metode delete () pada objek tabel target seperti yang diberikan dalam pernyataan berikut -
stmt = students.delete()Dalam kasus tabel siswa, baris kode di atas membangun ekspresi SQL sebagai berikut -
'DELETE FROM students'Namun, ini akan menghapus semua baris dalam tabel siswa. Biasanya kueri DELETE dikaitkan dengan ekspresi logis yang ditentukan oleh klausa WHERE. Pernyataan berikut menunjukkan di mana parameter -
stmt = students.delete().where(students.c.id > 2)Ekspresi SQL yang dihasilkan akan memiliki parameter terikat yang akan diganti saat runtime ketika pernyataan dijalankan.
'DELETE FROM students WHERE students.id > :id_1'Contoh kode berikut akan menghapus baris tersebut dari tabel siswa yang memiliki nama belakang sebagai 'Khanna' -
from sqlalchemy.sql.expression import update
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)
conn = engine.connect()
stmt = students.delete().where(students.c.lastname == 'Khanna')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()Untuk memverifikasi hasil, segarkan tampilan data tabel siswa di SQLiteStudio.
Salah satu fitur penting dari RDBMS adalah membangun hubungan antar tabel. Operasi SQL seperti SELECT, UPDATE dan DELETE dapat dilakukan pada tabel terkait. Bagian ini menjelaskan operasi ini menggunakan SQLAlchemy.
Untuk tujuan ini, dua tabel dibuat di database SQLite kami (college.db). Tabel siswa memiliki struktur yang sama seperti yang diberikan pada bagian sebelumnya; sedangkan tabel alamat memilikist_id kolom yang dipetakan ke id column in students table menggunakan batasan kunci asing.
Kode berikut akan membuat dua tabel di college.db -
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('sqlite:///college.db', echo=True)
meta = MetaData()
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)
addresses = Table(
'addresses', meta,
Column('id', Integer, primary_key = True),
Column('st_id', Integer, ForeignKey('students.id')),
Column('postal_add', String),
Column('email_add', String))
meta.create_all(engine)Kode di atas akan diterjemahkan menjadi kueri CREATE TABLE untuk siswa dan tabel alamat seperti di bawah ini -
CREATE TABLE students (
id INTEGER NOT NULL,
name VARCHAR,
lastname VARCHAR,
PRIMARY KEY (id)
)
CREATE TABLE addresses (
id INTEGER NOT NULL,
st_id INTEGER,
postal_add VARCHAR,
email_add VARCHAR,
PRIMARY KEY (id),
FOREIGN KEY(st_id) REFERENCES students (id)
)Tangkapan layar berikut menyajikan kode di atas dengan sangat jelas -
Tabel ini diisi dengan data dengan menjalankan insert() methodobjek tabel. Untuk memasukkan 5 baris dalam tabel siswa, Anda dapat menggunakan kode yang diberikan di bawah ini -
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
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),
)
conn.execute(students.insert(), [
{'name':'Ravi', 'lastname':'Kapoor'},
{'name':'Rajiv', 'lastname' : 'Khanna'},
{'name':'Komal','lastname' : 'Bhandari'},
{'name':'Abdul','lastname' : 'Sattar'},
{'name':'Priya','lastname' : 'Rajhans'},
])Rows ditambahkan dalam tabel alamat dengan bantuan kode berikut -
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()
addresses = Table(
'addresses', meta,
Column('id', Integer, primary_key = True),
Column('st_id', Integer),
Column('postal_add', String),
Column('email_add', String)
)
conn.execute(addresses.insert(), [
{'st_id':1, 'postal_add':'Shivajinagar Pune', 'email_add':'[email protected]'},
{'st_id':1, 'postal_add':'ChurchGate Mumbai', 'email_add':'[email protected]'},
{'st_id':3, 'postal_add':'Jubilee Hills Hyderabad', 'email_add':'[email protected]'},
{'st_id':5, 'postal_add':'MG Road Bangaluru', 'email_add':'[email protected]'},
{'st_id':2, 'postal_add':'Cannought Place new Delhi', 'email_add':'[email protected]'},
])Perhatikan bahwa kolom st_id pada tabel address mengacu pada kolom id pada tabel siswa. Sekarang kita dapat menggunakan relasi ini untuk mengambil data dari kedua tabel. Kami ingin menjemputname dan lastname dari tabel siswa sesuai dengan st_id di tabel alamat.
from sqlalchemy.sql import select
s = select([students, addresses]).where(students.c.id == addresses.c.st_id)
result = conn.execute(s)
for row in result:
print (row)Objek yang dipilih akan secara efektif diterjemahkan ke dalam ekspresi SQL berikut yang menggabungkan dua tabel pada hubungan yang sama -
SELECT students.id,
students.name,
students.lastname,
addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM students, addresses
WHERE students.id = addresses.st_idIni akan menghasilkan keluaran yang mengekstrak data yang sesuai dari kedua tabel sebagai berikut -
(1, 'Ravi', 'Kapoor', 1, 1, 'Shivajinagar Pune', '[email protected]')
(1, 'Ravi', 'Kapoor', 2, 1, 'ChurchGate Mumbai', '[email protected]')
(3, 'Komal', 'Bhandari', 3, 3, 'Jubilee Hills Hyderabad', '[email protected]')
(5, 'Priya', 'Rajhans', 4, 5, 'MG Road Bangaluru', '[email protected]')
(2, 'Rajiv', 'Khanna', 5, 2, 'Cannought Place new Delhi', '[email protected]')Pada bab sebelumnya, kita telah membahas tentang bagaimana menggunakan banyak tabel. Jadi kami melangkah lebih jauh dan belajarmultiple table updates di bab ini.
Dengan menggunakan objek tabel SQLAlchemy, lebih dari satu tabel dapat ditentukan dalam metode klausa WHERE dari update (). PostgreSQL dan Microsoft SQL Server mendukung pernyataan UPDATE yang merujuk ke beberapa tabel. Ini mengimplementasikan“UPDATE FROM”sintaks, yang memperbarui tabel satu per satu. Namun, tabel tambahan dapat direferensikan di klausa "FROM" tambahan di klausa WHERE secara langsung. Baris kode berikut menjelaskan konsepmultiple table updates jelas.
stmt = students.update().\
values({
students.c.name:'xyz',
addresses.c.email_add:'[email protected]'
}).\
where(students.c.id == addresses.c.id)Objek pembaruan setara dengan kueri UPDATE berikut -
UPDATE students
SET email_add = :addresses_email_add, name = :name
FROM addresses
WHERE students.id = addresses.idSejauh menyangkut dialek MySQL, beberapa tabel dapat disematkan ke dalam satu pernyataan UPDATE yang dipisahkan oleh koma seperti yang diberikan di bawah ini -
stmt = students.update().\
values(name = 'xyz').\
where(students.c.id == addresses.c.id)Kode berikut menggambarkan kueri UPDATE yang dihasilkan -
'UPDATE students SET name = :name
FROM addresses
WHERE students.id = addresses.id'Namun dialek SQLite tidak mendukung kriteria beberapa tabel dalam UPDATE dan menunjukkan kesalahan berikut -
NotImplementedError: This backend does not support multiple-table criteria within UPDATEKueri UPDATE dari SQL mentah memiliki klausa SET. Ini diberikan oleh konstruksi update () menggunakan urutan kolom yang diberikan dalam objek Table asal. Oleh karena itu, pernyataan UPDATE tertentu dengan kolom tertentu akan ditampilkan sama setiap saat. Karena parameternya sendiri diteruskan ke metode Update.values () sebagai kunci kamus Python, tidak ada pengurutan tetap lainnya yang tersedia.
Dalam beberapa kasus, urutan parameter yang dirender dalam klausa SET signifikan. Di MySQL, memberikan pembaruan ke nilai kolom didasarkan pada nilai kolom lainnya.
Berikut hasil pernyataan -
UPDATE table1 SET x = y + 10, y = 20akan memiliki hasil yang berbeda dari -
UPDATE table1 SET y = 20, x = y + 10Klausa SET di MySQL dievaluasi berdasarkan per-nilai dan bukan pada basis per-baris. Untuk tujuan ini, filepreserve_parameter_orderdigunakan. Daftar Python dari 2-tupel diberikan sebagai argumen keUpdate.values() metode -
stmt = table1.update(preserve_parameter_order = True).\
values([(table1.c.y, 20), (table1.c.x, table1.c.y + 10)])Objek List mirip dengan kamus kecuali yang diurutkan. Ini memastikan bahwa klausa SET kolom "y" akan dirender terlebih dahulu, kemudian klausa SET kolom "x".
Pada bab ini, kita akan melihat ekspresi Multiple Table Deletes yang mirip dengan menggunakan fungsi Multiple Table Updates.
Lebih dari satu tabel dapat dirujuk di klausa WHERE dari pernyataan DELETE dalam banyak dialek DBMS. Untuk PG dan MySQL, sintaks “DELETE USING” digunakan; dan untuk SQL Server, menggunakan ekspresi "DELETE FROM" merujuk ke lebih dari satu tabel. SQLAlchemydelete() construct mendukung kedua mode ini secara implisit, dengan menetapkan beberapa tabel dalam klausa WHERE sebagai berikut -
stmt = users.delete().\
where(users.c.id == addresses.c.id).\
where(addresses.c.email_address.startswith('xyz%'))
conn.execute(stmt)Pada backend PostgreSQL, SQL yang dihasilkan dari pernyataan di atas akan dirender sebagai -
DELETE FROM users USING addresses
WHERE users.id = addresses.id
AND (addresses.email_address LIKE %(email_address_1)s || '%%')Jika metode ini digunakan dengan database yang tidak mendukung perilaku ini, compiler akan memunculkan NotImplementedError.
Di bab ini, kita akan mempelajari cara menggunakan Gabungan di SQLAlchemy.
Pengaruh penggabungan dicapai dengan hanya menempatkan dua tabel di file columns clause atau where clausedari konstruksi select (). Sekarang kita menggunakan metode join () dan outerjoin ().
Metode join () mengembalikan objek gabungan dari satu objek tabel ke objek tabel lainnya.
join(right, onclause = None, isouter = False, full = False)Fungsi parameter yang disebutkan dalam kode di atas adalah sebagai berikut -
right- sisi kanan sambungan; ini adalah objek Tabel
onclause- ekspresi SQL yang mewakili klausa ON dari gabungan. Jika dibiarkan di None, ia mencoba untuk menggabungkan dua tabel berdasarkan hubungan kunci asing
isouter - jika True, membuat LEFT OUTER JOIN, bukan JOIN
full - jika True, membuat FULL OUTER JOIN, bukan LEFT OUTER JOIN
Misalnya, mengikuti penggunaan metode join () akan secara otomatis menghasilkan gabungan berdasarkan kunci asing.
>>> print(students.join(addresses))Ini sama dengan mengikuti ekspresi SQL -
students JOIN addresses ON students.id = addresses.st_idAnda dapat secara eksplisit menyebutkan kriteria bergabung sebagai berikut -
j = students.join(addresses, students.c.id == addresses.c.st_id)Jika kita sekarang membangun di bawah pilih konstruksi menggunakan gabungan ini sebagai -
stmt = select([students]).select_from(j)Ini akan menghasilkan ekspresi SQL berikut -
SELECT students.id, students.name, students.lastname
FROM students JOIN addresses ON students.id = addresses.st_idJika pernyataan ini dijalankan menggunakan mesin yang mewakili koneksi, data milik kolom yang dipilih akan ditampilkan. Kode lengkapnya adalah sebagai berikut -
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
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),
)
addresses = Table(
'addresses', meta,
Column('id', Integer, primary_key = True),
Column('st_id', Integer,ForeignKey('students.id')),
Column('postal_add', String),
Column('email_add', String)
)
from sqlalchemy import join
from sqlalchemy.sql import select
j = students.join(addresses, students.c.id == addresses.c.st_id)
stmt = select([students]).select_from(j)
result = conn.execute(stmt)
result.fetchall()Berikut ini adalah keluaran dari kode di atas -
[
(1, 'Ravi', 'Kapoor'),
(1, 'Ravi', 'Kapoor'),
(3, 'Komal', 'Bhandari'),
(5, 'Priya', 'Rajhans'),
(2, 'Rajiv', 'Khanna')
]Konjungsi adalah fungsi dalam modul SQLAlchemy yang mengimplementasikan operator relasional yang digunakan dalam klausa WHERE pada ekspresi SQL. Operator AND, OR, NOT, dll., Digunakan untuk membentuk ekspresi gabungan yang menggabungkan dua ekspresi logika individual. Contoh sederhana penggunaan AND dalam pernyataan SELECT adalah sebagai berikut -
SELECT * from EMPLOYEE WHERE salary>10000 AND age>30Fungsi SQLAlchemy and_ (), or_ () dan not_ () masing-masing mengimplementasikan operator AND, OR dan NOT.
dan_ () fungsi
Ini menghasilkan gabungan ekspresi yang digabungkan dengan AND. Contoh diberikan di bawah ini untuk pemahaman yang lebih baik -
from sqlalchemy import and_
print(
and_(
students.c.name == 'Ravi',
students.c.id <3
)
)Ini diterjemahkan menjadi -
students.name = :name_1 AND students.id < :id_1Untuk menggunakan and_ () dalam konstruksi select () pada tabel siswa, gunakan baris kode berikut -
stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))Pernyataan SELECT dengan sifat berikut akan dibangun -
SELECT students.id,
students.name,
students.lastname
FROM students
WHERE students.name = :name_1 AND students.id < :id_1Kode lengkap yang menampilkan output dari query SELECT di atas adalah sebagai berikut -
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())Baris berikut akan dipilih dengan asumsi bahwa tabel siswa diisi dengan data yang digunakan dalam contoh sebelumnya -
[(1, 'Ravi', 'Kapoor')]or_ () fungsi
Ini menghasilkan konjungsi ekspresi yang digabungkan dengan OR. Kita akan mengganti objek stmt pada contoh di atas dengan yang berikut menggunakan or_ ()
stmt = select([students]).where(or_(students.c.name == 'Ravi', students.c.id <3))Yang secara efektif akan setara dengan mengikuti kueri SELECT -
SELECT students.id,
students.name,
students.lastname
FROM students
WHERE students.name = :name_1
OR students.id < :id_1Setelah Anda melakukan substitusi dan menjalankan kode di atas, hasilnya adalah dua baris yang berada dalam kondisi OR -
[(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Khanna')]asc () fungsi
Ini menghasilkan klausa ORDER BY menaik. Fungsi tersebut mengambil kolom untuk menerapkan fungsi tersebut sebagai parameter.
from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))Pernyataan tersebut mengimplementasikan ekspresi SQL berikut -
SELECT students.id,
students.name,
students.lastname
FROM students
ORDER BY students.name ASCKode berikut mencantumkan semua catatan dalam tabel siswa dalam urutan kolom nama -
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)Kode di atas menghasilkan keluaran sebagai berikut -
(4, 'Abdul', 'Sattar')
(3, 'Komal', 'Bhandari')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')desc () fungsi
Demikian pula fungsi desc () menghasilkan klausa ORDER BY menurun sebagai berikut -
from sqlalchemy import desc
stmt = select([students]).order_by(desc(students.c.lastname))Ekspresi SQL yang setara adalah -
SELECT students.id,
students.name,
students.lastname
FROM students
ORDER BY students.lastname DESCDan output untuk baris kode di atas adalah -
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')
(3, 'Komal', 'Bhandari')antara () fungsi
Ini menghasilkan klausa predikat BETWEEN. Ini umumnya digunakan untuk memvalidasi jika nilai kolom tertentu berada di antara rentang. Misalnya, kode berikut memilih baris dengan kolom id antara 2 dan 4 -
from sqlalchemy import between
stmt = select([students]).where(between(students.c.id,2,4))
print (stmt)Ekspresi SQL yang dihasilkan menyerupai -
SELECT students.id,
students.name,
students.lastname
FROM students
WHERE students.id
BETWEEN :id_1 AND :id_2dan hasilnya adalah sebagai berikut -
(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')Beberapa fungsi penting yang digunakan dalam SQLAlchemy dibahas dalam bab ini.
SQL standar telah merekomendasikan banyak fungsi yang diimplementasikan oleh kebanyakan dialek. Mereka mengembalikan satu nilai berdasarkan argumen yang diteruskan padanya. Beberapa fungsi SQL mengambil kolom sebagai argumen sedangkan beberapa bersifat umum.Thefunc keyword in SQLAlchemy API is used to generate these functions.
Dalam SQL, now () adalah fungsi generik. Pernyataan berikut membuat fungsi now () menggunakan func -
from sqlalchemy.sql import func
result = conn.execute(select([func.now()]))
print (result.fetchone())Contoh hasil kode diatas dapat dilihat pada gambar dibawah -
(datetime.datetime(2018, 6, 16, 6, 4, 40),)Di sisi lain, fungsi count () yang mengembalikan jumlah baris yang dipilih dari tabel, diberikan dengan mengikuti penggunaan func -
from sqlalchemy.sql import func
result = conn.execute(select([func.count(students.c.id)]))
print (result.fetchone())Dari kode diatas akan diambil hitungan baris pada tabel siswa.
Beberapa fungsi SQL bawaan didemonstrasikan menggunakan tabel Karyawan dengan data berikut -
| Indo | Nama | Tanda |
|---|---|---|
| 1 | Kamal | 56 |
| 2 | Fernandez | 85 |
| 3 | Sunil | 62 |
| 4 | Bhaskar | 76 |
Fungsi max () diimplementasikan dengan mengikuti penggunaan func dari SQLAlchemy yang akan menghasilkan 85, total nilai maksimum yang diperoleh -
from sqlalchemy.sql import func
result = conn.execute(select([func.max(employee.c.marks)]))
print (result.fetchone())Demikian pula, fungsi min () yang akan mengembalikan 56, tanda minimum, akan dirender dengan kode berikut -
from sqlalchemy.sql import func
result = conn.execute(select([func.min(employee.c.marks)]))
print (result.fetchone())Jadi, fungsi AVG () juga dapat diimplementasikan dengan menggunakan kode di bawah ini -
from sqlalchemy.sql import func
result = conn.execute(select([func.avg(employee.c.marks)]))
print (result.fetchone())
Functions are normally used in the columns clause of a select statement.
They can also be given label as well as a type. A label to function allows the result
to be targeted in a result row based on a string name, and a type is required when
you need result-set processing to occur.from sqlalchemy.sql import func
result = conn.execute(select([func.max(students.c.lastname).label('Name')]))
print (result.fetchone())Pada bab terakhir, kita telah belajar tentang berbagai fungsi seperti max (), min (), count (), dll., Di sini, kita akan belajar tentang operasi himpunan dan penggunaannya.
Operasi set seperti UNION dan INTERSECT didukung oleh SQL standar dan sebagian besar dialeknya. SQLAlchemy mengimplementasikannya dengan bantuan fungsi berikut -
Persatuan()
Saat menggabungkan hasil dari dua atau lebih pernyataan SELECT, UNION menghilangkan duplikat dari kumpulan hasil. Jumlah kolom dan tipe data harus sama di kedua tabel.
Fungsi union () mengembalikan objek CompoundSelect dari beberapa tabel. Contoh berikut menunjukkan penggunaannya -
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()
addresses = Table(
'addresses', meta,
Column('id', Integer, primary_key = True),
Column('st_id', Integer),
Column('postal_add', String),
Column('email_add', String)
)
u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))))
result = conn.execute(u)
result.fetchall()Konstruksi gabungan diterjemahkan menjadi ekspresi SQL berikut -
SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?Dari tabel alamat kami, baris berikut mewakili operasi serikat -
[
(1, 1, 'Shivajinagar Pune', '[email protected]'),
(2, 1, 'ChurchGate Mumbai', '[email protected]'),
(3, 3, 'Jubilee Hills Hyderabad', '[email protected]'),
(4, 5, 'MG Road Bangaluru', '[email protected]')
]union_all ()
Operasi UNION ALL tidak dapat menghapus duplikat dan tidak dapat mengurutkan data dalam kumpulan hasil. Misalnya, dalam query di atas, UNION diganti dengan UNION ALL untuk melihat efeknya.
u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))Ekspresi SQL yang sesuai adalah sebagai berikut -
SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION ALL SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?kecuali_()
SQL EXCEPTklausa / operator digunakan untuk menggabungkan dua pernyataan SELECT dan mengembalikan baris dari pernyataan SELECT pertama yang tidak dikembalikan oleh pernyataan SELECT kedua. Fungsi exception_ () menghasilkan ekspresi SELECT dengan klausa EXCEPT.
Dalam contoh berikut, fungsi kecuali_ () mengembalikan hanya catatan dari tabel alamat yang memiliki 'gmail.com' di bidang email_add tetapi mengecualikan yang memiliki 'Pune' sebagai bagian dari bidang postal_add.
u = except_(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))Hasil dari kode di atas adalah ekspresi SQL berikut -
SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? EXCEPT SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?Dengan asumsi bahwa tabel alamat berisi data yang digunakan dalam contoh sebelumnya, itu akan menampilkan output berikut -
[(2, 1, 'ChurchGate Mumbai', '[email protected]'),
(3, 3, 'Jubilee Hills Hyderabad', '[email protected]')]memotong()
Menggunakan operator INTERSECT, SQL menampilkan baris umum dari kedua pernyataan SELECT. Fungsi intersect () mengimplementasikan perilaku ini.
Dalam contoh berikut, dua konstruksi SELECT adalah parameter ke fungsi intersect (). Satu baris kembali berisi 'gmail.com' sebagai bagian dari kolom email_add, dan baris hasil lainnya memiliki 'Pune' sebagai bagian dari kolom postal_add. Hasilnya adalah baris umum dari kedua kumpulan hasil.
u = intersect(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))Akibatnya, ini sama dengan mengikuti pernyataan SQL -
SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? INTERSECT SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?Dua parameter terikat '% gmail.com' dan '% Pune' menghasilkan satu baris dari data asli di tabel alamat seperti yang ditunjukkan di bawah ini -
[(1, 1, 'Shivajinagar Pune', '[email protected]')]Tujuan utama API Pemeta Relasional Objek SQLAlchemy adalah untuk memfasilitasi pengaitan kelas Python yang ditentukan pengguna dengan tabel database, dan objek kelas tersebut dengan baris dalam tabel yang sesuai. Perubahan status objek dan baris dicocokkan secara sinkron satu sama lain. SQLAlchemy memungkinkan pengungkapan kueri database dalam hal kelas yang ditentukan pengguna dan hubungan yang ditentukan.
ORM dibangun di atas SQL Expression Language. Ini adalah pola penggunaan tingkat tinggi dan abstrak. Faktanya, ORM adalah penggunaan terapan dari Expression Language.
Meskipun aplikasi yang berhasil dapat dibangun dengan menggunakan Object Relational Mapper secara eksklusif, terkadang aplikasi yang dibangun dengan ORM dapat menggunakan Expression Language secara langsung di mana interaksi database tertentu diperlukan.
Deklarasikan Pemetaan
Pertama-tama, fungsi create_engine () dipanggil untuk menyiapkan objek mesin yang selanjutnya digunakan untuk melakukan operasi SQL. Fungsi ini memiliki dua argumen, satu adalah nama database dan lainnya adalah parameter gema jika disetel ke True akan menghasilkan log aktivitas. Jika tidak ada, database akan dibuat. Dalam contoh berikut, database SQLite dibuat.
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)Engine membuat koneksi DBAPI nyata ke database saat metode seperti Engine.execute () atau Engine.connect () dipanggil. Ini kemudian digunakan untuk memancarkan SQLORM yang tidak menggunakan Mesin secara langsung; sebaliknya, ini digunakan di belakang layar oleh ORM.
Dalam kasus ORM, proses konfigurasi dimulai dengan mendeskripsikan tabel database dan kemudian dengan menentukan kelas yang akan dipetakan ke tabel tersebut. Di SQLAlchemy, kedua tugas ini dilakukan bersama. Ini dilakukan dengan menggunakan sistem Deklaratif; kelas yang dibuat menyertakan arahan untuk mendeskripsikan tabel database aktual tempat mereka dipetakan.
Kelas dasar menyimpan catlog kelas dan tabel yang dipetakan dalam sistem Deklaratif. Ini disebut sebagai kelas dasar deklaratif. Biasanya hanya akan ada satu contoh dari basis ini dalam modul yang biasa diimpor. Fungsi declarative_base () digunakan untuk membuat kelas dasar. Fungsi ini didefinisikan dalam modul sqlalchemy.ext.declarative.
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()Setelah kelas dasar dideklarasikan, sejumlah kelas yang dipetakan dapat didefinisikan dalam istilah itu. Kode berikut mendefinisikan kelas Pelanggan. Ini berisi tabel yang akan dipetakan, dan nama dan tipe data kolom di dalamnya.
class Customers(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)Kelas di Deklaratif harus memiliki __tablename__ atribut, dan setidaknya satu Columnyang merupakan bagian dari kunci utama. Deklaratif menggantikan semua fileColumn objek dengan aksesor Python khusus yang dikenal sebagai descriptors. Proses ini dikenal sebagai instrumentasi yang menyediakan sarana untuk merujuk ke tabel dalam konteks SQL dan memungkinkan mempertahankan dan memuat nilai kolom dari database.
Kelas yang dipetakan ini seperti kelas Python normal memiliki atribut dan metode sesuai kebutuhan.
Informasi tentang kelas dalam sistem Deklaratif disebut sebagai tabel metadata. SQLAlchemy menggunakan objek Tabel untuk mewakili informasi ini untuk tabel tertentu yang dibuat oleh Deklaratif. Objek Tabel dibuat sesuai dengan spesifikasi, dan dikaitkan dengan kelas dengan membangun objek Mapper. Objek mapper ini tidak langsung digunakan tetapi digunakan secara internal sebagai antarmuka antara kelas yang dipetakan dan tabel.
Setiap objek Tabel adalah anggota dari koleksi yang lebih besar yang dikenal sebagai MetaData dan objek ini tersedia menggunakan .metadataatribut kelas dasar deklaratif. ItuMetaData.create_all()metode ini, meneruskan Engine kami sebagai sumber konektivitas database. Untuk semua tabel yang belum dibuat, ini mengeluarkan pernyataan CREATE TABLE ke database.
Base.metadata.create_all(engine)Skrip lengkap untuk membuat database dan tabel, dan untuk memetakan kelas Python diberikan di bawah ini -
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Customers(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key=True)
name = Column(String)
address = Column(String)
email = Column(String)
Base.metadata.create_all(engine)Saat dijalankan, konsol Python akan bergema setelah ekspresi SQL dijalankan -
CREATE TABLE customers (
id INTEGER NOT NULL,
name VARCHAR,
address VARCHAR,
email VARCHAR,
PRIMARY KEY (id)
)Jika kita membuka Sales.db menggunakan alat grafik SQLiteStudio, ini menunjukkan tabel pelanggan di dalamnya dengan struktur yang disebutkan di atas.
Untuk berinteraksi dengan database, kita perlu mendapatkan pegangannya. Objek sesi adalah pegangan ke database. Kelas sesi didefinisikan menggunakan sessionmaker () - metode pabrik sesi yang dapat dikonfigurasi yang terikat ke objek mesin yang dibuat sebelumnya.
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)Objek sesi kemudian disiapkan menggunakan konstruktor defaultnya sebagai berikut -
session = Session()Beberapa metode kelas sesi yang sering dibutuhkan tercantum di bawah ini -
| Sr.No. | Metode & Deskripsi |
|---|---|
| 1 | begin() memulai transaksi pada sesi ini |
| 2 | add() menempatkan objek dalam sesi. Statusnya dipertahankan dalam database pada operasi pembilasan berikutnya |
| 3 | add_all() menambahkan koleksi objek ke sesi |
| 4 | commit() membersihkan semua item dan transaksi apa pun yang sedang berlangsung |
| 5 | delete() menandai transaksi sebagai telah dihapus |
| 6 | execute() mengeksekusi ekspresi SQL |
| 7 | expire() menandai atribut sebuah instance sebagai kedaluwarsa |
| 8 | flush() menghapus semua perubahan objek ke database |
| 9 | invalidate() menutup sesi menggunakan pembatalan koneksi |
| 10 | rollback() memutar kembali transaksi yang sedang berlangsung |
| 11 | close() Menutup sesi saat ini dengan menghapus semua item dan mengakhiri transaksi yang sedang berlangsung |
Di bab sebelumnya dari SQLAlchemy ORM, kita telah mempelajari cara mendeklarasikan pemetaan dan membuat sesi. Dalam bab ini, kita akan belajar cara menambahkan objek ke tabel.
Kami telah mendeklarasikan kelas Pelanggan yang telah dipetakan ke tabel pelanggan. Kita harus mendeklarasikan sebuah objek dari kelas ini dan terus menambahkannya ke tabel dengan metode add () dari objek sesi.
c1 = Sales(name = 'Ravi Kumar', address = 'Station Road Nanded', email = '[email protected]')
session.add(c1)Perhatikan bahwa transaksi ini ditunda sampai hal yang sama dihapus menggunakan metode commit ().
session.commit()Berikut ini adalah skrip lengkap untuk menambahkan catatan di tabel pelanggan -
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Customers(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key=True)
name = Column(String)
address = Column(String)
email = Column(String)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
c1 = Customers(name = 'Ravi Kumar', address = 'Station Road Nanded', email = '[email protected]')
session.add(c1)
session.commit()Untuk menambahkan beberapa record, kita bisa menggunakan add_all() metode kelas sesi.
session.add_all([
Customers(name = 'Komal Pande', address = 'Koti, Hyderabad', email = '[email protected]'),
Customers(name = 'Rajender Nath', address = 'Sector 40, Gurgaon', email = '[email protected]'),
Customers(name = 'S.M.Krishna', address = 'Budhwar Peth, Pune', email = '[email protected]')]
)
session.commit()Tampilan tabel SQLiteStudio menunjukkan bahwa catatan terus ditambahkan dalam tabel pelanggan. Gambar berikut menunjukkan hasilnya -
Semua pernyataan SELECT yang dibuat oleh SQLAlchemy ORM dibuat oleh objek Query. Ini menyediakan antarmuka generatif, oleh karena itu panggilan berturut-turut mengembalikan objek Query baru, salinan sebelumnya dengan kriteria dan opsi tambahan yang terkait dengannya.
Objek kueri awalnya dibuat menggunakan metode query () dari Sesi sebagai berikut -
q = session.query(mapped class)Pernyataan berikut juga setara dengan pernyataan yang diberikan di atas -
q = Query(mappedClass, session)Objek kueri memiliki metode all () yang mengembalikan kumpulan hasil dalam bentuk daftar objek. Jika kami menjalankannya di meja pelanggan kami -
result = session.query(Customers).all()Pernyataan ini secara efektif setara dengan mengikuti ekspresi SQL -
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customersObjek hasil dapat ditelusuri menggunakan perulangan For seperti di bawah ini untuk mendapatkan semua rekaman dalam tabel pelanggan yang mendasarinya. Berikut adalah kode lengkap untuk menampilkan semua catatan di tabel Pelanggan -
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Customers(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).all()
for row in result:
print ("Name: ",row.name, "Address:",row.address, "Email:",row.email)Konsol Python menampilkan daftar record seperti di bawah ini -
Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
Name: Komal Pande Address: Koti, Hyderabad Email: [email protected]
Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]Objek Query juga memiliki metode berguna berikut -
| Sr.No. | Metode & Deskripsi |
|---|---|
| 1 | add_columns() Ini menambahkan satu atau lebih ekspresi kolom ke daftar kolom hasil yang akan dikembalikan. |
| 2 | add_entity() Ini menambahkan entitas yang dipetakan ke daftar kolom hasil yang akan dikembalikan. |
| 3 | count() Ini mengembalikan hitungan baris yang akan dikembalikan Kueri ini. |
| 4 | delete() Itu melakukan kueri penghapusan massal. Menghapus baris yang cocok dengan kueri ini dari database. |
| 5 | distinct() Ini menerapkan klausa DISTINCT ke kueri dan mengembalikan Kueri yang baru dihasilkan. |
| 6 | filter() Ini menerapkan kriteria pemfilteran yang diberikan ke salinan Kueri ini, menggunakan ekspresi SQL. |
| 7 | first() Ini mengembalikan hasil pertama dari Query ini atau None jika hasilnya tidak berisi baris apa pun. |
| 8 | get() Ini mengembalikan sebuah instance berdasarkan pengenal kunci utama yang diberikan yang memberikan akses langsung ke peta identitas dari Sesi yang memiliki. |
| 9 | group_by() Ini menerapkan satu atau lebih kriteria GROUP BY ke kueri dan mengembalikan Kueri yang baru dihasilkan |
| 10 | join() Ini membuat SQL JOIN terhadap kriteria objek Query ini dan menerapkan secara umum, mengembalikan Query yang baru dihasilkan. |
| 11 | one() Ini mengembalikan tepat satu hasil atau memunculkan pengecualian. |
| 12 | order_by() Ini menerapkan satu atau beberapa kriteria ORDER BY ke kueri dan mengembalikan Kueri yang baru dihasilkan. |
| 13 | update() Itu melakukan kueri pembaruan massal dan memperbarui baris yang cocok dengan kueri ini di database. |
Pada bab ini, kita akan melihat bagaimana mengubah atau memperbarui tabel dengan nilai yang diinginkan.
Untuk mengubah data atribut tertentu dari objek apa pun, kita harus memberikan nilai baru padanya dan melakukan perubahan untuk membuat perubahan tetap ada.
Mari kita ambil objek dari tabel yang pengenal kunci utamanya, di tabel Pelanggan dengan ID = 2. Kita bisa menggunakan metode get () sesi sebagai berikut -
x = session.query(Customers).get(2)Kami dapat menampilkan konten dari objek yang dipilih dengan kode yang diberikan di bawah ini -
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)Dari tabel pelanggan kami, output berikut harus ditampilkan -
Name: Komal Pande Address: Koti, Hyderabad Email: [email protected]Sekarang kita perlu memperbarui bidang Alamat dengan memberikan nilai baru seperti yang diberikan di bawah ini -
x.address = 'Banjara Hills Secunderabad'
session.commit()Perubahan tersebut akan terus ditampilkan dalam database. Sekarang kita mengambil objek yang sesuai dengan baris pertama dalam tabel dengan menggunakanfirst() method sebagai berikut -
x = session.query(Customers).first()Ini akan mengeksekusi 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
LIMIT ? OFFSET ?Parameter yang terikat adalah LIMIT = 1 dan OFFSET = 0 masing-masing yang berarti baris pertama akan dipilih.
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)Sekarang, output untuk kode di atas yang menampilkan baris pertama adalah sebagai berikut -
Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]Sekarang ubah atribut nama dan tampilkan isinya menggunakan kode di bawah ini -
x.name = 'Ravi Shrivastava'
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)Output dari kode diatas adalah -
Name: Ravi Shrivastava Address: Station Road Nanded Email: [email protected]Meskipun perubahan ditampilkan, itu tidak dilakukan. Anda dapat mempertahankan posisi persisten sebelumnya dengan menggunakanrollback() method dengan kode di bawah ini.
session.rollback()
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)Isi asli dari rekaman pertama akan ditampilkan.
Untuk pembaruan massal, kami akan menggunakan metode update () dari objek Query. Mari kita coba dan beri awalan, 'Mr.' untuk memberi nama di setiap baris (kecuali ID = 2). Pernyataan update () yang sesuai adalah sebagai berikut -
session.query(Customers).filter(Customers.id! = 2).
update({Customers.name:"Mr."+Customers.name}, synchronize_session = False)The update() method requires two parameters as follows −
Kamus nilai-kunci dengan kunci menjadi atribut yang akan diperbarui, dan nilai menjadi konten baru dari atribut.
sync_session atribut yang menyebutkan strategi untuk memperbarui atribut dalam sesi. Nilai yang valid adalah salah: untuk tidak menyinkronkan sesi, fetch: melakukan kueri pemilihan sebelum pembaruan untuk menemukan objek yang cocok dengan kueri pembaruan; dan evaluasi: evaluasi kriteria pada objek dalam sesi.
Tiga dari 4 baris pada tabel akan memiliki nama yang diawali dengan 'Mr.' Namun, perubahan tidak dilakukan dan karenanya tidak akan tercermin dalam tampilan tabel SQLiteStudio. Ini akan disegarkan hanya ketika kita menjalankan sesi.
Pada bab ini, kita akan membahas bagaimana menerapkan filter dan juga operasi filter tertentu beserta kodenya.
Resultset yang direpresentasikan oleh objek Query dapat dikenai kriteria tertentu dengan menggunakan metode filter (). Penggunaan umum metode filter adalah sebagai berikut -
session.query(class).filter(criteria)Dalam contoh berikut, kumpulan hasil yang diperoleh dengan kueri SELECT pada tabel Pelanggan difilter oleh kondisi, (ID> 2) -
result = session.query(Customers).filter(Customers.id>2)Pernyataan ini akan diterjemahkan ke dalam 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 > ?Karena parameter terikat (?) Diberikan sebagai 2, hanya baris dengan kolom ID> 2 yang akan ditampilkan. Kode lengkap diberikan di bawah ini -
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Customers(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
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)Output yang ditampilkan di konsol Python adalah sebagai berikut -
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]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]Ada sejumlah metode objek Query yang segera mengeluarkan SQL dan mengembalikan nilai yang berisi hasil database yang dimuat.
Berikut adalah ikhtisar singkat daftar kembali dan skalar -
semua()
Ini mengembalikan daftar. Diberikan di bawah ini adalah baris kode untuk fungsi all ().
session.query(Customers).all()Konsol Python menampilkan ekspresi SQL berikut yang dipancarkan -
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customerspertama()
Ini menerapkan batas satu dan mengembalikan hasil pertama sebagai skalar.
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
LIMIT ? OFFSET ?Parameter terikat untuk LIMIT adalah 1 dan untuk OFFSET adalah 0.
satu()
Perintah ini sepenuhnya mengambil semua baris, dan jika tidak ada persis satu identitas objek atau baris gabungan yang ada dalam hasil, ini menimbulkan kesalahan.
session.query(Customers).one()Dengan beberapa baris ditemukan -
MultipleResultsFound: Multiple rows were found for one()Tanpa baris yang ditemukan -
NoResultFound: No row was found for one()Metode one () berguna untuk sistem yang berharap menangani "tidak ada item yang ditemukan" versus "beberapa item yang ditemukan" secara berbeda.
skalar ()
Ini memanggil metode one (), dan setelah berhasil mengembalikan kolom pertama dari baris sebagai berikut -
session.query(Customers).filter(Customers.id == 3).scalar()Ini menghasilkan pernyataan 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 = ?Sebelumnya, teks SQL menggunakan fungsi text () telah dijelaskan dari perspektif bahasa ekspresi inti SQLAlchemy. Sekarang kita akan membahasnya dari sudut pandang ORM.
String literal dapat digunakan secara fleksibel dengan objek Query dengan menentukan penggunaannya dengan konstruksi text (). Sebagian besar metode yang dapat diterapkan menerimanya. Misalnya, filter () dan order_by ().
Dalam contoh yang diberikan di bawah ini, metode filter () menerjemahkan string "id <3" ke WHERE id <3
from sqlalchemy import text
for cust in session.query(Customers).filter(text("id<3")):
print(cust.name)Ekspresi SQL mentah yang dihasilkan menunjukkan konversi filter ke klausa WHERE dengan kode yang diilustrasikan di bawah ini -
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE id<3Dari data sampel kami di tabel Pelanggan, dua baris akan dipilih dan kolom nama akan dicetak sebagai berikut -
Ravi Kumar
Komal PandeUntuk menentukan parameter mengikat dengan SQL berbasis string, gunakan titik dua, dan untuk menentukan nilai, gunakan metode params ().
cust = session.query(Customers).filter(text("id = :value")).params(value = 1).one()SQL efektif yang ditampilkan di konsol Python akan seperti yang diberikan di bawah ini -
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE id = ?Untuk menggunakan pernyataan berbasis string sepenuhnya, konstruksi text () yang mewakili pernyataan lengkap dapat diteruskan ke from_statement ().
session.query(Customers).from_statement(text("SELECT * FROM customers")).all()Hasil dari kode di atas akan menjadi pernyataan SELECT dasar seperti yang diberikan di bawah ini -
SELECT * FROM customersJelas, semua catatan di tabel pelanggan akan dipilih.
Konstruksi text () memungkinkan kita untuk menautkan SQL tekstualnya ke ekspresi kolom yang dipetakan oleh Core atau ORM secara posisional. Kita bisa mencapai ini dengan meneruskan ekspresi kolom sebagai argumen posisi ke metode TextClause.columns ().
stmt = text("SELECT name, id, name, address, email FROM customers")
stmt = stmt.columns(Customers.id, Customers.name)
session.query(Customers.id, Customers.name).from_statement(stmt).all()Kolom id dan nama semua baris akan dipilih meskipun mesin SQLite mengeksekusi ekspresi berikut yang dihasilkan oleh kode di atas menunjukkan semua kolom dalam metode text () -
SELECT name, id, name, address, email FROM customersSesi ini menjelaskan pembuatan tabel lain yang terkait dengan yang sudah ada di database kita. Tabel pelanggan berisi data master pelanggan. Sekarang kita perlu membuat tabel faktur yang mungkin memiliki sejumlah faktur milik pelanggan. Ini adalah kasus hubungan satu ke banyak.
Menggunakan deklaratif, kami mendefinisikan tabel ini bersama dengan kelas yang dipetakan, Faktur seperti yang diberikan di bawah ini -
from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship
class Customer(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)
class Invoice(Base):
__tablename__ = 'invoices'
id = Column(Integer, primary_key = True)
custid = Column(Integer, ForeignKey('customers.id'))
invno = Column(Integer)
amount = Column(Integer)
customer = relationship("Customer", back_populates = "invoices")
Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")
Base.metadata.create_all(engine)Ini akan mengirim kueri CREATE TABLE ke mesin SQLite seperti di bawah ini -
CREATE TABLE invoices (
id INTEGER NOT NULL,
custid INTEGER,
invno INTEGER,
amount INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(custid) REFERENCES customers (id)
)Kami dapat memeriksa bahwa tabel baru dibuat di sales.db dengan bantuan alat SQLiteStudio.
Kelas faktur menerapkan konstruksi ForeignKey pada atribut custid. Petunjuk ini menunjukkan bahwa nilai di kolom ini harus dibatasi menjadi nilai yang ada di kolom id di tabel pelanggan. Ini adalah fitur inti dari database relasional, dan merupakan "perekat" yang mengubah kumpulan tabel yang tidak terhubung menjadi memiliki hubungan yang kaya tumpang tindih.
Direktif kedua, yang dikenal sebagai hubungan (), memberi tahu ORM bahwa kelas Faktur harus ditautkan ke kelas Pelanggan menggunakan atribut Invoice.customer. Relasi () menggunakan relasi kunci asing antara dua tabel untuk menentukan sifat keterkaitan ini, menentukan bahwa itu banyak berbanding satu.
Direktif hubungan tambahan () ditempatkan pada kelas yang dipetakan Pelanggan di bawah atribut Customer.invoices. Parameter relasi.back_populates ditetapkan untuk merujuk ke nama atribut pelengkap, sehingga setiap relasi () dapat membuat keputusan cerdas tentang relasi yang sama seperti yang diekspresikan secara terbalik. Di satu sisi, Faktur.customer mengacu pada contoh Faktur, dan di sisi lain, Faktur Pelanggan mengacu pada daftar contoh Pelanggan.
Fungsi relasi adalah bagian dari API Relasi dari paket SQLAlchemy ORM. Ini menyediakan hubungan antara dua kelas yang dipetakan. Ini terkait dengan hubungan tabel induk-anak atau asosiatif.
Berikut adalah Pola Hubungan dasar yang ditemukan -
Satu Untuk Banyak
Hubungan One to Many mengacu pada induk dengan bantuan kunci asing pada tabel anak. hubungan () kemudian ditentukan pada induk, sebagai referensi kumpulan item yang diwakili oleh anak. Parameter relationship.back_populates digunakan untuk membuat hubungan dua arah dalam satu-ke-banyak, dengan sisi "kebalikan" adalah banyak ke satu.
Banyak Untuk Satu
Di sisi lain, hubungan Many to One menempatkan kunci asing di tabel induk untuk merujuk ke anak. hubungan () dideklarasikan pada induk, di mana atribut penahan skalar baru akan dibuat. Di sini sekali lagi parameter relationship.back_populates digunakan untuk Bidirectionalbehaviour.
One To One
Hubungan One To One pada dasarnya adalah hubungan dua arah. Bendera daftar pengguna menunjukkan penempatan atribut skalar alih-alih kumpulan di sisi "banyak" dari hubungan. Untuk mengonversi satu-ke-banyak menjadi jenis relasi satu-ke-satu, setel parameter uselist ke false.
Banyak ke banyak
Hubungan Banyak ke Banyak dibuat dengan menambahkan tabel asosiasi yang terkait dengan dua kelas dengan mendefinisikan atribut dengan kunci asingnya. Ini ditunjukkan oleh argumen sekunder ke relationship (). Biasanya, Tabel menggunakan objek MetaData yang terkait dengan kelas dasar deklaratif, sehingga direktif ForeignKey dapat menemukan tabel jarak jauh yang akan ditautkan. Parameter relationship.back_populates untuk setiap hubungan () membentuk hubungan dua arah. Kedua sisi hubungan itu berisi kumpulan.
Dalam bab ini, kita akan fokus pada objek terkait di SQLAlchemy ORM.
Sekarang ketika kita membuat objek Pelanggan, kumpulan faktur kosong akan hadir dalam bentuk Daftar Python.
c1 = Customer(name = "Gopal Krishna", address = "Bank Street Hydarebad", email = "[email protected]")Atribut faktur c1.invoices akan menjadi daftar kosong. Kami dapat menetapkan item dalam daftar sebagai -
c1.invoices = [Invoice(invno = 10, amount = 15000), Invoice(invno = 14, amount = 3850)]Mari kita komit objek ini ke database menggunakan objek Session sebagai berikut -
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
session.add(c1)
session.commit()Ini secara otomatis akan menghasilkan kueri INSERT untuk pelanggan dan tabel faktur -
INSERT INTO customers (name, address, email) VALUES (?, ?, ?)
('Gopal Krishna', 'Bank Street Hydarebad', '[email protected]')
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 10, 15000)
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 14, 3850)Sekarang mari kita lihat isi tabel pelanggan dan tabel faktur dalam tampilan tabel SQLiteStudio -
Anda dapat membuat objek Pelanggan dengan menyediakan atribut faktur yang dipetakan di konstruktor itu sendiri dengan menggunakan perintah di bawah ini -
c2 = [
Customer(
name = "Govind Pant",
address = "Gulmandi Aurangabad",
email = "[email protected]",
invoices = [Invoice(invno = 3, amount = 10000),
Invoice(invno = 4, amount = 5000)]
)
]Atau daftar objek yang akan ditambahkan menggunakan fungsi add_all () dari objek sesi seperti yang ditunjukkan di bawah ini -
rows = [
Customer(
name = "Govind Kala",
address = "Gulmandi Aurangabad",
email = "[email protected]",
invoices = [Invoice(invno = 7, amount = 12000), Invoice(invno = 8, amount = 18500)]),
Customer(
name = "Abdul Rahman",
address = "Rohtak",
email = "[email protected]",
invoices = [Invoice(invno = 9, amount = 15000),
Invoice(invno = 11, amount = 6000)
])
]
session.add_all(rows)
session.commit()Sekarang kita memiliki dua tabel, kita akan melihat bagaimana membuat query pada kedua tabel secara bersamaan. Untuk membuat gabungan implisit sederhana antara Pelanggan dan Faktur, kita bisa menggunakan Query.filter () untuk menyamakan kolom terkait. Di bawah ini, kami memuat entitas Pelanggan dan Faktur sekaligus menggunakan metode ini -
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
for c, i in session.query(Customer, Invoice).filter(Customer.id == Invoice.custid).all():
print ("ID: {} Name: {} Invoice No: {} Amount: {}".format(c.id,c.name, i.invno, i.amount))Ekspresi SQL yang dipancarkan oleh SQLAlchemy adalah sebagai berikut -
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email, invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM customers, invoices
WHERE customers.id = invoices.custidDan hasil dari baris kode diatas adalah sebagai berikut -
ID: 2 Name: Gopal Krishna Invoice No: 10 Amount: 15000
ID: 2 Name: Gopal Krishna Invoice No: 14 Amount: 3850
ID: 3 Name: Govind Pant Invoice No: 3 Amount: 10000
ID: 3 Name: Govind Pant Invoice No: 4 Amount: 5000
ID: 4 Name: Govind Kala Invoice No: 7 Amount: 12000
ID: 4 Name: Govind Kala Invoice No: 8 Amount: 8500
ID: 5 Name: Abdul Rahman Invoice No: 9 Amount: 15000
ID: 5 Name: Abdul Rahman Invoice No: 11 Amount: 6000Sintaks SQL JOIN yang sebenarnya mudah dicapai menggunakan metode Query.join () sebagai berikut -
session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()Ekspresi SQL untuk join akan ditampilkan di konsol -
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers JOIN invoices ON customers.id = invoices.custid
WHERE invoices.amount = ?Kita dapat melakukan iterasi melalui hasil menggunakan for loop -
result = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500)
for row in result:
for inv in row.invoices:
print (row.id, row.name, inv.invno, inv.amount)Dengan 8500 sebagai parameter bind, output berikut ditampilkan -
4 Govind Kala 8 8500Query.join () tahu bagaimana menggabungkan antara tabel-tabel ini karena hanya ada satu kunci asing di antara mereka. Jika tidak ada kunci asing, atau lebih banyak kunci asing, Query.join () bekerja lebih baik ketika salah satu dari formulir berikut digunakan -
| query.join (Faktur, id == Alamat.custid) | kondisi eksplisit |
| query.join (Customer.invoices) | tentukan hubungan dari kiri ke kanan |
| query.join (Invoice, Customer.invoices) | sama, dengan target eksplisit |
| query.join ('invoices') | sama, menggunakan string |
Demikian pula fungsi outerjoin () tersedia untuk mencapai gabungan luar kiri.
query.outerjoin(Customer.invoices)Metode subquery () menghasilkan ekspresi SQL yang mewakili pernyataan SELECT yang disematkan di dalam alias.
from sqlalchemy.sql import func
stmt = session.query(
Invoice.custid, func.count('*').label('invoice_count')
).group_by(Invoice.custid).subquery()Objek stmt akan berisi pernyataan SQL seperti di bawah ini -
SELECT invoices.custid, count(:count_1) AS invoice_count FROM invoices GROUP BY invoices.custidSetelah kita memiliki pernyataan kita, itu berperilaku seperti konstruksi Tabel. Kolom pada pernyataan dapat diakses melalui atribut yang disebut c seperti yang ditunjukkan pada kode di bawah ini -
for u, count in session.query(Customer, stmt.c.invoice_count).outerjoin(stmt, Customer.id == stmt.c.custid).order_by(Customer.id):
print(u.name, count)Perulangan for di atas menampilkan jumlah faktur berdasarkan nama sebagai berikut -
Arjun Pandit None
Gopal Krishna 2
Govind Pant 2
Govind Kala 2
Abdul Rahman 2Pada bab ini, kita akan membahas tentang operator yang membangun relasi.
__eq __ ()
Operator di atas adalah perbandingan banyak-ke-satu “sama dengan”. Baris kode untuk operator ini adalah seperti di bawah ini -
s = session.query(Customer).filter(Invoice.invno.__eq__(12))Kueri SQL yang setara untuk baris kode di atas adalah -
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers, invoices
WHERE invoices.invno = ?__ne __ ()
Operator ini adalah perbandingan banyak-ke-satu "tidak sama". Baris kode untuk operator ini adalah seperti di bawah ini -
s = session.query(Customer).filter(Invoice.custid.__ne__(2))Kueri SQL yang setara untuk baris kode di atas diberikan di bawah ini -
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers, invoices
WHERE invoices.custid != ?mengandung()
Operator ini digunakan untuk koleksi satu-ke-banyak dan diberikan di bawah ini adalah kode untuk contains () -
s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5]))Kueri SQL yang setara untuk baris kode di atas adalah -
SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE (invoices.invno LIKE '%' + ? || '%')apa saja()
any () operator digunakan untuk koleksi seperti yang ditunjukkan di bawah ini -
s = session.query(Customer).filter(Customer.invoices.any(Invoice.invno==11))Kueri SQL yang setara untuk baris kode di atas ditunjukkan di bawah ini -
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE EXISTS (
SELECT 1
FROM invoices
WHERE customers.id = invoices.custid
AND invoices.invno = ?)memiliki ()
Operator ini digunakan untuk referensi skalar sebagai berikut -
s = session.query(Invoice).filter(Invoice.customer.has(name = 'Arjun Pandit'))Kueri SQL yang setara untuk baris kode di atas adalah -
SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE EXISTS (
SELECT 1
FROM customers
WHERE customers.id = invoices.custid
AND customers.name = ?)Beban bersemangat mengurangi jumlah kueri. SQLAlchemy menawarkan fungsi eager loading yang dipanggil melalui opsi kueri yang memberikan instruksi tambahan ke Kueri. Opsi ini menentukan cara memuat berbagai atribut melalui metode Query.options ().
Beban Subkueri
Kami ingin Customer.invoices dimuat dengan penuh semangat. Opsi orm.subqueryload () memberikan pernyataan SELECT kedua yang memuat sepenuhnya koleksi yang terkait dengan hasil yang baru saja dimuat. Nama "subquery" menyebabkan pernyataan SELECT dibangun secara langsung melalui Query yang digunakan kembali dan disematkan sebagai subquery ke dalam SELECT terhadap tabel terkait.
from sqlalchemy.orm import subqueryload
c1 = session.query(Customer).options(subqueryload(Customer.invoices)).filter_by(name = 'Govind Pant').one()Ini menghasilkan dua 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 = ?
('Govind Pant',)
SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount, anon_1.customers_id
AS anon_1_customers_id
FROM (
SELECT customers.id
AS customers_id
FROM customers
WHERE customers.name = ?)
AS anon_1
JOIN invoices
ON anon_1.customers_id = invoices.custid
ORDER BY anon_1.customers_id, invoices.id 2018-06-25 18:24:47,479
INFO sqlalchemy.engine.base.Engine ('Govind Pant',)Untuk mengakses data dari dua tabel, kita dapat menggunakan program di bawah ini -
print (c1.name, c1.address, c1.email)
for x in c1.invoices:
print ("Invoice no : {}, Amount : {}".format(x.invno, x.amount))Output dari program di atas adalah sebagai berikut -
Govind Pant Gulmandi Aurangabad [email protected]
Invoice no : 3, Amount : 10000
Invoice no : 4, Amount : 5000Beban Bergabung
Fungsi lainnya disebut orm.joinedload (). Ini memancarkan LEFT OUTER JOIN. Objek utama serta objek atau koleksi terkait dimuat dalam satu langkah.
from sqlalchemy.orm import joinedload
c1 = session.query(Customer).options(joinedload(Customer.invoices)).filter_by(name='Govind Pant').one()Ini memancarkan ekspresi berikut yang memberikan keluaran yang sama seperti di atas -
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email, invoices_1.id
AS invoices_1_id, invoices_1.custid
AS invoices_1_custid, invoices_1.invno
AS invoices_1_invno, invoices_1.amount
AS invoices_1_amount
FROM customers
LEFT OUTER JOIN invoices
AS invoices_1
ON customers.id = invoices_1.custid
WHERE customers.name = ? ORDER BY invoices_1.id
('Govind Pant',)OUTER JOIN menghasilkan dua baris, tetapi memberikan satu contoh Pelanggan kembali. Ini karena Query menerapkan strategi "unik", berdasarkan identitas objek, ke entitas yang dikembalikan. Eager loading yang digabungkan dapat diterapkan tanpa memengaruhi hasil kueri.
Subqueryload () lebih sesuai untuk memuat koleksi terkait sementara joinload () lebih cocok untuk hubungan banyak-ke-satu.
Mudah untuk melakukan operasi hapus pada satu tabel. Yang harus Anda lakukan adalah menghapus objek kelas yang dipetakan dari sesi dan melakukan tindakan. Namun, operasi hapus pada beberapa tabel terkait sedikit rumit.
Dalam database sales.db kami, kelas Pelanggan dan Faktur dipetakan ke tabel pelanggan dan faktur dengan satu jenis hubungan ke banyak. Kami akan mencoba untuk menghapus objek Pelanggan dan melihat hasilnya.
Sebagai referensi cepat, berikut adalah definisi kelas Pelanggan dan Faktur -
from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship
class Customer(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)
class Invoice(Base):
__tablename__ = 'invoices'
id = Column(Integer, primary_key = True)
custid = Column(Integer, ForeignKey('customers.id'))
invno = Column(Integer)
amount = Column(Integer)
customer = relationship("Customer", back_populates = "invoices")
Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")Kami menyiapkan sesi dan mendapatkan objek Pelanggan dengan menanyakannya dengan ID utama menggunakan program di bawah ini -
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
x = session.query(Customer).get(2)Dalam tabel contoh kami, x.name kebetulan adalah 'Gopal Krishna'. Mari kita hapus x ini dari sesi dan hitung kemunculan nama ini.
session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()Ekspresi SQL yang dihasilkan akan mengembalikan 0.
SELECT count(*)
AS count_1
FROM (
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 = ?)
AS anon_1('Gopal Krishna',) 0Namun, objek Faktur terkait x masih ada. Itu dapat diverifikasi dengan kode berikut -
session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()Di sini, 10 dan 14 adalah nomor faktur milik pelanggan Gopal Krishna. Hasil query di atas adalah 2 yang berarti obyek terkait belum dihapus.
SELECT count(*)
AS count_1
FROM (
SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE invoices.invno IN (?, ?))
AS anon_1(10, 14) 2Ini karena SQLAlchemy tidak mengasumsikan penghapusan cascade; kita harus memberi perintah untuk menghapusnya.
Untuk mengubah perilaku, kita mengkonfigurasi opsi bertingkat pada hubungan User.addresses. Mari kita tutup sesi yang sedang berjalan, gunakan declarative_base () baru dan deklarasikan ulang kelas User, tambahkan dalam hubungan alamat termasuk konfigurasi cascade.
Atribut kaskade dalam fungsi hubungan adalah daftar aturan kaskade yang dipisahkan koma yang menentukan bagaimana operasi Sesi harus "bertingkat" dari induk ke turunan. Secara default, ini adalah False, yang berarti "simpan-perbarui, gabungkan".
Kaskade yang tersedia adalah sebagai berikut -
- save-update
- merge
- expunge
- delete
- delete-orphan
- refresh-expire
Opsi yang sering digunakan adalah "semua, hapus-orphan" untuk menunjukkan bahwa objek terkait harus mengikuti objek induk dalam semua kasus, dan dihapus saat tidak terkait.
Karenanya kelas Pelanggan yang dideklarasikan ulang ditunjukkan di bawah ini -
class Customer(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)
invoices = relationship(
"Invoice",
order_by = Invoice.id,
back_populates = "customer",
cascade = "all,
delete, delete-orphan"
)Mari kita hapus Pelanggan dengan nama Gopal Krishna menggunakan program di bawah ini dan melihat hitungan objek Faktur terkait -
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
x = session.query(Customer).get(2)
session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()
session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()Hitungannya sekarang 0 dengan mengikuti SQL yang dipancarkan oleh skrip di atas -
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 = ?
(2,)
SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE ? = invoices.custid
ORDER BY invoices.id (2,)
DELETE FROM invoices
WHERE invoices.id = ? ((1,), (2,))
DELETE FROM customers
WHERE customers.id = ? (2,)
SELECT count(*)
AS count_1
FROM (
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 = ?)
AS anon_1('Gopal Krishna',)
SELECT count(*)
AS count_1
FROM (
SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE invoices.invno IN (?, ?))
AS anon_1(10, 14)
0Many to Many relationshipantara dua tabel dicapai dengan menambahkan tabel asosiasi sehingga memiliki dua kunci asing - satu dari kunci primer setiap tabel. Selain itu, pemetaan kelas ke dua tabel memiliki atribut dengan kumpulan objek tabel asosiasi lain yang ditetapkan sebagai atribut sekunder dari fungsi relationship ().
Untuk tujuan ini, kita akan membuat database SQLite (mycollege.db) dengan dua tabel - departemen dan karyawan. Di sini, kami berasumsi bahwa seorang karyawan adalah bagian dari lebih dari satu departemen, dan sebuah departemen memiliki lebih dari satu karyawan. Ini merupakan hubungan banyak-ke-banyak.
Definisi kelas Karyawan dan Departemen yang dipetakan ke tabel departemen dan karyawan adalah sebagai berikut -
from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///mycollege.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship
class Department(Base):
__tablename__ = 'department'
id = Column(Integer, primary_key = True)
name = Column(String)
employees = relationship('Employee', secondary = 'link')
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key = True)
name = Column(String)
departments = relationship(Department,secondary='link')Kami sekarang mendefinisikan kelas Link. Ini ditautkan ke tabel tautan dan berisi atribut department_id dan employee_id yang masing-masing merujuk ke kunci utama tabel departemen dan karyawan.
class Link(Base):
__tablename__ = 'link'
department_id = Column(
Integer,
ForeignKey('department.id'),
primary_key = True)
employee_id = Column(
Integer,
ForeignKey('employee.id'),
primary_key = True)Di sini, perlu diperhatikan bahwa kelas Departemen memiliki atribut karyawan yang terkait dengan kelas Karyawan. Atribut sekunder fungsi hubungan diberi tautan sebagai nilainya.
Demikian pula, kelas Karyawan memiliki atribut departemen yang terkait dengan kelas Departemen. Atribut sekunder fungsi hubungan diberi tautan sebagai nilainya.
Ketiga tabel ini dibuat ketika pernyataan berikut dijalankan -
Base.metadata.create_all(engine)Konsol Python memancarkan kueri CREATE TABLE berikut -
CREATE TABLE department (
id INTEGER NOT NULL,
name VARCHAR,
PRIMARY KEY (id)
)
CREATE TABLE employee (
id INTEGER NOT NULL,
name VARCHAR,
PRIMARY KEY (id)
)
CREATE TABLE link (
department_id INTEGER NOT NULL,
employee_id INTEGER NOT NULL,
PRIMARY KEY (department_id, employee_id),
FOREIGN KEY(department_id) REFERENCES department (id),
FOREIGN KEY(employee_id) REFERENCES employee (id)
)Kami dapat memeriksa ini dengan membuka mycollege.db menggunakan SQLiteStudio seperti yang ditunjukkan pada tangkapan layar yang diberikan di bawah ini -
Selanjutnya kita membuat tiga objek kelas Departemen dan tiga objek kelas Karyawan seperti yang ditunjukkan di bawah ini -
d1 = Department(name = "Accounts")
d2 = Department(name = "Sales")
d3 = Department(name = "Marketing")
e1 = Employee(name = "John")
e2 = Employee(name = "Tony")
e3 = Employee(name = "Graham")Setiap tabel memiliki atribut koleksi yang memiliki metode append (). Kita dapat menambahkan objek Karyawan ke koleksi Karyawan di objek Departemen. Demikian pula, kita dapat menambahkan objek Departemen ke atribut koleksi departemen dari objek Karyawan.
e1.departments.append(d1)
e2.departments.append(d3)
d1.employees.append(e3)
d2.employees.append(e2)
d3.employees.append(e1)
e3.departments.append(d2)Yang harus kita lakukan sekarang adalah menyiapkan objek sesi, menambahkan semua objek ke dalamnya dan melakukan perubahan seperti yang ditunjukkan di bawah ini -
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
session.add(e1)
session.add(e2)
session.add(d1)
session.add(d2)
session.add(d3)
session.add(e3)
session.commit()Pernyataan SQL berikut akan dipancarkan di konsol Python -
INSERT INTO department (name) VALUES (?) ('Accounts',)
INSERT INTO department (name) VALUES (?) ('Sales',)
INSERT INTO department (name) VALUES (?) ('Marketing',)
INSERT INTO employee (name) VALUES (?) ('John',)
INSERT INTO employee (name) VALUES (?) ('Graham',)
INSERT INTO employee (name) VALUES (?) ('Tony',)
INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 2), (3, 1), (2, 3))
INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 1), (2, 2), (3, 3))Untuk memeriksa pengaruh operasi di atas, gunakan SQLiteStudio dan lihat data di tabel departemen, karyawan, dan tautan -
Untuk menampilkan data, jalankan pernyataan query berikut -
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
for x in session.query( Department, Employee).filter(Link.department_id == Department.id,
Link.employee_id == Employee.id).order_by(Link.department_id).all():
print ("Department: {} Name: {}".format(x.Department.name, x.Employee.name))Sesuai data yang diisi dalam contoh kami, output akan ditampilkan seperti di bawah ini -
Department: Accounts Name: John
Department: Accounts Name: Graham
Department: Sales Name: Graham
Department: Sales Name: Tony
Department: Marketing Name: John
Department: Marketing Name: TonySQLAlchemy menggunakan sistem dialek untuk berkomunikasi dengan berbagai jenis database. Setiap database memiliki pembungkus DBAPI yang sesuai. Semua dialek mengharuskan driver DBAPI yang sesuai diinstal.
Dialek berikut termasuk dalam SQLAlchemy API -
- Firebird
- Microsoft SQL Server
- MySQL
- Oracle
- PostgreSQL
- SQL
- Sybase
Objek Engine berdasarkan URL dihasilkan oleh fungsi create_engine (). URL ini dapat menyertakan nama pengguna, kata sandi, nama host, dan nama database. Mungkin ada argumen kata kunci opsional untuk konfigurasi tambahan. Dalam beberapa kasus, jalur file diterima, dan di kasus lain, "nama sumber data" menggantikan bagian "host" dan "database". Bentuk khas dari URL database adalah sebagai berikut -
dialect+driver://username:password@host:port/databasePostgreSQL
Dialek PostgreSQL menggunakan psycopg2sebagai DBAPI default. pg8000 juga tersedia sebagai pengganti Python murni seperti yang ditunjukkan di bawah ini:
# default
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')
# psycopg2
engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
# pg8000
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')MySQL
Dialek MySQL menggunakan mysql-pythonsebagai DBAPI default. Ada banyak DBAPI MySQL yang tersedia, seperti MySQL-connector-python sebagai berikut -
# default
engine = create_engine('mysql://scott:tiger@localhost/foo')
# mysql-python
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
# MySQL-connector-python
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')Peramal
Dialek Oracle menggunakan cx_oracle sebagai DBAPI default sebagai berikut -
engine = create_engine('oracle://scott:[email protected]:1521/sidname')
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')Microsoft SQL Server
Dialek SQL Server menggunakan pyodbcsebagai DBAPI default. pymssql juga tersedia.
# pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')
# pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')SQLite
SQLite terhubung ke database berbasis file, menggunakan modul built-in Python sqlite3secara default. Karena SQLite terhubung ke file lokal, format URL-nya sedikit berbeda. Bagian "file" dari URL adalah nama file dari database. Untuk jalur file relatif, ini membutuhkan tiga garis miring seperti yang ditunjukkan di bawah ini -
engine = create_engine('sqlite:///foo.db')Dan untuk jalur file absolut, tiga garis miring diikuti oleh jalur absolut seperti yang diberikan di bawah ini -
engine = create_engine('sqlite:///C:\\path\\to\\foo.db')Untuk menggunakan SQLite: memory: database, tentukan URL kosong seperti yang diberikan di bawah ini -
engine = create_engine('sqlite://')Kesimpulan
Di bagian pertama tutorial ini, kita telah belajar bagaimana menggunakan Expression Language untuk mengeksekusi pernyataan SQL. Bahasa ekspresi menyematkan konstruksi SQL dalam kode Python. Pada bagian kedua, kita telah membahas kapabilitas pemetaan relasi objek dari SQLAlchemy. API ORM memetakan tabel SQL dengan kelas Python.