【100天精通python】Day33:使用python操作数据库_SQLite数据库的使用与实战

简介: 【100天精通python】Day33:使用python操作数据库_SQLite数据库的使用与实战

专栏导读

专栏订阅地址:https://blog.csdn.net/qq_35831906/category_12375510.html

1 SQLite 简介

       SQLite 是一种嵌入式关系型数据库管理系统(RDBMS),以其轻量、高效和易用而闻名。它是一个自包含的、无服务器的数据库引擎,将整个数据库存储在一个单一的文件中。SQLite 不需要独立的服务器进程,而是直接在应用程序中嵌入,因此适用于嵌入式系统、移动应用和桌面应用等多种环境。

以下是 SQLite 的一些重要特点和优势:

  1. 轻量和快速:SQLite 是一个轻量级的数据库引擎,不占用太多内存或资源。它在访问和查询数据时非常快速,特别适合小型项目和移动应用。
  2. 零配置:与其他数据库不同,SQLite 不需要独立的服务器进程,无需复杂的配置。只需一个文件,你就可以开始存储和查询数据。
  3. 自包含:SQLite 数据库存储在单个文件中,这使得备份、迁移和共享数据库变得非常简单。
  4. 无需专门管理:由于 SQLite 是嵌入式的,它不需要独立的数据库管理员(DBA)来管理和维护,减轻了数据库管理的负担。
  5. 支持多种编程语言:SQLite 支持多种编程语言,包括 C/C++、Python、Java、C#、PHP 等,使开发者可以在自己熟悉的语言中使用。
  6. 事务支持:SQLite 支持事务处理,确保数据的一致性和完整性。你可以使用事务来执行一组操作,要么全部成功,要么全部回滚。
  7. 开源和跨平台:SQLite 是开源的,可以在不同的操作系统上运行,包括 Windows、macOS、Linux 等。

尽管 SQLite 适用于许多场景,但它并不适合大规模、高并发的应用,因为它不支持多用户同时写入(写入锁定)等复杂的数据库操作。然而,在许多轻量级和中小规模项目中,SQLite 提供了一种简单、快速、易用的数据库解决方案。

2 SQLite数据库安装及使用

       SQLite 是一个嵌入式数据库,通常无需单独安装,因为它已经作为 Python 的标准库的一部分。这意味着你可以在 Python 中直接使用 SQLite,而不需要安装额外的软件。以下是如何使用 Python 中的 SQLite:

2.1 检查 SQLite 支持

        大多数情况下,Python 已经默认包含了 SQLite 支持。你可以在 Python 交互式环境中运行以下代码,检查是否支持 SQLite:

import sqlite3
print(sqlite3.sqlite_version)

输出如下,如果输出显示了 SQLite 版本号,则说明你的 Python 支持 SQLite。

 2.2 创建数据库文件      

       在 SQLite 中,数据库文件会在第一次连接数据库时自动创建,如果文件不存在的话。你无需手动创建数据库文件,而是在连接时指定数据库文件的名称,如果该文件不存在,SQLite 将会创建一个新的数据库文件。

2.2.1 使用 Python 创建数据库文件

你可以使用以下代码来创建一个名为 mydatabase.db 的数据库文件并连接到它:

import sqlite3
# 连接到 SQLite 数据库(如果不存在则会创建)
conn = sqlite3.connect('mydatabase.db')
# 关闭连接
conn.close()

    在这个示例中,sqlite3.connect() 函数的参数是数据库文件的名称,即 'mydatabase.db'。如果该文件不存在,SQLite 会自动创建一个新的数据库文件。

       请注意,数据库文件将会在与你的 Python 脚本相同的目录下创建。如果你希望将数据库文件保存在特定的目录中,你需要提供完整的路径。在连接到数据库时,SQLite 将检查指定的路径是否存在,如果不存在则会创建相应的数据库文件。

       总之,你不需要手动创建 SQLite 数据库文件,只需要在连接时指定数据库文件的名称或路径即可。

2.2.2 使用命令行创建数据库文件

       如果你想在命令行中创建数据库文件,你可以使用 SQLite 的命令行工具。打开终端或命令行窗口,然后执行以下命令:

sqlite3 mydatabase.db

        这将会打开一个 SQLite 命令行会话,同时创建了一个名为 mydatabase.db 的数据库文件。在 SQLite 命令行会话中,你可以执行 SQL 语句来创建表、插入数据等操作。

       无论你是使用 Python 还是命令行,只要连接到一个不存在的数据库文件,SQLite 都会自动创建该数据库文件。

2.3 连接到 SQLite 数据库

        你可以使用 sqlite3.connect() 函数连接到 SQLite 数据库文件。如果指定的数据库文件不存在,它会被创建。

import sqlite3
# 连接到 SQLite 数据库(如果不存在则会创建)
conn = sqlite3.connect('mydatabase.db')

