Using executemany() with PYPYODBC

Question

My project is currently using pypyodbc Python library to connect to DB2 database, my source is a MS SQL server and I need to read data from that and load the data to a DB2 table. The data volume is million rows and I am attempting to use the executemany() method to load 50 records in one execution but I keep getting the error:

data must be in a list, tuple or row

I did use list function to typecast my cursor results but it still doesn't work. The data in the result set is in the format [(record1),(record2)]. The code snippet is as below:

Tried typecasting the sql results set tuple as well

# use pypyodbc to establish a connection - db2_conn. 
cur = db2_conn.cursor()
cur.execute('...a query with 10 columns...')
result = cur.fetchmany(50)

insert_query = 'insert into db2_table (col1,col2,col3,...) values (?,?,?,..)'
cur.executemany(insert_query, list(result))
cur.commit()

Where is the MS SQL cursor? This looks to only run queries on DB2 cocnnection. And does *still doesn't work* mean same exact error with or without `list()` on *results*? — Parfait, Aug 25 '19 at 15:32
--ms sql server connection sql_conn = pyodbc.connect("DRIVER="+src_driver+";SERVER="+src_server+";DATABASE="+src_database+";UID="+src_username+";PWD="+ src_password) --db2 connection db2_conn = pypyodbc.connect(driver=tgt_driver, system=tgt_system, uid=tgt_username, pwd=tgt_password) — Devender Kumar, Aug 25 '19 at 18:32
Please edit post with code (not in comments) and show how it integrates in above code block. Edit as needed. I had to fix quite a bit that could not run in Python. Then come back to delete above hard to read comment as will I. — Parfait, Aug 25 '19 at 20:18

score 1 · Answer 1 · answered Jan 01 '20 at 07:20

The code looks OK - please post the exact code that fails. I tested the following with Db2 as both source and target:

Created source and target tables

db2 "create table odbc_test(c1 int, c2 varchar(10))"
db2 "create table odbc_test_sorurce(c1 int, c2 varchar(10))"

Populated soruce with the data

db2 "insert into odbc_test_sorurce 
    with
       cte(c1)
    as
       (select
            1
        from
           sysibm.sysdummy1
        union all
        select
           c1 + 1
        from
           cte
        where c1 < 1000) select  c1, 'row' || c1 from cte"

Connected to the database and fetched the rows

import pyodbc
cnx = pyodbc.connect("DRIVER={DB2};HOSTNAME=localhost;DATABASE=sample;UID=xxxx;PWD=xxxxx;PROTOCOL=TCPIP;PORT=60115")
cnx.autocommit = False
cur = cnx.cursor()
cur.execute('select c1, c2 from odbc_test_sorurce')
result = cur.fetchmany(50)

This gives me a list as the result:

In [16]: result
Out[16]: 
[(1, 'row1'),
 (2, 'row2'),
 (3, 'row3'),
 (4, 'row4'),
 (5, 'row5'),
 (6, 'row6'),
 (7, 'row7'),
...


In [17]: type(result)
Out[17]: list

Insert works just fine:

cur.executemany("insert into odbc_test(c1, c2) values (?,?)", result)
cnx.commit()

Using executemany() with PYPYODBC

1 Answers1