Pass Argument Through Sql Queries Pandas-CodePudding

I want to convert lot of database table into dataframe. So I tried this step manually.

sql_query = pd.read_sql_query ('''
                               SELECT
                               *
                               FROM attendance
                               ''', test_db_engine)

test_db_attendance_df=pd.DataFrame(sql_query)

Where 'test_db_engine' is database connection. This method work and I can create dataframe for table attendance. Now, I want to put this into function so I can do with any table not just one. So I tried this method.

def sql_to_df(table_name):
    
    sql_query = pd.read_sql_query ('''
                               SELECT
                               *
                               FROM table_name
                               ''', test_db_engine)
    
    test_db_df=pd.DataFrame(sql_query)
    return test_db_df

sql_to_df(attendance)

It threw me an error:-

name 'attendance' is not defined

Can anyone tell me how to pass function argument through sql query so I can convert any number of database table into pandas dataframe? I need to pass attendance inside sql query replacing table_name.

CodePudding user response：

python thinks that atetendece is a variable , but you need to pass a string to the function and then use string replacement

def sql_to_df(table_name):
    
    sql_query = pd.read_sql_query ('''
                               SELECT
                               *
                               FROM %s
                               ''' % (table_name), test_db_engine)
    
    test_db_df=pd.DataFrame(sql_query)
    return test_db_df

sql_to_df('attendance')

CodePudding user response：

use f-strings to format your query. and pass attendance as a string (your error occurred because no variable attendance was set).

read_sql_query returns a dataframe.

def sql_to_df(table_name):
    
    return pd.read_sql_query(f'''
                               SELECT
                               *
                               FROM {table_name}
                               ''', test_db_engine)
    

sql_to_df("attendance")