[파이썬으로 SQL] pymysql기반 MySQL다루기

파이썬으로 Mysql을 다루려면 라이브러리가 필요로 하다. 그 중에서 가장 다루기 쉬운게  'pymysql'

 

🐩 pymysql라이브러리 설치

pymysql을 가지고 mysql을 어떻게 핸들링하는지는 여러 라인이 필요로 한데, 아래는 기본 코드 패턴을 정리한 것이다.

1. pymysql 모듈 import  (모듈 == 라이브러리)

2. pymysql.connect() 메소드를 사용하여 mysql에 연결

   * 호스트명,포트, 로그인, 암호, 접속할 DB 등 파라미터로 지정한다.

3. MYSQL 접속이 성공하면 Connection 객체로부터 cursor()메서드를 호출하여 cursor객체를 가져온다.

4. cursor객체의 execute()메서드를 사용하여 sql 문장을 DB에 전송

5. SQL 쿼리의 경우 Cursor 객체의 fetchall(), fetchone(), fetchmany() 등의 메서드를 사용하여 서버로부터 가져온 데이터를 코드에서 활용한다.

6. 삽입, 갱신, 삭제 등의 DML문장을 실행하는 경우, INSERT/ UPDATE/ DELETE 후 Connection 객체의 commit()메스드를 사용하여 데이터를 확정

7. Connection 객체의 close()메서드를 사용하여 DB연결을 닫음.

 

🐩 pymysql.connect() 메소드를 사용하여 MySQL에 연결한다. (접속)

파라미터 설명

host = 자기 pc면 자기 pc에서 자기 pc에 있는 mysql프로그램을 접속한다라고 하면 여기는 'localhost'라고 하거나 보안때문에 pc마다 안되면 127.0.0.1로 쓰거나  0.0.0.0으로 써도 된다. 보안 설정이 pc마다 달라서 잘 안될 수 있다.

port= 보통은 우리가 포트 자체를 바꾸지 않으면 무조건 3306을 default로 쓰고 있다. 포트란 컴퓨터 안에 여러 프로그램이 있고, 이 각 프로그램이 다른 컴퓨터의 프로그램과 통신을 한다고 가정을 하면 우리 컴퓨터의 프로그램이 다른 컴퓨터의 어떤 프로그램에 통신을 해야할지 알 수 없다.그래서 기본적으로 네트워크 기술에서는 각각의 프로그램 마다 포트라는 번호를 설정하게 되어 있다. 그래서 네트워크에 접속되어 잇는 프로그램은 각마다 pc에서 유니크한 포트라는 번호를 가지고 있다. mysql의 경우도 네트워크를 통해서 접속을 할 수 있어서 기본적으로 포트번호가 있어야 하는데, 이거를 우리가 시스템 관리자가 서버 관리자가 아닌 이상 포트 번호를 임의로 바꿀 일은 없다. 그래서 default로 3306이다.

user= 새로 만든 적이 없다면 root

passwd = 내가 workbench에서 지정한 패스워드

db = 데이터베이스인데, 이게 mysql서버에 접속하는 것이 아니라, mysql서버 안에 있는 특정 DB를 쓰겠다고 해서 그 db에 접속을 해야한다. 그래서 이 pymysql을 접속하려면 DB를 하나 별도로 만들어야 한다. 이거는 우리들이 각자 워크벤치로 들어가서  CREATE DATABASE ecommerce로 만든다.

charset= 요즘은 유니코드를 기본으로 해서 이 부분은 필요로 하지 않으나, 이거는 한글 인코딩과 관련이 있다.

에러가 없으니 접속에 성공한 것이다.

db가 이제 접속할 수 있는 객체로 만들어진 것이다. 그 다음에는 내가 데이터를 어디까지 읽었는지를 별도의 객체로 가지고 있다. 그것이 cusor()라는 매소드를 통해서 가지고 올 수 있다. 사실 지금은 맨 처음에 키는 거라서 현재 위치는 맨 앞일것이다. 그래서 이거를 가지고 오는 방법은

이렇게 하여 ecommerce가 저장될 현재 메모리 위치를 가지고 온다.

그 다음에 이 위치 상에서 우리가 sql을 하나 만들어서 .execute()로 실행을 하면 된다. 근데 이를 위해서 또 sql이 필요로 하다.

 

* Cursor는 control structure of database입니다.(연결된 객체)

