SQL 1064语法错误排查：Python列表转字符串适配数据库插入

问题情况 #

我是编程和SQL新手，开发图书馆管理数据库系统时，插入数据持续触发SQL 1064错误，推测是authors字段为Python列表，转字符串的方式有问题，不确定怎么转才不影响后续查询。

原代码如下：

def googleAPI(self):

    lineTitle = str(titleInfo.text())
    # create getting started variables
    api = "https://www.googleapis.com/books/v1/volumes?q=isbn:"
    isbn = lineTitle.strip()#input("Enter 10 digit ISBN: ").strip()

    # send a request and get a JSON response
    resp = urlopen(api + isbn)
    # parse JSON into Python as a dictionary
    book_data = json.load(resp)

    # create additional variables for easy querying
    volume_info = book_data["items"][0]["volumeInfo"]
    author = volume_info["authors"]
    # practice with conditional expressions!
    prettify_author = author if len(author) > 1 else author[0]

    # display title, author, page count, publication date
    # fstrings require Python 3.6 or higher
    # \n adds a new line for easier reading
    gTitle = str(volume_info['title'])
    pCount = str(volume_info['pageCount'])
    pubDate = str(volume_info['publishedDate'])
    author = str(volume_info["authors"])
    prettify_author = author if len(author) > 1 else author[0]
    stringAuthor = str(prettify_author)

    insertBooksF = "insert into "+bookTable+" values('"+isbn+"','"+gTitle+"','"+stringAuthor+"','"+pubDate+"','"+pCount+"')"
    try:
        cur.execute(insertBooksF)
        con.commit()
        print("You failed at failing")
    except:
        print("You actually failed")



    print(f"\nTitle: {volume_info['title']}")
    print(f"Author: {prettify_author}")
    print(f"Page Count: {volume_info['pageCount']}")
    print(f"Publication Date: {volume_info['publishedDate']}")
    print("\n***\n")

问题根源 #

1. 作者列表转字符串错误：直接用str(volume_info["authors"])会把列表转成['作者A', '作者B']这种格式，里面的单引号会和SQL语句中的单引号冲突，触发SQL语法错误（1064错误）。
2. SQL注入风险：直接拼接字符串生成SQL语句，不仅容易出语法问题，还会导致严重的安全漏洞。

解决方案 #

1. 正确转换作者列表为字符串 #

用", ".join(authors)把作者列表转为逗号分隔的字符串，比如["J.K.罗琳", "张三"]会变成"J.K.罗琳, 张三"，这种格式既符合SQL字符串要求，又方便后续用LIKE查询作者。

2. 使用参数化查询避免语法错误和注入 #

用数据库占位符（MySQL用%s，SQLite用?）代替字符串拼接，让数据库驱动自动处理特殊字符转义，彻底解决语法问题和注入风险。

修正后的代码 #

def googleAPI(self):
    lineTitle = str(titleInfo.text())
    api = "https://www.googleapis.com/books/v1/volumes?q=isbn:"
    isbn = lineTitle.strip()

    resp = urlopen(api + isbn)
    book_data = json.load(resp)

    volume_info = book_data["items"][0]["volumeInfo"]
    authors = volume_info["authors"]
    # 将作者列表转为逗号分隔的字符串，适配SQL存储
    prettify_author = ", ".join(authors)

    gTitle = volume_info['title']
    pCount = volume_info['pageCount']
    pubDate = volume_info['publishedDate']

    # 参数化SQL语句，用%s作为占位符
    insertBooksF = f"INSERT INTO {bookTable} VALUES (%s, %s, %s, %s, %s)"
    try:
        # 把参数打包成元组传入execute，自动处理转义
        cur.execute(insertBooksF, (isbn, gTitle, prettify_author, pubDate, pCount))
        con.commit()
        print("数据插入成功")
    except Exception as e:
        # 打印具体错误信息，方便排查问题
        print(f"插入失败：{str(e)}")

    print(f"\nTitle: {volume_info['title']}")
    print(f"Author: {prettify_author}")
    print(f"Page Count: {volume_info['pageCount']}")
    print(f"Publication Date: {volume_info['publishedDate']}")
    print("\n***\n")

关键修改说明 #

• 作者处理：替换原来的列表转字符串逻辑，用join生成干净的逗号分隔字符串，避免SQL语法冲突。
• SQL执行：改用参数化查询，彻底避免字符串拼接带来的语法错误和安全问题。
• 异常处理：捕获具体异常并打印错误信息，方便定位问题，而不是模糊的失败提示。

内容的提问来源于stack exchange，提问作者Jackson P