Python知识整合（操作数据库）

1、数据库编程接口

市面有多种数据库如mysql、SQLite等等。为了对数据库进行统一的操作，大多数语言提供了简单的、标准化的数据库接口（API）。在Python Database API 2.0规范中，定义了Python数据库API接口的各个部分。下面我将介绍数据库API接口中的连接对象和游标对象

1.1、连接对象

数据库连接对象（Connection Object）主要提供数据库游标对象和提交/回滚事物的方式，以及关闭数据库连接。

1.11、获取连接对象

使用connect（）函数获取数据库连接，改函数具有多个参数，具体使用那个参数，根据数据库的类型而定。

onnect()函数参数说明表

参数	说明
dsn	数据源名称，给出该参数表示数据库依赖
user	用户名
password	用户密码
host	主机名
database	数据库名称

例子，使用PyMySql模块连接MySQL数据库（在下面内容中会详细介绍）。

conn = pymysql.connect(host='localhost', 
						user='user',
						password='passwd',
						charset='utf-8'
						cursorclass=pymysql.cursors.DictCursor)

注意pymysql.connect()使用的参数与上表不完全相同，要以具体的数据库模块为准。

1.12、连接对象方法

connect（）函数返回连接对象，这个连接对象表示目前和数据库的会话。连接对象支持的方法如下表

方法名	说明
close()	关闭数据库连接
commit()	提交事物
rollback()	回滚事物
cursor()	获取游标对象，操作数据库，如执行DML（触发器）操作，调用存储过程等

1.2、游标对象

游标对象（Cursor Object）代表数据库中的游标，用于指示抓取数据操作的上下文，主要提供执行SQL语句、调用存储过程等。

通过cursor（）方法可以获取游标对象。

游标对象的属性：1、description:数据库列表类型和值的描述信息。2、rowcount():返回结果的函数统计信息，如SELECT、UPDATE等

游标对象的方法如下表

方法名	说明
callproc(procname[,parameters])	调用存储过程，需要数据库支持
close()	关闭当前游标
execute（operation[,parameters])	执行SQL语句或者数据库命令等数据库操作
executemany（operation,seq_params)	用于批量操作，如批量删除
fetchone()	获取查询结果集中的下一条记录
fetchmany(size)	获取指定数量的记录
fetchall()	获取结果集中的所有记录
nextset()	跳至下一个可用的结果集
arraysize()	指定使用fetchmany获取的函数，默认为1
setinputsize(sizes)	设置在调用execute*()方法时分配的内存区域大小
setoutputsize(sizes)	设置列缓冲区大小，对大数据列如LONGS和BLOBS（二进制大对象）尤其有用

2、使用SQLite

SQLite（c语言写的）是一种嵌入式数据库，它的数据库是一个文件。体积小，且可以跨平台使用，经常被嵌入到各种应用程序。python中内置了SQLite3。

2.1、创建数据库文件

python操作数据流的流程

开始>>创建connection(连接)>>获取cursor(游标)>>执行SQL语句，处理数据结果>>关闭cursor(游标)>>关闭connection(连接)

如

import sqlite3   # 连接到SQLite数据库
conn = sqlite3.connect('1.db')   # 数据库文件是test.db，如果文件不存在，会在当前目录创建
cursor = conn.cursor()  # 创建一个Cursor
cursor.execute('create table user (id int(10) primary key, name varchar(20))')  # 执行一条SQL操作，创建user表
cursor.close()  # 关闭游标
conn.close()  # 关闭连接

2.2、操作SQLite

2.21、新增用户数据信息

使用以下SQL语句

