I am new to Python and the psycopg2 module.
Working on a script that select data from MySQL, perform some operations (conversion of data-types and other "transformations"), and finally insert data into PostgreSQL using the
I've read on the official website of psycopg2 that it is better to instantiate new cursors each time it is possible:
When should I save and re-use a cursor as opposed to creating a new one as needed? Cursors are lightweight objects and creating lots of them should not pose any kind of problem. But note that cursors used to fetch result sets will cache the data and use memory in proportion to the result set size. Our suggestion is to almost always create a new cursor and dispose old ones as soon as the data is not required anymore (call close() on them.) The only exception are tight loops where one usually use the same cursor for a whole bunch of INSERTs or UPDATEs.
but the nature of my script seems to require the use of only one cursor for a whole bunch of inserts since I've add the check if the dataset is empty before try to insert in
for query, dataset in dict.iteritems() : if dataset: # this is the check if value(dataset) is not empty try: cur_psql.execute( query + dataset ) except psycopg2.Error as e: print "Cannot execute that query", e.pgerror cnx_psql.rollback() sys.exit( "Rollback! And leaving early this lucky script, find out what is wrong" ) else: print "The dataset for " + query + " is empty, skipping..."
this code insert data in different psql tables at each iteration with
cur_psql.execute( key + value )
My doubt is if in my scenario, I will encounter any drawback using the same cursor for all the inserts. I was not able to understand if I am in the category where
The only exception are tight loops where one usually use the same cursor for a whole bunch of INSERTs or UPDATEs.