Python操作SQLite数据库

SQLite 是一种轻量级的数据库引擎，广泛用于嵌入式设备和小型应用程序。在Python中，SQLite 是一个流行的选择，因为它易于使用、快速、可靠，并且无需独立的服务器进程。本文将深入探讨如何使用 Python 操作 SQLite 数据库，包括创建数据库、表格、插入数据、查询数据等。

主要优点是sqlite在Python中使用通常是以文件的形式存在， 这减少了很多配置工作，主要用于小型项目的数据上传保存。

安装Sqlite3

在 Python 中，SQLite3 已经内置，无需额外安装。可以直接 import sqlite3 来使用它。

import sqlite3

连接数据库

使用 connect() 函数连接到数据库，并返回一个 Connection 对象。

conn = sqlite3.connect('example.db')

同样支持指定路径

# 指定路径为 /path/to/your/database/example.db
conn = sqlite3.connect('/path/to/your/database/example.db')

创建表格

建表（如果已存在则报错）

# 建表（如果已存在则报错）
create_table_sql = """
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);
"""

conn.execute(create_table_sql)

建表（如果已存在则不做操作） 通常就使用这种方式

# 建表（如果已存在则不做操作）
create_table_sql = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);
"""

conn.execute(create_table_sql)

增删改查

# 插入数据
def insert_user(name, age):
    sql = "INSERT INTO users (name, age) VALUES (?, ?)"
    conn.execute(sql, (name, age))
    conn.commit()


# 删除数据
def delete_user(user_id):
    sql = "DELETE FROM users WHERE id = ?"
    conn.execute(sql, (user_id,))
    conn.commit()


# 更新数据
def update_user(user_id, new_name, new_age):
    sql = "UPDATE users SET name = ?, age = ? WHERE id = ?"
    conn.execute(sql, (new_name, new_age, user_id))
    conn.commit()


# 查询多个数据（返回list）
def fetch_users():
    sql = "SELECT * FROM users"
    return conn.execute(sql).fetchall()

# 查询一个数据
def fetch_users():
    sql = "SELECT * FROM users"
    return conn.execute(sql).fetchone()

# 查询指定数目数据（返回list）
def fetch_users():
    sql = "SELECT * FROM users"
    return conn.execute(sql).fetchmany(10) # 获取10行

综合案例

import json
import sqlite3


def get_connection_and_cursor():
    # 连接到 SQLite 数据库
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    return conn, cursor


def create_table():
    conn, cursor = get_connection_and_cursor()

    # 创建表
    cursor.execute('''CREATE TABLE IF NOT EXISTS output_data 
                      (gid INTEGER PRIMARY KEY, json_data TEXT)''')

    # 提交事务
    conn.commit()

    # 关闭游标
    cursor.close()


def insert_data(json_data):
    conn, cursor = get_connection_and_cursor()

    # 将 JSON 数据转换为字符串
    json_string = json.dumps(json_data)

    # 插入数据到表中
    cursor.execute("INSERT INTO output_data (json_data) VALUES (?)", (json_string,))

    # 提交事务
    conn.commit()

    # 关闭游标
    cursor.close()


def delete_data(gid):
    conn, cursor = get_connection_and_cursor()

    # 删除指定 gid 的数据
    cursor.execute("DELETE FROM output_data WHERE gid=?", (gid,))

    # 提交事务
    conn.commit()

    # 关闭游标
    cursor.close()


def update_data(gid, json_data):
    conn, cursor = get_connection_and_cursor()

    # 将 JSON 数据转换为字符串
    json_string = json.dumps(json_data)

    # 更新指定 gid 的数据
    cursor.execute("UPDATE output_data SET json_data=? WHERE gid=?", (json_string, gid))

    # 提交事务
    conn.commit()

    # 关闭游标
    cursor.close()


def retrieve_data(gid):
    conn, cursor = get_connection_and_cursor()

    # 检索指定 gid 的数据
    cursor.execute("SELECT json_data FROM output_data WHERE gid=?", (gid,))
    data = cursor.fetchone()

    # 关闭游标
    cursor.close()

    if data:
        # 将 JSON 字符串转换为对象并返回
        return json.loads(data[0])
    else:
        return None


def find_all_data():
    conn, cursor = get_connection_and_cursor()

    # 检索所有数据
    cursor.execute("SELECT gid, json_data FROM output_data")
    all_data = cursor.fetchall()

    # 关闭游标
    cursor.close()

    data_list = []
    for row in all_data:
        # 将每条记录的 gid 和 JSON 数据字符串转换为对象并添加到列表中
        data_list.append({"gid": row[0], "data": json.loads(row[1])})

    return data_list


# 创建表
create_table()
all_data = find_all_data()
print(all_data)

# 插入数据到表中
insert_data({
    "name": "John",
    "age": 30,
    "city": "New York"
})
insert_data({
    "name": "Jorben",
    "age": 20,
    "city": "Bei Jing"
})
insert_data({
    "name": "d0ublecl1ck",
    "age": 18,
    "city": "Hang Zhou"
})
all_data = find_all_data()
print(all_data)
# 删除指定 gid 的数据
delete_data(1)
all_data = find_all_data()
print(all_data)
# 更新指定 gid 的数据
update_data(2, {"name": "Alice", "age": 25, "city": "Los Angeles"})
all_data = find_all_data()
print(all_data)
# 检索指定 gid 的数据
retrieved_data = retrieve_data(2)
print(retrieved_data) 

# 检索所有数据
all_data = find_all_data()
print(all_data)

[]
[{'gid': 1, 'data': {'name': 'John', 'age': 30, 'city': 'New York'}}, {'gid': 2, 'data': {'name': 'Jorben', 'age': 20, 'city': 'Bei Jing'}}, {'gid': 3, 'data': {'name': 'd0ublecl1ck', 'age': 18, 'city': 'Hang Zhou'}}]
[{'gid': 2, 'data': {'name': 'Jorben', 'age': 20, 'city': 'Bei Jing'}}, {'gid': 3, 'data': {'name': 'd0ublecl1ck', 'age': 18, 'city': 'Hang Zhou'}}]
[{'gid': 2, 'data': {'name': 'Alice', 'age': 25, 'city': 'Los Angeles'}}, {'gid': 3, 'data': {'name': 'd0ublecl1ck', 'age': 18, 'city': 'Hang Zhou'}}]
{'name': 'Alice', 'age': 25, 'city': 'Los Angeles'}
[{'gid': 2, 'data': {'name': 'Alice', 'age': 25, 'city': 'Los Angeles'}}, {'gid': 3, 'data': {'name': 'd0ublecl1ck', 'age': 18, 'city': 'Hang Zhou'}}]