insert into 表名 （字段名1，字段名1，........） values(字段值1，字段值2，.......）

例子

    import sqlite3   # 连接到SQLite数据库
    conn = sqlite3.connect('name.db')   # 数据库文件是test.db，如果文件不存在，会在当前目录创建
    cursor = conn.cursor()  # 创建一个Cursor
    # 执行一条SQL操作，创建user表
    cursor.execute('create table user (id int(10) primary key, name varchar(20))')
    # 执行一条SQL操作，向表user插入一条记录
    cursor.execute('insert into user (id, name) values ("1","wr")')
    cursor.execute('insert into user (id, name) values ("2","小明")')
    cursor.close()  # 关闭游标
    conn.commit()  # 提交事物
    conn.close()  # 关闭连接

为了验证程序是否正常可以再次运行程序，如果出现如下异常

sqlite3.OperationalError: table user already exists

则说明数据成功插入

2.22、查看用户数据信息

可以使用以下SQL语句

select 字段名1，字段名2,..... from 表名 where 查询条件

fetchone():查询结果集中的下一条记录
fetchmany(size) :获取指定数量的记录
fetchall():获取结果集的所有记录

例子

import sqlite3   # 连接到SQLite数据库
conn = sqlite3.connect('name.db')   # 数据库文件是name.db，如果文件不存在，会在当前目录创建
cursor = conn.cursor()  # 创建一个Cursor

# 执行查询语句
cursor.execute('select * from user') 
# 获取查询结果
result = cursor.fetchone()
print(result)

cursor.close()  # 关闭游标
conn.close()  # 关闭连接

修改上述 cursor.execute('select * from user') 这句代码为cursor.execute('select * from user where id > ?',(1,))，其中使用问号作为占位符代替具体的值，然后使用最后一个元组来替换问号，注意元组最后面的逗号不能省略
修改后的代码等价于cursor.execute('select * from user where id > 1)
不过推荐占位符的方式，因为可以有效防止SQL注入

2.23、修改用户数据信息

可以使用以下sql语句

update 表名 set 字段名 = 字段值 where 查询语句

例子，将ID=1的name改为WR

import sqlite3   # 连接到SQLite数据库
conn = sqlite3.connect('name.db')   # 数据库文件是test.db，如果文件不存在，会在当前目录创建
cursor = conn.cursor()  # 创建一个Cursor

cursor.execute('update user set name = ? where id = ?', ('WR', 1))
# 执行查询语句
cursor.execute('select * from user')
# 获取查询结果
result1 = cursor.fetchall()
print(result1)

cursor.close()  # 关闭游标
conn.commit() # 提交事务
conn.close()  # 关闭连接

2.24、删除用户信息

可以使用以下sql语句

delete from 表名 where 查询语句

3、使用MySQL

可以直接安装phpstudy集成环境，包括mysql和apache数据库及其环境。安装步骤可以百度

3.1、安装PyMySQL模块

在python中支持MYSQL的数据库模块很多，我们选择PyMySQL模块
在命令行使用下面语句 pip install PyMySQL安装PyMySQL模块

3.2、连接数据库

PyMySQL模块也遵循Python Database API 2.0规范，故MySQL的操作方法与SQLite类似
例子

import pymysql

# 打开数据库连接，参数1：主机名或IP;参数2：用户名；参数3：密码；参数4；数据库名称
db = pymysql.connect("localhost", "root", "root", "mydata")
# 通过cursor()函数创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL查询
cursor.execute('SELECT VERSION()')
# 使用 fetchone()方法获取单条信息
data = cursor.fetchone()
print("database version： %s" % data)
# 关闭数据库连接、
db.close()

输出

database version： 5.5.53

3.3、创建数据表

例子:通过execute()方法创建表books(图书表)。books包含id(主键)，name(图书名)，category（图书馆分类），price(图书馆价格)

import pymysql

# 打开数据库连接，参数1：主机名或IP;参数2：用户名；参数3：密码；参数4；数据库名称
db = pymysql.connect("localhost", "root", "390800956", "mydata")
# 通过cursor()函数创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL语句，如果存在就删除
cursor.execute('DROP TABLE IF EXISTS books')
sql = """
CREATE TABLE books(
    id int(8) NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    category varchar(50) NOT NULL,
    price decimal(10,2) DEFAULT NULL,
    PRIMARY KEY (id)   
)   ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
"""
cursor.execute(sql)

# 关闭数据库连接、
db.close()

3.4、操作数据表

可以execute()方法添加一条记录，也可以使用executemany()方法进行批量添加记录
语法格式

executemany(operation,seg_of_params)

说明：
operation:执行的sql语句
seg_of_params：参数序列

例子

import pymysql

# 打开数据库连接，参数1：主机名或IP;参数2：用户名；参数3：密码；参数4；数据库名称
db = pymysql.connect("localhost", "root", "390800956", "mydata", charset="utf8")
# 通过cursor()函数创建一个游标对象cursor
cursor = db.cursor()
# 数据列表
data = [("《见识》", "literature", "50.00"),
        ("《智能时代》", "data", "70.00"),
        ("《活着》", "literature", "23.00")]
try:
    # 执行SQL语句，插入多条数据
    cursor.executemany("insert into books (name, category, price) values (%s, %s, %s)", data)
    # 提交数据
    db.commit()
except:
    # 发送错误时回滚
    db.rollback()
# 关闭数据库连接、
db.close()

在上面的代码中要注意
1、在使用connect()方法时，设置charset=“utf8”,为得是识别中文
2、在使用insert语句插入数据时，使用%s作为占位符，防止SQL注入