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)