title: reading external sql script in python
body: <p>I am working on a learning how to execute SQL in python (I know SQL, not Python). </p>
  
  <p>I have an external sql file. It creates and inserts data into three tables 'Zookeeper', 'Handles', 'Animal'.</p>
  
  <p>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:</p>
  
  <pre><code>--1.1
  
  SELECT ANAME,zookeepid
  FROM ANIMAL, HANDLES
  WHERE AID=ANIMALID;
  </code></pre>
  
  <p>--1.2</p>
  
  <pre><code>SELECT ZNAME, SUM(TIMETOFEED)
  FROM ZOOKEEPER, ANIMAL, HANDLES
  WHERE AID=ANIMALID AND ZOOKEEPID=ZID
  GROUP BY zookeeper.zname;
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>result = c.execute("SELECT * FROM %s;" % table);</p>
  
  <p>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.</p>
  
  <p>My entire python code is below.</p>
  
  <pre><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
  
  
  # 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()
  </code></pre>