python操作sqlite3----增删改查

1.  导入包

import sqlite3

2.  创建连接

con = sqlite3.connetc('test.db')

3.  获取游标

cur = con.cursor()

4.1 插入一条数据

insert_sql = 'insert into t_person(pname, age, score) values(?, ?)'

try:

    cur.execute(insert_sql, ('name', 20)

    #提交事务

    con.commit()

except Exception as e:

    print(e)

    con.rollback()#插入失败，回滚

finally:

    cur.close()

    con.close()

4.2  插入多条数据

try:

    cur.executemany(insert_sql, [('小李', 23), ('小花', 20), ('小明', 18)])

    con.commit()#提交事务

except Exception as e:

    print(e)

    con.rollback()#插入失败，回滚

finally:

    cur.close()

    con.close()

4.3  删除数据

del_sql = 'delete from t_person where pno=?'

try:

    cur.execute(del_sql, (3,))#删除的数据为一个元组，当只有一个数据的时候，需要加一个逗号，PyMySQL则不需要加逗号

    con.commit()#提交事务

except Exception as e:

    print(e)

    con.rollback()#删除失败则回滚

finally:

    cur.close()

    con.close()

4.4  查询一条数据

query_sql = 'select * from t_person'

try:

    cur.execute(query_sql)

    #获取一条数据

    person = cur.fetchone()

    print(person)

except Exception as e:

    print(e)

finally:

    cur.close()

    con.close()

4.5  查询所有数据

try:

    cur.execute(query_sql)

    persons = cur.fetchall()

    for person in persons:

        print(person)

except Exception as e:

    print(e)

finally:

    cur.close()

    con.close()