install.packages("RSQLite")
#data/example.sqlite 에 예제 데이터베이스를 만들려면 해당 디렉터리를 사용할 수있어야 함
#이 디렉터리가 없다면 만들어야 함
if(!dir.exists("data")) dir.create("data")
library(RSQLite)
데이터베이스
드라이버 SQLite ( 와 데이터베이스 파일 ( example.sqlite 제공하여 연결을 생성함
파일은
원래 없었지만 , 드라이버가 빈 SQLite 데이터베이스를 나타내는 빈 파일을
생성함
#빈파일을 생성함
con <- dbConnect(SQLite(), "data/example.sqlite")
example1 <- data.frame(
id = 1:5,type = c("A","A","B","B","C"),
score = c(8,9,8,10,9), stringsAsFactors = FALSE
)
example1
dbWriteTable(con, "example1", example1)#(두번째 값은 데이터명, 세번째값은 frame)
#데이터 갖고올때는 데이터베이스에 접속해있어야하는데 갖고 왔으면 데이터베이스 연결을 종료해야함!
dbDisconnect(con)
install.packages("nycflights13")
data("diamonds", package = "ggplot2")
data("flights", package = "nycflights13")
con <- dbConnect(SQLite(), "data/datasets.sqlite")
dbWriteTable(con, "diamonds", diamonds, row.names = FALSE)
dbWriteTable(con, "flights", flights, row.names = FALSE)
dbDisconnect(con)
class(diamonds)
class(flights)
con <- dbConnect(SQLite(), "data/example2.sqlite")
chunk_size <- 10
id <- 0
for (i in 1:6){
chunk <- data.frame(id = ((i - 1L) * chunk_size):(i * chunk_size -1L),
type = LETTERS[[i]],
score = rbinom(chunk_size,10,(10-i)/10), stringsAsFactors = FALSE)
dbWriteTable(con, "products", chunk,
append = i > 1, row.names = FALSE)} #for문이 실행되지 않으면 처음 i값을 검색해보고
#1이 있다. 그리고 i -1L를 실행시켜봐라. 제대로 값이 나오면 각각 한개씩 실행시켜봐라.
#chunk와 dbWriteTable를 실행시켜보면서 에러가 나면
#append함수에 있는 i값에 1을 넣어보면서 확인해보기
#부분적으로 i에 2,3,4,5,6을 넣으면서 확인하면 정상적으로 실행.
#for문은 개별적으로도 실행시켜도 되니 복잡한 식이면 한꺼번에 계속 실행시키려고 하지말자!
dbDisconnect(con)
con <- dbConnect(SQLite(), "data/example2.sqlite")
dbExistsTable(con, "diamonds")
dbExistsTable(con, "mtcars")
dbListFields(con, "diamonds")
db_diamonds <- dbReadTable(con, "diamonds")
db_diamonds
head(db_diamonds,3)
head(diamonds)
identical(diamonds, db_diamonds)
str(db_diamonds)
str(diamonds)
dbListTables(con)
#다음코드에서는 이전에 만든 data/ datasets.sqlite 를 사용함
#먼저 데이터베이스와 연결을 설정함
con <- dbConnect(SQLite(), "data/example2.sqlite")
dbListTables(con)
#데이터베이스에 테이블이 2 개 있음
#select문을 사용하여 diamonds 에서 모든 데이터를 선택할 수 있음
#여기에서 모든 열(또는 필드)을 선택하고자 함
#데이터베이스 연결 con 과 쿼리 문자열로 dbGetQuery () 함수를 호출해 보자
db_diamonds <- dbGetQuery(con, "select * from diamonds")#이 함수는 R이 직접 실행시키는게 아니라 SQLite에 보내서
#그쪽에서 실행시킨후 R로 다시 보내는 것이다! 그러니까 database를 연결시켰지!
head(db_diamonds)
#*는 모든 필드 또는 동일한 의미의 열 를 의미
#필드의 일부분만 필요하다면 필드 이름을 차례대로 지정할 수 있음
db_diamonds <- dbGetQuery(con, "select carat, cut, color, clarity, depth, price from diamonds")
head(db_diamonds, 3)
#데이터에 있는 모든 유일한 경우를 선택하려면 select distinct 를 사용함
#예를들어 다음 코드는 diamonds 에서 가능한 모든 cut 의 유일한 값을 구함
dbGetQuery(con, "select distinct cut from diamonds")
#dbGetQuery는 때로 열이 하나만 있을 때도 항상 data.frame 값을 돌려줌
#값을 원자 벡터로 추출하려면 데이터 프레임에서 첫 번째 열을 선택함
dbGetQuery(con, "select distinct clarity from diamonds")[[1]]
#select를 사용하여 쿼리할 열을 선택할 때 가끔 열 이름이 우리가 원하는 것과 다를 수
#있음
#이경우 A as B 같은 표현을 사용하면 A 열의 동일한 데이터를 B 열로 받게 됨
db_diamonds <- dbGetQuery(con, "select carat, price, clarity as clarity_level from diamonds")
head(db_diamonds, 3)
#또 다른 경우 , 원하는 값이 데이터베이스에는 없지만 구하려고 계산이 필요할 때가
#있음
#이때도 A as B 를 사용함
#여기에서 A 는 기존 열을 사용한 산술 계산이 됨
db_diamonds <- dbGetQuery(con, "select carat, price, x*y*z as size,price , x*y*z as size from diamonds")
head(db_diamonds, 3)
#다음코드처럼 기존에 있는 열을 사용해서 새로운 열을 만들고 , 새로 만든 열을
#사용하여 또 다른 열을 만들려고 한다면 어떻게 될까
db_diamonds <- dbGetQuery(con, "select carat, price, x*y*z as size,price / size as value_density from diamonds")
head(db_diamonds, 3)
#당연히 이러한 식으로는 할 수 없음
#A as B에서 A 는 기존 열로 구성되어 있음
#이렇게 꼭 해야 할 때는 중첩된 쿼리를 사용함
#즉, 중첩된 select 로 만드는 임시 테이블에서 열을 선택하면 됨
#price / size를 계산하려고 할 때 이미 size 는 임시 테이블에 정의되어 있음
db_diamonds <- dbGetQuery(con, "select *, price / size as value_density from (select carat, price, x*y*z as size from diamonds)")
head(db_diamonds, 3)
#데이터베이스 쿼리에서 다음으로 중요한 구성 요소는 조건임
#결과를 만족시키는 조건을 지정하려면 where 를 사용해야 함
#예를 들어 cut 이 Good 인 다이아몬드를 다음과 같이 선택할 수 있음
good_diamonds <- dbGetQuery(con,
"select carat, cut, price from diamonds where cut = 'Good'")
head(good_diamonds, 3)
#cut이 Good 인 레코드의 비율이 모든 레코드에서 낮다는 점에 유의하자
nrow(good_diamonds) /nrow(diamonds)
#동시에 만족해야 하는 조건이 여러 가지가 있을 때는 이러한 조건을 조합하려고 and 를
#사용할 수 있음
#예를들어 cut 이 Good 이고 , color 가 E 인 모든 레코드를 선택해 보자
good_e_diamonds <- dbGetQuery(con,
"select carat, cut, color, price from diamonds where cut = 'Good' and color = 'E'")
head(good_e_diamonds, 3)
nrow(good_e_diamonds) /nrow(diamonds)
#이와비슷한 연산자로는 or 와 not 이 있음
#간단한논리 연산자 외에도 필드 값이 주어진 집합에 포함되는지 알고자 레코드를
#검사할 때 in 을 사용할 수 있음
#예를들어 color 가 E 와 F 인 레코드를 선택할 수 있음
color_ef_diamonds <- dbGetQuery(con, "select carat, cut, color, price from diamonds where color in ('E', 'F')")
nrow(color_ef_diamonds)
table(diamonds$color)
#in을 사용하려면 한 집합을 지정해야 함
#in과 마찬가지로 범위를 지정할 때 between A and B 를 사용할 수 있음
some_price_diamonds <- dbGetQuery(con, "select carat, cut, color, price from diamonds where price between 5000 and 5500")
nrow(some_price_diamonds) /nrow(diamonds)
#문자형 열에 유용한 연산자로 like 가 있음
#이것은 비슷한 문자형 패턴으로 레코드를 필터링할 수 있음
#예를들어 cut 변수가 Good 으로 끝나는 레코드를 모두 선택할 수 있음
#즉 , Good 이나 Very Good 이 선택될 수 있는데 , like '% 처럼 표기하면 됨
good_cut_diamonds <- dbGetQuery(con, "select carat, cut, color, price from diamonds where cut like '%Good'")
nrow(good_cut_diamonds) /nrow(diamonds)
#where와 order by 를 동시에 사용하여 레코드의 부분 집합을 정렬된 형태로도 추출할
#수 있음
head(dbGetQuery(con, "select carat, price from diamonds where cut = 'Ideal' and clarity = 'IF' and color = 'J' order by price"))
#맨 처음에 위치하는 결과만 몇 개 원한다면 , limit 을 사용하여 가져올 레코드 개수를
#제한할 수 있음
dbGetQuery(con, "select carat, price from diamonds order by carat desc limit 3")
#각 색상에 해당하는 레코드 개수를 계산할 수 있음
dbGetQuery(con, "select color, count(*) as number from diamonds group by color")
table(diamonds$color)
#세 가지 경우의 필드 값이 정확히 일치하는 레코드를 골라내고자 cut, color,
#clarity 로 구성된 데이터 프레임 diamond_selector 를 만듦
diamond_selector <- data.frame(cut = c("Ideal", "Good", "Fair"), color = c("E","I","D"), clarity = c("VS1", "I1","IF"), stringsAsFactors = FALSE)
diamond_selector
#데이터프레임을 만들고 diamonds 와 diamond_selector 를 결합하여 원하는
#레코드를 필터링할 수 있게 데이터베이스에 저장함
dbWriteTable(con, "diamond_selector", diamond_selector, row.names = FALSE, overwrite = TRUE)
install.packages("RMySQL")
library(DBI)
con <- dbConnect(
drv=RMySQL::MySQL(),
user="root",
password="1234",
dbname = "univdb"
)
dbSendQuery(conn = con,
"create table economics(
data Date,
pce double,
pop double,
psavert double,
uempmed double,
uemploy double)")
#테이블생성,삭제 레코드 추가,삭제
#데이터프레임 통채로 넣을때는?
dbSendQuery(conn = con,
"SET GLOBAL local_infile = TRUE;")
economics = ggplot2::economics
economics = data.frame(economics)
dbWriteTable(con,
"economics",
economics[1:300,],
overwrite = TRUE,
row.names = FALSE)#인덱싱빼주려고 row.name은 F
dbWriteTable(con,
"economics",
economics[301:574,],
append = TRUE,
row.names=FALSE)
dbSendQuery(con, "INSERT INTO economics (date, pce,pop,psavert,uempmed,unemploy)
VALUES('2022-04-29','112300','32100','0','12','8600')")
#컬럼값 변경 update set
dbSendQuery(con,
"UPDATE economics
SET psavert=7.9, uempmed = 14
WHERE date = '2022-04-29'")
#삭제
dbSendQuery(con,
"DELETE FROM economics
WHERE date = '2022-04-29'")
#테이블 삭제
dbSendQuery(con, "DROP TABLE economics")
#데이터베이스생성
dbSendQuery(con, "CREATE DATABASE new_db")
#데이터베이스삭제
dbSendQuery(con, "DROP DATABASE new_db")
#연결 끊기
dbDisconnect(con)