Monthly Archives: October 2020

R으로 Docker로 구성한 MariaDB 사용하기

이 글은 R로 MariaDB(이하 마리아디비)에 접근하기 위한 글입니다. 컴퓨터에 직접 마리아디비를 설치하고 이용하는 것이 아니라, 도커에 설치한 마리아디비를 이용하고자 합니다. 직접 설치하신 마리아디비를 사용하고자 하실 분들이나 외부 다른 컴퓨터에 설치된 것을 사용하고자 하실 분들도 아래 연결 설정값을 적절하게 입력하신다면, 그 글을 따라가시는데 문제가 없을 것 같습니다.

본격적으로 시작하기에 앞서

참고로 여기에서는 이미 Docker로 마리아디비를 설치했다고 가정할 것입니다. 설치하는 방법은 다음 링크를 참고하세요. Docker를 사용해서 MariaDB을 설치하기 그리고 이미 다음과 같은 sql명령을 사용하여 gregs_list이라는 DATABASE를 만들고, 그 안에 my_contacts라는 테이블을 만들고 두 사람의 자료를 입력했습니다.

CREATE DATABASE gregs_list;

CREATE TABLE my_contacts(
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
birthday Date,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100)
);

INSERT INTO my_contacts(
last_name, first_name, email, gender, birthday, profession, location, status, interests, seeking
)
VALUES (
'Anderson', 'Jillian', 'jill_anderson@breakneckpizza.com', 'F', '1980-09-05', 'Technical Writer', 'Palo Alto, CA', 'Single', 'Kayaking, Reptiles', 'Relationship, Friends'
);

INSERT INTO my_contacts(
first_name, email,  profession, location
)
VALUES (
'Pat', 'patpost@breakneckpizza.com', 'Postal Worker', 'Princeton, NJ'
);

위에서 작업한 결과로 gregs_list 데이터베이스의 my_contacts라는 테이블에는 다음과 같이 두 사람의 자료가 들어있습니다.

MariaDB [gregs_list]> SELECT * FROM my_contacts;
+-----------+------------+----------------------------------+--------+------------+------------------+---------------+--------+--------------------+-----------------------+
| last_name | first_name | email                            | gender | birthday   | profession       | location      | status | interests          | seeking               |
+-----------+------------+----------------------------------+--------+------------+------------------+---------------+--------+--------------------+-----------------------+
| Anderson  | Jillian    | jill_anderson@breakneckpizza.com | F      | 1980-09-05 | Technical Writer | Palo Alto, CA | Single | Kayaking, Reptiles | Relationship, Friends |
| NULL      | Pat        | patpost@breakneckpizza.com       | NULL   | NULL       | Postal Worker    | Princeton, NJ | NULL   | NULL               | NULL                  |
+-----------+------------+----------------------------------+--------+------------+------------------+---------------+--------+--------------------+-----------------------+
2 rows in set (0.004 sec)

RMariaDB 설치하기

R 에서 MariaDB에 접근하기 위해서는 odbc 패키지나 RMariaDB를 사용해야 합니다. 이에 관련된 내용은 R Statistical Programming Using MariaDB as the Background Database – MariaDB Knowledge Base을 참고하시면 됩니다. 여기서는 RMariaDB를 사용하도록 하겠습니다. 앞 링크에서 RMariaDB을 다음과 같이 소개하고 있습니다.

“RMariaDB” R library, is a modern ‘MariaDB’ client based on ‘Rcpp’.

사용하기 위에서는 당연히 패키지를 설치해야 합니다.

install.packages("RMariaDB")

설치를 했으면 사용하시는 방법은 다음과 같습니다. 앞에서 말한 도커에 설치된 마리아디비의 정보를 그대로 입력했습니다. 여기서는 직접 입력하고 있지만, 실전에서 사용하시려면 Database Interface and MariaDB Driver • RMariaDB에서처럼 별도 파일에 입력하여 사용하시는 것이 좋을 것 습니다. 마지막 줄에서 dbListTables(con)을 통해서 gregs_list이라는 데이터베이스가 가지고 있는 테이블을 보여주게 됩니다.

library(RMariaDB)

con <- dbConnect(
  drv = RMariaDB::MariaDB(),
  username = "root",
  password = "RT27hDosK",
  host = "0.0.0.0",
  port = 3306,
  dbname = "gregs_list"
)

dbListTables(con)

앞의 코드가 실행된 결과는 다음과 같습니다. 현재 앞에서 입력한 my_contacts 테이블도 볼 수 있습니다.

