reading external sql script in python

reading external sql script in python

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

reading external sql script in python

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)