Python編程語言具有用於資料庫編程的強大功能。Python支持各種資料庫，例如SQLite，MySQL，Oracle，Sybase，PostgreSQL等。Python還支持數據定義語言(DDL)，數據操作語言(DML)和數據查詢語句。用於資料庫介面的Python標準是Python DB-API。大多數Python資料庫介面都遵守該標準。
以下是可用的Python資料庫介面的列表：Python資料庫介面和API。需要為訪問的每個資料庫下載單獨的DB API模組。
在本章中，我們演示python編程語言連接SQLite資料庫。這是通過使用python內置的sqlite3模組完成的。應該首先創建一個代表資料庫的連接對象，然後創建一些游標對象以執行SQL語句。
連接到資料庫
以下Python代碼顯示了如何連接到現有資料庫。如果資料庫不存在，則將創建該資料庫，最後將返回一個資料庫對象。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
在這裏，還可以提供資料庫名稱作為特殊名稱:memory:以在RAM中創建資料庫。現在，我們運行上面的程式在當前目錄中創建資料庫test.db。可以根據需要更改路徑，將上面的代碼保存在sqlite.py檔中，並如下所示執行它。如果成功創建了資料庫，則它將顯示以下消息。
$chmod +x sqlite.py
$./sqlite.py
Open database successfully
創建表
以下Python程式將用於在先前創建的資料庫中創建表。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
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()
執行上述程式後，它將在test.db中創建COMPANY表，並顯示以下消息-
Opened database successfully
Table created successfully
插入操作
以下Python程式顯示了如何在上面的示例中創建的COMPANY表中創建記錄。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 19000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 19800.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 22000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 55000.00 )");
conn.commit()
print "Records created successfully";
conn.close()
執行上述程式後，它將在COMPANY表中創建給定記錄，並將顯示以下兩行-
Opened database successfully
Records created successfully
選擇操作
以下Python程式演示了如何從上例中創建的COMPANY表中查詢獲取並顯示記錄。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
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 "Operation done successfully";
conn.close()
執行上述程式時，將產生以下結果 -
Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully
更新操作
以下Python代碼顯示了如何使用UPDATE語句更新任何記錄，然後從COMPANY表中查詢獲取並顯示更新的記錄。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit
print "Total number of rows updated :", conn.total_changes
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 "Operation done successfully";
conn.close()
執行上述程式時，將產生以下結果 -
Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000.0
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully
刪除操作
以下Python代碼顯示了如何使用DELETE語句刪除任何記錄，然後從COMPANY表中查詢獲取並顯示其餘記錄。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()
print "Total number of rows deleted :", conn.total_changes
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 "Operation done successfully";
conn.close()
執行上述程式時，將產生以下結果 -
Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully