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

What SQL query(s) are you supposed to execute, the 1.1 and 1.2 ones, or just get everything from every table? — Azeirah, Oct 20 '13 at 01:28
I want to run 1.1 and 1.2 (plus I have about 6 others) from the .sql file. — mpg, Oct 20 '13 at 01:34
I'm going a bit too fast I believe, do you need help understanding the code or do you need help editing your code to do something extra? — Azeirah, Oct 20 '13 at 01:58
probably both. All the sql I need is in zookeeper.sql (and is suppose to stay there). The python code then is suppose read the sql file, create the tables, populates them (which I think I have done right), AND then execute the queries (ie 1.1, 1.2) above. — mpg, Oct 20 '13 at 02:02

score 162 · Answer 1 · edited Aug 02 '21 at 21:37

162

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.

edited Aug 02 '21 at 21:37

Vaggelis Manousakis

292
1
5
15

answered Oct 20 '13 at 02:12

Azeirah

6,176
6
25
44

1

Happy to take my questions offline, but how do you poke on SOF? I plugged in what I think you were talking about and got a bunch of errors. So I am still not getting something. MPG – mpg Oct 20 '13 at 02:48
Oh, there is no official "poking" in SO, in my terms you just poked me. I think it's best for you to get familiar with how Python works overall, since I can't fix all your problems one by one (even if I do, next time you want to do something you can't). [This website](http://www.codecademy.com/) will definitely help you understand python's basic syntax. – Azeirah Oct 20 '13 at 02:55
1

Very helpful; definitely something I'll be saving for future reference! A [minor tweak](http://pastebin.com/Wy884qCJ) to work with python 3... – memilanuk Oct 20 '16 at 05:39
3

This code will attempt to execute an empty string query for all sql documents. The trailing semicolon will make an empty string list element. Try: sqlCommands = filter(None, sqlFile.split(';')) – sage88 Feb 01 '17 at 00:43
2

This works until you have a text column in your SQL file that contains a ';' – JeffSpicoli Aug 22 '17 at 21:48
@JeffSpicoli I am anything but an SQL expert, barely ever use it. I'm most certain that there are way better ways to read and execute sql files from Python, and have a hunch that this answer is becoming more actively harmful given its popularity. Feel free to update the answer. – Azeirah Aug 24 '17 at 15:55
@Azeirah I was wondering if I could an adapted form of your code here in a project. I'm not sure what the legal requirements are. Do you or any others know? – Trevor J. Smith Dec 08 '17 at 20:41
3

@TrevorJ.Smith All code posted directly on StackOverflow is licensed under the Creative Commons license [see this post for more info](https://meta.stackexchange.com/questions/12527/do-i-have-to-worry-about-copyright-issues-for-code-posted-on-stack-overflow). I am not a lawyer though, consult an attorney if you think it might be necessary in your case. – Azeirah Dec 13 '17 at 13:46
4

@Azeirah Your answer can cause SQL injections, can you make it use the `?` syntax for SQL formatting? – Labo May 31 '19 at 08:22
SyntaxError: multiple exception types must be parenthesized – Codelaby Nov 03 '22 at 21:14

score 13 · Answer 2 · answered Sep 02 '20 at 07:08

13

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

answered Sep 02 '20 at 07:08

Lukas Loos

159
1
5

can't this be done with two with statements? – bonobo Aug 19 '22 at 15:14
I read it up and it seems like using `sqlite3.connect()` as a context manager does not close the connection when exiting the context (I read [this blog](https://blog.rtwilson.com/a-python-sqlite3-context-manager-gotcha/) but didn't try it) In the comments they mention a `closing()` function from `contextlib` module though which you could use for writing this with two with statements – Lukas Loos Aug 21 '22 at 13:35

score 1 · Answer 3 · answered Jul 24 '21 at 21:58

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)

score -9 · Answer 4 · edited Jul 22 '20 at 15:26

-9

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

edited Jul 22 '20 at 15:26

Steffen Moritz

7,277
11
36
55

answered Jul 22 '20 at 15:02

Dhruv Vavliya

1

reading external sql script in python

4 Answers4

Linked