专栏首页凹凸玩数据干货 | 利用Python操作mysql数据库

干货 | 利用Python操作mysql数据库

作者 | Tao

来源 | 知乎


本文主要讲解如何利用python中的pymysql库来对mysql数据库进行操作。

先看一下最常见的操作:

  • 从数据库中select需要的字段(对数据简单聚合处理)
  • 将查找的数据导出为本地文件(csv、txt、xlsx等)
  • 通过pandas的read_excel(csv、txt)将本地文件转化成python中的变量,并对数据进行相应的处理和分析
  • 将处理好的数据通过pandas的to_excel(csv、txt)导出为本地文件

但是大家不觉得第二步很多余吗?为什么还要先导出再导入,这个中间步骤纯属浪费时间啊,理想中的步骤应该是这样的

  • 将mysql中的数据导入到python中
  • 利用python处理分析数据
  • 导出成excel报表

这么一看是不是感觉就舒服多了?那么问题来了,怎么实现直接把mysql中的数据直接导入python中呢?

这就要讲到今天的重点了:

  • 第一种方法:read_sql
  • 第二种方法:pymysql

先看一下我们今天的数据库信息: host:192.168.0.*** port:3306 user:root 密码:********

数据库:test 表名:weather_test 字段及数据:

1

read_sql()

read_sql(sql,con,index_col='None',coerce_float='True',params='None',parse_dates='None',columns='None',chunksize:None='None')

read_sql方法是pandas中用来在数据库中执行指定的SQL语句查询或对指定的整张表进行查询,以DataFrame 的类型返回查询结果.

其中各参数意义如下:

  • sql:需要执行的sql语句
  • con:连接数据库所需的engine,用其他数据库连接的包建立,例如SQLalchemy和pymysql
  • index_col: 选择哪列作为index
  • coerce_float:将数字形字符串转为float
  • parse_dates:将某列日期型字符串转换为datetime型数据
  • columns:选择想要保留的列
  • chunksize:每次输出多少行数据

1.首先导入pandas和sqlalchemy

2.创建连接

3.编写sql代码,执行sql代码,获取返回的值

import pandas as pd
import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://root:******@192.168.0.***:3306/test')

sql='''
select * from weather_test where
create_time between '2020-09-21' and '2020-09-22'
and city in ('杭州','上海')
'''
df = pd.read_sql(sql,engine)
df

利用pymysql建立连接并查询也是可以的

至此一次简单地利用pandas中read_sql方法从数据库获取数据就完成了

2

PyMySQL

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库,可以方便的连接数据库并操作数据库

1.安装

首先打开cmd,输入 pip install pymysql 来安装pymysql这个库

2.利用pymysql操作数据库

接下来打开jupyter notebook,开始尝试操作数据库

2.1 首先导入pandas,pymysql

import pandas as pd
import pymysql

2.2 接下来创建于数据库的连接

import pandas as pd
import pymysql

# 打开数据库连接
db = pymysql.connect("192.168.0.***", "root", "******", "test", charset='utf8' )

使用connect()方法可以建立与数据库的连接,其中需要的主要参数已经标注在图片上,charset建议选utf8,防止中文乱码,将建立好的连接对象赋值给db这个变量名

2.3 使用cursor()方法获取操作游标

import pandas as pd
import pymysql

# 打开数据库连接
db = pymysql.connect("192.168.0.***", "root", "******", "test", charset='utf8' )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()

游标(Cursor)是处理数据的一种方法,为了查看或者处理结果集中的数据,游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。

可以把游标当作一个指针,它可以指定结果中的任何位置,然后允许用户对指定位置的数据进行处理,通俗来说就是,操作数据和获取数据库结果都要通过游标来操作。如果不获取游标,我们就没法获得查询出来的数据。

最常用的也是默认的游标就是cursor,返回的数据格式为tuple,其余的游标类型还有DictCursor,SSCursor,SSDictCursor等,SS开头的游标称为流式游标,Cursor和DictCursor游标可以一次性返回所有的数据,流式游标智能一条一条得返回查询数据,所以这类游标适用于内存低、网络带宽小、数据量大的应用场景中。

DictCursor:返回字典(Dict)格式的数据 SSCursor:流式游标返回元组(Tuple)格式数据 SSDictCursor:流式游标返回字典(Dict)格式数据

使用其他游标时,只用在cursor()方法中加入相应的参数即可

cursor = db.cursor(pymysql.cursors.SSDictCursor)

2.4 编写sql代码,执行sql代码

