Python連線SQLite資料庫
由Gerhard Haring編寫的sqlite3模組與Python進行整合。 它提供了符合由PEP 249描述的DB-API 2.0規範的SQL介面。所以不需要單獨安裝此模組，因為預設情況下隨著Python 2.5.x以上版本一起發布執行。
要使用sqlite3模組，必須首先建立一個表示資料庫的連線物件，然後可以選擇建立的游標物件來執行SQL語句。
連線到資料庫
以下Python程式碼顯示了如何連線到一個指定的資料庫。 如果資料庫不存在，那麼它將被建立，最後將返回一個資料庫物件。
注意：在本範例中，使用的是 python 3.5.1
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('pydb.db')
print ("Opened database successfully");
在這裡，還可以提供資料庫名稱作為特殊名稱:memory:, 在RAM中建立資料庫。 現在，執行上面的程式在當前目錄中建立資料庫：pydb.db。
可以根據需要更改路徑。 在F:\worksp\sqlite\py-sqlite.py檔案中保留以上程式碼，並按如下所示執行。 如果資料庫成功建立，則會提供以下訊息：
建立表
以下Python程式將用於在先前建立的資料庫(py-sqlite.py)中建立一個表：
#!/usr/bin/python
import sqlite3
## 開啟資料庫連線
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
## 建立一個表 - company
conn.execute('''CREATE TABLE company
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print ("Table created successfully");
conn.close()
當執行上述程式後，將在py-sqlite.db中建立company表，並顯示以下訊息：
#!/usr/bin/python
import sqlite3
## 開啟資料庫連線
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
## 建立一個表 - company
conn.execute('''CREATE TABLE company
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print ("Table created successfully");
conn.close()
當執行上述程式時，它將在py-sqlite.db資料庫中建立company表，並顯示以下訊息：
Opened database successfully
Table created successfully
插入操作
以下Python程式顯示如何在上述範例中建立的COMPANY表中插入資料記錄：
#!/usr/bin/python
import sqlite3
## 開啟資料庫連線
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()
## 建立一個表 - company
conn.execute('''CREATE TABLE company
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print ("Table created successfully");
conn.commit()
## 插入資料
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");
conn.commit()
print ("Records Insert successfully");
conn.close()
當執行上述程式時，它將在COMPANY表中插入給定的資料記錄，並顯示以下結果：
Opened database successfully
Table created successfully
Records Insert successfully
SELECT/查詢操作
以下Python程式顯示如何從上述範例中建立的COMPANY表中獲取並顯示資料記錄：
#!/usr/bin/python
import sqlite3
## 開啟資料庫連線
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()
## 建立一個表 - company
conn.execute('''CREATE TABLE company
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print ("Table created successfully");
conn.commit()
## 插入資料
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");
conn.commit()
print ("Records Insert successfully");
print ('--------------------------- start fetch data from company --------------------------');
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print ("ID = ", row[0])
print ("NAME = ", row[1])
print ("ADDRESS = ", row[2])
print ("SALARY = ", row[3], "\n")
print ("Select Operation done successfully.");
conn.close()
執行上述程式時，會產生以下結果：
Opened database successfully
Table created successfully
Records Insert successfully
--------------------------- start fetch data from company --------------------------
ID = 1
NAME = Maxsu
ADDRESS = Haikou
SALARY = 20000.0
ID = 2
NAME = Allen
ADDRESS = Shenzhen
SALARY = 35000.0
ID = 3
NAME = Weiwang
ADDRESS = Guangzhou
SALARY = 22000.0
ID = 4
NAME = Marklee
ADDRESS = Beijing
SALARY = 45000.0
Select Operation done successfully.
更新操作
以下Python程式碼演示如何使用UPDATE語句來更新指定記錄，然後再從COMPANY表中獲取並顯示更新的記錄：
#!/usr/bin/python
import sqlite3
## 開啟資料庫連線
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()
## 建立一個表 - company
conn.execute('''CREATE TABLE company
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print ("Table created successfully");
conn.commit()
## 插入資料
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");
conn.commit()
## 更新資料
conn.execute("UPDATE COMPANY set SALARY = 29999.00 where ID=1")
conn.commit()
print ("Total number of rows updated :", conn.total_changes)
print ("Records Insert successfully");
print ('--------------------------- start fetch data from company --------------------------');
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print ("ID = ", row[0])
print ("NAME = ", row[1])
print ("ADDRESS = ", row[2])
print ("SALARY = ", row[3], "\n")
print ("Select Operation done successfully.");
conn.close()
執行上述程式時，會產生以下結果：
Opened database successfully
Table created successfully
Total number of rows updated : 5
Records Insert successfully
--------------------------- start fetch data from company --------------------------
ID = 1
NAME = Maxsu
ADDRESS = Haikou
SALARY = 29999.0
ID = 2
NAME = Allen
ADDRESS = Shenzhen
SALARY = 35000.0
ID = 3
NAME = Weiwang
ADDRESS = Guangzhou
SALARY = 22000.0
ID = 4
NAME = Marklee
ADDRESS = Beijing
SALARY = 45000.0
Select Operation done successfully.
刪除操作
以下Python程式碼演示如何使用DELETE語句來刪除記錄，然後從COMPANY表中獲取並顯示剩餘的記錄：
#!/usr/bin/python
import sqlite3
## 開啟資料庫連線
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()
## 建立一個表 - company
conn.execute('''CREATE TABLE company
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print ("Table created successfully");
conn.commit()
## 插入資料
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");
conn.commit()
## 刪除ID值小於等於2的資料
conn.execute("DELETE from COMPANY where ID<=2;")
conn.commit()
print ("Total number of rows updated :", conn.total_changes)
print ("Records Insert successfully");
print ('--------------------------- start fetch data from company --------------------------');
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print ("ID = ", row[0])
print ("NAME = ", row[1])
print ("ADDRESS = ", row[2])
print ("SALARY = ", row[3], "\n")
print ("Select Operation done successfully.");
conn.close()
執行上面語句後，得到以下結果 -
Opened database successfully
Table created successfully
Total number of rows updated : 6
Records Insert successfully
--------------------------- start fetch data from company --------------------------
ID = 3
NAME = Weiwang
ADDRESS = Guangzhou
SALARY = 22000.0
ID = 4
NAME = Marklee
ADDRESS = Beijing
SALARY = 45000.0
Select Operation done successfully.
這裡只是簡單演示如何使用Python來連線SQLite實現CURD操作，關於其它更複雜的操作，建議參考官方API和範例，這裡就不是一一講解了，如有問題歡迎留言。