将tbl_sql保存在r中？

发布于 2025-02-08 09:24:43 字数 687 浏览 5 评论 0原文

我只是与R一起学习mysql，想知道这是否可能。

我在数据库上进行了一个查询，该数据库使用RMARIADB软件包在MySQL上存在，如下所示：

library(RMariaDB)

con <- DBI::dbConnect(RMariaDB::MariaDB(),
                      dbname = "",
                      host = "",
                      user = "",
                      password = "") #details omitted 

df <- tbl(con,"df")

然后，我使用dplyr命令对此对象进行了一些调整。我可以将新的，清洁的表作为新表作为新表保存到MySQL，而不先将其转换为数据框架或Tibble？根据R的说法，它的类是：


class(df)
[1] "tbl_MariaDBConnection" "tbl_dbi"               "tbl_sql"              
[4] "tbl_lazy"              "tbl"

尝试遵循保存普通数据帧的方法，这给了我各种错误，通常是按照“无适用方法的tbl_mariadbconnection对象的适用方法”的路，

谢谢。

原文

I'm just learning MySQL with R and am wondering if this is possible.

I performed a query on a database that exists on MySQL with the RMariaDB package, as follows:

library(RMariaDB)

con <- DBI::dbConnect(RMariaDB::MariaDB(),
                      dbname = "",
                      host = "",
                      user = "",
                      password = "") #details omitted 

df <- tbl(con,"df")

I then made some adjustments to this object using dplyr commands. Can I save the new, cleaned table to mySQL as a new table without first converting it to a data frame or tibble? It's class, according to R is:


class(df)
[1] "tbl_MariaDBConnection" "tbl_dbi"               "tbl_sql"              
[4] "tbl_lazy"              "tbl"

Trying to follow methods for saving normal data frames gives me various errors, usually along the lines of "no applicable method for object of class tbl_MariaDBConnection"

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

今天小雨转甜 2025-02-15 09:24:43

您可以使用Compute（）函数将结果写入数据库中的表。
没有其他参数，它将写入临时表（在您的会话结束时将清除），但您可以指定以使其不久经时间。
compute（）在tbl_sql上的文档位于 https://dbplyr.tidyverse.org/reference/collapse.tbl_sql.html 。

至于您的代码，类似的事情

compute(df, temporary=FALSE, name="my_table_name")

将起作用；如果您在模式中需要它，则应该能够使用

compute(df, temporary=FALSE, name = dbplyr::in_schema("my_schema", "my_table_name"))

此方法指定，因为您不必自己编写SQL，因此可以处理不同数据库后端之间SQL语法的差异。

You can use the compute() function to write the result to a table in the database.
With no other arguments, it will write to a temporary table (that will be cleared when your session ends), but you can specify to make it non-temporary.
The documentation for compute() on a tbl_sql is at https://dbplyr.tidyverse.org/reference/collapse.tbl_sql.html.

As to your code, something like

compute(df, temporary=FALSE, name="my_table_name")

will work; if you need it in a schema, you should be able to specify with

compute(df, temporary=FALSE, name = dbplyr::in_schema("my_schema", "my_table_name"))

This method is nice as you don't have to write SQL yourself, and it therefore handles differences in SQL syntax between different database backends.

回复收藏 0 原文

悟红尘 2025-02-15 09:24:43

我使用

在您的上下文中，它可能看起来像：

sql_query <- glue::glue(
    "SELECT *\n",
    "INTO {db}.{schema}.{tbl_name}\n",
    "FROM (\n",
    dbplyr::sql_render(df),
    "\n) AS sub_query"
  )

dbExecute(tbl_connection, as.character(sql_query))

I use the process described in this answer. But you might also be interested in the answers to this question.

In your context it probably looks like:

sql_query <- glue::glue(
    "SELECT *\n",
    "INTO {db}.{schema}.{tbl_name}\n",
    "FROM (\n",
    dbplyr::sql_render(df),
    "\n) AS sub_query"
  )

dbExecute(tbl_connection, as.character(sql_query))

回复收藏 0 原文

~没有更多了~