在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.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

 

执行多条语句

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()
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.

注意，在使用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()
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.

异步方式:

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())
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.

 

 

更多使用: