Home >Database >Mysql Tutorial >How Can I Execute SQL Queries from an External Script in Python and Handle Table-Specific Queries?
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.
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.
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!