s中的sqlSave在R中创建并将数据框保存到sql表中 [英] sqlSave in R to create and save a dataframe to an sql table
问题描述
> df< read.csv(dat.csv)
其中dat.csv是一个没有头文件的csv,只是原始数据在两列中
然后我创建表:
> sqlQuery(channel,create table sqltable
(
col1 int,
col2 float
)
(
我确认表是通过在数据库中选择空表sqltable创建的。
所以现在我需要通过执行以下操作将dat.csv中的数据添加到sqltable中:
&sqlSave(channel,df,sqltable,verbose = T,fast = T,append = T)
否:1 rownames 1 / *** / no:2 col1 31105 / ** * / no:3 col2 0.001 / *** /
否:2 rownames 1 / *** / no:2 col1 31106 / *** / no:3 col2 0.023 / *** /
否:3 rownames 1 / *** /否: 2 col1 31107 / *** / no:3 col2 1.456 / *** /
否:4 rownames 1 / *** / no:2 col1 31108 / *** / no:3 col2 0.001 / ** * /
否:5 rownames 1 / *** / no:2 col1 31109 / *** / no:3 col2 2.102 / *** /
似乎很好,直到我做:
> sqlQuery频道,select * from sqltable)
[1] COL1 COL2
< 0 rows>或0长度的行。
sqlSave命令清楚地从dat.csv中提取数据,所以为什么没有添加到表中?我做错了什么?
在sqlSave中定义varTypes是个好主意。这是我做的。 :-)
columnTypes< - list(ColumnName =varchar(255),NumberColumn =float,datecolumn = date)
这定义了数据的列类型,以便您可以在sqlSave中添加参数。所以我们现在可以做sqlSave。
sqlSave(channel,YourTable,rownames = FALSE,varTypes = columnTypes)
/ pre>
如果表已经添加,那么我将在之前执行一个sqlDrop。
为什么这是一个答案?
嗯,我有同样的问题。我收到一个错误,说它无法执行sqlSave,但它正在将数据库中的列。所以我不得不修复columnTypes。 R尝试手动执行,但并不完美。 :-)
Hi I am using R to save a data frame into a DB2 SQL table. I seem to be able to create the table skeleton, but not append the data to the table -
>df <- read.csv("dat.csv")
where dat.csv is a csv with no headers, just raw data in two columns
then i create the table:
>sqlQuery(channel, "create table sqltable ( col1 int, col2 float )" (
where I confirm the table is created by being able to select the empty table "sqltable" on the database
so now I need to add the data from "dat.csv" into "sqltable" by doing:
>sqlSave(channel, df, "sqltable", verbose=T, fast=T, append=T) no: 1 rownames 1/***/no: 2 col1 31105/***/no: 3 col2 0.001/***/ no: 2 rownames 1/***/no: 2 col1 31106/***/no: 3 col2 0.023/***/ no: 3 rownames 1/***/no: 2 col1 31107/***/no: 3 col2 1.456/***/ no: 4 rownames 1/***/no: 2 col1 31108/***/no: 3 col2 0.001/***/ no: 5 rownames 1/***/no: 2 col1 31109/***/no: 3 col2 2.102/***/
all seems good until I do:
>sqlQuery(channel,"select * from sqltable") [1] COL1 COL2 <0 rows> or 0-length row.names
the sqlSave command clearly picks up the data from dat.csv, so why is it not added to the table? what am I doing wrong?
解决方案It would be a good idea to define varTypes within sqlSave. Here's how I do it. :-)
columnTypes <- list(ColumnName="varchar(255)", NumberColumn="float", datecolumn="date")
This defines the column types for you data so you can add the argument within sqlSave. So lets do the sqlSave now.
sqlSave(channel, YourTable, rownames = FALSE, varTypes = columnTypes)
and if the table has already been added then I would do an sqlDrop before that.
Why is this an answer?
Well, I had the same problem. I was getting an error saying it couldn't execute the sqlSave but it was putting in my columns in the database. So I had to fix the columnTypes. R tries to do it manually but it's not perfect. :-)
这篇关于s中的sqlSave在R中创建并将数据框保存到sql表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!