在R基础——数据的导入与导出(下)中,介绍了使用RODBC包连接SQL server数据库,在这篇文章中,根据我工作内容,介绍该包的基本操作,同时,根据我使用该包时出现的问题,介绍解决问题的方法。
首先,连接数据库;
#连接数据库conn
获取数据库表信息
场景:在数据库中有多个相同结构(列)的表,但是列名不同,获取其中一个表的列名,再统一其他表的列名。
#获取列名colname
对于批量处理,统一列名就可以使用同一列名操作了,而不用再重复写代码。当然,使用数值向量也是可以的,但是这样就降低了代码的可读性,我还是偏向于使用列名。
读取数据
场景:读取数据库的表中数据至R中。
#读取数据#整表读取df
可以看出来,该函数的使用较为简单,像其他where语句,group by语句等等复杂的查询都无法实现。所以这个函数在我工作中,基本不怎么使用。
使用sqlQuery函数
sqlQuery()函数直接使用sql语句,对数据库进行查询。
#使用sqlQuery进行复杂的查询df
sqlQuery()函数的第二个参数为sql的查询语句,需要对sql语句有一定的熟悉。
导出至数据库
场景:当在R中跑了模型或者处理完数据,需要保存至数据库重复利用时。
#整表导入数据库sqlSave(conn, df, "表名")#向已有表追加数据sqlSave(conn, df, "表名", append = T)
在向已有表中追加数据的时候,会经常出现问题:数据类型无法转化,导致无法追加;
#指定转换类型#列名colname
上面的方法能够解决一部分问题,但是仍然会在一些类型转换中出现问题。我一般的做法是这样:先将要追加进数据库的数据框导入到数据库中,再使用sql语句插入到已有表中。
#使用insert into方法将数据追加至数据库中已有表中SqlInsertSelect
对于sqlSave()函数的追加功能,实在是太差了,各种BUG,体验太差了。而使用sql语句插入,则完全没问题,故我一般使用该方法。
更新表
场景:需要对数据库中表进行更新。
#更新表sqlUpdate(conn, df, "更新的表名")
使用该函数更新表中的数据,需要数据框与数据库中表的结构一致,不然会更新失败。
在我工作中,由于需要更新的表不大,我一般使用整表更新:清空已有表,再插入表。
#整表更新#清空表sqlClear(conn, "表名")#插入表,使用上面的函数SqlInsertSelect(newdf, conn, "表名")
当表较大的时候,还是使用sqlQuery()函数更新为好。
#使用sqlQuery()函数更新sqlQuery(conn, "update 表名 set 列名 = 值 where ")
复制表
场景:当需要将数据库中的某一表复制至另一个数据库中。
#复制表sqlCopy( channel = conn, query = "select * from conn表名", destination = "conn2表名", #复制的表名 destchannel = conn2)
在我工作中,需要将主数据库中的一个表复制至另一个数据库,该函数很好的解决了我这个痛点。
其他操作
场景:其他操作,常用但简单功能。
#删除表sqlDrop(conn, "表名")#关闭连接odbcClose(conn)
使用完数据库后,记得关闭连接。
总结
整表读取导入更新,sqlFetch(),sqlSave(),sqlUpdate()函数;
复杂读取导入更新,使用sqlQuery()函数进行sql语句查询;
复制表,sqlCopyTable()函数;
领取专属 10元无门槛券
私享最新 技术干货