Python数据库模块sqlite3操作实例(非常实用)

喜欢ヅ旅行 2023-10-13 12:19 24阅读 0赞

当使用Python进行SQLite数据库操作时，`sqlite3`模块是一个非常常用和强大的工具。它提供了一系列函数和方法，用于创建、连接、查询和管理数据库。下面是一些常见的用法示例：

import sqlite3
    
    #连接到现有数据库Connection个新数据库
    conn = sqlite3.connect('stockssqlite3.db')
    
    # 创建内存中的数据库（不保存到磁盘）
    # conn = sqlite3.connect(':memory:')
    # 获取数据库游标
    cursor = conn.cursor()
    
    # 创建表
    # cursor.execute('''CREATE TABLE stocks2(date text, symbol text, price real)''')
    
    # 插入数据
    # cursor.execute("INSERT INTO stocks2 VALUES ('2023-07-01', 'WL22', 140.34)")
    # cursor.execute("INSERT INTO stocks VALUES ('2023-07-02', 'HengDa', 2515.62)")
    
    # 提交更改
    conn.commit()
    
    # # 更新数据
    cursor.execute("UPDATE stocks SET price =888 WHERE symbol = 'WL'")
    # conn.commit()
    
    # 删除数据
    cursor.execute("DELETE FROM stocks WHERE symbol = 'HengDa'")
    conn.commit()
    
    # 使用参数化查询防止SQL注入
    symbol = 'WL'
    cursor.execute("SELECT * FROM stocks WHERE symbol=?", (symbol,))
    rows2 = cursor.fetchall()
    # 遍历查询结果
    for row in rows2:
        print('WL: ',row)
    
    
    # # 开始事务
    # conn.execute("BEGIN TRANSACTION")
    # # 执行多个操作
    # cursor.execute("INSERT INTO stocks VALUES ('2023-07-03', 'MSFT', 278.21)")
    # cursor.execute("INSERT INTO stocks VALUES ('2023-07-04', 'AMZN', 3606.02)")
    
    # # 提交事务
    # conn.commit()
    
    # 批量插入数据
    # data = [('2023-07-05', 'SanAN', 144.50),
    #         ('2023-07-05', 'ZXGJ', 2540.10),
    #         ('2023-07-05', 'MaoTai', 280.50)]
    
    # cursor.executemany("INSERT INTO stocks VALUES (?, ?, ?)", data)
    # conn.commit()
    
    try:
        # 开始事务
        conn.execute("BEGIN TRANSACTION")
        # 执行一些操作
        cursor.execute("INSERT INTO stocks VALUES ('2023-07-06', 'WuHanD', 148.20)")
        cursor.execute("INSERT INTO stocks VALUES ('2023-07-06', 'GOOGL', 2570.80,'55')")
        # 抛出异常，触发回滚
        raise Exception("Some error occurred")
        # 提交事务
        conn.commit()
    except Exception as e:
        # 发生异常，回滚事务
        conn.rollback()
        print("Transaction rolled back:", str(e))
    
    # # 使用命名参数化查询
    # symbol = 'AAPL'
    # price = 150.25
    # cursor.execute("SELECT * FROM stocks WHERE symbol=:symbol AND price>=:price", {'symbol': symbol, 'price': price})
    
    # 查询数据
    cursor.execute("SELECT * FROM stocks")
    rows = cursor.fetchall()
    # 遍历查询结果 获取所有行数据
    for row in rows:
        print(row)
    
    # 获取部分行数据
    cursor.execute("SELECT * FROM stocks2 LIMIT 5")
    rows = cursor.fetchmany(3)
    for row in rows:
        print('stocks2 fetchmany(3): ',row)
    
    # 获取查询结果的列信息
    cursor.execute("SELECT * FROM stocks")
    columns = [description[0] for description in cursor.description]
    print(columns)
    
    # 创建索引
    # cursor.execute("CREATE INDEX idx_symbol ON stocks (symbol)")
    conn.commit()
    # 执行查询（使用索引）
    cursor.execute("SELECT * FROM stocks WHERE symbol = 'WK'")
    result = cursor.fetchall()
    print(result)
    
    # 创建触发器
    # 该语句指定了触发器的名称update_price
    # 触发的事件AFTER UPDATE OF price ON stocks，以及在触发时要执行的操作。
    # 触发器是强大的数据库功能，可以用于在特定事件发生时自动执行复杂的操作。它们可以用于实现数据完整性约束、记录日志、更新其他表的数据等
    # cursor.execute("""
    #     CREATE TRIGGER update_price
    #     AFTER UPDATE OF price ON stocks
    #     BEGIN
    #         -- 触发时执行的操作
    #         UPDATE stocks2 SET price = 5555 WHERE symbol = 'WL22';
    #     END
    # """)
    # conn.commit()
    
    # # 删除触发器
    # cursor.execute("DROP TRIGGER IF EXISTS update_price")
    
    # # 提交更改
    # conn.commit()
    
    # 查询触发器信息
    cursor.execute("SELECT name, tbl_name, sql FROM sqlite_master WHERE type='trigger'")
    triggers = cursor.fetchall()
    
    # 打印触发器信息
    for trigger in triggers:
        name, tbl_name, sql = trigger
        print(f"Name: {name}")
        print(f"Table: {tbl_name}")
        print(f"SQL: {sql}")
        print("-" * 30)

