python模块之sqlite3

 

在Python中操作sqlite3

1)基本使用

import sqlite3

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

# 执行单条SQL语句
cursor.execute('SELECT * FROM users')
result = cursor.fetchall()

conn.close()

 

执行多条语句

1)executescript

import sqlite3

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

# 执行多条SQL语句
cursor.executescript('''
    SELECT * FROM users;
    SELECT * FROM orders;
    SELECT * FROM products;
''')

# 获取执行结果
result = cursor.fetchall()

conn.close()

注意，在使用executescript()方法时，多条SQL语句必须以分号（;）进行分隔

2)executemany: 执行一条SQL语句的多个参数组合

import sqlite3

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

# 定义SQL语句
sql = 'INSERT INTO users (name, age) VALUES (?, ?)'

# 定义参数列表
params = [('Alice', 25), ('Bob', 30), ('Charlie', 35)]

# 执行多条SQL语句
cursor.executemany(sql, params)

# 提交事务
conn.commit()

conn.close()

异步方式:

import asyncio
import aiomysql

async def execute_sql(sql):
    conn = await aiomysql.connect(host='localhost', user='root', password='password', db='example')
    cursor = await conn.cursor()

    await cursor.execute(sql)
    result = await cursor.fetchall()

    await cursor.close()
    conn.close()

    return result

async def main():
    sqls = [
        'SELECT * FROM users',
        'SELECT * FROM orders',
        'SELECT * FROM products'
    ]

    tasks = [execute_sql(sql) for sql in sqls]
    results = await asyncio.gather(*tasks)

    for result in results:
        print(result)

loop = asyncio.get_event_loop()
loop.run_until_complete(main())

 

 

更多使用: