python - 操作sqlite

1. 连接数据库和创建游标

import sqlite3
conn = sqlite3.connect("test.db")
cur = conn.cursor()

2. 建表

sql = "CREATE TABLE test_table(id INTEGER PRIMARY KEY,name TEXT,age INTEGER);"
cur.execute(sql)

3. 查询语句

cur.execute("SELECT * FROM test_table")
# 查询第一条数据
print(cur.fetchone())
# 查询多条数据
print(cur.fetchmany(3))
# 查询所有数据
print(cur.fetchall())

4. 插入数据

# 单条数据
data = "1, '张三', 22"
cur.execute(f"INSERT INTO test_table VALUES({data})")
# 多条数据
cur.executemany("INSERT INTO test_table VALUES(?,?,?)", [(2,"李四",66),(3,"王五",23)])
# 结果 [(1, '张三', 22), (2, '李四', 66), (3, '王五', 23)]

5. 更新数据

cur.execute("UPDATE test_table SET age = 3 WHERE id = 2")
# 结果 [(1, '张三', 22), (2, '李四', 3), (3, '王五', 23)]

cur.execute("UPDATE test_table SET age = ? WHERE id = ?", (44,2))
# 结果 [(1, '张三', 22), (2, '李四', 44), (3, '王五', 23)]

6. 删除数据

cur.execute("DELETE FROM test_table WHERE id = 2")
# 结果 [(1, '张三', 22), (3, '王五', 23)]

cur.execute("DELETE FROM test_table WHERE id = ?", (1,))
# 结果 [(3, '王五', 23)]

7. 事务提交和回滚

# 提交
conn.commit()
# 回滚
conn.rollback()

8. 断开连接

# 关闭游标
cur.close()
# 断开数据库连接
cunn.close()