1

I'm a R user trying to pick up Python. In R, I often used vectors to pass as arguments to SQL query. For example,

ID <- c(1,2,3,4,5)
df <- dbGetQuery(con, paste("select * from table where ID in (", ID, ")")

How can I achieve this in Python? I have a dataframe and would like to use one of its columns as the parameters. So with a dataframe like this,

data = {'ID': [1,2,3,4,5],
        'Value': [10,20,30,40,50]}
df = pd.DataFrame(data)

[Edit] So basically I need a string that would read "Select * from table where ID in (1,2,3,4,5)" except, instead of manually typing "1,2,3,4,5" I want to use parameters.

DJL
  • 144
  • 1
  • 12
  • 1
    Would you elaborate on the "WHERE clause of my query"? – zabop Sep 29 '21 at 19:33
  • Which part are you having trouble with - selecting a column? – wwii Sep 29 '21 at 19:35
  • Does [select columns based on columns names containing a specific string in pandas](https://stackoverflow.com/questions/43643506/select-columns-based-on-columns-names-containing-a-specific-string-in-pandas) anser your question? – wwii Sep 29 '21 at 19:36
  • 2
    See [these](https://stackoverflow.com/questions/283645/python-list-in-sql-query-as-parameter) answers. To convert a pandas Series to list, use `df["ID"].tolist()` – Erfan Sep 29 '21 at 19:37
  • @zabop, I edited my question, hopefully that made it clear – DJL Sep 29 '21 at 19:52
  • @Ben.T, this seems very close. But does it only work if ID is string? I get this error: sequence item 0: expected str instance, int found. I need the numbers to be passed as integers in the SQL query. – DJL Sep 29 '21 at 19:59
  • @Ben.T, this worked beautifully. thanks!! – DJL Sep 29 '21 at 20:18

1 Answers1

1

OP are looking for something like

query = f"select * from table where ID in ({','.join(df['ID'].astype(str))})"

For more ways to create this query from list, one can also check this post provided by @Erfan in a comment.

Ben.T
  • 29,160
  • 6
  • 32
  • 54