You cannot see this page without javascript.

메뉴 건너뛰기

WHKorea

기술문서 [python] MySQL 사용법 예제

구피천사 2018.10.23 16:24 조회 수 : 35

[python] MySQL 사용법 예제

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

PyMySQL을 설치해야 한다.

1
$ pip install PyMySQL

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

데이터베이스(database) 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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) 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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()

 

위로