1

I am building a script to store some data in a database. First time I'm using PostgeSQL and everything goes well and as planned. I was thinking about the usage of the Cursor in PostgreSQl and what if I am making a lot of them while one is enough. But I don't want to pass the cursor to all my SQL functions.

Here's my simplified example.

dbConn, dbCurs = openDataBase(config)
doSomeThing(dbCurs, name, age, listOfJohns)

def doSomething(dbCurs, name, age, listOfPoeple):
  listOfPoeple SQLnrOfPeopleNamed(dbCurs, name, age)
  #here some fine checking if there's a list
  #room for more code. etc. etc.

def SQLnrOfPeopleNamed(dbCurs, inpName, inpAge)
  dbCurs.execute(Some SQL-thingy)
  #check and return result

dbCurs is the value that is passed on to every function that contains the SQL-query. Now is the dbCurs very PostgreSQL specific. Whenever I change this database to e.q. MySQL I have to rewrite the query function SQLnrOfPeople, and the interfacing to these functions.

I want to have the situation I only have to rewrite the functionality of the SQL function. So, I was thinking about creating a Cursor class in every SQL function and close it. This will result in a more generic interface where only the connection is needed.

dbConn = openDataBase(config)
doSomeThing(dbConn, name, age, listOfJohns)

def doSomething(dbConn, name, age, listOfPoeple):
  listOfPoeple SQLnrOfPeopleNamed(dbConn, name, age)
  #here some fine checking if there's a list
  #room for more code. etc. etc.

def SQLnrOfPeopleNamed(dbConn, inpName, inpAge)
   dbCurs = dbConn.cursor()
   dbCurs.execute(Some SQL-thingy)
   #check and return result

But I will create and close more cursors. I read in the manual that should be okay and I think this is a good solution. But I am still a bit doubtful about it.

millimoose
  • 39,073
  • 9
  • 82
  • 134
  • 1
    You could try implementing both versions and then comparing the performance. My gut feeling however tells me that unless you're creating, say, thousands of cursors it doesn't matter. – millimoose May 27 '12 at 10:55
  • 1
    I found some more information. Cursors is mainly used to get the DB some air for heavy queries, especially when you place them in a for-loop. There is no risk in creating and closing new cursors sequentially. Testing on speed is good to test the behaviour, but you'll never know if you make a serious fault in your code. –  May 27 '12 at 21:16
  • I haven't much worked with DB-API, but the spec tells me that multiple cursors in a single connection aren't isolated from each other. So it seems that you should actually use separate *connections* for separate logical operations. (And reuse a connection within a single transactional operation.) – millimoose May 27 '12 at 22:09

1 Answers1

0

In general there's no problem at all with opening and closing multiple cursors sequentially. You can think of the cursor as basically a pointer to a query result set (and it is a set of pointers to cached data). Opening a cursor allocates a pointer and closing one frees the memory.

In general I think that logical cleanliness is best here so having one cursor per query is the way to go.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182