SQLite - Python 安裝

				<div class="archive_info">

	<div id="content">
				</div>
			<h1>SQLite - <span class="color_h1">Python</span></h1>

安裝

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

		 </div>
        	        	 		<div class="clear"></div>
	</div>