2

I am new to Python and the psycopg2 module.

context

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 psycopg2 module.

main problem

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 psql

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 ) 

question

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.

2
  • 2
    If it is only INSERTs, you don't have to worry - you don't fetch the result set in any case. So the answer is already in your post. Please note that psql is only a client to PostgreSQL. – dezso Nov 20 '15 at 23:20
  • many thx dezso, for the naming clarification too I really appreciate, this will stick in my mind, you are always on the piece! – lese Nov 20 '15 at 23:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.