sql을 만들때는 문장으로 쿼리를 넣으면 되는데 sql이 여러 줄에 써 있는 경우가 많다. 그래서 이거를 파이썬에서 하나의 문자열인  """문장""" 처리한다.

그래서 이렇게 작성된 sql쿼리문에다가 cusor로 만들어진 객체를 통하여 execute를 한 다음 실행하면 된다.

※ 여기서 ecommerce는 데이터베이스를 제어하는 객체 즉, 얘가 따라다니면서 데이터베이스를 조정한다.

 

MYSQL 데이터를 처리하는 방식은 크게 두 가지가 있다. 하나는 수정, 또 하나는 읽기이다. 지금 우리가 한 작업은 데이터베이스에 테이블이라는 것을 만든 것이다. 이럴 경우에는 마지막에 COMMIT을 실행해줘야한다. 그래야 마지막에 최종적으로 fix가 된다.

 

접속을 끊어주려면

이렇게 해서 우리가 만들어진 테이블을 볼 수 있다.

 

 

🐩 pymysql로 데이터  입력

 

우선은 수정/ 삭제를 하면 어느정도 데이터가 있어야 실험을 할 수 있으니 반복문을 사용하여 데이터를 넣어보도록 한다.

여기서

 

여기다가 sql구문을 매번 실행을 하는 것이 좋다. sql구문을 한꺼번에 만들어서 한방에 실행시키는 것도 가능하지만, 보통은 그렇게 하지 않는다. 한방에 하면 어디서 문제가 발생했는지 모르기 때문에 한 쿼리문씩 실행을 해야한다.

commit명령은 execute를 여러 번 한 후에 마지막에 DB를 연결을 닫기 직전에 한 번만 하면 된다.

 

🐩 pymysql로 데이터  조회

기본적으로는 데이터를 읽는 것은 commit()만 빼면 된다.

 

데이터 조회 메소드

• fetchall(): 쿼리 결과의 모든 행을 가져온다. 결과는 튜플로 반환

cursor.execute(쿼리문 또는 쿼리문이 담긴 변수) → 커서로 쿼리 구문을 직접 넣어서 실행해도 된다. 

rows=cursor.fetchall() → 위 명령문 커서와 동일한 커서에 fetchall을 하여 일종의 list로 가져와진다.(row에 리스트 담김)

for row in rows: print(row) → 반복문으로 하여 각각의 아이템을 출력한다. row는 각각의 행을 나타내고, 이 각 행에는 여러개의 컬럼값이 존재한다. 그래서 이거는 파이썬의 튜플 형태로 구성해준다. 그래서 맨 앞에 있는 것은 0번 그 다음 1로 해서 인덱스 번호로 특정 컬럼 값도 추출할 수 있다.

 

• fetchone(): 쿼리 결과의 다음 행을 가지고 온다. 하나의 튜플이 반환되며, 각 튜플의 요소는 필드를 나타낸다. 더이상 가져올 행이 없다면 None을 출력한다.

전체 데이터를 보여주는 명령어 이렇게 하면 fetchall처럼 동작하게 된다.

cursor.execute("SELECT*FROM myTable") → 이렇게 하면 전체 데이터를 가지고 오는거니 여러 개의 데이터를 가져온다. 이 상태에서 

row=cursor.fetchone() → 이거를 실행하면 맨 위 첫 행 하나만 가지고 온다. 그러면 이 행은 반복문 없이 바로 출력하거나 

while row Is not None: print(row)  → 인덱스 구문을 넣어서 특정 컬럼값을 가져오면 된다.

row=cursor.fetchone()

 

그런데 이렇게 실행하고 또 fetchone을 실행하면 다시 맨 처음 행을 가져오는 것이 아니라 두 번째 행을 가져오게 된다. 그래서 10번 실행하면 10번쨰 행을 가져오게 된다.

 

• fetchmany(size): size는 가져올 행의 수를 지정한다. 결과는 튜플로 반환.

반복문을 사용하여 fetchall을 구현함

cursor.execute("SELECT * FROM MyTable")

row=cursor.fetchmany(5)    → 5행만 가져온다.

while rows: print(rows) row=cursor.fetchmany(5) 

fetchall 실행

fetchone()실행

fetchmany()실행

 

🐩 pymysql로 데이터  수정

 

이처럼 데이터가 변경된 모습을 확인할 수있다.

 

🐩 pymysql로 데이터  삭제

데이터를 바꾸는 것 

데이터가 삭제된 모습을 확인할 수 있다.