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程式將用於在先前建立的資料庫(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和範例,這裡就不是一一講解了,如有問題歡迎留言。