#->Exception error: The weak reference object no longer exists

Question

This is the code that produces the error:

import mysql.connector
import datetime

class Command:
    def __init__(self):
        mydb = mysql.connector.connect(host='localhost', passwd='1234', user='root', database='customers')

        self.mycursor = mydb.cursor()

    def execute(self, contest_id, url, Questions):
        date = datetime.date.today()
        Time = datetime.datetime.now().strftime("%I:%M")

        self.mycursor.execute(f"INSERT INTO contest(contest_name, url_tag, Questions, At_date, At_time) VALUES('{contest_id}', '{url}', {Questions}, '{date}', '{Time}')")

I'm running Python code but I get the following error message:

Traceback (most recent call last):
    
   File "C:\python39\lib\site-packages\mysql\connector\cursor.py", line 518, in execute
    if not self._connection:
ReferenceError: weakly-referenced object no longer exists

Answer 1

My guess is that your connection is lost because it doesn't belong to your class. Can you try this method?

import mysql.connector
import datetime

class Command:
    def __init__(self):
        self.conn = mysql.connector.connect(host='localhost', passwd='1234', user='root', database='customers')

        self.mycursor = self.conn.cursor()

    def execute(self, contest_id, url, Questions):
        date = datetime.date.today()
        Time = datetime.datetime.now().strftime("%I:%M")

        self.mycursor.execute(f"INSERT INTO contest(contest_name, url_tag, Questions, At_date, At_time) VALUES('{contest_id}', '{url}', {Questions}, '{date}', '{Time}')")

Answer 2

In your code, the mydb database connection object is created in the __init__ method of the Command class. However, this connection object is not saved as an attribute of the class, so when the __init__ method completes, the mydb object may be recycled by Python's garbage collector because it is no longer referenced by any variables.

In order to solve this problem, you need to save the database connection object mydb as an attribute of the class so that it will not be recycled after the __init__ method is executed. You can do this by setting a property on self, such as self.mydb.

import mysql.connector  
import datetime  
  
class Command:  
    def __init__(self):  
        self.mydb = mysql.connector.connect(host='localhost', passwd='1234', user='root', database='customers')  
        self.mycursor = self.mydb.cursor()  
  
    def execute(self, contest_id, url, questions):  
        date = datetime.date.today()  
        time = datetime.datetime.now().strftime("%H:%M")  # 使用 24 小时制可能更为普遍  
  
        # 使用参数化查询以防止 SQL 注入攻击  
        query = "INSERT INTO contest (contest_name, url_tag, Questions, At_date, At_time) VALUES (%s, %s, %s, %s, %s)"  
        values = (contest_id, url, questions, date, time)  
          
        try:  
            self.mycursor.execute(query, values)  
            self.mydb.commit()  # 提交事务以确保数据被保存到数据库  
        except mysql.connector.Error as err:  
            print(f"Error: {err}")  
            self.mydb.rollback()  # 回滚事务以防止部分数据写入