How to extract data from multiple SQL tables in one for multiple outputs-CodePudding

There are multiple tables in SQL DB based on countries where I need to extract data from. How can I extract multiple data in one script from multiple tables to use in a dataframe to multiple outputs based on the countries. How can I call this list in the dataframe for the output?

I am merging different results at the end to export the file. Currently its based on one country only (US). I'm trying to combine other countries results in one script to export all together.

 country_name = ["US", "Canada"]  #trying to extract data from different

SQL tables which is saved in a format target_US_category

 query = """SELECT * FROM DB.[Appservices\DBPowerUsers]. 
 [target_"""   country_name  """_category];""" 

 df= pd.read_sql(query, engine)


 def summary_tables (df):

female = df[df['sex1']=='Female'].shape[0]
male = df[df['sex1']=='Male'].shape[0]

category = pd.DataFrame([
    ['Male', male, (round(((male/(df.shape[0]))*100),2))],
    ['Female', female, (round(((female/(df.shape[0]))*100),2))]],
    columns = ['category', 'Amt', 'Percent'])
return(category)

  def summary_tables (df1):
  female = df1[df1['sex1']=='Female'].shape[0]
  male = df1[df1['sex1']=='Male'].shape[0]

category1 = pd.DataFrame([
    ['Male', male, (round(((male/(df1.shape[0]))*100),2))],
    ['Female', female, (round(((female/(df1.shape[0]))*100),2))]],
    columns = ['category', 'Amt', 'Percent'])
return(category1)

 df_merge = pd.merge(summary_tables (df),summary_tables 
  (df1), on='category')

CodePudding user response：

Further to our comments, should look similar as below. Difficult to write correctly without test data.

grade_dict = {
'CL' : '1',
'CL' : '2',
'CL' : '3',
'UNDEF' : 'na',
'DEFAULT' : 'na',
'No known' : 'na'}

def summary_tables (df):
# your fucntion code here

country_name = ["US", "India", "Canada"]  # check your list, countries must be in ""

df_dict = {}  # create empty dictionary and load each table to it during the loop
for item in country_name:
    query = """SELECT *
        FROM CustomerDB.[Appservices\CustomerDBPowerUsers].[target_"""   item   """_population];"""
    df_dict[item] = df = pd.read_sql(query, engine)
    df_dict[item]['pos'] = df_dict[item]['seq'].map(grade_dict)
    df_dict[item]['emp'] = df_dict[item]['seq'].map(grade_dict)
    summary_tables(df_dict[item]) # passing dataframe to your function

# if all tables for each country have the same columns, - all df's from dictionary will be placed into one large one called df_merge
df_merge = pd.concat(df_dict.values(), ignore_index=True)