如何使用python连接数据库后通过占位符添加数据

发布时间：2021-12-17 12:28:58 作者：小新
来源：亿速云阅读：181

# 如何使用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}")

四、安全注意事项

永远不要信任用户输入：即使使用占位符也要验证数据格式
最小权限原则：数据库用户只授予必要权限
错误处理：避免暴露数据库结构信息

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

五、性能优化建议

使用连接池（如SQLAlchemy的池化功能）
大批量插入考虑使用COPY命令（PostgreSQL）或LOAD DATA（MySQL）
建立适当索引提升查询速度

六、完整示例代码

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）可以更高效地进行数据库操作。记住：良好的数据库操作习惯是构建安全稳定应用的基石。 “`