• Posts tagged "R"

Blog Archives

2021 微软Ignite Post Watching Part:用R语言进行量化文本分析

跨界知识聚会系列文章,“知识是用来分享和传承的”,各种会议、论坛、沙龙都是分享知识的绝佳场所。我也有幸作为演讲嘉宾参加了一些国内的大型会议,向大家展示我所做的一些成果。从听众到演讲感觉是不一样的,把知识分享出来,你才能收获更多。

关于作者

  • 张丹,数据分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/meeting-ms-ignite-20211214

前言

疫情反复不断,线下开会越来越难,本次以上线进行分享,参与微软技术直通车。希望疫情早日过去,让大家恢复以往的能见面的交流。

本次的主题是用R语言进行量化文本分析,R语言在被大家广泛的使用,全球社区也在不断的壮大,每天都有好的包被创作出来。本文分析已经是老生长谈的话题,让文本分析过程如结构化数据分析一样的还是比较创新的体验。

目录

  1. 我分享的主题:用R语言进行量化文本分析
  2. 会议体验和照片分享

1. 我分享的主题:用R语言进行量化文本分析

本次分享的主题,是用R语言进行文本分析。在互联网的今天,我们每天都会生产和消费大量的文本信息,如报告、文档、新闻、聊天、图书、小说、语音转化的文字等。海量的文本信息,不仅提供扩宽的研究对象和研究领域,也为商业使用带来了巨大的机会。

R在做数据分析有自己独特的优势,方便、简单、高效。我从2个方面来介绍文本分析,先是jiebaR包进行中文文本分词,然后是quanteda包的进行量化文本分析。本次分享的PPT和代码,我上传到了github:https://github.com/bsspirit/quanteda

我主要为分2个部分进行介绍:

  1. jiebaR,中文文本分词
  2. quanteda,量化文本分析

jiebaR 结巴分词(jiebaR),是一款高效的R语言中文分词包,底层使用的是C++,通过Rcpp进行调用很高效。结巴分词基于MIT协议,就是免费和开源的,感谢国人作者的给力支持,让R的可以方便的处理中文文本,具体使用可参考:http://blog.fens.me/r-word-jiebar/

quanteda,以一种新的方式用结构化数据的方式来管理文本。提出以语料库的形式管理文本,语料库被定义为文本的集合,其中包括特定每个文本的文档级变量,和整个集合的元数据。用户可以轻松地按单词、段落、句子甚至用户提供的分隔符分割文本和标签,按文档级变量将它们分组为更大的文档,形成基于逻辑条件的变量组合。具体使用可参考:http://blog.fens.me/r-word-quanteda/

2. 会议体验和照片分享

本次活动是微软直通车的活动,主要由微软MVP给大家进行一些技术分享,我们不讲虚的都是干活,边讲PPT,边撸代码。

本次活动的官方报名页面:https://www.huodongxing.com/event/2626606217622

2.1 会议主题

MVP嘉宾代表团:由 3位MVP组成,张丹,谢佳标,郝冠军。

张丹,用R语言进行量化文本分析,像结构化数据一样来管理文本PPT下载
在互联网的今天,我们每天都会生产和消费大量的文本信息,如报告、文档、新闻、聊天、图书、小说、语音转化的文字等。海量的文本信息,不仅提供扩宽的研究对象和研究领域,也为商业使用带来了巨大的机会。

量化文本分析(Quantitative Analysis of Textual Data),一种新的方式,用结构化数据的方式来管理文本。quanteda包,提出以语料库的形式管理文本,语料库被定义为文本的集合,其中包括特定每个文本的文档级变量,和整个集合的元数据。用户可以轻松地按单词、段落、句子甚至用户提供的分隔符分割文本和标签,按文档级变量将它们分组为更大的文档,形成基于逻辑条件的变量组合。

谢佳标,《Keras深度学习:入门、实战及进阶》PPT下载

Keras是一个对小白用户非常友好而简单的深度学习框架,它是TensorFlow高级集成API,其特点是能够快速实现模型的搭建,是高效地进行科学研究的关键。本主题将介绍如何进行图像及文本数据预处理,并介绍深度学习常用的DNN、CNN、RNN、GAN等模型原理及Keras案例实现。

郝冠军,在 .NET 6 中应用 OpenTelemetryPPT下载

可观察性是微服务化应用的几个核心特性,OpenTelemetry 延续了 OpenTracing 和 OpenCensus 的发展,成为 CNCF 的针对可观察性的新标准。
该分享将首先介绍 OpenTelemetry 的核心概念,微软作为 OpenTelemetry 的核心成员,.NET 平台对于 OpenTelemetry 提供了优异的支持,这里将基于 .NET 6 介绍如何应用 OpenTelemetry 到 .NET 项目中。

2.2 相关照片

张丹, R语言实践者,北京青萌数海科技有限公司CTO,微软MVP。

10年以上互联网应用架构经验,在R、Java、NodeJS、大数据、数据挖掘等方面有深厚的积累。精通量化投资交易策略,熟悉中国金融二级市场、交易规则和投研体系。 熟悉数据学科方法论,在外汇、海关、区块链等领域均有落地的应用。著有《R的极客理想:量化投资篇》、《R的极客理想:工具篇》、《R的极客理想:高级开发篇》,英文版图书被CRC出版集团引进,在美国发行。个人博客:http://fens.me 。

谢佳标,数据挖掘专家,资深AI技术专家和数据挖掘专家,拥有超过14年的技术研发和管理经验,在数据挖掘和人工智能领域有非常丰富的积累。连续6年(2017-2022)被微软评为最具价值专家(MVP),中国现场统计研究会大数据统计分会首届理事。

郝冠军,郝冠军 10 年微软最有价值专家,多年耕耘在开发前沿,《ASP.NET 本质论》作者,《精通 ASP.NET Core MVC》译者。

感觉线上的分享还是没有线下分享体验好,似乎少一些沟通的氛围。最后,整个分享结束,感谢组织者刘力科,感谢霸姐支持也都辛苦啦。

转载请注明出处:
http://blog.fens.me/meeting-ms-ignite-20211214

打赏作者

2020微软Virtual Azure Community Day-异常检测算法

跨界知识聚会系列文章,“知识是用来分享和传承的”,各种会议、论坛、沙龙都是分享知识的绝佳场所。我也有幸作为演讲嘉宾参加了一些国内的大型会议,向大家展示我所做的一些成果。从听众到演讲感觉是不一样的,把知识分享出来,你才能收获更多。

