Python - 資料庫和SQL

Python程式語言具有強大的資料庫程式設計功能。Python支援各種資料庫，例如SQLite、MySQL、Oracle、Sybase、PostgreSQL等。Python還支援資料定義語言 (DDL)、資料操作語言 (DML) 和資料查詢語句。Python資料庫介面的標準是Python DB-API。大多數Python資料庫介面都遵循此標準。

以下是可用的Python資料庫介面列表：Python資料庫介面和API。您必須為需要訪問的每個資料庫下載單獨的DB API模組。

本章我們將學習如何在Python程式語言中使用SQLite資料庫。這是透過使用Python內建的sqlite3模組來實現的。您應該首先建立一個表示資料庫的連線物件，然後建立一些遊標物件來執行SQL語句。

連線資料庫

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

建立表

下面的Python程式將用於在前面建立的資料庫中建立表。

#!/usr/bin/python

import sqlite3

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

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

執行上述程式後，它將在您的test.db中建立COMPANY表，並顯示以下訊息：

Opened database successfully
Table created successfully

插入操作

下面的Python程式演示瞭如何在上面示例中建立的COMPANY表中建立記錄。

#!/usr/bin/python

import sqlite3

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

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

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

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

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

選擇操作

下面的Python程式演示瞭如何從上面示例中建立的COMPANY表中提取和顯示記錄。

#!/usr/bin/python

import sqlite3

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

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

更新操作

下面的Python程式碼演示瞭如何使用UPDATE語句更新任何記錄，然後從COMPANY表中提取和顯示更新後的記錄。

#!/usr/bin/python

import sqlite3

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

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

刪除操作

下面的Python程式碼演示瞭如何使用DELETE語句刪除任何記錄，然後從COMPANY表中提取和顯示剩餘的記錄。

#!/usr/bin/python

import sqlite3

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

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

列印頁面