Python错误集锦:sqlite3插入记录提示:IntegrityError: UNIQUE constraint failed: table_juzicode._id

原文链接:http://www.juzicode.com/archives/3986

错误提示:

下列代码第2次执行时,插入记录提示:IntegrityError: UNIQUE constraint failed: table_juzicode._id

#juzicode.com/vx:桔子code
import  sqlite3

#检查表是否存在,存在返回True
def check_table_exsist(cursor,table_name):
    sql = '''SELECT tbl_name FROM sqlite_master WHERE type = 'table' '''
    cursor.execute(sql)
    values = cursor.fetchall()
    tables = []
    for v in values:
        tables.append(v[0])
    print('数据库包含的表:',tables) 
    if table_name in tables:
        return True
    return False
#查询、显示
def query(cursor,table_name):
    sql = '''  SELECT _id, name, price, weight  from  ''' + table_name
    values = cursor.execute(sql)
    for v in values:
        print('----------------')
        print('_id   =',v[0])
        print('name  =',v[1])
        print('price =',v[2])
        print('weight=',v[3])    
    
db_name = 'test2.db'
table_name = 'table_juzicode'

#连接数据库,创建游标
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
print(db_name +' 数据库连接成功')

#表不存在则建表
if check_table_exsist(cursor,table_name) is not True:
    sql = '''CREATE TABLE '''+table_name +''' (
        _id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,      
        price REAL,
        weight REAL
        )'''
    cursor.execute(sql)    
    conn.commit()
    print(table_name + ' 创建成功')
else:
    print(table_name + ' 已经存在')

#增加
sql='''INSERT INTO ''' + table_name + ''' (_id,name,price,weight) \
      VALUES (1,'orange', 1.11,  100.1 )'''    
cursor.execute(sql)
sql='''INSERT INTO ''' + table_name + ''' (name,price,weight) \
      VALUES ('apple', 2.22,  200.2 )'''    
cursor.execute(sql)     
conn.commit()
query(cursor,table_name)

#关闭游标,关闭连接
cursor.close()
conn.close()
==========运行结果:
test2.db 数据库连接成功 数据库包含的表: ['table_juzicode', 'sqlite_sequence'] table_juzicode 已经存在 

--------------------------------------------------------------------------- IntegrityError                            Traceback (most recent call last) <ipython-input-10-424fbaac2126> in <module>      49 sql='''INSERT INTO ''' + table_name + ''' (_id,name,price,weight) \      50       VALUES (1,'orange', 1.11,  100.1 )'''     ---> 51 cursor.execute(sql)      52 sql='''INSERT INTO ''' + table_name + ''' (name,price,weight) \      53       VALUES ('apple', 2.22,  200.2 )'''     IntegrityError: UNIQUE constraint failed: table_juzicode._id

错误原因:

1、建表时 _id字段是主键必须唯一:_id INTEGER PRIMARY KEY AUTOINCREMENT,当上述程序第1次执行时已经写入了_id=1的记录,第2次执行时_id=1的记录因为已经存在,所以再次插入就会导致IntegrityError

解决方法:

1、因为是自增型的类型,可以不必指定_id插入记录:

#增加
sql='''INSERT INTO ''' + table_name + ''' (name,price,weight) \
      VALUES ('orange', 1.11,  100.1 )'''  #去掉_id字段插入  
cursor.execute(sql)
sql='''INSERT INTO ''' + table_name + ''' (name,price,weight) \
      VALUES ('apple', 2.22,  200.2 )'''    
cursor.execute(sql)     
conn.commit()
query(cursor,table_name)

扩展内容:

  1. Python进阶教程m12–sqlite3模块


关注微信公众号:“桔子code”,欢迎后台留言撩我,我会尽我所能为你解惑Python,C等编程知识

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注