Create MySQL Cursor Function with Python

Question

I am attempting to create a function that initializes the MySQL + Python connection. Here is what I have so far:

import mysql.connector

MySQLConfig = {
  'user': 'user',
  'password': 'password',
  'host': 'localhost',
  'database': 'DB',
  'raise_on_warnings': True,
}

# Initialize MySQL Connection + Cursor
def createCursor():
    cnx = mysql.connector.connect(**MySQLConfig)
    cursor = cnx.cursor()
    return cursor

cursor = createCursor()

query = 'SHOW tables'
cursor.execute(query)

for row in cursor:
    print(row)

The code will run fine when not enclosed in the createCursor() function. Once I put it in one I receive the following error:

Traceback (most recent call last):
  File "./config.py", line 24, in <module>
    cursor.execute(query)
  File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 473, in execute
    if not self._connection:
ReferenceError: weakly-referenced object no longer exists

Any ideas on what I may need to do? I've tried returning the connection only and working with the cursor outside the function, that also results in the same error too.

score 3 · Accepted Answer · 2015-03-22 22:20:06Z

3

As you have made cnx a local variable it is garbage collected at the end of function..
Do it something like this

cnx = mysql.connector.connect(**MySQLConfig)
# Initialize  Cursor
def createCursor():
    return cnx.cursor()

But that is a bad idea.My Approach would be something like this

import mysql.connector
class MysqlHelper:

    def __init__(self):
        MySQLConfig = {'user': 'user','password': 'password','host': 'localhost','database': 'DB','raise_on_warnings': True}
        self.cnx = mysql.connector.connect(**MySQLConfig) 
        self.cursor = self.cnx.cursor()

    def execute_query(self,query):
        self.cursor.execute(query)      
        for row in self.cursor:
            print(row)

mysql_helper = MysqlHelper()
mysql_helper.execute_query("SHOW tables")

edited Mar 22, 2015 at 22:20

answered Mar 22, 2015 at 22:04

user2493476

Sign up to request clarification or add additional context in comments.

2 Comments

user1804933 Over a year ago

Awesome thanks, exactly what I needed! Why exactly is it a bad idea though?

user2493476 Over a year ago

It is not good to create mysql cursor every time insted you create it as a global variable...

Collectives™ on Stack Overflow

Create MySQL Cursor Function with Python

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related