Trong phần này chúng ta sẽ làm việc với cơ sở dữ liệu SQLite.
Python cung cấp sẵn module sqlite3 hỗ trợ kết nối và thao tác với CSDL SQLite.
C:\User\PhoCode>python Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> import sqlite3 >>> sqlite3.version '2.6.0' >>> sqlite3.sqlite_version '3.8.11'
Chúng ta có thể kiểm tra một số thông tin về module sqlite3.
Trước khi bắt đầu chúng ta cần có một CSDL để test.
C:\User\PhoCode>sqlite3 test.db SQLite version 3.7.13 2012-06-11 02:05:22 Enter ".help" for instructions Enter SQL statements terminated with a ";"
Chúng ta tạo CSDL test.db.
Xem phiên bản SQLite
Trong ví dụ dưới đây chúng ta xem phiên bản CSDL SQLite.
import sqlite3 as lite
import sys
import os
con = None
try:
path = os.path.dirname(__file__) + "\\test.db"
con = lite.connect(path)
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
data = cur.fetchone()
print ("SQLite version: %s" % data)
except lite.Error as e:
print ("Error %s:" % e.args[0])
sys.exit(1)
finally:
if con:
con.close()
Chúng ta kết nối với CSDL test.db và thực thi câu truy vấn lấy thông tin về phiên bản SQLite đang sử dụng.
import sqlite3 as lite
Đầu tiên chúng ta import module sqlite3.
con = None
Biến con là biến lưu trữ đối tượng Connection khi chúng ta kết nối CSDL.
path = os.path.dirname(__file__) + "\\test.db" con = lite.connect(path)
Để kết nối đến CSDL thì chúng ta dùng phương thức connect(), phương thức này trả về một đối tượng Connection.
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
Sau khi đã có đối tượng Connection, chúng ta lấy một đối tượng Cursor, đối tượng này làm nhiệm vụ duyệt qua các bản ghi trong tập dữ liệu được lấy về và thực thi các câu truy vấn. Để thực thi một câu truy vấn thì chúng ta dùng phương thức execute().
data = cur.fetchone()
Phương thức fetchone() lấy về dòng đầu tiên của bảng dữ liệu trả về.
print ("SQLite version: %s" % data)
Chúng ta in dòng dữ liệu đó ra màn hình.
finally:
if con:
con.close()
Sau khi đã hoàn tất công việc thì chúng ta đóng kết nối tới CSDL với phương thức close().
SQLite version: 3.8.11
Trong ví dụ dưới đây, chúng ta cũng lấy phiên bản SQLite nhưng sử dụng từ khóa with.
import sqlite3 as lite
import sys
import os
path = os.path.dirname(__file__) + "\\test.db"
con = lite.connect(path)
with con:
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
data = cur.fetchone()
print ("SQLite version: %s" % data)
Dùng từ khóa with có tác dụng làm cho code dễ chịu hơn và các câu lệnh SQL có liên quan đến việc cập nhật dữ liệu như INSERT, UPDATE, DELETE… sẽ tự động được thực thi (nếu không thì bạn phải gọi thêm phương thức commit() thì mới thực sự thực thi câu truy vấn lên CSDL).
with con:
Python sẽ tự động xử lý exception và tự động ngắt kết nối CSDL khi không dùng nữa nếu có từ khóa with.
INSERT
Chúng ta sẽ thực thi câu truy vấn INSERT.
import sqlite3 as lite
import sys
import os
path = os.path.dirname(__file__) + "\\test.db"
con = lite.connect(path)
with con:
cur = con.cursor()
cur.execute("CREATE TABLE Cars(Id INT, Name TEXT, Price INT)")
cur.execute("INSERT INTO Cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO Cars VALUES(2,'Mercedes',57127)")
cur.execute("INSERT INTO Cars VALUES(3,'Skoda',9000)")
cur.execute("INSERT INTO Cars VALUES(4,'Volvo',29000)")
cur.execute("INSERT INTO Cars VALUES(5,'Bentley',350000)")
cur.execute("INSERT INTO Cars VALUES(6,'Citroen',21000)")
cur.execute("INSERT INTO Cars VALUES(7,'Hummer',41400)")
cur.execute("INSERT INTO Cars VALUES(8,'Volkswagen',21600)")
Đoạn code trên tạo bảng Cars và insert 8 dòng dữ liệu vào bảng này.
cur.execute("CREATE TABLE Cars(Id INT, Name TEXT, Price INT)")
Bảng Cars sẽ có 3 cột là Id, Name và Price.
cur.execute("INSERT INTO Cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO Cars VALUES(2,'Mercedes',57127)")
Chúng ta chỉ cần dùng phương thức execute() để thực thi các câu lệnh SQL. Khi dùng từ khóa with thì các câu lệnh này sẽ được thực thi ngay trên CSDL.
sqlite> .mode column sqlite> .headers on sqlite> SELECT * FROM Cars; Id Name Price ---------- ---------- ---------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
Các câu lệnh như UPDATE, DELETE… bạn cũng làm tương tự.
Phương thức executemany()
Phương thức executemany() tiện lợi hơn bằng cách thực thi nhiều câu lệnh cùng một lúc.
import sqlite3 as lite
import sys
import os
cars = (
(1, 'Audi', 52642),
(2, 'Mercedes', 57127),
(3, 'Skoda', 9000),
(4, 'Volvo', 29000),
(5, 'Bentley', 350000),
(6, 'Hummer', 41400),
(7, 'Volkswagen', 21600)
)
path = os.path.dirname(__file__) + "\\test.db"
con = lite.connect(path)
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS Cars")
cur.execute("CREATE TABLE Cars(Id INT, Name TEXT, Price INT)")
cur.executemany("INSERT INTO Cars VALUES(?, ?, ?)", cars)
Chúng ta xóa bảng và tạo lại bảng Cars.
cur.execute("DROP TABLE IF EXISTS Cars")
cur.execute("CREATE TABLE Cars(Id INT, Name TEXT, Price INT)")
Đầu tiên chúng ta kiểm tra xem bảng Cars đã tồn tại chưa, nếu rồi thì xóa bảng đó và tạo lại bảng mới.
cur.executemany("INSERT INTO Cars VALUES(?, ?, ?)", cars)
Chúng ta insert 8 dòng dữ liệu vào bảng bằng một phương thức duy nhất là executemany(), tham số đầu tiên là một câu lệnh SQL có tham số là các dấu ?, tham số thứ 2 là một tuple chứa nhiều tuple khác là các dữ liệu cần truyền vào.
SELECT
import sqlite3 as lite
import sys
import os
path = os.path.dirname(__file__) + "\\test.db"
con = lite.connect(path)
with con:
cur = con.cursor()
cur.execute("SELECT * FROM Cars")
rows = cur.fetchall()
for row in rows:
print (row)
Chúng ta sẽ lấy các bản ghi từ bảng Cars.
cur.execute("SELECT * FROM Cars")
Việc này cũng rất đơn giản, chỉ cần dùng phương thức execute() với câu SQL tương ứng.
rows = cur.fetchall()
Phương thức fetchall() sẽ trả về một tuple chứa các tuple là các dòng dữ liệu trong bảng.
for row in rows:
print (row)
Chúng ta in các tuple đó ra màn hình.
(1, u'Audi', 52642) (2, u'Mercedes', 57127) (3, u'Skoda', 9000) (4, u'Volvo', 29000) (5, u'Bentley', 350000) (6, u'Citroen', 21000) (7, u'Hummer', 41400) (8, u'Volkswagen', 21600)
Bạn cũng có thể in từng dòng một nếu muốn.
import sqlite3 as lite
import sys
import os
path = os.path.dirname(__file__) + "\\test.db"
con = lite.connect(path)
with con:
cur = con.cursor()
cur.execute("SELECT * FROM Cars")
while True:
row = cur.fetchone()
if row == None:
break
print (row[0], row[1], row[2])
Chúng ta lấy từng dòng và in chúng ra màn hình.
while True:
Chúng ta dùng một vòng lặp để lặp qua từng dòng dữ liệu trong bảng. Vòng lặp kết thúc khi đối tượng Cursor đã đọc hết dữ liệu trong bảng.
row = cur.fetchone()
if row == None:
break
Đối tượng Cursor có chứa một con trỏ chỉ đến các dòng trong bảng. Phương thức fetchone() sẽ đẩy con trỏ này lên một dòng và trả về dữ liệu của dòng đó, nếu con trỏ chỉ qua dòng cuối cùng thì sẽ trả về một đối tượng None.
print (row[0], row[1], row[2])
Dữ liệu trả về là một tuple nên bạn có thể truy xuất từng phần tử trong tuple bằng cặp dấu [].
1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
Lấy phần tử thông qua tên cột
Như các ví dụ trên, dữ liệu trả về là một tuple chứa các tuple, nhưng bạn có thể quy định dữ liệu trả về dạng Dictionary, bằng cách đó bạn có thể truy cập vào các cột thông qua tên cột chứ không cần dùng chỉ số nữa.
import sqlite3 as lite
import os
path = os.path.dirname(__file__) + "\\test.db"
con = lite.connect(path)
with con:
con.row_factory = lite.Row
cur = con.cursor()
cur.execute("SELECT * FROM Cars")
rows = cur.fetchall()
for row in rows:
print ("%s %s %s" % (row["Id"], row["Name"], row["Price"]))
Trong ví dụ này chúng ta sẽ lấy dữ liệu về dạng Dictionary.
con.row_factory = lite.Row
Để dữ liệu trả về là Dictionary thì chúng ta thiết lập thuộc tính row_factory trong đối tượng Connection là sqlite3.Row.
for row in rows:
print ("%s %s %s" % (row["Id"], row["Name"], row["Price"]))
Dữ liệu trả về kiểu Dictionary và bạn có thể truy xuất dữ liệu của các ô thông qua tên cột.
Truyền tham số vào câu truy vấn
Truyền tham số vào câu truy vấn giúp tăng tốc độ thực thi câu truy vấn và đảm bảo an toàn cho ứng dụng khỏi kiểu tấn công SQL Injection.
import sqlite3 as lite
import sys
import os
uId = 1
uPrice = 62300
path = os.path.dirname(__file__) + "\\test.db"
con = lite.connect(path)
with con:
cur = con.cursor()
cur.execute("UPDATE Cars SET Price=? WHERE Id=?", (uPrice, uId))
con.commit()
print ("Number of rows updated: %d" % cur.rowcount)
Chúng ta thực thi câu lệnh UPDATE và dùng tham số trong câu lệnh SQL.
cur.execute("UPDATE Cars SET Price=? WHERE Id=?", (uPrice, uId))
Các tham số trong câu truy vấn được đại diện bằng dấu ?.
print ("Number of rows updated: %d" % cur.rowcount)
Ngoài ra trong đối tượng Cursor có thuộc tính rowcount chứa số lượng các dòng dữ liệu vừa được cập nhật.
Number of rows updated: 1