时间戳

首页 > 解决方案 > 插入操作中的 RBDMS sqlite3 错误..回滚

python - 插入操作中的 RBDMS sqlite3 错误..回滚

问题描述

我正在尝试在 RBDMS 表中插入多条记录，但收到错误

Error in insert operation..rollback

谁能帮我解决这个问题？下面是添加多条记录和创建表本身的代码

# Insert multiple records in table DAY_CARE

qry="insert into DAY_CARE (dayCareID, dayCareWebsite, dayCareAddress, dayCarePhone, dayCareBills, clientID, clientBookingID, staffHead) values (?,?,?,?,?,?,?,?);"
daycarelist=[('day care','www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 1, 1, 'Bob Smith'),
           ('day care','www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100 , 2, 2, 'Bob Smith'),
           ('day care','www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 3, 3, 'Bob Smith'),
           ('day care','www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 4, 4, 'Bob Smith'),
           ('day care','www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 5, 5, 'Bob Smith'),
           ('day care','www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 6, 6, 'Bob Smith'),
           ('day care','www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 7, 7, 'Bob Smith'),
           ('day care','www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 8, 8, 'Bob Smith'),
           ('day care','www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 9, 9, 'Bob Smith'),
           ('day care','www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 10, 10, 'Bob Smith')]


try:
        cur.executemany(qry, daycarelist)
        conn.commit()
        print ('Records inserted successfully..committed')
except:
        print ('Error in insert operation..rollback')
        conn.rollback()

这是成功创建的 DAY_CARE 表。

# Day Care Table
qry='''

CREATE TABLE DAY_CARE (
        dayCareID INTEGER PRIMARY KEY,
        dayCareWebsite TEXT (40),
        dayCareAddress TEXT (50),
        dayCarePhone INTEGER (11),
        dayCareBills INTEGER (6),
        clientID INTEGER (50),
        clientBookingID INTEGER (50),
        staffHead TEXT (50) 
        
 );

'''
try:
        cur.execute(qry)
        print ('Table created successfully')
except:
# If table already exists then use the SQLite console to connect to the database and then use the drop table command.
# .. Altenatively, use the SQLiteStudio GUI to delete table ECC_DEPARTMENT and execute this code block again.
        print ('Error in creating table')

标签： pythondatabasesqlite

解决方案

dayCareID是一个integer primary key，这意味着它是autoincrement。
您不应将其包含在INSERT语句的列列表中：

qry="insert into DAY_CARE (dayCareWebsite, dayCareAddress, dayCarePhone, dayCareBills, clientID, clientBookingID, staffHead) values (?,?,?,?,?,?,?);"
daycarelist=[('www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 1, 1, 'Bob Smith'),
           ('www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100 , 2, 2, 'Bob Smith'),
           ('www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 3, 3, 'Bob Smith'),
           ('www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 4, 4, 'Bob Smith'),
           ('www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 5, 5, 'Bob Smith'),
           ('www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 6, 6, 'Bob Smith'),
           ('www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 7, 7, 'Bob Smith'),
           ('www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 8, 8, 'Bob Smith'),
           ('www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 9, 9, 'Bob Smith'),
           ('www.eddc.co.uk','Exeter Doggy Day Care, Stoker Rd, Exeter, EX4 4PY', 1392661000, 100, 10, 10, 'Bob Smith')]

另外，请注意，在表的语句中设置列的长度INTEGER(50)并不TEXT(50)意味着CREATE这将是列的最大长度。
事实上 SQLite 忽略了它。

推荐阅读