「R」使用RMySQL简单操作mysql数据库

王诗翔呀

发布于 2020-07-06 17:30:18

5890

发布于 2020-07-06 17:30:18

文章被收录于专栏：优雅R优雅R

这是很久之前 Openbiox 设置的一个练习题。

使用 MySQL 数据库创建一个用于存储用户信息的数据表：包含 username, email, password 三个字段，使用 R 客户端插入、删除和更新一行数据，密码使用 SHA256 进行加密

library(RMySQL)
con = dbConnect(MySQL(), user = "root", password = "xxx")
# creating a database using RMySQL in R
dbSendQuery(con, "CREATE DATABASE test_user;")
dbSendQuery(con, "USE test_user;")
dbDisconnect(con)
# reconnecting to database we just created using following command in R :
mydb = dbConnect(MySQL(), user = "root", password = "xxx", dbname="test_user")

init_table = data.frame(
    username = "user1",
    email = "wxxx@163.com",
    password = digest::sha1("yes",algo = "sha256"),
    stringsAsFactors = FALSE
)

append_table = data.frame(
    username = "user2",
    email = "wxxx@163.com",
    password = digest::sha1("another password",algo = "sha256"),
    stringsAsFactors = FALSE
)

dbWriteTable(mydb, name = "test", value = init_table, row.names = FALSE, overwrite = TRUE)
dbReadTable(mydb, "test")

# 追加数据
dbWriteTable(mydb, name = "test", value = append_table, row.names = FALSE, append = TRUE)
dbReadTable(mydb, "test")

# 更新数据
dbSendQuery(mydb, "UPDATE test set username = 'user3' where username = 'user2'")
dbReadTable(mydb, "test")

# 删除数据
dbSendQuery(mydb, "DELETE FROM test where username = 'user1'")
dbReadTable(mydb, "test")

dbDisconnect(mydb)

参考：https://mkmanu.wordpress.com/2014/07/24/r-and-mysql-a-tutorial-for-beginners/

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-01-16，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库