I'm trying to debug why my code is returning an error. I have a SQLite database and I'm trying to add records to a specific table. My code has worked perfectly fine in Python 2.7, but I get IntegrityError in Python 3.5
new_rec = [1, 12.0, -12.0, None, None, b'fakesource', None, None]
self.conn.execute("INSERT INTO {} VALUES({})".format('sources', ','.join('?' * len(columns))), tuple(new_rec))
columns is a list of the column names in my table. It has the same length as new_rec.
This returns:
sqlite3.IntegrityError: datatype mismatch
For reference, here is the table schema:
CREATE TABLE sources (id INTEGER PRIMARY KEY,
ra REAL,
dec REAL,
designation TEXT,
publication_id INTEGER,
shortname TEXT,
names TEXT,
comments TEXT)
Changing new_rec to something like: new_rec = [1, 12.0, None, None, None, None, None, None] still causes an IntegrityError. Any thoughts?
Update 8/16/2016
I've examined the types of new_rec and believe I found the problem:
[<class 'numpy.int64'>, <class 'numpy.float64'>, <class 'numpy.float64'>, <class 'NoneType'>, <class 'NoneType'>, <class 'numpy.bytes_'>, <class 'NoneType'>, <class 'NoneType'>]
Looks like sqlite3 does not play nice with numpy.int64: inserting numpy integer types into sqlite with python3
