SQLite初级入门

十一月 26, 2023

一、为什么要使用Sqlite

此前，我一直采用的使用json存储数据的方法，这种方法虽然轻便，但操作复杂。而且实际操作的过程中，是将json的全部数据存入内存，再进行操作的，会出现一个情况就是随着数据量的增加而导致占用内存越来越多。

使用数据库不仅可以提高效率并减少内存使用，而且还能提供更复杂的数据查询和操作功能。

此外，数据库作为IT行业中非常常用的软件，这使我有不得不学习的理由。

二、了解它

What Is SQLite?
SQLite是一个C语言库，它实现了一个〔small〕(https://www.sqlite.org/footprint.html)，快速，自给自足，高可靠性，功能齐全，SQL数据库引擎。SQLite是最常用的数据库引擎。SQLite内置于所有手机和大多数计算机中，并捆绑在人们每天使用的无数其他应用程序中。更多信息…

SQLite文件格式是稳定的、跨平台的、向后兼容的，开发人员承诺（到2050年）保持这种状态(https://www.sqlite.org/lts.html)。SQLite数据库文件通常用作在系统之间传输丰富内容的容器[1][2][3]并作为数据的长期存档格式[4]。有超过1万亿（1e12）个SQLite数据库在积极使用[5]。

SQLite源代码在公共领域并且每个人都可以自由用于任何目的。

—— 以上全文摘自SQLite官网

讲的太明白了。

三、最基本的使用

 我个人的理解，想要入门数据库，需要学会四点：创建数据库、从数据库插入数据、从数据库查询查询数据，学会以上四点，就没啥问题了。

而在Python中使用SQLite，首先就是安装它。

使用我们强大的pip！

1	pip install pysqlite3

勿要用错安装命令嗷，开箱即用，安装完就可以直接用了。

创建数据库

# 连接到数据库（如果不存在则创建）
conn = sqlite3.connect('data.db')

# 创建一个游标对象
cursor = conn.cursor()

# 创建一个表
cursor.execute('''CREATE TABLE IF NOT EXISTS UserLib (  
                    UserId TEXT PRIMARY KEY,  
                    Username TEXT,
                    DayQuantity int,
                     Favorability real,
                      Inauspicious int,
                      Time DATE
                 )''')

# 断开连接
conn.close()

只需要短短的几行代码，我们就可以创建数据库了。

过程分为四步：

连接
创建对象
使用execute执行命令
断开

其中，execute中的命令尤为关键。

数据库的表中我理解的情况是一个完整结构分为三部分：

第一层：
- 表名：上文的表名就是UserLib
- CREATE TABLE IF NOT EXISTS UserLib这条代码就成功创建了一个表名为UserLib的数据库。
第二层：
- 主键：上文的主键就是UserId，数据库主要就是通过这个来完成对数据的区分的。数据库的规则是，必须有主键，且主键的值唯一，也就是不能重复。
- UserId TEXT PRIMARY KEY这条代码就定义了一个TEXT类型的主键，在数据库中TEXT表示字符串类型。
第三层：
- 次键：也就是普通的键，它的值有很多种，且可重复。
- 定义时只需要简单的定义一个键名和类型就可以了，像这样 Favorability real，就定义了一个浮点数的类型。
小记 ps:
ORDER BY：用于对查询结果进行排序。
GROUP BY：用于对查询结果进行分组。
HAVING：用于对分组后的结果进行过滤。
LIMIT：用于限制查询结果的数量。

存入数据

数据库的存入也主要是利用SQL语句来进行操作的，即调用execute()方法。

在完成连接和创建的前提下，我们可以利用以下语句对已经存在的表UserLib插入一些数据。

1	cursor.execute("INSERT INTO UserLib VALUES ('ID1145124', 'Cure Sky', 1, 1, 1, 1,0)")

ps：
- 在插入数据时，是按照键的顺序来插入的，可以小于键的数量但不能多于键的数量。毕竟如果超过了，本身就不存在更多的键了，数据库就会寄。

查询数据

在存好数据后，想要把数据从数据库再提出来，也非常简单。

以下命令可以查询一个表中全部的数据，因为使用了通配符*，所以结构包含表中的全部内容。

同时我们使用了一个fetchall() 在执行完查询命令后实际上数据就到了游标对象这里，使用这个方法则是获取全部的数据。

# 创建一个游标对象
c = conn.cursor()

# 执行查询语句 读取全部数据
c.execute("SELECT * FROM UserLib")

# 获取所有结果
rows = c.fetchall()

那么查询单列数据，则只需要指定ID就可以了。

这个语句可以查出符合 UserId = 'ID1145124'这个条件的数据，而通过前文我们知道，UserId是主键，自然就只返回一列了。

在这里我们返回数据结果调用的是fetchone()方法，这个方法只会返回一条数据，因为我们很清楚我们只查了一条。

1 2	cursor.execute('''SELECT * FROM UserLib WHERE UserId = 'ID1145124';''') Var = cursor.fetchone()

如果要查询一条数据的某个键的话，只需要把*改为键名就可以了，像这样：

1 2	cursor.execute('''SELECT DayQuantity FROM UserLib WHERE UserId = 'ID1145124';''') Num = cursor.fetchone()

修改数据

修改数据同样是使用execute()方法执行SQL语句进行操作的。

cursor.execute(f'''
        UPDATE UserLib
        SET DayQuantity = 23333
        WHERE UserID = 'ID1145124';
                 ''')
conn.commit()
conn.close()

SQL语句主要分为三个部分：

第一部分：
- UPDATE UserLib：这条代码指定了数据更新的表。
第二部分：
- SET DayQuantity = 23333：这条代码指定了一个键以及这个键的内容，简单来说就是将DayQuantity 的值改成了23333，像变量赋值一样简单。
第三部分：
- WHERE UserID = 'ID1145124';指定了主键，虽说是修改某个键的值，但如果不说是哪个主键，数据库可不知道咋改。

看到这里，恭喜你，数据库已经入门啦~（仅限身娇体柔又可爱的SQLite）

四、构造数据库类

这个是我自己想出来的，针对同一个数据库可能有不同的繁多的操作，一条一条代码的反复去写非常麻烦，使用函数代替的话也需要不断的重复连接、创建游标这个过程。我们可以借助面向对象语言的特性来封装一个数据库类，只需要实例化类然后调用它的方法，就可以进行繁多的操作了。

以下代码是我在实际项目中对此的应用，可以参考一下：

class DataSQL:
    """ 数据库对象 """

    def __init__(self):

        path = os.getcwd() + '/data/MoreGoodJrrpData/MoreGoodJrrpData.db'
        self.conn = sqlite3.connect(path)
        # 连接到数据库（如果不存在则创建）
        self.cursor = self.conn.cursor()
        # 创建一个游标对象

    def InIt(self, ID, ImageName: str):
        """ 初始化新用户的数据 """
        Time = NtpTime()
        LuckValue = 0
        LuckQuantity = 0
        Inauspicious = 0
        Value = IfLuck(ImageName)  # 判断吉凶
        LuckValue += Value

        if Value > 0:
            LuckQuantity += 1
        else:
            Inauspicious += 1

        sql = f"INSERT INTO UserLib VALUES ('{ID}', '未命名用户', 1, 0, '{LuckQuantity}', '{Inauspicious}', '{LuckValue}','{Time}')"

        self.cursor.execute(sql)

        # 保存（提交）更改
        self.conn.commit()

        # 关闭连接
        self.conn.close()

    def TodayUpdate(self, ID, ImageName: str):
        """ 抽签数据更新"""
    # try:
        # self.cursor.execute()
        # 查询时间
        self.cursor.execute(f'''SELECT Time FROM UserLib WHERE UserId = '{ID}';''')
        try:
            Time = self.cursor.fetchone()[0]
            # 判断时间时间否更新
            if not TimeJudgement(str(Time)):
                _ = TimeJudgement(str(Time))
            else:
                _, Time = TimeJudgement(str(Time))
            if _:
                # 查到了，开始更新数据
                self.cursor.execute(
                    f'''SELECT DayQuantity, Favorability, LuckQuantity, Inauspicious, LuckValue  FROM UserLib WHERE UserId = '{ID}';''')
                DayQuantity, Favorability, LuckQuantity, Inauspicious, LuckValue = self.cursor.fetchone()
                DayQuantity += 1
                Favorability += 5
                Value = IfLuck(ImageName)  # 判断吉凶
                LuckValue += Value
                if Value > 0:
                    LuckQuantity += 1
                else:
                    Inauspicious += 1

                self.cursor.execute(f'''
                        UPDATE UserLib
                        SET DayQuantity = {DayQuantity},
                         Favorability = {Favorability},
                         LuckQuantity = {LuckQuantity},
                         Inauspicious = {Inauspicious},
                         LuckValue = {LuckValue},
                         Time = {Time}
                        WHERE UserID = '{ID}';
                                 ''')
                # 更新完了提交保存
                self.conn.commit()
                self.cursor.close()
                return True
            else:
                # "已经签到过了 返回个False赶紧润"
                return False
        except TypeError:
            self.InIt(ID=ID, ImageName=ImageName)

    async def ChangeName(self, ID, Name):
        """ 修改名字的方法"""

        self.cursor.execute(f'''SELECT * FROM UserLib WHERE UserId = '{ID}';''')
        if self.cursor.fetchone() is None:
            return False

        self.cursor.execute(f'''
                UPDATE UserLib
                SET Username = '{Name}'
                WHERE UserID = '{ID}';
                         ''')

        self.conn.commit()
        self.conn.close()
        # 究竟哪种断开才对呢。。。

        return True


    async def UserInfoImage(self, ID):

        self.cursor.execute(
            f'''SELECT Username, DayQuantity, Favorability, LuckQuantity,
             Inauspicious, LuckValue FROM UserLib WHERE UserId = '{ID}';''')
        Username, DayQuantity, Favorability, LuckQuantity, Inauspicious, LuckValue = self.cursor.fetchone()
        self.cursor.close()
        if await TextImage(UserName=Username, DayQuantity=DayQuantity, Favorability=Favorability,
                           LuckQuantity=LuckQuantity, LuckValue=LuckValue, Inauspicious=Inauspicious, ID=ID):
            return True

查看评论

1. 一、为什么要使用Sqlite
2. 二、了解它
3. What Is SQLite?
4. 三、最基本的使用
5. 四、构造数据库类