One of my SQLite database tables has a publication citation field. I'm trying to update/append to the database table from a source Excel file. So, I am using pandas to bring in the Excel file, and I wrote a loop to update by row. (Note, I wrote a loop instead of using pandas '.to_sql' because on replace, it deletes the primary keys from the SQLite table.)
I am getting an error related to ascii encoding when I try to update the SQLite table. The errors are related to em-dashes, accented letters in authors' names, etc.
How can I force some kind of ignore/replace on the encoding errors and just default to some kind of simple string? It is not important for the citation string to be perfectly replicated in the SQL table.
The loop below works if I drop the Citations field that has the encoding issues.
# my variables:
# fileIn, tab, db_path
import pandas as pd, sqlite3
xls = pd.ExcelFile(fileIn)
df = xls.parse(tab)
df['Citation'] = df['Citation'].fillna(value='tbd')
df = df.drop('Citation', axis= 'columns')
# drop for now, issues with unicode characters
conn = qlite3.connect(db_path)
cur = conn.cursor()
allfields = list(df)
allfields.remove('index')
fields2 = ', '.join(str(x) for x in allfields)
cells = ['?'] * len(allfields)
cells = ', '.join(str(x) for x in cells)
str_sql = 'INSERT INTO ' + tablename + ' (' + fields2 + ') ' + 'VALUES' + ' (' + cells + ')'
for row in df.index.values:
val = ['?'] * len(allfields)
for ii in range(len(allfields)):
field = allfields[ii]
val[ii] = df.loc[row].at[field]
# issues with unicode characters from Citations in val
print val
cur.executemany(str_sql, (val,))
del val
conn.commit()
conn.close()
Also worth noting that I fixed previous type errors by putting this before my loop:
sqlite3.register_adapter(np.float64, float)
sqlite3.register_adapter(np.float32, float)
sqlite3.register_adapter(np.int64, int)
sqlite3.register_adapter(np.int32, int)
def adapt_datetime(datetime):
return (datetime.strftime('%Y-%m-%d %H:%M:%S')).encode()
sqlite3.register_adapter(dt.datetime, adapt_datetime)
sqlite3.register_adapter(dt.date, adapt_datetime)
