Python数据分析之利用pymysql操作数据库

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Python数据分析之利用pymysql操作数据库

1 引言


mysql应该说是如今使用最为普遍的数据库了,没有之一,而Python作为最为流行的语言之一,自然少不了与mysql打交道,pymysql就是使用最多的工具库了。


2 创建库、表


我们先从创建数据库、数据表说起,有了库表,后面的增删改查才有根据。


2.1 创建数据库


pymysql的所有对数据库的操作都必须先与数据库服务建立连接,然后创建游标为基础执行具体的sql语句。创建数据库方法如下:


# -*- coding: utf-8 -*-
import pymysql
conn = pymysql.connect( # 创建数据库连接
    host='10.10.11.131', # 要连接的数据库所在主机ip
    user='chb', # 数据库登录用户名
    password='123456!', # 登录用户密码
    charset='utf8' # 编码,注意不能写成utf-8
)
cursor = con.cursor()
cursor .execute("create database test_db character set utf8;")
# 执行完之后别忘了关闭游标和数据库连接
cursor.close()
conn.close()


上面代码执行完后,就创建了一个名为test_db的数据库:


640.png

2.2 创建数据表


# -*- coding: utf-8 -*-
import pymysql
conn = pymysql.connect( # 创建数据库连接
    host='10.10.11.131', # 要连接的数据库所在主机ip
    user='chb', # 数据库登录用户名
    password='123456!', # 登录用户密码
    database='test_db', # 连接的数据库名,也可以后续通过cursor.execture('user test_db')指定
    charset='utf8' # 编码,注意不能写成utf-8
)
cursor = conn.cursor() # 创建一个游标
# 需要执行的创建表的sql语句
sql = """
create table book(
bookid int auto_increment primary key ,
bookname VARCHAR(255) not null ,
authors VARCHAR(255) not null ,
year_publication YEAR not null
);
"""
cursor.execute(sql) # 使用游标执行sql
# 执行完之后别忘了关闭游标和数据库连接
cursor.close()
conn.close()


640.png


在这个创建数据表的例子中,在创建数据库服务连接时,我们通过 database='test_db'这行代码指定参数连接了目标数据库,如果不想在创建数据库服务连接时指定数据库,可以在后续通过cursor.execture('user test_db')这种方法指定数据库。


从上面创建数据库和数据表的例子可以看出,pymysql执行具体操作时都是先创建数据库服务连接,然后通过连接创建游标,以游标来执行具体的sql语句来完成具体的对数据库操作。不单单可以创建数据库、数据表,还可以创建索引、视图等等,方法也是一样的,这里不再介绍。


3 增删改查


3.1 插入


插入操作可以通过游标的execute和executemany两个方法来完成。注意:只要是对数据表有修改的操作(插入、更新、删除)在使用execute方法后,都需要再次调用commit方法对数据库的修改才会最终生效。


execute方法一次插入一条记录,executemany一次插入多条记录:


(1)execute:一次插入一条记录


cursor.execute('insert into book(bookname, authors, year_publication) values("%s", "%s", %s);' % ('Python从入门到放弃', '乔布斯', 2019))conn.commit()


640.png


上面这种写法是先用字符串利用%s占位生成一条完整的sql语句,然后去执行。其实,pymysql的游标也自带这一功能,而且看起来更加方便,所以,我们还可以这样执行execute方法:


cursor.execute('insert into book(bookname, authors, year_publication) values(%s, %s, %s);', ('Python从入门到放弃', '乔布斯', 2019))conn.commit()


640.png


这种方法的好处是sql语句中的%s不需要加引号(如果加了引号,引号也会被当做数据插入到数据表中),游标执行这一语句时,会根据数据类型来判断是否要加上引号。


(2)executemany:一次插入多条记录


data = [
    ('21天完全入门Java', '扎克伯格', 2018),
    ('Linux学习手册', '李纳斯', 2017),
    ('MySQL从删库到跑路', '比尔盖茨', 2018),
]
cursor.executemany('insert into book(bookname, authors, year_publication) values("%s", "%s", %s);', data)
conn.commit()

640.png


3.2 更新


cursor.execute('update book set authors=%s where bookname=%s;', ["马云", "Python从入门到放弃"])
conn.commit()

640.png


3.3 查询


查询是最最要但也是最复杂的一个操作了。我们分为fetch操作、游标两部分来说。


(1)fetch操作


插入、更新、删除操作必须再执行commit操作之后才会生效,而查询操作也只有在执行fetch操作之后才会生效。fetch操作包括3个方法,分别是fetchone()、fetchall()、fetchmany()。


fetchall():查询指定的所有记录
cursor.execute('select * from book where bookid < %s;', [4])
books = cursor.fetchall()
print(books)


输出结果为:


((1, 'Python从入门到放弃', '马云', 2019), (2, 'Python从入门到放弃', '马云', 2019), (3, '21天完全入门Java', '扎克伯格', 2018))


输出结果是以元组的形式来保存,且每一条记录也是一个元素,这是由游标cursor决定的,在下文中还会说到。


fetchmany(size):查询指定数量的记录 cursor.execute('select * from book where bookid < %s;', [4]) books = cursor.fetchmany(2) print(books) 输出结果为:


((1, 'Python从入门到放弃', '马云', 2019), (2, 'Python从入门到放弃', '马云', 2019))


可以看出,fetchmany(size)是取出符合查询条件的最前面的指定数量记录。这里的size指的就是想要取出的记录条数。

fetchone():取出第一条记录


cursor.execute('select * from book where bookid < %s;', [4])
books = cursor.fetchone()
print(books)


输出结果:


(1, 'Python从入门到放弃', '马云', 2019) fetchone()相当于是fetchmany(1),取出第一条符合查询条件的记录。


(2)游标


我们之前使用游标都是采用默认的Cursor类型,除此以外,pymysql还提供了DictCursor、SSCursor、SSDictCursor这几类游标。


DictCursor 之前使用的Cursor返回的数据是以元组的方式保存,而DictCursor是以字典的形式保存。创建这种类型的游标方法也很简单,在conn.sursor()方法中传入DictCursor这个类即可:

cursor = conn.cursor(pymysql.cursors.DictCursor) # 创建一个字典游标
cursor.execute('select * from book where bookid < %s;', [3])
books = cursor.fetchall()
print(books)
cursor.execute('select * from book where bookid < %s;', [3])
book_one = cursor.fetchone()
print(book_one)


输出结果如下:


[{'bookid': 1, 'bookname': 'Python从入门到放弃', 'authors': '马云', 'year_publication': 2019}, {'bookid': 2, 'bookname': 'Python从入门到放弃', 'authors': '马云', 'year_publication': 2019}]
{'bookid': 1, 'bookname': 'Python从入门到放弃', 'authors': '马云', 'year_publication': 2019}


SSCursor和SSDictCursor


SSCursor和SSDictCursor被称为流式游标,这类游标不会像上面使用的Cursor和DictCursor那样,一次性返回所有的数据,流式游标会陆陆续续一条一条得返回查询数据,所以这类游标适用于内存低、网络带宽小、数据量大的应用场景中。


流式游标的使用方法类似于迭代器,再循环中每取一条,生成一条:


cursor = conn.cursor(pymysql.cursors.SSCursor) # 创建一个流式游标
cursor.execute('select * from book;')
book = cursor.fetchone()
while book:
print(book)
book = cursor.fetchone()


输出结果如下:


(1, 'Python从入门到放弃', '马云', 2019) (2, 'Python从入门到放弃', '马云', 2019) (3, '21天完全入门Java', '扎克伯格', 2018) (4, 'Linux学习手册', '李纳斯', 2017) (5, 'MySQL从删库到跑路', '比尔盖茨', 2018)


注意:流式游标虽然也有fetchall()方法,调用后的结果与普通游标一样返回所有数据,但是最好别去调用,这样会失去流式游标的优势。使用流式游标时,如果数据量很大,导致游标一直处在循环遍历状态,这时,数据库连接(conn)是被占用的,不能再被用于执行其他sql,如果要执行其他sql那就必须再创建一个数据库连接,游标占用数据库连接的时长是有限制的,如果流式游标一直在遍历,60秒后数据库连接会断开,不过可以在创建数据库连接时传入参数init_command=("SET NET_WRITE_TIMEOUT=XX")来设置这个超时时间。