3 SQLite 中常用的SQL语句

3.1 SQL语句概述

       SQL(Structured Query Language)是一种用于管理关系型数据库的标准化查询语言。它用于执行各种数据库操作,包括数据的查询、插入、更新、删除以及定义数据库结构等。以下是 常用的SQL 语句:

DDL(Data Definition Language): 数据定义语言用于定义和管理数据库的结构,包括表、列、索引等。

  • CREATE TABLE:创建数据表。
  • ALTER TABLE:修改数据表结构。
  • DROP TABLE:删除数据表。
  • CREATE INDEX:创建索引。

DML(Data Manipulation Language): 数据操作语言用于执行数据的增删改查操作。

  • SELECT:查询数据。
  • INSERT INTO:插入数据。
  • UPDATE:更新数据。
  • DELETE FROM:删除数据。

DQL(Data Query Language): 数据查询语言用于查询和检索数据。

  • SELECT:用于从数据库中检索数据。
3.2 SQLite 中常用的SQL 语句

SQLite 支持标准的 SQL 语法,以下是一些在 SQLite 中常用的 SQL 语句示例:

(1)创建表

使用 CREATE TABLE 语句创建数据表。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER
);
(2)插入数据

使用 INSERT INTO 语句插入数据到数据表中。

INSERT INTO users (username, email, age)
VALUES ('user1', 'user1@example.com', 25);
(3)查询数据

使用 SELECT 语句查询数据。

SELECT * FROM users;
(4) 更新数据

使用 UPDATE 语句更新数据。

UPDATE users
SET age = 30
WHERE username = 'user1';
(5) 删除数据

使用 DELETE FROM 语句删除数据。

DELETE FROM users
WHERE username = 'user1';
(6)过滤数据

使用 WHERE 子句来过滤数据。

SELECT * FROM users
WHERE age > 25;
(7)排序数据

使用 ORDER BY 子句来排序数据。

SELECT * FROM users
ORDER BY age DESC;
(8)计算统计

使用聚合函数(如 COUNTSUMAVG)来计算统计信息。

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
(9)连接表

使用 JOIN 语句连接多个数据表。

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
(10)分组数据

使用 GROUP BY 子句分组数据。

SELECT gender, AVG(age) FROM users
GROUP BY gender;

4 操作SQLite

       操作 SQLite 数据库涉及创建连接、创建游标、执行 SQL 语句、处理数据等步骤。以下是一个简单的示例,演示了如何使用 Python 的 sqlite3 模块来操作 SQLite 数据库:

import sqlite3
# 连接到 SQLite 数据库(如果不存在则会创建)
conn = sqlite3.connect('mydatabase.db')
# 创建游标对象
cursor = conn.cursor()
# 创建数据表
create_table_query = '''
CREATE TABLE IF NOT EXISTS books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT,
    price REAL
)
'''
cursor.execute(create_table_query)
# 插入数据
insert_query = 'INSERT INTO books (title, author, price) VALUES (?, ?, ?)'
data = [('Book A', 'Author A', 19.99),
        ('Book B', 'Author B', 29.99),
        ('Book C', 'Author C', 9.99)]
cursor.executemany(insert_query, data)
# 提交事务
conn.commit()
# 查询数据
select_query = 'SELECT * FROM books'
cursor.execute(select_query)
rows = cursor.fetchall()
for row in rows:
    print(row)
# 关闭游标和连接
cursor.close()
conn.close()

输出

在这个示例中,我们首先创建了一个数据库连接,然后创建了一个游标对象。接着,我们执行 SQL 语句来创建数据表、插入数据和查询数据。最后,我们提交事务并关闭游标和连接。

5 SQLite 实战

       SQLite 在许多轻量级和中小型项目中都有很好的应用。

5.1 实战1   创建一个简单的任务管理应用    

        以下是一个 SQLite 实战示例,展示了如何创建一个简单的任务管理应用,其中包括创建任务、列出任务、标记任务为完成等功能。

import sqlite3
# 连接到 SQLite 数据库(如果不存在则会创建)
conn = sqlite3.connect('task_manager.db')
# 创建游标对象
cursor = conn.cursor()
# 创建数据表
create_table_query = '''
CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    completed BOOLEAN
)
'''
cursor.execute(create_table_query)
def create_task(title, description):
    insert_query = 'INSERT INTO tasks (title, description, completed) VALUES (?, ?, ?)'
    cursor.execute(insert_query, (title, description, False))
    conn.commit()
    print("Task created successfully.")
def list_tasks():
    select_query = 'SELECT * FROM tasks'
    cursor.execute(select_query)
    tasks = cursor.fetchall()
    for task in tasks:
        print(f"Task ID: {task[0]}, Title: {task[1]}, Completed: {task[3]}")
