Check row exists in MySQL through function in python class

Question

I am trying to implement a function in my database manager class that checks if a row (user) exists (through their email) in my MySQL table.

See code below:

def has_user(self, table_name : str, user_credentials: UserCredentials) -> bool:
        mysql_hasuser_query = """
        SELECT COUNT(1) FROM {t_name} WHERE email = {u_email}
        """.format(t_name=table_name, u_email=user_credentials.email)

        cursor = self.connection.cursor()
        cursor.execute(mysql_hasuser_query)
        if cursor.fetchone()[0]:
            print("User exists in database!")
            return True

I am receiving the following syntax error mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com' at line 1

However, I implemented this in MySQL query editor and it worked fine. I am not sure what I am doing wrong here.

Barmar · Accepted Answer · 2021-07-28 20:54:14Z

1

You're not quoting the email in the query. But you should use a parameter instead of formatting the string into the query.

def has_user(self, table_name : str, user_credentials: UserCredentials) -> bool:
    mysql_hasuser_query = """
    SELECT COUNT(1) FROM {t_name} WHERE email = %s
    """.format(t_name=table_name)

    cursor = self.connection.cursor()
    cursor.execute(mysql_hasuser_query, (user_credentials.email,))
    if cursor.fetchone()[0]:
        print("User exists in database!")
        return True

answered Jul 28, 2021 at 20:54

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ossz Over a year ago

Great, I see now what I was doing wrong. Thanks so much this works as intended.

Collectives™ on Stack Overflow

Check row exists in MySQL through function in python class

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related