How to Execute External SQL Queries (Including Non-Table Creation Queries) in Python?

Reading External SQL Script in Python

Question: How can I execute external SQL queries from within a Python script, especially when dealing with non-table-creation queries?

Explanation:

To run SQL commands in Python, you can use the sqlite3 module. This module provides an interface to the SQLite database management system. To read an external SQL script, you can follow these steps:

1. Open and read the file: Open the SQL file and read its content into a string.
2. Split the commands: Split the string into a list of individual SQL commands, separated by ;.
3. Execute the commands: Iterate through the list of commands and execute them one by one using the c.execute(command) method of the cursor object.

Code Example:

import sqlite3

# Open the database connection
conn = sqlite3.connect('database.db')
c = conn.cursor()

# Read the SQL file
with open('external_sql.sql', 'r') as f:
    sql_file = f.read()

# Split the commands
sql_commands = sql_file.split(';')

# Execute the commands
for command in sql_commands:
    try:
        c.execute(command)
    except sqlite3.OperationalError as e:
        print(f"Command skipped: {e}")

# Commit the changes and close the connection
conn.commit()
c.close()
conn.close()

Specific Queries:

The queries 1.1 and 1.2 in your provided SQL script are non-table-creation queries. To execute them, you can use the c.execute method directly rather than the SELECT * query:

# Query 1.1
result = c.execute(
    """
    SELECT ANAME,zookeepid
    FROM ANIMAL, HANDLES
    WHERE AID=ANIMALID;
    """
)

# Get the results
rows = result.fetchall()

# Print the results
print("Query 1.1:")
for row in rows:
    print(row)

# Query 1.2
result = c.execute(
    """
    SELECT ZNAME, SUM(TIMETOFEED)
    FROM ZOOKEEPER, ANIMAL, HANDLES
    WHERE AID=ANIMALID AND ZOOKEEPID=ZID
    GROUP BY zookeeper.zname;
    """
)

# Get the results
rows = result.fetchall()

# Print the results
print("Query 1.2:")
for row in rows:
    print(row)

The above is the detailed content of How to Execute External SQL Queries (Including Non-Table Creation Queries) in Python?. For more information, please follow other related articles on the PHP Chinese website!