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 yearsite
1 27.0 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.0 Manchuria1931 University Farm\n
2 48.86667 Manchuria1931 Waseca\n
3 27.43334 Manchuria1931 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@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
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 - 1
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 ',',
