makeSQLDataFrame: Construct SQLDataFrame from file.

In SQLDataFrame: Representation of SQL database in DataFrame metaphor

Description

Given a file name, makeSQLDataFrame will write the file contents into SQL database, and open the database table as SQLDataFrame.

Usage

makeSQLDataFrame(
  filename,
  dbtable = NULL,
  dbkey = character(),
  conn,
  host,
  user,
  dbname = NULL,
  password = NULL,
  type = c("SQLite", "MySQL"),
  overwrite = FALSE,
  sep = ",",
  index = FALSE,
  ...
)

Arguments

filename	A data.frame or DataFrame object, or a character string of the filepath to the text file that to be saved as SQL database table. For filepath, the data columns should not be quoted on disk.
dbtable	A character string for the to be saved database table name. If not provided, will use the name of the input data.frame or DataFrame object, or the basename(filename) without extension if filename is a character string.
dbkey	A character vector of column name(s) that could uniquely identify each row of the filename. Must be provided in order to construct a SQLDataFrame.
conn	a valid DBIConnection from SQLite or MySQL. If provided, arguments of 'user', 'host', 'dbname', 'password' will be ignored.
host	host name for SQL database.
user	user name for SQL database.
dbname	database name for SQL connection. For SQLite connection, it uses a tempfile(fileext = ".db") if not provided.
password	password for SQL database connection.
type	The SQL database type, supports "SQLite" and "MySQL".
overwrite	Whether to overwrite the dbtable if already exists. Default is FALSE.
sep	a character string to separate the terms. Not ‘NA_character_’. Default is ,.
index	Whether to create an index table. Default is FALSE.
...	additional arguments to be passed.

Details

The provided file must has one or more columns to unique identify each row (no duplicate rows allowed). The file must be rectangular without rownames. (if rownames are needed, save it as a column.)

Value

A SQLDataFrame object.

Examples

mtc <- tibble::rownames_to_column(mtcars)

## data.frame input
obj <- makeSQLDataFrame(mtc, dbkey = "rowname")
obj

## character input
filename <- file.path(tempdir(), "mtc.csv")
write.csv(mtc, file= filename, row.names = FALSE, quote = FALSE)
obj <- makeSQLDataFrame(filename, dbkey = "rowname")
obj

## save as MySQL database
## Not run: 
localConn <- DBI::dbConnect(dbDriver("MySQL"),
                            host = "",
                            user = "",
                            password = "",
                            dbname = "")
makeSQLDataFrame(filename, dbtable = "mtcMysql", dbkey = "rowname", conn = localConn)

## End(Not run)

SQLDataFrame documentation built on Nov. 29, 2020, 2:01 a.m.