第七章:Python之数据库编程_python编写数据库管理系统
第一节:数据库API与全局变量及核心类基本流程
- 数据库API
- python DB API 2.0
- 通过全局变量查看 DB API特性
- 全局变量用于判断该数据库模块所支持的功能，通常有以下3个全局变量
- apilevel：显示数据库模块的API版本号
- threadsafety：指定该数据库模块的线程安全等级
- paramstyle：指定当SQL语句需要参数时，可以使用哪种风格（qmark、numeric、named）的参数
- 核心API
- connect()函数：链接数据库，返回数据库链接
- 数据库链接：用于打开游标，开启或提交事务
- 游标：用于执行SQL语句，获取执行结果
- 操作数据库的流程
第二节：案例实操-动态创建数据表
- 导入sqlite3模块
- python自带了sqlite数据库和sqlite数据库的API模块，无需再安装，如果是导入其他sqlite自身未带有的模块，就需要大家手动去安装了
- 导入sqlite3模块，通过全局变量可了解该模块支持的特性
- 执行DDL创建数据库
- 按照前面的步骤操作SQLite数据库，只要用游标执行DDL语句即可
import sqlite3
# 1 打开数据库链接
# SQLite是一个没有后台进程的数据库，磁盘上的一个文件就可以对应SQLite数据库
conn = sqlite3.connect("test.db")
# 2 打开游标
c = conn.cursor()
# 3 使用游标的execute方法执行任意的SQL语句（DDL）
c.execute('''
craete table user_tb(
_id integer primary key autoincrement,
name text,
pass text,
age interger)
''')
c.execute('''
craete table order_tb(
_id integer primary key autoincrement,
item_name text,
item_price real,
item_number integer,
user_id integer,
foreign key(user_id) references user_tb(_id))
''')
# 4 关闭游标
c.close()
# 5 关闭数据库链接
conn.colse()- SQLite数据库特性
- SQLite 内部只支持NULL 、INTEGER 、REAL(浮点型)、TEXT(文本)和BLOB(大二进制对象)这五种数据类型
- SQLite允许输入数据时忽略底层数据列实际的数据类型，因此在编写建表语句时可以省略数据列后面的类型声明
import sqlite3
# 1 打开数据库链接
# SQLite是一个没有后台进程的数据库，磁盘上的一个文件就可以对应SQLite数据库
conn = sqlite3.connect("test.db")
# 2 打开游标
c = conn.cursor()
# 3 使用游标的execute方法执行任意的SQL语句（DDL）
# 省略数据列后面的类型声明
c.execute('''
craete table user_tb(
_id integer primary key autoincrement,
name,
pass,
age)
'''
)
# 4 关闭游标
c.close()
# 5 关闭数据库链接
conn.colse()第三节：使用SQLite Expert
- 下载安装SQLite EXpert
- 登录http://www.sqliteexpert.com/download.html下载软件
- 安装：跟安装普通软件相同，按照步骤安装即可
- 使用SQLite EXpert创建数据库
- 主界面左上角的第一个和第二个按钮（创建内存中的数据库）都可以创建数据库
- 创建数据库之后就可以创建数据表：选择工具栏中的SQL->New SQL Tab
- 使用SQLite EXpert打开数据库
- 单击主界面工具条上第三个按钮即可打开数据库，打开数据库之后 ，可查看该数据库包含的数据表以及表中包含的数据
第四节：执行DML语句
- 执行DML语句
- 使用游标的execute()方法也可以执行语句的insert、update、delete语句
- SQLite数据 API默认就是开启事务，因此必须提交事务，否则程序对数据所做的修改(包括插入数据、删除数据，整理数据)不会生效
import sqlite3
# 创建数据库
conn = sqlite3.connect("test.db")
# 获取游标
c = conn.cursor()
# 执行SQL语句
# 插入insert into tabname
c.execute('insert into user_tb values(null, ?, ?, ?)', ('fkjava', '33445', 23))
c.execute('insert into user_tb values(null, ?, ?, ?)', ('crazyit', '35555', 25))
c.execute('insert into order_tb values(null, ?, ?, ?, ?)', ('鼠标', 33, 3, 1))
# 更新 update tabname
c.execute('update user_tb set pass=?', ('98765',))
# 执行完DML语句之后，如果程序获取被DML语句修改的记录条数，可通过游标的rowcount来获取
print('受影响的记录条数：' , c.rowcount)
# 提交事务，使修改生效
conn.commit()
# 关闭资源
c.close()
conn.close()- 重复执行多次DML语句
- 使用executemany()方法则可以将同一条DML语句重复执行多次
- 该方法的第二个参数包含几个元组，该DML语句就会被执行几次
import sqlite3
# 创建数据库
conn = sqlite3.connect("test.db")
# 获取游标
c = conn.cursor()
# 执行SQL语句
# 此处每个元组就代表一行数据
c.executemany('insert into user_tb values(null, ?, ?, ?)',
(('悟空', '4444', 20),
('八戒', '5555', 30),
('沙僧', '6666', 40),
('唐僧', '7777', 50)))
# 提交事务，使修改生效
conn.commit()
# 关闭资源
c.close()
conn.close()第五节：执行查询
- 使用execute执行查询语句
- 此时改为执行select语句，由于select语句执行完成后可以得到查询结果，因此程序可通过游标的fetchone()、fetchmany(n)、fetchall()来获取查询结果，也可以直接将游标当成可迭代对象来获取查询结果
- fetchone():返回一个元组，该元组代表一行数据
- fetchmany(n)：返回一个长度小于等于n的列表，列表每个元素都是一个元组（每个元组代表一行数据）
- fetchall()：尽量避免使用fetchall()来获取查询返回的全部记录，原因是可能导致内存开销过大，严重时可能导致系统崩溃
import sqlite3
# 创建数据库
conn = sqlite3.connect("test.db")
# 获取游标
c = conn.cursor()
# 执行SQL语句
c.execute("select * from user_td where _id > ?", (2,))
# 所有查询结果都通过游标来获取
# description属性（元组）返回列信息
# 如果要获取查询数据，fetchxxx或者直接迭代游标
for col in c.description:
print(col[0], end='\t')
print()
#--------------------fetchone方法------------------
while True:
# 用fetchone每次获取一条记录
row = c.fetchone()
# 如果row为空，说明没有数据
if not row:
break
else:
# 输出该行内各个单元格的数据
for d in row:
print(col[0], end='\t')
print()
#--------------------游标当成可迭代对象------------------
for row in c:
# 输出该行内各个单元格的数据
for d in row:
print(col[0], end='\t')
print()
# 关闭资源
c.close()
conn.close() 第六节：案例实操-使用事务控制数据库操作
- 事务：事务由一步或者几步数据库操作序列组成的逻辑执行单元
- 事务具备的4个特性：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持续性(Durability)，简称ACID
- 事务回滚两种方式：显示回滚和自动回滚
- 显示回滚：调用数据库连接对象的rollback
- 自动回滚：系统错误或者强行退出（退出之前没有提交）
import sqlite3
# 创建数据库
conn = sqlite3.connect("test.db")
# 获取游标
c = conn.cursor()
# 如果游标只是执行DDL语句，程序不需要显示提交事务，程序所做的修改会自动生效
# 如果程序先执行DML语句
# 执行DML语句，事务开启了，该游标后面所执行ddl语句也不会自动生效
c.execute('insert into user_tb values(null, ?, ?, ?)', ('aaa', 'bbb', 23))
# 因此这条DDL语句也不会自动生效
c.execute('create table haha(_id integer primary key)')
# 提交事务，上面的语句才能生效
# conn.commit()
# 显示回滚：回滚事务，如果程序不提交事务，默认就会回滚,上面的语句不会生效
# 自动回滚：没有提交事务
conn.rollback()
# 关闭资源
c.close()
conn.close()第七节：案例实操-用程序执行SQL脚本
- 编写SQL脚本
- 多条SQL语句组成SQL脚本
insert into user_tb values(null, '张三', '11111', 23)
insert into user_tb values(null, '李四', '22222', 24)
insert into user_tb values(null, '小吴', '33333', 25)
creat table test_td(
_id integer primary key autoincrement,
name text,
pass text,
description);
creat table emp_td(
_id integer primary key autoincrement,
emp_name,
emp_pass,
emp_title);- 执行SQL脚本
- 游标对象还包含一个executescript()方法，可执行一段SQL脚本，它并不是一个标准的API，但是大部分的数据库API模块中都有这个方法
import sqlite3
# 创建数据库
conn = sqlite3.connect("test.db")
# 获取游标
c = conn.cursor()
# 打卡SQL脚本所在的文件
with open('a.sql', 'r', True, 'UTF-8') as f:
# 读取文件中的SQL语句
sql = f.read()
# 使用游标来执行SQL脚本，用executescript方法
# SQL脚本中的所有语句都会被执行
c.executescript(sql)
# 提交事务
conn.commit()
# 关闭资源
c.close()
conn.close()- 便捷方法：
- execute(sql[, parameters]):执行一条SQL语句
- executemany(sql[, parameters]):根据序列重复执行SQL语句
- executescript(sql_script)：执行SQL脚本
import sqlite3
# 创建数据库
conn = sqlite3.connect("test.db")
# 打卡SQL脚本所在的文件
with open('a.sql', 'r', True, 'UTF-8') as f:
# 读取文件中的SQL语句
sql = f.read()
# 直接用数据库连接对象来执行SQL脚本
c.executescript(sql)
# 提交事务
conn.commit()
# 关闭资源
conn.close()