Data Analyst로 취업하기 (4) SQL_2

Data Analyst로 취업하기 (3) SQL_1

 

이전 포스팅에 이어서 Python 을 통해서 MySQL Server 에 database 의 생성/삭제하고 생성된database에 table 을 CRUD 할 준비를 해본다.

pip install PyMySQL 을 통해 라이브러리를 설치 한 후, PyMySQL의 example code를 보자

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db',
                             cursorclass=pymysql.cursors.DictCursor)

with connection:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)

pymysql.connect 함수의 매개변수로 host, user, password, databse, cursorclass 를 정해줬다.

이후 connection.cursor() 함수를 통해 database 에 table 을 CRUD 하는 구조인것 같다.

해당 코드를 활용하기에 앞서 먼저 config.json 파일을 만들어준다.

{
    "localhost": "123.456.7.89", 
    "user": "root",
    "password": "write_my_password"
}

localhost 는 인터넷 연결 속성에 IPv4 주소 를 넣어주면 된다.

이후 다시 python 코드로 돌아와서 코드를 수정해준다.

import json #
import pymysql.cursors

config = open('config.json') #
config = json.load(config) #

# Connect to the database
connection = pymysql.connect(host=config["localhost"], #
                             user=config["user"], #
                             password=config["password"], #
                             database='db',
                             cursorclass=pymysql.cursors.DictCursor)

with connection:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)

github 에 연동되어 있다면, config.json 파일은 꼭 .gitignore에 입력한 후에 commit 해주자

위의 코드를 실행하려고 하면 에러가 뜬다.

 

첫번째 이유는 user 를 root 로 설정했기 때문이다.

pymysql.err.OperationalError: (1130, "YOUR_HOST' is not allowed to connect to this MySQL server")

MySQL 8.0 Command Line Client 에서 새로운 유저를 만들고, 권한을 부여해주자

mysql> CREATE USER 'YOUR_NEW_USERNAME'@'123.456.7.89' IDENTIFIED BY 'YOUR_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_NEW_USERNAME'@'123.456.7.89' WITH GRANT OPTION;

이후 config.json 파일에서 새로 만든 유저정보로 업데이트 하면 권한 문제는 해결된다.

 

두번째 이유는 db 라는 databse 가 아직 존재하지 않기 때문이다.

pymysql.err.OperationalError: (1049, "Unknown database 'db'")

다양한 해결방법이 있겠지만, 가장 간단한 방법은 connect 해야하는 database 를 지정하지 않으면 된다.

connection = pymysql.connect(host=config["localhost"],
                             user=config["user"],
                             password=config["password"],
                             database='', #
                             cursorclass=pymysql.cursors.DictCursor)

 

이제 database 를 create delete 하는 함수들을 만들어보자

# Create a database
def create_db(name=None):
    if name == None:
        print("Please enter database name")
    else:
        conn = pymysql.connect(host=config["localhost"], user=config["user"], password=config["password"], database='', port=3306)
        cursor = conn.cursor()

        sql = 'CREATE DATABASE ' + name
        
        try:
            cursor.execute(sql)
            print("created db", name)
        except Exception as e:
            print(e)
        conn.commit()
        conn.close()

# Delete a database
def delete_db(name=None):
    if name == None:
        print("Please enter database name")
    else:
        conn = pymysql.connect(host=config["localhost"], user=config["user"], password=config["password"], database='', port=3306)
        cursor = conn.cursor()

        sql = 'DROP DATABASE ' + name
        try:
            cursor.execute(sql)
            print("deleted db", name)
        except Exception as e:
            print(e)
        conn.commit()
        conn.close()
        
create_db(name="studysql")
delete_db(name="studysql")

 

위 함수들을 실행하면, 실제로 MySQL Server에 database가 생성되고 삭제되는걸 확인할 수 있다.

코드를 보면 cursor 라는 함수가 보이는데, database 와 연결된 후 cursor 를 통해서 sql 을 execute 시키는걸 볼 수있다.

그리고 cursor 는 생성된 함수 밖으로 return 을 하여도 잘 작동을 하는것 같다.

# Connect to the database
def connect_db(name=None):
    if name == None:
        print("Please enter database name")
    else:
        try:
            conn = pymysql.connect(host=config["localhost"], user=config["user"], password=config["password"], database=name, port=3306)
            cursor = conn.cursor()
            return cursor
        except Exception as e:
            print(e)
         
cursor = connect_db(name="studysql")

sql = '''
    CREATE TABLE USERS (
        UserID int,
        LastName varchar(255),
        FirstName varchar(255),
        City varchar(255)
    );
'''
cursor.execute(sql)

# >>> cursor.execute(sql)
# 0

사실 이부분이 확실하지 않은데 우선 코드는 돌아간다... 문제가 된다면 분명 나중에 코드가 멈추겠지...

이제 database 를 생성/삭제 할 수 있으니 다음번에는 database 에서 table을 CRUD 해봐야겠다.

위에서 사용한 예제 코드는 pypi.org/project/PyMySQL/ 에서 볼 수 있다.