MySQL基础入门——MySQL与R语言、Python交互

发表于

MySQL作为z最为流行的关系型数据库管理平台之一，与绝大多数数据分析工具或者编程语言都有接口，今天这一篇分享如何将MySQL与R语言、Python进行连接。

R语言中与SQL管理平台通讯的接口包有很多，可以根据自己使用的数据库平台类型以及习惯，挑选合适的接口包。因为我个人笔记本使用的MySQL平台，所以本篇仅以MySQL为例分享。（如果你需要其他平台的接口导入方案，可以直接在csdn博客上搜关键字，有很多博客资料可以参考）。

我习惯使用的接口包是RMySQL，里面的核心函数主要涉及数据库连接，数据读写，数据查询三个方面，以下是三个方面的内容实例。

R与数据库的连接： library(“RMySQL”)library(“magrittr”)

数据库连接语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
复制代码conn <- dbConnect(
              MySQL(),           #数据库平台类型
              dbname=”db1”,      #数据库名称
              username=”root”,   #登录账号（MySQL初始安装时设置的账号）
              password=”**“,     #登录密码（MySQL初始安装时设置的密码）
              host=”127.0.0.1”,  #地址
              port=3306          #端口号
              )
summary(conn)      #查看连接信息：
User:   root
 Host:   127.0.0.1
 Dbname: db1
 Connection type: 127.0.0.1 via TCP/IP
dbGetInfo(conn)    #查看连接详细信息（列表形式） $host
‘127.0.0.1’
$user
‘root’
$dbname
‘db1’
$conType
‘127.0.0.1 via TCP/IP’
$serverVersion
‘5.7.17-log’
$protocolVersion
10
$threadId
11
$rsId 
dbListTables(conn) #查看该数据库连接内的表信息 ‘birthdays’ ‘company’ ‘dataanalyst’ ‘foodranking’ ‘foodtypes’ ‘orderinfo’ ‘str_date’ ‘userinfo’
dbDisconnect(conn) #关闭连接(数据通讯完成之后再运行)
R语言与MySQL数据库读写：

(mydata <- iris)
dbWriteTable(
          conn = conn,      #连接名称
          name = "mydata",  #指定导入后的表名
          value = iris,     #指定要导入的R内存空间数据对象
          row.names = FALSE #忽略行名
          )                 #写表
dbListTables(conn)
'birthdays' 'company' 'dataanalyst' 'foodranking' 'foodtypes' 'mydata' 'orderinfo' 'str_date' 'userinfo'

mydata1 <- dbReadTable( 
         conn = conn,       #连接名称
         name = "mydata"    #数据库中的表名
          )                 #读表  
head(mydata1,10)



以上读写都是一次性操作，不能在读写的同时执行条件筛选等步骤，通常我们需要使用查询方式来获取指定条件的数据并返回数据框。

1
2
3
4
5
6
7
8
复制代码result1 <- dbSendQuery(conn = conn,  
                       statement = "SELECT * from mydata where `Sepal.Length` between 4 and 5 
                                   and Species = 'setosa' "
                                   #查询条件
                      ) %>% dbFetch()
                      #将查询结果返回数据框
head(result1,10)
dbClearResult(result1)   #清除查询(释放内存)

这一句清除的是查询，即上一句中的dbSendQuery部分（布包含后面的dbFetch，我只是为了方便一次性输出了）。

1
2
3
4
复制代码dbRemoveTable(conn,"mydata")   #删除表    
 dbListTables(conn)                             
'birthdays' 'company' 'dataanalyst' 'foodranking' 'foodtypes' 'orderinfo' 'str_date' 'userinfo'
dbDisconnect(conn)             #断开连接

Python:

Python与MySQL连接：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
复制代码from sklearn.datasets import load_iris
import pandas as pd 
from sqlalchemy import create_engine
import MySQLdb
conn=MySQLdb.connect(
                    host="localhost",  #地址
                    user="root",       #登录名（同上）
                    passwd="******",   #登录密码（同上）
                    db="db1",          #要连接的数据库名称
                    charset="utf8"     #声明数据编码
                    )

engine = create_engine('mysql+mysqldb://root:password@localhost:3306/db1?charset=utf8')
#使用 sqlalchemy接口连接连接

Python与MySQL数据读写操作：

Pandas库中有封装过的数据读写函数，可以直接针对连接后的数据进行数据读写，非常方便。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
复制代码iris = load_iris()
mydata = pd.DataFrame(
             iris.data[:,:],
             columns=['sepal_length','sepal_width','petal_length','petal_width']
             )
mydata.head(10)
#将数据框直接写入MySQL
mydata.to_sql(
       name = "iris",
       con  = engine
        )
#直接读取MySQL中的表：
mydata1 = pd.read_sql_table(
            table_name= "str_date",
            con =engine
            )
#通过查询过滤条件获取表数据：
mydata1 = pd.read_sql_query(
            sql = "SELECT * from iris where sepal_length between 4 and 5 and petal_width != 0.2 ",
            con =engine
            )




你可以通过以上MySQLlb接口建立的连接来执行查询操作！

1
2
3
4
5
6
复制代码cursor = conn.cursor()         #获取操作游标 
sql = "SELECT * from iris where sepal_length between 4 and 5 and petal_width != 0.2"
cursor.execute(sql)            # 使用execute方法执行SQL语句
cursor.fetchall()              #获取查询数据
cursor.close()                 # 关闭游标
conn.close()                   # 关闭数据库连接


总觉得MySQLlb的接口使用起来过于复杂，不直观，输出数据也不友好，还好pandas支持sqlalchemy的链接，使用pandas里面的函数可以基本满足写表、读表、执行查询的需要。

以上仅仅是MySQL与R语言、Python交互的基础函数，当然还有更为复杂的增删以及插入命令，如果需要了解详细内容可以参考RMySQL、sqlalchemy库的官方文档。

在线课程请点击文末原文链接：

Hellobi Live | R语言可视化在商务场景中的应用

往期案例数据请移步本人GitHub： github.com/ljtyduyu/Da…

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%