reading external sql script in python

I am working on a learning how to execute SQL in python (I know SQL, not Python).

I have an external sql file. It creates and inserts data into three tables 'Zookeeper', 'Handles', 'Animal'.

Then I have a series of queries to run off the tables. The below queries are in the zookeeper.sql file that I load in at the top of the python script. Example for the first two are:

--1.1 SELECT ANAME,zookeepid FROM ANIMAL, HANDLES WHERE AID=ANIMALID; 

--1.2

SELECT ZNAME, SUM(TIMETOFEED) FROM ZOOKEEPER, ANIMAL, HANDLES WHERE AID=ANIMALID AND ZOOKEEPID=ZID GROUP BY zookeeper.zname; 

These all execute fine in SQL. Now I need to execute them from within Python. I have been given and completed code to read in the file. Then execute all the queries in the loop.

The 1.1 and 1.2 is where I am getting confused. I believe in the loop this is the line where I should put in something to run the first and then second query.

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

but what? I think I am missing something very obvious. I think what is throwing me off is % table. In query 1.1 and 1.2, I am not creating a table, but rather looking for a query result.

My entire python code is below.

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 # For each of the 3 tables, query the database and print the contents for table in ['ZooKeeper', 'Animal', 'Handles']: **# Plug in the name of the table into SELECT * query result = c.execute("SELECT * FROM %s;" % table);** # Get all rows. rows = result.fetchall(); # \n represents an end-of-line print "\n--- TABLE ", table, "\n" # This will print the name of the columns, padding each name up # to 22 characters. Note that comma at the end prevents new lines for desc in result.description: print desc[0].rjust(22, ' '), # End the line with column names print "" for row in rows: for value in row: # Print each value, padding it up with ' ' to 22 characters on the right print str(value).rjust(22, ' '), # End the values from the row print "" c.close() conn.close() 

4 Answers

Your code already contains a beautiful way to execute all statements from a specified sql file

# 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) 

Wrap this in a function and you can reuse it.

def executeScriptsFromFile(filename): # Open and read the file as a single buffer fd = open(filename, '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) 

To use it

executeScriptsFromFile('zookeeper.sql') 

You said you were confused by

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

In Python, you can add stuff to a string by using something called string formatting.

You have a string "Some string with %s" with %s, that's a placeholder for something else. To replace the placeholder, you add % ("what you want to replace it with") after your string

ex:

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

Bit of a childish example, but it should be clear.

Now, what

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

means, is it replaces %s with the value of the table variable.

(created in)

for table in ['ZooKeeper', 'Animal', 'Handles']: # for loop example for fruit in ["apple", "pear", "orange"]: print(fruit) >>> apple >>> pear >>> orange 

If you have any additional questions, poke me.

A very simple way to read an external script into an sqlite database in python is using executescript():

import sqlite3 conn = sqlite3.connect('csc455_HW3.db') with open('ZooDatabase.sql', 'r') as sql_file: conn.executescript(sql_file.read()) conn.close() 

First make sure that a table exists if not, create a table then follow the steps.

import sqlite3 from sqlite3 import OperationalError conn = sqlite3.connect('Client_DB.db') c = conn.cursor() def execute_sqlfile(filename): c.execute("CREATE TABLE clients_parameters (adress text, ie text)") # fd = open(filename, 'r') sqlFile = fd.readlines() fd.close() lvalues = [tuple(v.split(';')) for v in sqlFile[1:] ] try: #print(command) c.executemany("INSERT INTO clients_parameters VALUES (?, ?)", lvalues) except OperationalError as msg: print ("Command skipped: ", msg) execute_sqlfile('clients.sql') print(c.rowcount) 

according me, it is not possible

solution:

1. import .sql file on mysql server

2. after

   import mysql.connector import pandas as pd 

   and then you use .sql file by convert to dataframe