Python数据库编程

Python操作SQLite数据库实现添加

添加单条数据

c.execute("insert into question values(null,?,?,?,?,?,?)", ("墨菲定律", "aa", "bb", "cc", "dd", "A"))

添加多条数据: 方式1

for i in range(15):
    c.execute("insert into question values(null,?,?,?,?,?,?)", ("墨菲定律", "aa", "bb", "cc", "dd", "A"))

添加多条数据: 方式2

c.executemany("insert into question values(null,?,?,?,?,?,?)", (
    ("圣墟1", "aa", "bb", "cc", "dd", "B"),
    ("圣墟2", "aa", "bb", "cc", "dd", "C"),
    ("圣墟3", "aa", "bb", "cc", "dd", "B"),
    ("圣墟4", "aa", "bb", "cc", "dd", "B"),
    ("圣墟5", "aa", "bb", "cc", "dd", "B")
))

Python操作SQLite数据库实现修改

# 修改单条

c.execute("update question set answer = ? where _id = ?", ("D", 13))

# 修改多条

c.executemany("update question set answer = ? where _id = ?", (
    ("W", 2),
    ("WW", 3),
    ("GG", 4)
))

Python操作SQLite数据库实现删除

# 删除单条

c.execute("delete from question where _id = ?", (15,))

删除多条

c.executemany("delete from question where _id = ?", (
    (11,),
    (12,)
))

## Python操作SQLite数据库实现查询

# 查询
c.execute("select * from question")

# 拿结果
# 1.拿第一条
# print(c.fetchone()[1])

# 2.拿多条
# for q in c.fetchmany(5):
#     print(q)

# 2.拿所有
# for q in c.fetchall():
#     print(q)


# 提交事物
conn.commit()
# 关闭游标
c.close()
# 关闭连接
conn.close()

Python操作SQLite数据库特殊方法

# 其他方法
c.executescript("""
insert into question values(null, "墨菲定律aaa", "aa", "bb", "cc", "dd", "A");
update question set question = "墨菲定律aaa" where _id = 17;
""")

# 定义函数

def reverseText(str):
    return str[::-1]

# 创建自定义函数

conn.create_function("rt", 1, reverseText)

c.execute("insert into question values(null,rt(?),?,?,?,?,?)", ("墨菲定律aaa", "aa", "bb", "cc", "dd", "A"))