使用 sqlite 从 excel 工作表创建数据库，操作错误：接近 "Android" ：语法错误

Question

我收到一个操作错误：靠近"Android"：语法错误，我不确定为什么，到目前为止，类中的示例使用了 for 循环和 format(( 的方法，没有提到这种类型的错误，我不知道如何修复它并为元组列表创建我的数据库

import openpyxl
import sqlite3
connection = sqlite3.connect("test1.db")
cursor = connection.cursor()
wb = openpyxl.load_workbook('items.xlsx')
sheet=wb['20190928-items']
savedata=[]
lseg=[] #a list of each rows contents
x=sheet.max_row
y=sheet.max_column
for i in range(1, x+1):
for j in range(1, y+1):
av=(sheet.cell(row=i, column=j).value)
lseg.append(av)
savedata.append(tuple(lseg))
lseg=[]
sql_command = """
CREATE TABLE item ( 
asin CHAR(10) PRIMARY KEY, 
brand VARCHAR(20), 
title VARCHAR(30), 
url VARCHAR(80),
image VARCHAR(255),
rating float(1),
reviewUrl VARCHAR(80),
totalreviews int(100),
prices float(2));"""
cursor.execute(sql_command)
for p in savedata:
format_str = """INSERT INTO item (asin, brand, title, url, image, rating, reviewUrl, totalreviews, prices)
VALUES ("{asin}", "{brand}", "{title}", "{url}", "{image}", "{rating}", "{reviewUrl}", "{totalreviews}", "{prices}");"""
sql_command = format_str.format(asin=p[0], brand=p[1], title=p[2], url=p[3], image=p[4], rating=p[5], reviewUrl=p[6], totalreviews=p[7], prices=p[8])
cursor.execute(sql_command)
connection.commit()
connection.close()

Answer 1

值应为单引号，而不是双引号。

取代

VALUES ("{asin}", "{brand}", "{title}", "{url}", "{image}", "{rating}", "{reviewUrl}", "{totalreviews}", "{prices}");

跟

VALUES ('{asin}', '{brand}', '{title}', '{url}', '{image}', '{rating}', '{reviewUrl}', '{totalreviews}', '{prices}');

编辑：

此外，更好的选择是丢弃引号并让库为您处理它(部分省略值(：

format_str = """INSERT INTO item (asin, brand)
VALUES (?, ?);"""
cursor.execute(sql_command, (p[0], p[1]))

https://docs.python.org/3.5/library/sqlite3.html#sqlite3.Cursor.execute