Select the columns in sql based on a column from dataframe-CodePudding

I have a dataframe with one column which shows the IDs. Like the following ( I read and save this data to this dataframe from another sql table) :

I also have one table in mysql which has three columns 'user', 'sell', and 'buy'. I want to select the rows in the table where their users are in the dataframe. Here is the code which I've used, however I got error for that.

data     = pd.read_sql(f"""select * from mytable where  user in (%s)" % ','.join(df['user'])limit 5""", db)

I have searched a lot and saw the question Pandas Dataframe - Mysql select from table where condition in <A column from Dataframe> and the others, however I can not solve my problem.

CodePudding user response：

You should first change the dataframe to a tuple. Then use the where in {tuple}.

Here is it:

 x = tuple(df.user.unique())
 data     = pd.read_sql(f"""select * from mytable where  user in {x} limit 5""", db)

CodePudding user response：

query = 'SELECT * FROM mytable WHERE user in ' tuple(df['user'].tolist())

Try this may be it work if convert to list.