Python 數(shù)據(jù)庫(kù)和SQL

Python編程語(yǔ)言具有用于數(shù)據(jù)庫(kù)編程的強(qiáng)大功能。Python支持各種數(shù)據(jù)庫(kù)，例如SQLite，MySQL，Oracle，Sybase，PostgreSQL等。Python還支持?jǐn)?shù)據(jù)定義語(yǔ)言(DDL)，數(shù)據(jù)操作語(yǔ)言(DML)和數(shù)據(jù)查詢語(yǔ)句。用于數(shù)據(jù)庫(kù)接口的Python標(biāo)準(zhǔn)是Python DB-API。大多數(shù)Python數(shù)據(jù)庫(kù)接口都遵守該標(biāo)準(zhǔn)。

以下是可用的Python數(shù)據(jù)庫(kù)接口的列表：Python數(shù)據(jù)庫(kù)接口和API。需要為訪問(wèn)的每個(gè)數(shù)據(jù)庫(kù)下載單獨(dú)的DB API模塊。

在本章中，我們演示python編程語(yǔ)言連接SQLite數(shù)據(jù)庫(kù)。這是通過(guò)使用python內(nèi)置的sqlite3模塊完成的。應(yīng)該首先創(chuàng)建一個(gè)代表數(shù)據(jù)庫(kù)的連接對(duì)象，然后創(chuàng)建一些游標(biāo)對(duì)象以執(zhí)行SQL語(yǔ)句。

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

以下Python代碼顯示了如何連接到現(xiàn)有數(shù)據(jù)庫(kù)。如果數(shù)據(jù)庫(kù)不存在，則將創(chuàng)建該數(shù)據(jù)庫(kù)，最后將返回一個(gè)數(shù)據(jù)庫(kù)對(duì)象。

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

在這里，還可以提供數(shù)據(jù)庫(kù)名稱作為特殊名稱:memory:以在RAM中創(chuàng)建數(shù)據(jù)庫(kù)。現(xiàn)在，我們運(yùn)行上面的程序在當(dāng)前目錄中創(chuàng)建數(shù)據(jù)庫(kù)test.db?？梢愿鶕?jù)需要更改路徑，將上面的代碼保存在sqlite.py文件中，并如下所示執(zhí)行它。如果成功創(chuàng)建了數(shù)據(jù)庫(kù)，則它將顯示以下消息。

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

創(chuàng)建表

以下Python程序?qū)⒂糜谠谙惹皠?chuàng)建的數(shù)據(jù)庫(kù)中創(chuàng)建表。

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : 
# 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()
  
 

執(zhí)行上述程序后，它將在test.db中創(chuàng)建COMPANY表，并顯示以下消息：

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

插入操作

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

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : 
# 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()
  
 

執(zhí)行上述程序后，它將在COMPANY表中創(chuàng)建給定記錄，并將顯示以下兩行：

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

選擇操作

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

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : 
# 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()
  
 

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

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : 
# 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語(yǔ)句更新任何記錄，然后從COMPANY表中查詢獲取并顯示更新的記錄。

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : 
# 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()
  
 

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

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : 
# 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語(yǔ)句刪除任何記錄，然后從COMPANY表中查詢獲取并顯示其余記錄。

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : 
# 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()
  
 

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

 
# Filename : example.py
# Copyright : 2020 By Codebaoku
# Author by : 
# 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