MySQL Connection R

Create a new Database

con <- dbConnect(MySQL(), user='root', password='Root1312893-7', host='localhost')

dbGetQuery(con, "SHOW DATABASES") # List of databases available

##              Database
## 1             giraffe
## 2  information_schema
## 3               mysql
## 4  performance_schema
## 5              prueba
## 6        python_mysql
## 7              sakila
## 8              sampdb
## 9              siacon
## 10                sys
## 11              world

dbSendQuery(con, "CREATE DATABASE enigh16")

## <MySQLResult:0,0,1>

dbSendQuery(con, "USE enigh16")

## <MySQLResult:0,0,2>

dbDisconnect(con)

## Warning: Closing open result sets

## [1] TRUE

Conection to a Database

con <- dbConnect(MySQL(),
                 user='root',
                 password='Root1312893-7',
                 host='localhost',
                 dbname='siacon')

dbListTables(con)

## character(0)

cier_es <- read.csv("db.csv", header = T, sep = ";") # Also for dbWriteTable

# To be used when working with raw data
cier_es_tab <- sqlCreateTable(con, # USING con
                             "cierre_est", # Create the table poblacion
                             cier_es) # Using the columns of the data frame poblacion

## Warning: Do not rely on the default value of the row.names argument for
## sqlCreateTable(), it will change in the future.

dbSendQuery(con, cier_es_tab) # Creates table produced by sqlCreateTable

## <MySQLResult:419581072,1,1>

#dbGetQuery(con, "LOAD DATA LOCAL INFILE 'db.txt' 
#           INTO TABLE cierre_est
#           FIELDS TERMINATED BY ';'")

dbListTables(con)

## [1] "cierre_est"

# To be used when working with R data frames read from csv files
# dbWriteTable(con, # uSING con
#              value = poblacion, # Write the data frame poblacion 
#              name = "poblacion", # in the table poblacion anready in MySQL
#              append = TRUE, # Piecemeal writting
#              row.names = F) # Ignore rows numbers

dbGetQuery(con, "DESCRIBE cierre_est")

##                Field       Type Null Key Default Extra
## 1               anio bigint(20)  YES        <NA>      
## 2           idestado bigint(20)  YES        <NA>      
## 3            idciclo bigint(20)  YES        <NA>      
## 4        idmodalidad bigint(20)  YES        <NA>      
## 5          idcultivo bigint(20)  YES        <NA>      
## 6         idvariedad bigint(20)  YES        <NA>      
## 7     idunidadmedida bigint(20)  YES        <NA>      
## 8  idtipoagricultura bigint(20)  YES        <NA>      
## 9   idtipoproduccion bigint(20)  YES        <NA>      
## 10          sembrada     double  YES        <NA>      
## 11         cosechada     double  YES        <NA>      
## 12       siniestrada     double  YES        <NA>      
## 13 volumenproduccion     double  YES        <NA>      
## 14   valorproduccion     double  YES        <NA>      
## 15       rendimiento     double  YES        <NA>      
## 16  preciomediorural     double  YES        <NA>

dbGetQuery(con, "SELECT * FROM cierre_est")

##  [1] anio              idestado          idciclo          
##  [4] idmodalidad       idcultivo         idvariedad       
##  [7] idunidadmedida    idtipoagricultura idtipoproduccion 
## [10] sembrada          cosechada         siniestrada      
## [13] volumenproduccion valorproduccion   rendimiento      
## [16] preciomediorural 
## <0 rows> (or 0-length row.names)

dbDisconnect(con)

## [1] TRUE