T O P

[资源分享]     Python中内置数据库!SQLite使用指南! ⛵

  • By - 楼主

  • 2022-12-02 20:04:25
  • :bulb: 作者:韩信子@ShowMeAI
    :blue_book: Python3◉技能提升系列https://www.showmeai.tech/tutorials/56
    :blue_book: 本文地址https://www.showmeai.tech/article-detail/390
    :loudspeaker: 声明:版权所有,转载请联系平台与作者并注明出处
    :loudspeaker: 收藏ShowMeAI查看更多精彩内容

    Python 是一个广泛使用的编程语言,在各个领域都能发挥很大的作用,而且安装 Python 环境的同时,我们也安装了很多其他出色的工具,其中当然少不了数据库。

    Python 内置了 SQLite3,在 Python 中使用 SQLite,不需要安装任何东西,可以直接使用。我们只需要导入内置 Python 库sqlite3就可以开始使用这个数据库啦!

    在本篇内容中,ShowMeAI将带大家一起来了解,如何基于 Python 环境连接到数据库、创建表、插入数据,查询数据,以及与 Pandas 工具库搭配使用。

    对于 SQL 更详尽的内容,欢迎大家查阅ShowMeAI制作的速查表:

    :blue_book: 编程语言速查表 | SQL 速查表

    :bulb: 连接数据库

    要使用数据库,我们需要先连接数据库。在 Python 中很简单,我们只需导入sqlite3工具库并使用.connect函数,函数的参数是数据库名称,在本例中为students.db

    # 导入工具库
    import sqlite3
    # 建立连接
    conn = sqlite3.connect('students.db')
    

    我们第1次运行上面代码的话,会在工作目录中创建一个名为“students.db”的新文件。

    :bulb: 创建表

    接下来我们可以在连接的数据库中创建一个表,并将数据插入其中。

    在创建表之前,我们需要创建一个游标 cursor(用于建立连接以执行 SQL 查询的对象),我们将使用它来创建表、插入数据等。具体的操作如下代码:

    c = conn.cursor()
    

    完成游标创建后,我们可以使用 .execute方法执行SQL语句,在我们的数据库中创建一个新表。在引号内,我们编写了建表 SQL 语句,使用CREATE TABLE语句:

    c.execute("""CREATE TABLE students (
    name TEXT,
    age INTEGER,
    height REAL
    )""")
    

    我们在创建表的字段时,需要定义数据类型。SQLite 只有 5 种数据类型:

    • Null:缺失值
    • INTEGER:没有小数点的数字(例如,1、2、3、4)
    • REAL:带小数点的数字(例如,6.2、7.6、11.2)
    • TEXT:任何字符数据
    • Blob:二进制数据的集合,作为值存储在数据库中。它允许我们在数据库中存储文档、图像和其他多媒体文件。

    我们要提交上述语句,并关闭连接。截止目前的完整代码如下:

    # 导入工具库
    import sqlite3
    
    # 创建连接
    conn = sqlite3.connect('students.db')
    
    # 游标
    c = conn.cursor()  
    
    # 建表语句
    c.execute("""CREATE TABLE students (
                name TEXT,
                age INTEGER,
                height REAL
        )""")
    
    # 执行
    conn.commit()
    
    # 关闭连接
    conn.close()
    

    :bulb: 插入数据

    我们可以使用.execute执行INSERT INTO语句在“students”表中插入一行数据。下面是添加一个20 岁,身高 1.9 米的学生mark的代码:

    c.execute("INSERT INTO students VALUES ('mark', 20, 1.9)")
    

    我们也可以一次插入多行,换成.executemany方法即可。不过注意一下,我们在INSERT语句中会使用?作为占位符。代码如下所示:

    all_students = [
    ('john', 21, 1.8),
    ('david', 35, 1.7),
    ('michael', 19, 1.83),
    ]
    c.executemany("INSERT INTO students VALUES (?, ?, ?)", all_students)
    

    :bulb: 查询数据

    我们可以使用SELECT语句查看我们的数据,注意一下如果要获取数据并输出,需要执行.fetchall方法:

    c.execute("SELECT * FROM students")
    print(c.fetchall())
    

    打印的输出如下:

    [(‘mark’, 20, 1.9), (‘john’, 21, 1.8), (‘david’, 35, 1.7), (‘michael’, 19, 1.83)]
    

    当然,大家其实可以配合一些在线工具来完成数据的直观查询,例如 :blue_book:SQLiteViewer。我们只需拖动前面 Python 代码生成的 .db 数据库文件进去,即可查看其内容。

    截止目前为止的所有代码如下

    # 导入工具库
    import sqlite3
    
    # 创建连接
    conn = sqlite3.connect('students.db')
    
    # 游标
    c = conn.cursor()  
    
    # 建表语句
    c.execute("""CREATE TABLE students (
                name TEXT,
                age INTEGER,
                height REAL
        )""")
    
    # 插入单条数据
    c.execute("INSERT INTO students VALUES ('mark', 20, 1.9)")
    
    
    # 插入多条数据
    all_students = [
        ('john', 21, 1.8),
        ('david', 35, 1.7),
        ('michael', 19, 1.83),
    ]
    c.executemany("INSERT INTO students VALUES (?, ?, ?)", all_students)
    
    # 查询数据
    c.execute("SELECT * FROM students")
    print(c.fetchall())
    
    # 执行
    conn.commit()
    
    # 关闭连接
    conn.close()
    

    其实大家在SQL中的更高级的复杂查询,都可以通过上述方式进行查询和交互

    :bulb: SQLite 配合 Pandas 应用

    SQLite 可以与 Pandas 中的Dataframe搭配使用。

    例如,我们有一个名为:trophy:population_total.csv的 csv 文件,大家可以通过 ShowMeAI 的百度网盘地址下载。

    :trophy: 实战数据集下载(百度网盘):公✦众✦号『ShowMeAI研究中心』回复『实战』,或者点击 这里 获取本文 [61]Python内置数据库SQLite使用指南SQLite示例数据集

    :star: ShowMeAI官方GitHubhttps://github.com/ShowMeAI-Hub

    我们可以方便地使用 Pandas 读取它:

    import pandas as pd
    df = pd.read_csv("population_total.csv")
    

    Dataframe 内容如下所示:

    >>> df             country year    population0             China  2020.0  1.439324e+09
    1             China  2019.0  1.433784e+09
    2             China  2018.0  1.427648e+09
    3             China  2017.0  1.421022e+09
    4             China  2016.0  1.414049e+09
    ...             ...     ...           ...
    4180  United States  1965.0  1.997337e+08
    4181  United States  1960.0  1.867206e+08
    4182  United States  1955.0  1.716853e+08
    4183          India  1960.0  4.505477e+08
    4184          India  1955.0  4.098806e+08
    

    我们可以把 pandas Dataframe 形态的数据一次性导入 SQLite 数据库中,这里我们需要借助 sqlalchemy 工具库(可以通过pip install sqlalchemy轻松安装)

    from sqlalchemy import create_engine
    engine = create_engine('sqlite://', echo=False)
    

    下面我们就可以轻松把数据导入数据库并创建 population 表:

    df.to_sql("population", con=engine)
    

    查询数据表的语句如下:

    engine.execute("SELECT * FROM population").fetchall()
    

    如果你想创建表的同时生成一个 sqlite 文件(前面的操作,生成的是内存数据库),可以如下方式操作。(我们创建了一个mydb.db文件作为数据库的实体文件)。

    from sqlalchemy import create_engine
    engine = create_engine("sqlite:///mydb.db")
    df.to_sql("population", engine)
    

    这个mydb.db就和前面的使用方式一样啦,我们也可以使用 SQLite 查看器查看数据内容。

    :bulb: 总结

    以上就是ShowMeAI带大家简单了解python的内置数据库SQLite的使用方法,我们可以很方便地完成建表、插入数据、查询数据,也可以配合pandas进行灵活使用,大家快快用起来吧!

    参考资料

    推荐阅读

    Image

    本帖子中包含资源

    您需要 登录 才可以下载,没有帐号?立即注册