1. 简介
数据库种类繁多，每种数据库的对外接口实现各不相同，为了方便对数据库进行统一的操作，大部分编程语言都提供了标准化的数据库接口，用户不需要了解每种数据的接口实现细节，只需要简单地配置，就能快速切换，操作不同的数据库，这样大大降低了编程的难度。
在Python Database API V2.0 中，规范了Python操作不同类型数据库的标准方法，以及组成部分，通过DB API接口可以使用相同的方法连接、操作不同的数据库。主要作用：兼容不同类型的数据库，降低编程难度。该API主要包括：数据库连接对象、数据库交互对象、数据库异常类。
使用DB API的流程如下：
- 安装数据库驱动程序。
- 引入数据库API模块。
- 获取与数据库的连接。
- 执行SQL语句和存储过程。
- 关闭数据库连接。
安装数据库驱动之后，就可以使用Python DB API规范的connect()函数连接数据库。调用connect()函数返回一个connection对象，通过connection对象可以连接数据库，然后访问数据库。
符合规范的数据驱动接口都支持connect对象及连接方法。参数说明如下：
- user：登录数据库的用户名。
- password：登录数据库的用户密码。
- host：数据库服务器的主机名，本地数据库服务器一般为localhost。
- database：数据库名称。
- dsn：数据源名称。如果数据库支持则可以设置。
connect()函数返回一个连接对象，表示当前用户与数据库服务器建立的会话。通过连接对象支持的方法可以实现对数据库的读、写操作。connection对象包含的主要方法如下：
commit()：提交事务。在事务提交之前，所有对数据库进行的修改操作都不同步到数据库，只有在提交事务之后，才同步到数据库。rollback()：回滚事务。恢复数据库到操作之前的数据状态。
cursor()：获取游标对象，通过游标对象操作数据库。close()：关闭数据库连接。关闭后无法再进行操作，除非再次创建连接。
DB API操作数据的主要步骤如下：
- 使用
connect()函数创建connection对象。 - 使用connection对象创建cursor对象。
- 使用cursor对象执行SQL语句，查询数据库，或者执行SQL命令，操作数据库。
- 使用cursor对象从结果集中获取数据。
- 处理获取的数据。
- 关闭cursor对象。
- 关闭connection对象。
2. 使用PyMySQL
安装PyMySQL：根据以下命令安装和查看：
'''Windows 按住win+R 输入 cmd，Mac 打开Terminal''' pip install PyMySQL pip install mysql-connector-python ''' import pymysql # 查看是否导入成功判断是否安装驱动成功
连接数据库：根据DB API操作数据的主要步骤，连接MySQL数据库。
import pymysql # 导入PyMySQL模块 # 连接数据库 db = pymysql.connect( host = 'localhost', port = 3306, user = 'root', password = '88888888', db = 'sql_test1') db.close() # 关闭数据库连接
建立数据表：连接数据库之后，可以使用execute()方法为数据库创建表。
import pymysql # 导入PyMySQL模块 # 连接数据库 db = pymysql.connect( host = 'localhost', port = 3306, user = 'root', password = '88888888', db = 'sql_test1') cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor cursor.execute('drop table if exists tb_new;') # 使用execute()方法执行SQL，如果存在则删除 # 使用预处理语句创建表 ct_sql = """ create table tb_new( id int not null auto_increment comment '自增id', user_name varchar(255) comment '用户名', primary key (id) ); """ cursor.execute(ct_sql) # 使用execute()方法执行SQL查询 cursor.close() # 关闭游标对象 db.close() # 关闭数据库连接
事务处理：
事务就是一个数据库操作序列，当一个事务被提交后，数据库要确保该事务中的所有操作都完成，如果部分未完成，则事务中的所有操作都被回滚，恢复到事务执行前的数据状态，这样可以确保数据操作的一致性和完整性。
提供了两个基本方法：commit()和rollback()。
当执行事务时，可以使用数据连接对象的commit()方法进行提交，如果事务处理成功，则不可撤销；如果事务处理失败，可以使用数据库连接对象的rollback()方法进行回滚，恢复数据库在操作之前的状态。
插入数据：为了避免操作失败，可以使用try语句进行异常跟踪，如果发生异常，则回滚操作，恢复数据库在操作之前的数据状态。
import pymysql # 导入PyMySQL模块 # 连接数据库 db = pymysql.connect( host = 'localhost', port = 3306, user = 'root', password = '88888888', db = 'sql_test1') cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor try: sql = "insert into tb_new(id, user_name) values (10,'test');" cursor.execute(sql) # 使用execute()方法执行SQL查询 db.commit() # 提交事务，同步数据库数据 except: db.rollback() # 如果发生错误则回滚事务 cursor.close() # 关闭游标对象 db.close() # 关闭数据库连接
使用executemany(sql, data)方法批量插入数据：
import pymysql # 导入PyMySQL模块 # 连接数据库 db = pymysql.connect( host = 'localhost', port = 3306, user = 'root', password = '88888888', db = 'sql_test1') cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor sql = "insert into tb_new(id, user_name) values (%s,%s);" # 定义要执行的SQL语句 # 定义数据列表 data = [ (1,'listi'), (2,'wangwu'), (3,'zhaoliu') ] try: cursor.executemany(sql,data) # 批量执行SQL查询 db.commit() # 提交事务，同步数据库数据 except: db.rollback() # 如果发生错误则回滚事务 cursor.close() # 关闭游标对象 db.close() # 关闭数据库连接
查询记录：使用cursor对象的execute()方法执行查询后，通过4种方法从结果集中读取数据。
fetchall()：获取结果集下所有行。fetchmany(size=None)：获取结果集中下面size条记录。如果size大于结果集中的行数，则返回cursor.arraysize记录。fetchone()：获取结果集的一行记录。
rowcount：只读属性，返回执行execute()方法后影响的行数。
import pymysql # 导入PyMySQL模块 # 连接数据库 db = pymysql.connect( host = 'localhost', port = 3306, user = 'root', password = '88888888', db = 'sql_test1') cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor # SQL查询语句 sql = "select * from tb_new" try: cursor.execute(sql) results = cursor.fetchall() for row in results: id = row[0] user_name = row[1] print('id=%s,user_name=%s'%(id,user_name)) except: print('Error:unable to fetch data') cursor.close() # 关闭游标对象 db.close() # 关闭数据库连接
更新记录：修改表中的数据，主要使用SQL的update语句实现。
import pymysql # 导入PyMySQL模块 # 连接数据库 db = pymysql.connect( host = 'localhost', port = 3306, user = 'root', password = '88888888', db = 'sql_test1') cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor # SQL更新语句 sql = """ update tb_new set user_name = 'zhangs' where id = 3; """ try: cursor.execute(sql) db.commit() except: db.rollback() cursor.close() # 关闭游标对象 db.close() # 关闭数据库连接
删除记录：删除表中的数据，主要使用SQL的delete from语句实现。
import pymysql # 导入PyMySQL模块 # 连接数据库 db = pymysql.connect( host = 'localhost', port = 3306, user = 'root', password = '88888888', db = 'sql_test1') cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor # SQL删除语句 sql = """ delete from tb_new where id = 2; """ try: cursor.execute(sql) db.commit() except: db.rollback() cursor.close() # 关闭游标对象 db.close() # 关闭数据库连接
2. 使用SQLite
SQLite是一种嵌入式数据库，由C语言编写，体积很小，经常被集成到各种应用程序中，在IOS和Android的App中都可以集成。
SQLite是一个基于文件的关系型数据库，数据库只是一个文件，最多能存储140TB的数据。没有独立的进程，所有的维护都来自程序本身。
判断是否适合使用SQLite的标准，除了下面3点外，可以选择SQLite。
- 如果程序和数据分离，且它们通过互联网连接，那么不适合使用SQLite。
- 高并发写入，不适合用SQLite。
- 如果数据量非常大，不适合用SQLite。
在使用SQLite之前，需要了解下面几个概念：
- 表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个表，如学生表、班级表、学校表等，表和表之间通过键关联。
- 要操作关系数据库，首先需要连接到数据库，一个数据库连接称为connection。
- 连接到数据库后，需要打开游标，称之为cursor，通过cursor执行SQL语句，然后，获得执行结果。
使用SQLite步骤如下：
- 导入sqlite3数据库模块。
- 创建和打开数据库。
- 获取数据连接对象connection。方法如下：
commit()：事务提交。rollback()：事务回滚。close()：关闭一个数据库连接。cursor()：创建一个游标。
- 使用连接对象connection的
cursor()方法打开一个cursor对象。 - 调用游标对象cursor的方法，执行SQL命令，如查询、更新、删除、插入等操作。
- 使用游标对象的
fetchone()、fetchmany()或fetchall()方法读取结果。
- 分别调用
close()方法，关闭cursor、connection 对象，结束整个操作。
import sqlite3 conn = sqlite3.connect(r'/Users/guanfawang/Downloads/test.db') # 连接SQLite数据库。若不存在，则自动创建 cursor = conn.cursor() # 创建一个cursor try: cursor.execute('create table user_tb(id varchar(20) primary key,name varchar(20))') # 创建表 cursor.execute("insert into user_tb(id,name) values(\'1\',\'Michael\')") # 插入一条记录 conn.commit() # 提交事务 except: conn.rollback() # 回滚事务 print(cursor.rowcount) # 影响的行数：1 cursor.close() # 关闭cursor conn.close() # 关闭数据库连接
插入数据
# 插入单行数据 cur.execute('insert into 数据表 values(%s)'%data) cur.execute('insert into 数据表 values(?,?,?)',(值1,值2,值3)) cur.execute('insert into 数据表 values(字段1,字段2,字段3) values(值1,值2,值3);') # 插入多行数据 data = [(1,'a'),(2,'b'),(3,'c')] # 多行样例 sql_insert = "insert into 数据表 values" sql_values = "" for i in range(0,len(data)): # 根据列表下标索引，提取一行数据 sql_values += '(' sql_values += str(data[i]).strip('(').strip(')') sql_values += '),' sql_values = sql_values.strip(',') # 去掉最后一个逗号 sql_todo = sql_insert + sql_values # 拼接成插入语句 cur.execute(sql_todo)
更新、删除、查询数据
import sqlite3 # 导入模块 conn = sqlite3.connect('test.db') # 创建数据库 cur = conn.cursor() # 创建一个cur游标对象 try: cur.execute("update company set salary = 25000 where id = 1") # 更新数据 cur.execute("delete from company where id = 1") # 删除数据 conn.commit() # 提交事务 except: conn.rollback() # 事务回滚 # 查询记录 results = conn.execute('select id,name,address,salary from company where id = 1') for row in results: print('id=',row[0]) print('name=',row[1]) print('address=',row[2]) print('salary=',row[3]) cur.close() # 关闭游标 conn.close() # 关闭数据库连接
