CODEWITHSUNDEEP
python-2.7oracle11gpyodbc
Anthony
Anthony

Reputation: 11

Executemany on pyodbc only return result from last parameter

I have a problem when I try to use pyodbc executemany function. I have an Oracle database and I want to extract data for multiple days.

I cannot use between in my request, because the database is not indexed on the date field and its taking forever. I want to manually ask all day and process answers. I cannot thread this part, so I wanted to use executemany to get rows more quickly.

The problem is when I use executemany I only got the result of the last argument asked.

Here is my code:

import pyodbc

conn = pyodbc.connect('DRIVER={Oracle in instantclient_11_2};DBQ=dbname;UID=uid;PWD=pwd')
cursor = conn.cursor()
query = "SELECT date FROM table WHERE date = TO_DATE(?, 'DD/MM/YYYY')"
query_args = (
                 ('29/04/2016',),
                 ('28/04/2016',),
)
cursor.executemany(query, query_args)
rows = cursor.fetchall()

In rows, I can only find rows with (datetime.datetime(2016, 4, 28, 0, 0), ). Always the last argument.

I am using python 2.7.9 from WinPython on a Oracle database with a client on 11.0.2. Except this query, every other query is perfectly fine.

I cannot use IN () synthax for 2 reasons:

(Right now I'm using IN() OR IN() OR IN()... but if anyone find something better that would be wonderful !)

Am I doing something wrong ?

Thanks for helping.

Upvotes: 1

Views: 1224

Answers (1)

RG_Glpj
RG_Glpj

Reputation: 27

Your query runs once with one argument. If you want to run for multiple dates either use "IN" clause, this will require to modify query_args a bit.

  "SELECT date FROM table WHERE date in (TO_DATE(?, 'DD/MM/YYYY'), TO_DATE(?, 'DD/MM/YYYY'))"

 query_args = (
             ('29/04/2016','28/04/2016'),
 )

or cursor through each date argument:

while query_arg in query_args:
  cursor.executemany(query, query_arg )
  rows = cursor.fetchall()

Upvotes: 1

Related Questions