Python 操作 SQLite 数据库完整指南：从建表到可视化

Nora

发布时间：2026-10-14 | 3人浏览过

原创

本文详细介绍如何使用 Python 内置的 sqlite3 模块进行轻量级数据库开发。内容涵盖数据库创建、数据插入、查询、更新与删除等基础 CRUD 操作。此外，还深入讲解了基于 SQLite 的用户登录验证逻辑，包括错误密码锁定机制，以及如何结合 matplotlib 库提取时间序列数据并绘制曲线图，适合小型应用开发参考。

SQLite 数据库概述

SQLite 是一款轻量级的关系型数据库管理系统，遵循 ACID（原子性、一致性、隔离性、持久性）原则，其核心代码封装在较小的 C 库中。由于资源占用极低，它被广泛应用于各类嵌入式产品中。Python 标准库默认集成了 sqlite3 模块，该模块并非独立的数据库引擎，而是对 SQLite 数据库的直接封装。对于开发自用的小型应用程序而言，使用 SQLite 是一个极佳的选择。

SQLite 基础操作

SQLite 数据库是一个轻量级的数据库服务器，该模块默认集成在 Python 中，非常适合开发小型应用程序。


import sqlite3

# 数据表的创建
conn = sqlite3.connect("data.db")
cursor = conn.cursor()
create = "create table persion(" \
     "id int auto_increment primary key," \
     "name char(20) not null," \
     "age int not null," \
     "msg text default null" \
     ")"
cursor.execute(create)    # 执行创建表操作

数据插入操作

演示 SQLite 数据库的基本插入语句操作。


insert = "insert into persion(id,name,age,msg) values(1,'lyshark',1,'hello lyshark');"
cursor.execute(insert)
insert = "insert into persion(id,name,age,msg) values(2,'guest',2,'hello guest');"
cursor.execute(insert)
insert = "insert into persion(id,name,age,msg) values(3,'admin',3,'hello admin');"
cursor.execute(insert)
insert = "insert into persion(id,name,age,msg) values(4,'wang',4,'hello wang');"
cursor.execute(insert)
insert = "insert into persion(id,name,age,msg) values(5,'sqlite',5,'hello sql');"
cursor.execute(insert)

data = [(6, '王舞',8, 'python'), (7, '曲奇',8,'python'), (9, 'C语言',9,'python')]
insert = "insert into persion(id,name,age,msg) values(?,?,?,?);"
cursor.executemany(insert,data)

数据查询操作

演示 SQLite 数据库的基本查询语句操作。


select = "select * from persion;"
cursor.execute(select)
#print(cursor.fetchall())  # 取出所有的数据

select = "select * from persion where name='lyshark';"
cursor.execute(select)
print(cursor.fetchall())  # 取出所有的数据

select = "select * from persion where id >=1 and id <=2;"
list = cursor.execute(select)
for i in list.fetchall():
  print("字段1：", i[0])
  print("字段2：", i[1])

数据更新与删除

演示 SQLite 数据库中的数据更新与删除操作。


update = "update persion set name='苍老师' where id=1;"
cursor.execute(update)

update = "update persion set name='苍老师' where id>=1 and id<=3;"
cursor.execute(update)

delete = "delete from persion where id=3;"
cursor.execute(delete)

select = "select * from persion;"
cursor.execute(select)
print(cursor.fetchall())  # 取出所有的数据

conn.commit()    # 事务提交，每执行一次数据库更改的操作，就执行提交
cursor.close()
conn.close()

用户登录与安全验证

通过 SQLite 实现简单的用户名和密码验证功能。当用户输入错误密码时，系统会自动锁定该用户 1 分钟。


import sqlite3
import re,time

conn = sqlite3.connect("data.db")
cursor = conn.cursor()
"""create = "create table login(" \
     "username text not null," \
     "password text not null," \
     "time int default 0" \
     ")"
cursor.execute(create)
cursor.execute("insert into login(username,password) values('admin','123123');")
cursor.execute("insert into login(username,password) values('guest','123123');")
cursor.execute("insert into login(username,password) values('lyshark','1231');")
conn.commit()"""

while True:
  username = input("username:") # 这个地方应该严谨验证,尽量不要让用户拼接SQL语句
  password = input("passwor:")  # 此处为了方便不做任何验证(注意:永远不要相信用户的输入)
  sql = "select * from login where username='{}'".format(username)
  ret = cursor.execute(sql).fetchall()
  if len(ret) != 0:
    now_time = int(time.time())
    if ret[0][3] <= now_time:
      print("当前用户{}没有被限制,允许登录...".format(username))
      if ret[0][0] == username:
        if ret[0][1] == password:
          print("用户 {} 登录成功...".format(username))
        else:
          print("用户 {} 密码输入有误..".format(username))
          times = int(time.time()) + 60
          cursor.execute("update login set time={} where username='{}'".format(times,username))
          conn.commit()
      else:
        print("用户名正确,但是密码错误了...")
    else:
      print("账户 {} 还在限制登陆阶段,请等待1分钟...".format(username))
  else:
    print("用户名输入错误")

时间范围数据检索

利用自定义的 TimeIndex 函数，检索指定时间戳范围内的数据记录。


import os,time,datetime
import sqlite3

"""
conn = sqlite3.connect("data.db")
cursor = conn.cursor()
create = "create table lyshark(" \
     "time int primary key," \
     "cpu int not null" \
     ")"
cursor.execute(create)
# 批量生成一堆数据,用于后期的测试.
for i in range(1,500):
  times = int(time.time())
  insert = "insert into lyshark(time,cpu) values({},{})".format(times,i)
  cursor.execute(insert)
  conn.commit()
  time.sleep(1)"""

# db = data.db 传入数据库名称
# table = 指定表lyshark名称
# start = 2019-12-12 14:28:00
# ends = 2019-12-12 14:29:20
def TimeIndex(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 {} where time >= {} and time <= {}".format(table,start_time,end_time)
  return cursor.execute(select).fetchall()

if __name__ == "__main__":
  temp = TimeIndex("data.db","lyshark","2019-12-12 14:28:00","2019-12-12 14:29:00")

数据提取与可视化绘图

提取 SQLite 中指定时间的数据记录，并使用 matplotlib 库直接绘制曲线图。


import os,time,datetime
import sqlite3
import numpy as np
from matplotlib import pyplot as plt

def TimeIndex(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 {} where time >= {} and time <= {}".format(table,start_time,end_time)
  return cursor.execute(select).fetchall()

def Display():
  temp = TimeIndex("data.db","lyshark","2019-12-12 14:28:00","2019-12-12 14:29:00")
  list = []
  for i in range(0,len(temp)):
    list.append(temp[i][1])
  plt.title("CPU Count")
  plt.plot(list, list)
  plt.show()
  
if __name__ == "__main__":
  Display()