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

Querying External SQL File in Python

Confusion:

When executing SQL queries from an external file, some developers may get confused by lines like:

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

Explanation:

String formatting in Python allows us to dynamically replace placeholders (%s) with values. In this case, %s is replaced by the value of the table variable, which is a string representing the table name. So, if table is 'Animal', the query becomes "SELECT * FROM Animal;".

Using the Provided Code:

The provided Python code includes a function executeScriptsFromFile that can be used to execute all the SQL commands in an external file.

def executeScriptsFromFile(filename):
    with open(filename, 'r') as fd:
        sqlFile = fd.read()
    sqlCommands = sqlFile.split(';')

    for command in sqlCommands:
        try:
            c.execute(command)
        except OperationalError as msg:
            print("Command skipped: ", msg)

You can use this function to execute the SQL queries in your zookeeper.sql file:

executeScriptsFromFile('zookeeper.sql')

Queries 1.1 and 1.2:

The queries 1.1 and 1.2 are already included in the zookeeper.sql file. The code above will execute them when the file is loaded.

Complete Code:

Combining the executeScriptsFromFile function and the table looping code, your complete Python code can be simplified as follows:

import sqlite3
from sqlite3 import OperationalError

conn = sqlite3.connect('csc455_HW3.db')
c = conn.cursor()

executeScriptsFromFile('zookeeper.sql')

for table in ['ZooKeeper', 'Animal', 'Handles']:
    result = c.execute("SELECT * FROM %s;" % table)
    rows = result.fetchall()
    print("\n--- TABLE ", table, "\n")
    for desc in result.description:
        print(desc[0].rjust(22, ' '), end=',')
    print()  # End the line with column names

    for row in rows:
        for value in row:
            print(str(value).rjust(22, ' '))
        print()

c.close()
conn.close()

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