def mark_task_completed(task_id):
    update_query = 'UPDATE tasks SET completed = ? WHERE id = ?'
    cursor.execute(update_query, (True, task_id))
    conn.commit()
    print("Task marked as completed.")
# 创建任务
create_task("Buy groceries", "Get milk, eggs, and bread")
# 列出任务
list_tasks()
# 标记任务为完成
task_id_to_complete = 1
mark_task_completed(task_id_to_complete)
# 列出更新后的任务列表
list_tasks()
# 关闭游标和连接
cursor.close()
conn.close()

输出:

        在这个示例中,我们创建了一个 tasks 数据表,用于存储任务的信息。我们实现了三个基本功能:创建任务、列出任务和标记任务为完成。

       确保你已经安装了 sqlite3 模块,通常它已经作为 Python 的标准库的一部分。在实际应用中,你可能还需要进行更多的错误处理、用户界面设计等,以满足实际需求。

5.2 实战2  创建一个任务管理应用,扩展编辑和删除任务

       当涉及编辑和删除任务时,我们可以添加相应的功能来实现这些操作。以下是一个扩展了编辑和删除任务功能的 SQLite 实战示例:

import sqlite3
# 连接到 SQLite 数据库(如果不存在则会创建)
conn = sqlite3.connect('task_manager.db')
# 创建游标对象
cursor = conn.cursor()
# 创建数据表
create_table_query = '''
CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    completed BOOLEAN
)
'''
cursor.execute(create_table_query)
def create_task(title, description):
    insert_query = 'INSERT INTO tasks (title, description, completed) VALUES (?, ?, ?)'
    cursor.execute(insert_query, (title, description, False))
    conn.commit()
    print("Task created successfully.")
def list_tasks():
    select_query = 'SELECT * FROM tasks'
    cursor.execute(select_query)
    tasks = cursor.fetchall()
    for task in tasks:
        print(f"Task ID: {task[0]}, Title: {task[1]}, Completed: {task[3]}")
def mark_task_completed(task_id):
    update_query = 'UPDATE tasks SET completed = ? WHERE id = ?'
    cursor.execute(update_query, (True, task_id))
    conn.commit()
    print("Task marked as completed.")
def edit_task(task_id, new_title, new_description):
    update_query = 'UPDATE tasks SET title = ?, description = ? WHERE id = ?'
    cursor.execute(update_query, (new_title, new_description, task_id))
    conn.commit()
    print("Task edited successfully.")
def delete_task(task_id):
    delete_query = 'DELETE FROM tasks WHERE id = ?'
    cursor.execute(delete_query, (task_id,))
    conn.commit()
    print("Task deleted successfully.")
# 创建任务
create_task("Buy groceries", "Get milk, eggs, and bread")
# 编辑任务
edit_task(1, "Buy groceries", "Get milk, eggs, bread, and fruits")
# 列出任务
list_tasks()
# 删除任务
delete_task(1)
# 列出更新后的任务列表
list_tasks()
# 关闭游标和连接
cursor.close()
conn.close()
5.3 实战3  实现用户数据增删查看修改等

       以下是一个使用 SQLite 数据库的案例,展示了如何实现新增用户数据、查看用户数据、修改用户数据和删除用户数据等操作:

import sqlite3
# 连接到 SQLite 数据库(如果不存在则会创建)
conn = sqlite3.connect('user_database.db')
# 创建游标对象
cursor = conn.cursor()
# 创建数据表
create_table_query = '''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL,
    age INTEGER
)
'''
cursor.execute(create_table_query)
def create_user(username, email, age):
    insert_query = 'INSERT INTO users (username, email, age) VALUES (?, ?, ?)'
    cursor.execute(insert_query, (username, email, age))
    conn.commit()
    print("User created successfully.")
def list_users():
    select_query = 'SELECT * FROM users'
    cursor.execute(select_query)
    users = cursor.fetchall()
    for user in users:
        print(f"User ID: {user[0]}, Username: {user[1]}, Email: {user[2]}, Age: {user[3]}")
def edit_user(user_id, new_username, new_email, new_age):
    update_query = 'UPDATE users SET username = ?, email = ?, age = ? WHERE id = ?'
    cursor.execute(update_query, (new_username, new_email, new_age, user_id))
    conn.commit()
    print("User information edited successfully.")
def delete_user(user_id):
    delete_query = 'DELETE FROM users WHERE id = ?'
    cursor.execute(delete_query, (user_id,))
    conn.commit()
    print("User deleted successfully.")
# 创建用户
create_user("user1", "user1@example.com", 25)
# 查看用户列表
list_users()
# 修改用户信息
edit_user(1, "updated_user1", "updated@example.com", 30)
# 查看更新后的用户列表
list_users()
# 删除用户
delete_user(1)
# 查看删除后的用户列表
list_users()
# 关闭游标和连接
cursor.close()
conn.close()

