python 에서 MySQL 사용 하기(Linux - Ubuntu)

1. MySQL 설치

$ sudo apt-get update

$ sudo apt-get install mysql-server

 

2. MySQL  root계정의 패스워드 설정

$ sudo mysql

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new password';
mysql> exit

sudo mysql을 입력하면 로그인 없이 바로 mysql에 들어가게 되는데, 이것은 인증을 해주는 플러그 인을 이용해서 그렇다고 한다.

그래서 mysql에 로그인후 root의 패스워드('new password')를 설정해주고 mysql을 빠져 나온다.

이제 sudo mysql 로 mysql 에 들어 가려 하면 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

위와 같은 메시지가 뜨며 접속이 거부되었다고 나오므로 다음 명령으로 패스워드를 입력하고 들어갈수 있다.

$ sudo mysql -u root -p

 

3. mysqlclient 

: python에서 mysql에 연결할때 필요한 패키지

$ sudo apt-get install python3-dev

$ sudo apt-get install python3-setuptools

$ sudo apt-get install libmysqlclient-dev

$ pip install mysqlclient( 또는 pip3 install mysqlclient)

mysqlclient를 설치하는것은 쉽지 않았다. 몇번을 지우고 깔는것을 했는지...

자꾸만 오류가 뜨고 여기 저기를 돌아다니며 겨우 방법을 찾았는데, 이것 역시 얼마후에는 방법이 바뀔수도 있을것 같다.

또한 설치 할때 주의할 점은 당연하겠지만 자신의 기본 pip와pip3  그리고 python과 python3가 각각 어떻게 설정 되어있는지 잘 알고 있어야 겠다.

내경우에는 터미널에는 "python"라고 입력 하면 python3.6이 그리고 "python3.7"이라고 입력 하면 python3.7이  실행된다.또 "pip"로 설치하면 python2에 "pip3"로 설치하면 python3.6에 설치 되기 때문에 "pip" 명령으로는 설치후 "python"으로 실행하면 , 아무리 설치해도 실행코드에서는 모듈을 찾을 수 없다고 나오게 된다.결국 "pip3"로 설치하고 하고 "python"라고 실행해서 해결 했다. (좀 정리가 필요한 듯...)

다시 말해 pip install mysqlclient를 입력 해야 하는지 pip3 install mysqlclient를  입력해야하는지 잘 구분해주어야 한다.  참고로 다음과 명령을 실행하면 자신의 python과 pip버전을 확인할 수 있다.

$ python -V
$ python3 -V

$ pip -V
$ pip3 -V

 

모든 설치가 완료 되었으면  다음 코드를 실행 해보고 결과가 잘 나오면 성공.

import MySQLdb

conn= MySQLdb.connect(
    user='root',
    passwd='new password',
    host='localhost',
    db='mysql' 
)

cur=conn.cursor()

cur.execute('select "haha",3')
for row in cur.fetchall():
   print(row) 

 

혹시 파이썬 결과에 

MySQLdb._exceptions.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")

와 같은 메시지가 나오면 Mysql의 user/password 와 소스의 user/password 가 달라 접속이 안되는것이니 확인 바람.

 

4. mysql내에서 한글 처리  문제

python과 mysql 의 연결은 성공을 하였지만,

mysql> create database test_book;

mysql> use test_book;

mysql> create table Book_Main(code varchar(20),title varchar(30),author varchar(40),isbn varchar(13));

mysql> insert into Book_Main(code,title,author,isbn) values(1,"aa","aaa","123456789012");

mysql> insert into Book_Main(code,title,author,isbn) values(2,"bb","가가","123456789012");

DB와 table을 만들고 입력을 하면 첫번째 입력은 잘 되지만, 한글을 포함한 입력은

ERROR 1366 (HY000): Incorrect string value: '\xEA\xB0\x80\xEA\xB0\x80...' for column 'author' at row 1

라는 에러가 나타난다.

이것은 한글을 이용하려면 DB characterset을 utf8로 해주어야 하는데,

characterset을 확인 하려면, mysql에 로그인 한후 다음과 같이 입력하여 확인 할 수 있다.

