python - pymysql.err.ProgrammingError:您的SQL语法有错误

我正在尝试将数据从python字典插入mySql DB。但我不明白我的SQL查询出了什么问题。

我收到此错误：


  pymysql.err.ProgrammingError：（1064，u“您的SQL错误
  句法;检查与您的MySQL服务器版本相对应的手册
  在``DiedIn''（'name'，'city'）VALUES附近使用的正确语法
  （'\'Ethel_Merman \''，'\'New_York_City \\ n \''）'在第1行“）


这是我的代码：

import pymysql.cursors

wasBornIn = {}
with open("wasBornIn.txt") as f:
for line in f:
   (key, val) = line.split(':')
   wasBornIn[key] = val

diedIn = {}
with open("diedIn.txt") as f:
for line in f:
   (key, val) = line.split(':')
   diedIn[key] = val

 isLocatedIn = {}
 with open("isLocatedIn.txt") as f:
for line in f:
   (key, val) = line.split(':')
   isLocatedIn[key] = val

connection = pymysql.connect(host='********', user='******', password='******', db='*******',
                         charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
# Create a new record
    sql = "DROP TABLE DiedIn"
    cursor.execute(sql)

with connection.cursor() as cursor:
# Create a new record
    sql = "DROP TABLE isLocatedIn"
    cursor.execute(sql)

with connection.cursor() as cursor:
# Create a new record
    sql = "DROP TABLE BornIn"
    cursor.execute(sql)

with connection.cursor() as cursor:
    sql = "CREATE TABLE `DiedIn`(`name` varchar(100) COLLATE utf8_bin NOT NULL, `city` varchar(50) COLLATE utf8_bin NOT NULL, " \
         "PRIMARY KEY(`name`)) ENGINE = InnoDB DEFAULT CHARSET = utf8" \
          " COLLATE = utf8_bin;"
    cursor.execute(sql)

with connection.cursor() as cursor:
    sql = "CREATE TABLE `isLocatedIn`(`name` varchar(150) COLLATE utf8_bin NOT NULL, `location` varchar(50) COLLATE utf8_bin NOT NULL, " \
         "PRIMARY KEY(`name`)) ENGINE = InnoDB DEFAULT CHARSET = utf8" \
          " COLLATE = utf8_bin;"
    cursor.execute(sql)

with connection.cursor() as cursor:
    sql = "CREATE TABLE `BornIn`(`name` varchar(100) COLLATE utf8_bin NOT NULL, `city` varchar(50) COLLATE utf8_bin NOT NULL, " \
         "PRIMARY KEY(`name`)) ENGINE = InnoDB DEFAULT CHARSET = utf8" \
          " COLLATE = utf8_bin;"
    cursor.execute(sql)

with connection.cursor() as cursor:
    for key, value in diedIn.iteritems():
        strKey = repr(key)
        strValue = repr(value)
        sql = "INSERT INTO 'DiedIn' ('name', 'city') VALUES (%s, %s);"
        cursor.execute(sql, (strKey, strValue))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
finally:
connection.close()

谢谢您的帮助。

最佳答案

尝试：

sql = "INSERT INTO 'DiedIn' (name, city) VALUES ('%s', '%s');"

关于python - pymysql.err.ProgrammingError:您的SQL语法有错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43799305/