python으로 mysql 시작해보기

나는 살면서 한번도 웹을 건드릴 일이 없을줄 알았는데, 지인의 부탁으로 어쩔 수 없이 떠맞게 되었다.

디자인은 bootstrap(부트스트랩)으로 하였는데 중요한 데이터 베이스도 슬슬 구축할 필요성이 보여서 구축을 해보려고 한다. 참고로 이 분야는 내 전공이 아니다, 그래서 잘못된 내용이 있을수도 있으니 이점 양해를 구하고 시작한다.

 

MySQL은 오픈소스 데이터 베이스로 유명하다. 나는 학부때 MSSQL을 배워서 그나마 MSSQL를 사용하면 빨리 구축할수 있을거라 예상했으나, 지인의 부탁으로 플랫폼(windows server에 ASP를 예상했으나 ubuntu로 바뀜)이 바뀌어서 MySQL을 사용한다.

 

먼저 패키지 업데이트부터 진행후 설치를 진행한다.

$ sudo apt update
$ apt install mysql-server

그냥 mysql-server 라고 치면 기본적으로 5.7.x 버전이 설치가 된다.

 

* 만약 8 버전을 설치하고 싶다면 다음 저장소(Repository)설정 데비안 파일을 다운로드하고 패키지파일을 실행후 설정을 마치고 $ sudo apt install mysql-server 를 쳐주면 설치가 된다.

 

다음 보안 설정을 해준다.(5.7.x 버전)

$ sudo mysql_secure_installation

참고로 나는 다음과 같이 진행하였다.

$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

위 처럼 데비안 패키지 저장소로부터 설치를 진행했다면 보통 데이터 디렉토리(data directory)는 자동으로 초기화 된다고 한다[1].

 

만약 다음처럼 데이터 디렉토리가 조회되지 않는다면 mysqld --initialize 를 쳐서 초기화를 해준다[1, 2].

$ sudo mysql -uroot -p -e 'SHOW VARIABLES WHERE Variable_Name LIKE "%dir"'

Enter password:
+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| basedir                   | /usr/                      |
| character_sets_dir        | /usr/share/mysql/charsets/ |
| datadir                   | /var/lib/mysql/            |
| innodb_data_home_dir      |                            |
| innodb_log_group_home_dir | ./                         |
| innodb_tmpdir             |                            |
| lc_messages_dir           | /usr/share/mysql/          |
| plugin_dir                | /usr/lib/mysql/plugin/     |
| slave_load_tmpdir         | /tmp                       |
| tmpdir                    | /tmp                       |
+---------------------------+----------------------------+

그리고 다음과 같이 데이터 베이스를 만든다음 유저를 만들고 권한을 주고, 그 다음에 만든 유저로 테스트용 테이블을 만들었다.

$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.31-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'Pass12!@#';
Query OK, 0 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON test.* TO 'testuser'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

$ mysql -utestuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.31-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed

mysql> CREATE TABLE test(
    -> id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> title VARCHAR(255))CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

test 테이블이 잘 되는지 확인해본다.

mysql> INSERT INTO test (title) VALUES ('스바루'), ('에밀리아'), ('렘');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------------+
| id | title        |
+----+--------------+
|  1 | 스바루       |
|  2 | 에밀리아     |
|  3 | 렘           |
+----+--------------+
3 rows in set (0.00 sec)

mysql> exit
Bye

자 이제 마지막으로 제일 중요한 python과의 연동을 확인해본다.

먼저 다음과 같이 MySQL connector(커넥터)를 설치해주어야 한다.(나는 테스트중인 python 버전이 3.6.9 이다.)

$ pip3 install mysql-connector-python

* mysql-connector-python 버전이 8 버전이면 MySQL 경우 8.0, 5.7, 5.6, 5.5 사용이 가능하고 python 경우 3.6, 3.5, 3.4, 2.7이 지원된다[3].

 

설치를 완료했다면 다음과 같이 간단히 db.py 란라는 커넥터를 만들고 실행해본다.

import mysql.connector
from mysql.connector import Error
from datetime import datetime

# configuration for connect to MySQL
host = 'localhost'
database = 'test'
user = 'testuser'
password = 'Pass12!@#'

# logging option
logging = True

# simple connector
def connect():
    conn = mysql.connector.connect(
                host = host,
                database = database,
                user = user,
                password = password
            )

    if logging and conn.is_connected():
        print("Connected to MySQL host: '{}', database: '{}', user: '{}', time: '{}'".format(
            host, database, user, datetime.now()))

    return conn

