Python 操作SQLite数据库

微软技术分享

已于 2023-07-02 21:22:28 修改

阅读量1.1w

点赞数 1

分类专栏：《Python 编程技术实践》文章标签： sqlite 数据库 python database 开发语言增删改查

于 2020-01-09 17:36:00 首次发布

本文链接：https://blog.csdn.net/lyshark_csdn/article/details/124939270

版权

《Python 编程技术实践》专栏收录该内容

154 篇文章 68 订阅

订阅专栏

SQLite是一种轻量级的嵌入式数据库管理系统，它是一个C语言库，可以在客户端应用程序中直接嵌入，而不需要独立的数据库服务器。SQLite数据库以文件形式存储在客户端的设备中，适用于嵌入式系统、移动设备以及一些小型的桌面应用程序。

SQLite具有以下特点：

无服务器架构：SQLite是无服务器的，没有独立的数据库服务器进程，所有的数据库操作都在客户端进行。
嵌入式数据库：SQLite数据库可以被嵌入到应用程序中，无需单独的数据库服务器。这使得SQLite成为嵌入式系统和移动设备上的理想选择。
轻量级和快速：由于没有网络通信和独立的数据库进程，SQLite数据库操作非常快速。
跨平台支持：SQLite支持跨平台，可以在多个操作系统上运行，包括Windows、macOS、Linux等。
自包含：SQLite数据库以单个文件形式存在，包含了所有表、索引和数据。这使得备份和迁移变得简单。

在Python中，可以使用sqlite3模块与SQLite数据库进行交互。这个模块提供了一个简单的API，可以执行SQL查询、创建表、插入数据、更新数据等数据库操作。

下面是一个简单的示例代码，演示了如何使用Python的sqlite3模块来创建一个SQLite数据库、创建表、插入数据和查询数据：

import sqlite3

# 连接数据库（如果不存在则会创建一个新的数据库文件）
conn = sqlite3.connect('example.db')

# 创建游标对象，用于执行SQL语句
cursor = conn.cursor()

# 创建表
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')

# 插入数据
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 25))
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Charlie', 22))

# 提交事务
conn.commit()

# 查询数据
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)

# 关闭游标和连接
cursor.close()
conn.close()

上述代码演示了如何创建一个SQLite数据库文件（如果不存在的话），创建一个名为users的表，插入一些数据，然后查询并输出所有数据。

创建数据库

如下代码是使用Python的sqlite3模块创建一个名为persion的数据表。代码中的数据库文件名为data.db，通过sqlite3.connect()方法创建与数据库的连接。然后，使用cursor()方法创建一个游标对象，可以通过游标对象执行SQL语句。

接下来，使用cursor.execute()方法执行create语句，创建了一个名为persion的数据表。create语句定义了表的结构，包括id字段（自增主键）、name字段（20个字符的字符串，非空）、age字段（整数类型，非空）和msg字段（可选的文本类型，默认为null）。

执行cursor.execute(create)后，数据表persion就被成功创建了。

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)        # 执行创建表操作

插入数据

如下代码是使用cursor.execute()和cursor.executemany()方法向创建的persion数据表中插入数据的示例。

首先，使用cursor.execute()方法分别执行了一系列的insert语句，向persion表中插入了一条条记录。每个insert语句指定了要插入的字段和对应的值。例如，第一个insert语句插入了一条记录，id为1，name为'lyshark'，age为1，msg为'hello lyshark'。依次类推，通过多个insert语句可以插入多条记录。

接着，使用cursor.executemany()方法插入了一个列表中的多条记录。该方法可以一次性执行多个插入操作。在示例中，data列表中存储了多个记录的数据，每个记录是一个元组，包含了id、name、age和msg四个字段的值。通过?占位符来表示待插入的值，最后将data作为参数传递给cursor.executemany()方法。

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)

以上代码示例了向数据表插入数据的方法，可以根据需要，使用不同的插入语句和数据来灵活地操作数据表。请注意，在执行插入操作后，应该使用conn.commit()方法提交事务，以确保插入的数据被持久化保存到数据库中。

查询语句

如下代码是使用cursor.execute()方法执行SELECT语句从数据表中检索数据的示例。

首先，使用cursor.execute()方法执行了一个SELECT * FROM persion;语句，该语句会检索persion表中的所有数据。通过cursor.fetchall()方法可以取出所有的数据，并以列表的形式返回。在示例中，可以使用print(cursor.fetchall())语句打印出所有的数据。

接着，执行了一个带有条件的SELECT语句，SELECT * FROM persion WHERE name='lyshark';，该语句会检索name字段值为'lyshark'的记录。同样地，使用cursor.fetchall()方法可以取出满足条件的所有数据，并以列表的形式返回。在示例中，使用print(cursor.fetchall())语句打印出满足条件的数据。

最后，执行了一个带有范围条件的SELECT语句，SELECT * FROM persion WHERE id >= 1 AND id <= 2;，该语句会检索id字段值在1和2之间（包含1和2）的记录。通过cursor.fetchall()方法可以取出满足条件的所有数据，并以列表的形式返回。在示例中，使用cursor.fetchall()返回的结果赋值给list变量，并通过遍历list列表来访问每条记录的字段值。在示例中，通过i[0]和i[1]来访问记录的第一个字段和第二个字段的值，并将其打印出来。

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

更新与删除数据

如下代码展示了如何使用cursor.execute()方法执行更新（UPDATE）和删除（DELETE）操作，以及提交事务（commit）并关闭数据库连接。

首先，执行了一个更新（UPDATE）语句，UPDATE persion SET name='苍老师' WHERE id=1;，该语句会将id为1的记录的name字段更新为'苍老师'。通过cursor.execute()方法执行更新操作。

接着，执行了一个带有范围条件的更新（UPDATE）语句，UPDATE persion SET name='苍老师' WHERE id>=1 AND id<=3;，该语句会将id在1和3之间（包含1和3）的记录的name字段更新为'苍老师'。同样地，使用cursor.execute()方法执行更新操作。

然后，执行了一个删除（DELETE）语句，DELETE FROM persion WHERE id=3;，该语句会删除id为3的记录。通过cursor.execute()方法执行删除操作。

在执行完更新和删除操作后，通过执行SELECT * FROM persion;语句，并使用cursor.fetchall()方法取出所有的数据，并以列表的形式返回。通过print(cursor.fetchall())语句打印出更新和删除后的数据。

接下来，调用conn.commit()方法提交事务，将之前的数据库更改操作生效。

最后，通过cursor.close()方法关闭游标（cursor），通过conn.close()方法关闭数据库连接（conn）。

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

账号密码验证

实现用户名密码验证,当用户输入错误密码后,自动锁定该用户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")
    print(temp)

提取数据并绘图

通过使用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()

微软技术分享

关注

1
点赞
踩
13

收藏

觉得还不错? 一键收藏
打赏
0
评论
Python 操作SQLite数据库

SQLite是一种轻量级的嵌入式数据库管理系统，它是一个C语言库，可以在客户端应用程序中直接嵌入，而不需要独立的数据库服务器。SQLite数据库以文件形式存储在客户端的设备中，适用于嵌入式系统、移动设备以及一些小型的桌面应用程序。在Python中，可以使用sqlite3模块与SQLite数据库进行交互。这个模块提供了一个简单的API，可以执行SQL查询、创建表、插入数据、更新数据等数据库操作。
复制链接

扫一扫