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()