> library(RMariaDB)
> con <- dbConnect(
+   drv = RMariaDB::MariaDB(),
+   username = "root",
+   password = "RT27hDosK",
+   host = "0.0.0.0",
+   port = 3306,
+   dbname = "gregs_list"
+ )
>
> con
<mariadbconnection>
Host:    0.0.0.0
Server:
Client:
> dbListTables(con)
[1] "movie_table"   "easy_drinks"   "my_contacts"   "doughnut_list" "users"         "clown_info"

R로 데이터베이스에 입력한 테이블을 살펴보기

이제 본격적으로 앞에서 데이터베이스에 저장한 테이블을 살펴보겠습니다. dbReadTable()을 사용하면 쉽게 테이블 내용을 확인하실 수 있습니다.

dbReadTable(con, "my_contacts")

윗 코드를 실행하면 다음과 같습니다.

> dbReadTable(con, "my_contacts")
contact_id phone last_name first_name                            email gender   birthday       profession      location status          interests               seeking
1          1  <na>  Anderson    Jillian jill_anderson@breakneckpizza.com      F 1980-09-05 Technical Writer Palo Alto, CA Single Kayaking, Reptiles Relationship, Friends
2          2  <na>      <na>        Pat       patpost@breakneckpizza.com   <na>       <na>    Postal Worker Princeton, NJ   <na>               <na>                  <na>

SQL을 사용하려면 다음과 같이 하시면 됩니다.

res <- dbSendQuery(con, "SELECT * FROM my_contacts")

위의 코드를 실행하면 다음과 같습니다. dbClearResult()을 이용해서 사용하신 다음에 정리하고 마무리하지 않으시면 에러가 날 수 있습니다. 주의하세요!

> res <- dbSendQuery(con, "SELECT * FROM my_contacts")
> res
<mariadbresult>
SQL  SELECT * FROM my_contacts
ROWS Fetched: 0 [incomplete]
Changed: 0
> dbFetch(res)
contact_id phone last_name first_name                            email gender   birthday       profession      location status          interests               seeking
1          1  <na>  Anderson    Jillian jill_anderson@breakneckpizza.com      F 1980-09-05 Technical Writer Palo Alto, CA Single Kayaking, Reptiles Relationship, Friends
2          2  <na>      <na>        Pat       patpost@breakneckpizza.com   <na>       <na>    Postal Worker Princeton, NJ   <na>               <na>                  <na>
> dbClearResult(res)

조금 더 세련되게 할 수도 있습니다.

res <- dbSendQuery(con, "SELECT * FROM my_contacts")
while(!dbHasCompleted(res)){
  chunk <- dbFetch(res, n = 5)
  print(chunk)
  print(nrow(chunk))
}

윗 코드를 실행하면 다음과 같습니다.

> dbClearResult(res)
> res <- dbSendQuery(con, "SELECT * FROM my_contacts")
> while(!dbHasCompleted(res)){
+   chunk <- dbFetch(res, n = 5)
+   print(chunk)
+   print(nrow(chunk))
+ }
  contact_id phone last_name first_name                            email gender   birthday       profession      location status          interests               seeking
1          1  <na>  Anderson    Jillian jill_anderson@breakneckpizza.com      F 1980-09-05 Technical Writer Palo Alto, CA Single Kayaking, Reptiles Relationship, Friends
2          2  <na>      <na>        Pat       patpost@breakneckpizza.com   <na>       <na>    Postal Worker Princeton, NJ   <na>               <na>                  <na>
[1] 2

tidyverse를 추가해서 사용하면 좀 더 쉽고 멋있게 처리할 수 있습니다. 다음 코드에서 볼 수 있는 것처럼 SQL를 사용하지 않고 filter()와 같은 것을 이용하여 쉽게 테이블에 있는 자료를 처리할 수 있습니다. 마지막 줄에서 filter()를 사용하여 first_name 열에 있는 “Pat”이라는 단어가 있는 줄을 뽑아서 보여줍니다.

library(tidyverse)
library(RMariaDB)
library(dplyr, warn.conflicts = FALSE)
con <- dbConnect(
  drv = RMariaDB::MariaDB(),
  username = "root",
  password = "RT27hDosK",
  host = "0.0.0.0",
  port = 3306,
  dbname = "gregs_list"
)
my_contacts <- tbl(con, "my_contacts")
my_contacts
my_contacts %>% filter(first_name == "Pat")

