SQLite - Python

---

---

在本章中，您將學習如何在 Python 程式中使用 SQLite。

安裝

SQLite3 可以使用 sqlite3 模組與 Python 整合，該模組由 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