Python 操作SQLite数据库的示例
SQLite,是一款轻型的数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中。在很多嵌入式产品中使用了它,它占用资源非常的低,python中默认继承了操作此款数据库的引擎sqlite3说是引擎不如说就是数据库的封装版,开发自用小程序的使用使用它真的大赞
简单操作SQLite数据库:创建sqlite数据库是一个轻量级的数据库服务器,该模块默认集成在python中,开发小应用很不错.
importsqlite3 #数据表的创建 conn=sqlite3.connect("data.db") cursor=conn.cursor() create="createtablepersion("\ "idintauto_incrementprimarykey,"\ "namechar(20)notnull,"\ "ageintnotnull,"\ "msgtextdefaultnull"\ ")" cursor.execute(create)#执行创建表操作
简单操作SQLite数据库:简单的插入语句的使用
insert="insertintopersion(id,name,age,msg)values(1,'lyshark',1,'hellolyshark');" cursor.execute(insert) insert="insertintopersion(id,name,age,msg)values(2,'guest',2,'helloguest');" cursor.execute(insert) insert="insertintopersion(id,name,age,msg)values(3,'admin',3,'helloadmin');" cursor.execute(insert) insert="insertintopersion(id,name,age,msg)values(4,'wang',4,'hellowang');" cursor.execute(insert) insert="insertintopersion(id,name,age,msg)values(5,'sqlite',5,'hellosql');" cursor.execute(insert) data=[(6,'王舞',8,'python'),(7,'曲奇',8,'python'),(9,'C语言',9,'python')] insert="insertintopersion(id,name,age,msg)values(?,?,?,?);" cursor.executemany(insert,data)
简单的查询语句的使用
select="select*frompersion;" cursor.execute(select) #print(cursor.fetchall())#取出所有的数据 select="select*frompersionwherename='lyshark';" cursor.execute(select) print(cursor.fetchall())#取出所有的数据 select="select*frompersionwhereid>=1andid<=2;" list=cursor.execute(select) foriinlist.fetchall(): print("字段1:",i[0]) print("字段2:",i[1])
简单的更新数据与删除
update="updatepersionsetname='苍老师'whereid=1;" cursor.execute(update) update="updatepersionsetname='苍老师'whereid>=1andid<=3;" cursor.execute(update) delete="deletefrompersionwhereid=3;" cursor.execute(delete) select="select*frompersion;" cursor.execute(select) print(cursor.fetchall())#取出所有的数据 conn.commit()#事务提交,每执行一次数据库更改的操作,就执行提交 cursor.close() conn.close()
SQLite小试牛刀实现用户名密码验证,当用户输入错误密码后,自动锁定该用户1分钟.
importsqlite3 importre,time conn=sqlite3.connect("data.db") cursor=conn.cursor() """create="createtablelogin("\ "usernametextnotnull,"\ "passwordtextnotnull,"\ "timeintdefault0"\ ")" cursor.execute(create) cursor.execute("insertintologin(username,password)values('admin','123123');") cursor.execute("insertintologin(username,password)values('guest','123123');") cursor.execute("insertintologin(username,password)values('lyshark','1231');") conn.commit()""" whileTrue: username=input("username:")#这个地方应该严谨验证,尽量不要让用户拼接SQL语句 password=input("passwor:")#此处为了方便不做任何验证(注意:永远不要相信用户的输入) sql="select*fromloginwhereusername='{}'".format(username) ret=cursor.execute(sql).fetchall() iflen(ret)!=0: now_time=int(time.time()) ifret[0][3]<=now_time: print("当前用户{}没有被限制,允许登录...".format(username)) ifret[0][0]==username: ifret[0][1]==password: print("用户{}登录成功...".format(username)) else: print("用户{}密码输入有误..".format(username)) times=int(time.time())+60 cursor.execute("updateloginsettime={}whereusername='{}'".format(times,username)) conn.commit() else: print("用户名正确,但是密码错误了...") else: print("账户{}还在限制登陆阶段,请等待1分钟...".format(username)) else: print("用户名输入错误")
SQLite检索时间记录通过编写的TimeIndex函数检索一个指定范围时间戳中的数据.
importos,time,datetime importsqlite3 """ conn=sqlite3.connect("data.db") cursor=conn.cursor() create="createtablelyshark("\ "timeintprimarykey,"\ "cpuintnotnull"\ ")" cursor.execute(create) #批量生成一堆数据,用于后期的测试. foriinrange(1,500): times=int(time.time()) insert="insertintolyshark(time,cpu)values({},{})".format(times,i) cursor.execute(insert) conn.commit() time.sleep(1)""" #db=data.db传入数据库名称 #table=指定表lyshark名称 #start=2019-12-1214:28:00 #ends=2019-12-1214:29:20 defTimeIndex(db,table,start,ends): start_time=int(time.mktime(time.strptime(start,"%Y-%m-%d%H:%M:%S"))) end_time=int(time.mktime(time.strptime(ends,"%Y-%m-%d%H:%M:%S"))) conn=sqlite3.connect(db) cursor=conn.cursor() select="select*from{}wheretime>={}andtime<={}".format(table,start_time,end_time) returncursor.execute(select).fetchall() if__name__=="__main__": temp=TimeIndex("data.db","lyshark","2019-12-1214:28:00","2019-12-1214:29:00")
SQLite提取数据并绘图通过使用matplotlib这个库函数,并提取出指定时间的数据记录,然后直接绘制曲线图.
importos,time,datetime importsqlite3 importnumpyasnp frommatplotlibimportpyplotasplt defTimeIndex(db,table,start,ends): start_time=int(time.mktime(time.strptime(start,"%Y-%m-%d%H:%M:%S"))) end_time=int(time.mktime(time.strptime(ends,"%Y-%m-%d%H:%M:%S"))) conn=sqlite3.connect(db) cursor=conn.cursor() select="select*from{}wheretime>={}andtime<={}".format(table,start_time,end_time) returncursor.execute(select).fetchall() defDisplay(): temp=TimeIndex("data.db","lyshark","2019-12-1214:28:00","2019-12-1214:29:00") list=[] foriinrange(0,len(temp)): list.append(temp[i][1]) plt.title("CPUCount") plt.plot(list,list) plt.show() if__name__=="__main__": Display()
文章作者:lyshark
文章出处:https://www.cnblogs.com/lyshark
以上就是Python操作SQLite数据库的示例的详细内容,更多关于Python操作SQLite数据库的资料请关注毛票票其它相关文章!