Write a python script that is going to read the queries that you have created (S

Question

Write a python script that is going to read the queries that you have created (See below) from a SQL file, execute each SQL query against SQLite database and print the output of that query. You must read your SQL queries from a file, please do not copy SQL directly into python code. The code that would run commands from the ZooDatabase.sql file is provided (also see below), so all you have to do is to change it so that it reads your queries from a SQL file in the same way and also prints the output of your queries. You must print every row individually using a for-loop.

Queries:

1. SELECT ANAME, ZOOKEEPID FROM ANIMAL, HANDLES WHERE AID = ANIMALID")
2. SELECT ANAME, ZOOKEEPID FROM ANIMAL LEFT OUTER JOIN HANDLES ON AID = ANIMALID")
3. SELECT ZNAME, ZID, SUM(TIMETOFEED) FOOD ANIMAL INNER JOIN HANDLES ON ANIMALID = AID INNER JOIN ZOOKEEPER ON ZID = ZOOKEEPID GROUP BY ZNAME, ZID ORDER BY ZNAME")
4. SELECT ANAME, ASSIGNED, ZNAME, ANAME FROM ANIMAL INNER JOIN HANDLES ON ANIMALID = AID INNER JOIN ZOOKEEPER ON ZID = ZOOKEEPID ORDER BY ASSIGNED;")
5. SELECT ANAME, COUT(ZNAME) FROM ANIMAL INNER JOIN HANDLES ON ANIMAL =AID INNER JOIN ZOOKEEPER ON ZID = ZOOKEEPID GROUP BY ANAME HAVING COUT(ZNAME) = 1 ORDER BY NAME")
6.SELECT ANAME, COUNT(ZNAME) FROM ZOOKEEPER ON ANIMALID = AID FULL JOIN ZOOKEEPER ON ZID = ZOOKEEPID GROUP BY ANAME HAVING COUT(ZNAME)< 2 ORDER BY ANAME;")

The Code I have so far....

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')
# Read as a single document (not individual lines)
sqlFile = fd.read()
fd.close()

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

# Execute every command from the input file (separated by ";")
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 as msg:
print ("Command skipped: ", msg)

c.close()
conn.commit()
conn.close()

Explanation / Answer

NOTE:
I have created a sample table ANIMAL in sqlite database to test connecting to sqlite and getting results. Because i do not have the data for ANIMAL, HANDLES table etc which would be difficult to show the results of given query. So once you are able to get the below things running based on code suggested. Create tables in sqlite for the above tables mentioned in queries, insert data and put the same queries in ZooDatabase.sql file. After that the script should run fine without any issues. There should not be any change in code even if we modify ZooDatabase.sql file with new queries provided the tables and data exist in sqlite.

Code:
#!/usr/bin/python

# script name: connectSQL.py
# script to read queries from ZoodDatabase.sql file and execute queries in sqlite

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')
# Read as a single document (not individual lines)
sqlFile = fd.read()
fd.close()

# all SQL commands (split on ';' which separates them)
sqlCommands = sqlFile.split(';')
# Execute every command from the input file (separated by ";")
for query in sqlCommands:
   try:
   c.execute(query)
   except OperationalError as msg:
   print ("Command skipped: ", msg)
   # printing each row from the table
   print "Results from the query" + " '" + query + "'"
   for row in c:
       print row

c.close()
conn.commit()
conn.close()

Table in sqlite DB:
sqlite> select * from ANIMAL;
ziraffee|1
elephant|2
horse|3
tiger|4
lion|5
sqlite>

Execution and output:

Input file:

Ensure that each query is separated by semi colon just like how i have put the 2 queries here.
186590cb0725:Chegg bonkv$ cat ZooDatabase.sql
select ANAME, ANIMALID from ANIMAL;select ANAME from ANIMAL

186590cb0725:Chegg bonkv$ python connectSQL.py
Results from the query 'select ANAME, ANIMALID from ANIMAL'
(u'ziraffee', 1)
(u'elephant', 2)
(u'horse', 3)
(u'tiger', 4)
(u'lion', 5)
Results from the query 'select ANAME from ANIMAL
'
(u'ziraffee',)
(u'elephant',)
(u'horse',)
(u'tiger',)
(u'lion',)

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.