第七章：Python之数据库编程

copy_left 2021-02-02

展开全文

第一节:数据库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./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 tabnamec.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 tabnamec.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()