mysql> status;

 

 

* 해결방법 1* DATABASE를 만들때 characterset 설정 하기

mysql> CREATE DATABASE test_book CHARACTER SET utf8;

 

* 해결방법 2* 만들어진 DATABASE의 characterset 수정하기

mysql> ALTER DATABASE test_book CHARACTER SET utf8 COLLATE utf8_general_ci;

mysql> ALTER TABLE Book_Main CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

이렇게 하면 한글 입력도 가능해진다.

 

5. python으로 연결하여 한글 처리 문제

하아~ 또 문제가 있다.

characterset을 바꾸고 입력과 조회가 mysql안에서는 위와 같이 잘 되지만, 파이썬에서 연결 후  검색하면...

한글이 물음표로 깨져 나온다.

('1', 'aba', 'aaa', '123456789012')
('1', 'aba', 'a??aa', '123456789012')

또한 다음 코드를 실행 해보면

import MySQLdb

conn= MySQLdb.connect(
    user='root',
    passwd='rootpswd',
    host='localhost',
    db='test_book',
)


cur=conn.cursor()

cur.execute('insert into Book_Main(code,title,author,isbn) values(3,"cc","ccc","123456789012");')
cur.execute('insert into Book_Main(code,title,author,isbn) values(4,"dd","다다다","123456789012");')
cur.execute('select * from Book_Main;')
for row in cur.fetchall():
   print(row) 

3번 자료는 입력이 잘 되지만

4번자료는 다음과 같이 에러나 난다.

UnicodeEncodeError: 'charmap' codec can't encode characters in position 61-63: character maps to <undefined>

 

* 해결방법 1 * MySQL Connector 사용

MySQL Connector는 MysQL 개발사인 오라클에서 제공하는 드라이버로 2번에서 설치한 mysqlclient 대신 이 드라이버를 사용하여 연결하면 한글을 주고 받을 수 있게 된다.

$ pip install mysql-connector-python

로 설치 후 파이썬 코드의 import 와 connect 부분을 다음과 같이 바꿔준다.

import mysql.connector

conn= mysql.connector.connect(
    user='root',
    passwd='rootpswd',
    host='localhost',
    db='test_book',
)


cur=conn.cursor()

cur.execute('insert into Book_Main(code,title,author,isbn) values(3,"cc","ccc","123456789012");')
cur.execute('insert into Book_Main(code,title,author,isbn) values(4,"dd","다다다","123456789012");')
cur.execute('select * from Book_Main;')
for row in cur.fetchall():
   print(row) 

하지만 원래 mysqlclient를 사용한 이유가 MySQL Connector보다 속도가 빠르기도 하고 djanog에서도 이 패키지를 추천하기 때문에 그대로 mysqlclient를 사용하는 방법은 조금더 찾아 봐야 겠다.

다행인것은 python의 모듈에 따라 값을 갖고 오기도 하고 못 갖고 오기도 한다는 것은 MySQL쪽 문제보다 파이썬쪽에서 해결이 가능한것 아닌가하는 실마리는 찾은것이다...^^

 

* 해결방법 2 * mysqlclient 사용

 

결국 연결 코드에 charset을 추가 하는것으로 모든것이 해결되었다.  후우~

import MySQLdb

conn= MySQLdb.connect(
    user='root',
    passwd='rootpswd',
    host='localhost',
    db='test_book',
    charset='utf8', #이부분을 추가
)


cur=conn.cursor()

cur.execute('insert into Book_Main(code,title,author,isbn) values(3,"cc","ccc","123456789012");')
cur.execute('insert into Book_Main(code,title,author,isbn) values(4,"dd","다다다","123456789012");')
cur.execute('select * from Book_Main;')
for row in cur.fetchall():
   print(row) 

 

 

참고사이트 )

1) Django의 mysqlclient 추천

Databases | Django documentation | Django

 

2) "mysqlclient" 와 "MySQL connector/Python"의 속도비교

What's the difference between MySQLdb, mysqlclient and MySQL connector/Python?

 

3) mysqlclient(MySQLdb 설명)

MySQLdb User’s Guide — MySQLdb 1.2.4b4 documentation