I have tried this, and I'm having trouble getting it to work. I was originally having issues with utf decoding of the document, so I added the errors='ignore part. Now I'm at a pont where I'm having an sqlite3 syntax error. I have the same number of placeholders as I do columns (I know it's a lot of columns. I'm trying to put it all into a database to make it easier to put some of these columns together in a way that makes sense)
Here is my code.
import csv, sqlite3
con = sqlite3.connect("4x4orce.db")
cur = con.cursor()
cur.execute("""CREATE TABLE t (Premier Part Number, Long Description, External Long Description, Upc, USA, Item,
Availability, Canada Item Availability, Core Charge, Customer Price, Jobber, MAP, Retail, Inventory Status, Brand,
Mfg Part Number, UT whse, KY whse, TX whse, CA whse, WA whse, ImageURL, ShipsLTL, ItemWithCores, Cust CAD Price,
Proposition 65 Carcinogen, Proposition 65 Reproductive Harm)""") # use your column names here
with open('python/4x4orce/premier_data_feed_4x4orce.csv', 'r', errors='ignore') as fin:
# `with` statement available in 2.5+
# csv.DictReader uses first line in file for column headings by default
dr = csv.DictReader(fin) # comma is default delimiter
to_db = [(i['Premier Part Number'], i['Long Description'], i['External Long Description'], i['Upc'],
i['USA Item Availability'], i['Canada Item Availability'], i['Core Charge'], i['Customer Price'],
i['Jobber'], i['MAP'], i['Retail'], i['Inventory Status'], i['Brand'], i['Mfg Part Number'], i['UT whse'],
i['KY whse'], i['TX whse'], i['CA whse'], i['WA whse'], i['ImageURL'], i['ShipsLTL'], i['ItemWithCores'],
i['Customer CAD Price'], i['Proposition 65 Carcinogen'], i['Proposition 65 Reproductive Harm']) for i in dr]
cur.executemany("""INSERT INTO t (Premier Part Number, Long Description, External Long Description, Upc, USA, Item,
Availability, Canada Item Availability, Core Charge, Customer Price, Jobber, MAP, Retail,
Inventory Status, Brand, Mfg Part Number, UT whse, KY whse, TX whse, CA whse, WA whse, ImageURL,
ShipsLTL, ItemWithCores, Cust CAD Price, Proposition 65 Carcinogen, Proposition 65 Reproductive Harm)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", to_db)
con.commit()
con.close()
Here is the error I'm getting:
Traceback (most recent call last):
File "/Users/mwhites/programming/python/4x4orce/ImportCSV.py", line 8, in <module>
Proposition 65 Carcinogen, Proposition 65 Reproductive Harm)""") # use your column names here
sqlite3.OperationalError: near "65": syntax error
Process finished with exit code 1
What am I doing wrong here?