2

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

Community
  • 1
  • 1
David Rodriguez
  • 41
  • 5
  • 1
    An integrity error means that an entry with the same primary key already exists in the table. Are you trying to insert a row with a key of 1 when the table already has one? It may not be a Python issue...it could just be that you originally inserted the row in Python 2.7. – Karin Aug 12 '16 at 21:16
  • No the table is empty and changing the id to be something else still causes the error. It feels to me like something else in my code, or maybe my version of sqlite3, is breaking as there shouldn't be a datatype mistmatch error. – David Rodriguez Aug 15 '16 at 15:35

0 Answers0