输出:

在这个案例中,我们创建了一个 users 数据表,用于存储用户的信息。我们实现了四个基本功能:创建用户、查看用户列表、编辑用户信息和删除用户。这些功能的实现都依赖于 SQL 的 INSERTSELECTUPDATEDELETE 语句。

目录
相关文章
|
1天前
|
存储 Go 索引
牢记python对象的操作方式
【6月更文挑战第20天】在Python中,`hash()`和`is`帮助确定对象的相等性。`dir()`和`vars()`揭示对象的属性和内部表示,`__slots__`优化内存使用。列表和字典结构有不同的内存和性能特性,字典使用哈希表进行快速访问。
15 5
牢记python对象的操作方式
|
5天前
|
数据采集 数据可视化 数据挖掘
数据挖掘实战:使用Python进行数据分析与可视化
在大数据时代,Python因其强大库支持和易学性成为数据挖掘的首选语言。本文通过一个电商销售数据案例,演示如何使用Python进行数据预处理(如处理缺失值)、分析(如销售额时间趋势)和可视化(如商品类别销售条形图),揭示数据背后的模式。安装`pandas`, `numpy`, `matplotlib`, `seaborn`后,可以按照提供的代码步骤,从读取CSV到数据探索,体验Python在数据分析中的威力。这只是数据科学的入门,更多高级技术等待发掘。【6月更文挑战第14天】
42 11
|
5天前
|
数据采集 存储 数据挖掘
Python网络爬虫实战:抓取并分析网页数据
使用Python的`requests`和`BeautifulSoup`,本文演示了一个简单的网络爬虫,抓取天气网站数据并进行分析。步骤包括发送HTTP请求获取HTML,解析HTML提取温度和湿度信息,以及计算平均温度。注意事项涉及遵守robots.txt、控制请求频率及处理动态内容。此基础爬虫展示了数据自动收集和初步分析的基础流程。【6月更文挑战第14天】
72 9
|
2天前
|
Python
Python列表推导式是一种简洁的创建新列表的方式,它允许你在一行代码中完成对数据的操作和转换
【6月更文挑战第19天】Python列表推导式是创建新列表的简洁语法,它在一行内处理数据。表达式如`[expr for item in iterable if cond]`,其中`expr`是对元素的操作,`item`来自`iterable`,`if cond`是可选过滤条件。例如,将数字列表平方:`[x**2 for x in numbers]`。嵌套列表推导处理复杂结构,如合并二维数组:`[[a+b for a,b in zip(row1, row2)] for row1, row2 in zip(matrix1, matrix2)]`。简洁但勿过度复杂化。
11 5
|
1天前
|
Python
高阶函数如`map`, `filter`, `reduce`和`functools.partial`在Python中用于函数操作
【6月更文挑战第20天】高阶函数如`map`, `filter`, `reduce`和`functools.partial`在Python中用于函数操作。装饰器如`@timer`接收或返回函数,用于扩展功能,如记录执行时间。`timer`装饰器通过包裹函数并计算执行间隙展示时间消耗,如`my_function(2)`执行耗时2秒。
11 3
|
2天前
|
Python
Python教程:Python中的输入与输出操作
在编程语言中,输入(Input)和输出(Output),简称I/O,是基础且重要的概念。Python作为一门易于学习且功能强大的编程语言,在处理输入和输出方面提供了多种方式。本文将深入探讨Python中的输入输出操作,包括标准输入输出、文件操作、以及网络I/O等领域
10 4
|
1天前
|
数据库 Android开发 数据安全/隐私保护
在 Android Studio 中结合使用 SQLite 数据库实现简单的注册和登录功能
在 Android Studio 中结合使用 SQLite 数据库实现简单的注册和登录功能
15 2
|
1天前
|
SQL Oracle 关系型数据库
Python连接数据库进行数据查询的操作代码
mysql数据库(mariadb) 连接数据库 首先,你需要使用MySQLdb.connect()函数建立与MySQL数据库的连接。你需要提供数据库服务器的地址(host),用户名(user),密码(passwd),以及你想要操作的数据库名称(db)。 创建Cursor对象 一旦建立了数据库连接,你可以使用连接对象的cursor()方法来创建一个cursor对象。这个方法返回一个cursor实例,你可以使用这个实例来执行SQL查询和命令。
|
3天前
|
Python
【干货】python xlwt写入excel操作
【干货】python xlwt写入excel操作
10 2
|
4天前
|
数据库 数据安全/隐私保护 数据库管理
QT中sqlite数据库数据加密/混淆---MD5/SHA1/SHA2/SHA3
QT中sqlite数据库数据加密/混淆---MD5/SHA1/SHA2/SHA3

热门文章

最新文章