关于作者

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/meeting-ms-virtual-community-day-20201203

前言

由于疫情在全球肆虐,原来线下的各种会议都改成了线上的模式。微软及时做了线上的分享的调整,这次有幸参加微软的Virtual Community Day,做一次数据分析的主题演讲。除了我的分享,其他人都是.Net和Azure的主题,希望听众能接受这种跨领域的内容。

目录

  1. 我分享的主题:异常检测算法-自动发现数据中的异常值
  2. 会议体验和照片分享

1. 我分享的主题:异常检测算法-自动发现数据中的异常值

在处理时间序列数据时,经常会观测数据中有一个或几个数值与其他数值相比差异较大,或者在周期型的数据中出现了与周期性不相符的数据分布。通过异常检测算法,可以对不匹配预期的模式或异常数据进行识别,自动发现数据中的离群值、噪声值、偏差值等。

我分享主题:数据分析领域正在发生的变革

数据分析,作为大数据和人工智能的一个分支,正在各领域中发挥着作用。异常检测就是一种常见的,而且落地的一个AI的应用场景。本次我的分享也是从4个方面进行介绍,本次分享的PPT下载

我主要为分四个部分进行介绍:

  • 什么是异常检测?
  • 异常检测算法介绍
  • R语言算法实现
  • 现实场景应用

异常检测(Anomaly detection)是目前时序数据分析最成熟的应用之一,从正常的时间序列中识别不正常的事件或行为的过程。

常见的应用场景包括

  • 金融领域:从金融数据中识别”欺诈案例“,如识别信用卡申请欺诈、虚假信贷等;
  • 网络安全:从流量数据中找出”入侵者“,并识别新的网络入侵模式;
  • 电商领域:从交易数据中识别”恶意买家“,如羊毛党、恶意刷屏团伙;
  • 生态灾难预警:基于对风速、降雨量、气温等指标的预测,判断未来可能出现的极端天气;
  • 工业界:可通过异常检测手段进行工业产品的瑕疵检测,代替人眼进行测量和判断。

举例说明一下,下图就是一组时间序列数据,这些数据有趋势型的、周期型的、平稳型型的,蓝色的线是正常的数据,红色的点代表异常的数据。

  • 左上1图是趋势型的,红色的点,在数据趋势变动的过程中,出现了一种凸起。
  • 左下1图是平稳型的,红色的点,在数据点突然的变大,导致数据不平稳了。
  • 左下2图是周期型的,红色的点,出现的位置都是反周期的,导致数据局部反周期的异常。

这些数据异常的情况,在我们的现实生活中会经常的发生,通过算法来自动识别这样的异常,就可以大大解放人的工作,从而实现AI驱动。

2. 会议体验和照片分享

Virtual Azure Community Day全球直播又来啦,本次大会的官方页面:https://azureday.community/, 微信公众号地址:https://mp.weixin.qq.com/s/L2xDf1JIZsHYwJyEZ0wejA

2.1 会议主题

会议主题:从10:00开始 到 17:15,连续不间断。

2.2 相关照片

我在进行分享时候的屏幕截图,CSDN直播 热度1w 不知道是一个什么水平。

最后,整个分享结束,各位嘉宾都辛苦啦。

微软在越来越放开,融合各种技术,并且自己也在支持多种技术的融合和创新。同时,R语言做为数据分析的主要语言,一定会在各个领域中大有可为。

最后打个小广告:公司招聘!

转载请注明出处:
http://blog.fens.me/meeting-ms-virtual-community-day-20201203

打赏作者

打通Linux上的ODBC

R的极客理想系列文章,涵盖了R的思想,使用,工具,创新等的一系列要点,以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言,一直在小众领域闪耀着光芒。直到大数据的爆发,R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入,R语言的社区在迅速扩大成长。现在已不仅仅是统  计领域,教育,银行,电商,互联网….都在使用R语言。

要成为有理想的极客,我们不能停留在语法上,要掌握牢固的数学,概率,统计知识,同时还要有创新精神,把R语言发挥到各个领域。让我们一起动起来吧,开始R的极客理想。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/r-rodbc-linux/

前言

ODBC是由微软公司开发的,用于数据库连接的统一连接方案,在基于Windows的操作系统上被广泛地使用。在Linux上,其实也有ODBC的解决方案。今天我就来试试,在Linux上配置ODBC进行数据库的连接和交互。关于Window的ODBC解决方案,请参考文章用RODBC连接数据库

目录

  1. 在Linux在安装ODBC
  2. 通过RODBC连接PostgreSQL数据库
  3. 通过RODBC连接MySQL数据库

1. 在Linux在安装ODBC

我们先来整理一下ODBC在 Linux 环境中系统架构,我们需要找到Linux的ODBC软件程序 unixodbc ,unixodbc 就是解决方案核心的连接器。通过 unixodbc 一端绑定数据库,另一端绑定编程语言的API。

我在测试的过程中,ODBC只能是绑定本地的数据库,不能连接远程数据库,所以需要在本地计算机提前装好数据库。MySQL的数据库安装请参考文章在Ubuntu上安装MySQL,PostgreSQL的数据库安装请参考文章在Ubuntu上安装PostgreSQL

我们具体的操作步骤:

  • 第一步,在Linux中安装unixodbc驱动程序
  • 第二步,绑定MySQL/PostgreSQL数据库到unixodbc。
  • 第三步,用RDOBC与unixodbc建立连接

我使用Linux Ubuntu 操作系统,可以通过apt 来直接安装 unixodbc 软件包。

我的操作系统环境:

  • Linux Ubuntu 20.04.1 LTS 64bit
  • R version 3.6.3 64bit

在Linux中,安装unixodbc 软件,就是一条命令。


~ sudo apt install unixodbc

在odbc软件安装好后,在 /etc/目录下面,就可以看到2个文件,odbc.ini和odbcinst.ini文件。

  • odbc.ini,文件中主要是配置数据库连接,要手动配置
  • odbcinst.ini,配置数据库驱动文件,各数据库驱动安装时,自动配置。

通过odbcinst 的工具,来查看一下odbc程序的基本情况。


~ odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/conan/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

另外,可以通过isql的命令行工具,来测试一下odbc的数据库连接,后面配置好数据源后就可以进行测试。

打开odbc客户端 isql 。


