Psycopg是否提供executescript方法？有无替代执行方案？

Great question! Unfortunately, neither psycopg2 nor the newer psycopg3 includes a built-in executescript() method like SQLite's DB-API implementation. But there are several simple, reliable workarounds to run multi-statement SQL scripts with Psycopg—let's break them down:

1. Split and execute statements manually #

If you want full control over each statement (and avoid any autocommit or injection risks), split your script into individual statements and run them one by one with cursor.execute(). Just be mindful of edge cases like semicolons inside string literals or comments (you might need a basic parser for complex scripts, but this works for most simple use cases).

Example code:

import psycopg2

def execute_script(conn, script):
    # Split script into clean statements (skip empty lines/whitespace)
    statements = [stmt.strip() for stmt in script.split(';') if stmt.strip()]
    
    with conn.cursor() as cur:
        for stmt in statements:
            cur.execute(stmt)
    conn.commit()

# Usage
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pass")
sql_script = """
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL
    );
    INSERT INTO users (username) VALUES ('alice'), ('bob');
"""
execute_script(conn, sql_script)
conn.close()

2. Use autocommit mode for trusted scripts #

If your script comes from a fully trusted source (so SQL injection isn't a concern), you can enable autocommit on your connection and run the entire script in a single execute() call. Postgres allows executing multiple statements in one go when autocommit is enabled.

Example with psycopg2:

import psycopg2

conn = psycopg2.connect("dbname=your_db user=your_user password=your_pass")
conn.autocommit = True  # Required for multi-statement execution in one call

with conn.cursor() as cur:
    cur.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL
        );
        INSERT INTO products (name) VALUES ('Laptop'), ('Phone');
    """)

conn.close()

Important note: Only use this approach with scripts you control or fully trust—this bypasses Psycopg's built-in protection against multi-statement SQL injection attacks.

3. Psycopg3's multi=True parameter (cleanest option for newer versions) #

If you're using Psycopg 3 (the latest major release), you can use the multi=True parameter with cursor.execute() to safely run multiple statements. This is purpose-built for multi-statement scripts and avoids the need for autocommit.

Example:

import psycopg

with psycopg.connect("dbname=your_db user=your_user password=your_pass") as conn:
    with conn.cursor() as cur:
        # Run multiple statements explicitly with multi=True
        cur.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                id SERIAL PRIMARY KEY,
                user_id INT REFERENCES users(id)
            );
            INSERT INTO orders (user_id) VALUES (1), (2);
        """, multi=True)
    conn.commit()

4. Load and execute from a SQL file #

For larger scripts stored in a .sql file, simply read the file content and apply one of the above methods. This is perfect for database setup or migration scripts.

Example:

import psycopg2

def run_sql_file(conn, filepath):
    with open(filepath, 'r') as f:
        script_content = f.read()
    
    conn.autocommit = True
    with conn.cursor() as cur:
        cur.execute(script_content)
    conn.commit()

# Usage
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pass")
run_sql_file(conn, "database_setup.sql")
conn.close()

Final Notes #

Which approach you choose depends on your Psycopg version, script complexity, and security requirements. For most cases, splitting statements manually (option 1) or using Psycopg3's multi=True (option 3) are the safest and most flexible choices.

内容的提问来源于stack exchange，提问作者Charlie