0

I have created a lookup table (in Excel) which has the table and column name for the various tables and the the column names under these table along with all the SQL queries to be run on these fields. Below is an example table.

Lookup Table

Results from all SQL Queries are in the format Total_Count and Fail_Count. I want to output these results along with all the information in the current version of the lookup table and date of execution into a separate table.

Sample result Table:

Sample_Result Table

Below is the code I used to get the results together in the same lookup table but have trouble storing the same results in a separate result_set table with separate columns for total and fail counts.

df['Results'] = ''
from pandas import DataFrame
for index, row in df.iterrows(): 
            cur.execute(row["SQL_Query"]) 
            df.loc[index,'Results'] = (cur.fetchall())
Ondra K.
  • 2,767
  • 4
  • 23
  • 39
Dhruv
  • 87
  • 1
  • 9

1 Answers1

0

It might be easier to load the queries into a DataFrame directly using the read_sql method: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html.

The one caveat is that you need to use a sqlAlchemy engine for the connection. I also find itertuples easier to work with.

One you have those your code is merely:

df['Results'] = ''
for row in df.itertuples(): 
            df_result = pd.read_sql(row.Sql_Query)
            df.loc[row.Table_Name, 'Total_count'] = df_result.total_count
            df.loc[row.Table_Name, 'Fail_count'] = df_result.fail_count

Your main problem above is that you're passing two columns from the result query to one column in df. You need to pass each column separately.

Sam
  • 541
  • 1
  • 3
  • 10
  • Hey, thanks for the answer. I know of sql_engine but not sure how it is more helpful than using pyodbc. Anyhoo, for now this is giving me "Incompatible indexer with Series" error – Dhruv Apr 22 '19 at 19:11
  • At the end of the day, `pyodbc` and `sqlalchemy` are just going to be grabbing data from a database; I just like removing the step of dealing with cursors. Regarding your error, this seems to be the issue: https://stackoverflow.com/questions/37815837/python-if-statement-dictionary-incompatible-indexer-with-series – Sam Apr 22 '19 at 19:18
  • Got it. I'll use sqlalchemy. Error makes sense since the Table column may have the same table name multiple times so they key will be duplicate. Not sure how to get rid of this error – Dhruv Apr 22 '19 at 19:58
  • Do you know how I can do this?https://stackoverflow.com/questions/59148525/return-multiple-datasets-from-a-stored-procedure#59148525 – Ratha Dec 03 '19 at 02:23