R 连接数据库

library(duckdb)

con = dbConnect(duckdb(), dbdir = "local.duckdb")

# 从CSV创建表

dbExecute(con, "

    CREATE TABLE persons AS 

    SELECT * FROM read_csv_auto('/mnt/c/Users/xuefe/Downloads/persons.csv')

")

# 写入 R 中的数据框

dbWriteTable(con, "table_iris", iris)

dbWriteTable(con, "table_mtcars", mtcars)

dbListTables(con)

dbGetQuery(con, "SELECT Species, COUNT(*) AS n

                 FROM table_iris

                 GROUP BY Species

                 ORDER BY n;")

# 断开数据库连接

dbDisconnect(con)

###############PostgreSQL

library('RPostgreSQL')

# create a connection

# save the password that we can "hide" it as best as we can by collapsing it

pw <- {

  "xuefliang"

}

# loads the PostgreSQL driver

drv <- dbDriver("PostgreSQL")

# creates a connection to the postgres database

# note that "con" will be used later in each connection to the database

con <- dbConnect(drv, dbname = "postgres",

                 host = "172.17.135.111", port = 5432,

                 user = "postgres", password = pw)

# check for the cartable

dbExistsTable(con, "persons")

dbWriteTable(con, "iris",value=iris, append = TRUE, row.names = FALSE)

df_postgres <- dbGetQuery(con, "SELECT * from iris")

dbDisconnect(con)

import duckdb as duck

conn = duck.connect('/mnt/d/my.duckdb')

#导入csv文件

conn.execute("""

    INSERT INTO persons

    SELECT * FROM read_csv_auto('/mnt/c/Users/xuefe/Downloads/persons.csv')

""")

conn.execute("""

    CREATE TABLE persons AS

    SELECT * FROM read_csv_auto('/mnt/c/Users/xuefe/Downloads/persons.csv',encoding='UTF-8')

""")