CODEWITHSUNDEEP
pythonpycharmweb.py
Raju Singh
Raju Singh

Reputation: 475

pymysql.err.Error: Already closed

I am trying to create a login function. But it only works ones. Ex- When i give a wrong userid and password I got correct error massage that "Could't login" after canceling that message and giving correct userid and password then I get "pymysql.err.Error: Already closed" below are the sample code.

import pymysql

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='python_code',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
class LoginModel:
    def check_user(self, data):

        try:
            with connection.cursor() as cursor:
                # Read a single record
                sql = "SELECT `username` FROM `users` WHERE `username`=%s"
                cursor.execute(sql, (data.username))
                user = cursor.fetchone()
                print(user)

            if user:
                if (user, data.password):
                    return user
                else:
                    return False
            else:
                return False

        finally:
            connection.close()

Upvotes: 5

Views: 11797

Answers (4)

Kowsalya
Kowsalya

Reputation: 1

Removing finally block of code worked for me:

except pymysql.MySQLError as e:
    print(f"Error: {e}")
    connection.rollback()

finally:
    connection.close()
    #parsed_data = parse_data(data)   
    print("Parsed data for table:::", parsed_data)
    return "ok"

Upvotes: 0

Oluwatobi Giwa
Oluwatobi Giwa

Reputation: 1

Had the same issue. The "Finally clause is needed for Postgres with the psycopg2 driver, if used with context manager (with clause), it close the cursor but not the connection. The same does not apply with Pymysql.

Upvotes: 0

pbuck
pbuck

Reputation: 4551

You have a mismatch with respect to the number of times you're creating the connection (once) and the number of times you're closing the connection (once per login attempt).

One fix would be to move your: 

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='python_code',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

into your def check__user(). It would work because you'd create and close the connection on each invocation (as others have pointed out, the finally clause always gets executed.)

That's not a great design because getting database connections tends to be relatively expensive. So keeping the connection creating outside of the method is preferred.... which means you must remove the connection.close() within the method.

I think you're mixing up connection.close() with cursor.close(). You want to do the latter, not the former. In your example you don't have to explicitly close the cursor because that happens automatically with your with connection.cursor() as cursor: line.

Change finally to except, or remove the try block completely.

Upvotes: 4

JacobIRR
JacobIRR

Reputation: 8946

This is the culprit code:

finally:
    connection.close()

Per the docs: "A finally clause is always executed before leaving the try statement, whether an exception has occurred or not" From: https://docs.python.org/2/tutorial/errors.html

You didn't describe alternative behavior for what you would like to see happen instead of this, but my answer addresses the crux of your question.

Upvotes: 3

Related Questions