Sieun Kim

안녕하세요 :)

3 분 소요

SQL과 파이썬 연결

파이썬과 MySQL의 연동

파이썬 pymysql 라이브러리를 설치한 후에는 MySQL과 연동하는 데이터베이스 연동 프로그램을 작성할 수 있다.

연동 프로그래밍 기본

파이썬과 MySQL 데이터베이스를 연동해보겠다.

연동 프로그램을 위한 쇼핑몰 생성

먼저 MySQL 워크벤치를 실행해서 ‘혼공 쇼핑몰 DB(solo DB)’를 생성한다.

DROP DATABASE IF EXISTS soloDB;
CREATE DATABASE soloDB;

soloDB를 생성한 후에는 더 이상 MySQL 워크벤치를 사용하지 않는다.

파이썬에서 데이터 입력

파이썬에서 데이터를 입력하기 위해서는 다음과 같은 단계를 거치게 된다.

  1. MySQL 연결하기

  2. 커서 생성하기

  3. 테이블 만들기

  4. 데이터 입력하기

  5. 입력한 데이터 저장하기

  6. MySQL 연결 종료하기

MySQL를 사용하기 위해서는 먼저 관련 모듈인 pymysql을 임포트한 후 pymysql.connect()로 데이터베이스와 연동해야 한다.

pymysql.connect(host=서버IP주소, user=사용자, password=암호, db=데이터베이서, cahrset=문자세트)

import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='0000', db='soloDB', charset='utf8')

커서(cursor)는 데이터베이스에 SQL 문을 실행하거나 실행된 결과를 돌려받는 통로로 생각하면 된다.

cur = conn.cursor()

테이블을 만드는 SQL 문을 커서이름.execute() 함수의 매개변수로 넘겨주면 SQL 문이 데이터베이스에 실행된다.
파이썬에서도 MySQL 워크벤치에서 사용한 것과 동일한 SQL 문을 사용하면 된다.

cur.execute("CREATE TABLE userTable (id char(4), userName char(15), email char(20), birthYear int)")

-> 결과의 번호는 0 또는 다른 번호가 나와도 상관없다.

데이터 입력도 SQL 문을 사용해야 하므로 커서이름.execute() 함수를 사용한다.

cur.execute("INSERT INTO userTable VALUES ('hong', '홍지윤', 'hong@naver.com', 1996)")
cur.execute("INSERT INTO userTable VALUES ('kim', '김태연', 'kim@daum.net', 2011)")
cur.execute("INSERT INTO userTable VALUES ('star', '별사랑', 'star@paran.com', 1990)")
cur.execute("INSERT INTO userTable VALUES ('yang', '양지은', 'yang@gmail.com', 1993)")

-> 결과의 번호는 1이 나온다.

임시로 저장된 상태로, 이를 확실하게 저장한는 것을 커밋(commit)이라고 부른다.

conn.commit()

데이터베이스를 모두 사용했다면 연결한 데이터베이스를 닫아야 한다.

conn.close()

연동 프로그래밍 활용

이제 완전한 파이썬 응용 프로그램을 만들어보겠다.

완전한 데이터 입력 프로그램의 완성

사용자가 반복해서 데이터를 입력하는 코드를 작성해보겠다.
앞에서 생성한 soloDB의 userTable에 직접 키보드로 입력받아 한 행씩 계속해서 데이터를 입력하겠다.
그리고 더 이상 입력할 데이터가 없으면 Enter 키를 입력하여 종료하는 것을 처리하겠다.

import pymysql

# 전역변수 선언부
conn, cur = None, None
data1, data2, data3, data4 = "", "", "", ""
sql=""

# 메인 코드
conn = pymysql.connect(host='127.0.0.1', user='root', password='0000', db='soloDB', charset='utf8')
cur = conn.cursor()

while (True) :
    data1 = input("사용자 ID ==> ")
    if data1 == "" :
        break;
    data2 = input("사용자 이름 ==> ")
    data3 = input("사용자 이메일 ==> ")
    data4 = input("사용자 출생연도 ==> ")
    sql = "INSERT INTO userTable VALUES('" + data1 + "','" + data2 + "','" + data3 + "'," + data4 + ")"
    cur.execute(sql)

conn.commit()
conn.close()

INSERT 문으로 입력한 데이터를 sql 변수에 문자열로 만들 때 주의할 점은 data1 ~ data3는 작음따옴표(‘)로 묶어야 하고, 마지막 data4는 정수이므로 작은따옴표로 묶으면 안 된다.

사용자 ID ==>  su  --> 사용자가 입력한 값
사용자 이름 ==>  수지  --> 사용자가 입력한 값
사용자 이메일 ==>  suji@hanbit.com  --> 사용자가 입력한 값
사용자 출생연도 ==>  1994  --> 사용자가 입력한 값
사용자 ID ==>  --> 사용자가 입력한 값(Enter 키를 누르면 종료)

MySQL의 데이터 조회를 위한 파이썬 코딩 순서

파이썬으로 데이터를 조회하기 위해서는 다음과 같은 단계를 거치게 된다.

  1. MySQL 연결하기

  2. 커서 생성하기

  3. 데이터 조회하기

  4. 조회한 데이터 출력하기

  5. MySQL 연결 종료하기

데이터를 조회하는 순서는 데이터 입력 순서와 비슷하지만 3, 4번이 다르다.
3번에서는 커서에 SELECT로 조회한 결괄르 한꺼번에 저장해 놓는다.
그리고 4번에서 조회한 데이터를 fetchone() 함수로 한 행씩 접근한 후 출력한다.
또한 조회하는 것은 데이터를 입력하거나 변경하는 것이 아니므로 굳이 커밋(저장)을 해줄 필요는 없다.

완전한 데이터 조회 프로그램의 완성

import pymysql

# 전역변수 선언부
con, cur = None, None
data1, data2, data3, data4 = "", "", "", ""
row=None

# 메인 코드
conn = pymysql.connect(host='127.0.0.1', user='root', password='0000', db='soloDB', charset='utf8')
cur = conn.cursor()

cur.execute("SELECT * FROM userTable")

print("사용자ID    사용자이름    이메일        출생연도")
print("----------------------------------------------------")

while (True) :
    row = cur.fetchone()
    if row== None :
        break
    data1 = row[0]
    data2 = row[1]
    data3 = row[2]
    data4 = row[3]
    print("%5s   %15s   %20s   %d" % (data1, data2, data3, data4))

conn.close()

fetchone() 함수로 조회된 결과가 저장된 row 변수에는 튜블 형식으로 각 행 데이터가 저장된다.
예로 첫 번째 행은 “hong”, “홍지윤”, “hong@naver.com”, 1996 형식으로 저장되어 있다.
그래서 data1, data2, … 방식으로 추출해서 형식에 맞춰 한 줄에 출력한다.

사용자ID    사용자이름    이메일        출생연도
----------------------------------------------------
 hong               홍지윤         hong@naver.com   1996
  kim               김태연           kim@daum.net   2011
 star               별사랑         star@paran.com   1990
 yang               양지은         yang@gmail.com   1993
   su                수지        suji@hanbit.com   1994

참고