~ isql
**********************************************
* unixODBC - isql                            *
**********************************************
* Syntax                                     *
*                                            *
*      isql DSN [UID [PWD]] [options]        *
*                                            *
* Options                                    *
*                                            *
* -b         batch.(no prompting etc)        *
* -dx        delimit columns with x          *
* -x0xXX     delimit columns with XX, where  *
*            x is in hex, ie 0x09 is tab     *
* -w         wrap results in an HTML table   *
* -c         column names on first row.      *
*            (only used when -d)             *
* -mn        limit column display width to n *
* -v         verbose.                        *
* -lx        set locale to x                 *
* -q         wrap char fields in dquotes     *
* -3         Use ODBC 3 calls                *
* -n         Use new line processing         *
* -e         Use SQLExecDirect not Prepare   *
* -k         Use SQLDriverConnect            *
* -L         Length of col display (def:300) *
* --version  version                         *
*                                            *
* Commands                                   *
*                                            *
* help - list tables                         *
* help table - list columns in table         *
* help help - list all help options          *
*                                            *
* Examples                                   *
*                                            *
*      isql WebDB MyID MyPWD -w < My.sql     *
*                                            *
*      Each line in My.sql must contain      *
*      exactly 1 SQL command except for the  *
*      last line which must be blank (unless *
*      -n option specified).                 *
*                                            *
* Please visit;                              *
*                                            *
*      http://www.unixodbc.org               *
*      nick@lurcher.org                      *
*      pharvey@codebydesign.com              *
**********************************************

装完ODBC后,我们就可以尝试把不同的数据库和ODBC进行绑定,再通过程序来连接ODBC,从而实现对数据库的访问。

2. 通过RODBC连接PostgreSQL数据库

我们开始配置PostgreSQL数据库,进行ODBC数据源的绑定。

2.1 配置PostgreSQL的ODBC数据源

安装PostgresSQL在Ubuntu上的odbc驱动


~ sudo apt install odbc-postgresql

安装后驱动程序后,我们会发现在 /etc/odbcinst.ini 文件中,已经配置了好PostgreSQL 驱动程序信息。

查看文件 /etc/odbcinst.ini


