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

파이썬에서 MySQL DB에 접속하면, 파이썬 코드를 이용해서 쿼리를 실행할 수 있습니다. 파이썬에서 MySQL DB를 연결하기 위해서는 파이썬 DB커넥터를 설치해야 합니다. 이전 포스팅에서 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 MySQL 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함수를 사용할 수 없다고 합니다. 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 DB를 연결하고 사용하는 방법에 대해서 알아보았습니다. MySQL커넥터를 이용하여 DB에 접속하고 사용할 수 있었지만, 판다스 데이터 프레임의 to_sql함수를 이용할 수 없었습니다. sqlalcemy와 pymysql을 이용하면, 동일하게 사용이 가능하니 참고하도록 하세요.