Mac OS X Python 3 MySQL 연동

Mac OS X Python 3 MySQL 연동

Mac OS X MySQL Install & Set-up

HomeBrew를 이용해 MySQL을 설치합니다.

$ brew install mysql

MySQL 시작

$ mysql.server start

MySQL 설정

$ mysql_secure_installation

Would you like to setup VALIDATE PASSWORD plugin? : 비밀번호 가이드. 복잡한 비밀번호를 사용하도록 제한해주는 플러그인을 사용하려면 yes, 보안은 무시하고 그냥 쓰던 비밀번호 제한받지 않고 쓰고 싶다면 no.

Remove anonymous users? : 익명사용자 삭제할지/계속 사용할지 여부. no 하면 $ mysql -uroot가 아니라 $ mysql만으로도 접속 가능. yes하면 -u 옵션 필수.

Disallow root login remotely? : localhost외에 다른 ip에서 root 아이디로 원격접속 가능하게 할지. yes하면 원겹접속 불가.

Remove test database and access to it? : mysql에 기본적으로 설정된 test 디비 삭제 여부.

Reload privilege tables now? : 하나라도 권한 변경을 했다면 yes 해서 하는 게 정신건강에 좋을 것입니다.

MySQL 로그인

$ msyql -u root -p

charset 설정

> status;

MySQL 데몬 실행

$ brew services start mysql

Python 2.7 using MySQL-python

MySQL-python install

MySQL-python을 설치하기 이전에 xcode developer tools를 설치해야 합니다.

$ xcode-select --install

명령어 라인 개발자 도구가 없다면 설치해야 합니다.

$ pip install MySQL-python

설치 도중에 이런 오류가 났다면

$ sudo chown -R kimjungchul /Library/Python/2.7

$ pip install MySQL-python

Python 2 or 3 PyMySQL

Python에서 MySQL 데이터베이스를 사용하기 위해 우선 Python DB API 표준을 따르는 MySQL DB 모듈을 다운받아 설치한다.

$ pip install PyMySQL

$ pip3 install PyMySQL

MySQL Database 보기와 생성

데이터베이스 보기

mysql> show databases;

데이터베이스 생성

mysql > create database capstone;

mysql > use capstone;

테이블 생성

msyql > CREATE TABLE url_list (

id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  url varchar(255) NOT NULL,

PRIMARY KEY(id)

);

mysql > DESC url_list

Python Code

1. PyMySql 모듈 import

2. pymysql.connect() 메소드를 사용하여 MySQL에 연결한다. 호스트명, 로그인, 암호, 데이터베이스명 등을 Parameter로 지정한다.

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

4. Cursor 객체의 execute() 메서드를 사용하여 INSERT, UPDATE 혹은 DELETE 문장을 DB 서버에 전송한다.

5. 삽입, 갱신, 삭제 등이 모두 끝났으면 Connection 객체의 commit() 메서드를 사용하여 데이터를 Commit.
   디폴트로 Autocommit 이 아니므로 commit()을 명시적으로 호출.
   자동커밋을 원하는 경우, connect() 메서드 호출시 autocommit=True 를 지정.

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

#-*- coding: utf-8 -*-

import pymysql as mysql

# MySQL DB 연결을 한다

db = mysql.connect(host='localhost', user='root', password='비밀번호', db='데이터베이스명', charset='utf8')

# Connection 으로부터 Cursor 생성

curs = db.cursor()

# SQL문 실행

sql = "SELECT * FROM url_list"

curs.execute(sql)

# 데이터 Fetch

rows = curs.fetchall()

print(rows)

db.close()

Insert 문

테이블에 데이터 추가 INSERT문을 사용한다. INSERT문에 사용하는 각 컬럼 값들을 직접 지정하거나 Placeholder를 사용할 수 있다.

데이터 INSERT만 실행하고 Commit하지 않으면, 테이블의 데이터는 변경되지 않는다는 점이다.

execute() 메서드는 하나의 row를 실행하는 반면, executemany() 메서드는 tuple 데이터를 DML 문으로 적용한다.

$ mysql -u root -p

msyql > create database test;

mysql > use test;

mysql > CREATE TABLE table_test (

id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

name varchar(255) NOT NULL,

region varchar(255) NOT NULL,

PRIMARY KEY(id)

);

Python Code

#-*- coding: utf-8 -*-

import pymysql as mysql

# MySQL DB 연결을 한다

db = mysql.connect(host='localhost', user='root', password='비밀번호', db='test', charset='utf8')

# Connection 으로부터 Cursor 생성

curs = db.cursor()

# SQL문 실행

sql = "INSERT INTO table_test(name, region) VALUES (%s,%s)"

curs.execute(sql, ('Kim JeongChul','Seoul'))

curs.execute(sql, ('Kim JeongJae','Suwon'))

data = (

  ('Lee Dong Woo','Seoul'),

  ('S3','Seoul'),

)

sql = "INSERT INTO table_test(name, region) VALUES (%s,%s)"

curs.executemany(sql,data)

db.commit()

db.close()

$ python3 MySQL-Insert.py

mysql> SELECT * FROM table_test;

UPDATE, DELETE 문

테이블의 데이터를 수정하기 위해 UPDATE를 사용하고, 삭제 시에는 DELETE 사용.

#-*- coding: utf-8 -*-

import pymysql as mysql

# MySQL DB 연결을 한다

db = mysql.connect(host='localhost', user='root', password='kimjc8978', db='test', charset='utf8')

# Connection 으로부터 Cursor 생성

curs = db.cursor()

# SQL문 실행

sql = "UPDATE table_test SET name = 'Seon Sung Soo' where name='S3'"

curs.execute(sql)

sql = "DELETE FROM table_test WHERE id =%s"

curs.execute(sql, 2)

db.commit()

db.close()

$ python3 MySQL-UpdateDelete.py

> SELECT * FROM table_test;

try, with 문 사용

SQL Connection을 열고 프로그램이 강제 종료되면, 열려 있는 상태로 Connection Leak이 발생하게 된다.

#-*- coding: utf-8 -*-

import pymysql as mysql

# MySQL DB 연결을 한다

db = mysql.connect(host='localhost', user='root', password='kimjc8978', db='test', charset='utf8')

# Connection 으로부터 Cursor 생성

curs = db.cursor()

try:

  # INSERT

  with db.cursor() as curs:

      sql = "INSERT INTO table_test(name, region) VALUES (%s, %s)"

      curs.execute(sql,('Kim JeongJae','Suwon'))

  db.commit()

  # SELECT

  with db.cursor() as curs:

      sql = "SELECT * FROM table_test"

      curs.execute(sql)

      rows = curs.fetchall()

      for row in rows:

          print(row)

         

finally:

  db.close()

$ python3 MySQL-TryWith.py