Python MySQL ReferenceError: weakly-referenced object no longer exists

Question

I want to connect to a mySQL Server and get all users from the users table. But if i try to execute my code, it says:

/bin/python /home/luca/PycharmProjects/Test/sql.py
Traceback (most recent call last):
  File "/home/luca/PycharmProjects/Test/sql.py", line 18, in <module>
    for x in get_user_by_username("testuser"):
  File "/usr/lib/python3.8/site-packages/mysql/connector/cursor.py", line 861, in fetchone
    row = self._fetch_row()
  File "/usr/lib/python3.8/site-packages/mysql/connector/cursor.py", line 831, in _fetch_row
    if not self._have_unread_result():
  File "/usr/lib/python3.8/site-packages/mysql/connector/cursor.py", line 351, in _have_unread_result
    return self._connection.unread_result
ReferenceError: weakly-referenced object no longer exists

Process finished with exit code 1

Here is my code

import mysql.connector


def get_user_by_username(username):
 mydb = mysql.connector.connect(
     host="localhost",
     user="testuser",
     passwd="k3gc8pHPvEtGqND",
     database="test"
 )

 mycursor = mydb.cursor()

 mycursor.execute("SELECT * FROM users")
 return mycursor


for x in get_user_by_username("testuser"):
    print(x)

Why is this happening and how can I fix it?

Answer 1

cursor object uses connection object.
When your get_user_by_username function finishes execution,
the connection to mysql gets closed therefore cursor cannot exists as well.
Having the function return both connection and cursor will work.

import mysql.connector

def get_user_by_username(username):
    mydb = mysql.connector.connect(
        host="localhost",
        user=username,
        passwd="k3gc8pHPvEtGqND",
        database="test"
        )

    mycursor = mydb.cursor()

    mycursor.execute("SELECT * FROM users")
    return mydb, mycursor

mydb, mycursor = get_user_by_username("testuser")
for x in mycursor:
    print(x)

Answer 2

You could use a variable that has all rows in it and the lopop over it

import mysql.connector


def get_user_by_username(username):
 mydb = mysql.connector.connect(
     host="localhost",
     user="testuser",
     passwd="k3gc8pHPvEtGqND",
     database="test"
 )

 mycursor = mydb.cursor()

 mycursor.execute("SELECT * FROM users") 
 records = mycursor.fetchall()
 return records

for x in get_user_by_username("testuser"):
    print(x)

Answer 3

THis is basically happening because of "Weak Reference" read more about this at this DOC

refer to this code, it will work like charm:

import mysql.connector as connector

def connection():

    config = {
        "user": "dev_staq",
        "password": "dev_staq",
        "host": "localhost",
        "port": 3306,
        "database": "dev_staq"
    }
    try:
        c = connector.connect(**config)
        return c
    except:
        print "connection error"
        exit(1)


def test1(): #no error method
    cn = connection()
    cur = cn.cursor()
    cur.execute("select * from ulogin")
    print cur.fetchone()
test1()

let me know any further query. Thanks.