SSCursor与SSDictCursor的区别就类似于Cursor和DictCursor的区别,这里就不在介绍了。


3.4 删除


使用pymysql执行插入、更新、删除操作都是相似的,最后都需要commit提交:


cursor.execute('delete from book where bookid=%s;', [1])
conn.commit()


这时候,bookid为1的记录已经被删除,如下图所示:


640.png


也可以使用executemany()一次性删除多条:


cursor.executemany('delete from book where bookid=%s;', [[2], [4]])
conn.commit()


如下图所示,bookid为2和4的记录已经被删除:


640.png

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
关系型数据库 数据库连接 数据库
Python执行PG数据库查询语句:以Markdown格式打印查询结果
使用Python的`psycopg2`和`pandas`库与PostgreSQL交互,执行查询并以Markdown格式打印结果。首先确保安装所需库:`pip install psycopg2 pandas`。接着建立数据库连接,执行查询,将查询结果转换为DataFrame,再用`tabulate`库将DataFrame格式化为Markdown。代码示例包括连接函数、查询函数、转换和打印函数。最后限制列宽以适应输出。
|
1天前
|
JSON 数据挖掘 API
数据分析实战丨基于pygal与requests分析GitHub最受欢迎的Python库
数据分析实战丨基于pygal与requests分析GitHub最受欢迎的Python库
10 2
|
1天前
|
SQL 分布式计算 DataWorks
DataWorks操作报错合集之在运行数据分析任务时,底层依赖服务alisa.olap.submitjob出现了异常,如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
1天前
|
分布式计算 DataWorks API
DataWorks操作报错合集之数据集成同步到本地数据库时,出现报错,如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
2天前
|
Java Serverless 应用服务中间件
Serverless 应用引擎操作报错合集之部署python项目时,构建过程报错,怎么解决
Serverless 应用引擎(SAE)是阿里云提供的Serverless PaaS平台,支持Spring Cloud、Dubbo、HSF等主流微服务框架,简化应用的部署、运维和弹性伸缩。在使用SAE过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
2天前
|
SQL 关系型数据库 MySQL
Python 操作 MySQL 数据库
Python 操作 MySQL 数据库
|
2天前
|
数据采集 数据可视化 数据挖掘
如何快速上手 Python,成为一名数据分析师
【6月更文挑战第10天】学习Python是成为数据分析师的关键。首先,掌握基本语法和数据结构,如变量、控制结构及列表、字典等。然后,学习NumPy、Pandas和Matplotlib库:NumPy用于数组计算,Pandas处理和分析数据,Matplotlib进行数据可视化。不断实践项目,从简单分析任务做起,并保持学习新技能的热情,关注技术动态。通过系统学习和实践,可迅速踏上数据分析师之路。
|
3天前
|
机器学习/深度学习 数据可视化 数据挖掘
python每日可视化分析:从过去到现代数据分析的演进
python每日可视化分析:从过去到现代数据分析的演进
|
3天前
|
SQL 关系型数据库 数据库
Python查询PostgreSQL数据库
木头左教你如何用Python连接PostgreSQL数据库:安装`psycopg2`库,建立连接,执行SQL脚本如创建表、插入数据,同时掌握错误处理和事务管理。别忘了性能优化,利用索引、批量操作提升效率。下期更精彩!💡 csvfile
Python查询PostgreSQL数据库
|
3天前
|
SQL 关系型数据库 数据库
Python执行PostgreSQL数据库查询语句,并打印查询结果
本文介绍了如何使用Python连接和查询PostgreSQL数据库。首先,确保安装了`psycopg2`库,然后创建数据库连接函数。接着,展示如何编写SQL查询并执行,例如从`employees`表中选取所有记录。此外,还讨论了处理查询结果、格式化输出和异常处理的方法。最后,提到了参数化查询和事务处理以增强安全性及确保数据一致性。
Python执行PostgreSQL数据库查询语句,并打印查询结果