PyODBC + Pandas + Parameterization

Question

I'm using PyODBC to connect to Oracle with a connection called cnxn.

I have a list of unique identifiers: list1 = [1234, 2345, 3456, 4567]

I also have a query:

query1 = """
select * from tablename where unique_id = ?
""'

What I'd like to do is use this list of identifiers to create a pandas dataframe.

As a test, I did this:

testid = "1234" (since Oracle wants a string as that id not an integer)

However, when I do this:

pd.read_sql(query1, cnxn, params = testid)

I get "the sql contains 1 parameter marker, yet 4 were supplied."

Eventually, I want to be able to do something like this:

for i in list1:
    newdataframe.append(pd.read_sql(query1, cnxn, params = i))

and have it spit out a dataframe.

I've read the docs on PyODBC and it looks like it specifies ? as the parameter. I've also looked at this question and it's similar, but I need to be able to feed the results into a Pandas dataframe for further manipulation.

I think if I can get the testid working, I'll be on the right track.

Thanks

Answer 1

From the pandas read_sql docs: "params: List of parameters to pass to execute method".

The params needs to be a list (or tuple), and hence the string is interpreted as a list of 4 single characters. But this can be solved easily:

pd.read_sql(query1, cnxn, params=[testid])