Help please. Looping the same SQL query over different databases and combine them

[danlin123]

Suppose I have 3 SQL dataset tables with the same columns but different values. Those tables contain Tommy's test scores in different subjects on their respective dates,i.e. the table names are '20200601', '20200602' and '20200603'.

Let's say I am interested in extracting Tommy's math test score on 3 different dates by using these 3 tables, so my SQL query would be 'Select subject, test_score from [table name] where subject = 'math'

Suppose the 3 table names are now contained in a list ['20200601', '20200602','20200603'] and as you can see, I can make a for loop to execute the same SQL query based on each table in the list.

I expect to have 3 results in dataframe like (index might be '0' and not date in the result):

From table '20200601':
index subject test_score
0 math 90

From table '20200602':
index subject test_score
0 math 99

From table '20200603':
index subject test_score
0 math 89

So now, ideally I want to combine them into one dataframe which will look like:

From table '20200601':
index subject test_score
0 math 90
1 math 99
2 math 89

And then, I want to merge it into another existing dataframe which is already indexed in date (but have no other columns):
index
20200601
20200602
20200603

After merging the two, it should look like:
From table '20200601':
index subject test_score
20200601 math 90
20200602 math 99
20200603 math 89

I am pretty new to Python, and I know the part of running SQL query in Python (by using pd.read_sql_query) but I am confused about what to do with the look and combine the results together as the next step. Would appreciate your help.

[danlin123]

Any help please?

[Larz60+]

Please show what you have tried so far

[danlin123]

Please show what you have tried so far

Hi, thank you for trying to help.

My mind is really blank now so I am not sure what coding lines I can share for now. I have tried many things and it is quite messy.

But to keep it simple, I now have two dataframes as follows:

1) DF 1 (result extracted from running SQL query on dataset '20200602')
It is 2 rows x 3 columns (the 1ist row is essentially the header)

0 subject test_score
1 math 90

2) DF2 is an existing dataframe which is currently indexed by date.
It is 3 rows x 0 column

2020/06/01
2020/06/02
2020/06/03

So essentially I want to merge DF1 into DF2 in the specific row 2 because DF1 reflects data from 2020/06/02. Any advice on how to do so by coding?

Add question: if in DF1 I have 3 rows with 1 more row showing Tommy's test score of English subject, how would I merge it into DF2 then? Should it then have two indexes: 1st index reflects the date and 2nd reflects the subject?

Much appreciated your help.