Python sqlite3.Cursor.executescript Method
Complete guide to Python's sqlite3.Cursor.executescript method covering script execution, transactions, and usage patterns.
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.
basic_executescript.py
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.
transaction_behavior.py
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.
schema_init.py
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.
error_handling.py
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.
load_from_file.py
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.
combined_usage.py
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.
performance_test.py
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
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.
