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

Question

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?

Daniel Roseman · Accepted Answer · 2019-04-04 21:28:10Z

1

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

answered Apr 4, 2019 at 21:28

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

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

3 Comments

JD2775 Over a year ago

thank you Daniel. that worked great. Silly question, how would I then add the table name next to the result? The above returns the results which is great, but I want to associate it with the table as well

Daniel Roseman Over a year ago

You could append a tuple of (table, a.fetchone..) fur example. Or make z a dict where the key is the table name.

JD2775 Over a year ago

thanks, i was just going to reply that I had figured it out using list comprehension. Thanks again

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related