3

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?

Community
  • 1
  • 1
jxn
  • 7,685
  • 28
  • 90
  • 172
  • Googling around led me to [a Teradata documentation](http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/General_Reference/B035_1096_112K/Database.27.1190.html#ww1687158). Not sure if this helps you. – bastian Jan 21 '17 at 11:00
  • Regarding performance: Bulk inserts can help. But JayDeBeApi used on CPython has some Py2Java overhead. Give Jython+JayDeBeApi a try instead or use Java directly. – bastian Jan 21 '17 at 11:02

1 Answers1

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
Sean Summers
  • 2,514
  • 19
  • 26