reading external sql script in python

Question

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

User · Answer

according me  it is not possible solution   import  sql file on mysql server  after import mysql connector import pandas as pd  and then you use  sql file by convert to dataframe

User · Answer

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   gt  gt  gt  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  gt  gt  gt  apple  gt  gt  gt  pear  gt  gt  gt  orange   If you have any additional questions  poke me

User · Answer

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

[python] reading external sql script in python

Examples related to python

Examples related to sql