윗 코드를 실행하면 다음과 같습니다.

> library(tidyverse)
─ Attaching packages ─────────────────────────────────────────────────────────────────────── tidyverse 1.2.1 ─
✔ ggplot2 3.3.0     ✔ purrr   0.3.4
✔ tibble  3.0.1     ✔ dplyr   1.0.0
✔ tidyr   1.1.0     ✔ stringr 1.4.0
✔ readr   1.3.1     ✔ forcats 0.5.0
─ Conflicts ──────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ─
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
> library(RMariaDB)
> library(dplyr, warn.conflicts = FALSE)
> con <- dbConnect(
+   drv = RMariaDB::MariaDB(),
+   username = "root",
+   password = "RT27hDosK",
+   host = "0.0.0.0",
+   port = 3306,
+   dbname = "gregs_list"
+ )
> my_contacts <- tbl(con, "my_contacts")
> my_contacts
# Source:   table<my_contacts> [?? x 12]
# Database: mysql [root@0.0.0.0:NA/gregs_list]
contact_id phone last_name first_name email                            gender birthday   profession       location      status interests          seeking
<int> <chr> <chr>     <chr>      <chr>                            <chr>  <date>     <chr>            <chr>         <chr>  <chr>              <chr>
1          1 <na>  Anderson  Jillian    jill_anderson@breakneckpizza.com F      1980-09-05 Technical Writer Palo Alto, CA Single Kayaking, Reptiles Relationship, Friends
2          2 <na>  <na>      Pat        patpost@breakneckpizza.com       <na>   NA         Postal Worker    Princeton, NJ <na>   <na>               <na>
> my_contacts %>% filter(first_name == "Pat")
# Source:   lazy query [?? x 12]
# Database: mysql [root@0.0.0.0:NA/gregs_list]
contact_id phone last_name first_name email                      gender birthday   profession    location      status interests seeking
<int> <chr> <chr>     <chr>      <chr>                      <chr>  <date>     <chr>         <chr>         <chr>  <chr>     <chr>
1          2 <na>  <na>      Pat        patpost@breakneckpizza.com <na>   NA         Postal Worker Princeton, NJ <na>   <na>      <na>

참고 자료

파이썬으로 Docker로 구성한 MariaDB 사용하기

도커를 이용하여 MariaDB(이하 마리아디비)를 설치해봤습니다. 이렇게 설치한 마리아디비에 파이썬으로 이용하여 자료를 저장하고 저장한 값을 확인하고 지워보겠습니다. 우선 파이썬으로 마리아디비와 같은 실제 DB(이하 데이터베이스)에 접근하기 위해서는 해당 데이터베이스에 맞는 드라이버(또는 클라이언트 라이브러리(client library)라고 하기도 합니다)를 설치해줘야 합니다. 이 글에서는 PyMySQL을 사용하겠습니다. 설치 방법은 Installation을 참고하세요. 현재 사용하고 있는 PyMySQL의 버전은 0.10.1입니다.

본격적으로 시작하기에 앞서

참고로 여기에서는 이미 Docker로 MariaDB를 설치했다고 가정할 것입니다. 설치하는 방법은 다음 링크를 참고하세요. Docker를 사용해서 MariaDB을 설치하기 그리고 이미 다음과 같은 sql명령을 사용하여 gregs_list이라는 DATABASE를 만들고, 그 안에 my_contacts라는 테이블을 만들고 두 사람의 자료를 입력했습니다.그리고 사용하기 쉽게 하기 위해서 sqlalchemy을 사용합니다. 이것의 설치 방법은 How to Install SQLAlchemy on Windows, Mac and Linux | Python Central을 참고하세요!

CREATE DATABASE gregs_list;

CREATE TABLE my_contacts(
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
birthday Date,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100)
);

INSERT INTO my_contacts(
last_name, first_name, email, gender, birthday, profession, location, status, interests, seeking
)
VALUES (
'Anderson', 'Jillian', 'jill_anderson@breakneckpizza.com', 'F', '1980-09-05', 'Technical Writer', 'Palo Alto, CA', 'Single', 'Kayaking, Reptiles', 'Relationship, Friends'
);

INSERT INTO my_contacts(
first_name, email,  profession, location
)
VALUES (
'Pat', 'patpost@breakneckpizza.com', 'Postal Worker', 'Princeton, NJ'
);

위에서 작업한 결과로 gregs_list 데이터베이스의 my_contacts라는 테이블에는 다음과 같이 두 사람의 자료가 들어있습니다.

