Python操作sqlite数据库小节

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

学习了Python操作sqlite数据库，做一个小结，以备后用。

import sqlite3
import os
# 进行数据库操作时，主要是参数如何传输
try:
# 链接数据库
conn=sqlite3.connect('DYDHX.db')
c=conn.cursor()
# 插入
c.execute("insert into tb_goods values(?,?,?,?,?)",("4","小龙虾",'xia','2000','斤'))
c.execute("insert into tb_goods values(%d,'%s','%s','%s','%s')"%(5,'小龙虾','xia','2000','斤'))
c.execute("insert into tb_goods('goodsid','goodsname','kucun','unit') values (?,?,?,?)",("14","小龙虾",'2000','斤'))
conn.commit()
# 查询
goods=c.execute("select * from tb_goods ")
goods=c.execute("select * from tb_goods where goodsid= ? ",("2"))
goods=c.execute("select * from tb_goods where goodsid= ? ","2")
goods=c.execute("select * from tb_goods where goodsid= %d "%(2))
goods=c.execute("select * from tb_goods where goodsid= %d and goodsname='%s'"%(1,"小龙虾"))
goods=c.execute("select * from tb_goods where goodsid=? and goodsname=?",(1,"小龙虾"))
# 将查询出的数据放入列表中，result列表中的元素是字典，goodlist列表中的元素是列表
result=[]
goodlist=[]
for g in goods:
# print("name=",g[1])
v={}
v['id']=g[0]
v['name']=g[1]
v['kucun']=g[3]
result.append(v) tmp=[]
tmp.append(g[0])
tmp.append(g[1])
tmp.append(g[3])
goodlist.append(tmp)
print(result)
print("goodlist=",goodlist)# 生成一个列表数据，列表的元素是字典
for i in range(0,len(result)):
result[i]['id']=result[i]['id']+10
result[i]['name']="螃蟹"
result[i]['kucun']=1200
print(result)
# 将一个列表插入到表里,列表中元素是字典
for g in result:
conn.execute("insert into tb_goods('goodsid','goodsname','kucun') values (?,?,?)", (g['id'],g['name'],g['kucun']))
conn.commit()
# 生成一个列表数据，列表的元素是列表
for i in range(0,len(goodlist)):
goodlist[i][0]=goodlist[i][0]+10
goodlist[i][1]="螃蟹"
goodlist[i][2]=1200
print(goodlist)
# 将一个列表插入到表里,列表中元素是列表
for g in goodlist:
conn.execute("insert into tb_goods('goodsid','goodsname','kucun') values (?,?,?)", g)
conn.commit()# 删除
c.execute("delete from tb_goods where goodsid= ? ","2")
c.execute("delete from tb_goods where goodsid= %d "%(14))
conn.commit()
except Exception as e:
print(e)