sqlite python 編程接口

1. 安裝

sqlite3 可使用 sqlite3 模塊與 python 進行集成。sqlite3 模塊是由 gerhard haring 編寫的。它提供了一個與 pep 249 描述的 db-api 2.0 規范兼容的 sql 接口。您不需要單獨安裝該模塊，因為 python 2.5.x 以上版本默認自帶了該模塊。

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

2. python sqlite3 模塊 api

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

3. 連接數據庫

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

4. 創建表

下面的 python 代碼段將用于在先前創建的數據庫中創建一個表：

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

上述程序執行時，它會在 test.db 中創建 company 表，并顯示下面所示的消息：

opened database successfully
table created successfully

5. insert 操作

下面的 python 程序顯示了如何在上面創建的 company 表中創建記錄：

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

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

opened database successfully
records created successfully

6. select 操作

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

上述程序執行時，它會產生以下結果：

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

7. 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()

上述程序執行時，它會產生以下結果：

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

8. 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()

上述程序執行時，它會產生以下結果：

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