Mysql - How to write out dataframe mtcars to `mtcars.sql` file?

anderwyang
July 11, 2024
211 views
1 vote
2 Answers

How to write out dataframe mtcars to mtcars.sql file ?
So I can import the sql file to MySql manual.
(xx.write just wished function )

library(tidyverse)

xx.write(mtcars,'mtcars.sql')

Tags: mysql r

Answers

I’ve shamelessly stolen margusl’s solution and wrapped it into a utility function:

export_to_sql = function (x, name = deparse(substitute(x)), file_path = paste0(name, '.sql')) {
  con = DBI::dbConnect(RSQLite::SQLite(), ':memory:')
  on.exit(DBI::dbDisconnect(con))

  write_rownames = ! identical(rownames(x), as.character(seq_len(nrow(x))))
  sql = c(
    DBI::sqlCreateTable(con, name, x, row.names = write_rownames), ';',
    DBI::sqlAppendTable(con, name, x, row.names = write_rownames), ';'
  )

  writeLines(sql, file_path)
}

Use it like this:

export_to_sql(mtcars)

This will create a file mtcars.sql with the following contents:

CREATE TABLE `mtcars` (
  `row_names` TEXT,
  `mpg` REAL,
  `cyl` REAL,
  `disp` REAL,
  `hp` REAL,
  `drat` REAL,
  `wt` REAL,
  `qsec` REAL,
  `vs` REAL,
  `am` REAL,
  `gear` REAL,
  `carb` REAL
)

;
INSERT INTO `mtcars`
  (`row_names`, `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`)
VALUES
  ('Mazda RX4', 21, 6, 160, 110, 3.9, 2.62, 16.46, 0, 1, 4, 4),
  ('Mazda RX4 Wag', 21, 6, 160, 110, 3.9, 2.875, 17.02, 0, 1, 4, 4),
  …
  ('Volvo 142E', 21.4, 4, 121, 109, 4.11, 2.78, 18.6, 1, 1, 4, 2)
;

Or you can explicitly specify the table and/or file path:

# Creates a table named `mycars` inside a file `mycars.sql`
export_to_sql(mtcars, name = 'mycars')

# Creates a table `mtcars` inside a file `~/yourcars.sql`
export_to_sql(mtcars, file_path = '~/yourcars.sql')

# Creates a table named `mycars` inside a file `~/yourcars.sql`
export_to_sql(mtcars, name = 'mycars', file_path = '~/yourcars.sql')

You can get SQL insert script from DBI::sqlAppendTable(); if you need to create that table first, there’s also DBI::sqlCreateTable().

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
sqlCreateTable(con, table = "mtcars", fields = mtcars[1:10, ], row.names = TRUE) |> 
  readr::write_file("mtcars_create.sql")
sqlAppendTable(con, table =  "mtcars", values =  mtcars, row.names = TRUE) |> 
  readr::write_file("mtcars_insert.sql")
dbDisconnect(con)

# check resulting files
readr::read_file("mtcars_create.sql") |> stringr::str_view()
#> [1] │ CREATE TABLE `mtcars` (
#>     │   `row_names` TEXT,
#>     │   `mpg` REAL,
#>     │   `cyl` REAL,
#>     │   `disp` REAL,
#>     │   `hp` REAL,
#>     │   `drat` REAL,
#>     │   `wt` REAL,
#>     │   `qsec` REAL,
#>     │   `vs` REAL,
#>     │   `am` REAL,
#>     │   `gear` REAL,
#>     │   `carb` REAL
#>     │ )
#>     │