写一句简单地sql语句,目的是查上海和杭州在2020-09-21~2020-09-22这两天的天气,将写好的sql语句改为字符串格式并赋值给sql这个变量名,使用excute()这个方法可以通过定义好的游标来执行写好的sql语句,可以看到输出了一个数字4,代表查询出的数据集共包含4条数据。

2.5 获取返回的查询结果

使用fetchall()方法可以通过定义好的游标来获取查询出的完整数据集,并赋值给变量名cds

打印一下cds这个变量,可以看到数据已经获取到了,现在要将其变成我们常用的DataFrame格式

除了fetchall()这个方法,还有fetchone()和fetchmany(size)这两种方法可以获取返回的数据

fetchall():返回所有数据 fetchone():返回下一条数据 fetchmany(size):返回下size个数据

2.6 将获取到的数据转换成DataFrame格式

将tuple格式的cds变量转换为list,再通过pandas中的DataFrame()方法,将cds转化为DataFrame格式,并改好列名,赋值给weather变量名

输出weather看一下数据

2.7 关闭游标,关闭数据库连接

import pandas as pd
import pymysql

# 打开数据库连接
db = pymysql.connect("192.168.0.***", "root", "******", "test", charset='utf8' )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()

sql = """
select * from weather_test
where create_time between '2020-09-21' and '2020-09-22'
and city in ('上海','杭州')
"""

cursor.execute(sql)
cds = cursor.fetchall()
weather = pd.DataFrame(list(cds),columns=['ID','时间','省份','城市','最高温度','最低温度','白天天气','夜间天气','风力','风向'])
cursor.close()  # 关闭游标
db.close()  # 关闭数据库连接

使用pymysql创建一个connect对象的时候,就已经和mysql之间创建了一个tcp的长连接,只要不调用这个对象的close方法,这个长连接就不会断开,就会一直占用资源,所以执行完之后别忘了关闭游标和数据库连接

以上只是最简单的使用python查询数据库的办法,其他增删改操作与此类似,大家可以自行发挥

- END -

本文为转载分享&推荐阅读,若侵权请联系后台删除

本文分享自微信公众号 - 凹凸数据(alltodata)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-11-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 通过cursor游标讲解,带你初步搞懂python操作mysql数据库

    有时候,我们执行一条查询语句的时候,往往会得到N条返回结果,执行sql语句取出这些返回结果的接口(起始点),就是游标。沿着这个游标,我们可以一次取出一行记录。

    朱小五
  • 原来Python自带了数据库,用起来真方便!

    Python作为数据科学主流语言,被广泛用于数据读存、处理、分析、建模,可以说是无所不能。

    朱小五
  • 干货!python与MySQL数据库的交互实战

    如果你想要使用python操作MySQL数据库,就必须先要安装pymysql库,这个

    朱小五
  • Python接口自动化之pymysql数据库操作

    在上一篇Python接口自动化测试系列文章:Python接口自动化之yaml配置文件,主要介绍主要介绍yaml语法、yaml存储数据,封装类读写yaml配置文件...

    ITester软件测试小栈
  • Python应用MongoDB数据库的一些总结

    数据库,顾名思义,就是数据存储的一个仓库。个人理解,与普通的文件不同,数据库因为是专门用于存储特定格式的数据,所以术业有专攻,它在处理数据相关的事务时更为专业和...

    luanhz
  • 【招聘信息】杭州51信用卡招聘MySQL DBA

    1. 负责数据库服务日常维护、可用性监控、容量规划、故障诊断和排除、数据迁移、扩容实施等;

    用户1278550
  • Python在SQLite数据库中动态创建数据表的思路与实现

    在管理信息系统或者动态网站开发时,离不开数据库的使用。以SQLite数据库为例,系统运行时要求数据库和对应的数据表已存在,一种方案是提前建好数据库和所有表,再一...

    Python小屋屋主
  • JDBC

    Java使用JDBC访问数据库的步骤如下: 1.加载数据库驱动; 2.建立数据链接; 3.创建Statement对象; 4.执行SQL语句; 5.访问...

    德勒
  • Oracle数据库应用系统结构

    在安装、部署Oracle数据库软件时,需要根据不同应用结构(即硬件平台、操作系统平台)采用不同的方法(基本安装、高级安装),下面介绍几种常见的应用结构。

    数据和云
  • Gartner最新报告:腾讯云数据库增速国内第一

    近日,国际权威研究机构Gartner公司发布《The Future of the Database Management System (DBMS) Marke...

    勤劳的小蜜蜂

扫码关注云+社区

领取腾讯云代金券