# 获取数据库版本号
    cursor.execute("SELECT sqlite_version()")
    version = cursor.fetchone()[0]
    print("SQLite version:", version)
    
    # 获取数据库中的表列表
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = cursor.fetchall()
    for table in tables:
        print("Table name:", table[0])
    
    # 获取表的列信息
    cursor.execute("PRAGMA table_info(stocks)")
    columns = cursor.fetchall()
    for column in columns:
        print("Column name:", column[1])
        print("Column type:", column[2])

# 定义用户自定义函数
    def calculate_total(a, b):
        return a + b
    
    # 注册用户自定义函数
    conn.create_function("total", 2, calculate_total)
    
    # 使用用户自定义函数
    cursor.execute("SELECT total(10, 5)")
    result = cursor.fetchone()[0]
    print("Total:", result)

import re
    
    # 创建自定义函数来进行正则表达式匹配
    def regex_match(pattern, string):
        return bool(re.match(pattern, string))
    
    # 注册自定义函数
    conn.create_function("regex_match", 2, regex_match)
    
    # 使用正则表达式进行查询
    cursor.execute("SELECT * FROM my_table WHERE regex_match('^A.*', name)")

import sqlite3
    
    # 连接到数据库
    conn = sqlite3.connect("mydatabase.db")
    cursor = conn.cursor()
    
    # 删除触发器
    cursor.execute("DROP TRIGGER IF EXISTS update_price")
    
    # 提交更改
    conn.commit()
    
    # 关闭连接
    conn.close()

import sqlite3
    
    # 连接到数据库
    conn = sqlite3.connect("mydatabase.db")
    cursor = conn.cursor()
    
    # 查询触发器信息
    cursor.execute("SELECT name, tbl_name, sql FROM sqlite_master WHERE type='trigger'")
    triggers = cursor.fetchall()
    
    # 打印触发器信息
    for trigger in triggers:
        name, tbl_name, sql = trigger
        print(f"Name: {name}")
        print(f"Table: {tbl_name}")
        print(f"SQL: {sql}")
        print("-" * 30)
    
    # 关闭连接
    conn.close()

`sqlite-utils`、`sqlitebiter`和其他类似的库可以提供更高级的功能，包括预编译语句的执行优化和数据库连接池的管理。这些库可以作为`sqlite3`模块的扩展，提供更便捷和高效的SQLite数据库操作。下面是这些库的简要介绍：

1.  `sqlite-utils`：它是一个Python库，提供了许多工具和功能来简化SQLite数据库的操作。它支持创建、连接、查询和管理数据库，还提供了高级功能，如预编译语句的执行优化、数据导入导出、数据库迁移等。你可以使用`sqlite-utils`来执行高级操作，以提高SQLite数据库的性能和功能。
2.  `sqlitebiter`：它是一个命令行工具和Python库，用于将不同的数据源（如CSV、Excel、JSON等）转换为SQLite数据库。它可以自动推断表结构，并将数据导入SQLite数据库中。`sqlitebiter`还提供了选项来优化导入过程，包括使用预编译语句和批量插入等。这使得数据导入过程更快速和高效。