将R Dataframe中的多行插入Oracle数据库 [英] Insert multiple rows from R Dataframe into Oracle Database
问题描述
例如,我有一个数据框:
I have for example dataframe:
df <- as.data.frame(matrix(sample(c(NA, 1:50), 49, replace = TRUE), 7))
它看起来像这样:
V1 V2 V3 V4 V5 V6 V7
1 46 6 23 7 22 42 1
2 47 33 47 50 42 NA 49
3 14 35 49 48 37 10 22
4 42 23 5 4 41 46 48
5 32 36 24 26 19 31 45
6 26 47 28 19 34 19 32
7 37 13 46 46 NA 22 49
现在,我想不使用sqlSave 就将此数据帧写入到oracle数据库中,因为我有一个巨大的data.frame,如果这样做,R Studio会崩溃.相反,我决定使用sqlQuery做到这一点:
Now I want to write this dataframe to an oracle database without using sqlSave because I have a huge data.frame and R Studio crashes if I do it. Instead I decided to do it with sqlQuery:
library(RODBC)
connHandle <- odbcConnect("DBName", uid="user", pwd="password")
sqlQuery(connHandle, sprintf("INSERT INTO MYTABLE VALUES %s", stringWithMyDataframeValues))
close(connHandle)
我已阅读此帖子但这对我不起作用.
I have read this post but it doesn't work for me.
做这件事的最佳方法是什么?我要传递的字符串应如何显示?预先感谢.
What is the optimal way of doing it? How should my string that I want to pass in look like? Thanks in advance.
推荐答案
假定R数据帧列在Oracle中是完全相同的列,并且顺序相同(不多或少),请考虑将apply
至paste
与折叠每行中的所有值:
Assuming R dataframe columns are exactly the same columns and in same order in Oracle (not more or less), consider apply
to paste
with collapse all values in each row:
sqls <- sprintf("INSERT INTO MYTABLE VALUES (%s)",
apply(df, 1, function(i) paste(i, collapse=",")))
sqls
# [1] "INSERT INTO MYTABLE VALUES (2,10,9,50,34,37,29)"
# [2] "INSERT INTO MYTABLE VALUES (7,24,33,21,21,20,3)"
# [3] "INSERT INTO MYTABLE VALUES (39,38,2,33,43,33,7)"
# [4] "INSERT INTO MYTABLE VALUES (30,11,33,1,29,26,11)"
# [5] "INSERT INTO MYTABLE VALUES (50,45,13,27,3,35,36)"
# [6] "INSERT INTO MYTABLE VALUES (41,5,39,17,5,22,5)"
# [7] "INSERT INTO MYTABLE VALUES (21,50,39,30,2,11,49)"
# RECOMMENDED APPROACH TO SPECIFY COLUMNS
sqls <- sprintf("INSERT INTO MYTABLE (Col1, Col2, Col3, Col4, Col5, Col6, Col7) VALUES (%s)",
apply(df, 1, function(i) paste(i, collapse=",")))
connHandle <- odbcConnect("DBName", uid="user", pwd="password")
lapply(sqls, function(s) sqlQuery(connHandle, s))
close(connHandle)
更好的方法是将参数化与 RODBCext
,您只需传入原始数据帧即可,没有循环:
And even better approach is to use parameterization with RODBCext
where you just pass in original dataframe with no loop:
library(RODBCext)
connHandle <- odbcConnect("DBName", uid="user", pwd="password")
query <- "INSERT INTO MYTABLE (Col1, Col2, Col3, Col4, Col5, Col6, Col7) VALUES (?, ?, ?, ?, ?, ?, ?)"
sqlExecute(connHandle, query, df)
odbcClose(connHandle)
这篇关于将R Dataframe中的多行插入Oracle数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!