Python - Database Access
Database Access in Python
Truy cập cơ sở dữ liệu trong Python được sử dụng để tương tác với các cơ sở dữ liệu, cho phép các ứng dụng lưu trữ, truy xuất, cập nhật và quản lý dữ liệu một cách nhất quán. Nhiều hệ quản trị cơ sở dữ liệu quan hệ (RDBMS) được hỗ trợ cho các tác vụ này, mỗi hệ thống yêu cầu các gói Python cụ thể để kết nối.
- GadFly
- MySQL
- PostgreSQL
- Microsoft SQL Server
- Informix
- Oracle
- Sybase
- SQLite
- and many more...
Dữ liệu đầu vào và dữ liệu được tạo ra trong quá trình thực thi của một chương trình được lưu trữ trong RAM. Nếu muốn lưu trữ một cách bền vững, nó cần được lưu trữ trong các bảng cơ sở dữ liệu.
Cơ sở dữ liệu quan hệ sử dụng SQL (Ngôn ngữ truy vấn có cấu trúc) để thực hiện các thao tác INSERT/DELETE/UPDATE trên các bảng cơ sở dữ liệu. Tuy nhiên, việc triển khai SQL khác nhau giữa các loại cơ sở dữ liệu. Điều này gây ra các vấn đề về tính không tương thích. Các hướng dẫn SQL cho một cơ sở dữ liệu không khớp với cơ sở dữ liệu khác.
DB-API (Database API)
Để giải quyết vấn đề tương thích này, Đề xuất Cải tiến Python (PEP) 249 đã giới thiệu một giao diện chuẩn hóa được gọi là DB-API. Giao diện này cung cấp một khung nhất quán cho các driver cơ sở dữ liệu, đảm bảo hành vi đồng nhất giữa các hệ thống cơ sở dữ liệu khác nhau. Nó đơn giản hóa quá trình chuyển đổi giữa các cơ sở dữ liệu khác nhau bằng cách thiết lập một bộ quy tắc và phương thức chung.
Using SQLite with Python
Thư viện tiêu chuẩn của Python bao gồm mô-đun sqlite3 , một trình điều khiển tương thích với DB_API cho cơ sở dữ liệu SQLite3. Nó phục vụ như một triển khai tham chiếu cho DB-API. Đối với các loại cơ sở dữ liệu khác, bạn sẽ phải cài đặt gói Python liên quan.
| Database | Python Package |
|---|---|
| Oracle | cx_oracle, pyodbc |
| SQL Server | pymssql, pyodbc |
| PostgreSQL | psycopg2 |
| MySQL | MySQL Connector/Python, pymysql |
Working with SQLite
Sử dụng SQLite với Python rất dễ dàng nhờ vào mô-đun sqlite3 tích hợp sẵn. Quá trình bao gồm −
-
Connection Establishment − Tạo một đối tượng kết nối bằng cách sử dụng sqlite3.connect(), cung cấp các thông tin cần thiết như tên máy chủ, cổng, tên người dùng và mật khẩu.
-
Transaction Management − Đối tượng kết nối quản lý các hoạt động cơ sở dữ liệu, bao gồm mở, đóng và kiểm soát giao dịch (cam kết hoặc hoàn tác giao dịch).
-
Cursor Object − Lấy đối tượng con trỏ từ kết nối để thực hiện các truy vấn SQL. Con trỏ đóng vai trò là cổng vào cho các thao tác CRUD (Tạo, Đọc, Cập nhật, Xóa) trên cơ sở dữ liệu.
Trong hướng dẫn này, chúng ta sẽ học cách truy cập cơ sở dữ liệu bằng Python, cách lưu trữ dữ liệu của các đối tượng Python trong cơ sở dữ liệu SQLite, và cách truy xuất dữ liệu từ cơ sở dữ liệu SQLite và xử lý nó bằng chương trình Python.
The sqlite3 Module
SQLite là một cơ sở dữ liệu quan hệ nhẹ, dựa trên tệp và không có máy chủ. Nó không yêu cầu cài đặt và không cần thông tin xác thực như tên người dùng và mật khẩu để truy cập cơ sở dữ liệu.
Mô-đun sqlite3 của Python chứa triển khai DB-API cho cơ sở dữ liệu SQLite. Nó được viết bởi Gerhard Häring. Hãy cùng tìm hiểu cách sử dụng mô-đun sqlite3 để truy cập cơ sở dữ liệu với Python.
Hãy bắt đầu bằng cách nhập sqlite3 và kiểm tra phiên bản của nó.
>>> import sqlite3 >>> sqlite3.sqlite_version '3.39.4'
The Connection Object
Một đối tượng kết nối được thiết lập bởi hàm connect() trong mô-đun sqlite3. Tham số vị trí đầu tiên của hàm này là một chuỗi đại diện cho đường dẫn (tương đối hoặc tuyệt đối) đến một tệp cơ sở dữ liệu SQLite. Hàm này trả về một đối tượng kết nối tham chiếu đến cơ sở dữ liệu.
>>> conn=sqlite3.connect('testdb.sqlite3')
>>> type(conn)
<class 'sqlite3.Connection'>
Nhiều phương thức được định nghĩa trong lớp kết nối. Một trong số đó là phương thức cursor() trả về một đối tượng con trỏ, mà chúng ta sẽ tìm hiểu trong phần tiếp theo. Kiểm soát giao dịch được thực hiện thông qua các phương thức commit() và rollback() của đối tượng kết nối. Lớp kết nối có các phương thức quan trọng để định nghĩa các hàm tùy chỉnh và các tổng hợp được sử dụng trong các truy vấn SQL.
The Cursor Object
Tiếp theo, chúng ta cần lấy đối tượng con trỏ từ đối tượng kết nối. Nó là tay cầm của bạn để truy cập cơ sở dữ liệu khi thực hiện bất kỳ thao tác CRUD nào trên cơ sở dữ liệu. Phương thức cursor() trên đối tượng kết nối trả về đối tượng con trỏ.
>>> cur=conn.cursor() >>> type(cur) <class 'sqlite3.Cursor'>
Chúng ta bây giờ có thể thực hiện tất cả các thao tác truy vấn SQL, với sự trợ giúp của phương thức execute() có sẵn cho đối tượng con trỏ. Phương thức này cần một đối số kiểu chuỗi, mà phải là một câu lệnh SQL hợp lệ.
Creating a Database Table
Chúng ta sẽ thêm bảng Employee vào cơ sở dữ liệu 'testdb.sqlite3' mà chúng ta vừa tạo. Trong đoạn mã sau, chúng ta gọi phương thức execute() của đối tượng con trỏ, cung cấp cho nó một chuỗi chứa câu lệnh CREATE TABLE bên trong.
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry='''
CREATE TABLE Employee (
EmpID INTEGER PRIMARY KEY AUTOINCREMENT,
FIRST_NAME TEXT (20),
LAST_NAME TEXT(20),
AGE INTEGER,
SEX TEXT(1),
INCOME FLOAT
);
'''
try:
cur.execute(qry)
print ('Table created successfully')
except:
print ('error in creating table')
conn.close()
Khi chương trình trên được chạy, cơ sở dữ liệu với bảng Nhân viên sẽ được tạo trong thư mục làm việc hiện tại.
Chúng ta có thể xác minh bằng cách liệt kê các bảng trong cơ sở dữ liệu này trong bảng điều khiển SQLite.
sqlite> .open mydb.sqlite sqlite> .tables Employee
INSERT Operation
Phép toán INSERT là cần thiết khi bạn muốn tạo các bản ghi của mình vào một bảng cơ sở dữ liệu.
Example
Ví dụ sau đây thực thi câu lệnh SQL INSERT để tạo một bản ghi trong bảng EMPLOYEE −
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="""INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
cur.execute(qry)
conn.commit()
print ('Record inserted successfully')
except:
conn.rollback()
print ('error in INSERT operation')
conn.close()
Bạn cũng có thể sử dụng kỹ thuật thay thế tham số để thực hiện truy vấn INSERT như sau −
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="""INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES (?, ?, ?, ?, ?)"""
try:
cur.execute(qry, ('Makrand', 'Mohan', 21, 'M', 5000))
conn.commit()
print ('Record inserted successfully')
except Exception as e:
conn.rollback()
print ('error in INSERT operation')
conn.close()
READ Operation
Hoạt động READ trên bất kỳ cơ sở dữ liệu nào có nghĩa là truy xuất một số thông tin hữu ích từ cơ sở dữ liệu.
Khi kết nối cơ sở dữ liệu được thiết lập, bạn đã sẵn sàng để thực hiện truy vấn vào cơ sở dữ liệu này. Bạn có thể sử dụng phương thức fetchone() để lấy một bản ghi đơn lẻ hoặc phương thức fetchall() để lấy nhiều giá trị từ một bảng cơ sở dữ liệu.
-
fetchone() − Nó lấy hàng tiếp theo của một tập kết quả truy vấn. Tập kết quả là một đối tượng được trả về khi một đối tượng con trỏ được sử dụng để truy vấn một bảng.
-
fetchall() − Nó lấy tất cả các hàng trong một tập kết quả. Nếu một số hàng đã được trích xuất từ tập kết quả, thì nó sẽ lấy các hàng còn lại từ tập kết quả.
-
rowcount − Đây là một thuộc tính chỉ đọc và trả về số lượng hàng đã bị ảnh hưởng bởi phương thức execute().
Example
Trong đoạn mã sau, đối tượng con trỏ thực hiện truy vấn SELECT * FROM EMPLOYEE. Tập kết quả được lấy bằng phương thức fetchall(). Chúng ta in tất cả các bản ghi trong tập kết quả bằng một vòng lặp for .
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="SELECT * FROM EMPLOYEE"
try:
# Execute the SQL command
cur.execute(qry)
# Fetch all the rows in a list of lists.
results = cur.fetchall()
for row in results:
fname = row[1]
lname = row[2]
age = row[3]
sex = row[4]
income = row[5]
# Now print fetched result
print ("fname={},lname={},age={},sex={},income={}".format(fname, lname, age, sex, income ))
except Exception as e:
print (e)
print ("Error: unable to fecth data")
conn.close()
Nó sẽ tạo ra output −
fname=Mac,lname=Mohan,age=20,sex=M,income=2000.0 fname=Makrand,lname=Mohan,age=21,sex=M,income=5000.0
Update Operation
Cập nhật (UPDATE) trên bất kỳ cơ sở dữ liệu nào có nghĩa là cập nhật một hoặc nhiều bản ghi, đã có sẵn trong cơ sở dữ liệu.
Thủ tục sau đây cập nhật tất cả các bản ghi có thu nhập = 2000. Ở đây, chúng ta tăng thu nhập lên 1000.
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="UPDATE EMPLOYEE SET INCOME = INCOME+1000 WHERE INCOME=?"
try:
# Execute the SQL command
cur.execute(qry, (1000,))
# Fetch all the rows in a list of lists.
conn.commit()
print ("Records updated")
except Exception as e:
print ("Error: unable to update data")
conn.close()
DELETE Operation
Phép toán DELETE là cần thiết khi bạn muốn xóa một số bản ghi khỏi cơ sở dữ liệu của mình. Dưới đây là quy trình để xóa tất cả các bản ghi từ EMPLOYEE nơi THU NHẬP nhỏ hơn 2000.
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="DELETE FROM EMPLOYEE WHERE INCOME<?"
try:
# Execute the SQL command
cur.execute(qry, (2000,))
# Fetch all the rows in a list of lists.
conn.commit()
print ("Records deleted")
except Exception as e:
print ("Error: unable to delete data")
conn.close()
Performing Transactions
Giao dịch là một cơ chế đảm bảo tính nhất quán của dữ liệu. Giao dịch có bốn thuộc tính sau đây −
-
Atomicity − Hoặc một giao dịch hoàn tất hoặc không có gì xảy ra cả.
-
Consistency − Một giao dịch phải bắt đầu trong một trạng thái nhất quán và rời khỏi hệ thống trong một trạng thái nhất quán.
-
Isolation − Các kết quả trung gian của một giao dịch không thể nhìn thấy bên ngoài giao dịch hiện tại.
-
Durability − Một khi giao dịch đã được cam kết, các hiệu ứng sẽ tồn tại, ngay cả sau khi hệ thống gặp sự cố.
API DB Python 2.0 cung cấp hai phương thức để thực hiện commit hoặc rollback một giao dịch.
Example
Bạn đã biết cách thực hiện giao dịch. Dưới đây là một ví dụ tương tự −
# Prepare SQL query to DELETE required records sql = "DELETE FROM EMPLOYEE WHERE AGE > ?" try: # Execute the SQL command cursor.execute(sql, (20,)) # Commit your changes in the database db.commit() except: # Rollback in case there is any error db.rollback()
COMMIT Operation
Commit là một thao tác, cho phép cơ sở dữ liệu xác nhận các thay đổi, và sau thao tác này, không thể hoàn tác bất kỳ thay đổi nào.
Dưới đây là một ví dụ đơn giản để gọi phương thức commit.
db.commit()
ROLLBACK Operation
Nếu bạn không hài lòng với một hoặc nhiều thay đổi và bạn muốn hoàn tác những thay đổi đó hoàn toàn, hãy sử dụng phương thức rollback().
Dưới đây là một ví dụ đơn giản để gọi phương thức rollback().
db.rollback()
The PyMySQL Module
PyMySQL là một giao diện để kết nối đến máy chủ cơ sở dữ liệu MySQL từ Python. Nó triển khai API Cơ sở dữ liệu Python v2.0 và chứa một thư viện khách MySQL thuần túy bằng Python. Mục tiêu của PyMySQL là trở thành một sự thay thế có thể thay thế trực tiếp cho MySQLdb.
Installing PyMySQL
Trước khi tiếp tục, bạn hãy chắc chắn rằng bạn đã cài đặt PyMySQL trên máy của mình. Chỉ cần nhập đoạn mã sau vào script Python của bạn và thực thi nó −
import PyMySQL
Nếu nó tạo ra kết quả sau, thì điều đó có nghĩa là mô-đun MySQLdb chưa được cài đặt −
Traceback (most recent call last):
File "test.py", line 3, in <module>
Import PyMySQL
ImportError: No module named PyMySQL
Bản phát hành ổn định cuối cùng có sẵn trên PyPI và có thể được cài đặt bằng pip −
pip install PyMySQL
Note − Hãy chắc chắn rằng bạn có quyền root để cài đặt mô-đun trên.
MySQL Database Connection
Trước khi kết nối đến cơ sở dữ liệu MySQL, hãy đảm bảo các điểm sau:
-
Bạn đã tạo một cơ sở dữ liệu có tên là TESTDB.
-
Bạn đã tạo một bảng EMPLOYEE trong TESTDB.
-
Bảng này có các trường FIRST_NAME, LAST_NAME, AGE, SEX và INCOME.
-
ID người dùng "testuser" và mật khẩu "test123" được thiết lập để truy cập TESTDB.
-
Mô-đun PyMySQL của Python đã được cài đặt đúng cách trên máy của bạn.
-
Bạn đã hoàn thành hướng dẫn MySQL để hiểu các kiến thức cơ bản về MySQL.
Example
Để sử dụng cơ sở dữ liệu MySQL thay vì cơ sở dữ liệu SQLite trong các ví dụ trước, chúng ta cần thay đổi hàm connect() như sau −
import PyMySQL
# Open database connection
db = PyMySQL.connect("localhost","testuser","test123","TESTDB" )
Ngoài sự thay đổi này, mọi thao tác cơ sở dữ liệu có thể được thực hiện mà không gặp khó khăn.
Handling Errors
Có nhiều nguồn gốc của lỗi. Một vài ví dụ là lỗi cú pháp trong một câu lệnh SQL được thực thi, lỗi kết nối, hoặc gọi phương thức fetch cho một handle câu lệnh đã bị hủy hoặc đã hoàn thành.
API cơ sở dữ liệu (DB API) định nghĩa một số lỗi cần tồn tại trong mỗi mô-đun cơ sở dữ liệu. Bảng sau liệt kê các ngoại lệ này.
| Sr.No. | Exception & Description |
|---|---|
| 1 | Warning Used for non-fatal issues. Must subclass StandardError. |
| 2 | Error Base class for errors. Must subclass StandardError. |
| 3 | InterfaceError Used for errors in the database module, not the database itself. Must subclass Error. |
| 4 | DatabaseError Used for errors in the database. Must subclass Error. |
| 5 | DataError Subclass of DatabaseError that refers to errors in the data. |
| 6 | OperationalError Subclass of DatabaseError that refers to errors such as the loss of a connection to the database. These errors are generally outside of the control of the Python scripter. |
| 7 | IntegrityError Subclass of DatabaseError for situations that would damage the relational integrity, such as uniqueness constraints or foreign keys. |
| 8 | InternalError Subclass of DatabaseError that refers to errors internal to the database module, such as a cursor no longer being active. |
| 9 | ProgrammingError Subclass of DatabaseError that refers to errors such as a bad table name and other things that can safely be blamed on you. |
| 10 | NotSupportedError Subclass of DatabaseError that refers to trying to call unsupported functionality. |