[pymysql] python에서 mysql 사용

1. 먼저 pymysql을 설치한다. 

2. 데이터 베이스 생성

# 데이터 베이스 생성
import pymysql.cursors
conn = pymysql.connect(host='localhost',user='root',password=pwd,charset='utf8')
try:
    with conn.cursor() as cursor:
        sql = 'CREATE DATABASE example'
        cursor.execute(sql)
    conn.commit()

finally:
    conn.close()

3. 테이블 생성

# 테이블 생성
import pymysql.cursors

conn = pymysql.connect(host='localhost',user='root',password=pwd,charset='utf8',db='example')
try:
    with conn.cursor() as cursor:
        sql = '''
            CREATE TABLE users (
                id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                name varchar(255) NOT NULL,
                email varchar(255) NOT NULL
             )ENGINE=InnoDB DEFAULT CHARSET=utf8
'''
# user라는 table생성하고 열 이름과 타입 지정
        cursor.execute(sql)
    conn.commit()
finally:
    conn.close()

 

4. 데이터 삽입

# 데이터 삽입
import pymysql.cursors

conn = pymysql.connect(host='localhost',user='root',password=pwd,db='example',charset='utf8')

try:
    with conn.cursor() as cursor:
        sql = "INSERT INTO users (name,email) VALUES (%s,%s)" # users라는 테이블 안에 email, password 값 추가
        cursor.execute(sql,('Jake','test@test.com'))
    conn.commit()
    print(cursor.lastrowid)
finally:
    conn.close()

 

5. 데이터 조회

# 데이터 조회(select)
import pymysql.cursors

conn = pymysql.connect(host='localhost',user='root',password=pwd,db='example',charset='utf8')

try:
    with conn.cursor() as cursor:
        sql = 'SELECT * FROM users WHERE email=%s' # email이 test@test.com인거 행 뽑아내기
        cursor.execute(sql,('test@test.com',))
        result = cursor.fetchone()
        print(result)
finally:
    conn.close()

(1, 'Jake', 'test@test.com')

 

 

6. 데이터 삽입 후 추출

# 데이터 삽입 후 추출
import pymysql.cursors

conn = pymysql.connect(host='localhost',user='root',password=pwd,db='example',charset='utf8')

try:
    with conn.cursor() as cursor:
        sql = "INSERT INTO users (name, email) VALUES (%s,%s)"
        cursor.execute(sql,('Kim','your@tet.com'))
    conn.commit()
    
    with conn.cursor() as cursor:
        sql = 'SELECT * FROM users' # 조건없이 모든 행 추출
        cursor.execute(sql)
        result = cursor.fetchall() # fetchall 사용
        print(result)
finally:
    conn.close()

((1, 'Jake', 'test@test.com'), (2, 'Kim', 'your@tet.com'))

 

7. 데이터 수정

# 데이터 수정(updqte)
import pymysql.cursors

conn = pymysql.connect(host='localhost',user='root',password=pwd,db='example',charset='utf8')

try:
    with conn.cursor() as cursor:
        sql = "UPDATE users SET email=%s WHERE email=%s" # email 주소 변경(변경값, 변경 전)
        cursor.execute(sql,('my@test.com','test@test.com'))
    conn.commit() # commit은 모든 작업을 정상적으로 잘 처리하겠다고 확정하는 명령어
    print(cursor.rowcount) # 데이터를 가져오는 경우 전체 개수

    with conn.cursor() as cursor:
        sql = "SELECT * FROM users"
        cursor.execute(sql)
        result = cursor.fetchall()
        # 모든 행의 개수가 나오게 됨
        print(cursor.rowcount)
        print(result)
finally:
    conn.close()

1
2
((1, 'Jake', 'my@test.com'), (2, 'Kim', 'your@tet.com'))

 

8. 데이터 삭제

# 데이터 삭제(delete)
import pymysql.cursors

conn = pymysql.connect(host='localhost',user='root',password=pwd,db='example',charset='utf8')

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

    with conn.cursor() as cursor:
        sql = "SELECT * FROM users"
        cursor.execute(sql)
        result = cursor.fetchall()
        print(cursor.rowcount)

finally:
    conn.close()