SQL (Python with MySQL)

윤·2023년 4월 29일

SQL

목록 보기

6/11

📌 Python with MySQL

📒 `Connect()`

파이썬에서 MySQL 을 사용하기 위해서는 MySQL Driver 를 설치해야 한다.

pip install mysql-connector-python

💡 맥 유저는 vscode 터미널에서 바로 설치해도 된다. 그러나 윈도우 유저는 설치는 정상적으로 되지만 import mysql.connector 해줬더니 No module named 'mysql' 오류가 발생했다. 그래서 아나콘다 프롬프트에서 다시 설치해주니 import mysql.connector 가 정상적으로 실행되었다.

MySQL 에 접속하기 위한 코드

Local Database 연결

AWS RDS(database-1) 연결

특정 Database 에 접속하기 위한 코드

Local MySQL 의 mydb 에 연결

AWS RDS(database-1) 의 mydb에 연결

✍ close 해주는 거 잊지 말기 !

📒 `Execute()`

쿼리를 실행하기 위한 코드

local = mysql.connector.connect(
	host = 'host_name',
    user = 'user_name',
    password = 'password',
    database = 'database_name'
)

mycursor = local.cursor()
mycursor.execute('query');

cursor 는 데이터베이스와 상호작용하기 위한 객체이다. cursor 를 사용하여 쿼리를 실행하고, 결과를 받아올 수 있다.

✍
만약, mysql.connector.connect() 에서 database를 지정해주지 않았다면 !
local.cmd_query('use database_name') 을 해주고 mycursor = local.cursor() 을 해줘야 한다.
앞서 배웠던 내용을 보면, 특정 데이터베이스로 이동을 한 후 쿼리를 실행해야 하기 때문이다 !

테이블 생성

테이블 삭제

SQL File 을 실행하기 위한 코드

local = mysql.connector.connect(
	host = 'host_name',
    user = 'user_name',
    password = 'password',
    database = 'database_name'
)

mycursor = local.cursor()
sql = open('filename.sql').read()
mycursor.execute(sql)

작업할 때, 파일이 같은 경로에 있어서 open('test03.sql') 이렇게 썼지만 그게 아니라면 경로를 지정해줘야 한다. 예를 들어, ./ (현재 디렉토리), .. (상위 디렉토리) 또는 절대경로를 지정해줘야 한다.

SQL File 내에 Query 가 여러개 존재하는 경우

기존 코드로 실행하면 오류가 발생한다. 오류가 발생하지 않으려면 multi=True 옵션을 추가해줘야 한다.

📒 `Fetchall()`

mycursor.execute('query')

result = mycursor.fetchall()
for data in result:
	print(data)

그냥 실행하는 쿼리가 아니라 조회하는 select문을 실행한 경우에는 데이터를 가지고 오는데, 그 데이터를 fetchall() 을 써서 result 변수에 담을 수 있다. for문을 이용해서 가져오면 데이터가 row마다 찍히게 되고, for문을 쓰지 않고 바로 출력하면 한번에 찍힌다.

=> 읽어올 양이 많은 경우 buffered=True 설정

=> for문을 사용하지 않고 바로 출력하면 다음과 같이 한번에 찍힌다.

[(1, 'test01.sql'), (2, 'test02.sql'), (3, 'test03.sql'), (4, 'test04.sql')]

📌 Python with CSV

큰 데이터들이 excel, csv 파일에 들어있는 경우가 있다. 이 데이터들을 DB에 넣고 쿼리를 해보고 싶을 때 한꺼번에 넣는 방법이 있을까? MySQL 에서는 그 방법을 제공한다 ! 그런데 인코딩이 맞지 않는 경우에는 MySQL 이 제공하는 인터페이스를 그대로 사용하면 fail이 많이 뜬다. 특히, 우리나라 사이트에 다운받은 파일들은 utf-8 이 아닌 파일들이 많아서 더욱 그렇다.
하지만, 파이썬으로 하면 간단하게 인코딩해서 DB에 넣고 쿼리를 할 수가 있다 !

CSV 에 있는 데이터를 파이썬으로 INSERT

1) Read CSV : csv파일을 Pandas 로 읽어와서 데이터를 확인한다.

2) mydb 데이터베이스에 연결

3) Cursor 생성 : 읽어올 양이 많은 경우, cursor 생성 시 `buffered=True` 설정

4) Insert문 생성 : 테이블에 데이터 삽입하기

5) 데이터 입력 : commit() 으로 database 에 적용하기

tuple(row)를 sql에 대입시키고, cursor.execute 해준다. => 데이터가 들어간 쿼리가 한 줄씩 완성이 되어 실행이 된다.

✍ cursor.execute 만으로 데이터에 바로 적용이 되지 않고, commit 하는 순간 적용된다.

for문 안에 commit이 들어가있으면, sql문이 실행될 때마다 바로바로 변경 내용이 데이터베이스에 저장되기 때문에 중간에 오류가 발생하더라도 이전에 성공적으로 실행한 sql문들은 이미 DB에 저장되어 있다. 하지만, for문 밖에서 commit을 호출하면, 모든 sql문이 실행된 이후에 한번에 commit 이 실행되기 때문에 중간에 오류가 발생하면 이전에 성공적으로 실행한 sql문들도 전부 롤백된다.

✍
4), 5) 를 좀 더 잘 이해하기 위해서 MySQL 를 들어가보면 다음과 같이 예시가 나온다. 이걸 보니까 더 잘 이해가 된다 !!! 더 알고 싶다면 사이트 참고하면 된다.