SQLite - Python

安裝

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

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

Python sqlite3 模塊 API

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

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

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

#!/usr/bin/python

import sqlite3

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

print "Opened database successfully"

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

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

創(chuàng)建表

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

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully"
c = conn.cursor()
c.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.commit()
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')
c = conn.cursor()
print "Opened database successfully"

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) 
      VALUES (1, 'Paul', 32, 'California', 20000.00 )")

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

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

c.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')
c = conn.cursor()
print "Opened database successfully"

cursor = c.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 語句來更新任何記錄，然后從 COMPANY 表中獲取并顯示更新的記錄：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Opened database successfully"

c.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 語句刪除任何記錄，然后從 COMPANY 表中獲取并顯示剩余的記錄：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Opened database successfully"

c.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