CODEWITHSUNDEEP
pythonsqlloops
JD2775
JD2775

Reputation: 3801

Loop through multiple SQL statements and store the results in Python object

I have a text file that has about 100 DB table names in it separated by a new line, and I have a function to return those table names in a list here:

def grab_tables(self):
    table_list = []
    with open('tables.txt', 'r') as file:
        datalines = (line.rstrip('\r\n') for line in file)
        for line in datalines:
            table_list.append(line)
    return table_list

I now want to connect to the DB and do a select count(*) on all those tables and store the table name and counts in some python object (list, dictionary, whatever). What I have so far is this:

def run_sql(self):
    s = self.connection_src() #function that connects to Oracle DB
    tables = self.grab_tables()
    a = s.cursor()
    z = []
    for table in tables:            
        a.execute('SELECT count(*) FROM {}'.format(table))
        z.append(a)

    print(z)

This is not working though, as it is appending the connection details to z. Clearly incorrect. Any ideas?

Upvotes: 0

Views: 1512

Answers (1)

Daniel Roseman
Daniel Roseman

Reputation: 599580

The problem isn't really about looping through multiple SQL statements at all. The question is how to get the result from a call to execute.

The answer to that is to call fetchone() on the cursor. This will give you a tuple, so get the first element of that:

a.execute('SELECT count(*) FROM {}'.format(table))
z.append(a.fetchone()[0])

Upvotes: 1

Related Questions