Pass values from a pandas Series to PostgreSQL query using psycopg2

Question

I have a column in a DataFrame that contains the ids of records I want to return from a query.

This is what I have so far:

query = """select * from frames where recording_id in (%d) """ % (data['recording_id'].values)
mydata = pandas.read_sql(query, db_conn)

This returns a type error:

TypeError: %d format: a number is required, not numpy.ndarray

so I also tried using .tolist() instead of values but I have the same problem.

What is the proper way to pass these values?

@JackManey I'm sorry but I'm not sure how to use your comment to help me. :( — Ellis Valentiner
– Ellis Valentiner, Commented Feb 26, 2015 at 20:14
You want a comma-separated list of the elements in your column. That's how you get it: via ",".join. — user554546
– user554546, Commented Feb 26, 2015 at 20:20
@JackManey I think I understand. I am getting a type error: TypeError: sequence item 0: expected string, numpy.int64 found but I think I can figure this out — Ellis Valentiner
– Ellis Valentiner, Commented Feb 26, 2015 at 20:24

Ellis Valentiner · Accepted Answer · 2015-02-26 20:27:53Z

1

Thanks to Jack and some googling, I managed to figure out a solution:

query = """select * from frames where recording_id in (%d) """ % (','.join([str(i) for i in data['recording_id']]))

answered Feb 26, 2015 at 20:27

Ellis Valentiner

2,2144 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

This is vulnerable to SQL injections as far as I understand.