如何使用python連接數據庫后通過占位符添加數據

# 如何使用Python連接數據庫后通過占位符添加數據

## 引言

在現代軟件開發中，數據庫操作是核心功能之一。Python作為最流行的編程語言之一，提供了多種數據庫連接方案。本文將詳細介紹如何通過Python連接主流數據庫（MySQL、SQLite、PostgreSQL），并重點講解使用占位符安全添加數據的最佳實踐。

## 一、數據庫連接基礎

### 1.1 為什么需要占位符
直接拼接SQL字符串存在嚴重安全隱患（SQL注入攻擊），例如：
```python
# 危險示例：字符串拼接
user_input = "admin'; DROP TABLE users;--"
sql = f"INSERT INTO users VALUES ('{user_input}')"

占位符系統通過參數化查詢實現： - 安全性：自動處理特殊字符轉義 - 性能：預編譯語句可重復使用 - 可讀性：SQL與數據分離更清晰

1.2 Python DB-API規范

Python通過PEP 249定義的標準數據庫接口，主要包含：

import sqlite3
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)")

二、主流數據庫連接示例

2.1 SQLite連接

內置支持，無需安裝額外驅動：

import sqlite3

def sqlite_demo():
    conn = sqlite3.connect("example.db")
    try:
        cursor = conn.cursor()
        # 使用?作為占位符
        cursor.execute("INSERT INTO users VALUES (?, ?)", (1, "張三"))
        conn.commit()
    finally:
        conn.close()

2.2 MySQL連接

需要安裝mysql-connector-python：

pip install mysql-connector-python

連接示例：

import mysql.connector

config = {
    "host": "localhost",
    "user": "root",
    "password": "mypassword",
    "database": "testdb"
}

conn = mysql.connector.connect(**config)
cursor = conn.cursor()
# 使用%s作為占位符（注意不是格式化字符串的%s）
cursor.execute("INSERT INTO products (name, price) VALUES (%s, %s)", ("筆記本電腦", 5999))
conn.commit()

2.3 PostgreSQL連接

安裝psycopg2驅動：

pip install psycopg2-binary

操作示例：

import psycopg2

dsn = "dbname=test user=postgres password=secret host=127.0.0.1"
conn = psycopg2.connect(dsn)
# 使用%s作為占位符
cursor.execute("INSERT INTO employees (name, department) VALUES (%s, %s)", 
               ("李四", "人力資源"))
conn.commit()

三、占位符高級用法

3.1 批量插入數據

使用executemany提升批量插入效率：

data = [
    ("手機", 2999),
    ("耳機", 399),
    ("智能手表", 1299)
]
cursor.executemany("INSERT INTO products (name, price) VALUES (?, ?)", data)

3.2 命名占位符

部分驅動支持更易讀的字典參數：

# SQLite示例
cursor.execute(
    "INSERT INTO users VALUES (:id, :name)", 
    {"id": 2, "name": "王五"}
)

# PostgreSQL示例
cursor.execute(
    "INSERT INTO logs (level, message) VALUES (%(level)s, %(msg)s)",
    {"level": "ERROR", "msg": "Disk full"}
)

3.3 事務處理

確保數據完整性：

try:
    cursor.execute("INSERT INTO accounts (user, balance) VALUES (?, ?)", ("Alice", 1000))
    cursor.execute("UPDATE accounts SET balance = balance - ? WHERE user = ?", (500, "Bob"))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Transaction failed: {e}")

四、安全注意事項

1. 永遠不要信任用戶輸入：即使使用占位符也要驗證數據格式
2. 最小權限原則：數據庫用戶只授予必要權限
3. 錯誤處理：避免暴露數據庫結構信息

try:
    cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
except sqlite3.DatabaseError as e:
    print("Database error occurred")
    # 不要打印原始錯誤給用戶

五、性能優化建議

1. 使用連接池（如SQLAlchemy的池化功能）
2. 大批量插入考慮使用COPY命令（PostgreSQL）或LOAD DATA（MySQL）
3. 建立適當索引提升查詢速度

六、完整示例代碼

SQLite綜合操作示例：

import sqlite3
from contextlib import closing

def init_db():
    with closing(sqlite3.connect("app.db")) as conn:
        conn.execute("""
        CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            content TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """)
        conn.commit()

def add_post(title, content):
    with closing(sqlite3.connect("app.db")) as conn:
        conn.execute(
            "INSERT INTO posts (title, content) VALUES (?, ?)",
            (title.strip(), content.strip())
        )
        conn.commit()

if __name__ == "__main__":
    init_db()
    add_post("Python數據庫教程", "本文介紹如何使用占位符...")

結語

通過本文的學習，您應該已經掌握： 1. Python連接三大主流數據庫的方法 2. 參數化查詢的正確使用姿勢 3. 數據庫操作的安全實踐

建議進一步學習ORM工具（如SQLAlchemy、Django ORM）可以更高效地進行數據庫操作。記?。毫己玫臄祿觳僮髁晳T是構建安全穩定應用的基石。 “`