由Gerhard Haring編寫的sqlite3模塊與Python進行集成。 它提供了符合由PEP 249描述的DB-API 2.0規(guī)范的SQL接口。所以不需要單獨安裝此模塊，因為默認情況下隨著Python 2.5.x以上版本一起發(fā)布運行。

要使用sqlite3模塊，必須首先創(chuàng)建一個表示數(shù)據(jù)庫的連接對象，然后可以選擇創(chuàng)建的游標對象來執(zhí)行SQL語句。

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

以下Python代碼顯示了如何連接到一個指定的數(shù)據(jù)庫。 如果數(shù)據(jù)庫不存在，那么它將被創(chuàng)建，最后將返回一個數(shù)據(jù)庫對象。

注意：在本示例中，使用的是 python 3.5.1

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('pydb.db')
print ("Opened database successfully");

在這里，還可以提供數(shù)據(jù)庫名稱作為特殊名稱:memory:, 在RAM中創(chuàng)建數(shù)據(jù)庫。 現(xiàn)在，運行上面的程序在當前目錄中創(chuàng)建數(shù)據(jù)庫：pydb.db。

可以根據(jù)需要更改路徑。 在F:\worksp\sqlite\py-sqlite.py文件中保留以上代碼，并按如下所示執(zhí)行。 如果數(shù)據(jù)庫成功創(chuàng)建，則會提供以下消息：

創(chuàng)建表

以下Python程序?qū)⒂糜谠谙惹皠?chuàng)建的數(shù)據(jù)庫(py-sqlite.py)中創(chuàng)建一個表：

#!/usr/bin/python

import sqlite3

## 打開數(shù)據(jù)庫連接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 創(chuàng)建一個表 - company
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í)行上述程序后，將在py-sqlite.db中創(chuàng)建company表，并顯示以下消息：

#!/usr/bin/python

import sqlite3

## 打開數(shù)據(jù)庫連接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 創(chuàng)建一個表 - company
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í)行上述程序時，它將在py-sqlite.db數(shù)據(jù)庫中創(chuàng)建company表，并顯示以下消息：

Opened database successfully
Table created successfully

插入操作

以下Python程序顯示如何在上述示例中創(chuàng)建的COMPANY表中插入數(shù)據(jù)記錄：

#!/usr/bin/python

import sqlite3

## 打開數(shù)據(jù)庫連接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()

## 創(chuàng)建一個表 - company
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.commit()

## 插入數(shù)據(jù)
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");

conn.commit()
print ("Records Insert successfully");

conn.close()

當執(zhí)行上述程序時，它將在COMPANY表中插入給定的數(shù)據(jù)記錄，并顯示以下結(jié)果：

Opened database successfully
Table created successfully
Records Insert successfully

SELECT/查詢操作

以下Python程序顯示如何從上述示例中創(chuàng)建的COMPANY表中獲取并顯示數(shù)據(jù)記錄：

#!/usr/bin/python

import sqlite3

## 打開數(shù)據(jù)庫連接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()

## 創(chuàng)建一個表 - company
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.commit()

## 插入數(shù)據(jù)
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");

conn.commit()
print ("Records Insert successfully");
print ('--------------------------- start fetch data from company --------------------------');

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 ("Select Operation done successfully.");

conn.close()

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

Opened database successfully
Table created successfully
Records Insert successfully
--------------------------- start fetch data from company --------------------------
ID =  1
NAME =  Maxsu
ADDRESS =  Haikou
SALARY =  20000.0 

ID =  2
NAME =  Allen
ADDRESS =  Shenzhen
SALARY =  35000.0 

ID =  3
NAME =  Weiwang
ADDRESS =  Guangzhou
SALARY =  22000.0 

ID =  4
NAME =  Marklee
ADDRESS =  Beijing
SALARY =  45000.0 

Select Operation done successfully.

更新操作

以下Python代碼演示如何使用UPDATE語句來更新指定記錄，然后再從COMPANY表中獲取并顯示更新的記錄：

#!/usr/bin/python

import sqlite3

## 打開數(shù)據(jù)庫連接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()

## 創(chuàng)建一個表 - company
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.commit()

## 插入數(shù)據(jù)
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");

conn.commit()

## 更新數(shù)據(jù)
conn.execute("UPDATE COMPANY set SALARY = 29999.00 where ID=1")
conn.commit()
print ("Total number of rows updated :", conn.total_changes)


print ("Records Insert successfully");
print ('--------------------------- start fetch data from company --------------------------');

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 ("Select Operation done successfully.");

conn.close()

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

Opened database successfully
Table created successfully
Total number of rows updated : 5
Records Insert successfully
--------------------------- start fetch data from company --------------------------
ID =  1
NAME =  Maxsu
ADDRESS =  Haikou
SALARY =  29999.0 

ID =  2
NAME =  Allen
ADDRESS =  Shenzhen
SALARY =  35000.0 

ID =  3
NAME =  Weiwang
ADDRESS =  Guangzhou
SALARY =  22000.0 

ID =  4
NAME =  Marklee
ADDRESS =  Beijing
SALARY =  45000.0 

Select Operation done successfully.

刪除操作

以下Python代碼演示如何使用DELETE語句來刪除記錄，然后從COMPANY表中獲取并顯示剩余的記錄：

#!/usr/bin/python

import sqlite3

## 打開數(shù)據(jù)庫連接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()

## 創(chuàng)建一個表 - company
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.commit()

## 插入數(shù)據(jù)
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");

conn.commit()

## 刪除ID值小于等于2的數(shù)據(jù)
conn.execute("DELETE from COMPANY where ID<=2;")
conn.commit()

print ("Total number of rows updated :", conn.total_changes)


print ("Records Insert successfully");
print ('--------------------------- start fetch data from company --------------------------');

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 ("Select Operation done successfully.");

conn.close()

執(zhí)行上面語句后，得到以下結(jié)果 -

Opened database successfully
Table created successfully
Total number of rows updated : 6
Records Insert successfully
--------------------------- start fetch data from company --------------------------
ID =  3
NAME =  Weiwang
ADDRESS =  Guangzhou
SALARY =  22000.0 

ID =  4
NAME =  Marklee
ADDRESS =  Beijing
SALARY =  45000.0 

Select Operation done successfully.

這里只是簡單演示如何使用Python來連接SQLite實現(xiàn)CURD操作，關(guān)于其它更復雜的操作，建議參考官方API和實例，這里就不是一一講解了，如有問題歡迎留言。