How Can I Execute SQL Queries from an External File in Python?

Running SQL Queries from an External File in Python

In Python, executing SQL queries is a versatile task. This article focuses on reading an external SQL file and executing the queries within.

The Issue: Executing Queries from a File

When executing specific queries from a file, it's not immediately clear how to tailor the c.execute() function to fetch query results. The provided code executes commands successfully but requires clarification for the line:

result = c.execute("SELECT * FROM %s;" % table);

Understanding String Formatting

The key to understanding this line is string formatting in Python. %s functions as a placeholder, and the following variable table replaces it. For example:

a = "Hi, my name is %s and I have a %s hat" % ("Azeirah", "cool")
print(a)
# Output: Hi, my name is Azeirah and I have a Cool hat

Applying String Formatting to Queries

By substituting %s with the table variable, the c.execute() function executes queries dynamically. The for loop iterates through tables, allowing sequential query execution.

The following code provides a reusable function for executing SQL scripts from files:

def executeScriptsFromFile(filename):
    fd = open(filename, 'r')
    sqlFile = fd.read()
    fd.close()
    
    sqlCommands = sqlFile.split(';')
    for command in sqlCommands:
        try:
            c.execute(command)
        except OperationalError, msg:
            print("Command skipped: ", msg)

To use it, simply call:

executeScriptsFromFile('zookeeper.sql')

With the power of string formatting, executing SQL queries from an external file in Python becomes a straightforward process, enabling dynamic query execution and efficient database manipulation.

The above is the detailed content of How Can I Execute SQL Queries from an External File in Python?. For more information, please follow other related articles on the PHP Chinese website!