Python 與數(shù)據(jù)庫交互的 15 個 SQL 查詢技巧
作者：手把手PythonAI編程
通過本文，你學會了如何使用 Python 進行基本的 SQL 查詢，包括連接數(shù)據(jù)庫、創(chuàng)建表、插入數(shù)據(jù)、查詢記錄、更新和刪除記錄等。
當你剛開始學習 Python 與數(shù)據(jù)庫交互時，可能會覺得有點不知所所措。但別擔心，通過本文，你將學會如何使用 Python 進行基本的 SQL 查詢，并掌握一些高級技巧。讓我們一步步來，從簡單的查詢到更復雜的操作。
1. 連接到數(shù)據(jù)庫
首先，你需要連接到數(shù)據(jù)庫。這里以 SQLite 為例，因為它是輕量級且易于上手的。
import sqlite3
# 連接到 SQLite 數(shù)據(jù)庫（如果不存在則會創(chuàng)建）
conn = sqlite3.connect('example.db')
# 創(chuàng)建一個游標對象
cursor = conn.cursor()2. 創(chuàng)建表
在開始查詢之前，我們需要一個表。假設我們要創(chuàng)建一個存儲用戶信息的表。
# 創(chuàng)建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL,
email TEXT UNIQUE NOT NULL
)
''')3. 插入數(shù)據(jù)
接下來，我們可以向表中插入一些數(shù)據(jù)。
# 插入數(shù)據(jù)
cursor.execute('''
INSERT INTO users (name, age, email) VALUES (?, ?, ?)
''', ('Alice', 30, 'alice@example.com'))
cursor.execute('''
INSERT INTO users (name, age, email) VALUES (?, ?, ?)
''', ('Bob', 25, 'bob@example.com'))
# 提交事務
conn.commit()4. 查詢所有記錄
現(xiàn)在，讓我們查詢表中的所有記錄。
# 查詢所有記錄
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
print(row)5. 查詢特定記錄
你可以通過條件來查詢特定的記錄。
# 查詢特定記錄
cursor.execute('SELECT * FROM users WHERE age > 25')
rows = cursor.fetchall()
for row in rows:
print(row)6. 使用參數(shù)化查詢
為了避免 SQL 注入攻擊，建議使用參數(shù)化查詢。
# 參數(shù)化查詢
age_threshold = 25
cursor.execute('SELECT * FROM users WHERE age > ?', (age_threshold,))
rows = cursor.fetchall()
for row in rows:
print(row)7. 更新記錄
你可以更新表中的記錄。
# 更新記錄
cursor.execute('UPDATE users SET age = ? WHERE name = ?', (31, 'Alice'))
conn.commit()8. 刪除記錄
你也可以刪除表中的記錄。
# 刪除記錄
cursor.execute('DELETE FROM users WHERE name = ?', ('Bob',))
conn.commit()9. 使用 JOIN 查詢
如果你有多個表，可以使用 JOIN 來查詢相關數(shù)據(jù)。
# 假設有一個 orders 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
user_id INTEGER,
product TEXT,
FOREIGN KEY (user_id) REFERENCES users (id)
)
''')
# 插入訂單數(shù)據(jù)
cursor.execute('INSERT INTO orders (user_id, product) VALUES (?, ?)', (1, 'Laptop'))
conn.commit()
# 使用 JOIN 查詢
cursor.execute('''
SELECT users.name, orders.product
FROM users
JOIN orders ON users.id = orders.user_id
''')
rows = cursor.fetchall()
for row in rows:
print(row)10. 使用聚合函數(shù)
聚合函數(shù)可以幫助你處理和匯總數(shù)據(jù)。
# 使用聚合函數(shù)
cursor.execute('SELECT COUNT(*) FROM users')
count = cursor.fetchone()[0]
print(f'Total number of users: {count}')11. 分組和排序
你可以使用 GROUP BY 和 ORDER BY 來分組和排序數(shù)據(jù)。
# 分組和排序
cursor.execute('''
SELECT age, COUNT(*)
FROM users
GROUP BY age
ORDER BY age DESC
''')
rows = cursor.fetchall()
for row in rows:
print(row)12. 使用子查詢
子查詢可以在查詢中嵌套另一個查詢。
# 使用子查詢
cursor.execute('''
SELECT *
FROM users
WHERE id IN (SELECT user_id FROM orders)
''')
rows = cursor.fetchall()
for row in rows:
print(row)13. 使用事務管理
事務管理可以幫助你確保數(shù)據(jù)的一致性和完整性。
# 事務管理
try:
cursor.execute('INSERT INTO users (name, age, email) VALUES (?, ?, ?)', ('Charlie', 28, 'charlie@example.com'))
cursor.execute('INSERT INTO orders (user_id, product) VALUES (?, ?)', (3, 'Phone'))
conn.commit()
except Exception as e:
conn.rollback()
print(f'Error: {e}')14. 使用上下文管理器
上下文管理器可以自動管理資源，如關閉數(shù)據(jù)庫連接。
# 使用上下文管理器
with sqlite3.connect('example.db') as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
print(row)15. 使用 ORM 框架
對于更復雜的項目，可以考慮使用 ORM 框架，如 SQLAlchemy。
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
age = Column(Integer, nullable=False)
email = Column(String, unique=True, nullable=False)
class Order(Base):
__tablename__ = 'orders'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'))
product = Column(String, nullable=False)
user = relationship("User")
# 創(chuàng)建數(shù)據(jù)庫引擎
engine = create_engine('sqlite:///example.db')
# 創(chuàng)建表
Base.metadata.create_all(engine)
# 創(chuàng)建會話
Session = sessionmaker(bind=engine)
session = Session()
# 插入數(shù)據(jù)
new_user = User(name='David', age=27, email='david@example.com')
session.add(new_user)
session.commit()
# 查詢數(shù)據(jù)
users = session.query(User).all()
for user in users:
print(user.name, user.age, user.email)實戰(zhàn)案例：用戶管理系統(tǒng)
假設你要開發(fā)一個簡單的用戶管理系統(tǒng)，需要實現(xiàn)以下功能：
- 添加用戶：允許管理員添加新用戶。
- 查詢用戶：允許管理員按條件查詢用戶。
- 更新用戶信息：允許管理員更新用戶的年齡和郵箱。
- 刪除用戶：允許管理員刪除用戶。
def add_user(name, age, email):
with sqlite3.connect('example.db') as conn:
cursor = conn.cursor()
cursor.execute('INSERT INTO users (name, age, email) VALUES (?, ?, ?)', (name, age, email))
conn.commit()
def query_users(age_threshold):
with sqlite3.connect('example.db') as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM users WHERE age > ?', (age_threshold,))
rows = cursor.fetchall()
return rows
def update_user(user_id, new_age, new_email):
with sqlite3.connect('example.db') as conn:
cursor = conn.cursor()
cursor.execute('UPDATE users SET age = ?, email = ? WHERE id = ?', (new_age, new_email, user_id))
conn.commit()
def delete_user(user_id):
with sqlite3.connect('example.db') as conn:
cursor = conn.cursor()
cursor.execute('DELETE FROM users WHERE id = ?', (user_id,))
conn.commit()
# 示例操作
add_user('Eve', 32, 'eve@example.com')
print(query_users(30))
update_user(1, 33, 'alice_new@example.com')
delete_user(2)總結
通過本文，你學會了如何使用 Python 進行基本的 SQL 查詢，包括連接數(shù)據(jù)庫、創(chuàng)建表、插入數(shù)據(jù)、查詢記錄、更新和刪除記錄等。此外，你還了解了如何使用參數(shù)化查詢、JOIN 查詢、聚合函數(shù)、分組和排序、子查詢、事務管理和 ORM 框架。最后，我們通過一個實戰(zhàn)案例展示了如何將這些知識應用于實際項目中。希望這些內(nèi)容對你有所幫助！
責任編輯：趙寧寧
來源：
手把手PythonAI編程
