SQLite – Python

安裝

SQLite3 可使用sqlite3 模塊與Python 進行集成。 sqlite3 模塊是由Gerhard Haring 編寫的。 它提供了一個與PEP 249 描述的DB-API 2.0 規範兼容的SQL 接口。 您不需要單獨安裝該模塊，因為Python 2.5.x 以上版本默認自帶了該模塊。

為了使用sqlite3 模塊，您首先必須創建一個表示數據庫的連接對象，然後您可以有選擇地創建光標對象，這將幫助您執行所有的SQL 語句。

Python sqlite3 模塊API

以下是重要的sqlite3 模塊程序，可以滿足您在Python 程序中使用SQLite 數據庫的需求。 如果您需要了解更多細節，請查看Python sqlite3 模塊的官方文檔。

連接數據庫

下面的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

INSERT 操作

下面的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', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

上述程序執行時，它會在COMPANY 表中創建給定記錄，並會顯示以下兩行：

Opened database successfully
Records created successfully

SELECT 操作

下面的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

UPDATE 操作

下面的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

DELETE 操作

下面的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