Reading external SQL script in Python

Beyond the basic: advanced techniques

While the straightforward open() and executescript() method is effective, sometimes you require more flexibility. Let's dwell into some advanced techniques.

Handling placeholders like a pro

SQL files might contain placeholders for dynamic data insertion. You can change these using Python's string formatting:

# SQL with a placeholder: {name}
sql_command = "INSERT INTO users (name) VALUES ('{name}')"

# Replace placeholder with the actual value
formatted_command = sql_command.format(name='John Doe')

# Execute the command, making John Doe a little more real each day
cursor.execute(formatted_command)


Being the Error Ninja

Enclose your SQL executions within a try-except block, gracefully catching exceptions:

try:
    cursor.execute(formatted_command)  # Fingers crossed...
except sqlite3.DatabaseError as error:
    print(f"Caught you, little error: {error}")


Choosing your battles

When you need to execute specific queries from a file, use regex or if-conditions to filter out the right ones:

import re

pattern = re.compile(r'INSERT INTO users')
selected_queries = [q for q in queries if pattern.match(q)]  # Gotcha!

# Execute each query one by one, because one is a magic number
for query in selected_queries:
    cursor.execute(query)


Cleaning up after the party

Ensure you close the file and database connections properly.

file.close()  # Always put your toys back where they belong
conn.close()  # Lights off when you leave


Employing those techniques will lift your code from standard to extraordinary.

Going modular

Structured code is always easier to digest. Define functions for better clarity and reuse:

def execute_sql_script(file_path, db_path):
    with open(file_path, 'r') as file, sqlite3.connect(db_path) as conn:
        conn.executescript(file.read())


Multiple executions

executescript() runs multiple SQLite commands from a file. cursor.executemany() runs multiple parameterized queries:

data = [('Alice',), ('Bob',)]  # We've got Alice and Bob in the house!
cursor.executemany("INSERT INTO users (name) VALUES (?)", data)


Verifications

Check cursor.rowcount after a query to see the number of affected rows:

cursor.execute("DELETE FROM users WHERE age < 18")
print(f"Affected rows: {cursor.rowcount}")  # How many kiddos had to leave?


Custom placeholders

import re

query = re.sub(r'{{username}}', 'JohnDoe', query)  # Because JohnDoe is a man of mystery


Combine all these tricks, and you get smooth script execution with zero glitches.