Python sqlite3.Connection.executescript Method
Last modified April 15, 2025
This comprehensive guide explores Python's sqlite3.Connection.executescript method,
which executes multiple SQL statements at once. We'll cover basic usage, transaction behavior,
and practical examples.
Basic Definitions
The executescript method executes a script of SQL statements in a single call.
It's part of the sqlite3.Connection class and is useful for batch operations.
Key characteristics: it executes all statements in one transaction, accepts a string parameter with SQL commands, and doesn't return any results. It's ideal for schema setup or migrations.
Basic executescript Example
Here's a simple example demonstrating how to use executescript to create tables
and insert data in one operation.
import sqlite3
with sqlite3.connect('test.db') as conn:
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');
"""
conn.executescript(script)
This example creates two related tables and inserts sample data using a single script.
The with statement ensures proper connection cleanup.
All statements execute as part of one transaction. If any statement fails, the entire transaction rolls back automatically.
Transaction Behavior
This example demonstrates the transaction behavior of executescript when
an error occurs in the script.
import sqlite3
with sqlite3.connect('transactions.db') as conn:
try:
script = """
CREATE TABLE IF NOT EXISTS accounts (
id INTEGER PRIMARY KEY,
name TEXT,
balance REAL
);
INSERT INTO accounts (name, balance) VALUES ('Alice', 1000);
INSERT INTO accounts (name, balance) VALUES ('Bob', 500);
-- This will cause an error (table doesn't exist)
INSERT INTO transactions (account_id, amount) VALUES (1, -100);
"""
conn.executescript(script)
except sqlite3.Error as e:
print("Error executing script:", e)
# Verify no partial changes were committed
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM accounts")
print("Accounts count:", cursor.fetchone()[0]) # Outputs 0
The script contains an error (inserting into non-existent table). The entire transaction rolls back, leaving the database unchanged.
This atomic behavior ensures data consistency when executing multiple related statements.
Schema Migration Script
executescript is ideal for database schema migrations. This example shows
a version upgrade script.
import sqlite3
with sqlite3.connect('app.db') as conn:
migration_script = """
-- Add new columns to existing table
ALTER TABLE users ADD COLUMN last_login TEXT;
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';
-- Create new tables for features
CREATE TABLE IF NOT EXISTS user_preferences (
user_id INTEGER PRIMARY KEY,
theme TEXT DEFAULT 'light',
notifications INTEGER DEFAULT 1,
FOREIGN KEY(user_id) REFERENCES users(id)
);
-- Backfill existing data
UPDATE users SET status = 'active' WHERE status IS NULL;
"""
conn.executescript(migration_script)
This script performs multiple schema changes atomically. It adds columns, creates a new table, and backfills data.
Using executescript ensures all changes succeed or fail together,
preventing partial migrations.
Loading SQL from External File
This example shows how to load SQL from an external file and execute it with
executescript.
import sqlite3
def load_and_execute_sql(db_file, sql_file):
with sqlite3.connect(db_file) as conn:
with open(sql_file, 'r') as f:
sql_script = f.read()
conn.executescript(sql_script)
# Execute schema.sql file against test.db
load_and_execute_sql('test.db', 'schema.sql')
The function reads SQL from a file and executes it against the database. This pattern is useful for deployment scripts.
Error handling should be added for production use to handle file reading and SQL execution errors.
Combining with Parameterized Queries
While executescript doesn't support parameters directly, you can combine
it with regular executes for mixed operations.
import sqlite3
with sqlite3.connect('inventory.db') as conn:
# Schema setup with executescript
conn.executescript("""
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY,
name TEXT,
price REAL,
stock INTEGER
);
CREATE TABLE IF NOT EXISTS categories (
id INTEGER PRIMARY KEY,
name TEXT
);
""")
# Parameterized inserts with execute
cursor = conn.cursor()
products = [
('Laptop', 999.99, 10),
('Phone', 699.99, 25),
('Tablet', 399.99, 15)
]
cursor.executemany("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)", products)
This example uses executescript for schema creation and regular
execute for parameterized inserts.
The combination provides both the convenience of script execution and the safety of parameterized queries.
Performance Considerations
This example demonstrates how executescript can improve performance
for bulk operations.
import sqlite3
import time
def time_operations():
with sqlite3.connect('perf_test.db') as conn:
conn.execute("DROP TABLE IF EXISTS test_data")
conn.execute("CREATE TABLE test_data (id INTEGER, value TEXT)")
# Method 1: Individual executes
start = time.time()
cursor = conn.cursor()
for i in range(1000):
cursor.execute(f"INSERT INTO test_data VALUES ({i}, 'test{i}')")
conn.commit()
print(f"Individual executes: {time.time() - start:.3f} seconds")
# Method 2: executescript
conn.execute("DROP TABLE test_data")
conn.execute("CREATE TABLE test_data (id INTEGER, value TEXT)")
start = time.time()
script = "\n".join(f"INSERT INTO test_data VALUES ({i}, 'test{i}');"
for i in range(1000))
conn.executescript(script)
print(f"executescript: {time.time() - start:.3f} seconds")
time_operations()
The example compares individual inserts with a single executescript call.
The script approach is typically faster for bulk operations.
However, for very large scripts, memory usage and SQLite's SQL length limit should be considered.
Complex Database Initialization
This example shows a complete database initialization script with views, indexes, and sample data.
import sqlite3
def initialize_database(db_file):
with sqlite3.connect(db_file) as conn:
init_script = """
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
-- Main tables
CREATE TABLE IF NOT EXISTS customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount REAL NOT NULL,
status TEXT DEFAULT 'pending',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(customer_id) REFERENCES customers(id)
);
-- Indexes
CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
-- Views
CREATE VIEW IF NOT EXISTS customer_totals AS
SELECT c.id, c.name, SUM(o.amount) as total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;
-- Sample data
INSERT INTO customers (name, email) VALUES
('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com');
INSERT INTO orders (customer_id, amount) VALUES
(1, 99.99),
(1, 49.99),
(2, 149.99);
"""
conn.executescript(init_script)
initialize_database('ecommerce.db')
This comprehensive initialization script sets up a complete database schema with tables, indexes, views, and sample data.
The use of executescript ensures all components are created atomically,
and the PRAGMA statements configure database behavior before schema creation.
Best Practices
- Use for schema changes: Ideal for migrations and initial setup
- Handle errors: Wrap in try-except for proper error handling
- Combine approaches: Mix with parameterized queries when needed
- Consider size limits: SQLite has SQL length limits (1,000,000 bytes)
- Test scripts: Verify complex scripts in development first
Source References
Author
List all Python tutorials.