jaydebeapi set autocommit off for bulk inserts

Question

I have many rows to insert into a table and tried doing row by row but it is taking a really long time. i read this link Python+MySQL - Bulk Insert and seems like setting autocommit to be off can speed things up.

import jadebeapi

connection = jaydebeapi.connect('com.teradata.jdbc.TeraDriver', ['jdbc:teradata://some url',USER,PASS], ['tdgssconfig.jar','terajdbc4.jar'],) 


cur = connection.cursor()
connection.jconn.setAutoCommit(False)
cur.execute('select * from my_table')
connection.commit()

Other queries i perform are:

l = [(1,2,3),(2,4,6).....]
for tup in l:
    cur.execute('my insert statement')
#this is the really slow part.

When i have the connection.jconn.setAutoCommit(False) i always get this error:

[Teradata Database] [TeraJDBC 15.10.00.14] [Error 3932] [SQLState 25000] Only an ET or null statement is legal after a DDL Statement.

When that line and connection.commit() is commented out, the code works fine. What is the right syntax to set autocommit false?

Answer 1

If speed/efficiency is a concern, you should be using prepared statements and passing your parameters in as the second argument.

You could then also use .executemany():

l = [(1,2,3),(2,4,6).....]
cur.executemany('my insert statement with 3 ? params', l)
#this should be much faster