# 导入sqlite包
import sqlite3
# 创建路径
file = r'D:/testDB.db'
# 打开连接
conn =sqlite3.connect(file)
# 修改查询格式
def dict_factory(c,row):
'''使用工厂模式修改查询格式'''
d = {}
for idx, col in enumerate(c.description):
d[col[0]] = row[idx]
return d
conn.row_factory = dict_factory
# 创建游标
c = conn.cursor()
# 删除表(不需要提交)
sql = '''
DROP TABLE IF EXISTS tb_test
'''
c.execute(sql)
# 创建表
sql = '''
CREATE TABLE IF NOT EXISTS tb_test(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(20) NOT NULL,
remark TEXT
)
'''
# 执行查询语句
c.execute(sql)
# 批量执行添加数据
sql = '''
INSERT INTO tb_test(name, remark)
VALUES(?, ?)
'''
# 添加单行
student = ('李斯', '统一文字-小篆')
c.execute(sql, student)
# 添加多行
stuents = [
('李斯', '统一文字-小篆'),
('唐三', '斗罗大陆'),
('王小二', '中国惊奇先生'),
('python', '人生苦短'),
('java', '语法冗长,强制面向对象'),
('zhangsan', '哇哈哈哈')
]
c.executemany(sql, stuents)
# 提交
conn.commit()
# 更新(update)需要提交
sql = '''
UPDATE tb_test
SET name = ?
WHERE name = ?
'''
params = ('张三丰', 'zhangsan')
rc = c.execute(sql, params).rowcount
print('更新了的行数:', rc)
conn.commit() # 提交更改
# 删除数据
sql = '''
DELETE FROM tb_test
WHERE id > 4
'''
rc = c.execute(sql).rowcount
print('删除了的数据条数:', rc)
# 回滚 取消操作
# conn.rollback() # 取消更改
# 查询
sql = '''SELECT *
FROM tb_test'''
c.execute(sql)
# 查询单条数据
result_one = c.fetchone()
print(result_one)
# 查询多条(可指定查询条数)数据
result_many = c.fetchmany(2)
print(result_many)
# 查询全部数据
result_all = c.fetchall()
print(result_all)
# 关闭
c.close()
conn.close()