파이썬에서 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.cursorsconn = 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.cursorsconn = 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.cursorsconn = 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.cursorsconn = 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.cursorsconn = 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.cursorsconn = 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.cursorsconn = 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() |
