IntegrityError: datatype mismatch

Question

I know this question was asked before here. The reason was a mismatch between the SQL schema and the inserted data.

So I made sure that the SQL schema matches my inserted data. But I get an IntegrityError Error. Could you tell me, where the datatype mismatch could be?

   conn = sqlite3.connect("contdata_sql.db")
   c = conn.cursor() # ich brauche ein Curser Object

   c.execute('''CREATE TABLE imbalanced ([ChannelStatusResult] INTEGER PRIMARY KEY,
      [Channels] text,
      [Channel] text,
      [Channel_Type] text,
      [Channel_Name] text)''')

     np.array(map(str, array_2d_sql))# make sure all values are strings
     print("array_2d_sql = ",array_2d_sql) 
     # = ['ChannelStatusResult' 'Channels' 'Channel' 'ChannelName'
     #'REST_RECEIVER_LOOKUP']
     # ['ChannelStatusResult' 'Channels' 'Channel' 'ChannelID'
     # '87842bb134ba31cf9c43685fabcd2eac']
      ...
     print("array_2d_sql.shape = ",array_2d_sql.shape) # = (461, 5)

      c.executemany('''INSERT INTO imbalanced VALUES (?,?,?,?,?)''',  array_2d_sql) # Error occures here!!!

If your elements are all strings, it could be problematic trying to insert values into your `INTEGER PRIMARY KEY` column. — sjw, Jul 10 '19 at 15:28

MikeT · Answer 1 · 2019-07-11T19:40:51.977

Any type of data can be stored in any type of column , with one exception, the exception being that a column defined specifically as INTEGER PRIMARY KEY (with or without AUTOINCREMENT) is an alias of the rowid column, which must be an integer. If it is not an integer then that is when you get the datatype mismatch error.

As such the cause is that the first value of your insert is not an integer.

Typically INTEGER PRIMARY KEY is used for a self-generated unique identifier and the value is not supplied but used as a means of uniquely identifying a row. In such a usage a value is not provided (or null can be used) and SQLite generates a value (1 for the first, then likely 2, then 3 and so on).

The actual best fix is unclear, other than to say that you probably need to define the [ChannelStatusResult] differently, so that it is not INTEGER PRIMARY KEY and therefore that the column is not an alias of the rowid column.

If you used

c.execute('''CREATE TABLE imbalanced ([ChannelStatusResult] INT PRIMARY KEY,
      [Channels] text,
      [Channel] text,
      [Channel_Type] text,
      [Channel_Name] text)''')

As INTEGER PRIMARY KEY isn't coded then the [ChannelStatusResult] column is not an alias of the rowid and thus can be any value. The rowid column is then hidden but still usable e.g. you could use SELECT *,rowid FROM any_table.

However, without being aliased, the rowid can be changed by VACUUM and therefore cannot be relied upon to not change (and should therefore not be used for relationships (a common and efficient means of building relationships between tables)).
Note although INT PRIMARY KEY may resolve the issue, this may not be the best fix.

IntegrityError: datatype mismatch

1 Answers1