05 - 1 PYMYSQL (데이터베이스 연결, 커저 생성, INSERT)

 

 

오늘은 python으로 mySQL 사용하는 방법을 익혀보자Go!

또 python 이라니

만물 python 같아서 조금 질리는 감이 있지만

데이터 삽입하고 업데이트 할 때는 python 연동을 사용하게 될 수도 있으니

밀히밀히 익혀놔야지.

 

자 다덜 아래 글 읽기 전에 pymysql 설치하는 것 잊지 말긔

!pip install pymysql

---

01 기본구문

01 Database 연결

import pymysql # pymysql 함수 호출

# database 연결
connection = pymysql.connect(host = "DBSM서버 ip", 
							port = "port번호", 
                            user = "계정명", 
                            password = "비밀번호",
                            db = "연결한 데이터 베이스 이름",
                            charset = 'itf-8')
# port 기본값 번호: 3306

02  cursor 생성 

cursor: 연결된 database에 sql문 전송하고 select 결과 조회 기능 제공하는 객체. 입출력 담당

cursor = connection.cursor()

03 SQL문 전송

cursor.execute() 함수 안에 sql문을 작성해서 작업을 실행

cursor.execute("sql문")

04 연결닫기

여러개의 데이터 베이스와 연결할 수 없으니, 작업 끝나면 꼭 연결을 닫아야 한다.

cursor.close()
connection.close()

 

02 테이블 생성

pymysql을 이용해선 주로 DML(insert, update, delete, update)문을 사용하지만, 구문을 익히기 위해 테이블 생성을 해보자!

import mysql

# 1. 데이터 베이스 연결
connection = pymysql.connect(host = '127.0.0.1', # local host: 127.0.0.1
                             port = 3306,
                             user = 'scott',
                             password = 'tiger',
                             db = 'testdb')
                             
# 2. cursor 생성
cursor = connection.cursor()

# 3. sql문 작성(sql 문법으로 작성)
#  create databases
sql = """
create table test_user(
id int auto_increment primary key,
name varchar(30) not null,
email varchar(30) not null unique,
tall decimal(5,2),
birthday date
)
"""
# id, name, email, tall birthday 컬럼을 가진 table을 만드는 sql문 작성

# 4. sql문 실행
cursor.execute(sql)

# 5. 연결 끊기
cursor.close()
connection.close()

 

03 Insert

잠깐 🤚🏻

Insert를 배우기 전, 하나 짚고 가야 할 것이 있다. 

cursor.close(), connection.close()를사용할 필요 없이 하나의 구문으로 파일을 여는 것부터 닫는 것까지 해결할 수 있다.

바로 with 구문이다. 앞으로는 with 구문안에 sql문을 넣어서 한 번에 실행해 보자!

 

test_user desc; 실행 결과

 

with 문 기본 구문

with 연결 코드 as 변수

 

🤚🏻 with 문 활용 구문

# 위에서 만든 테이블에 맞는 정보를 넣어주자.

sql = "insert into test_user(name, email, tall, birthday) values ('김낄희', 'ele@a.com', 100, '2021-03-01')"

with pymysql.connect(host = '127.0.0.1', port = 3306, user = 'scott', password = 'tiger', db = 'testdb', charset = 'utf8') as connection:
	with connection.cursor() as cursor:
    	cnt = cursor.execute(sql)
    	connection.commit()

• with 문으로 데이터 베이스 연결
• with 문으로 cursor 생성
• 자동 commit이 되지 않기 때문에, commit method를 통해 현재 처리 실행하라고 요청! 

 

🤚🏻 함수 구문

def insert_user(name, email, tall, birthday): # name, email, tall, birthday를 변수로 받는 함수 생성
    sql = f"insert into test_user (name, email, tall, birthday) values ('{name}', '{email}', {tall}, '{birthday}')"
    with pymysql.connect(host='127.0.0.1', port=3306, user='scott', password='tiger', db='testdb', charset='utf8') as connection:
        with connection.cursor() as cursor:
            cnt = cursor.execute(sql)
            connection.commit()

• name, email, tall, birthday를 변수로 받는 함수 생성
• f-string 사용해서 변수로 받을 아이들은 { }안에 넣어 줌
• with 구문은 동일한 방식으로 사용

 

🤚🏻 Parameterize Query

SQL 문에서 값이 들어갈 자리에 %S placeholder를 사용한 뒤 execute()에서 placeholder에 넣을 값을 튜플로 제공하는 방법

위에선 f-string을 사용하여 변수 표시 하였지만 여기에선 %S placeholder 사용하는 방법을 배워보자Go!

sql = "insert into test_user(name, email, tall, birthday) values (%s, %s, %s, %s)"
# %s로 들어갈 값 자리 표시
with pymysql.connect(host='127.0.0.1', port=3306, user='scott', password='tiger', db='testdb', charset='utf8') as connection:
        with connection.cursor() as cursor:
            cnt = cursor.execute(sql, ('박붱철', 'sexowl@d.com', 120, 2022-06-06)) # 튜플로 값 전달
            connection.commit()

• SQL문은 구문만 작성하고 값은 placeholder로 표시
• execute 문에서 SQL문 안에 들어갈 값을 tuple( )로 전달 

 

🤚🏻 여러행 Insert

실제 상황에선 우리가 값을 하나만 insert 할일은 ㄴㄴ 분명 여러개 insert 하겠지?

여러행을 for문과 executemany 구문을 사용해서 insert 하는 방법을 알아보자.

 

for문

names = ['김낄희', '박붱춸', '선인장', '황광희']
emeails = ['ele@a.com', 'sexowl@d.com', 'cac@d.com', 'hwang@d.com']
talls = [100, 120, 160, 180]
birthdays = ['2021-03-01', '2022-06-06', '2020-03-03', '1988-08-25']


sql = "insert into test_user(name, email, tall, birthday) values (%s, %s, %s, %s)"
# %s로 들어갈 값 자리 표시
with pymysql.connect(host='127.0.0.1', port=3306, user='scott', password='tiger', db='testdb', charset='utf8') as connection:
        with connection.cursor() as cursor:
            for data in zip(names, emails, talls, birthdays):
            	cursor.execute(sql, data)
          
            connection.commit()

 

 

executemany

insert 할 값들을 가진 리스트를 넣어 한번에 여러행을 insert 하는 구문

datas = [
	['김낄희', 'elephant@a.com', 100, '2021-03-01']
	['박붱춸', 'sexowl@d.com', 120, '2022-06-06']
	['선인장', 'cac@d.com', 160, '2020-03-03']
	['황광희', 'hwang@d.com', 180, '1988-08-25']
]

sql = "insert into test_user(name, email, tall, birthday) values (%s, %s, %s, %s)"
with pymysql.connect(host='127.0.0.1', port=3306, user='scott', password='tiger', db='testdb', charset='utf8') as connection:
        with connection.cursor() as cursor:
        	cursor.executemany(sql, datas)
            connection.commit()

• executemany(sql, 데이터)를 변수로 넣어주어서 여러행 한꺼번에 insert

 

---

update, delete, select는 다음시간에 이어서 배워봐여 열허분 👋