Pass Argument Through Sql Queries Pandas

Question

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.

nbk · Accepted Answer · 2022-09-21 12:15:25Z

1

python thinks that attendance 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')

edited Sep 21, 2022 at 12:15

answered Sep 21, 2022 at 11:37

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bitflip · Accepted Answer · 2022-09-21 11:36:04Z

0

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")

answered Sep 21, 2022 at 11:36

bitflip

3,7391 gold badge6 silver badges22 bronze badges

Collectives™ on Stack Overflow

Pass Argument Through Sql Queries Pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related