Hi,
Thanks for reporting the two problems. I'm attaching a simple update
to two functions that will allow you to specify a different separator,
e.g., using your example:
dbWriteTable(con, "barley", barley, overwrite = TRUE, sep =
";")
This workaround still relies in dumping the data.frame into a temporary
file and then importing into SQLite, but using prepared statements (which
SQLite 3 supports) will require some more work.
I'll look into the problem with the trailing newline soon.
--
David
Na Li wrote:>
> Hi, I'm experimenting with using (R)SQLite to do data management. Here
are
> two little problems that I've encountered:
>
> 1. The presence of ',' in string values causes trouble since
',' is also the
> delimiter used in the SQL statement.
>
> 2. A newline '\n' line attached to the last string value of each
row.
>
> Some examples:
>
> > library (RSQLite)
> Loading required package: DBI
> > sqlite <- dbDriver ("SQLite")
> > db <- dbConnect (sqlite, dbname = "test.dbms")
> > data (barley)
> > dbWriteTable (db, "barley", barley, overwrite = TRUE)
> [1] TRUE
> > barley[1:3,]
> yield variety year site
> 1 27.00000 Manchuria 1931 University Farm
> 2 48.86667 Manchuria 1931 Waseca
> 3 27.43334 Manchuria 1931 Morris
> > dbReadTable (db, "barley")[1:3,]
> yield variety year__1 site
> 1 27.00000 Manchuria 1931 University Farm\n
> 2 48.86667 Manchuria 1931 Waseca\n
> 3 27.43334 Manchuria 1931 Morris\n
>
> > barley$site <- as.character (barley$site)
> > barley$site[1] <- "University, Farm"
> > dbWriteTable (db, "barley", barley, overwrite = TRUE)
> Error in sqliteWriteTable(conn, name, value, ...) :
> RS-DBI driver: (RS_sqlite_import: /tmp/RtmpgSNaLn/rsdbi6a5d128c line 1
> expected 5 columns of data but found 6)
>
> I'm using RSQLite 0.4.0 with R 2.1.1 on Mac OS X.
>
> Cheers,
>
> Michael
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
http://www.R-project.org/posting-guide.html
-------------- next part --------------
"safe.write" <-
function (value, file, batch, ..., sep=",", eol="\n",
quote.string = FALSE)
{
N <- nrow(value)
if (N < 1) {
warning("no rows in data.frame")
return(NULL)
}
if (missing(batch) || is.null(batch))
batch <- 10000
else if (batch <= 0)
batch <- N
from <- 1
to <- min(batch, N)
while (from <= N) {
if (usingR())
write.table(value[from:to, , drop = FALSE], file = file,
append = TRUE, quote = quote.string, sep = sep,
na = .SQLite.NA.string, row.names = FALSE, col.names = FALSE,
eol = eol, ...)
else write.table(value[from:to, , drop = FALSE], file = file,
append = TRUE, quote.string = quote.string, sep = ",",
na = .SQLite.NA.string, dimnames.write = FALSE, end.of.row =
"\n",
...)
from <- to + 1
to <- min(to + batch, N)
}
invisible(NULL)
}
"sqliteWriteTable" <-
function (con, name, value, field.types, row.names = TRUE, overwrite = FALSE,
append = FALSE, ..., sep = ",")
{
if (overwrite && append)
stop("overwrite and append cannot both be TRUE")
if (!is.data.frame(value))
value <- as.data.frame(value)
if (row.names) {
value <- cbind(row.names(value), value)
names(value)[1] <- "row.names"
}
if (missing(field.types) || is.null(field.types)) {
field.types <- sapply(value, dbDataType, dbObj = con)
}
i <- match("row.names", names(field.types), nomatch = 0)
if (i > 0)
field.types[i] <- dbDataType(con, field.types$row.names)
names(field.types) <- make.db.names(con, names(field.types),
allow.keywords = F)
if (length(dbListResults(con)) != 0) {
new.con <- dbConnect(con)
on.exit(dbDisconnect(new.con))
}
else {
new.con <- con
}
if (dbExistsTable(con, name)) {
if (overwrite) {
if (!dbRemoveTable(con, name)) {
warning(paste("table", name, "couldn't be
overwritten"))
return(FALSE)
}
}
else if (!append) {
warning(paste("table", name, "exists in database:
aborting dbWriteTable"))
return(FALSE)
}
}
if (!dbExistsTable(con, name)) {
sql1 <- paste("create table ", name, "\n(\n\t",
sep = "")
sql2 <- paste(paste(names(field.types), field.types),
collapse = ",\n\t", sep = "")
sql3 <- "\n)\n"
sql <- paste(sql1, sql2, sql3, sep = "")
rs <- try(dbSendQuery(new.con, sql))
if (inherits(rs, ErrorClass)) {
warning("could not create table: aborting assignTable")
return(FALSE)
}
else dbClearResult(rs)
}
fn <- tempfile("rsdbi")
safe.write(value, file = fn, ..., sep=sep)
on.exit(unlink(fn), add = TRUE)
if (FALSE) {
sql4 <- paste("COPY '", name, "' FROM
'", fn, "' USING DELIMITERS ','",
sep = "")
rs <- try(dbSendQuery(new.con, sql4))
if (inherits(rs, ErrorClass)) {
warning("could not load data into table")
return(FALSE)
}
else dbClearResult(rs)
TRUE
}
conId <- as(new.con, "integer")
sep <- as.character(sep[1])
.Call("RS_SQLite_importFile", conId, name, fn, sep, PACKAGE =
"RSQLite")
}