SQLite 数据库学习

0.引言

SQL, 全名是 Structured Query Language, 中文翻译是"结构化查询语言"。它是一种专门用来与数据库交互的语言。简单来说，SQL就是跟数据库"对话"的一种语言。

1.install

sudo apt update
sudo apt install sqlitebrowser

这是一个开源的图形用户界面工具，专门用于开发、管理和分析 SQLite 数据库。它支持创建或导入导出表、编辑数据、执行 SQL 查询等功能。

2.python 操作数据库

在这里插入图片描述

Python 内置了 sqlite3 模块，使得与 SQLite 数据库交互变得简单而直接。

创建连接
首先，导入 sqlite3 模块并建立到数据库的连接。如果指定的数据库文件不存在，SQLite 会自动创建一个新的数据库文件。

import sqlite3
# 建立连接
conn = sqlite3.connect('example.db')
# 创建游标对象
cursor = conn.cursor()

创建表

# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS stocks (
    date TEXT,
    trans TEXT,
    symbol TEXT,
    qty REAL,
    price REAL
)
''')

插入数据
向表中插入数据也很简单，可以使用参数化查询来避免 SQL 注入攻击。

# 单条插入
cursor.execute("INSERT INTO stocks (date, trans, symbol, qty, price) VALUES (?, ?, ?, ?, ?)",
               ('2024-12-10', 'BUY', 'RHAT', 100, 35.14))

# 批量插入
purchases = [
    ('2024-01-05', 'BUY', 'IBM', 1000, 45.0),
    ('2024-06-17', 'SELL', 'MSFT', 500, 20.0),
]
cursor.executemany("INSERT INTO stocks (date, trans, symbol, qty, price) VALUES (?, ?, ?, ?, ?)", purchases)

# 提交事务
conn.commit()

查询数据: 从表中检索数据同样可以通过执行 SQL 查询实现。

# 简单查询
for row in cursor.execute("SELECT * FROM stocks ORDER BY price"):
    print(row)

# 条件查询
cursor.execute("SELECT * FROM stocks WHERE symbol=?", ('RHAT',))
print(cursor.fetchall())

更新和删除数据: 你也可以更新或删除记录。

# 更新
cursor.execute("UPDATE stocks SET qty = ? WHERE symbol = ?", (200, 'RHAT'))
conn.commit()

# 删除
cursor.execute("DELETE FROM stocks WHERE trans = ?", ('SELL',))
conn.commit()

关闭连接: 完成所有操作后，确保关闭游标和连接以释放资源。

cursor.close()
conn.close()

使用上下文管理器:为了简化代码并确保资源被正确清理，可以使用上下文管理器 (with 语句)：

with sqlite3.connect('example.db') as conn:
    with conn:  # 自动提交
        cursor = conn.cursor()
        cursor.execute("INSERT INTO stocks VALUES ('2024-12-10', 'BUY', 'AAPL', 50, 200.1)")

完整python 代码：

import sqlite3

# -------- 连接数据库 --------
# 建立连接
# conn = sqlite3.connect('/mnt/data2/exp_data/1208_sql_learn/example.db')
conn = sqlite3.connect('/mnt/data2/exp_data/1208_sql_learn/example.sq3')

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

# -------- 创建表 --------
cursor.execute('''
CREATE TABLE IF NOT EXISTS stocks (
    date TEXT,
    trans TEXT,
    symbol TEXT,
    qty REAL,
    price REAL
)
''')

# -------- 插入数据 --------
# 单条插入
cursor.execute("INSERT INTO stocks (date, trans, symbol, qty, price) VALUES (?, ?, ?, ?, ?)",
               ('2024-12-10', 'BUY', 'RHAT', 100, 35.14))

# 批量插入
purchases = [
    ('2024-01-05', 'BUY', 'IBM', 1000, 45.0),
    ('2024-06-17', 'SELL', 'MSFT', 500, 20.0),
]
cursor.executemany("INSERT INTO stocks (date, trans, symbol, qty, price) VALUES (?, ?, ?, ?, ?)", purchases)

# 提交事务
conn.commit()

# -------- 查询数据 --------
# 简单查询
for row in cursor.execute("SELECT * FROM stocks ORDER BY price"):
    print(row)

def print_query_result(cursor, query='SELECT * FROM stocks ORDER BY price'):
    for row in cursor.execute(query):
        print(row)

# 条件查询
cursor.execute("SELECT * FROM stocks WHERE symbol=?", ('RHAT',))
print(cursor.fetchall())

# -------- 更新和删除 --------
# 更新
cursor.execute("UPDATE stocks SET qty = ? WHERE symbol = ?", (200, 'RHAT'))
conn.commit()
print_query_result(cursor)
# 删除
cursor.execute("DELETE FROM stocks WHERE trans = ?", ('SELL',))
conn.commit()
print_query_result(cursor)
# -------- 关闭连接 --------
cursor.close()
conn.close()

# # -------- 使用上下文管理器 --------
# with sqlite3.connect('example.db') as conn:
#     with conn:  # 自动提交
#         cursor = conn.cursor()
#         cursor.execute("INSERT INTO stocks VALUES ('2024-12-10', 'BUY', 'AAPL', 50, 200.1)")