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()