第七章: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 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()
赞 (0)