[python] MySQL 사용법 예제

파이썬에서 PyMySQL을 이용한 MySQL 사용법을 알아보자.

PyMySQL을 설치해야 한다.

$ pip install PyMySQL

MySQL 접속 아이디는 user이고, 패스워드는 설정되지 않은 것으로 가정한다.

데이터베이스(database) 생성

import pymysql.cursors

conn = pymysql.connect(host='localhost',
        user='user',
        password=None,
        charset='utf8mb4')

try:
    with conn.cursor() as cursor:
        sql = 'CREATE DATABASE test'
        cursor.execute(sql)
    conn.commit()
finally:
    conn.close()

테이블(table) 생성

import pymysql.cursors

conn = pymysql.connect(host='localhost',
        user='user',
        password=None,
        db='test',
        charset='utf8mb4')

try:
    with conn.cursor() as cursor:
        sql = '''
            CREATE TABLE users (
                id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                email varchar(255) NOT NULL,
                password varchar(255) NOT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8
'''
        cursor.execute(sql)
    conn.commit()
finally:
    conn.close()

데이터 삽입(insert)

import pymysql.cursors

conn = pymysql.connect(host='localhost',
        user='user',
        password=None,
        db='test',
        charset='utf8mb4')

try:
    with conn.cursor() as cursor:
        sql = 'INSERT INTO users (email, password) VALUES (%s, %s)'
        cursor.execute(sql, ('test@test.com', 'my-passwd'))
    conn.commit()
    print(cursor.lastrowid)
    # 1 (last insert id)
finally:
    conn.close()

데이터 조회(select)

import pymysql.cursors

conn = pymysql.connect(host='localhost',
        user='user',
        password=None,
        db='test',
        charset='utf8mb4')

try:
    with conn.cursor() as cursor:
        sql = 'SELECT * FROM users WHERE email = %s'
        cursor.execute(sql, ('test@test.com',))
        result = cursor.fetchone()
        print(result)
        # (1, 'test@test.com', 'my-passwd')
finally:
    conn.close()

import pymysql.cursors

conn = pymysql.connect(host='localhost',
        user='user',
        password=None,
        db='test',
        charset='utf8mb4')

try:
    with conn.cursor() as cursor:
        sql = 'INSERT INTO users (email, password) VALUES (%s, %s)'
        cursor.execute(sql, ('your@test.com', 'your-passwd'))
    conn.commit()

    with conn.cursor() as cursor:
        sql = 'SELECT * FROM users'
        cursor.execute(sql)
        result = cursor.fetchall()
        print(result)
        # ((1, 'test@test.com', 'my-passwd'), (2, 'your@test.com', 'your-passwd'))
finally:
    conn.close()

데이터 수정(update)

import pymysql.cursors

conn = pymysql.connect(host='localhost',
        user='user',
        password=None,
        db='test',
        charset='utf8mb4')

try:
    with conn.cursor() as cursor:
        sql = 'UPDATE users SET email = %s WHERE email = %s'
        cursor.execute(sql, ('my@test.com', 'test@test.com'))
    conn.commit()
    print(cursor.rowcount) # 1 (affected rows)
finally:
    conn.close()

데이터 삭제(delete)

import pymysql.cursors

conn = pymysql.connect(host='localhost',
        user='user',
        password=None,
        db='test',
        charset='utf8mb4')

try:
    with conn.cursor() as cursor:
        sql = 'DELETE FROM users WHERE email = %s'
        cursor.execute(sql, ('my@test.com',))
    conn.commit()
    print(cursor.rowcount) # 1 (affected rows)
finally:
    conn.close()

위의 예제를 샐행한 후 데이터베이스를 조회해 보면 아래와 같이 나오는 것을 확인할 수 있다.