PyMySQL and Database

Question

My current school assignment is requiring us to create a library system to manage both members and books. I am using PyMySQL to create the codes in the back. I am also using Tkinter to do the front-end coding as well.

For instance, I have a membership creation feature in my library system. I know in MYSQL I would have to use the INSERT INTO TABLE VALUES (A,B,C) to insert a new entry into MYSQL. However, I am not sure how to read a user's input in PyMySQL and put that data into the database. I know what I did was wrong (in picture). I was just experimenting around.

memberid = input("Enter your member ID: ")
name = input("Enter your name: ")
faculty = input("Enter your faculty: ")
phoneNumber = input("Enter your phone number: ")
emailAddress = input("Enter your email address: ")

with connection.cursor() as cursor:
    query = "INSERT INTO Members VALUES(memberid, name, faculty, phoneNumber, emailAddress)"
    cursor.execute(query)
    print("Membership created")

Could you please help me? Thank you.

Answer 1

pymysql supports prepared statements see manual

with connection:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `members`  VALUES (%s, %s,%s, %s,%s)"
        cursor.execute(sql, (memberid, name, faculty, phoneNumber, emailAddress))