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注入

0%