SQLAlchemy - Hướng dẫn nhanh
SQLAlchemy là một bộ công cụ SQL phổ biến và Object Relational Mapper. Nó được viết bằngPythonvà cung cấp toàn bộ sức mạnh và tính linh hoạt của SQL cho một nhà phát triển ứng dụng. Nó là mộtopen source và cross-platform software phát hành theo giấy phép MIT.
SQLAlchemy nổi tiếng với trình ánh xạ quan hệ đối tượng (ORM), sử dụng nó, các lớp có thể được ánh xạ tới cơ sở dữ liệu, do đó cho phép mô hình đối tượng và lược đồ cơ sở dữ liệu phát triển theo cách tách biệt rõ ràng ngay từ đầu.
Khi kích thước và hiệu suất của cơ sở dữ liệu SQL bắt đầu quan trọng, chúng hoạt động ít giống như các bộ sưu tập đối tượng. Mặt khác, khi sự trừu tượng hóa trong các bộ sưu tập đối tượng bắt đầu quan trọng, chúng hoạt động ít giống như bảng và hàng. SQLAlchemy nhằm mục đích đáp ứng cả hai nguyên tắc này.
Vì lý do này, nó đã sử dụng data mapper pattern (like Hibernate) rather than the active record pattern used by a number of other ORMs. Cơ sở dữ liệu và SQL sẽ được nhìn theo một góc độ khác khi sử dụng SQLAlchemy.
Michael Bayer là tác giả ban đầu của SQLAlchemy. Phiên bản đầu tiên của nó được phát hành vào tháng 2 năm 2006. Phiên bản mới nhất được đánh số là 1.2.7, được phát hành gần đây vào tháng 4 năm 2018.
ORM là gì?
ORM (Object Relational Mapping) là một kỹ thuật lập trình để chuyển đổi dữ liệu giữa các hệ thống kiểu không tương thích trong ngôn ngữ lập trình hướng đối tượng. Thông thường, hệ thống kiểu được sử dụng trong ngôn ngữ Hướng đối tượng (OO) như Python chứa các kiểu không vô hướng. Chúng không thể được biểu thị dưới dạng các kiểu nguyên thủy như số nguyên và chuỗi. Do đó, lập trình viên OO phải chuyển đổi các đối tượng trong dữ liệu vô hướng để tương tác với cơ sở dữ liệu phụ trợ. Tuy nhiên, các kiểu dữ liệu trong hầu hết các sản phẩm cơ sở dữ liệu như Oracle, MySQL, v.v., là chính.
Trong hệ thống ORM, mỗi lớp ánh xạ tới một bảng trong cơ sở dữ liệu bên dưới. Thay vì tự viết mã giao tiếp cơ sở dữ liệu tẻ nhạt, ORM sẽ giải quyết những vấn đề này cho bạn trong khi bạn có thể tập trung vào việc lập trình logic của hệ thống.
SQLAlchemy - Thiết lập môi trường
Hãy để chúng tôi thảo luận về thiết lập môi trường cần thiết để sử dụng SQLAlchemy.
Bất kỳ phiên bản Python nào cao hơn 2.7 đều cần thiết để cài đặt SQLAlchemy. Cách dễ nhất để cài đặt là sử dụng Trình quản lý gói Python,pip. Tiện ích này được đóng gói với phân phối chuẩn của Python.
pip install sqlalchemySử dụng lệnh trên, chúng ta có thể tải xuống latest released versioncủa SQLAlchemy từ python.org và cài đặt nó vào hệ thống của bạn.
Trong trường hợp phân phối anaconda của Python, SQLAlchemy có thể được cài đặt từ conda terminal sử dụng lệnh dưới đây -
conda install -c anaconda sqlalchemyCũng có thể cài đặt SQLAlchemy từ mã nguồn bên dưới -
python setup.py installSQLAlchemy được thiết kế để hoạt động với một triển khai DBAPI được xây dựng cho một cơ sở dữ liệu cụ thể. Nó sử dụng hệ thống phương ngữ để giao tiếp với nhiều kiểu triển khai DBAPI và cơ sở dữ liệu. Tất cả các phương ngữ đều yêu cầu phải cài đặt trình điều khiển DBAPI thích hợp.
Sau đây là các phương ngữ được bao gồm:
- Firebird
- Microsoft SQL Server
- MySQL
- Oracle
- PostgreSQL
- SQLite
- Sybase
Để kiểm tra xem SQLAlchemy có được cài đặt đúng cách hay không và để biết phiên bản của nó, hãy nhập lệnh sau vào dấu nhắc Python:
>>> import sqlalchemy
>>>sqlalchemy.__version__
'1.2.7'SQLAlchemy core bao gồm SQL rendering engine, DBAPI integration, transaction integrationvà schema description services. SQLAlchemy core sử dụng ngôn ngữ biểu thức SQL cung cấpschema-centric usage mô hình trong khi SQLAlchemy ORM là một domain-centric mode of usage.
Ngôn ngữ biểu thức SQL trình bày một hệ thống biểu diễn các cấu trúc và biểu thức cơ sở dữ liệu quan hệ bằng cách sử dụng các cấu trúc Python. Nó trình bày một hệ thống đại diện cho các cấu trúc nguyên thủy của cơ sở dữ liệu quan hệ một cách trực tiếp mà không có ý kiến, trái ngược với ORM trình bày một cách sử dụng cấp cao và trừu tượng, bản thân nó là một ví dụ về cách sử dụng được áp dụng của Ngôn ngữ Biểu thức.
Ngôn ngữ biểu thức là một trong những thành phần cốt lõi của SQLAlchemy. Nó cho phép lập trình viên chỉ định các câu lệnh SQL bằng mã Python và sử dụng nó trực tiếp trong các truy vấn phức tạp hơn. Ngôn ngữ biểu thức độc lập với phụ trợ và bao gồm toàn diện mọi khía cạnh của SQL thô. Nó gần với SQL thô hơn bất kỳ thành phần nào khác trong SQLAlchemy.
Ngôn ngữ biểu thức đại diện trực tiếp các cấu trúc nguyên thủy của cơ sở dữ liệu quan hệ. Vì ORM dựa trên ngôn ngữ Biểu thức, một ứng dụng cơ sở dữ liệu Python điển hình có thể đã sử dụng chồng chéo cả hai. Ứng dụng có thể chỉ sử dụng ngôn ngữ biểu thức, mặc dù nó phải xác định hệ thống dịch các khái niệm ứng dụng thành các truy vấn cơ sở dữ liệu riêng lẻ.
Các câu lệnh của ngôn ngữ Biểu thức sẽ được công cụ SQLAlchemy dịch thành các truy vấn SQL thô tương ứng. Bây giờ chúng ta sẽ học cách tạo công cụ và thực thi các truy vấn SQL khác nhau với sự trợ giúp của nó.
Trong chương trước, chúng ta đã thảo luận về Ngôn ngữ biểu thức trong SQLAlchemy. Bây giờ chúng ta hãy tiến hành các bước liên quan đến việc kết nối với cơ sở dữ liệu.
Lớp động cơ kết nối một Pool and Dialect together cung cấp một nguồn cơ sở dữ liệu connectivity and behavior. Một đối tượng của lớp Engine được khởi tạo bằng cách sử dụngcreate_engine() chức năng.
Hàm create_engine () nhận cơ sở dữ liệu làm một đối số. Cơ sở dữ liệu không cần thiết phải được xác định ở bất kỳ đâu. Biểu mẫu gọi tiêu chuẩn phải gửi URL dưới dạng đối số vị trí đầu tiên, thường là một chuỗi biểu thị phương ngữ cơ sở dữ liệu và đối số kết nối. Sử dụng đoạn mã dưới đây, chúng ta có thể tạo một cơ sở dữ liệu.
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///college.db', echo = True)Cho một MySQL database, sử dụng lệnh dưới đây -
engine = create_engine("mysql://user:pwd@localhost/college",echo = True)Đề cập cụ thể DB-API được sử dụng để kết nối, URL string có dạng như sau:
dialect[+driver]://user:password@host/dbnameVí dụ, nếu bạn đang sử dụng PyMySQL driver with MySQL, sử dụng lệnh sau:
mysql+pymysql://<username>:<password>@<host>/<dbname>Các echo flaglà một lối tắt để thiết lập ghi nhật ký SQLAlchemy, được thực hiện thông qua mô-đun ghi nhật ký tiêu chuẩn của Python. Trong các chương tiếp theo, chúng ta sẽ tìm hiểu tất cả các SQL được tạo. Để ẩn đầu ra dài dòng, hãy đặt thuộc tính echo thànhNone. Các đối số khác cho hàm create_engine () có thể là phương ngữ cụ thể.
Hàm create_engine () trả về một Engine object. Một số phương thức quan trọng của lớp Engine là:
| Sr.No. | Phương pháp & Mô tả |
|---|---|
| 1 | connect() Trả về đối tượng kết nối |
| 2 | execute() Thực thi một cấu trúc câu lệnh SQL |
| 3 | begin() Trả về trình quản lý ngữ cảnh cung cấp Kết nối với Giao dịch được thiết lập. Sau khi hoạt động thành công, Giao dịch được cam kết, nếu không, Giao dịch sẽ được khôi phục |
| 4 | dispose() Loại bỏ nhóm kết nối được Engine sử dụng |
| 5 | driver() Tên trình điều khiển của phương ngữ được Engine sử dụng |
| 6 | table_names() Trả về danh sách tất cả các tên bảng có sẵn trong cơ sở dữ liệu |
| 7 | transaction() Thực thi chức năng đã cho trong ranh giới giao dịch |
Bây giờ chúng ta hãy thảo luận về cách sử dụng hàm tạo bảng.
Ngôn ngữ biểu thức SQL xây dựng các biểu thức của nó dựa trên các cột của bảng. Đối tượng SQLAlchemy Column đại diện cho mộtcolumn trong một bảng cơ sở dữ liệu mà lần lượt được đại diện bởi một Tableobject. Siêu dữ liệu chứa các định nghĩa về bảng và các đối tượng liên quan như chỉ mục, chế độ xem, trình kích hoạt, v.v.
Do đó, một đối tượng của lớp MetaData từ Siêu dữ liệu SQLAlchemy là một tập hợp các đối tượng Bảng và các cấu trúc lược đồ liên quan của chúng. Nó chứa một bộ sưu tập các đối tượng Bảng cũng như một ràng buộc tùy chọn đối với Động cơ hoặc Kết nối.
from sqlalchemy import MetaData
meta = MetaData()Khối tạo của lớp MetaData có thể có các tham số liên kết và lược đồ theo mặc định None.
Tiếp theo, chúng tôi xác định tất cả các bảng của mình trong danh mục siêu dữ liệu ở trên, sử dụng the Table construct, giống như câu lệnh SQL CREATE TABLE thông thường.
Một đối tượng của lớp Table đại diện cho bảng tương ứng trong cơ sở dữ liệu. Hàm tạo nhận các tham số sau:
| Tên | Tên của bảng |
|---|---|
| metadata | Đối tượng MetaData sẽ giữ bảng này |
| Cột) | Một hoặc nhiều đối tượng của lớp cột |
Đối tượng cột đại diện cho một column trong một database table. Constructor lấy tên, kiểu và các tham số khác như khóa_chính_chính, autoincrement và các ràng buộc khác.
SQLAlchemy đối sánh dữ liệu Python với các kiểu dữ liệu cột chung chung tốt nhất có thể được xác định trong đó. Một số kiểu dữ liệu chung là -
- BigInteger
- Boolean
- Date
- DateTime
- Float
- Integer
- Numeric
- SmallInteger
- String
- Text
- Time
Để tạo ra một students table trong cơ sở dữ liệu đại học, hãy sử dụng đoạn mã sau:
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),
)Hàm create_all () sử dụng đối tượng engine để tạo tất cả các đối tượng bảng đã xác định và lưu trữ thông tin trong siêu dữ liệu.
meta.create_all(engine)Mã hoàn chỉnh được cung cấp bên dưới sẽ tạo cơ sở dữ liệu SQLite college.db với bảng sinh viên trong đó.
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)Vì thuộc tính echo của hàm create_engine () được đặt thành True, bảng điều khiển sẽ hiển thị truy vấn SQL thực tế để tạo bảng như sau:
CREATE TABLE students (
id INTEGER NOT NULL,
name VARCHAR,
lastname VARCHAR,
PRIMARY KEY (id)
)Cao đẳng.db sẽ được tạo trong thư mục làm việc hiện tại. Để kiểm tra xem bảng sinh viên đã được tạo chưa, bạn có thể mở cơ sở dữ liệu bằng bất kỳ công cụ GUI nào của SQLite chẳng hạn nhưSQLiteStudio.
Hình ảnh dưới đây cho thấy bảng sinh viên được tạo trong cơ sở dữ liệu -
Trong chương này, chúng ta sẽ tập trung ngắn gọn vào Biểu thức SQL và các chức năng của chúng.
Biểu thức SQL được xây dựng bằng cách sử dụng các phương thức tương ứng liên quan đến đối tượng bảng đích. Ví dụ, câu lệnh INSERT được tạo bằng cách thực thi phương thức insert () như sau:
ins = students.insert()Kết quả của phương pháp trên là một đối tượng chèn có thể được xác minh bằng cách sử dụng str()chức năng. Đoạn mã dưới đây chèn các chi tiết như id sinh viên, tên, họ.
'INSERT INTO students (id, name, lastname) VALUES (:id, :name, :lastname)'Có thể chèn giá trị vào một trường cụ thể bằng cách values()phương thức chèn đối tượng. Mã cho điều tương tự được đưa ra dưới đây:
>>> ins = users.insert().values(name = 'Karan')
>>> str(ins)
'INSERT INTO users (name) VALUES (:name)'SQL lặp lại trên bảng điều khiển Python không hiển thị giá trị thực (trong trường hợp này là 'Karan'). Thay vào đó, SQLALchemy tạo ra một tham số liên kết hiển thị ở dạng đã biên dịch của câu lệnh.
ins.compile().params
{'name': 'Karan'}Tương tự, các phương pháp như update(), delete() và select()tạo các biểu thức UPDATE, DELETE và SELECT tương ứng. Chúng ta sẽ tìm hiểu về chúng trong các chương sau.
Trong chương trước, chúng ta đã học về Biểu thức SQL. Trong chương này, chúng ta sẽ xem xét việc thực thi các biểu thức này.
Để thực thi các biểu thức SQL kết quả, chúng ta phải obtain a connection object representing an actively checked out DBAPI connection resource và sau đó feed the expression object như được hiển thị trong mã dưới đây.
conn = engine.connect()Đối tượng insert () sau đây có thể được sử dụng cho phương thức execute () -
ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
result = conn.execute(ins)Bảng điều khiển hiển thị kết quả thực thi biểu thức SQL như bên dưới:
INSERT INTO students (name, lastname) VALUES (?, ?)
('Ravi', 'Kapoor')
COMMITSau đây là toàn bộ đoạn mã cho thấy việc thực thi truy vấn INSERT bằng kỹ thuật cốt lõi của 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)Kết quả có thể được xác minh bằng cách mở cơ sở dữ liệu bằng SQLite Studio như được hiển thị trong ảnh chụp màn hình bên dưới -
Biến kết quả được gọi là ResultProxy object. Nó tương tự như đối tượng con trỏ DBAPI. Chúng tôi có thể thu thập thông tin về các giá trị khóa chính được tạo từ câu lệnh của chúng tôi bằng cách sử dụngResultProxy.inserted_primary_key như hình dưới đây -
result.inserted_primary_key
[1]Để phát hành nhiều chèn bằng phương thức thực thi nhiều () của DBAPI, chúng ta có thể gửi vào danh sách các từ điển, mỗi từ điển chứa một tập hợp các tham số riêng biệt sẽ được chèn.
conn.execute(students.insert(), [
{'name':'Rajiv', 'lastname' : 'Khanna'},
{'name':'Komal','lastname' : 'Bhandari'},
{'name':'Abdul','lastname' : 'Sattar'},
{'name':'Priya','lastname' : 'Rajhans'},
])Điều này được phản ánh trong chế độ xem dữ liệu của bảng như trong hình sau:
Trong chương này, chúng ta sẽ thảo luận về khái niệm chọn hàng trong đối tượng bảng.
Phương thức select () của đối tượng bảng cho phép chúng tôi construct SELECT expression.
s = students.select()Đối tượng được chọn dịch sang SELECT query by str(s) function như hình dưới đây -
'SELECT students.id, students.name, students.lastname FROM students'Chúng ta có thể sử dụng đối tượng chọn này làm tham số để thực thi () phương thức của đối tượng kết nối như được hiển thị trong đoạn mã bên dưới:
result = conn.execute(s)Khi câu lệnh trên được thực thi, trình bao Python lặp lại sau biểu thức SQL tương đương:
SELECT students.id, students.name, students.lastname
FROM studentsBiến kết quả tương đương với con trỏ trong DBAPI. Bây giờ chúng tôi có thể tìm nạp các bản ghi bằng cách sử dụngfetchone() method.
row = result.fetchone()Tất cả các hàng đã chọn trong bảng có thể được in bằng for loop như dưới đây -
for row in result:
print (row)Mã hoàn chỉnh để in tất cả các hàng từ bảng sinh viên được hiển thị bên dưới:
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)Đầu ra được hiển thị trong trình bao Python như sau:
(1, 'Ravi', 'Kapoor')
(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')Mệnh đề WHERE của truy vấn SELECT có thể được áp dụng bằng cách sử dụng Select.where(). Ví dụ: nếu chúng ta muốn hiển thị các hàng có id> 2
s = students.select().where(students.c.id>2)
result = conn.execute(s)
for row in result:
print (row)Đây c attribute is an alias for column. Kết quả sau sẽ được hiển thị trên shell -
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')Ở đây, chúng ta phải lưu ý rằng đối tượng select cũng có thể được lấy bằng hàm select () trong mô-đun sqlalchemy.sql. Hàm select () yêu cầu đối tượng bảng làm đối số.
from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)SQLAlchemy cho phép bạn chỉ sử dụng chuỗi, đối với những trường hợp khi SQL đã được biết đến và không cần câu lệnh hỗ trợ các tính năng động. Cấu trúc text () được sử dụng để soạn một câu lệnh dạng văn bản được chuyển đến cơ sở dữ liệu hầu như không thay đổi.
Nó tạo ra một TextClause, đại diện trực tiếp cho một chuỗi SQL văn bản như được hiển thị trong đoạn mã dưới đây -
from sqlalchemy import text
t = text("SELECT * FROM students")
result = connection.execute(t)Những lợi thế text() cung cấp trên một chuỗi đơn giản là -
- hỗ trợ trung lập phụ trợ cho các tham số ràng buộc
- các tùy chọn thực thi mỗi câu lệnh
- hành vi nhập cột kết quả
Hàm text () yêu cầu tham số Bound ở định dạng dấu hai chấm được đặt tên. Chúng nhất quán bất kể phụ trợ cơ sở dữ liệu. Để gửi các giá trị cho các tham số, chúng tôi truyền chúng vào phương thức execute () như các đối số bổ sung.
Ví dụ sau sử dụng các tham số liên kết trong SQL văn bản:
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()Hàm text () xây dựng biểu thức SQL như sau:
select students.name, students.lastname from students where students.name between ? and ?Giá trị của x = 'A' và y = 'L' được chuyển dưới dạng tham số. Kết quả là danh sách các hàng có tên từ 'A' đến 'L' -
[('Komal', 'Bhandari'), ('Abdul', 'Sattar')]Cấu trúc text () hỗ trợ các giá trị ràng buộc được thiết lập trước bằng phương thức TextClause.bindparams (). Các tham số cũng có thể được nhập rõ ràng như sau:
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()Bạn cũng có thể dùng and_() hàm kết hợp nhiều điều kiện trong mệnh đề WHERE được tạo với sự trợ giúp của hàm 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()Đoạn mã trên tìm nạp các hàng có tên từ “A” đến “L” với id lớn hơn 2. Đầu ra của mã được đưa ra bên dưới:
[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar')]Bí danh trong SQL tương ứng với phiên bản "được đổi tên" của bảng hoặc câu lệnh SELECT, xảy ra bất cứ lúc nào bạn nói "SELECT * FROM table1 AS a". AS tạo một tên mới cho bảng. Bí danh cho phép bất kỳ bảng hoặc truy vấn con nào được tham chiếu bằng một tên duy nhất.
Trong trường hợp một bảng, điều này cho phép cùng một bảng được đặt tên trong mệnh đề FROM nhiều lần. Nó cung cấp tên cha cho các cột được biểu thị bằng câu lệnh, cho phép chúng được tham chiếu liên quan đến tên này.
Trong SQLAlchemy, bất kỳ cấu trúc Table, select () nào hoặc đối tượng có thể chọn khác đều có thể được chuyển thành bí danh bằng cách sử dụng From Clause.alias()phương thức này tạo ra một cấu trúc Bí danh. Hàm alias () trong mô-đun sqlalchemy.sql đại diện cho một bí danh, thường được áp dụng cho bất kỳ bảng hoặc lựa chọn con nào trong câu lệnh SQL bằng cách sử dụng từ khóa AS.
from sqlalchemy.sql import alias
st = students.alias("a")Bí danh này hiện có thể được sử dụng trong cấu trúc select () để tham chiếu đến bảng sinh viên -
s = select([st]).where(st.c.id>2)Điều này chuyển sang biểu thức SQL như sau:
SELECT a.id, a.name, a.lastname FROM students AS a WHERE a.id > 2Bây giờ chúng ta có thể thực thi truy vấn SQL này với phương thức execute () của đối tượng kết nối. Mã hoàn chỉnh như sau:
from sqlalchemy.sql import alias, select
st = students.alias("a")
s = select([st]).where(st.c.id > 2)
conn.execute(s).fetchall()Khi dòng mã trên được thực thi, nó tạo ra kết quả sau:
[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar'), (5, 'Priya', 'Rajhans')]Các update() phương thức trên đối tượng bảng đích xây dựng biểu thức SQL UPDATE tương đương.
table.update().where(conditions).values(SET expressions)Các values()phương thức trên đối tượng cập nhật kết quả được sử dụng để chỉ định điều kiện SET của CẬP NHẬT. Nếu để là Không, các điều kiện SET được xác định từ các tham số đó được truyền cho câu lệnh trong quá trình thực thi và / hoặc biên dịch câu lệnh.
Mệnh đề where là một biểu thức Tùy chọn mô tả điều kiện WHERE của câu lệnh UPDATE.
Đoạn mã sau thay đổi giá trị của cột 'họ' từ 'Khanna' thành 'Kapoor' trong bảng sinh viên -
stmt = students.update().where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')Đối tượng stmt là một đối tượng cập nhật dịch sang -
'UPDATE students SET lastname = :lastname WHERE students.lastname = :lastname_1'Tham số ràng buộc lastname_1 sẽ được thay thế khi execute()phương thức được gọi. Mã cập nhật đầy đủ được cung cấp bên dưới -
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()Đoạn mã trên hiển thị đầu ra sau với hàng thứ hai hiển thị hiệu quả của hoạt động cập nhật như trong ảnh chụp màn hình đã cho -
[
(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Kapoor'),
(3, 'Komal', 'Bhandari'),
(4, 'Abdul', 'Sattar'),
(5, 'Priya', 'Rajhans')
]
Lưu ý rằng chức năng tương tự cũng có thể đạt được bằng cách sử dụng update() hàm trong mô-đun sqlalchemy.sql.expression như hình dưới đây -
from sqlalchemy.sql.expression import update
stmt = update(students).where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')Trong chương trước, chúng ta đã hiểu thế nào là Updatebiểu thức không. Biểu thức tiếp theo mà chúng ta sẽ học làDelete.
Thao tác xóa có thể đạt được bằng cách chạy phương thức delete () trên đối tượng bảng đích như được đưa ra trong câu lệnh sau:
stmt = students.delete()Trong trường hợp bảng sinh viên, dòng mã trên tạo biểu thức SQL như sau:
'DELETE FROM students'Tuy nhiên, điều này sẽ xóa tất cả các hàng trong bảng sinh viên. Thông thường truy vấn DELETE được kết hợp với một biểu thức logic được chỉ định bởi mệnh đề WHERE. Câu lệnh sau đây cho thấy tham số where -
stmt = students.delete().where(students.c.id > 2)Biểu thức SQL kết quả sẽ có một tham số bị ràng buộc sẽ được thay thế trong thời gian chạy khi câu lệnh được thực thi.
'DELETE FROM students WHERE students.id > :id_1'Ví dụ mã sau sẽ xóa các hàng đó khỏi bảng sinh viên có họ là '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()Để xác minh kết quả, hãy làm mới chế độ xem dữ liệu của bảng sinh viên trong SQLiteStudio.
Một trong những tính năng quan trọng của RDBMS là thiết lập mối quan hệ giữa các bảng. Các hoạt động SQL như SELECT, UPDATE và DELETE có thể được thực hiện trên các bảng liên quan. Phần này mô tả các hoạt động này bằng cách sử dụng SQLAlchemy.
Vì mục đích này, hai bảng được tạo trong cơ sở dữ liệu SQLite của chúng tôi (college.db). Bảng sinh viên có cấu trúc tương tự như trong phần trước; trong khi bảng địa chỉ cóst_id cột được ánh xạ tới id column in students table sử dụng ràng buộc khóa ngoại.
Đoạn mã sau sẽ tạo hai bảng trong 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)Đoạn mã trên sẽ dịch sang truy vấn TẠO BẢNG cho sinh viên và bảng địa chỉ như bên dưới -
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)
)Các ảnh chụp màn hình sau đây trình bày đoạn mã trên rất rõ ràng -
Các bảng này được điền dữ liệu bằng cách thực thi insert() methodcủa các đối tượng bảng. Để chèn 5 hàng trong bảng sinh viên, bạn có thể sử dụng mã được cung cấp bên dưới:
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 được thêm vào bảng địa chỉ với sự trợ giúp của mã sau:
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]'},
])Lưu ý rằng cột st_id trong bảng địa chỉ tham chiếu đến cột id trong bảng sinh viên. Bây giờ chúng ta có thể sử dụng mối quan hệ này để tìm nạp dữ liệu từ cả hai bảng. Chúng tôi muốn tìm nạpname và lastname từ bảng sinh viên tương ứng với st_id trong bảng địa chỉ.
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)Các đối tượng được chọn sẽ dịch một cách hiệu quả thành biểu thức SQL sau khi kết hợp hai bảng trên quan hệ chung:
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_idĐiều này sẽ tạo ra kết quả trích xuất dữ liệu tương ứng từ cả hai bảng như sau:
(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]')Trong chương trước, chúng ta đã thảo luận về cách sử dụng nhiều bảng. Vì vậy, chúng tôi tiến thêm một bước nữa và tìm hiểumultiple table updates trong chương này.
Sử dụng đối tượng bảng của SQLAlchemy, nhiều hơn một bảng có thể được chỉ định trong mệnh đề WHERE của phương thức update (). PostgreSQL và Microsoft SQL Server hỗ trợ các câu lệnh UPDATE tham chiếu đến nhiều bảng. Điều này thực hiện“UPDATE FROM”cú pháp, cập nhật từng bảng một. Tuy nhiên, các bảng bổ sung có thể được tham chiếu trực tiếp trong mệnh đề “FROM” trong mệnh đề WHERE. Các dòng mã sau đây giải thích khái niệmmultiple table updates thông suốt.
stmt = students.update().\
values({
students.c.name:'xyz',
addresses.c.email_add:'[email protected]'
}).\
where(students.c.id == addresses.c.id)Đối tượng cập nhật tương đương với truy vấn UPDATE sau:
UPDATE students
SET email_add = :addresses_email_add, name = :name
FROM addresses
WHERE students.id = addresses.idTheo như phương ngữ MySQL có liên quan, nhiều bảng có thể được nhúng vào một câu lệnh UPDATE duy nhất được phân tách bằng dấu phẩy như được đưa ra bên dưới:
stmt = students.update().\
values(name = 'xyz').\
where(students.c.id == addresses.c.id)Đoạn mã sau mô tả truy vấn UPDATE kết quả:
'UPDATE students SET name = :name
FROM addresses
WHERE students.id = addresses.id'Tuy nhiên, phương ngữ SQLite không hỗ trợ tiêu chí nhiều bảng trong UPDATE và hiển thị lỗi sau:
NotImplementedError: This backend does not support multiple-table criteria within UPDATETruy vấn UPDATE của SQL thô có mệnh đề SET. Nó được hiển thị bởi cấu trúc update () bằng cách sử dụng thứ tự cột được đưa ra trong đối tượng Bảng gốc. Do đó, một câu lệnh UPDATE cụ thể với các cột cụ thể sẽ được hiển thị giống nhau mỗi lần. Vì bản thân các tham số được truyền cho phương thức Update.values () dưới dạng khóa từ điển Python, không có thứ tự cố định nào khác.
Trong một số trường hợp, thứ tự của các tham số được hiển thị trong mệnh đề SET là quan trọng. Trong MySQL, việc cung cấp các bản cập nhật cho giá trị cột dựa trên bản cập nhật của các giá trị cột khác.
Kết quả của câu lệnh sau -
UPDATE table1 SET x = y + 10, y = 20sẽ có kết quả khác với -
UPDATE table1 SET y = 20, x = y + 10Mệnh đề SET trong MySQL được đánh giá trên cơ sở mỗi giá trị chứ không phải trên cơ sở mỗi hàng. Với mục đích này,preserve_parameter_orderĐược sử dụng. Danh sách 2 bộ dữ liệu Python được đưa ra làm đối số choUpdate.values() phương pháp -
stmt = table1.update(preserve_parameter_order = True).\
values([(table1.c.y, 20), (table1.c.x, table1.c.y + 10)])Đối tượng List tương tự như từ điển ngoại trừ nó được sắp xếp theo thứ tự. Điều này đảm bảo rằng mệnh đề SET của cột “y” sẽ hiển thị đầu tiên, sau đó là mệnh đề SET của cột “x”.
Trong chương này, chúng ta sẽ xem xét biểu thức Xóa Nhiều Bảng tương tự như cách sử dụng chức năng Cập nhật Nhiều Bảng.
Nhiều hơn một bảng có thể được tham chiếu trong mệnh đề WHERE của câu lệnh DELETE trong nhiều phương ngữ DBMS. Đối với PG và MySQL, cú pháp "XÓA SỬ DỤNG" được sử dụng; và đối với SQL Server, sử dụng biểu thức "XÓA TỪ" đề cập đến nhiều hơn một bảng. SQLAlchemydelete() construct hỗ trợ ngầm định cả hai chế độ này, bằng cách chỉ định nhiều bảng trong mệnh đề WHERE như sau:
stmt = users.delete().\
where(users.c.id == addresses.c.id).\
where(addresses.c.email_address.startswith('xyz%'))
conn.execute(stmt)Trên chương trình phụ trợ PostgreSQL, SQL kết quả từ câu lệnh trên sẽ hiển thị như sau:
DELETE FROM users USING addresses
WHERE users.id = addresses.id
AND (addresses.email_address LIKE %(email_address_1)s || '%%')Nếu phương pháp này được sử dụng với cơ sở dữ liệu không hỗ trợ hành vi này, trình biên dịch sẽ nâng cao NotImplementedError.
Trong chương này, chúng ta sẽ học cách sử dụng Joins trong SQLAlchemy.
Hiệu quả của việc tham gia đạt được bằng cách chỉ cần đặt hai bảng vào columns clause hoặc là where clausecủa cấu trúc select (). Bây giờ chúng ta sử dụng các phương thức join () và externaljoin ().
Phương thức join () trả về một đối tượng nối từ đối tượng bảng này sang đối tượng bảng khác.
join(right, onclause = None, isouter = False, full = False)Chức năng của các tham số được đề cập trong đoạn mã trên như sau:
right- phía bên phải của phép nối; đây là bất kỳ đối tượng Table nào
onclause- một biểu thức SQL đại diện cho mệnh đề ON của phép nối. Nếu được để ở Không có, nó sẽ cố gắng nối hai bảng dựa trên mối quan hệ khóa ngoài
isouter - nếu Đúng, hiển thị THAM GIA TRÁI OUTER, thay vì THAM GIA
full - nếu True, hiển thị FULL OUTER JOIN, thay vì LEFT OUTER JOIN
Ví dụ: việc sử dụng phương thức join () sau sẽ tự động dẫn đến phép nối dựa trên khóa ngoại.
>>> print(students.join(addresses))Điều này tương đương với biểu thức SQL sau:
students JOIN addresses ON students.id = addresses.st_idBạn có thể đề cập rõ ràng các tiêu chí tham gia như sau:
j = students.join(addresses, students.c.id == addresses.c.st_id)Nếu bây giờ chúng ta xây dựng cấu trúc chọn bên dưới bằng cách sử dụng phép nối này dưới dạng:
stmt = select([students]).select_from(j)Điều này sẽ dẫn đến biểu thức SQL sau:
SELECT students.id, students.name, students.lastname
FROM students JOIN addresses ON students.id = addresses.st_idNếu câu lệnh này được thực thi bằng công cụ biểu diễn kết nối, dữ liệu thuộc các cột đã chọn sẽ được hiển thị. Mã hoàn chỉnh như sau:
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()Sau đây là kết quả của đoạn mã trên:
[
(1, 'Ravi', 'Kapoor'),
(1, 'Ravi', 'Kapoor'),
(3, 'Komal', 'Bhandari'),
(5, 'Priya', 'Rajhans'),
(2, 'Rajiv', 'Khanna')
]Các hàm là các hàm trong mô-đun SQLAlchemy triển khai các toán tử quan hệ được sử dụng trong mệnh đề WHERE của biểu thức SQL. Các toán tử AND, OR, NOT, v.v., được sử dụng để tạo thành một biểu thức ghép kết hợp hai biểu thức logic riêng lẻ. Một ví dụ đơn giản về việc sử dụng AND trong câu lệnh SELECT như sau:
SELECT * from EMPLOYEE WHERE salary>10000 AND age>30Các hàm SQLAlchemy và_ (), or_ () và not_ () tương ứng triển khai các toán tử AND, OR và NOT.
hàm and_ ()
Nó tạo ra một tổ hợp các biểu thức được nối bởi AND. Dưới đây là một ví dụ để hiểu rõ hơn -
from sqlalchemy import and_
print(
and_(
students.c.name == 'Ravi',
students.c.id <3
)
)Điều này dịch thành -
students.name = :name_1 AND students.id < :id_1Để sử dụng và_ () trong một cấu trúc select () trên bảng sinh viên, hãy sử dụng dòng mã sau:
stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))Câu lệnh SELECT có tính chất sau sẽ được xây dựng:
SELECT students.id,
students.name,
students.lastname
FROM students
WHERE students.name = :name_1 AND students.id < :id_1Mã hoàn chỉnh hiển thị đầu ra của truy vấn SELECT ở trên như sau:
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())Hàng sau sẽ được chọn giả sử rằng bảng sinh viên được điền bằng dữ liệu được sử dụng trong ví dụ trước -
[(1, 'Ravi', 'Kapoor')]hàm or_ ()
Nó tạo ra kết hợp các biểu thức được nối bởi OR. Chúng ta sẽ thay thế đối tượng stmt trong ví dụ trên bằng đối tượng sau bằng cách sử dụng or_ ()
stmt = select([students]).where(or_(students.c.name == 'Ravi', students.c.id <3))Điều này sẽ tương đương với truy vấn SELECT sau:
SELECT students.id,
students.name,
students.lastname
FROM students
WHERE students.name = :name_1
OR students.id < :id_1Khi bạn thực hiện thay thế và chạy đoạn mã trên, kết quả sẽ là hai hàng rơi vào điều kiện HOẶC -
[(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Khanna')]hàm asc ()
Nó tạo ra một mệnh đề ORDER BY tăng dần. Hàm lấy cột để áp dụng hàm làm tham số.
from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))Câu lệnh triển khai biểu thức SQL sau:
SELECT students.id,
students.name,
students.lastname
FROM students
ORDER BY students.name ASCĐoạn mã sau liệt kê tất cả các bản ghi trong bảng sinh viên theo thứ tự tăng dần của cột tên -
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)Đoạn mã trên tạo ra kết quả sau:
(4, 'Abdul', 'Sattar')
(3, 'Komal', 'Bhandari')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')hàm desc ()
Tương tự, hàm desc () tạo ra mệnh đề ORDER BY giảm dần như sau:
from sqlalchemy import desc
stmt = select([students]).order_by(desc(students.c.lastname))Biểu thức SQL tương đương là:
SELECT students.id,
students.name,
students.lastname
FROM students
ORDER BY students.lastname DESCVà đầu ra cho các dòng mã trên là -
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')
(3, 'Komal', 'Bhandari')giữa () hàm
Nó tạo ra một mệnh đề vị ngữ GIỮA. Điều này thường được sử dụng để xác thực nếu giá trị của một cột nhất định nằm giữa một phạm vi. Ví dụ: mã sau đây chọn các hàng có cột id nằm trong khoảng từ 2 đến 4 -
from sqlalchemy import between
stmt = select([students]).where(between(students.c.id,2,4))
print (stmt)Biểu thức SQL kết quả giống như sau:
SELECT students.id,
students.name,
students.lastname
FROM students
WHERE students.id
BETWEEN :id_1 AND :id_2và kết quả như sau:
(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')Một số hàm quan trọng được sử dụng trong SQLAlchemy được thảo luận trong chương này.
SQL chuẩn đã đề xuất nhiều hàm được thực hiện bởi hầu hết các phương ngữ. Chúng trả về một giá trị duy nhất dựa trên các đối số được truyền cho nó. Một số hàm SQL nhận các cột làm đối số trong khi một số là chung.Thefunc keyword in SQLAlchemy API is used to generate these functions.
Trong SQL, now () là một hàm chung. Các câu lệnh sau hiển thị hàm now () bằng cách sử dụng func:
from sqlalchemy.sql import func
result = conn.execute(select([func.now()]))
print (result.fetchone())Kết quả mẫu của đoạn mã trên có thể như hình dưới đây -
(datetime.datetime(2018, 6, 16, 6, 4, 40),)Mặt khác, hàm count () trả về số hàng được chọn từ bảng, được hiển thị bằng cách sử dụng hàm func sau:
from sqlalchemy.sql import func
result = conn.execute(select([func.count(students.c.id)]))
print (result.fetchone())Từ đoạn mã trên, số lượng hàng trong bảng sinh viên sẽ được tìm nạp.
Một số hàm SQL tích hợp được trình bày bằng cách sử dụng bảng Nhân viên với dữ liệu sau:
| TÔI | Tên | Điểm |
|---|---|---|
| 1 | Kamal | 56 |
| 2 | Fernandez | 85 |
| 3 | Sunil | 62 |
| 4 | Bhaskar | 76 |
Hàm max () được triển khai bằng cách sử dụng hàm func từ SQLAlchemy sau đây sẽ dẫn đến 85, tổng số điểm tối đa thu được -
from sqlalchemy.sql import func
result = conn.execute(select([func.max(employee.c.marks)]))
print (result.fetchone())Tương tự, hàm min () sẽ trả về 56, điểm tối thiểu, sẽ được hiển thị bằng mã sau:
from sqlalchemy.sql import func
result = conn.execute(select([func.min(employee.c.marks)]))
print (result.fetchone())Vì vậy, hàm AVG () cũng có thể được triển khai bằng cách sử dụng đoạn mã dưới đây:
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())Trong chương trước, chúng ta đã tìm hiểu về các hàm khác nhau như max (), min (), count (), v.v., ở đây, chúng ta sẽ tìm hiểu về các phép toán tập hợp và công dụng của chúng.
Các hoạt động tập hợp như UNION và INTERSECT được hỗ trợ bởi SQL tiêu chuẩn và hầu hết phương ngữ của nó. SQLAlchemy triển khai chúng với sự trợ giúp của các hàm sau:
liên hiệp()
Trong khi kết hợp các kết quả của hai hoặc nhiều câu lệnh SELECT, UNION sẽ loại bỏ các bản sao khỏi tập kết quả. Số lượng cột và kiểu dữ liệu phải giống nhau trong cả hai bảng.
Hàm union () trả về một đối tượng CompoundSelect từ nhiều bảng. Ví dụ sau thể hiện công dụng của nó:
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()Cấu trúc union dịch sang biểu thức SQL sau:
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 ?Từ bảng địa chỉ của chúng tôi, các hàng sau đại diện cho hoạt động liên hợp:
[
(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 ()
Hoạt động UNION ALL không thể loại bỏ các bản sao và không thể sắp xếp dữ liệu trong tập kết quả. Ví dụ, trong truy vấn trên, UNION được thay thế bằng UNION ALL để xem hiệu ứng.
u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))Biểu thức SQL tương ứng như sau:
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 ?ngoại trừ_()
SQL EXCEPTmệnh đề / toán tử được sử dụng để kết hợp hai câu lệnh SELECT và trả về các hàng từ câu lệnh SELECT đầu tiên mà không được trả về bởi câu lệnh SELECT thứ hai. Hàm exception_ () tạo ra biểu thức SELECT với mệnh đề EXCEPT.
Trong ví dụ sau, hàm exception_ () chỉ trả về những bản ghi từ bảng địa chỉ có 'gmail.com' trong trường email_add nhưng loại trừ những bản ghi có 'Pune' như một phần của trường post_add.
u = except_(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))Kết quả của đoạn mã trên là biểu thức SQL sau:
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 ?Giả sử rằng bảng địa chỉ chứa dữ liệu được sử dụng trong các ví dụ trước đó, nó sẽ hiển thị kết quả sau:
[(2, 1, 'ChurchGate Mumbai', '[email protected]'),
(3, 3, 'Jubilee Hills Hyderabad', '[email protected]')]giao nhau()
Sử dụng toán tử INTERSECT, SQL hiển thị các hàng chung từ cả hai câu lệnh SELECT. Hàm interect () thực hiện hành vi này.
Trong các ví dụ sau, hai cấu trúc SELECT là các tham số cho hàm giao nhau (). Một trả về các hàng chứa 'gmail.com' như một phần của cột email_add và các hàng khác trả về các hàng có 'Pune' là một phần của cột post_add. Kết quả sẽ là các hàng chung từ cả hai tập kết quả.
u = intersect(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))Trên thực tế, điều này tương đương với câu lệnh SQL sau:
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 ?Hai tham số liên kết '% gmail.com' và '% Pune' tạo ra một hàng từ dữ liệu gốc trong bảng địa chỉ như được hiển thị bên dưới -
[(1, 1, 'Shivajinagar Pune', '[email protected]')]Mục tiêu chính của API ánh xạ quan hệ đối tượng của SQLAlchemy là tạo điều kiện liên kết các lớp Python do người dùng định nghĩa với các bảng cơ sở dữ liệu và các đối tượng của các lớp đó với các hàng trong bảng tương ứng của chúng. Các thay đổi về trạng thái của đối tượng và hàng được khớp đồng bộ với nhau. SQLAlchemy cho phép thể hiện các truy vấn cơ sở dữ liệu theo các lớp do người dùng xác định và các mối quan hệ được xác định của chúng.
ORM được xây dựng trên ngôn ngữ biểu thức SQL. Đây là một kiểu sử dụng cấp cao và trừu tượng. Trên thực tế, ORM là cách sử dụng được áp dụng của Ngôn ngữ biểu thức.
Mặc dù một ứng dụng thành công có thể được tạo riêng bằng Object Relational Mapper, nhưng đôi khi một ứng dụng được xây dựng bằng ORM có thể sử dụng trực tiếp Ngôn ngữ Biểu thức khi cần có các tương tác cơ sở dữ liệu cụ thể.
Khai báo ánh xạ
Trước hết, hàm create_engine () được gọi để thiết lập một đối tượng động cơ sau đó được sử dụng để thực hiện các hoạt động SQL. Hàm có hai đối số, một là tên của cơ sở dữ liệu và một là tham số echo khi được đặt thành True sẽ tạo ra nhật ký hoạt động. Nếu nó không tồn tại, cơ sở dữ liệu sẽ được tạo. Trong ví dụ sau, một cơ sở dữ liệu SQLite được tạo.
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)Engine thiết lập một kết nối DBAPI thực với cơ sở dữ liệu khi một phương thức như Engine.execute () hoặc Engine.connect () được gọi. Sau đó, nó được sử dụng để phát ra SQLORM không sử dụng trực tiếp Engine; thay vào đó, nó được ORM sử dụng ở hậu trường.
Trong trường hợp ORM, quá trình cấu hình bắt đầu bằng cách mô tả các bảng cơ sở dữ liệu và sau đó bằng cách xác định các lớp sẽ được ánh xạ tới các bảng đó. Trong SQLAlchemy, hai tác vụ này được thực hiện cùng nhau. Điều này được thực hiện bằng cách sử dụng hệ thống Khai báo; các lớp được tạo bao gồm các chỉ thị để mô tả bảng cơ sở dữ liệu thực tế mà chúng được ánh xạ tới.
Một lớp cơ sở lưu trữ một danh mục các lớp và các bảng được ánh xạ trong hệ thống Khai báo. Đây được gọi là lớp cơ sở khai báo. Thường sẽ chỉ có một phiên bản của cơ sở này trong một mô-đun thường được nhập. Chức năng khai báo_base () được sử dụng để tạo lớp cơ sở. Hàm này được định nghĩa trong mô-đun sqlalchemy.ext.decl Compare.
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()Khi lớp cơ sở được khai báo, bất kỳ số lượng lớp được ánh xạ nào cũng có thể được định nghĩa theo nó. Mã sau xác định lớp của Khách hàng. Nó chứa bảng được ánh xạ tới, tên và kiểu dữ liệu của các cột trong đó.
class Customers(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)Một lớp trong Khai báo phải có __tablename__ thuộc tính và ít nhất một Columnlà một phần của khóa chính. Khai báo thay thế tất cảColumn các đối tượng có trình truy cập Python đặc biệt được gọi là descriptors. Quá trình này được gọi là thiết bị đo đạc cung cấp phương tiện để tham chiếu đến bảng trong ngữ cảnh SQL và cho phép duy trì và tải giá trị của các cột từ cơ sở dữ liệu.
Lớp được ánh xạ này giống như một lớp Python bình thường có các thuộc tính và phương thức theo yêu cầu.
Thông tin về lớp trong hệ thống Khai báo, được gọi là siêu dữ liệu bảng. SQLAlchemy sử dụng đối tượng Bảng để biểu diễn thông tin này cho một bảng cụ thể được tạo bởi Khai báo. Đối tượng Table được tạo theo các thông số kỹ thuật và được liên kết với lớp bằng cách xây dựng một đối tượng Mapper. Đối tượng ánh xạ này không được sử dụng trực tiếp nhưng được sử dụng nội bộ làm giao diện giữa lớp và bảng được ánh xạ.
Mỗi đối tượng Bảng là một thành viên của tập hợp lớn hơn được gọi là MetaData và đối tượng này có sẵn bằng cách sử dụng .metadatathuộc tính của lớp cơ sở khai báo. CácMetaData.create_all()là, chuyển vào Công cụ của chúng tôi như một nguồn kết nối cơ sở dữ liệu. Đối với tất cả các bảng chưa được tạo, nó đưa ra các câu lệnh CREATE TABLE cho cơ sở dữ liệu.
Base.metadata.create_all(engine)Tập lệnh hoàn chỉnh để tạo cơ sở dữ liệu và bảng cũng như ánh xạ lớp Python được đưa ra dưới đây:
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)Khi được thực thi, bảng điều khiển Python sẽ lặp lại sau khi biểu thức SQL được thực thi:
CREATE TABLE customers (
id INTEGER NOT NULL,
name VARCHAR,
address VARCHAR,
email VARCHAR,
PRIMARY KEY (id)
)Nếu chúng ta mở Sales.db bằng công cụ đồ họa SQLiteStudio, nó sẽ hiển thị bảng khách hàng bên trong nó với cấu trúc đã đề cập ở trên.
Để tương tác với cơ sở dữ liệu, chúng ta cần có được xử lý của nó. Một đối tượng phiên là xử lý cơ sở dữ liệu. Lớp phiên được định nghĩa bằng cách sử dụng sessionmaker () - một phương thức nhà máy phiên có thể cấu hình được liên kết với đối tượng engine đã tạo trước đó.
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)Đối tượng phiên sau đó được thiết lập bằng cách sử dụng hàm tạo mặc định của nó như sau:
session = Session()Một số phương thức thường xuyên được yêu cầu của lớp phiên được liệt kê dưới đây:
| Sr.No. | Phương pháp & Mô tả |
|---|---|
| 1 | begin() bắt đầu giao dịch trong phiên này |
| 2 | add() đặt một đối tượng trong phiên. Trạng thái của nó vẫn tồn tại trong cơ sở dữ liệu vào hoạt động xả tiếp theo |
| 3 | add_all() thêm một tập hợp các đối tượng vào phiên |
| 4 | commit() xóa tất cả các mục và bất kỳ giao dịch nào đang diễn ra |
| 5 | delete() đánh dấu một giao dịch là đã xóa |
| 6 | execute() thực thi một biểu thức SQL |
| 7 | expire() đánh dấu các thuộc tính của một phiên bản là đã lỗi thời |
| số 8 | flush() chuyển tất cả các thay đổi đối tượng vào cơ sở dữ liệu |
| 9 | invalidate() đóng phiên bằng cách sử dụng kết nối vô hiệu |
| 10 | rollback() quay trở lại giao dịch hiện tại đang diễn ra |
| 11 | close() Đóng phiên hiện tại bằng cách xóa tất cả các mục và kết thúc mọi giao dịch đang diễn ra |
Trong các chương trước của SQLAlchemy ORM, chúng ta đã học cách khai báo ánh xạ và tạo phiên. Trong chương này, chúng ta sẽ học cách thêm các đối tượng vào bảng.
Chúng tôi đã khai báo lớp Khách hàng đã được ánh xạ tới bảng khách hàng. Chúng ta phải khai báo một đối tượng của lớp này và liên tục thêm nó vào bảng bằng phương thức add () của đối tượng phiên.
c1 = Sales(name = 'Ravi Kumar', address = 'Station Road Nanded', email = '[email protected]')
session.add(c1)Lưu ý rằng giao dịch này đang chờ xử lý cho đến khi giao dịch tương tự được xóa bằng phương thức commit ().
session.commit()Sau đây là tập lệnh hoàn chỉnh để thêm bản ghi trong bảng khách hàng -
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()Để thêm nhiều bản ghi, chúng ta có thể sử dụng add_all() phương thức của lớp phiên.
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()Chế độ xem bảng của SQLiteStudio cho thấy rằng các bản ghi được thêm liên tục trong bảng khách hàng. Hình ảnh sau đây cho thấy kết quả:
Tất cả các câu lệnh SELECT được tạo bởi SQLAlchemy ORM đều được xây dựng bởi đối tượng Truy vấn. Nó cung cấp một giao diện chung, do đó các cuộc gọi liên tiếp trả về một đối tượng Truy vấn mới, bản sao của đối tượng cũ với các tiêu chí và tùy chọn bổ sung được liên kết với nó.
Các đối tượng truy vấn được tạo ban đầu bằng phương thức query () của Session như sau:
q = session.query(mapped class)Câu lệnh sau cũng tương đương với câu lệnh đã cho ở trên:
q = Query(mappedClass, session)Đối tượng truy vấn có phương thức all () trả về tập kết quả ở dạng danh sách các đối tượng. Nếu chúng tôi thực hiện nó trên bàn khách hàng của mình -
result = session.query(Customers).all()Câu lệnh này tương đương với biểu thức SQL sau:
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customersĐối tượng kết quả có thể được duyệt qua bằng vòng lặp For như bên dưới để lấy tất cả các bản ghi trong bảng khách hàng cơ bản. Đây là mã hoàn chỉnh để hiển thị tất cả các bản ghi trong bảng Khách hàng -
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)Bảng điều khiển Python hiển thị danh sách các bản ghi như bên dưới:
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]Đối tượng Truy vấn cũng có các phương thức hữu ích sau:
| Sr.No. | Phương pháp & Mô tả |
|---|---|
| 1 | add_columns() Nó thêm một hoặc nhiều biểu thức cột vào danh sách các cột kết quả được trả về. |
| 2 | add_entity() Nó thêm một thực thể được ánh xạ vào danh sách các cột kết quả sẽ được trả về. |
| 3 | count() Nó trả về một số hàng mà Truy vấn này sẽ trả về. |
| 4 | delete() Nó thực hiện một truy vấn xóa hàng loạt. Xóa các hàng được kết hợp bởi truy vấn này khỏi cơ sở dữ liệu. |
| 5 | distinct() Nó áp dụng một mệnh đề DISTINCT cho truy vấn và trả về Truy vấn mới kết quả. |
| 6 | filter() Nó áp dụng tiêu chí lọc nhất định cho bản sao của Truy vấn này, sử dụng các biểu thức SQL. |
| 7 | first() Nó trả về kết quả đầu tiên của Truy vấn này hoặc Không có nếu kết quả không chứa bất kỳ hàng nào. |
| số 8 | get() Nó trả về một thể hiện dựa trên mã định danh khóa chính đã cho cung cấp quyền truy cập trực tiếp vào bản đồ nhận dạng của Phiên sở hữu. |
| 9 | group_by() Nó áp dụng một hoặc nhiều tiêu chí GROUP BY cho truy vấn và trả về Truy vấn mới kết quả |
| 10 | join() Nó tạo ra một SQL JOIN dựa trên tiêu chí của đối tượng Truy vấn này và áp dụng chung, trả về Truy vấn mới kết quả. |
| 11 | one() Nó trả về chính xác một kết quả hoặc đưa ra một ngoại lệ. |
| 12 | order_by() Nó áp dụng một hoặc nhiều tiêu chí ORDER BY cho truy vấn và trả về Truy vấn mới kết quả. |
| 13 | update() Nó thực hiện một truy vấn cập nhật hàng loạt và cập nhật các hàng phù hợp với truy vấn này trong cơ sở dữ liệu. |
Trong chương này, chúng ta sẽ thấy cách sửa đổi hoặc cập nhật bảng với các giá trị mong muốn.
Để sửa đổi dữ liệu của một thuộc tính nhất định của bất kỳ đối tượng nào, chúng ta phải gán giá trị mới cho nó và cam kết các thay đổi để thay đổi được duy trì.
Hãy để chúng tôi tìm nạp một đối tượng từ bảng có mã định danh khóa chính, trong bảng Khách hàng của chúng tôi với ID = 2. Chúng ta có thể sử dụng phương thức get () của phiên như sau:
x = session.query(Customers).get(2)Chúng ta có thể hiển thị nội dung của đối tượng đã chọn với đoạn mã dưới đây:
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)Từ bảng khách hàng của chúng tôi, kết quả sau sẽ được hiển thị:
Name: Komal Pande Address: Koti, Hyderabad Email: [email protected]Bây giờ chúng ta cần cập nhật trường Địa chỉ bằng cách gán giá trị mới như dưới đây:
x.address = 'Banjara Hills Secunderabad'
session.commit()Thay đổi sẽ được phản ánh liên tục trong cơ sở dữ liệu. Bây giờ chúng ta tìm nạp đối tượng tương ứng với hàng đầu tiên trong bảng bằng cách sử dụngfirst() method như sau -
x = session.query(Customers).first()Điều này sẽ thực thi biểu thức SQL sau:
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 ?Các tham số liên kết sẽ lần lượt là LIMIT = 1 và OFFSET = 0 có nghĩa là hàng đầu tiên sẽ được chọn.
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)Bây giờ, đầu ra cho đoạn mã trên hiển thị hàng đầu tiên như sau:
Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]Bây giờ hãy thay đổi thuộc tính tên và hiển thị nội dung bằng đoạn mã dưới đây -
x.name = 'Ravi Shrivastava'
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)Đầu ra của đoạn mã trên là -
Name: Ravi Shrivastava Address: Station Road Nanded Email: [email protected]Mặc dù thay đổi được hiển thị, nó không được cam kết. Bạn có thể giữ vị trí cố định trước đó bằng cách sử dụngrollback() method với mã bên dưới.
session.rollback()
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)Nội dung gốc của bản ghi đầu tiên sẽ được hiển thị.
Đối với cập nhật hàng loạt, chúng tôi sẽ sử dụng phương thức update () của đối tượng Truy vấn. Hãy để chúng tôi thử và đưa ra một tiền tố, 'Mr.' để đặt tên trong mỗi hàng (ngoại trừ ID = 2). Câu lệnh update () tương ứng như sau:
session.query(Customers).filter(Customers.id! = 2).
update({Customers.name:"Mr."+Customers.name}, synchronize_session = False)The update() method requires two parameters as follows −
Một từ điển khóa-giá trị với khóa là thuộc tính cần cập nhật và giá trị là nội dung mới của thuộc tính.
thuộc tính syncnize_session đề cập đến chiến lược cập nhật các thuộc tính trong phiên. Các giá trị hợp lệ là false: vì không đồng bộ hóa phiên, tìm nạp: thực hiện truy vấn chọn trước khi cập nhật để tìm các đối tượng phù hợp với truy vấn cập nhật; và đánh giá: đánh giá các tiêu chí trên các đối tượng trong phiên.
Ba trong số 4 hàng trong bảng sẽ có tên bắt đầu bằng 'Mr.' Tuy nhiên, các thay đổi không được cam kết và do đó sẽ không được phản ánh trong chế độ xem bảng của SQLiteStudio. Nó sẽ chỉ được làm mới khi chúng tôi cam kết phiên.
Trong chương này, chúng ta sẽ thảo luận về cách áp dụng bộ lọc và các hoạt động bộ lọc nhất định cùng với mã của chúng.
Tập kết quả được đại diện bởi đối tượng Truy vấn có thể tuân theo các tiêu chí nhất định bằng cách sử dụng phương thức filter (). Cách sử dụng chung của phương pháp lọc như sau:
session.query(class).filter(criteria)Trong ví dụ sau, tập kết quả thu được bằng truy vấn SELECT trên bảng Khách hàng được lọc theo một điều kiện, (ID> 2) -
result = session.query(Customers).filter(Customers.id>2)Câu lệnh này sẽ chuyển thành biểu thức SQL sau:
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 > ?Vì tham số ràng buộc (?) Được cho là 2 nên chỉ những hàng có cột ID> 2 mới được hiển thị. Mã hoàn chỉnh được cung cấp bên dưới -
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)Đầu ra được hiển thị trong bảng điều khiển Python như sau:
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]Bây giờ, chúng ta sẽ tìm hiểu các hoạt động của bộ lọc với các mã và đầu ra tương ứng của chúng.
Bằng
Toán tử thông thường được sử dụng là == và nó áp dụng các tiêu chí để kiểm tra sự bình đẳng.
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 sẽ gửi biểu thức SQL sau:
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 = ?Đầu ra cho đoạn mã trên như sau:
ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: [email protected]Không bằng
Toán tử được sử dụng cho không bằng là! = Và nó cung cấp tiêu chí không bằng.
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)Biểu thức SQL kết quả là:
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 != ?Đầu ra cho các dòng mã trên như sau:
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]Giống
Phương thức like () tự tạo ra tiêu chí LIKE cho mệnh đề WHERE trong biểu thức 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)Mã SQLAlchemy ở trên tương đương với biểu thức SQL sau:
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 ?Và đầu ra cho đoạn mã trên là -
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]TRONG
Toán tử này kiểm tra xem giá trị cột có thuộc tập hợp các mục trong danh sách hay không. Nó được cung cấp bởi phương thức 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)Ở đây, biểu thức SQL được đánh giá bởi SQLite engine sẽ như sau:
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 (?, ?)Đầu ra cho đoạn mã trên như sau:
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]VÀ
Sự kết hợp này được tạo ra bởi putting multiple commas separated criteria in the filter or using and_() method như dưới đây -
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)Cả hai phương pháp trên đều dẫn đến biểu thức SQL tương tự:
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 ?Đầu ra cho các dòng mã trên là -
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]HOẶC LÀ
Sự kết hợp này được thực hiện bởi 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)Kết quả là, công cụ SQLite nhận được biểu thức SQL tương đương sau:
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 ?Đầu ra cho đoạn mã trên như sau:
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]Có một số phương thức của đối tượng Truy vấn ngay lập tức phát hành SQL và trả về một giá trị chứa kết quả cơ sở dữ liệu đã tải.
Dưới đây là tóm tắt ngắn gọn về danh sách trả về và các đại lượng vô hướng -
tất cả()
Nó trả về một danh sách. Dưới đây là dòng mã cho hàm all ().
session.query(Customers).all()Bảng điều khiển Python hiển thị biểu thức SQL sau được phát ra:
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customersĐầu tiên()
Nó áp dụng giới hạn là một và trả về kết quả đầu tiên dưới dạng vô hướng.
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 ?Các tham số giới hạn cho LIMIT là 1 và OFFSET là 0.
một()
Lệnh này tìm nạp đầy đủ tất cả các hàng và nếu không có chính xác một nhận dạng đối tượng hoặc hàng tổng hợp trong kết quả, nó sẽ gây ra lỗi.
session.query(Customers).one()Với nhiều hàng được tìm thấy -
MultipleResultsFound: Multiple rows were found for one()Không tìm thấy hàng nào -
NoResultFound: No row was found for one()Phương thức one () hữu ích cho các hệ thống mong đợi xử lý “không tìm thấy mục nào” so với “tìm thấy nhiều mục” khác nhau.
vô hướng()
Nó gọi phương thức one () và khi thành công trả về cột đầu tiên của hàng như sau:
session.query(Customers).filter(Customers.id == 3).scalar()Điều này tạo ra câu lệnh SQL sau:
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 = ?Trước đó, SQL văn bản sử dụng hàm text () đã được giải thích từ quan điểm của ngôn ngữ biểu đạt cốt lõi của SQLAlchemy. Bây giờ chúng ta sẽ thảo luận về nó từ quan điểm ORM.
Các chuỗi văn bản có thể được sử dụng linh hoạt với đối tượng Truy vấn bằng cách chỉ định việc sử dụng chúng với cấu trúc text (). Hầu hết các phương pháp áp dụng đều chấp nhận nó. Ví dụ, filter () và order_by ().
Trong ví dụ được đưa ra bên dưới, phương thức filter () dịch chuỗi “id <3” thành id WHERE <3
from sqlalchemy import text
for cust in session.query(Customers).filter(text("id<3")):
print(cust.name)Biểu thức SQL thô được tạo cho thấy chuyển đổi bộ lọc sang mệnh đề WHERE với mã được minh họa bên dưới:
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<3Từ dữ liệu mẫu của chúng tôi trong bảng Khách hàng, hai hàng sẽ được chọn và cột tên sẽ được in như sau:
Ravi Kumar
Komal PandeĐể chỉ định các tham số liên kết với SQL dựa trên chuỗi, hãy sử dụng dấu hai chấm và để chỉ định các giá trị, hãy sử dụng phương thức params ().
cust = session.query(Customers).filter(text("id = :value")).params(value = 1).one()SQL hiệu quả được hiển thị trên bảng điều khiển Python sẽ như dưới đây:
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 = ?Để sử dụng một câu lệnh hoàn toàn dựa trên chuỗi, một cấu trúc text () đại diện cho một câu lệnh hoàn chỉnh có thể được chuyển tới from_statement ().
session.query(Customers).from_statement(text("SELECT * FROM customers")).all()Kết quả của đoạn mã trên sẽ là một câu lệnh SELECT cơ bản như dưới đây:
SELECT * FROM customersRõ ràng, tất cả các bản ghi trong bảng khách hàng sẽ được chọn.
Cấu trúc text () cho phép chúng ta liên kết SQL văn bản của nó với biểu thức cột Core hoặc ORM được ánh xạ theo vị trí. Chúng ta có thể đạt được điều này bằng cách chuyển các biểu thức cột làm đối số vị trí cho phương thức 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()Các cột id và tên của tất cả các hàng sẽ được chọn mặc dù công cụ SQLite thực thi biểu thức sau được tạo bởi mã trên hiển thị tất cả các cột trong phương thức text () -
SELECT name, id, name, address, email FROM customersPhiên này mô tả việc tạo một bảng khác có liên quan đến một bảng đã tồn tại trong cơ sở dữ liệu của chúng tôi. Bảng khách hàng chứa dữ liệu chính của khách hàng. Bây giờ chúng ta cần tạo bảng hóa đơn có thể có bất kỳ số lượng hóa đơn nào thuộc về một khách hàng. Đây là trường hợp của một đến nhiều mối quan hệ.
Sử dụng tính năng khai báo, chúng tôi xác định bảng này cùng với lớp được ánh xạ của nó, Hóa đơn như được cung cấp bên dưới -
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)Thao tác này sẽ gửi một truy vấn CREATE TABLE tới SQLite engine như bên dưới:
CREATE TABLE invoices (
id INTEGER NOT NULL,
custid INTEGER,
invno INTEGER,
amount INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(custid) REFERENCES customers (id)
)Chúng tôi có thể kiểm tra bảng mới được tạo trong sales.db với sự trợ giúp của công cụ SQLiteStudio.
Lớp hóa đơn áp dụng cấu trúc ForeignKey trên thuộc tính custid. Chỉ thị này chỉ ra rằng các giá trị trong cột này phải được giới hạn là các giá trị có trong cột id trong bảng khách hàng. Đây là đặc điểm cốt lõi của cơ sở dữ liệu quan hệ và là “chất kết dính” biến tập hợp các bảng không được kết nối thành có các mối quan hệ chồng chéo phong phú.
Chỉ thị thứ hai, được gọi là mối quan hệ (), cho ORM biết rằng lớp Hóa đơn nên được liên kết với lớp Khách hàng bằng cách sử dụng thuộc tính Invoice.customer. Mối quan hệ () sử dụng các mối quan hệ khóa ngoài giữa hai bảng để xác định bản chất của mối liên kết này, xác định rằng nó là nhiều đối với một.
Một chỉ thị quan hệ () bổ sung được đặt trên lớp ánh xạ Khách hàng trong thuộc tính Customer.invoices. Tham số mối quan hệ.back_popates được chỉ định để tham chiếu đến các tên thuộc tính bổ sung, do đó mỗi mối quan hệ () có thể đưa ra quyết định thông minh về mối quan hệ giống như được biểu thị ngược lại. Ở một bên, Invoices.customer đề cập đến bản sao Hóa đơn và ở phía bên kia, Customer.invoices đề cập đến danh sách các bản sao của Khách hàng.
Hàm quan hệ là một phần của API quan hệ của gói ORM SQLAlchemy. Nó cung cấp mối quan hệ giữa hai lớp được ánh xạ. Điều này tương ứng với mối quan hệ cha-con hoặc bảng kết hợp.
Sau đây là các Mẫu mối quan hệ cơ bản được tìm thấy:
Một đến nhiều
Mối quan hệ Một đến Nhiều đề cập đến phụ huynh với sự trợ giúp của khóa ngoại trên bảng con. Mối quan hệ () sau đó được chỉ định trên cha mẹ, như tham chiếu đến một tập hợp các mục được đại diện bởi con. Tham số mối quan hệ.back_popates được sử dụng để thiết lập mối quan hệ hai chiều trong một-nhiều, trong đó mặt "ngược lại" là nhiều đối một.
Nhiều thành một
Mặt khác, quan hệ Many to One đặt khóa ngoại trong bảng cha để tham chiếu đến phần tử con. Mối quan hệ () được khai báo trên cha, nơi một thuộc tính giữ vô hướng mới sẽ được tạo. Ở đây một lần nữa, tham số Mối quan hệ.back_popates được sử dụng cho Hai chiều.
One To One
Mối quan hệ One To One về bản chất là mối quan hệ hai chiều. Cờ danh sách sử dụng cho biết vị trí của thuộc tính vô hướng thay vì tập hợp ở phía "nhiều" của mối quan hệ. Để chuyển đổi một-nhiều thành kiểu quan hệ một-một, hãy đặt tham số danh sách sử dụng thành false.
Nhiều nhiều
Mối quan hệ Many to Many được thiết lập bằng cách thêm một bảng kết hợp liên quan đến hai lớp bằng cách xác định các thuộc tính bằng khóa ngoại của chúng. Nó được chỉ ra bởi đối số phụ của mối quan hệ (). Thông thường, Bảng sử dụng đối tượng MetaData được liên kết với lớp cơ sở khai báo, để các chỉ thị ForeignKey có thể xác định vị trí các bảng từ xa cần liên kết. Tham số mối quan hệ.back_popates cho mỗi mối quan hệ () thiết lập mối quan hệ hai chiều. Cả hai mặt của mối quan hệ đều chứa một tập hợp.
Trong chương này, chúng ta sẽ tập trung vào các đối tượng liên quan trong SQLAlchemy ORM.
Bây giờ khi chúng ta tạo một đối tượng Khách hàng, một tập hợp hóa đơn trống sẽ hiện diện ở dạng Danh sách Python.
c1 = Customer(name = "Gopal Krishna", address = "Bank Street Hydarebad", email = "[email protected]")Thuộc tính hóa đơn của c1.invoices sẽ là một danh sách trống. Chúng ta có thể gán các mục trong danh sách dưới dạng:
c1.invoices = [Invoice(invno = 10, amount = 15000), Invoice(invno = 14, amount = 3850)]Hãy để chúng tôi cam kết đối tượng này với cơ sở dữ liệu bằng cách sử dụng đối tượng Session như sau:
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
session.add(c1)
session.commit()Thao tác này sẽ tự động tạo các truy vấn CHÈN cho khách hàng và bảng hóa đơn -
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)Bây giờ chúng ta hãy xem nội dung của bảng khách hàng và bảng hóa đơn trong chế độ xem bảng của SQLiteStudio -
Bạn có thể tạo đối tượng Khách hàng bằng cách cung cấp thuộc tính được ánh xạ của hóa đơn trong chính hàm tạo bằng cách sử dụng lệnh dưới đây:
c2 = [
Customer(
name = "Govind Pant",
address = "Gulmandi Aurangabad",
email = "[email protected]",
invoices = [Invoice(invno = 3, amount = 10000),
Invoice(invno = 4, amount = 5000)]
)
]Hoặc danh sách các đối tượng được thêm vào bằng cách sử dụng hàm add_all () của đối tượng phiên như hình dưới đây:
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()Bây giờ chúng ta có hai bảng, chúng ta sẽ xem cách tạo truy vấn trên cả hai bảng cùng một lúc. Để tạo một liên kết ngầm đơn giản giữa Khách hàng và Hóa đơn, chúng ta có thể sử dụng Query.filter () để cân bằng các cột liên quan của chúng với nhau. Dưới đây, chúng tôi tải các thực thể Khách hàng và Hóa đơn cùng một lúc bằng phương pháp này -
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))Biểu thức SQL do SQLAlchemy phát ra như sau:
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.custidVà kết quả của các dòng mã trên như sau:
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: 6000Cú pháp SQL JOIN thực tế có thể dễ dàng đạt được bằng cách sử dụng phương thức Query.join () như sau:
session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()Biểu thức SQL cho phép nối sẽ được hiển thị trên bảng điều khiển -
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 = ?Chúng ta có thể lặp lại kết quả bằng vòng lặp for -
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)Với 8500 làm tham số liên kết, kết quả sau được hiển thị:
4 Govind Kala 8 8500Query.join () biết cách nối giữa các bảng này vì chỉ có một khóa ngoại giữa chúng. Nếu không có khóa ngoại hoặc nhiều khóa ngoại hơn, Query.join () sẽ hoạt động tốt hơn khi một trong các dạng sau được sử dụng:
| query.join (Hóa đơn, id == Address.custid) | điều kiện rõ ràng |
| query.join (Customer.invoices) | xác định mối quan hệ từ trái sang phải |
| query.join (Hóa đơn, Khách hàng.invoices) | giống nhau, với mục tiêu rõ ràng |
| query.join ('hóa đơn') | tương tự, sử dụng một chuỗi |
Tương tự như vậy, hàm ngoài () có sẵn để đạt được kết nối bên ngoài bên trái.
query.outerjoin(Customer.invoices)Phương thức subquery () tạo ra một biểu thức SQL đại diện cho câu lệnh SELECT được nhúng trong một bí danh.
from sqlalchemy.sql import func
stmt = session.query(
Invoice.custid, func.count('*').label('invoice_count')
).group_by(Invoice.custid).subquery()Đối tượng stmt sẽ chứa một câu lệnh SQL như sau:
SELECT invoices.custid, count(:count_1) AS invoice_count FROM invoices GROUP BY invoices.custidKhi chúng ta có câu lệnh của mình, nó hoạt động giống như một cấu trúc Bảng. Các cột trên câu lệnh có thể truy cập thông qua một thuộc tính có tên là c như được hiển thị trong đoạn mã dưới đây:
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)Vòng lặp for ở trên hiển thị số lượng hóa đơn theo tên khôn ngoan như sau:
Arjun Pandit None
Gopal Krishna 2
Govind Pant 2
Govind Kala 2
Abdul Rahman 2Trong chương này, chúng ta sẽ thảo luận về các toán tử xây dựng trên các mối quan hệ.
__eq __ ()
Toán tử trên là một so sánh nhiều-một "bằng". Dòng mã cho toán tử này như hình dưới đây -
s = session.query(Customer).filter(Invoice.invno.__eq__(12))Truy vấn SQL tương đương cho dòng mã trên là:
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 __ ()
Toán tử này là một so sánh nhiều-một "không bằng". Dòng mã cho toán tử này như hình dưới đây -
s = session.query(Customer).filter(Invoice.custid.__ne__(2))Truy vấn SQL tương đương cho dòng mã trên được đưa ra bên dưới:
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 != ?chứa đựng()
Toán tử này được sử dụng cho một-nhiều tập hợp và dưới đây là mã cho hàm chứa () -
s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5]))Truy vấn SQL tương đương cho dòng mã trên là:
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 '%' + ? || '%')bất kì()
bất kỳ toán tử () nào được sử dụng cho các tập hợp như hình dưới đây -
s = session.query(Customer).filter(Customer.invoices.any(Invoice.invno==11))Truy vấn SQL tương đương cho dòng mã trên được hiển thị bên dưới:
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 = ?)có ()
Toán tử này được sử dụng cho các tham chiếu vô hướng như sau:
s = session.query(Invoice).filter(Invoice.customer.has(name = 'Arjun Pandit'))Truy vấn SQL tương đương cho dòng mã trên là:
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 = ?)Tải hào hứng làm giảm số lượng truy vấn. SQLAlchemy cung cấp các hàm tải mong muốn được gọi thông qua các tùy chọn truy vấn cung cấp các hướng dẫn bổ sung cho Truy vấn. Các tùy chọn này xác định cách tải các thuộc tính khác nhau thông qua phương thức Query.options ().
Tải truy vấn con
Chúng tôi muốn rằng các hóa đơn của Khách hàng sẽ được tải nhanh chóng. Tùy chọn orm.subqueryload () đưa ra câu lệnh SELECT thứ hai tải đầy đủ các tập hợp được liên kết với kết quả vừa tải. Tên “truy vấn con” khiến câu lệnh SELECT được xây dựng trực tiếp thông qua Truy vấn được sử dụng lại và nhúng dưới dạng truy vấn con vào một SELECT đối với bảng liên quan.
from sqlalchemy.orm import subqueryload
c1 = session.query(Customer).options(subqueryload(Customer.invoices)).filter_by(name = 'Govind Pant').one()Điều này dẫn đến hai biểu thức SQL sau:
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',)Để truy cập dữ liệu từ hai bảng, chúng ta có thể sử dụng chương trình dưới đây:
print (c1.name, c1.address, c1.email)
for x in c1.invoices:
print ("Invoice no : {}, Amount : {}".format(x.invno, x.amount))Kết quả của chương trình trên như sau:
Govind Pant Gulmandi Aurangabad [email protected]
Invoice no : 3, Amount : 10000
Invoice no : 4, Amount : 5000Tải tham gia
Hàm còn lại được gọi là orm.joinedload (). Điều này tạo ra một THAM GIA NGOÀI TRÁI. Đối tượng khách hàng tiềm năng cũng như đối tượng hoặc bộ sưu tập có liên quan được tải trong một bước.
from sqlalchemy.orm import joinedload
c1 = session.query(Customer).options(joinedload(Customer.invoices)).filter_by(name='Govind Pant').one()Điều này phát ra biểu thức sau cho kết quả tương tự như trên:
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 dẫn đến hai hàng, nhưng nó cung cấp lại một trường hợp của Khách hàng. Điều này là do Truy vấn áp dụng chiến lược "duy nhất", dựa trên nhận dạng đối tượng, cho các thực thể được trả về. Tải mong muốn đã tham gia có thể được áp dụng mà không ảnh hưởng đến kết quả truy vấn.
Subqueryload () thích hợp hơn để tải các tập hợp liên quan trong khi joinload () phù hợp hơn cho mối quan hệ nhiều-một.
Dễ dàng thực hiện thao tác xóa trên một bảng. Tất cả những gì bạn phải làm là xóa một đối tượng của lớp được ánh xạ khỏi một phiên và thực hiện hành động. Tuy nhiên, thao tác xóa trên nhiều bảng liên quan hơi khó.
Trong cơ sở dữ liệu sales.db của chúng tôi, các lớp Khách hàng và Hóa đơn được ánh xạ tới khách hàng và bảng hóa đơn với kiểu quan hệ từ một đến nhiều. Chúng tôi sẽ thử xóa đối tượng Khách hàng và xem kết quả.
Để tham khảo nhanh, dưới đây là định nghĩa của các lớp Khách hàng và Hóa đơn -
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")Chúng tôi thiết lập một phiên và lấy đối tượng Khách hàng bằng cách truy vấn đối tượng đó với ID chính bằng chương trình bên dưới -
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
x = session.query(Customer).get(2)Trong bảng mẫu của chúng tôi, x.name tình cờ là 'Gopal Krishna'. Hãy để chúng tôi xóa x này khỏi phiên và đếm sự xuất hiện của tên này.
session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()Biểu thức SQL kết quả sẽ trả về 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',) 0Tuy nhiên, các đối tượng Invoice liên quan của x vẫn còn đó. Nó có thể được xác minh bằng mã sau:
session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()Ở đây, 10 và 14 là số hóa đơn thuộc về khách hàng Gopal Krishna. Kết quả của truy vấn trên là 2, có nghĩa là các đối tượng liên quan chưa bị xóa.
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) 2Điều này là do SQLAlchemy không giả định việc xóa thác; chúng ta phải đưa ra một lệnh để xóa nó.
Để thay đổi hành vi, chúng tôi định cấu hình các tùy chọn tầng trên mối quan hệ User.addresses. Hãy để chúng tôi đóng phiên đang diễn ra, sử dụng new secure_base () và khai báo lại lớp Người dùng, thêm vào mối quan hệ địa chỉ bao gồm cả cấu hình tầng.
Thuộc tính cascade trong hàm mối quan hệ là một danh sách các quy tắc xếp tầng được phân tách bằng dấu phẩy xác định cách các hoạt động của Session nên được “xếp tầng” từ cha sang con. Theo mặc định, nó là False, có nghĩa là nó là "save-update, merge".
Các tầng có sẵn như sau:
- save-update
- merge
- expunge
- delete
- delete-orphan
- refresh-expire
Tùy chọn thường được sử dụng là "all, delete-orphan" để chỉ ra rằng các đối tượng liên quan sẽ theo sau cùng với đối tượng mẹ trong mọi trường hợp và bị xóa khi hủy liên kết.
Do đó, lớp Khách hàng được khai báo lại được hiển thị bên dưới:
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"
)Hãy để chúng tôi xóa Khách hàng có tên Gopal Krishna bằng chương trình bên dưới và xem số lượng các đối tượng Hóa đơn có liên quan của nó -
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()Số lượng bây giờ là 0 với SQL sau được phát ra bởi tập lệnh trên:
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 relationshipgiữa hai bảng đạt được bằng cách thêm một bảng liên kết sao cho nó có hai khóa ngoại - một từ khóa chính của mỗi bảng. Hơn nữa, các lớp ánh xạ tới hai bảng có một thuộc tính với tập hợp các đối tượng của các bảng kết hợp khác được gán làm thuộc tính phụ của hàm quan hệ ().
Với mục đích này, chúng ta sẽ tạo một cơ sở dữ liệu SQLite (mycollege.db) với hai bảng - phòng ban và nhân viên. Ở đây, chúng tôi giả định rằng một nhân viên là một bộ phận của nhiều bộ phận và một bộ phận có nhiều hơn một nhân viên. Điều này tạo thành mối quan hệ nhiều-nhiều.
Định nghĩa về các lớp Nhân viên và Phòng ban được ánh xạ tới phòng ban và bảng nhân viên như sau:
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')Bây giờ chúng ta định nghĩa một lớp Liên kết. Nó được liên kết với bảng liên kết và chứa các thuộc tính Department_id và worker_id tương ứng tham chiếu đến các khóa chính của phòng ban và bảng nhân viên.
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)Ở đây, chúng ta phải lưu ý rằng lớp Phòng ban có thuộc tính nhân viên liên quan đến lớp nhân viên. Thuộc tính phụ của hàm quan hệ được gán một liên kết làm giá trị của nó.
Tương tự, lớp Nhân viên có thuộc tính phòng ban liên quan đến lớp Phòng ban. Thuộc tính phụ của hàm quan hệ được gán một liên kết làm giá trị của nó.
Tất cả ba bảng này được tạo khi câu lệnh sau được thực thi:
Base.metadata.create_all(engine)Bảng điều khiển Python phát ra các truy vấn CREATE TABLE sau:
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)
)Chúng tôi có thể kiểm tra điều này bằng cách mở mycollege.db bằng SQLiteStudio như được hiển thị trong ảnh chụp màn hình được cung cấp bên dưới:
Tiếp theo, chúng ta tạo ba đối tượng của lớp Phòng ban và ba đối tượng của lớp Nhân viên như hình dưới đây:
d1 = Department(name = "Accounts")
d2 = Department(name = "Sales")
d3 = Department(name = "Marketing")
e1 = Employee(name = "John")
e2 = Employee(name = "Tony")
e3 = Employee(name = "Graham")Mỗi bảng có một thuộc tính collection có phương thức append (). Chúng ta có thể thêm các đối tượng Nhân viên vào bộ sưu tập Nhân viên của đối tượng Bộ phận. Tương tự, chúng ta có thể thêm các đối tượng Phòng ban vào thuộc tính thu thập phòng ban của các đối tượng Nhân viên.
e1.departments.append(d1)
e2.departments.append(d3)
d1.employees.append(e3)
d2.employees.append(e2)
d3.employees.append(e1)
e3.departments.append(d2)Tất cả những gì chúng ta phải làm bây giờ là thiết lập một đối tượng phiên, thêm tất cả các đối tượng vào nó và thực hiện các thay đổi như hình dưới đây -
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()Các câu lệnh SQL sau sẽ được phát ra trên bảng điều khiển 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))Để kiểm tra ảnh hưởng của các thao tác trên, hãy sử dụng SQLiteStudio và xem dữ liệu trong các bảng phòng ban, nhân viên và liên kết -
Để hiển thị dữ liệu, hãy chạy câu lệnh truy vấn sau:
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))Theo dữ liệu được điền trong ví dụ của chúng tôi, đầu ra sẽ được hiển thị như bên dưới:
Department: Accounts Name: John
Department: Accounts Name: Graham
Department: Sales Name: Graham
Department: Sales Name: Tony
Department: Marketing Name: John
Department: Marketing Name: TonySQLAlchemy sử dụng hệ thống phương ngữ để giao tiếp với nhiều loại cơ sở dữ liệu khác nhau. Mỗi cơ sở dữ liệu có một trình bao bọc DBAPI tương ứng. Tất cả các phương ngữ đều yêu cầu phải cài đặt trình điều khiển DBAPI thích hợp.
Các phương ngữ sau được bao gồm trong API SQLAlchemy -
- Firebird
- Microsoft SQL Server
- MySQL
- Oracle
- PostgreSQL
- SQL
- Sybase
Đối tượng Engine dựa trên URL được tạo bởi hàm create_engine (). Các URL này có thể bao gồm tên người dùng, mật khẩu, tên máy chủ và tên cơ sở dữ liệu. Có thể có các đối số từ khóa tùy chọn cho cấu hình bổ sung. Trong một số trường hợp, đường dẫn tệp được chấp nhận và trong những trường hợp khác, "tên nguồn dữ liệu" thay thế các phần "máy chủ lưu trữ" và "cơ sở dữ liệu". Dạng điển hình của URL cơ sở dữ liệu như sau:
dialect+driver://username:password@host:port/databasePostgreSQL
Phương ngữ PostgreSQL sử dụng psycopg2làm DBAPI mặc định. pg8000 cũng có sẵn dưới dạng thay thế Python thuần túy như hình dưới đây:
# 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
Phương ngữ MySQL sử dụng mysql-pythonlàm DBAPI mặc định. Có nhiều MySQL DBAPI có sẵn, chẳng hạn như MySQL-connector-python như sau:
# 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')Oracle
Phương ngữ Oracle sử dụng cx_oracle làm DBAPI mặc định như sau:
engine = create_engine('oracle://scott:[email protected]:1521/sidname')
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')Microsoft SQL Server
Phương ngữ SQL Server sử dụng pyodbclàm DBAPI mặc định. pymssql cũng có sẵn.
# pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')
# pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')SQLite
SQLite kết nối với cơ sở dữ liệu dựa trên tệp, sử dụng mô-đun tích hợp sẵn trong Python sqlite3theo mặc định. Khi SQLite kết nối với các tệp cục bộ, định dạng URL hơi khác. Phần "tệp" của URL là tên tệp của cơ sở dữ liệu. Đối với đường dẫn tệp tương đối, điều này yêu cầu ba dấu gạch chéo như hình dưới đây:
engine = create_engine('sqlite:///foo.db')Và đối với đường dẫn tệp tuyệt đối, ba dấu gạch chéo được theo sau bởi đường dẫn tuyệt đối như dưới đây:
engine = create_engine('sqlite:///C:\\path\\to\\foo.db')Để sử dụng cơ sở dữ liệu SQLite: memory:, hãy chỉ định một URL trống như được cung cấp bên dưới -
engine = create_engine('sqlite://')Phần kết luận
Trong phần đầu tiên của hướng dẫn này, chúng ta đã học cách sử dụng Ngôn ngữ biểu thức để thực thi các câu lệnh SQL. Ngôn ngữ biểu thức nhúng các cấu trúc SQL trong mã Python. Trong phần thứ hai, chúng ta đã thảo luận về khả năng ánh xạ quan hệ đối tượng của SQLAlchemy. API ORM ánh xạ các bảng SQL với các lớp Python.