readr::read_file("mtcars_insert.sql") |> stringr::str_view()
#> [1] │ INSERT INTO `mtcars`
#>     │   (`row_names`, `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`)
#>     │ VALUES
#>     │   ('Mazda RX4', 21, 6, 160, 110, 3.9, 2.62, 16.46, 0, 1, 4, 4),
#>     │   ('Mazda RX4 Wag', 21, 6, 160, 110, 3.9, 2.875, 17.02, 0, 1, 4, 4),
#>     │   ('Datsun 710', 22.8, 4, 108, 93, 3.85, 2.32, 18.61, 1, 1, 4, 1),
#>     │   ('Hornet 4 Drive', 21.4, 6, 258, 110, 3.08, 3.215, 19.44, 1, 0, 3, 1),
#>     │   ('Hornet Sportabout', 18.7, 8, 360, 175, 3.15, 3.44, 17.02, 0, 0, 3, 2),
#>     │   ('Valiant', 18.1, 6, 225, 105, 2.76, 3.46, 20.22, 1, 0, 3, 1),
#>     │   ('Duster 360', 14.3, 8, 360, 245, 3.21, 3.57, 15.84, 0, 0, 3, 4),
#>     │   ('Merc 240D', 24.4, 4, 146.7, 62, 3.69, 3.19, 20, 1, 0, 4, 2),
#>     │   ('Merc 230', 22.8, 4, 140.8, 95, 3.92, 3.15, 22.9, 1, 0, 4, 2),
#>     │   ('Merc 280', 19.2, 6, 167.6, 123, 3.92, 3.44, 18.3, 1, 0, 4, 4),
#>     │   ('Merc 280C', 17.8, 6, 167.6, 123, 3.92, 3.44, 18.9, 1, 0, 4, 4),
#>     │   ('Merc 450SE', 16.4, 8, 275.8, 180, 3.07, 4.07, 17.4, 0, 0, 3, 3),
#>     │   ('Merc 450SL', 17.3, 8, 275.8, 180, 3.07, 3.73, 17.6, 0, 0, 3, 3),
#>     │   ('Merc 450SLC', 15.2, 8, 275.8, 180, 3.07, 3.78, 18, 0, 0, 3, 3),
#>     │   ('Cadillac Fleetwood', 10.4, 8, 472, 205, 2.93, 5.25, 17.98, 0, 0, 3, 4),
#>     │   ('Lincoln Continental', 10.4, 8, 460, 215, 3, 5.424, 17.82, 0, 0, 3, 4),
#>     │   ('Chrysler Imperial', 14.7, 8, 440, 230, 3.23, 5.345, 17.42, 0, 0, 3, 4),
#>     │   ('Fiat 128', 32.4, 4, 78.7, 66, 4.08, 2.2, 19.47, 1, 1, 4, 1),
#>     │   ('Honda Civic', 30.4, 4, 75.7, 52, 4.93, 1.615, 18.52, 1, 1, 4, 2),
#>     │   ('Toyota Corolla', 33.9, 4, 71.1, 65, 4.22, 1.835, 19.9, 1, 1, 4, 1),
#>     │   ('Toyota Corona', 21.5, 4, 120.1, 97, 3.7, 2.465, 20.01, 1, 0, 3, 1),
#>     │   ('Dodge Challenger', 15.5, 8, 318, 150, 2.76, 3.52, 16.87, 0, 0, 3, 2),
#>     │   ('AMC Javelin', 15.2, 8, 304, 150, 3.15, 3.435, 17.3, 0, 0, 3, 2),
#>     │   ('Camaro Z28', 13.3, 8, 350, 245, 3.73, 3.84, 15.41, 0, 0, 3, 4),
#>     │   ('Pontiac Firebird', 19.2, 8, 400, 175, 3.08, 3.845, 17.05, 0, 0, 3, 2),
#>     │   ('Fiat X1-9', 27.3, 4, 79, 66, 4.08, 1.935, 18.9, 1, 1, 4, 1),
#>     │   ('Porsche 914-2', 26, 4, 120.3, 91, 4.43, 2.14, 16.7, 0, 1, 5, 2),
#>     │   ('Lotus Europa', 30.4, 4, 95.1, 113, 3.77, 1.513, 16.9, 1, 1, 5, 2),
#>     │   ('Ford Pantera L', 15.8, 8, 351, 264, 4.22, 3.17, 14.5, 0, 1, 5, 4),
#>     │   ('Ferrari Dino', 19.7, 6, 145, 175, 3.62, 2.77, 15.5, 0, 1, 5, 6),
#>     │   ('Maserati Bora', 15, 8, 301, 335, 3.54, 3.57, 14.6, 0, 1, 5, 8),
#>     │   ('Volvo 142E', 21.4, 4, 121, 109, 4.11, 2.78, 18.6, 1, 1, 4, 2)

For simple frames like mtcars, sqlCreateTable() and sqlAppendTable() outputs are identical for both SQLite and MySQL/MariaDB. I´m not really sure if the same applies to all corner cases, but you could also just switch to MariaDB DBI interface to play it safe (this is what you should be using for MySQL too as RMySQL is being phased out). Though of course you’d need to have access to MySQL or MariaDB instance for this:

# start MariaDB docker container
# docker run -p 3306:3306 --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 --env MARIADB_DATABASE=db --env MARIADB_USER=u --env MARIADB_PASSWORD=pw mariadb:latest
con <- dbConnect(RMariaDB::MariaDB(), user = "u", host="127.0.0.1", port = 3306, dbname = "db", password="pw")

^{Created on 2024-07-11 with reprex v2.1.0}

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – How to write out dataframe mtcars to `mtcars.sql` file?

Answers