是否可以使用 dplyr 包向 SQLite db 表插入(添加)一行?

2021-12-23 00:00:00 r sqlite dplyr

我对 dplyr 包的数据库连接功能不熟悉，但我对将其用于 SQLite 连接非常感兴趣.我按照 本教程 创建了一个 SQLite 数据库(my_db)

I am new to the database connection capabilities of dplyr package, but I am very interested in using it for an SQLite connection. I followed this tutorial and created an SQLite database (my_db) 

my_db <- src_sqlite("my_db.sqlite3", create = T)

并插入一个数据框(df)作为该数据库的表(my_table).

and inserted a dataframe (df) as a table (my_table) of this database.

copy_to(my_db,df,"my_table")

现在我想在这个表中插入新行.我尝试过这样的事情(是的，我必须承认它甚至看起来都不那么有希望……但我还是试了一下):

Now I want to insert new rows in this table. I tried something like this (and yes I must admit it doesn't even look like promising... but I still gave it a try):

collect(build_sql("INSERT INTO my_table VALUES (",newdf,")", con=my_db))

有谁知道是否可以使用 dplyr 向现有的 sqlite db 表添加行?或者你会如何处理这个问题?非常感谢！

Does anyone know if adding rows to an existing sqlite db table is even possible using dplyr? Or how would you deal with this problem? Many thanks in advance!

推荐答案

您可以对通过 dplyr 创建的数据库/表执行 SQL 操作，但您必须还原到 RSQLite/DBI 调用并更改您制作数据库/表的方式:

You can perform SQL operations on a database/table created via dplyr, but you have to revert to RSQLite/DBI calls and change how you made the database/table:

library(dplyr)

my_db <- src_sqlite("my_db.sqlite3", create=TRUE) 
copy_to(my_db, iris, "my_table", temporary=FALSE) # need to set temporary to FALSE

# grab the db connection from the object created by src_sqlite
# and issue the INSERT That way

res <- dbSendQuery(my_db$con, 
                   'INSERT INTO my_table VALUES (9.9, 9.9, 9.9, 9.9, "new")')

相关文章