SQLite in python reported an SyntaxError but there's no problem in SQLite console

Question

I want to insert some data to the database, and using the code below, but when I running it, the python interpreter repoted sqlite3.OperationalError: near "s": syntax error. Then I copied the sql printed by python link insert into movie250(info_link, pic_link, chinese_name, other_name, score, rate, introduction, info) values ('https://movie.douban.com/subject/1292052/', 'https://img2.doubanio.com/view/photo/s_ratio_poster/public/p480747492.jpg', '肖申克的救赎', 'The Shawshank Redemption', 9.7, 2615494, '希望让人自由', '导演: 弗兰克·德拉邦特 Frank Darabont 主演: 蒂姆·罗宾斯 Tim Robbins ... 1994 美国犯罪剧情') to sqlite console and there is no any problem.

def save_data_to_db(data: list | None,
                    db_url: str) -> None:
    conn: sqlite3.Connection = init_database(db_url)
    print('Database loaded...')

    cursor: sqlite3.Cursor = conn.cursor()

    for datum in data:
        sql = "insert into movie250" \
              "(" \
              "`info_link`, " \
              "`pic_link`, " \
              "`chinese_name`, " \
              "`other_name`, " \
              "`score`, " \
              "`rate`, " \
              "`introduction`, " \
              "`info`" \
              ") " \
              "values " \
              f"('{datum[0]}', '{datum[1]}', '{datum[2]}', '{datum[3]}', {datum[4]}, {datum[5]}, '{datum[6]}', '{datum[7]}')"
        print(f"Executing sql: [{sql}]")
        cursor.execute(sql)
        conn.commit()

    return None

I tried to debug it, and I find that the value sql is

Console report:

The last sql run before error occurred:

insert into movie250(`info_link`, `pic_link`, `chinese_name`, `other_name`, `score`, `rate`, `introduction`, `info`) values ('https://movie.douban.com/subject/1291561/', 'https://img1.doubanio.com/view/photo/s_ratio_poster/public/p2557573348.jpg', '千与千寻', '千と千尋の神隠し', 9.4, 2043030, '最好的宫崎骏，最好的久石让 ', '导演: 宫崎骏 Hayao Miyazaki   主演: 柊瑠美 Rumi Hîragi   入野自由 Miy... 2001   日本   剧情 动画 奇幻')

Full Stack Trace Report:

Traceback (most recent call last):
  File "C:\Program Files\JetBrains\PyCharm 2021.3.3\plugins\python\helpers\pydev\pydevd.py", line 1483, in _exec
    pydev_imports.execfile(file, globals, locals)  # execute the script
  File "C:\Program Files\JetBrains\PyCharm 2021.3.3\plugins\python\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)
  File "D:/workspace/python/douban-movies-spider/main.py", line 214, in <module>
    save_data_to_db(data_list, "./movies.db")
  File "D:/workspace/python/douban-movies-spider/main.py", line 199, in save_data_to_db
    cursor.execute(sql)
sqlite3.OperationalError: near "s": syntax error

**Never** use string concatenation to include values in sql statements - your code is currently vulnerable to an SQL injection. Instead, use the built-in parameter substitution functionality - there are examples at the top of the [sqlite3 docs](https://docs.python.org/3/library/sqlite3.html) — SuperStormer, May 12 '22 at 02:55
What is your Windows code page? My wild guess is that the translation of your string somehow ends up with a quote mark. That isn't true for UTF-8, but that's not the normal code page for WIndows. — Tim Roberts, May 12 '22 at 03:02
On a wild whim, does it work if you run it from the command line instead of inside PyCharm? — Tim Roberts, May 12 '22 at 03:06
@SuperStormer Now it comes to sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 8, and there are 41 supplied. The printed data is ('info link', 'image src', '肖申克的救赎', 'The Shawshank Redemption', '9.7', '2615523', 'inq', 'some info') — vorbote, May 12 '22 at 03:08
@TimRoberts It is the same situation, but after using cursor.executemany, the error is sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 8, and there are 41 supplied. — vorbote, May 12 '22 at 03:10

score 0 · Answer 1 · answered May 12 '22 at 03:27

convert all item to a tuple in the list

for index in range(len(data)):
        data[index] = tuple(data[index])

generate a sql like this:

sql = "insert into movie250(`info_link`, `pic_link`, " \
          "`chinese_name`, `other_name`, `score`, " \
          "`rate`, `introduction`, `info`) " \
          "values " \
          "(?, ?, ?, ?, ?, ?, ?, ?)"

then use cursor run cursor.executemany()

cursor.executemany(sql, data)

Thanks to @SuperStormer's comment, and all those people gave the ideas.

It isn't necessary to convert the items to tuples. `executemany` works with any nested sequence: list of lists, tuple of tuples, list of tuples, etc. — Tim Roberts, May 12 '22 at 03:29

SQLite in python reported an SyntaxError but there's no problem in SQLite console

1 Answers1