Python sqlite3.Cursor.executescript Method

Last modified April 15, 2025

This comprehensive guide explores Python's sqlite3.Cursor.executescript method, which executes multiple SQL statements at once. We'll cover basic usage, transaction behavior, error handling, and practical examples.

Basic Definitions

The executescript method executes a script of SQL statements separated by semicolons. It's part of the sqlite3.Cursor class and provides a way to run multiple statements in a single call.

Key characteristics: it executes all statements in one transaction, doesn't support parameter substitution, and is useful for schema initialization or bulk operations. The method automatically commits if successful.

Basic executescript Example

Here's the simplest usage of executescript to create tables and insert data.

import sqlite3

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    
    script = """
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    );
    
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY,
        user_id INTEGER,
        amount REAL,
        FOREIGN KEY(user_id) REFERENCES users(id)
    );
    
    INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
    INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
    """
    
    cursor.executescript(script)

This example shows how to execute multiple SQL statements in one call. The script creates two related tables and inserts initial data. The with statement ensures proper resource cleanup.

Note that executescript commits the transaction automatically upon successful completion, unlike execute which requires explicit commit.

Transaction Behavior with executescript

executescript runs all statements in a single transaction. If any statement fails, the entire transaction is rolled back.

import sqlite3

try:
    with sqlite3.connect('test.db') as conn:
        cursor = conn.cursor()
        
        script = """
        CREATE TABLE products (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            price REAL CHECK(price > 0)
        );
        
        INSERT INTO products (name, price) VALUES ('Laptop', 999.99);
        INSERT INTO products (name, price) VALUES ('Mouse', -19.99);  -- This will fail
        INSERT INTO products (name, price) VALUES ('Keyboard', 49.99);
        """
        
        cursor.executescript(script)
except sqlite3.Error as e:
    print("Transaction failed:", e)