~ cat /etc/odbcinst.ini
[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

之后,我们需要修改 /etc/odbc.ini,把PostgreSQL数据库绑定到ODBC数据源中,就可以使用ODBC了。

编辑文件 /etc/odbc.ini,


~ vi /etc/odbc.ini

[postgres01]
Description = post01
Driver      = PostgreSQL Unicode
Database = testdb
Servername = 127.0.0.1
UserName = test
Password = test
Port = 5432
ReadOnly = 0
ConnSettings = set client_encoding to UTF8

参数解释

  • [postgres01]:为用户DNS,这个名称是用于配置到编程语言程序中的。
  • Driver: PostgreSQL Unicode,驱动名词,这个名词为 /etc/odbcinst.ini 所对应的驱动名词。
  • Database :testdb,PostgreSQL的数据库名称
  • Servername : 127.0.0.1,PostgreSQL的数据库服务器IP
  • UserName: test,PostgreSQL的数据库用户名
  • Password: test,PostgreSQL的数据库用户密码
  • Port: 5432,PostgreSQL的数据库端口
  • ReadOnly: 0,是否是只读,0不是,1是
  • ConnSettings :客户端的连接字符编码

用命令检测数据源是否配置成功


~ isql -v postgres01
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

2.2 用RODBC进行数据库连接

RODBC的安装,比较简单,在R语言环境中用install.packages(“RODBC”)就能成功安装了。也可以参考Window的ODBC解决方案,请参考文章用RODBC连接数据库

下面我们在Ubuntu的R语言环境中调用ODBC。


# 安装RODBC
> install.packages("RODBC")
> library(RODBC)

建立ODBC的数据库连接。


# 建立连接
> o1<-odbcConnect("postgres01")

# 查看连接对象
> o1
RODBC Connection 6
Details:
  case=tolower
  DSN=postgres01
  DATABASE=testdb
  SERVER=127.0.0.1
  PORT=5432
  UID=test
  PWD=******
  SSLmode=disable
  ReadOnly=0
  Protocol=7.4
  FakeOidIndex=0
  ShowOidColumn=0
  RowVersioning=0
  ShowSystemTables=0
  Fetch=100
  UnknownSizes=0
  MaxVarcharSize=255
  MaxLongVarcharSize=8190
  Debug=0
  CommLog=0
  UseDeclareFetch=0
  TextAsLongVarchar=1
  UnknownsAsLongVarchar=0
  BoolsAsChar=1
  Parse=0
  ExtraSysTablePrefixes=
  LFConversion=0
  UpdatableCursors=1
  TrueIsMinus1=0
  BI=0
  ByteaAsLongVarBinary=1
  UseServerSidePrepare=1
  LowerCaseIdentifier=0
  D6=-101

# 查看连接信息
> odbcGetInfo(o1)
       DBMS_Name         DBMS_Ver  Driver_ODBC_Ver Data_Source_Name
    "PostgreSQL"         "12.0.4"          "03.51"     "postgres01"
     Driver_Name       Driver_Ver         ODBC_Ver      Server_Name
  "psqlodbcw.so"     "12.01.0000"          "03.52"      "127.0.0.1"

接下来,做一下数据库的操作。


# 查看数据库中的所有表的列表
> sqlTables(o1)
  TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1    testdb      public        acc      TABLE
2    testdb      public    account      TABLE
3    testdb      public       iris      TABLE

# 读取iris表
> idat<-sqlFetch(o1, "iris");idat
  sepallength sepalwidth petallength petalwidth species
1         5.1        3.5         1.4        0.2  setosa
2         4.9        3.0         1.4        0.2  setosa
3         4.7        3.2         1.3        0.2  setosa
4         4.6        3.1         1.5        0.2  setosa
5         5.0        3.6         1.4        0.2  setosa

# 增加插入数据
> iris[6:7,]
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
6          5.4         3.9          1.7         0.4  setosa
7          4.6         3.4          1.4         0.3  setosa

# 执行插入命令
> sqlSave(o1, iris[6:7,] ,"iris",append=TRUE)
> sqlFetch(o1, "iris")
  sepallength sepalwidth petallength petalwidth species
1         5.1        3.5         1.4        0.2  setosa
2         4.9        3.0         1.4        0.2  setosa
3         4.7        3.2         1.3        0.2  setosa
4         4.6        3.1         1.5        0.2  setosa
5         5.0        3.6         1.4        0.2  setosa
6         5.4        3.9         1.7        0.4  setosa
7         4.6        3.4         1.4        0.3  setosa

2.3 错误排查

用RODBC进行连接时,可能会报这个错误,主要是 /etc/odbc.ini 文件中的Driver没有写对造成的,详细核对一下配置就行了。


> o1<-odbcConnect("postgres01")
Warning messages:
1: In RODBC::odbcDriverConnect("DSN=postgres01") :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In RODBC::odbcDriverConnect("DSN=postgres01") : ODBC connection failed

3. 通过RODBC连接MySQL数据库

同样的步骤,我们先安装Linux ubuntu中MySQL的ODBC驱动程序,

3.1 配置MySQL的ODBC数据源

首先,安装MySQL的ODBC恭驱动。在apt源中,找到mysql-connector-odbc的包尝试安装,但会出现错误。


# 从apt源中安装mysql odbc
~ sudo apt install mysql-connector-odbc
Reading package lists... Done
Building dependency tree
Reading state information... Done
Package mysql-connector-odbc is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source

E: Package 'mysql-connector-odbc' has no installation candidate

安装失败,我们就需要换一种方式进行安装。从MySQL官网手动下载MySQL驱动 https://dev.mysql.com/downloads/connector/odbc/ 上传到服务器/home/conan/soft 目录,我下载的包为 mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit.tar.gz

上传到/home/conan/soft 目录,进行解压。


~ cd /home/conan/soft
~ tar xvf mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit.tar.gz
~ cd  mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit

在 /usr/lib 下面新建mysql-odbc目录,把mysql-odbc类库复制过来。


~ sudo mkdir /usr/lib/mysql-odbc
~ sudo cp -r /home/conan/soft/mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit/lib/* /usr/lib/mysql-odbc

通过myodbc-installer命令,手动安装mysql-odbc的驱动,指定libmyodbc8w.so库对应的位置。


 ~ sudo bin/myodbc-installer -d -a -n "MySQL ODBC 8.0 Unicode Driver" -t "DRIVER=/usr/lib/mysql-odbc/libmyodbc8w.so"
Success: Usage count is 1

安装完成后,在 odbcinst.ini 文件中,会自动创建MySQL的驱动,建立和ODBC的连接。

查看odbcinst.ini文件,增加了 [MySQL ODBC 8.0 Unicode Driver] 的驱动声明。


~ cat /etc/odbcinst.ini

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib/mysql-odbc/libmyodbc8w.so
UsageCount=1

接下来,我们配置MySQL数据库,与ODBC进行连接。编辑odbc.ini文件,增加[mysql01]的DNS命名。


[mysql01]
Description = post01
Driver      = MySQL ODBC 8.0 Unicode Driver
Database = world
Servername = 192.168.1.6
UserName = root
Password = bsspirit
Port = 3306
ReadOnly = 0
ConnSettings = set client_encoding to UTF8

用isql检查一下,ODBC是否配置成功。


~ sudo isql -v mysql01
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from aa;
+-----------+
| id        |
+-----------+
| 1         |
| 2         |
+-----------+
SQLRowCount returns 2
2 rows fetched
SQL>

3.2 用RODBC进行数据库连接


# 加载程序包
> library(RODBC)

# 连接ODBC
> o2<-odbcConnect("mysql01")

# 查看连接信息
> o2
RODBC Connection 1
Details:
  case=nochange
  DSN=mysql01
> odbcGetInfo(o2)
                DBMS_Name                  DBMS_Ver           Driver_ODBC_Ver
                  "MySQL" "8.0.22-0ubuntu0.20.04.2"                   "03.80"
         Data_Source_Name               Driver_Name                Driver_Ver
                "mysql01"          "libmyodbc8w.so"              "08.00.0022"
                 ODBC_Ver               Server_Name
                  "03.52"    "localhost via TCP/IP"

最后,我们进行一些数据库操作,来验证一下数据库连接成功。


# 查询数据表
> sqlFetch(o2, "aa")
  id
1  1
2  2

# 查入数据表
> sqlQuery(o2,"insert into aa values(3)")
character(0)

# 查询数据表
> sqlFetch(o2, "aa")
  id
1  1
2  2
3  3

经过测试我们就完成了RODBC在Linux上的数据连接操作,这样就可以方便地解决数据库多样性的问题,不仅能让代码保持一致的风格,而且对于复杂的配置过程也可以一次性的解决,是一个不错的方案。

转载请注明出处:
http://blog.fens.me/r-rodbc-linux/

打赏作者

用RODBC连接数据库

R的极客理想系列文章,涵盖了R的思想,使用,工具,创新等的一系列要点,以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言,一直在小众领域闪耀着光芒。直到大数据的爆发,R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入,R语言的社区在迅速扩大成长。现在已不仅仅是统  计领域,教育,银行,电商,互联网….都在使用R语言。

要成为有理想的极客,我们不能停留在语法上,要掌握牢固的数学,概率,统计知识,同时还要有创新精神,把R语言发挥到各个领域。让我们一起动起来吧,开始R的极客理想。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/r-rodbc/

前言

在进行数据开发的时候,数据源经常是多样,有时候是Oracle,有时候是MySQL,有时候是PostgreSQL等,不同的数据库有不同的特征和不同的API。很难保证,开发人员对每种数据库都熟悉,而且一个项目如果使用超过3个数据库,程序复杂度会骤然升高。

微软提供了一个基于Window的数据开放接口统一方案就是ODBC,让所有的数据库都通过ODBC进行注册统一管理,我们只需要基于ODBC进行编程,就可以连接所有数据库了。RODBC包,则作为R语言连接ODBC的数据访问接口程序。

目录

  1. ODBC和RODBC包介绍
  2. 通过RODBC连接MySQL数据库
  3. 通过RODBC连接PostgreSQL数据库

1. ODBC和RODBC包介绍

ODBC(Open Database Connectivity,开放数据库互连),是微软公司开放服务结构(WOSA,Windows Open Services Architecture)中有关数据库的一个组成部分,它建立了一组规范,并提供了一组对数据库访问的标准API(应用程序编程接口)。这些API利用SQL来完成其大部分任务。ODBC本身也提供了对SQL语言的支持,用户可以直接将SQL语句送给ODBC。一组数据的位置,可以使用 ODBC驱动程序访问该位置。

一个基于ODBC的应用程序对数据库的操作不依赖任何DBMS,不直接与DBMS打交道,所有的数据库操作由对应的DBMS的ODBC驱动程序完成。也就是说,不论是MySQL 还是 Oracle数据库,均可用ODBC API进行访问。由此可见,ODBC的最大优点是能以统一的方式处理所有的数据库。

RODBC包,为R语言实现了ODBC数据库连接。在RODBC中提供了两种主要功能函数,主要是内部 odbc* 开头的函数,命令使用相似的名称实现对C语言级别低级访问。 sql*开头函数,在更高级别上运行以读取,保存,复制和操作数据之间的数据框架和SQL表。

2. 通过RODBC连接MySQL数据库

我们尝试在Window操作系统进行ODBC的连接。

Window系统的环境:

  • win10 64bit professional
  • R version 3.6.1 64bit

2.1 配置ODBC数据源

首先,需要安装MySQL,具体安装过程请参考文章在Ubuntu中安装MySQL

我们需要在win10操作中先配置MySQL到ODBC连接,所以需要安装ODBC的驱动程序,请下载Connector/ODBC,然后安装。

打开 “ODBC数据源管理器” 界面,配置MySQL到ODBC连接。

选 用户DSN ,然后 添加 一个配置,会弹出选择数据源驱动程序,这个驱动是需要安装的,用来让数据库可以和ODBC进行连接。Window系统中,通常会默认支持MS SQL Server数据库。

选择 MySQL ODBC 后,输入MySQL连接的配置信息。

这样,MySQL和ODBC的连接就算是建立好了,我们就可以用R语言通过RODBC的通道和MySQL数据库进行通信了。DSN就是外部调用的名字。

2.2 安装RODBC包

接下来,我们安装RODBC,非常简单,一条语句就行了。

安装和加载RPostgreSQL包

> install.packages("RODBC")
> library(RODBC)

2.3 RODBC包API操作

基本的ODBC的API操作。

  • odbcConnect(“dsn”, uid=”username”, pwd=”password”),建立ODBC数据库连接
  • sqlTables(channel),查看数据库中的表
  • sqlFetch(channel, sqtable), 读取数据表并返回一个数据框
  • sqlQuery(channel, query), 向数据库提交一个查询,并返回结果
  • sqlSave(channel, mydf, tablename = sqtable, append = FALSE), 将一个数据框写入或更新(append=True)到数据库
  • sqlColumns(channel, sqtable) , 返回数据库表sqtable列的信息
  • sqlDrop(channel, sqtable), 从数据库删除一个表
  • sqlClear(channel, sqtable), 删除表中的内容
  • close(channel), 关闭连接

2.4 建立ODBC数据库连接

接下来,我们就可以使用RODBC进行ODBC的数据源连接了。


# 查看ODBC支持的数据源
> odbcDataSources()
                          mysql 
"MySQL ODBC 8.0 Unicode Driver" 

# 建立ODBC连接,设置DSN名字
> ocon1<-odbcConnect("mysql")

# 查看连接信息
> ocon1
RODBC Connection 4
Details:
  case=tolower
  DSN=mysql

# 查看连接信息
> odbcGetInfo(ocon1)
             DBMS_Name               DBMS_Ver        Driver_ODBC_Ver       Data_Source_Name            Driver_Name 
               "MySQL"               "8.0.19"                "03.80"                "mysql"         "myodbc8w.dll" 
            Driver_Ver               ODBC_Ver            Server_Name 
          "08.00.0019"           "03.80.0000" "localhost via TCP/IP" 

2.5 查看数据库基本信息

查看数据库中所有表


# 使用SQL,查看数据库中所有的表
> sqlQuery(ocon1, "SHOW TABLES")
  Tables_in_world
1            city
2           city2
3         country
4 countrylanguage

# 通过函数,查看数据库有所有的表
> sqlTables(ocon1)
  TABLE_CAT TABLE_SCHEM      TABLE_NAME TABLE_TYPE REMARKS
1     world                        city      TABLE        
2     world                       city2      TABLE        
3     world                     country      TABLE        
4     world             countrylanguage      TABLE        

2.5 查看数据表的结构基本信息

查看数据表的结构


# 用SQL查看city表数据结构
> sqlQuery(ocon1, "DESCRIBE city")
        Field     Type Null Key Default          Extra
1          ID      int   NO PRI      NA auto_increment
2        Name char(35)   NO          NA               
3 CountryCode  char(3)   NO MUL      NA               
4    District char(20)   NO          NA               
5  Population      int   NO           0    

# 用函数,查看city表数据结构
> sqlColumns(ocon1, "city")
  TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE
1                  <na>       city          ID         4   integer          10             4              0             10        1
2                  <na>       city        Name         1      char          35            35             NA             NA        0
3                  <na>       city CountryCode         1      char           3             3             NA             NA        0
4                  <na>       city    District         1      char          20            20             NA             NA        0
5                  <na>       city  Population         4   integer          10             4              0             10        0
  REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
1                  0             4               NA                NA                1         YES
2                 ''             1               NA                35                2          NO
3                 ''             1               NA                 3                3          NO
4                 ''             1               NA                20                4          NO
5                  0             4               NA                NA                5          NO

2.6 用SQL执行查询

通常的数据库操作,我们都是基于SQL语句的,可以使用sqlQuery操作进行查询。


> sqlQuery(ocon1, paste("SELECT Code,Name,Continent,Region,GNP FROM country WHERE GNP > 500000 ORDER BY GNP DESC"))
   Code           Name     Continent          Region     GNP
1   USA  United States North America   North America 8510700
2   JPN          Japan          Asia    Eastern Asia 3787042
3   DEU        Germany        Europe  Western Europe 2133367
4   FRA         France        Europe  Western Europe 1424285
5   GBR United Kingdom        Europe British Islands 1378330
6   ITA          Italy        Europe Southern Europe 1161755
7   CHN          China          Asia    Eastern Asia  982268
8   BRA         Brazil South America   South America  776739
9   CAN         Canada North America   North America  598862
10  ESP          Spain        Europe Southern Europe  553233

2.7 批量数据操作

R语言中,经常会有对数据批量处理,ODBC的API也提供了,批量进行增、删、改、查操作的API函数。


# 增加,将一个数据框写入或更新
> sqlSave(ocon1, iris[1:5,] ,"iris")

# 查询
> sqlFetch(ocon1, "iris")
  sepallength sepalwidth petallength petalwidth species
1         5.1        3.5         1.4        0.2  setosa
2         4.9        3.0         1.4        0.2  setosa
3         4.7        3.2         1.3        0.2  setosa
4         4.6        3.1         1.5        0.2  setosa
5         5.0        3.6         1.4        0.2  setosa

# 增量写入
> sqlSave(ocon1, iris[6:10,] ,"iris", append=TRUE)

# 查询
> df1<-sqlFetch(ocon1, "iris");df1
   sepallength sepalwidth petallength petalwidth species
1          5.1        3.5         1.4        0.2  setosa
2          4.9        3.0         1.4        0.2  setosa
3          4.7        3.2         1.3        0.2  setosa
4          4.6        3.1         1.5        0.2  setosa
5          5.0        3.6         1.4        0.2  setosa
6          5.4        3.9         1.7        0.4  setosa
7          4.6        3.4         1.4        0.3  setosa
8          5.0        3.4         1.5        0.2  setosa
9          4.4        2.9         1.4        0.2  setosa
10         4.9        3.1         1.5        0.1  setosa

# 修改
> df1[,1]<-rnorm(1:10)
> df2<-df1[2:4,];df2
  sepallength sepalwidth petallength petalwidth species
2  -0.1721062        3.0         1.4        0.2  setosa
3   1.2726538        3.2         1.3        0.2  setosa
4   0.4437929        3.1         1.5        0.2  setosa
> sqlUpdate(ocon1, df2, "iris")

# 查看2-4行的修改结果
> sqlFetch(ocon1, "iris")
   sepallength sepalwidth petallength petalwidth species
1    5.1000000        3.5         1.4        0.2  setosa
2   -0.1721062        3.0         1.4        0.2  setosa
3    1.2726538        3.2         1.3        0.2  setosa
4    0.4437929        3.1         1.5        0.2  setosa
5    5.0000000        3.6         1.4        0.2  setosa
6    5.4000000        3.9         1.7        0.4  setosa
7    4.6000000        3.4         1.4        0.3  setosa
8    5.0000000        3.4         1.5        0.2  setosa
9    4.4000000        2.9         1.4        0.2  setosa
10   4.9000000        3.1         1.5        0.1  setosa

# 清空内数据
> sqlClear(ocon1, "iris")
> sqlFetch(ocon1, "iris")
[1] sepallength sepalwidth  petallength petalwidth  species    
<0 行> (或0-长度的row.names)

# 删除表
> sqlDrop(ocon1, "iris", errors = FALSE)
> sqlFetch(ocon1, "iris")
Error in odbcTableExists(channel, sqtable) : 
  ‘iris’: table not found on channel

2.8 事务处理

事务操作,默认的ODBC数据库连接的自动提交模式,必须手动设置了autoCommit = FALSE 后在odbcSetAutoCommit()函数中,才会进行手动管理。 一个事务完成后,还需要再次调用odbcSetAutoCommit()函数。进行下一条语句的事务管理。


# 设置
> odbcSetAutoCommit(ocon1,autoCommit = FALSE)
[1] 0

# 创建表
> sqlSave(ocon1, iris[1:5,] ,"iris")

# 执行查询,表示已经成功
> sqlFetch(ocon1, "iris")
  sepallength sepalwidth petallength petalwidth species
1         5.1        3.5         1.4        0.2  setosa
2         4.9        3.0         1.4        0.2  setosa
3         4.7        3.2         1.3        0.2  setosa
4         4.6        3.1         1.5        0.2  setosa
5         5.0        3.6         1.4        0.2  setosa

# 事务不提交,则进行回滚
> odbcEndTran(ocon1,commit = FALSE)
[1] 0

# 再次查询时,表中没有数据。
> sqlFetch(ocon1, "iris")
[1] sepallength sepalwidth  petallength petalwidth  species    
<0 行> (或0-长度的row.names)

从上面的例子中,我们可以看到事务可以控制数据的插入(DML)操作,但不能控制表的创建(DDL)。

最后,关闭数据库连接。


# 关闭单个数据库连接
> odbcClose(ocon1)

# 关闭所有数据库连接
> odbcCloseAll()

# 查看连接信息
> odbcGetInfo(ocon1)
Error in odbcGetInfo(ocon1) : argument is not an open RODBC channel

3. 通过RODBC连接PostgreSQL数据库

整个过程与上文中MySQL的过程类似,简略介绍。首先,安装PostgreSQL数据库,我在远程服务器进行了安装,安装过程请参考文章在Ubuntu上安装PostgreSQL

下载window上的postgreSQL的ODBC数据源驱动psqlODBC ,https://odbc.postgresql.org/

下载后进行驱动程序的安装,然后在ODBC的驱动程序的管理控制台,可以查看刚安装的PostgreSQL ODBC 驱动程序。

为postgreSQL数据库,添加 用户DSN 。

PostgreSQL数据库的配置信息。

接下为,用R语言连接 配置好的ODBC 的数据源。


# 加载RODBC包
> library(RODBC)

# 查看odbc数据源支持,这时又多了一个postgresql的数据源
> odbcDataSources()
                          mysql                      postgresql 
"MySQL ODBC 8.0 Unicode Driver"       "PostgreSQL Unicode(x64)" 

# 建立数据连接
> ocon2<-odbcConnect("postgresql")

# 查看连接信息
> odbcGetInfo(ocon2)
        DBMS_Name          DBMS_Ver   Driver_ODBC_Ver  Data_Source_Name       Driver_Name        Driver_Ver          ODBC_Ver 
     "PostgreSQL"          "12.0.4"           "03.51"      "postgresql" "PSQLODBC35W.DLL"      "12.02.0000"      "03.80.0000" 
      Server_Name 
    "192.168.1.5" 

# 查看数据库中,所有数所表
> sqlTables(ocon2)
  TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1    testdb      public        acc      TABLE        
2    testdb      public    account      TABLE        
3    testdb      public       iris      TABLE        

# 读取account表的数据
> sqlFetch(ocon2, "account")
  user_id username password
1       3       a3     密码
2       5       fd   mddddd

# 关闭数据连接
> odbcClose(ocon1)

在我们配置好了 ODBC 数据源后,从R的代码的角度,对MySQL和PostgreSQL的数据库操作API都是一致的,都可以基本RODBC包提供的API来完成。ODBC很好的解决了,不同数据库之间数据一致性访问的问题。

转载请注明出处:
http://blog.fens.me/r-rodbc/

打赏作者

RPostgreSQL数据库编程指南

R的极客理想系列文章,涵盖了R的思想,使用,工具,创新等的一系列要点,以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言,一直在小众领域闪耀着光芒。直到大数据的爆发,R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入,R语言的社区在迅速扩大成长。现在已不仅仅是统计领域,教育,银行,电商,互联网….都在使用R语言。

要成为有理想的极客,我们不能停留在语法上,要掌握牢固的数学,概率,统计知识,同时还要有创新精神,把R语言发挥到各个领域。让我们一起动起来吧,开始R的极客理想。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/r-rpostgressql/

前言

PostgreSQL和R通常可以一起用于数据分析,PostgreSQL作为数据库引擎,R作为统计工具。在R语言中,有2个包可以直接通过R连接PostgreSQL数据库,分别是RPostgreSQL包和RPostgres包,本文将先介绍用RPostgreSQL包进行直接连接PostgreSQL数据库。间接的连接方法还有RJDBC,RODBC等,以后再有专门的文章进行介绍。

PostgreSQL的数据库安装和基本信息,请参考文章在Ubuntu上安装PostgreSQL

目录

  1. RPostgreSQL包介绍
  2. 安装RPostgreSQL包
  3. RPostgreSQL包的API使用
  4. window客户端环境的中文乱码解决方案

1. RPostgreSQL包介绍

RPostgreSQL一个R语言程序包,由官方提供访问 PostgreSQL数据库的R语言接口程序,RPostgreSQL主要依赖于DBI项目,以访问 PostgreSQL 数据库系统。CRAN中的项目地址
https://cran.r-project.org/web/packages/RPostgreSQL/index.html

2. 安装RPostgreSQL包

RPostgreSQL包的安装过程非常简单,就是一条语句就能够搞定了,不管是在Window环境还是Linux环境。

在Linux Ubuntu中,需要先安装libpq-dev系统库,不然会出现libpq-fe.h错误


In file included from RS-PQescape.c:7:
RS-PostgreSQL.h:23:14: fatal error: libpq-fe.h: No such file or directory
   23 | #    include "libpq-fe.h"
      |              ^~~~~~~~~~~~

安装libpq-dev系统库


~ sudo apt install libpq-dev

安装和加载RPostgreSQL包


> install.packages("RPostgreSQL")
> library(RPostgreSQL)

3. RPostgreSQL包的API使用

RPostgreSQL包,提供了很多的API函数,API的使用过程,我们可以分成6个部分来介绍。

  • 3.1 数据库连接
  • 3.2 数据库状态查看
  • 3.3 数据直接查询
  • 3.4 数据交互查询
  • 3.5 数据批量插入
  • 3.6 事务处理

3.1 建立数据库链接


# 加载PostgreSQL驱动
> drv = dbDriver("PostgreSQL") 

# 建立数据库连接
> con1 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

3.2 数据库状态查看


# 再建立2个数据库连接
> con2 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")
> con3 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

查看驱动状态


> dbGetInfo(drv)
$drvName
[1] "PostgreSQL"

$connectionIds                   # 连接1
$connectionIds[[1]]
<postgresqlconnection>

$connectionIds[[2]]              # 连接2
<postgresqlconnection>

$connectionIds[[3]]              # 连接3
<postgresqlconnection>

$fetch_default_rec
[1] 500

$managerId
<postgresqldriver>

$length
[1] 16

$num_con
[1] 3

$counter
[1] 9

查看con1数据连接状态


> dbGetInfo(con1)
$host
[1] "192.168.1.5"

$port
[1] "5432"

$user
[1] "test"

$dbname
[1] "testdb"

$serverVersion
[1] "12.0.4"

$protocolVersion
[1] 3

$backendPId
[1] 27263

$rsId
list()

关闭连接


# 关闭连接1
> dbDisconnect(con1)
[1] TRUE

# 查看con1状态
> isPostgresqlIdCurrent(con1)
[1] FALSE

# 查睦con1信息
> dbGetInfo(con1)
Error in postgresqlConnectionInfo(dbObj, ...) : 
  expired PostgreSQLConnection dbObj

关闭全部连接


# 通过循环关闭全部连接
> for(con in dbListConnections(drv)){
+   dbDisconnect(con)
+ }

# 查询驱动状态,没有活动连接
> dbGetInfo(drv)
$drvName
[1] "PostgreSQL"

$connectionIds   # 没有活动连接
list()

$fetch_default_rec
[1] 500

$managerId
<postgresqldriver>

$length
[1] 16

$num_con
[1] 0

$counter
[1] 9

3.3 数据直接查询

数据直接查询可以通过直接读取整个表数据dbReadTable()函数,或者执行查询语句dbGetQuery()函数来实现。

建立新连接


> con1 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

查看数据库中,所有的表。


# 所有表的列表
> dbListTables(con1)
[1] "account" "iris"  

# 确认表名是否存在
> dbExistsTable(con1,"iris")
[1] TRUE

通过表名,读取整个表的数据


> dbReadTable(con1,"iris")
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa

通过SQL语句,读取表中的数据


> dbGetQuery(con1,  "SELECT * FROM iris")
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

3.4 数据交互查询

数据交互查询,可以通过发送一条sql请求dbSendQuery()函数来实现,返回数据访问句柄,再用dbFetch()函数来分批次获取数据。


# 发送SQL
> rs = dbSendQuery(con1,  statement  =  "SELECT * FROM iris")

# 判断rs句柄是否完成
> dbHasCompleted(rs)
[1] FALSE

# 取数据n=-1时,取全表数据
> dbFetch(rs,n=-1)
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

# 判断rs句柄是否完成
> dbHasCompleted(rs)
[1] TRUE

# 取完数据后,句柄rs就失效了
> dbFetch(rs,n=1)
data frame with 0 columns and 0 rows

我们在获得句柄后,可以查到关于句柄的详细信息。


# 发送SQL,获得句柄rs
> rs = dbSendQuery(con1,  statement  =  "SELECT * FROM iris")

# 查看rs信息
> dbGetInfo(rs)
$statement
[1] "SELECT * FROM iris"

$isSelect
[1] 1

$rowsAffected
[1] -1

$rowCount
[1] 0

$completed
[1] 0

$fieldDescription
$fieldDescription[[1]]
$fieldDescription[[1]]$name
[1] "row.names"    "Sepal.Length" "Sepal.Width"  "Petal.Length" "Petal.Width"  "Species"     

$fieldDescription[[1]]$Sclass
[1] 16 14 14 14 14 16

$fieldDescription[[1]]$type
[1]  25 701 701 701 701  25

$fieldDescription[[1]]$len
[1] -1  8  8  8  8 -1

$fieldDescription[[1]]$precision
[1] -1 -1 -1 -1 -1 -1

$fieldDescription[[1]]$scale
[1] -1 -1 -1 -1 -1 -1

$fieldDescription[[1]]$nullOK
[1] TRUE TRUE TRUE TRUE TRUE TRUE

# 查看执行SQL语句
> dbGetStatement(rs)
[1] "SELECT * FROM iris"

# 查看数据结构
> dbColumnInfo(rs)
          name    Sclass   type len precision scale nullOK
1    row.names character   TEXT  -1        -1    -1   TRUE
2 Sepal.Length    double FLOAT8   8        -1    -1   TRUE
3  Sepal.Width    double FLOAT8   8        -1    -1   TRUE
4 Petal.Length    double FLOAT8   8        -1    -1   TRUE
5  Petal.Width    double FLOAT8   8        -1    -1   TRUE
6      Species character   TEXT  -1        -1    -1   TRUE

# 查看已获取的行
> dbGetRowCount(rs)
[1] 0

# 获取2行
> ret1 <- dbFetch(rs, 2)
> ret1
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa

# 查看已获取的行
> dbGetRowCount(rs)
[1] 2

# 查看句柄是否完成
> dbHasCompleted(rs)
[1] FALSE

# 获取剩余的行
> ret2 <- dbFetch(rs, -1)
> ret2
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

# 查看句柄是否完成
> dbHasCompleted(rs)
[1] TRUE

# 清空rs句柄
> dbClearResult(rs)
[1] TRUE

# 拼接完整数据集
> rbind(ret1,ret2)
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

3.5 数据批量插入
R语言中,数据组织方式是用data.frame的,data.frame可以批量地进行数据操作,在操作数据库时,我们也可以直接用data.frame与PostgreSQL数据库进行这种批量的操作。

当数据库没表时,dbWriteTable()函数会新建一个数据表。


# 新建数据表iris-table,插入5条数据
> dbWriteTable(con1,'iris-table',iris[1:5,])
[1] TRUE

# 读取数据表
> dbReadTable(con1,'iris-table')
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa

再进行数据表插入,由于表已存在,所以提示不能插入,如果需求追加插入时,可以增加append的合并参数。


# 插入数据失败
> dbWriteTable(con1,'iris-table',iris[6:10,])
[1] FALSE
Warning message:
In postgresqlWriteTable(conn, name, value, ...) :
  table iris-table exists in database: aborting assignTable

# 追加插入数据
> dbWriteTable(con1,'iris-table',iris[6:10,],append=TRUE)
[1] TRUE

# 查看数据表,变成了10行。
> dbReadTable(con1,'iris-table')
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1           5.1         3.5          1.4         0.2  setosa
2           4.9         3.0          1.4         0.2  setosa
3           4.7         3.2          1.3         0.2  setosa
4           4.6         3.1          1.5         0.2  setosa
5           5.0         3.6          1.4         0.2  setosa
6           5.4         3.9          1.7         0.4  setosa
7           4.6         3.4          1.4         0.3  setosa
8           5.0         3.4          1.5         0.2  setosa
9           4.4         2.9          1.4         0.2  setosa
10          4.9         3.1          1.5         0.1  setosa

当表不需要后,可以直接删除数据表


# 删除数据表
> dbRemoveTable(con1,"iris-table")
[1] TRUE

# 删除后,数据表读取失败
> dbReadTable(con1,'iris-table')
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  relation "iris-table" does not exist
LINE 1: SELECT * from "iris-table"
                      ^
)
Error in names(out) <- make.names(names(out), unique = TRUE) : 
  NULL是不能有属性的
此外: Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create execute: SELECT * from "iris-table"

3.6 事务提交和回滚
RPostgreSQL包的事务控制,是通过dbSendQuery()函数,配合事务控制dbBegin()函数,dbRollback()函数,dbCommit()函数来完成的。

我们模拟一个事务的场景,当删除一条数据时,判断这条数据影响多少行,如果删除超过1行则不执行删除进行回滚,如果正好为1行,则进行删除。


# 查看数据集,共5行
> df1<-dbGetQuery(con1, "select * from iris");df1
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa


# 开始事务
> dbBegin(con1)
[1] TRUE

# 进行删除
> rs <- dbSendQuery(con1, "Delete from iris where \"Species\" = 'setosa'")

# 删除判断,影响大于1行则回滚,
> if(dbGetInfo(rs, what = "rowsAffected") > 1){
+   warning("Rolling back transaction")
+   dbRollback(con1)
+ }else{
+   dbCommit(con1)
+ }
[1] TRUE
Warning message:
Rolling back transaction           # 进行回滚

# 再查询数据表结果,未发生删除
> dbGetQuery(con1, "select count(*) from iris")
  count
1     5

4. window客户端环境的中文乱码解决方案

执行查询,出现中文乱码。我们就需要逐步排查,到底是哪个环节产生了乱码,统一把编码以UTF-8做为编码标准。


# 创建中文data.frame
> acc<-data.frame(
+   user_id=1:3,
+   useranme=c("a1","小明","粉丝日志"),
+   password=c("678fdaiufda","jfdaked21+_~!","E!SM<I*")
+ )

# 建表,插入数据
> dbWriteTable(con1,"acc",acc)
[1] TRUE

# 读取数据
> df3<-dbReadTable(con1,"acc");df3
  user_id     useranme      password
1       1           a1   678fdaiufda
2       2       灏忔槑 jfdaked21+_~!
3       3 绮変笣鏃ュ織       E!SM<I*

我们插入的是正确,但读出来的数据是乱码。我们用pgadmin客户端,检查一下数据表,验证一下数据库中数据是否显示正常。

接下来,再检查一下PostgreSQL数据库中testdb库的编码为UTF8。

在服务器上,启动psql查询客户端。


# 启动psql客户端
~ sudo -u postgres psql

# 检查数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | test=CTc/postgres
(4 rows)

再检查一下,客户端通信的字符编码,也是UTF8是正常的。


> dbGetQuery(con1, "SHOW CLIENT_ENCODING")
  client_encoding
1            UTF8

最后,考虑到Windows环境下,R的客户端默认编码为GBK,所以我们再试一下,把数据在R中进行编码转型。


# 字符编码转型
> df3a <- as.data.frame(apply(df3, 2, function(x){
+   iconv(x, 'UTF-8', 'GBK')
+ }))

# 查看输出结果
> df3a
  user_id useranme      password
1       1       a1   678fdaiufda
2       2     小明 jfdaked21+_~!
3       3 粉丝日志       E!SM<I*

这样就可以解决了,中文乱码的问题了。

我们已经完成,掌握了RPostgreSQL包的各种使用技巧,希望大家理解原理后,能少犯错误,提高工作效率!

转载请注明出处:
http://blog.fens.me/r-postgressql/

打赏作者