5

I'm kind of new to Python and its MySQLdb connector. I'm writing an API to return some data from a database using the RESTful approach. In PHP, I wrapped the Connection management part in a class, acting as an abstraction layer for MySQL queries.

In Python:

  • I define the connection early on in the script: con = mdb.connect('localhost', 'user', 'passwd', 'dbname')

  • Then, in all subsequent methods:

    import MySQLdb as mdb

def insert_func():

with con: 

cur = con.cursor(mdb.cursors.DictCursor)
cur.execute("INSERT INTO table (col1, col2, col3) VALUES (%s, %s, %s)", (val1, val2, val3) )

rows = cur.fetchall()

#do something with the results

return someval

    etc.

  • I use mdb.cursors.DictCursor because I prefer to be able to access database columns in an associative array manner.

Now the problems start popping up:

  • in one function, I issue an insert query to create a 'group' with unique 'groupid'.

  • This 'group' has a creator. Every user in the database holds a JSON array in the 'groups' column of his/her row in the table.

  • So when I create a new group, I want to assign the groupid to the user that created it.

  • I update the user's record using a similar function.

  • I've wrapped the 'insert' and 'update' parts in two separate function defs.

  • The first time I run the script, everything works fine.

  • The second time I run the script, the script runs endlessly (I suspect due to some idle connection to the MySQL database).

  • When I interrupt it using CTRL + C, I get one of the following errors:

    • "'Cursor' object has no attribute 'connection'"
    • "commands out of sync; you can't run this command now"
    • or any other KeyboardInterrupt exception, as would be expected.

It seems to me that these errors are caused by some erroneous way of handling connections and cursors in my code.

I read it was good practice to use with con: so that the connection will automatically close itself after the query. I use 'with' on 'con' in each function, so the connection is closed, but I decided to define the connection globally, for any function to use it. This seems incompatible with the with con: context management. I suspect the cursor needs to be 'context managed' in a similar way, but I do not know how to do this (To my knowledge, PHP doesn't use cursors for MySQL, so I have no experience using them).

I now have the following questions:

  1. Why does it work the first time but not the second? (it will however, work again, once, after the CTRL + C interrupt).

  2. How should I go about using connections and cursors when using multiple functions (that can be called upon in sequence)?

MattDMo
  • 100,794
  • 21
  • 241
  • 231
timbit
  • 353
  • 2
  • 11

2 Answers2

0

I think there are two main issues going on here- one appears to be python code and the other is the structure of how you're interacting to your DB.

First, you're not closing your connection. This depends on your application's needs - you have to decide how long it should stay open. Reference this SO question

from contextlib import closing
with closing( connection.cursor() ) as cursor:
    ... use the cursor ...

# cursor closed.  Guaranteed.

connection.close()

Right now, you have to interrupt your program with Ctl+C because there's no reason for your with statement to stop running.

Second, start thinking about your interactions with the DB in terms of 'transactions'. Do something, commit it to the DB, if it didn't work, rollback, if it did, close the connection. Here's a tutorial.

Community
  • 1
  • 1
Jared
  • 3,651
  • 11
  • 39
  • 64
  • Thank you! Very helpful. So you would suggest to open the connection, start a transaction before calling the insert and update functions? Would I need to pass the connection as an argument to these functions, and end the transaction through 'commit' or 'rollback' depending on return values, for e.g.? – timbit May 17 '15 at 15:47
  • `commit` would always be attempted under a `try:` block. If it fails, execute `rollback()` under the `except` block. I think it's OK to leave your connection open and just work in these 'discrete' blocks of transactions. If something happens to your connection - no worries, you've already committed what you needed. If something fails, rollback so your data is still in tact and try again. So your connection exists throughout and only ends when you explicitly call it to close (con should be a global variable so its accessed everywhere) – Jared May 17 '15 at 15:53
  • Okay, thanks a lot. I will try to improve my code with this and see if it solves my issue. – timbit May 17 '15 at 15:57
0

With connections, as with file handles the rule of thumb is open late, close early.

So I would recommend share connections only where they are trying to do one thing. Or if you multiprocess, then each process gets a connection, again following open late, close early. And if you are doing sequential operation (say in a loop) open and close outside the loop. Having global connections can get messy. Mainly because now you have to keep track of which function uses it at what time, and what it tries to do with it.

The issue of "cannot run command now", is because your keyboard interrupt kills the active connection.

As to part one of your question - endlessly could be anywhere. Each instance of python will get its own connection. So when you run it the second time it should get its own connection. Open up a mysql client and do 

show full processlist

to see whats going on.

Rcynic
  • 392
  • 3
  • 10
  • Thanks for your answer. I will try to rethink my global connection strategy to fit the 'open late' close early' model. – timbit May 17 '15 at 15:53