pymysql.err.Error: Already closed

Question

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()

Gutenberg

add usage example, how you are "canceling that message and giving correct userid and password" – Isaiah

On the last line of your code example, you're closeing the connection. After that you no longer have an open connection and so you get the exception you see. – Carburize

@AzatIbrakov, in case of wrong userid or password there is just an alert message (Java script) saying that your userid or password is wrong and I cancel it by clicking on the cross sign. So once I came to know that i typed wrong userid or password, then I give correct userid and password but then I got this ** pymysql.err.Error: Already closed** here i was expecting from the code is that whenever i click on login button it should make a connection with database. – Gutenberg

@AnthonySottile, when I remove {finally: connection.close()} then code does not work at all. – Gutenberg

Answer 1

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.

Armbruster

Answer 2

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.

Cashbook

I don't want to close the connection. So whenever I click on the login button it should make a connection with the database.Right now it makes connection only a single time. At the first attempt if the user gives a correct userid or password, then the script opens the home page for the user, but if the user provides a wrong userid or password, then script shows that "you have entered a wrong userid or password" after canceling this message they replaces correct password with the wrong password, this time I was expecting to see the homepage but it shows error "pymysql.err.Error:Already closed" – Gutenberg

if i remove finally: connection.close() this then script doesn't work at all. – Gutenberg

Answer 3

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.

Factual

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Autosuggestion

Answer 4

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"

Xiomaraxiong