MariaDB [gregs_list]> SELECT * FROM my_contacts;
+-----------+------------+----------------------------------+--------+------------+------------------+---------------+--------+--------------------+-----------------------+
| last_name | first_name | email                            | gender | birthday   | profession       | location      | status | interests          | seeking               |
+-----------+------------+----------------------------------+--------+------------+------------------+---------------+--------+--------------------+-----------------------+
| Anderson  | Jillian    | jill_anderson@breakneckpizza.com | F      | 1980-09-05 | Technical Writer | Palo Alto, CA | Single | Kayaking, Reptiles | Relationship, Friends |
| NULL      | Pat        | patpost@breakneckpizza.com       | NULL   | NULL       | Postal Worker    | Princeton, NJ | NULL   | NULL               | NULL                  |
+-----------+------------+----------------------------------+--------+------------+------------------+---------------+--------+--------------------+-----------------------+
2 rows in set (0.004 sec)

데이터베이스에 입력된 것을 가져오기

그러면 파이썬으로 이것에 연결해보겠습니다. 도커에 설치된 마리아디비의 정보를 파이썬에 입력하기 위해서 정보가 들어 있는 dict를 하나 만들었습니다.

db = {
'user'     : 'root',
'password' : 'RT27hDosK',
'host'     : '0.0.0.0',
'port'     : '3306',
'database' : 'gregs_list'
}

위에 입력한 자료를 가지고 DB_URL이라는 문자열을 만들어서 이를 가지고 데이터 베이스에 접근하기 위해 sqlalchemy으로 create_engine을 만들고, my_contacts 테이블에 접근하기 위해 execute을 가지고 sql을 직접 실행합니다. 그렇게 받아온 자료를 print를 이용해 확인합니다.

import sqlalchemy as sa
DB_URL = f"mysql+pymysql://{db['user']}:{db['password']}@{db['host']}:{db['port']}/{db['database']}?charset=utf8"
conn = sa.create_engine(DB_URL, encoding = 'utf-8')

rows = conn.execute('SELECT * FROM my_contacts')

for row in rows:
print(row)

지금까지 한 것을 실행하면 다음과 같습니다. 맥에서 실행한 것이니 조금씩 다를 수는 있겠지만, 거의 다 같게 작동할 것입니다. 만약 자료를 보기 위해서 print(rows)라고 하신다면 <sqlalchemy.engine.result.resultproxy object="" at="" 0x102e33940="">이라고 나올 것입니다. 왜냐하면 sqlalchemy의 ResultProxy 객체는 출력해서 볼 수 없기 때문입니다.</sqlalchemy.engine.result.resultproxy>

