SQLite 与 Python,Python项目中充分利用SQLite的综合指南-Toy模板网

SQLite 是一种轻量级、无服务器、独立的关系数据库管理系统，由于其简单、高效且占用空间紧凑，在许多应用程序中常用。

本综合指南将探讨如何在 Python 项目中充分利用 SQLite 来深入创建、访问、修改和管理数据库信息。

在 Python 中设置 SQLite

要在 Python 中使用 SQLite，我们首先需要导入 sqlite3 模块。这是 Python 内置的：

import sqlite3

sqlite3 模块提供了从 Python 连接、创建 SQLite 数据库并与之交互所需的所有 API 和工具。

要连接到 SQLite 数据库（如果不存在则创建一个），请使用 sqlite3.connect() 方法：

import sqlite3
db = sqlite3.connect('database.db')

这将打开与数据库文件database.db的连接并返回代表数据库的连接对象。如果该文件不存在，SQLite 将自动创建它。

一旦我们完成与数据库的交互，关闭连接是一个很好的做法。这可以通过 close() 方法来完成：

db.close()

创建表

要在 SQLite 中创建表，我们必须执行 CREATE TABLE SQL 语句。这是通过首先从数据库连接获取游标对象来完成的，这使我们能够执行 SQL：

import sqlite3
db = sqlite3.connect('database.db')
cursor = db.cursor()

然后我们可以使用游标的execute()方法执行CREATE TABLE语句：

cursor.execute("""
  CREATE TABLE inventory (
    id integer PRIMARY KEY,
    name text NOT NULL,
    quantity integer,
    price real
  )
""")

这将创建一个包含 ID、名称、数量和价格列的库存表。请注意，我们定义了每列的数据类型。

执行 SQL 后，必须将更改提交到数据库。这会保留更改：

db.commit()

让我们将其放在一个函数中来封装表创建逻辑：

def create_table():
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("""
    CREATE TABLE IF NOT EXISTS inventory (
      id integer PRIMARY KEY,
      name text NOT NULL,
      quantity integer,
      price real
    )
  """)
  db.commit()
  db.close()

我们使用 CREATE TABLE IF NOT EXISTS，因此如果表已经存在，尝试再次创建该表不会导致错误。

插入数据

要将数据插入表中，我们可以再次使用execute()方法执行SQL语句。SQLite 有一个 INSERT INTO 语句，允许插入新行：

INSERT INTO inventory VALUES (1, 'Bananas', 150, 1.49)

这将插入一个新行，id 为 1，名称为 Bananas，数量为 150，价格为 1.49。

虽然我们可以使用字符串格式插入数据，但更好的方法是使用可以安全地将值与查询分开的查询参数：

data = (2, 'Oranges', 200, 2.49)
cursor.execute("INSERT INTO inventory VALUES (?, ?, ?, ?)", data)

他们？充当值的占位符。这有助于防止 SQL 注入攻击。

我们可以编写一个函数来插入数据：

def insert_data(values):
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("INSERT INTO inventory VALUES (?, ?, ?, ?)", values)
  db.commit()
  db.close()
# Usage:
insert_data((3, 'Apples', 75, 1.25))

这封装了插入逻辑，同时允许我们干净地将数据作为元组传递。

查看数据

我们可以使用 SELECT 语句从数据库中查询和获取数据。例如，要获取所有行：

SELECT * FROM inventory

我们可以执行此查询，然后使用cursor.fetchall()以元组形式检索结果集：

def get_all_data():
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("SELECT * FROM inventory")
  rows = cursor.fetchall()
  db.close()
  return rows

这将以元组列表的形式返回完整的结果集，每个元组代表一行及其值。

我们可以将参数传递给 SELECT 查询来过滤结果：

def get_by_name(name):
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("SELECT * FROM inventory WHERE name=?", (name,))
  row = cursor.fetchone()  
  db.close()
  return row

这允许我们按名称检索特定行。cursor.fetchone() 仅返回第一个结果。

更新数据

要修改现有数据，我们可以使用 UPDATE 语句：

UPDATE inventory SET quantity = 200 WHERE name = 'Bananas'

这会将香蕉的数量更新为 200。

我们可以编写一个函数来处理更新逻辑：

def update_data(new_quantity, name):
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("UPDATE inventory SET quantity =? WHERE name=?",
                 (new_quantity, name))
  db.commit()
  db.close()
# Usage:  
update_data(350, 'Bananas')

与插入类似，这使用查询参数安全地传入值。

删除数据

可以使用 DELETE 语句删除行：

DELETE FROM inventory WHERE name = 'Oranges'

这将删除橙色行。

下面是我们如何实现删除功能：

def delete_data(name):
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("DELETE FROM inventory WHERE name=?", (name,))
  db.commit()
  db.close()
# Usage:
delete_data('Oranges')

同样，使用查询参数可以避免漏洞并使代码更易于维护。

使用Sqlite3构建

首先，请在此处克隆存储库并按照教程进行操作。尽管这是初学者课程，但您必须对 SQL 有一点了解才能完成。

第一步是导入 sqlite3 模块：

import sqlite3

这使我们能够访问 Python 中的所有 SQLite 数据库功能。

创建表

接下来，定义 create_table() 函数来创建一个名为 store 的表，其中包含项目、数量和价格列：

def create_table():
  new_data = sqlite3.connect('data_base1')
  change = new_data.cursor()
  change.execute("CREATE TABLE IF NOT EXISTS store (item TEXT, quantity INTEGER, price REAL)")
  new_data.commit()
  new_data.close()

分解一下：

sqlite3.connect() 打开到数据库文件的连接。
new_data.cursor() 获取一个游标对象来执行SQL。
execute() 运行 CREATE TABLE 查询。
commit() 保存更改。
close() 关闭连接。

插入数据

item() 函数处理将数据插入表中：

def item(item, quantity, price):
  new_data = sqlite3.connect('data_base1')
  change = new_data.cursor()
  change.execute("INSERT INTO store VALUES(?,?,?)", (item, quantity, price))
  new_data.commit()
  new_data.close()

它以项目、数量和价格作为参数。INSERT 查询使用占位符 ? 将这些值安全地插入到新行中。

查看数据

为了查询数据，定义了view()函数：

def view():
  new_data = sqlite3.connect('data_base1')
  change = new_data.cursor()
  change.execute("SELECT * FROM store")
  rows = change.fetchall()
  change.close()
  return rows

它使用 SELECT * 选择所有行，使用 fetchall() 获取结果，然后返回它们。

删除数据

要按项目名称删除行，请使用delete()函数：

def delete(item):
  new_data = sqlite3.connect('data_base1')
  change = new_data.cursor()
  change.execute("DELETE FROM store WHERE item =?", (item,))  
  new_data.commit()
  new_data.close()

WHERE 子句通过项目名称标识要删除的行。

更新数据

最后，update() 可以修改给定商品的数量和价格：

def update(quantity, price, item):
  new_data = sqlite3.connect('data_base1')
  change = new_data.cursor()
  change.execute("UPDATE store SET quantity =?, price =? WHERE item=?",
                (quantity, price, item))
  new_data.commit()
  new_data.close()

SET 子句指定要更新的列，WHERE 标识行。