0

I was looking at this similar question: Parameterize an SQL IN clause But the solution is not using Python, so I had to raise a new question: How do I parameterize a query containing an IN clause, the strings 'ruby','rails','scruffy','rubyonrails' comes from a column of a dataframe

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

The dataframe df might look like:

column1  column2...
ruby     .
rails    . 
scruffy  .
xxx
xxxx

Here's what I've tried: I converted the first column to a list and name it list, then update the second line in the query:

WHERE Name IN %(list)s

But this gave me an error: sqlalchemy.exc.ProgrammingError: (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where Name IN (('ruby','rails','xxx','xxxx','xxx','' at line 2")

I also tried list = str(list)[1:-1] to remove the square bracket, but then I got error: MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where Name IN ('\\'ruby\\', \\'rails\\', \\'xxxxx\\', \\'xxx\\',' at line 2")

My question is what format/datatype I should use for df[column1] to get this working? Can someone help please? Thanks.

wawawa
  • 2,835
  • 6
  • 44
  • 105
  • Update: I've resolved this, turns out I only need to convert it to a list (with the square bracket), no need to remove the bracket otherwise it won't work! – wawawa Jun 11 '21 at 09:48

2 Answers2

0

The only answer here doesn't work, since I've resolved this, so here's the solution: turns out I only need to convert it to a list (with the square bracket), no need to remove the bracket otherwise it won't work!

wawawa
  • 2,835
  • 6
  • 44
  • 105
-1

Maybe you can convert the column list to a tuple:

col_tuple = tuple(list)

Then use a python f-string in your query:

f"""SELECT * FROM Tags 
WHERE Name IN {col_tuple}
ORDER BY Count DESC"""
NoobsterNoob
  • 125
  • 1
  • 2
  • 7
  • Hi thanks, I'm not sure if this works because in my query, I have a couple of other parameterized strings, like date etc, they are using the format `%(date)s` in the MySQL query. – wawawa Jun 11 '21 at 07:58