Using a context manager with mysql connector python

Question

I'm moving my code across from an sqlite database to mysql and I'm having a problem with the context manager, getting the following attribute error.

I've tried combinations of mydb.cursor() as cursor, mydb: etc...

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
    database="database_name"

cur = mydb.cursor()

with mydb as cursor:
 AttributeError: __enter__

Answer 1

Python has a built-in way to implement a context manager if the object you're creating have a .close() method, by using the contextlib.closing context manager.

From the Python docs:

contextlib.closing(thing)

Return a context manager that closes thing upon completion of the block. This is basically equivalent to:

 from contextlib import contextmanager
 
 @contextmanager
 def closing(thing):
     try:
         yield thing
     finally:
         thing.close()

So, for your specific issue, you can use not only on the connection, but also the cursor.

Your code would be:

from contextlib import closing

import mysql.connector


query = "SELECT * FROM table"

db_conn_info = {
    "user": "root",
    "passwd": "",
    "host": "localhost",
    "port": 5000,
    "database": "database_name"
}

with closing(mysql.connector.connect(**db_conn_info)) as conn:
    with closing(conn.cursor()) as cur:
        cur.execute(query)
        result = cur.fetchall()