❯ python3
Python 3.8.6 (default, Oct 16 2020, 14:50:59)
[Clang 11.0.3 (clang-1103.0.32.62)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy as sa
>>> db = {
...     'user'     : 'root',
...     'password' : 'RT27hDosK',
...     'host'     : '0.0.0.0',
...     'port'     : '3306',
...     'database' : 'gregs_list'
... }
>>> DB_URL = f"mysql+pymysql://{db['user']}:{db['password']}@{db['host']}:{db['port']}/{db['database']}?charset=utf8"
>>> conn = sa.create_engine(DB_URL, encoding = 'utf-8')
>>> rows = conn.execute('SELECT * FROM my_contacts')
>>> for row in rows:
...     print(row)
...
('Anderson', 'Jillian', 'jill_anderson@breakneckpizza.com', 'F', datetime.date(1980, 9, 5), 'Technical Writer', 'Palo Alto, CA', 'Single', 'Kayaking, Reptiles', 'Relationship, Friends')
(None, 'Pat', 'patpost@breakneckpizza.com', None, None, 'Postal Worker', 'Princeton, NJ', None, None, None)

위의 방법을 보시면 아시겠지만, 기본적으로 SQL을 이용하는 것과 별 차이가 없습니다. 또한 PostgreSQL와 같은 다른 종류의 데이터베이스를 사용하면, 사용할 수 있는 SQL가 다르기 때문에 각각 DB에 맞춰 코드를 짜야 합니다. 이처럼 같은 기능을 하지만, 각각의 데이터베이스에서는 상이한 표현으로 되어 있는 것들을 같은 함수로 처리할 수 있는 기능이 SQLAlchemy에 들어 있습니다. 이 기능이 바로 ‘SQL Expression Language’입니다. 이것을 이용하여 위의 것과 동일한 일을 하는 코드를 작성하면 아래와 같습니다.

db = {
'user'     : 'root',
'password' : 'RT27hDosK',
'host'     : '0.0.0.0',
'port'     : '3306',
'database' : 'gregs_list'
}
import sqlalchemy as sa

DB_URL = f"mysql+pymysql://{db['user']}:{db['password']}@{db['host']}:{db['port']}/{db['database']}?charset=utf8"

conn = sa.create_engine(DB_URL, encoding = 'utf-8')

meta = sa.MetaData()

my_contacts = sa.Table('my_contacts', meta,
sa.Column('last_name', sa.String),
sa.Column('first_name', sa.String),
sa.Column('email', sa.String),
sa.Column('birthday', sa.Date),
sa.Column('profession', sa.String),
sa.Column('location', sa.String),
sa.Column('status', sa.String),
sa.Column('interests', sa.String),
sa.Column('seeking', sa.String))

meta.create_all(conn)

result = conn.execute(my_contacts.select())
rows = result.fetchall()
print(rows)

이 코드를 보시면 아시겠지만, SQL에서 벗어나, 순수 파이썬 코드처럼 보입니다. 윗 코드를 실행시키면 앞에 한 것과 동일한 결과가 나옵니다. 앞부분은 윗 코드와 동일하지만 데이블에 접근하는 방법은 많이 다릅니다.

Python 3.8.6 (default, Oct 16 2020, 14:50:59)
[Clang 11.0.3 (clang-1103.0.32.62)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy as sa
>>> db = {
...     'user'     : 'root',
...     'password' : 'RT27hDosK',
...     'host'     : '0.0.0.0',
...     'port'     : '3306',
...     'database' : 'gregs_list'
... }
>>> DB_URL = f"mysql+pymysql://{db['user']}:{db['password']}@{db['host']}:{db['port']}/{db['database']}?charset=utf8"
>>> conn = sa.create_engine(DB_URL, encoding = 'utf-8')
>>> meta = sa.MetaData()
>>> my_contacts = sa.Table('my_contacts', meta,
...     sa.Column('last_name', sa.String),
...     sa.Column('first_name', sa.String),
...     sa.Column('email', sa.String),
...     sa.Column('birthday', sa.Date),
...     sa.Column('profession', sa.String),
...     sa.Column('location', sa.String),
...     sa.Column('status', sa.String),
...     sa.Column('interests', sa.String),
...     sa.Column('seeking', sa.String))
>>> meta.create_all(conn)
>>> result = conn.execute(my_contacts.select())
>>> rows = result.fetchall()
>>> print(rows)
[('Anderson', 'Jillian', 'jill_anderson@breakneckpizza.com', datetime.date(1980, 9, 5), 'Technical Writer', 'Palo Alto, CA', 'Single', 'Kayaking, Reptiles', 'Relationship, Friends'), (None, 'Pat', 'patpost@breakneckpizza.com', None, 'Postal Worker', 'Princeton, NJ', None, None, None)]

이것을 이용해서 자료를 입력해보겠습니다. 참고로 생년월일을 입력하기 위해서 datetime가 필요합니다.

import datetime

conn.execute(my_contacts.insert(('Soukup', 'Alan', 'soupup@breakneckbizza.com', datetime.date(1975, 12, 2), 'Aeronautical Engineer', 'San Antonio, TX', 'Married', 'RPG, Programming', 'Nothing')))

윗 코드를 작동시켜보면 자료가 입력된 것을 확인할 수 있습니다.

>>> import datetime
>>> conn.execute(my_contacts.insert(('Soukup', 'Alan', 'soupup@breakneckbizza.com', datetime.date(1975, 12, 2), 'Aeronautical Engineer', 'San Antonio, TX', 'Married', 'RPG, Programming', 'Nothing')))
>>> result = conn.execute(my_contacts.select())
>>> rows = result.fetchall()
>>> print(rows)
[('Anderson', 'Jillian', 'jill_anderson@breakneckpizza.com', datetime.date(1980, 9, 5), 'Technical Writer', 'Palo Alto, CA', 'Single', 'Kayaking, Reptiles', 'Relationship, Friends'), (None, 'Pat', 'patpost@breakneckpizza.com', None, 'Postal Worker', 'Princeton, NJ', None, None, None), ('Soukup', 'Alan', 'soupup@breakneckbizza.com', datetime.date(1975, 12, 2), 'Aeronautical Engineer', 'San Antonio, TX', 'Married', 'RPG, Programming', 'Nothing')]