Why doesn't Python return my mysql connector cursor from a function?

Question

Python (2.7.3) breaks my mysql connector cursor in some strange way when I return it from a function. This first example works fine ...

cnx = connect() sql = "SELECT * FROM MyTable" cursor = cnx.cursor() cursor.execute(sql) row = cursor.fetchone() 

However, if I return the cursor and try fetchone () (or fetchall ()) from the outside, it throws an exception ...

 def run_query(): cnx = connect() sql = "SELECT * FROM MyTable" cursor = cnx.cursor() cursor.execute(sql) return cursor mycursor = run_query() row = mycursor.fetchone() 

He throws ...

 File "/usr/lib/pymodules/python2.7/mysql/connector/cursor.py", line 533, in fetchone row = self._fetch_row() File "/usr/lib/pymodules/python2.7/mysql/connector/cursor.py", line 508, in _fetch_row (row, eof) = self.db().protocol.get_row() AttributeError: 'NoneType' object has no attribute 'protocol' 

This is despite the fact that "print type (mycursor)" will print "mysql.connector.cursor.MySQLCursor"

What type of unholy corruption is Python running on objects returned from functions? (Keep in mind that this will be done for the cursors passed in the module ... so this does not look like an object passed from the "import mysql.connector" area ...)

Answer 1

I don't have MySQL right away, but as Preet Sangha said, when you connect to the database inside the function and return the cursor, your cnx variable cnx out of scope when the function exits, so the database connection closes and your cursor refers to the closed connection to the database.

This is not the case with the upper code example, which can explain why it works and why the lower example does not.

Answer 2

Can you print the type (join) in your function?

Example:

 >>> import MySQLdb as mydb >>> def runQuery(sql): ... db = mydb.connect('localhost', 'testuser', 'test', 'test') ... cur = db.cursor() ... cur.execute(sql) ... data = cur.fetchall() ... print "Query :: %s" %sql ... print "Result:: %s" %data ... return cur ... >>> >>> cursor = runQuery("SELECT VERSION()") Query :: SELECT VERSION() Result:: ('5.6.11-log',) >>> >>> cursor.execute("SELECT * FROM EMPLOYEES") 3L >>> data = cursor.fetchall() >>> >>> print data (('JOHN', 30L, 23000.0), ('SONY', 26L, 14000.0), ('SMITH', 53L, 123000.0)) >>> >>>