时间戳

首页 > 解决方案 > IntegrityError：数据类型不匹配

python - IntegrityError：数据类型不匹配

问题描述

我知道这个问题在这里之前被问过。原因是 SQL 模式和插入的数据不匹配。

所以我确保 SQL 模式与我插入的数据相匹配。但我得到一个 IntegrityError 错误。你能告诉我，数据类型不匹配可能在哪里吗？

   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!!!

标签： pythonsqlite

解决方案

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.

推荐阅读