how to insert pandas dataframe into IN operator of SQL-CodePudding

I have pandas dataframe with unique number of user:

data_frame = pd.DataFrame({'uniq_num' :['1qw3','2wed','3das','4frr','533ew','612w']})

I want to pass this column to sql query where I use IN operator:

SELECT users FROM database
where users IN ("here I want to pass my dataframe, so it would search in all rows of my dataframe")

I have tried doing this

    data_frame = ','.join([str(x) for x in data_frame.iloc[:, 0].tolist()])

which would retrun whith this '1qw3,2wed,3das,4frr,533ew,612w'

and then something like WHERE users in STRING_SPLIT(data_frame, ',') but this one is obviousely doesnt work...

CodePudding user response：

You can convert the list into a tuple, this will give you the correct format

import pandas as pd
data_frame = pd.DataFrame({'uniq_num' :['1qw3','2wed','3das','4frr','533ew','612w']})

in_statement = tuple(data_frame.iloc[:, 0].tolist())
sql = f"""SELECT users FROM database
where users IN {in_statement}"""

output sql variable:

SELECT users FROM database
where users IN ('1qw3', '2wed', '3das', '4frr', '533ew', '612w')

CodePudding user response：

If you are creating SQL query in python then try this,

user_ids = "'" "','".join(data_frame.uniq_num.values) "'"
query = "SELECT users FROM database " \
"WHERE users IN (" user_ids ")"