CODEWITHSUNDEEP
pythonsqlfile-iosqlite
nicorellius
nicorellius

Reputation: 4053

Process SQL Queries in Input Files with Python

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.

Upvotes: 0

Views: 575

Answers (1)

AlexWei
AlexWei

Reputation: 1103

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)

Upvotes: 1

Related Questions