Cannot insert data into database using pymysql, what is wrong with my syntax?

Question

I am trying to insert some data into a table in mysql. But there is something wrong with my sql syntax, and I just couldn't find it, cannot find anything online either.

Tried using the raw string to insert as well, got the same error message.

def save_room_info(data):
    conn = pymysql.connect(...)//this goes right
    try:
        with conn.cursor() as cursor:
            table = 'room'
            keys = ', '.join(data.keys())
            values = ', '.join(['%s'] * len(data))
            sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
            try:
                if cursor.execute(sql, tuple(data.values())):
                    print('Successful')
                    conn.commit()
            except Exception:
                print('Failed', sys.exc_info())
                conn.rollback()
    finally:
        conn.close()


def main():
    data = {
        'title': 'a',
        'screenshot': 'a',
        'type': 0,
        'viewCount': 0,
        'nickname': 'Allen',
        'level': 20,
        'headUrl': 'a',
        'tag': 'a',
        'rank': 3,
        'followerCount': 290
    }
    save_room_info(data)

There is an 'id' field in table 'room', which I set it to Auto Increment. And 'id' is the only primary key. But even I add id during data insertion, it still gave the same error message.

Got this error message:

ProgrammingError(1064, "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 'rank, followerCount) VALUES ('a', 'a', 0, 0, 'Allen', 20, 'a', 'a', 3, 290)' at line 1"), 

Answer 1

Rank is reserved keyword in mysql. Look here https://dev.mysql.com/doc/refman/8.0/en/keywords.html

You have two options 1. Rename column 2. Wrap column in backticks like rank