if __name__ == '__main__':
    connect()

실행해본다.

$ python3 db.py
Connected to MySQL host: 'localhost', database: 'test', user: 'testuser', time: '2020-08-30 00:43:09.697235'

잘 작동됨이 확인이 되었고 다음 test 테이블에 맞는 기본적인 CRUD(create, read, update and delete)를 아래와 같이 만들고 실행해본다.

import mysql.connector
from mysql.connector import Error

from datetime import datetime

host = 'localhost'
database = 'test'
user = 'testuser'
password = 'Pass12!@#'

logging = True

# simple connector
def connect():
    conn = mysql.connector.connect(
                host = host,
                database = database,
                user = user,
                password = password
            )

    if logging and conn.is_connected():
        print("Connected to MySQL host: '{}', database: '{}', user: '{}', time: '{}'".format(
            host, database, user, datetime.now()))

    return conn

# insert datas into test table
def create(table, fields, values):
    # check fields is innumerable or not
    if isinstance(fields, str):
        fields = (fields, )
    # check values is innumerable or not
    if isinstance(values, str):
        values = (values, )

    fields, values = ','.join(fields), "'"+"','".join(values)+"'"
    query = "INSERT INTO {} ({}) VALUES ({})".format(table, fields, values)

    return query

# read datas from test table
def read(table, fields, where=None, orderby=None):
    # check fields is innumerable or not
    if isinstance(fields, str):
        fields = (fields, )

    fields = ','.join(fields)
    if where is None and orderby is None:
        query = "SELECT {} FROM {}".format(fields, table)
    elif where is None:
        query = "SELECT {} FROM {} ORDER BY {}".format(fields, table, orderby)
    elif orderby is None:
        query = "SELECT {} FROM {} WHERE {}".format(fields, table, where)
    else:
        query = "SELECT {} FROM {} WHERE {} ORDER BY {}".format(fields, table, where, orderby)
    return query

# change datas into test table
def update(table, set, where):
    query = "UPDATE {} SET {} WHERE {}".format(table, set, where)
    return query

# delete datas into test table
def delete(table, where):
    query = "DELETE FROM {} WHERE {}".format(table, where)
    return query

if __name__ == '__main__':
    # connect
    conn = connect()
    cursor = conn.cursor()

    # create
    cursor.execute(create('test', ('title'), ('람')))
    cursor.execute(read('test', ('*')))
    print(cursor.fetchall())

    # read
    cursor.execute(read('test', ('id', 'title')))
    print(cursor.fetchall())
    cursor.execute(read('test', ('*'), orderby='id DESC'))
    print(cursor.fetchall())
    cursor.execute(read('test', ('*'), where='id = 2'))
    print(cursor.fetchall())
    cursor.execute(read('test', ('*'), where='id >= 2', orderby='id DESC'))
    print(cursor.fetchall())

    # update
    cursor.execute(update('test', "title = '베아트리스'", 'id = 4'))
    cursor.execute(read('test', ('*')))
    print(cursor.fetchall())

    # delete
    cursor.execute(delete('test', 'id = 1'))
    cursor.execute(read('test', ('*')))
    print(cursor.fetchall())
    
    conn.commit()
    conn.close()

실행해본다.

$ python3 db.py
Connected to MySQL host: 'localhost', database: 'test', user: 'testuser', time: '2020-08-30 02:57:01.585607'
[(1, '스바루'), (2, '에밀리아'), (3, '렘'), (4, '람')]
[(1, '스바루'), (2, '에밀리아'), (3, '렘'), (4, '람')]
[(4, '람'), (3, '렘'), (2, '에밀리아'), (1, '스바루')]
[(2, '에밀리아')]
[(4, '람'), (3, '렘'), (2, '에밀리아')]
[(1, '스바루'), (2, '에밀리아'), (3, '렘'), (4, '베아트리스')]
[(2, '에밀리아'), (3, '렘'), (4, '베아트리스')]

잘 됨을 확인할 수 있다.

추가적으로 SQL injection(인젝션) 대비와 태그가 들어가는거 및 몇 이상한 문자가 흘러 들어가는 것 정도만 막아주면 그럭저럭 사용할수 있지 않을까 생각한다.

참고문헌

1. "How To Install MySQL on Ubuntu 18.04", April 2020, www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04

2. "How to find the mysql data directory from command line in windows", Jul 2013, stackoverflow.com/questions/17968287/how-to-find-the-mysql-data-directory-from-command-line-in-windows

3. "Getting Started with MySQL python Connector", www.mysqltutorial.org/getting-started-mysql-python-connector/