Python操作mysql数据库

本文介绍如何利用python中的pymysql库来对mysql数据库进行操作。将mysql中的数据直接导入python中,有如下两种方式:

1)使用read_sql;

2) 使用pymysql

先看一下我们测试用的数据库信息:

host:192.168.0.***
port:3306
user:root
密码:********
图片.png

数据库:test

表名:weather_test

字段及数据:

图片.png


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:每次输出多少行数据

如下代码:

#导入pandas和sqlalchemy
import pandas as pd
import sqlalchemy
#创建连接
engine = sqlalchemy.create_engine('mysql+pymysql://root:******@192.168.0.***:3306/test')
#执行sql,获取返回值
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

如下图所示:

图片.png

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

图片.png

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


2、PyMySQL

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

安装

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

图片.png

利用pymysql操作数据库

接下来打开jupyter notebook,开始尝试操作数据库。如下代码:

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

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

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

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

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

  • DictCursor:返回字典(Dict)格式的数据

  • SSCursor:流式游标返回元组(Tuple)格式数据

  • SSDictCursor:流式游标返回字典(Dict)格式数据

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

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

编写sql代码,执行sql代码,如下图:

图片.png

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

获取返回的查询结果

cds=cursor.fetchall()

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

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

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

  • fetchall():返回所有数据

  • fetchone():返回下一条数据

  • fetchmany(size):返回下size个数据

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

图片.png

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

输出weather看一下数据:

图片.png

最后关闭游标,关闭数据库连接,如下完整代码:

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方法,这个长连接就不会断开,就会一直占用资源,所以执行完之后别忘了关闭游标和数据库连接。