파이썬 MySQL 연동

2023-07-20

 

1. Python과 MySQL 연동

SMALL

 

1.1) 프로그래밍 언어와 데이터베이스 연동 방법

• 데이터베이스 드라이버 만을 이용해서 작업

SQL을 직접 작성

• 데이터베이스와 드라이버 그리고 프레임워크를 이용하는 방식

SQL Mapper : 소스코드와 SQL을 분리시켜서 수행하는 방식 - MyBatis가 대표적인 Framework(우리나라 SI업계에서만 주로 이용, 쉽기는 한데 효율이 떨어)

 

ORM(Object Relation Model) : 프로그래밍 언어의 인스턴스와 관계형 데이터베이스의 행을 매핑시켜서 SQL을 사용하지 않고도 데이터베이스 작업을 할 수 있도록 해주는 방식

 

데이터베이스를 변경해도 수정이 거의 발생하지 않는다.

효율이 좋지만 난이도가 있어, 솔루션 업체에서 주로 이용

최근에는 거의 대부분 이 방식을 사용

 

Django 나 Java의 JPA 등이 대표적인 ORM 프레임워크이다.

 

 

2. 데이터베이스 드라이버 만을 이용해서 작업

2.1) 파이썬과 MySQL 연결을 위한 드라이버 패키지 다운로드 받아서 설치

 

- pyMySQL 패키지 사용 : pip install pyMySQL

 

2.2) 데이터베이스와 연결

• 연결을 위한 정보
• host IP 또는 도메인 : MySQL 서버가 설치된 곳의 IP나 도메인, 자기 컴퓨터는 localhost
• 포트 번호: 컴퓨터에서 서비스를 구분하기 위한 번호, MySQL이나 Maria DB는 기본이 3306
• 사용할 데이터베이스 이름 : MySQL에서는 DB이름이고 Oracle에서는 서비스 이름이나 SID

 

- user : 사용자 아이디

 

- passwd : 사용자비밀번호 

 

- charset : 인코딩 방식

 

• MySQL 데이터베이스 연결하고 접속 종료

변수 = pymysql.connect(host = ~~~. port = ~~~, user = ~~~, passwd=~~~, db = ~~~, charset=~~~)

#성공시 아무런 메세지 없고, 실패하면 에러 메시지와 함께 예외가 발생

 

#닫기

변수.close()

 

#연동 코드 : 접속 정보는 자신의 데이터베이스에 맞게 수정이 필요

import pymysql
import sys
try :
    #데이터베이스에 연결
    con = pymysql.connect(host= 'localhost', port = 3306, user = 'root', passwd = '~~~', db = 'svng', charset = 'utf8')

    print(con)


except :
    print("예외 발생", sys.exc_info())
finally :
    if con != None :
        con.close()

#위와 같이 코드를 이용하면 파이썬에서 본인컴퓨터의 MySQL을 연동할 수 있습니다

 

 

2.3) DML 작업

• 데이터베이스 연결 객체(con)가 cursor 함수를 호출해서 SQL 실행 객체를 리턴받는다.
• SQL 실행 객체.execute(SQL 문장[, 파라미터 값 나열])

파라미터 값을 SQL에 삽입할 수 있고 서식을 설정한 다음 대입해도 됩니다.

최근에는 보안 상의 문제로 서식을 설정해서 대입하는 것을 권장합니다.

• 데이터베이스 연결 객체(con).commit() 을 호출하면 원본에 반영되고 rollback()을 호출하면 원본에 반영되지 않는다.

 

2.4) 데이터 삽입 작업

 

작업할 테이블 확인 - DBeaver에서 수행

 

파이썬에서 데이터 삽입

import pymysql
import sys
try :
    #데이터베이스 연결객체를 생성
    con = pymysql.connect(host= 'localhost', port = 3306, user = 'root', passwd = '252585', db = 'svng', charset = 'utf8')

    #SQL 실행 객체 생성
    cursor = con.cursor()
    #SQL 실행 - 값을 직접 SQL에 작성
    cursor.execute("INSERT INTO DEPT VALUES(11, '비서', '신안')") #덜 권장
    
    #SQL 실행 - SQL에 서식을 설정하고 파라미터를 대입하는 코드 작성
    cursor.execute("INSERT INTO DEPT VALUES(%s, %s, %s)",(12, '기획', '제주')) #보안 가이드 문서 권장 방식

    #원본에 반영
    con.commit()
except :
    print("예외 발생", sys.exc_info())
finally :
    if con != None :
        con.close()

#연결 완료

 

 

만약 DEPT의 12번의 데이터를 바꾸고 싶다면!!

 

코드 :

#12 번 데이터의 부서를 영업 그리고 위치를 서초로 수정
    cursor.execute("UPDATE DEPT SET DNAME = %s, LOC = %s WHERE DEPTNO=%s",('영업', '서초',12))

혹은 DEPT의 12번 데이터를 삭제한다면!

#12번 데이터 삭제
    cursor.execute("DELETE FROM DEPT WHERE DEPTNO = %s",(12))

 

 

2.7) DQL 작업

• cursor 객체를 이용해서 select 구문 수행
• 하나의 데이터만 가져오고자 하는 경우는 cursor 객체를 가지고 fetchone 메서드를 호출하면 하나의 튜플로 데이터를 리턴

