파이썬에서 MySQL 연동 및 사용법은?!

2021. 8. 29. 09:12

파이썬에서 MySQL DB를 연결하고 사용하려면, 파이썬 DB커넥터를 설치해야 한다. 이전 포스팅에서 MySQL을 설치하고 실행해 보았다. 오늘은 파이썬에서 MySQL 연동 및 사용법에 대해서 알아보도록 하겠다.

 

 

파이썬 MySQL 연동

 

 

테스트를 위해 계정을 하나 만들었다. MySQL Workbench에서 전에 만든 temp라는 DB를 사용할 수 있는 계정을 아래와 같이 하나 만들었다. terminal에서 mysql을 실행해서 만들 수도 있다.

create user 'test' identified by 'test_pw';
grant all on temp.* to 'test'

 

사용자를 만들 때는 앞에가 id고, 뒤에가 비밀번호다. id를 만들 때 뒤에 @localhost나 @%를 붙일 수 있다.(test@localhost)
localhost는 로컬에서만 접속이 가능하고, %를 붙이면 외부접속이 가능하다고 한다. 아무것도 안 붙이니 외부접속이이 가능한 계정으로 만들어졌다. 

 

 

파이썬 DB커넥터 설치하기

파이썬에서 MySQL DB를 사용하기 위헤서는 MySQL커넥터를 설치해야 한다. pip install mysql명령어를 호기롭게 날렸는데, 문제가 있는지 설치가 되지 않는다.

xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools), missing xcrun at: /Library/Developer/CommandLineTools/usr/bin/xcrun

    error: command 'gcc' failed with exit status 1

    ----------------------------------------

ERROR: Command errored out with exit status 1: /Users/open/anaconda3/bin/python -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/private/var/folders/8w/0rhtnz452rxb_j0hc0hqvt940000gn/T/pip-install-zpj3sa9i/mysqlclient/setup.py'"'"'; __file__='"'"'/private/var/folders/8w/0rhtnz452rxb_j0hc0hqvt940000gn/T/pip-install-zpj3sa9i/mysqlclient/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' install --record /private/var/folders/8w/0rhtnz452rxb_j0hc0hqvt940000gn/T/pip-record-wbud3z2l/install-record.txt --single-version-externally-managed --compile Check the logs for full command output.

 

 

MySQL DB커넥터는 MySQL 공식 홈페이지에서도 다운로드 받아 설치가 가능하다. MySQL 커넥터는 아래 주소에서 다운로드 받을 수 있다.

( MySQL 커넥터 다운로드: https://dev.mysql.com/downloads/connector/python )


여기서 운영체제를 Platform independent를 선택하고, zip으로 된 파일을 다운로드 받는다. 파일을 다운로드 받은 후에, 압축을 풀고 해당 폴더에서 아래 명령어를 실행하니 정상적으로 설치가 됐다.

> python setup.py install

 

이제 python에서 DB에 연결해보자

mysql.connector를 이용해서 쿼리를 날리는 것은 sqlite3를 사용할 때랑 비슷하다. cursor를 만들고, fetch함수를 이용해서 쿼리를 실행하고 결과를 받을 수 있다.

import mysql.connector

dbcon = { 'host': '127.0.0.1',
          'user': 'test',
          'password' : 'test',
          'database': 'temp'}

conn = mysql.connector.connect(**dbcon)
cursor = conn.cursor()
sql = "show tables"
cursor.execute(sql)
res = cursor.fetchall()
con.close()

 

 

내친 김에 pandas 데이터프레임의 to_sql함수도 이용해 봤는데 문제가 생겼다. 에러가 생긴다.

import pandas as pd

test1 = pd.DataFrame({"a":[1,2,3,4]})
test1.to_sql('test1', conn, if_exists="append", index=False)

 

sqlite3를 이용할 때랑 동일하게 했는데 무슨 파리미터가 부족한가 보다.

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Not all parameters were used in the SQL statement

 

여기저기 찾아보니, mysql.connector로 붙을 때는 to_sql함수를 사용할 수 없는 듯 하다. pymysql과 sqlalchemy를 이용해야 한다.

 

 

pymysql과 sqlalchemy 이용하기

sqlalchemy는 이미 설치되어 있고 pymysql을 추가로 설치하였다.

> pip install pymysql

 

설치하고 나면 아래와 같이 입력해서, to_sql함수를 이용할 수 있었다.

import pandas as pd
from sqlalchemy import create_engine

pymysql.install_as_MySQLdb()

engine = create_engine("mysql://test:test@localhost/temp")

con = engine.connect()
test1 = pd.DataFrame({"a":[1,2,3,4]})
test1.to_sql('test1', con, if_exists="append", index=False)
con.close()

 

 

이렇게 하고 나니, mysql 공식 드라이버보다 sqlalchemy와 pymysql을 더 자주 이용할 것 같다는 생각이 든다. 판다스 데이터프레임을 자주 이용하기 때문이다. 

 

MYSQL의 쿼리 문법이나 자세한 설정에 대한 내용이 궁금하다면, 아래 공식 홈페이지 주소를 참조하도록 하자.

( 참조: https://dev.mysql.com/doc/refman/8.0/en/connecting-disconnecting.html )

 

 

오늘은 이렇게 파이썬에서 MySQL DB를 연결하고 사용하는 방법에 대해서 알아보았다. MySQL커넥터를 이용하여 DB에 접속하고 사용할 수 있었지만, 판다스 데이터프레임의 to_sql함수를 이용할 수 없었다. sqlalcemy와 pymysql을 이용하면, 동일하게 사용이 가능하니 참고하도록 하자.

댓글()