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代碼顯示了如何連接到現有數據庫。如果數據庫不存在，則將創建該數據庫，最后將返回一個數據庫對象。

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
#!/usr/bin/python
 import sqlite3
 conn = sqlite3.connect('test.db')
 print "Opened database successfully";
  
 

在這里，還可以提供數據庫名稱作為特殊名稱:memory:以在RAM中創建數據庫。現在，我們運行上面的程序在當前目錄中創建數據庫test.db。可以根據需要更改路徑，將上面的代碼保存在sqlite.py文件中，并如下所示執行它。如果成功創建了數據庫，則它將顯示以下消息。

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
$chmod +x sqlite.py
 $./sqlite.py
 Open database successfully
  
 

創建表

以下Python程序將用于在先前創建的數據庫中創建表。

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
#!/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表，并顯示以下消息：

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
Opened database successfully
 Table created successfully
  
 

插入操作

以下Python程序顯示了如何在上面的示例中創建的COMPANY表中創建記錄。

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
#!/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', 19000.00 )");
 conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
       VALUES (2, 'Allen', 25, 'Texas', 19800.00 )");
 conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
       VALUES (3, 'Teddy', 23, 'Norway', 22000.00 )");
 conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
       VALUES (4, 'Mark', 25, 'Rich-Mond ', 55000.00 )");
 conn.commit()
 print "Records created successfully";
 conn.close()
  
 

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

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
Opened database successfully
 Records created successfully
  
 

選擇操作

以下Python程序演示了如何從上例中創建的COMPANY表中查詢獲取并顯示記錄。

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
#!/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()
  
 

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

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
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表中查詢獲取并顯示更新的記錄。

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
#!/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()
  
 

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

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
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表中查詢獲取并顯示其余記錄。

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
#!/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()
  
 

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

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : www.090948.com
# Date : 2020-08-25
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