# Verify no data was inserted
with sqlite3.connect('test.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM products")
    print("Rows in products:", cursor.fetchone()[0])  # Output: 0

This example demonstrates atomic transaction behavior. The second INSERT violates the CHECK constraint, causing the entire script to fail. No changes are persisted to the database.

The atomicity guarantee is crucial for maintaining database consistency when executing multiple related operations.

Schema Initialization Script

executescript is particularly useful for database schema initialization. Here's a complete schema setup example.

import sqlite3

def initialize_database(db_path):
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        
        schema_script = """
        PRAGMA foreign_keys = ON;
        
        CREATE TABLE departments (
            dept_id INTEGER PRIMARY KEY,
            name TEXT NOT NULL UNIQUE,
            budget REAL DEFAULT 0.0
        );
        
        CREATE TABLE employees (
            emp_id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            salary REAL CHECK(salary >= 0),
            dept_id INTEGER,
            hire_date TEXT DEFAULT CURRENT_DATE,
            FOREIGN KEY(dept_id) REFERENCES departments(dept_id)
        );
        
        CREATE INDEX idx_employees_dept ON employees(dept_id);
        
        CREATE TRIGGER update_dept_budget
        AFTER INSERT ON employees
        BEGIN
            UPDATE departments
            SET budget = budget + NEW.salary
            WHERE dept_id = NEW.dept_id;
        END;
        
        INSERT INTO departments (name, budget) VALUES ('Engineering', 500000);
        INSERT INTO departments (name, budget) VALUES ('Marketing', 300000);
        """
        
        cursor.executescript(schema_script)

initialize_database('company.db')

This example creates a complete database schema with tables, indexes, triggers, and initial data. The PRAGMA statement enables foreign key constraints.

Using executescript for schema initialization ensures all schema elements are created atomically and maintains proper dependency ordering.

Error Handling with executescript

Proper error handling is essential when executing scripts. This example shows how to catch and handle errors.

import sqlite3

def run_safe_script(db_path, script):
    try:
        with sqlite3.connect(db_path) as conn:
            cursor = conn.cursor()
            cursor.executescript(script)
    except sqlite3.Error as e:
        print(f"Script execution failed: {e}")
        return False
    return True

script = """
CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY,
    message TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO logs (message) VALUES ('Script started');
BAD SQL STATEMENT;  -- This will cause an error
INSERT INTO logs (message) VALUES ('Script completed');
"""

success = run_safe_script('logs.db', script)
print("Script success:", success)

# Verify partial execution
with sqlite3.connect('logs.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT message FROM logs")
    print("Log entries:", cursor.fetchall())  # Output: []

This example demonstrates that when an error occurs in the script, no changes are applied to the database. The table creation and first insert are rolled back.

The sqlite3.Error exception catches all SQLite-related errors, including syntax errors and constraint violations.

Loading SQL Script from File

For larger scripts, it's practical to store them in separate files. This example shows how to load and execute a script from a file.

import sqlite3

def execute_sql_file(db_path, file_path):
    try:
        with open(file_path, 'r') as file:
            sql_script = file.read()
            
        with sqlite3.connect(db_path) as conn:
            cursor = conn.cursor()
            cursor.executescript(sql_script)
            
    except FileNotFoundError:
        print(f"Error: SQL file not found at {file_path}")
    except sqlite3.Error as e:
        print(f"Database error: {e}")
    except Exception as e:
        print(f"Unexpected error: {e}")

# Assume we have a file named 'schema.sql' with SQL statements
execute_sql_file('inventory.db', 'schema.sql')

This approach separates SQL code from Python code, making maintenance easier. The script file can be edited without modifying the Python application.

For production use, consider adding validation for the SQL file content and implementing proper logging instead of just printing errors.

Combining executescript with execute

You can combine executescript with regular execute calls for more complex scenarios.

import sqlite3

def setup_application_database():
    with sqlite3.connect('app.db') as conn:
        cursor = conn.cursor()
        
        # Execute schema creation script
        cursor.executescript("""
            CREATE TABLE IF NOT EXISTS config (
                key TEXT PRIMARY KEY,
                value TEXT
            );
            
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY,
                username TEXT UNIQUE,
                password_hash TEXT
            );
        """)
        
        # Insert configuration using execute with parameters
        config_data = [
            ('max_connections', '100'),
            ('timeout', '30'),
            ('theme', 'dark')
        ]
        cursor.executemany(
            "INSERT INTO config (key, value) VALUES (?, ?)",
            config_data
        )
        
        # Create indexes separately
        cursor.execute("""
            CREATE INDEX IF NOT EXISTS idx_users_username 
            ON users(username)
        """)

setup_application_database()

This example shows a hybrid approach where schema setup uses executescript, while data insertion uses parameterized executemany for security.

The combination allows leveraging the strengths of each method: script for schema, parameterized queries for data. This approach prevents SQL injection for user data.

Performance Considerations

For bulk data operations, executescript can be more efficient than multiple execute calls, but there are tradeoffs.

import sqlite3
import time

def test_performance():
    # Test executescript
    start = time.time()
    with sqlite3.connect(':memory:') as conn:
        cursor = conn.cursor()
        script = "CREATE TABLE test1 (id INTEGER, data TEXT);\n"
        script += "\n".join(f"INSERT INTO test1 VALUES ({i}, 'data{i}');" 
                          for i in range(1000))
        cursor.executescript(script)
    script_time = time.time() - start
    
    # Test multiple executes
    start = time.time()
    with sqlite3.connect(':memory:') as conn:
        cursor = conn.cursor()
        cursor.execute("CREATE TABLE test2 (id INTEGER, data TEXT)")
        for i in range(1000):
            cursor.execute(f"INSERT INTO test2 VALUES (?, ?)", (i, f'data{i}'))
        conn.commit()
    execute_time = time.time() - start
    
    print(f"executescript: {script_time:.3f} seconds")
    print(f"multiple execute: {execute_time:.3f} seconds")

test_performance()

This performance test compares inserting 1000 rows using executescript versus individual execute calls. Results will vary but typically show executescript is faster for bulk operations.

However, executescript doesn't support parameterized queries, so for user-provided data, individual execute calls with parameters are safer despite being slower.

Best Practices

• Use for schema initialization: Ideal for creating tables, indexes, triggers
• Avoid for user input: Doesn't support parameterized queries
• Handle errors properly: Scripts fail atomically - plan accordingly
• Consider file storage: Large scripts are better in separate files
• Combine methods: Use with execute/executemany when appropriate

Source References

• Python sqlite3 Documentation
• SQLite SQL Syntax
• SQLite Transactions

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Python tutorials.