SQLite – Python

安裝

SQLite3 可使用 sqlite3 模塊與 Python 進(jìn)行集成。sqlite3 模塊是由 Gerhard Haring 編寫(xiě)的。它提供了一個(gè)與 PEP 249 描述的 DB-API 2.0 規(guī)范兼容的 SQL 接口。您不需要單獨(dú)安裝該模塊，因?yàn)?Python 2.5.x 以上版本默認(rèn)自帶了該模塊。

為了使用 sqlite3 模塊，您首先必須創(chuàng)建一個(gè)表示數(shù)據(jù)庫(kù)的連接對(duì)象，然后您可以有選擇地創(chuàng)建光標(biāo)對(duì)象，這將幫助您執(zhí)行所有的 SQL 語(yǔ)句。

 簡(jiǎn)單的說(shuō)，只要是python2.5.X以上的版本或者所有python3的版本都不需要安裝該模塊即可直接使用sqlite。

另外，因?yàn)閟qlite已經(jīng)內(nèi)嵌在python模塊內(nèi)，所以也不需要額外安裝sqlite，也就是說(shuō)不需要安裝配置即可直接使用sqlite。

Python sqlite3 模塊 API

以下是重要的 sqlite3 模塊程序，可以滿(mǎn)足您在 Python 程序中使用 SQLite 數(shù)據(jù)庫(kù)的需求。如果您需要了解更多細(xì)節(jié)，請(qǐng)查看 Python sqlite3 模塊的官方文檔。

連接數(shù)據(jù)庫(kù)

下面的 Python 代碼顯示了如何連接到一個(gè)現(xiàn)有的數(shù)據(jù)庫(kù)。如果數(shù)據(jù)庫(kù)不存在，那么它就會(huì)被創(chuàng)建，最后將返回一個(gè)數(shù)據(jù)庫(kù)對(duì)象。

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')

print "Opened database successfully";

在這里，您也可以把數(shù)據(jù)庫(kù)名稱(chēng)復(fù)制為特定的名稱(chēng) :memory:，這樣就會(huì)在 RAM 中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)?，F(xiàn)在，讓我們來(lái)運(yùn)行上面的程序，在當(dāng)前目錄中創(chuàng)建我們的數(shù)據(jù)庫(kù) test.db。您可以根據(jù)需要改變路徑。保存上面代碼到 sqlite.py 文件中，并按如下所示執(zhí)行。如果數(shù)據(jù)庫(kù)成功創(chuàng)建，那么會(huì)顯示下面所示的消息：

$chmod +x sqlite.py
$./sqlite.py
Open database successfully

創(chuàng)建表

下面的 Python 代碼段將用于在先前創(chuàng)建的數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)表：

#!/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()

上述程序執(zhí)行時(shí)，它會(huì)在 test.db 中創(chuàng)建 COMPANY 表，并顯示下面所示的消息：

Opened database successfully
Table created successfully

INSERT 操作

下面的 Python 程序顯示了如何在上面創(chuàng)建的 COMPANY 表中創(chuàng)建記錄：

#!/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()

上述程序執(zhí)行時(shí)，它會(huì)在 COMPANY 表中創(chuàng)建給定記錄，并會(huì)顯示以下兩行：

Opened database successfully
Records created successfully

SELECT 操作

下面的 Python 程序顯示了如何從前面創(chuàng)建的 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()

上述程序執(zhí)行時(shí)，它會(huì)產(chǎn)生以下結(jié)果：

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 語(yǔ)句來(lái)更新任何記錄，然后從 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()

上述程序執(zhí)行時(shí)，它會(huì)產(chǎn)生以下結(jié)果：

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 語(yǔ)句刪除任何記錄，然后從 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()

上述程序執(zhí)行時(shí)，它會(huì)產(chǎn)生以下結(jié)果：

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