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

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.

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.