本文分享自華為雲社群《Python資料庫程式設計全指南SQLite和MySQL實踐》，作者： 檸檬味擁抱。
1. 安裝必要的庫
首先，我們需要安裝Python的資料庫驅動程式，以便與SQLite和MySQL進行互動。對於SQLite，Python自帶了支援；而對於MySQL，我們需要安裝額外的庫，如mysql-connector-python。
# 安裝 MySQL 聯結器
pip install mysql-connector-python
2. 連線SQLite資料庫
SQLite是一種輕量級的嵌入式資料庫，無需伺服器即可使用。以下是如何連線並操作SQLite資料庫的示例程式碼：
import sqlite3 # 連線到 SQLite 資料庫 conn = sqlite3.connect('example.db') # 建立一個遊標物件 cursor = conn.cursor() # 建立表 cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''') # 插入資料 cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30)) cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 25)) # 查詢資料 cursor.execute("SELECT * FROM users") rows = cursor.fetchall() for row in rows: print(row) # 提交併關閉連線 conn.commit() conn.close()
3. 連線MySQL資料庫
MySQL是一種常見的關係型資料庫管理系統。使用Python連線MySQL需要使用相應的庫，比如mysql-connector-python。以下是連線並操作MySQL資料庫的示例程式碼：
import mysql.connector # 連線到 MySQL 資料庫 conn = mysql.connector.connect( host="localhost", user="username", password="password", database="mydatabase" ) # 建立一個遊標物件 cursor = conn.cursor() # 建立表 cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)''') # 插入資料 sql = "INSERT INTO users (name, age) VALUES (%s, %s)" val = ("Alice", 30) cursor.execute(sql, val) # 查詢資料 cursor.execute("SELECT * FROM users") rows = cursor.fetchall() for row in rows: print(row) # 提交併關閉連線 conn.commit() conn.close()
4. 程式碼解析
-
連線資料庫：使用
sqlite3.connect()連線SQLite資料庫，使用mysql.connector.connect()連線MySQL資料庫。 -
建立表：透過執行SQL語句建立表，使用
cursor.execute()方法執行。 -
插入資料：執行插入資料的SQL語句，使用
cursor.execute()方法並傳入引數。 -
查詢資料：執行查詢資料的SQL語句，使用
cursor.execute()方法，然後使用cursor.fetchall()獲取所有查詢結果。 -
提交和關閉連線：對於SQLite，使用
conn.commit()提交事務並使用conn.close()關閉連線。對於MySQL，同樣使用conn.commit()提交事務，但需要使用conn.close()關閉連線。
透過這些示例程式碼，你可以輕鬆地使用Python連線和操作SQLite和MySQL資料庫。務必記住在實際應用中，要處理好異常情況，並採取安全措施，如防止SQL隱碼攻擊等。
5. 資料庫連線引數
在連線資料庫時，需要提供一些引數以確保正確的連線。對於SQLite，只需提供資料庫檔案的路徑即可。而對於MySQL，除了資料庫名稱外，還需要提供主機名、使用者名稱和密碼等資訊。
-
對於SQLite連線：
sqlite3.connect('example.db')
-
對於MySQL連線：
conn = mysql.connector.connect( host="localhost", user="username", password="password", database="mydatabase" )
6. 資料庫操作的異常處理
在實際應用中，資料庫操作可能會出現各種異常情況，比如連線失敗、SQL語法錯誤等。因此，在進行資料庫操作時，務必新增適當的異常處理機制，以提高程式的健壯性和穩定性。
以下是一個簡單的異常處理示例：
import sqlite3 import mysql.connector try: # SQLite 連線 conn_sqlite = sqlite3.connect('example.db') cursor_sqlite = conn_sqlite.cursor() # MySQL 連線 conn_mysql = mysql.connector.connect( host="localhost", user="username", password="password", database="mydatabase" ) cursor_mysql = conn_mysql.cursor() # 進行資料庫操作（省略） except sqlite3.Error as e: print("SQLite error:", e) except mysql.connector.Error as e: print("MySQL error:", e) finally: # 關閉連線 if conn_sqlite: conn_sqlite.close() if conn_mysql: conn_mysql.close()
7. 引數化查詢
在執行SQL語句時，尤其是涉及使用者輸入的情況下，應該使用引數化查詢來防止SQL隱碼攻擊。引數化查詢可以確保使用者輸入不會被誤解為SQL程式碼的一部分。
下面是一個使用引數化查詢的示例：
import sqlite3 import mysql.connector # SQLite 連線 conn_sqlite = sqlite3.connect('example.db') cursor_sqlite = conn_sqlite.cursor() # MySQL 連線 conn_mysql = mysql.connector.connect( host="localhost", user="username", password="password", database="mydatabase" ) cursor_mysql = conn_mysql.cursor() # 引數化查詢 name = "Alice" age = 30 # SQLite 引數化查詢 cursor_sqlite.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age)) # MySQL 引數化查詢 sql = "INSERT INTO users (name, age) VALUES (%s, %s)" val = (name, age) cursor_mysql.execute(sql, val) # 提交事務並關閉連線 conn_sqlite.commit() conn_sqlite.close() conn_mysql.commit() conn_mysql.close()
8. ORM框架
ORM（Object-Relational Mapping）框架可以將資料庫表的行對映為Python物件，簡化了資料庫操作。在Python中，有許多流行的ORM框架，比如SQLAlchemy、Django的ORM等。這些框架提供了高階的抽象和功能，使得與資料庫的互動更加方便和直觀。
以下是一個使用SQLAlchemy進行資料庫操作的示例：
from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker # 建立引擎 engine = create_engine('sqlite:///example.db', echo=True) # 宣告基類 Base = declarative_base() # 定義對映類 class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) age = Column(Integer) # 建立資料表 Base.metadata.create_all(engine) # 建立會話 Session = sessionmaker(bind=engine) session = Session() # 插入資料 user1 = User(name='Alice', age=30) user2 = User(name='Bob', age=25) session.add(user1) session.add(user2) session.commit() # 查詢資料 users = session.query(User).all() for user in users: print(user.id, user.name, user.age) # 關閉會話 session.close()
9. 使用SQLite記憶體資料庫
除了連線到檔案中的SQLite資料庫，還可以使用SQLite記憶體資料庫。SQLite記憶體資料庫完全儲存在RAM中，對於臨時性的資料處理或測試非常方便。
以下是一個使用SQLite記憶體資料庫的示例：
import sqlite3 # 連線到記憶體資料庫 conn = sqlite3.connect(':memory:') # 建立一個遊標物件 cursor = conn.cursor() # 建立表 cursor.execute('''CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''') # 插入資料 cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30)) cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 25)) # 查詢資料 cursor.execute("SELECT * FROM users") rows = cursor.fetchall() for row in rows: print(row) # 提交併關閉連線 conn.commit() conn.close()
10. 資料庫連線池
在高併發的應用中，頻繁地開啟和關閉資料庫連線會消耗大量資源。為了提高效能，可以使用資料庫連線池技術，將資料庫連線預先建立好並儲存在池中，需要時從池中獲取連線，使用完畢後歸還到池中。
以下是使用sqlitepool庫實現SQLite資料庫連線池的示例：
from sqlitepool import ConnectionPool # 建立資料庫連線池 pool = ConnectionPool('example.db', max_connections=5) # 從連線池中獲取連線 conn = pool.getconn() # 建立遊標物件 cursor = conn.cursor() # 執行查詢 cursor.execute("SELECT * FROM users") rows = cursor.fetchall() for row in rows: print(row) # 釋放連線回連線池 pool.putconn(conn)
11. 效能最佳化
在進行大規模資料操作時，需要考慮效能最佳化。一些常見的效能最佳化策略包括：
- 使用索引來加速查詢。
- 合理設計資料庫結構，避免過度規範化或反規範化。
- 批次運算元據，減少資料庫互動次數。
- 快取查詢結果，減少重複查詢資料庫的次數。
12. 使用非同步資料庫庫
隨著非同步程式設計的流行，出現了許多支援非同步操作的資料庫庫，如aiosqlite和aiomysql。這些庫可以與非同步框架（如asyncio）結合使用，提高程式的併發效能。
以下是一個使用aiosqlite庫進行非同步SQLite資料庫操作的示例：
import asyncio import aiosqlite async def main(): # 連線到 SQLite 資料庫 async with aiosqlite.connect('example.db') as db: # 建立一個遊標物件 cursor = await db.cursor() # 建立表 await cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''') # 插入資料 await cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30)) await cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 25)) # 查詢資料 await cursor.execute("SELECT * FROM users") rows = await cursor.fetchall() for row in rows: print(row) # 執行非同步主程式 asyncio.run(main())
13. 資料庫遷移
在實際專案中，隨著需求的變化，可能需要對資料庫結構進行修改，這時候就需要進行資料庫遷移（Migration）。資料庫遷移工具可以幫助我們管理資料庫結構變更的過程，並確保資料的一致性。
對於SQLite，可以使用sqlite3自帶的支援。對於MySQL等資料庫，常用的遷移工具包括Alembic、django.db.migrations等。
以下是一個簡單的資料庫遷移示例（以SQLite為例）：
import sqlite3 # 連線到 SQLite 資料庫 conn = sqlite3.connect('example.db') cursor = conn.cursor() # 執行遷移操作（修改表結構） cursor.execute("ALTER TABLE users ADD COLUMN email TEXT") # 提交併關閉連線 conn.commit() conn.close()
14. 備份與恢復
定期備份資料庫是保障資料安全的重要措施之一。備份可以透過資料庫管理工具或程式設計方式來實現，具體方法取決於資料庫型別和需求。
以下是一個簡單的備份資料庫的示例（以SQLite為例）：
import shutil # 備份資料庫檔案 shutil.copyfile('example.db', 'example_backup.db')
在實際應用中，備份資料庫時需要考慮資料庫是否處於活動狀態、備份檔案儲存位置、備份週期等因素。
15. 使用環境變數管理資料庫連線資訊
在實際專案中，將資料庫連線資訊硬編碼在程式碼中可能不夠安全或不夠靈活。一種更好的做法是使用環境變數來管理敏感資訊，比如資料庫的主機名、使用者名稱和密碼等。
以下是一個使用環境變數管理資料庫連線資訊的示例：
import os import sqlite3 import mysql.connector # 從環境變數中獲取資料庫連線資訊 DB_HOST = os.getenv('DB_HOST', 'localhost') DB_USER = os.getenv('DB_USER', 'username') DB_PASSWORD = os.getenv('DB_PASSWORD', 'password') DB_NAME = os.getenv('DB_NAME', 'mydatabase') # SQLite 連線 conn_sqlite = sqlite3.connect('example.db') cursor_sqlite = conn_sqlite.cursor() # MySQL 連線 conn_mysql = mysql.connector.connect( host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME ) cursor_mysql = conn_mysql.cursor() # 進行資料庫操作（省略） # 關閉連線 conn_sqlite.close() conn_mysql.close()
透過使用環境變數，我們可以輕鬆地在不同的環境中切換資料庫連線資訊，而無需修改程式碼。
16. 使用配置檔案管理資料庫連線資訊
除了使用環境變數，還可以使用配置檔案來管理資料庫連線資訊。這種方法更加靈活，可以根據需要配置不同的環境，如開發環境、測試環境和生產環境等。
以下是一個使用配置檔案管理資料庫連線資訊的示例：
import configparser import sqlite3 import mysql.connector # 從配置檔案中讀取資料庫連線資訊 config = configparser.ConfigParser() config.read('config.ini') DB_HOST = config.get('Database', 'host') DB_USER = config.get('Database', 'user') DB_PASSWORD = config.get('Database', 'password') DB_NAME = config.get('Database', 'database') # SQLite 連線 conn_sqlite = sqlite3.connect('example.db') cursor_sqlite = conn_sqlite.cursor() # MySQL 連線 conn_mysql = mysql.connector.connect( host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME ) cursor_mysql = conn_mysql.cursor() # 進行資料庫操作（省略） # 關閉連線 conn_sqlite.close() conn_mysql.close()
透過配置檔案的方式，我們可以將資料庫連線資訊集中管理，便於維護和修改。
17. 資料庫連線的安全性考慮
在連線資料庫時，需要考慮安全性問題，特別是涉及到密碼和敏感資訊的處理。一些常見的安全性措施包括：
- 不要將敏感資訊硬編碼在程式碼中，而是使用環境變數或配置檔案管理。
- 使用加密技術保護敏感資訊在傳輸過程中的安全性。
- 使用強密碼，並定期更換密碼。
- 限制資料庫使用者的許可權，避免賦予過高的許可權。
透過採取這些安全性措施，可以有效保護資料庫連線資訊和資料的安全。
總結
本文介紹了使用Python進行資料庫連線與操作的多種方法和技術。首先，我們學習瞭如何使用Python連線和操作SQLite和MySQL資料庫，包括建立表、插入資料、查詢資料等基本操作。然後，我們探討了一些高階技術，如引數化查詢、ORM框架、非同步資料庫庫、資料庫遷移、備份與恢復等，這些技術可以提高資料庫操作的效率和安全性。此外，我們還介紹瞭如何使用環境變數和配置檔案來管理資料庫連線資訊，以及一些資料庫連線的安全性考慮。透過這些技術和方法，我們可以更好地管理和保護資料庫，使得資料庫程式設計更加安全、靈活和高效。
在實際專案中，我們需要根據專案需求和安全標準選擇合適的技術和工具，確保資料庫連線和操作的安全性和可靠性。同時，我們也要不斷學習和探索新的技術，以跟上資料庫領域的發展和變化。希望本文能夠幫助讀者更好地理解和應用Python資料庫程式設計的相關知識，為實際專案開發提供幫助和指導。
點選關注，第一時間瞭解華為雲新鮮技術~