I really got stuck about context managers (__enter__ & __exit__) and mysql.connector with an error

I wrote a class to collect data from MySQL db. I didn’t understand how to use the __enter__ and __exit__ methods when I use the with.

More specifically, I don’t understand:

  1. Where should I return the self.raws = self.crouser.execute(self.que) is actually the data from the db.

  2. Until when the connection is going to be open?

  3. How to use the with method in this specific problem?

My code :

import mysql.connector

class tak:
     def __init__(self,host1, user1, password1, db1,aat, que1):
          self.host = host1
          self.user = user1
          self.password = password1
          self.database = db1
          self.auth_plugin = aat
          self.que = que1

     def co(self):
          try:
               self.my = mysql.connector.connect(
                    host = self.host,
                    user = self.user,
                    password = self.password,
                    database = self.database,
                    auth_plugin = self.auth_plugin,
                    charset = 'utf8'
               )
          except mysql.connector.errors as err :
               print(f'{err}')
          self.crouser = self.my.cursor()
          self.raws = self.crouser.execute(self.que)
          self.crouser.fetchall()

     def __enter__(self):
          return

     def __exit__(self, exc_type, exc_val, exc_tb):
          self


with tak(host1="localhost",
         user1="root",
         password1="1234",
         db1="local_db",
         aat='mysql_native_password',
         que1='select * from nand').co() as log:
     print(log)

Answer

A lot of explanation below:

__enter__ prepares stuff (opens stuff that should be later closed and such). If you want to do with ... as ..., then returned object should be the one that you want to get in as ... (very often it’s self but it doesn’t have to be).

__exit__ should clean up stuff – close stuff that should be closed, reassign stuff their original values…

I have no idea why you’re doing the query in the constructor already. The classic way would be: __enter__ opens connection, __exit__ closes. Then any query is in the block.

Compare to file operations:

f = open(filename)
data = f.readlines()
f.close()

Becomes:

with open(filename) as f: # __enter__ returns self
    data = f.readlines()
# we exit the block, so __exit__ is performed - it does f.close() for us

While your code is more like…

with open_and_read(filename) as data:
    # ????? do something with data I guess?

You do exactly that: open stuff and do one predefined thing… and get stuff that is not closeable, so it could live for longer (right now it lives only inside the with block).

If that’s really what you wanted, then ditch context managers altogether: just do a function that opens connection, gets the query, closes connection, returns log.

But if you want a correct context manager, it would probably look like this:

import mysql.connector
class tak :
     def __init__(self,host1, user1, password1, db1,aat): #removed query
          self.host = host1
          self.user = user1
          self.password = password1
          self.database = db1
          self.auth_plugin = aat
          # self.que = que1 nope, we don't want it here

     def __enter__(self):
          self.my = mysql.connector.connect(
              host = self.host,
              user = self.user,
              password = self.password,
              database = self.database,
              auth_plugin = self.auth_plugin,
              charset = 'utf8'
          )
          return self.my.cursor()
     
     def __exit__(self, exc_type, exc_val, exc_tb):
          self.my.close()
     

# now we can get database info once but open the connection multiple times
db = tak(...)
with db as cur: # our __enter__ returns the cursor
    cur.execute(...)
    data1 = cur.fetchall()
# as we exit the block, __exit__ is called

# do something with data1

with db as cur: # open new connection, return new cursor
    cur.execute(...)
    ...
...

[I haven’t tested this code, it might need some adjustments. The point of my answer was to explain proper usage of context managers rather than to give perfect code.]