Python連接SQLite數據庫

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

要使用sqlite3模塊，必須首先創建一個表示數據庫的連接對象，然後可以選擇創建的遊標對象來執行SQL語句。

連接到數據庫

以下Python代碼顯示瞭如何連接到一個指定的數據庫。如果數據庫不存在，那麼它將被創建，最後將返回一個數據庫對象。

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

#!/usr/bin/python

import sqlite3

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

在這裏，還可以提供數據庫名稱作爲特殊名稱*:memory:**, 在RAM中創建數據庫。現在，運行上面的程序在當前目錄中創建數據庫：pydb.db*。

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

Python連接SQLite數據庫

創建表

以下Python程序將用於在先前創建的數據庫(py-sqlite.py)中創建一個表：

#!/usr/bin/python

import sqlite3

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

## 創建一個表 - 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()

當執行上述程序後，將在py-sqlite.db中創建company表，並顯示以下消息：

#!/usr/bin/python

import sqlite3

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

## 創建一個表 - 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()

當執行上述程序時，它將在py-sqlite.db數據庫中創建company表，並顯示以下消息：

Opened database successfully
Table created successfully

插入操作

以下Python程序顯示如何在上述示例中創建的COMPANY表中插入數據記錄：

#!/usr/bin/python

import sqlite3

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

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

## 創建一個表 - 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()

## 插入數據
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()

當執行上述程序時，它將在COMPANY表中插入給定的數據記錄，並顯示以下結果：

Opened database successfully
Table created successfully
Records Insert successfully

SELECT/查詢操作

以下Python程序顯示如何從上述示例中創建的COMPANY表中獲取並顯示數據記錄：

#!/usr/bin/python

import sqlite3

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

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

## 創建一個表 - 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()

## 插入數據
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()

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

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

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

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

## 創建一個表 - 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()

## 插入數據
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()

## 更新數據
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()

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

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

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

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

## 創建一個表 - 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()

## 插入數據
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的數據
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()

執行上面語句後，得到以下結果 -

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實現CURD操作，關於其它更復雜的操作，建議參考官方API和實例，這裏就不是一一講解了，如有問題歡迎留言。

SQLite教程

數據庫和表

CURD操作

子句和條件

連接操作

SQLite時間日期

聚合函數

SQLite觸發器

SQLite主鍵

SQLite導出導入

SQLite連接程序

Python連接SQLite數據庫

連接到數據庫

創建表

插入操作

SELECT/查詢操作

更新操作

刪除操作