Executemany on pyodbc only return result from last parameter
Question: 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:

  * I want to limit operations on database side, and do most of thing on script side (I've tried but it's way too long)

  * I might have more than 1000 different dates in the request.

(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.


Answer: 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()
    

