CODEWITHSUNDEEP
pythonexceldatabasesqlite
Dru
Dru

Reputation: 13

Creating a database from an excel sheet using sqlite, OperationalError: near "Android": syntax error

I am getting an OperationalError: near "Android": syntax error and I am not sure why, the examples in the class so far have used this method of a for loop and format() with no mention of this type of error and I am lost as to how to fix it and create my database for the list of tuples

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()

Upvotes: 1

Views: 54

Answers (1)

Vsevolod Timchenko
Vsevolod Timchenko

Reputation: 756

Values should be single-quoted, not double-quoted.

Replace

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

with 

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

EDIT:

Moreover, even better alternative would be to discard quotes and let the library handle it for you (values partially omitted):

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

Upvotes: 1

Related Questions