fetchall 메서드를 호출하면 tuple의 tuple로 모든 데이터를 리턴합니다.

• fetchone 을 호출하는 경우는 기본키를 조건으로 해서 조회하는 경우이고 그 이외의 경우는 fetchall 을 호출하여 아이디 존재 여부를 확인 -> 데이터 1개 조회

    #12번 데이터를 조회
    cursor.execute(
        "SELECT * FROM DEPT WHERE DEPTNO = %s",(10)
    )


    #검색 결과 중 하나의 데이터를 읽어오는 것
    #검색된 결과가 없으면 None이고 존재하면 Tuple
    record = cursor.fetchone()
    if record == None :
        print("검색된 데이터가 없음")
    else :
        for attr in record :
            print(attr)

    print(record) 
#10
#ACCOUNTING
#NEW YORK
#(10, 'ACCOUNTING', 'NEW YORK')

 

• 데이터 여러개 조회

# 특정 값보다 큰 deptno 조회
    cursor.execute(
        "SELECT * FROM DEPT WHERE DEPTNO >= %s",(10)
    )


    #검색 결과 전체 데이터 읽어오기
    #검색된 결과가 없으면 None이고 존재하면 Tuple
    #여러개의 데이터를 가져오는 경우는 데이터가 없는 경우 빈 튜플을 리턴한다.
    #데이터의 개수가 0인지 확인해야 한다.
    record = cursor.fetchall()
    if len(record) == 0 :
        print("검색된 데이터가 없음")
    else :
        for attr in record :
            print(attr)

    print(record) #() 출력

 

2.8) BLOB

 

• 데이터베이스에 파일을 저장하는 방법

                파일을 파일 서버(AWS의 S3 또는 Google Firebase 의 File Storage 서비스)에 별도로 저장하고 그 경로를 저장하는 방법

                파일의 이름과 내용을 모두 데이터베이스에 저장하는 방식

• 파일을 파일 서버에 별도로 저장하면 데이터베이스에 저장하는 것 보다 비용이 저렴하지만 파일을 사용할려고 할 때마다 다운로드를 받아야 합니다.

SELECT 구문 만으로는 파일을 사용할 수 없습니다.

 

• 최근에는 대부분 파일을 데이터베이스에 저장하지는 않음.
• 파일의 내용을 데이터베이스에 저장하고자 하면 데이터 타입을 blob나 longblob로 설정해야 합니다.

파일의 내용을 저장할 때 파일의 형식을 알아야 하기 때문에 파일 이름을 같이 저장합니다.

파이썬에서는 blob는 bytes와 매핑 됩니다.

 

-- 파일을 저장할 수 있는 테이블
CREATE TABLE dj(
	filename varchar(1000),
	filecontent longblob
);


desc dj;

 

#데이터 베이스에 저장된 이미지 파일을 읽어오기

#데이터 읽어오기
    cursor.execute("select * from dj")
    data = cursor.fetchone()
    #2번째 데이터가 blob 이므로 두번째 데이터를 파일로 변경
    print(data[0])

    #파일을 쓰기 모드로 생성
    f = open(data[0], 'wb')

    #읽은 데이터를 파일에 기록
    f.write(data[1])
    f.close()


    #con.commit()

 

---

오라클에 관하여

1. Oracle

• 미국의 오라클 사에서 만든 관계형 데이터베이스
• 우리나라 대기업과 공공기관에서 가장 많이 사용하는 데이터베이스

(오라클에서 안되면 다른 DB로는 안된다)

• 버전은 Enterprise(ORCL), Standard, Express Edition(XE) 이 존재

학습을 하는 경우는 Express Edition이면 충분!

관리의 영역은 Enterprise 버전 사용

 

2. 설치

2.1) 운영체제에 직접 설치

 

• MAC에서는 설치가 안됨
• Windows와 Linux에서 설치가 가능한데 실제로는 Linux에 설치해서 사용하는 경우가 대부분
• Oracle Web Site에서 회원가입 후 다운로드 받아 설치

2.2) Docker에 설치

• Windows는 설치에 아무런 제약이 없지만, MAC의 M1, M2 프로세서 사용시 별도의 프로그램이 필요합니다.
• 11g : docker run --name 컨테이너이름 -d -p 1521:1521 jaspeen/oracle-xe-11g

sid는 xe가 되고 슈퍼 관리자는 sys,  관리자는 system, 비번은 oracle

 

• 21c : docker run --name 컨테이너이름 -d -p 1521:1521 -e ORACLE_PASSWORD=관리자비밀번호 gvenzl/oracle-xe

sid는 xe가 되고 관리자는sys, system, 비번은 설정한 비밀번호

 

2.3) 접속

• 접속에 필요한 정보

Oracle이 설치된 컴퓨터의 IP 또는 도메인

포트번호

sid 나 service name

계정

비밀번호

드라이버

 

• 강의장에서 오라클을 사용하고자 하면

무선 네트워크를 201-1 로 설정

 

host : 192.168.0.4

포트번호 : 1521

sid : xe

계정 : user01 ~ user30

비밀번호 : user01 ~ user 30

드라이버는 다운로드

 

INLINE VIEW / FETCH 싸우면 INLINE 이 이김! 성능이 더 좋음