0

I am trying to loop through several SQL Queries and append the results for these queries in a dataframe or dictionary with the key being the SQL Query

I was able to retrieve results from these SQL Queries.

from pandas import DataFrame
for index, row in df.iterrows(): 
    cur.execute(row["SQL_Query"]) 
    print(cur.fetchall())

Output:

[(datetime.date(2019, 4, 8), datetime.date(2019, 4, 1))]

[(2, )]

[('6', 2), ('7', 2)]

[(13, 2)]

But when I try to add them to a dataframe, I am only able to fetch result from the last query.

from pandas import DataFrame
for index, row in df.iterrows(): 
    res = cur.execute(row["SQL_Query"]) 
    df['Results'] = DataFrame(cur.fetchall())

"Output"

The goal right now is only to have results from SQL Queries in different columns. For instance, is the query return Total Count and Failed Count, then that be in 2 different columns.

Dhruv
  • 87
  • 1
  • 9

1 Answers1

0

The issue is that you're iterating over the dataframe for queries and then assigning over df['Results'] each time. You should apply an index value.

The below should work or at least get you on the right track. I can't replicate this quickly to test it.

from pandas import DataFrame
for index, row in df.iterrows(): 
    cur.execute(row["SQL_Query"]) 
    df.loc[index,'Results'] = cur.fetchall()
krewsayder
  • 446
  • 4
  • 9
  • I get a ValueError: Must have equal len keys and value when setting with an ndarray. Should I be initializing the column before the loop? – Dhruv Apr 17 '19 at 16:49
  • Yes, I believe it should be initialized before the loop. – krewsayder Apr 17 '19 at 16:51
  • I did df['Results'] = [] before the loop. But this gives me ValueError: Length of values does not match length of index – Dhruv Apr 17 '19 at 17:00
  • Try ```df['Results'] = ''``` instead. – krewsayder Apr 17 '19 at 17:06
  • That worked. Thanks! Do you have an idea about how would I be able to separate the result into 2 different columns (if the query results in 2 different columns)? – Dhruv Apr 17 '19 at 17:57
  • You can use a split method maybe. I think it would depend on your output. If you're outputting 2 objects you want to split you can check the below link. https://stackoverflow.com/questions/14745022/how-to-split-a-column-into-two-columns – krewsayder Apr 17 '19 at 18:59