提交: 501a2b56575175faf3d325786395c1e2ce9a51db - myprojects-hg-reborn (hg)

Commit MetaInfo

• 修订版: 501a2b56575175faf3d325786395c1e2ce9a51db (tree)
• 时间: 2022-07-07 00:00:33
• 作者: Lorenzo Isella <lorenzo.isella@gmai...>
• Commiter: Lorenzo Isella

Log Message

A script showing the revision of the csv_to_sqlite function.

更改概述

• add: R-codes/revise_create_sqlite.R

差异

diff -r c2a9e93444f3 -r 501a2b565751 R-codes/revise_create_sqlite.R
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/R-codes/revise_create_sqlite.R	Wed Jul 06 17:00:33 2022 +0200
@@ -0,0 +1,212 @@
+rm(list=ls())
+
+library(tidyverse)
+
+library(DBI) # main DB interface
+library(dbplyr) # dplyr back-end for DBs
+library(RSQLite)
+library(lubridate)
+
+
+
+csv_to_sqlite <- function(csv_file, sqlite_file, table_name,
+                          delim = ",",
+                          pre_process_size = 1000, chunk_size = 50000,
+                          show_progress_bar = TRUE, ...) {
+    con <- dbConnect(SQLite(), dbname = sqlite_file)
+
+    # read a first chunk of data to extract the colnames and types
+    # to figure out the date and the datetime columns
+    df <- read_delim(csv_file, delim = delim, n_max = pre_process_size, ...)
+    date_cols <- df %>%
+        select_if(is.Date) %>%
+        colnames()
+    datetime_cols <- df %>%
+        select_if(is.POSIXt) %>%
+        colnames()
+
+    # write the first batch of lines to SQLITE table, converting dates to string
+    # representation
+    df <- df %>%
+      mutate_at(.vars = date_cols, .funs = as.character.Date) %>%
+      mutate_at(.vars = datetime_cols, .funs = as.character.POSIXt)
+    dbWriteTable(con, table_name, df, overwrite = TRUE)
+
+    # readr chunk functionality
+    read_delim_chunked(
+      csv_file,
+      callback = append_to_sqlite(con = con, table_name = table_name,
+                                  date_cols = date_cols,
+                                  datetime_cols = datetime_cols),
+      delim = delim,
+      skip = pre_process_size, chunk_size = chunk_size,
+      progress = show_progress_bar,
+      col_names = colnames(df), ...)
+    dbDisconnect(con)
+}
+
+#' Callback function that appends new sections to the SQLite table.
+#' @param con A valid connection to SQLite database.
+#' @param table_name Name of the table to store the data table in the sqlite
+#'   database.
+#' @param date_cols Name of columns containing Date objects
+#' @param datetime_cols Name of columns containint POSIXt objects.
+#'
+#' @keywords internal
+append_to_sqlite <- function(con, table_name,
+                             date_cols, datetime_cols) {
+  #' @param x Data.frame we are reading from.
+  function(x, pos) {
+
+    x <- as.data.frame(x)
+    x <- x %>%
+      mutate_at(.vars = date_cols, .funs = as.character.Date) %>%
+      mutate_at(.vars = datetime_cols, .funs = as.character.POSIXt)
+    # append data frame to table
+    dbWriteTable(con, table_name, x, append = TRUE)
+
+  }
+}
+
+
+
+
+
+
+
+
+
+
+csv_to_sqlite2 <- function(csv_file, sqlite_file, table_name,
+                          delim = ",",
+                          pre_process_size = 1000, chunk_size = 50000,
+                          show_progress_bar = TRUE, ...) {
+    con <- dbConnect(SQLite(), dbname = sqlite_file)
+
+    # read a first chunk of data to extract the colnames and types
+    # to figure out the date and the datetime columns
+    df <- read_delim(csv_file, delim = delim, n_max = pre_process_size, ...)
+
+
+    date_cols <- df  |> 
+        select(where(is.Date)) |> 
+        colnames()
+    
+    datetime_cols <- df  |> 
+        select(where(is.POSIXt))  |> 
+        colnames()
+
+    # write the first batch of lines to SQLITE table, converting dates to string
+    # representation
+    ## df <- df %>%
+    ##   mutate_at(.vars = date_cols, .funs = as.character.Date) %>%
+    ##   mutate_at(.vars = datetime_cols, .funs = as.character.POSIXt)
+
+
+    df <- df  |> 
+      mutate(across( all_of(date_cols), \(x)  as.character.Date(x)))  |> 
+      mutate(across(all_of(datetime_cols), \(x) as.character.POSIXt(x)))
+
+
+
+    dbWriteTable(con, table_name, df, overwrite = TRUE)
+
+    # readr chunk functionality
+    read_delim_chunked(
+      csv_file,
+      callback = append_to_sqlite2(con = con, table_name = table_name,
+                                  date_cols = date_cols,
+                                  datetime_cols = datetime_cols),
+      delim = delim,
+      skip = pre_process_size, chunk_size = chunk_size,
+      progress = show_progress_bar,
+      col_names = colnames(df), ...)
+    dbDisconnect(con)
+}
+
+
+
+
+
+append_to_sqlite2 <- function(con, table_name,
+                             date_cols, datetime_cols) {
+  #' @param x Data.frame we are reading from.
+  function(x, pos) {
+
+    x <- as.data.frame(x)
+    x <- x  |> 
+      mutate(across( all_of(date_cols), \(x) as.character.Date(x))) |> 
+      mutate(across( all_of(datetime_cols), \(x) as.character.POSIXt(x)))
+    # append data frame to table
+    dbWriteTable(con, table_name, x, append = TRUE)
+
+  }
+}
+
+
+
+
+
+
+
+
+
+
+
+################################################################
+################################################################
+################################################################
+################################################################
+################################################################
+
+
+
+
+
+csv_to_sqlite("scoreboard.csv.gz", "scoreboard.db", "mytable")
+
+csv_to_sqlite2("scoreboard.csv.gz", "scoreboard2.db", "mytable")
+
+
+
+
+con <- dbConnect(drv = RSQLite::SQLite(), # give me a SQLite connection
+        dbname = "scoreboard.db")
+
+con2 <- dbConnect(drv = RSQLite::SQLite(), # give me a SQLite connection
+        dbname = "scoreboard2.db")
+
+
+table_list <- dbListTables(con) # List me the tables at the connection
+
+print(table_list)
+
+
+
+fields <- dbListFields(con, # Reach into my connection and ...
+      "mytable")   # tell me what fields does the `bakers` table have?
+
+
+print("the database fields are")
+print(fields)
+
+my_glimpse <- tbl(con, "mytable") 
+
+print(my_glimpse)
+
+
+
+my_glimpse2 <- tbl(con2, "mytable") 
+
+print(my_glimpse2)
+
+
+
+
+## df <- read_csv("scoreboard.csv.gz")
+
+
+
+
+
+print("So far so good")