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