Home >Database >Mysql Tutorial >How Can I Execute SQL Queries from an External Script in Python and Handle Table-Specific Queries?

How Can I Execute SQL Queries from an External Script in Python and Handle Table-Specific Queries?

Mary-Kate Olsen
Mary-Kate OlsenOriginal
2024-12-30 03:54:08372browse

How Can I Execute SQL Queries from an External Script in Python and Handle Table-Specific Queries?

Executing SQL Queries from External Script in Python

When working with Python and SQL, it's common to encounter the need to execute SQL queries stored in an external script. This process involves reading the script, breaking it down into individual queries, and executing them one by one.

Provided Code

import sqlite3
from sqlite3 import OperationalError

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

# Open and read the file as a single buffer
fd = open('ZooDatabase.sql', 'r')
sqlFile = fd.read()
fd.close()

# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')

# Execute every command from the input file
for command in sqlCommands:
    # This will skip and report errors
    # For example, if the tables do not yet exist, this will skip over
    # the DROP TABLE commands
    try:
        c.execute(command)
    except OperationalError, msg:
        print "Command skipped: ", msg

This code effectively reads the SQL script into a string, splits it into individual commands, and executes them using c.execute(). However, the remaining code in the provided snippet is incomplete and needs to be modified to handle table-specific queries.

Executing Table-Specific Queries

The code is missing the step where it executes table-specific queries. To do this, you can modify the code as follows:

for table in ['ZooKeeper', 'Animal', 'Handles']:
    result = c.execute("SELECT ANAME,zookeepid FROM ANIMAL, HANDLES WHERE AID=ANIMALID;")
    rows = result.fetchall()
    print("\n--- TABLE", table, "\n")

    for desc in result.description:
        print(desc[0].rjust(22, ' '), end=", ")  # Print column names

    print()

    for row in rows:
        for value in row:
            print(str(value).rjust(22, ' '), end=", ")  # Print each value
        print()

This code will loop through the table names and execute the specified queries for each table. It then formats and prints the table details, column names, and row data.

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

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn