Search code examples
pythonsqlplsqlapache-zeppelin

Using a column as a list in where clause in a sql query

I created a list using a column a in python. I am trying to that in where clause in a sql query. list is a list of account numbers.

creating a list from the df

data1
acc_d1= data1['ACCOUNT_NUMBER']
t1 = tuple(acc_d1)

my code for sql query in python (I am using zeppelin)

sql="""
select id_number from table
where account_number IN {}""".format(t1)
prog_list_d1 = pd.read_sql(sql, dbc)

when I create a list by manually typing the numbers

acc_d1 = [12129530695080,12129530755769,12129516984649......]
t = tuple(acc_d1)

sql="""
select id_number from table
where account_number IN {}""".format(t)
prog_list_d1 = pd.read_sql(sql, dbc)

it works just fine. I am using python in a zeppelin notebook, and data is pulling from an Oracle database

Solution

  • The column in the df was an object. By changing the column type to string before converting it to list worked. I kept everything else the same.

    data4['account_number'] = data4['account_number'].astype(str)
prog_d4 = list(data4['account_number'])
prog_d4 = tuple(prog_d4)

sql="""
select account_number from table
where account_number IN {}""".format(prog_d4)
prog_list_d4 = pd.read_sql(sql, dbc)