Process SQL Queries in Input Files with Python

Question

UPDATE

I have implemented some new code and now have a new problem:

query_dir = '/path/to/sql'
sql_commands = []    

with open(inputfile, 'r') as argfile:
    infiles = argfile.readlines()

    for infile in infiles:

        print("infile: {0}".format(infile))

        try:     
            infile = os.path.join(query_dir, infile).replace('\n', '')
            queries = [sqlfile.strip() for sqlfile in open(infile, 'r')]

        except IOError as e:
            print("IOError: {0}".format(e))

        for query in queries:
            sql_commands.append(query)

    print("sql commands: {0}".format(sql_commands))

Now at the end, when the print statement gives the SQL commands, the content of the file is present but in a format that is not working for me. For example, it looks like this:

sql commands: ['-- comment', '', 'select column1, column2,',
 . . . 'from x p', . . .]

I would like to get the SQL query that is in the file that is on one line of the input file to be formatted to be run by the command:

cursor.execute(command)

from below...

Original questions

I have tried different paths and locations (including using absolute paths in the input file) and I'm not sure what is going on. I tried placing all the files in the same directory with the script, and this gave same error. I'm not sure how to get the SQL file to be interpreted as a file in the inner part of code...I have a file that for each line there is an SQL query in a file:

input.file:

query_file1.sql
query_file2.sql
query_file3.sql

I have a Python script that takes a file (one of these) with an SQL query in it and executes the query and prints the pretty version of it's output, using pprint and PrettyPrinter.

I want to be able to put a file name that has an SQL query in it on each line of a file, and then process this file with the same Python script.

I'm having trouble getting there. I can print the lines of the the file, each of which is an input file with an SQL query:

. . .

with open(inputfile, 'r') as argfile:
    lines = argfile.readlines()
    for line in lines:
        print(line)

. . .

I'm not sure how to go about doing this such that I can use this snippet:

. . .

for command in sql_commands:
    try:
        pp = pprint.PrettyPrinter(indent=4)
        cursor.execute(command)
        pp.pprint(cursor.fetchall())

. . .

I need to translate the lines of the input files into arguments so I can process them with the script.

AlexWei · Accepted Answer · 2014-10-08 23:39:10Z

1

I think you need a list named sql_commands.First you get line in file,
then store it in a list.
sql_commands=[]
with open(inputfile, 'r') as argfile:
    lines = argfile.readlines()
    for line in lines:
        with open(line,'r') as sqlfile:
            sqls=sqlfile.readlines()
            for sql in sqls:
                sql_commands.append(sql)

edited Oct 8, 2014 at 23:39

answered Oct 8, 2014 at 22:50

AlexWei

1,1032 gold badges11 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nicorellius Over a year ago

Thanks. I tried this and it results in SQL syntax error. It seems the title of the file is the command rather than the contents of the file (which is the actual query). I will keep tweaking...

AlexWei Over a year ago

@nicorellius Do you mean query_file1.sql is actually a file with sqlcommands in it?

AlexWei Over a year ago

@nicorellius If my assumption is right. You should open(line,'r') then do the same thing

nicorellius Over a year ago

Yes, query_file1.sql has one or more SQL queries in it... I am fiddling with your second comment because I figured the same thing but I'm still having trouble getting it right.

Collectives™ on Stack Overflow

Process SQL Queries in Input Files with Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related