banana like sunshine RSS FEED

오늘은 SQL query문 중 기초적인 부분에 대해서 써볼까 합니다. 물론 오늘 한 작업 중 한 가지 소스이지요 ㅋㅋ

DATETIME 필드를 기준으로 현재로부터 6시간 이내에 발생한 record를 불러오는 겁니다.

MYSQL에는 DATE_ADD()라는 함수가 있습니다. 이 함수를 이용하는 거지요.

SELECT * FROM foo WHERE created_at > DATE_ADD(now(), INTERVAL -6 HOUR)

지금 현재 시각 now() 에서 6시간의 INTERVAL을 빼주었으니 현재에서 6시간 이내가 되는 거지요.

HOUR 대신에 MINUTE, SECOND, DAY 등으로 할 수도 있으니 (쓰다 보니 DAY를 이용한 거는 예전에 포스팅을 했던 것 같기도..) 응용해서 쓰시면 됩니다.

binary로 설치할 때는 http://dev.mysql.com/downloads/connector/python/에서 OS별로 다운받아서 설치하면 됩니다.

제 Mac에서는 python2와 3을 동시에 사용하고 있어서.. pip으로 설치하는 방법을 찾았습니다:

pip3.4 install --allow-external mysql-connector-python mysql-connector-python

3rd party pip repo라 옵션이 붙어야 하는 것 같습니다.

Ubuntu에서 mysql-python 설치하는 방법입니다. 아무것도 깔려있지 않은 깨끗한(?) 서버 상태일 때를 가정한 것입니다.

sudo apt-get install python-pip
sudo apt-get install python-dev
sudo apt-get install libmysqlclient-dev
sudo pip install MySQL-python

pip이 설치되어 있다면 1행은 건너 뛰셔도 됩니다.

오늘은 wamp 서버 설치를 해보도록 하겠습니다.

wamp는 윈도우즈 환경에서 Apache + mysql + php를 셋으로 구성해서 간단하게 개발환경을 구축해주고, 서비스 할 수 있도록 도와주는 kit입니다. 물론 이 세 가지는 설치도 그다지 어렵지 않아서 따로따로 설치해도 잘되지만, 저처럼 윈도우즈 별로 안좋아하는 사람도 쉽게 설치 및 관리를 할 수 있도록 해줍니다.

1. 설치파일 다운받기

먼저 wamp server 페이지(http://www.wampserver.com/en/)로 가서 설치파일을 다운받습니다.

OS가 32비트인지 64비트인지 확인해서 받으셔야 됩니다. 클릭하면 sourceforge에서 설치파일을 다운로드 받습니다.

2. setup 파일 실행

셋업과정은 단순합니다. 설치 경로와 기본 브라우져, 그리고 SMTP에 이용할 메일 주소만 확인해주면 알아서 잘 설치가 되는군요.

3. 설치 완료

어떻게 생각해보면 허무하게 세팅이 끝나버렸습니다. ㅋㅋ

브라우져에서 http://localhost 해서 아래 화면이 나오면 정상적으로 설치가 완료된 것입니다. 제 컴에서는 트레이에 아이콘은 server offline으로 표시되어서 문제가 있나 했는데, php나 mysql 모두 정상적으로 잘 작동을 하는 걸 보면 제 컴의 문제이거나 트레이의 정보와는 상관이 없거나 한 것 같습니다.

4. 설정하기

아파치 설정은 bin/apache 밑의 conf 디렉토리에 있습니다. 입맛대로 설정하시면 되고요..

mysql은 그래도 root 패스워드 설정은 해놔야 쓰겠지요? 위 3의 화면에서 phpmyadmin으로 가서 사용해도 되겠지만, console에서 이용하려면 PATH에 다음을 추가합니다 :

C:\wamp\bin\mysql\mysql5.6.17\bin

그 다음에는 root 패스워드 설정을 해줍니다.

자.. 이제 끝났습니다. 사실 워드프레스 한번도 안써보았는데 아는 분이 부탁을 하셔서 한번 깔아보고 하느라 익숙하지 않은 윈도우즈에 설치를 해 보았네요. 가능하면 다음에는 wordpress 설치를 주제로 다시 포스팅 해보도록 하겠습니다.

혹시 wamp 설치하면서 안되거나, 궁금하신 점이 있으시면 답글 달아주세요.

Update (11/26/2014)

저는 mysql을 console에서 많이 이용하는 것도 있고 해서 위와 같이 root 패스워드 설정하고 이것저것 하는데.. 이렇게 하면 phpmyadmin 접속이 안됩니다.

확인해보니 wamp server와 같이 배포되는 phpmyadmin은 local 설정이 되어 있어서 local에서만 접속이 됩니다. phpmyadmin 이용이 더 익숙하신 분은 (어차피 wamp 자체가 서비스용이라기 보다는 간단하게 개발환경을 만들어 주는 용도이니) 굳이 설정 변경을 안하고 그냥 이용하시는게 더 낫지 않을까 싶네요. ^^

단순한 팁인데, SQL문에 날짜 기준을 삼기 위해서 PHP 등에서 날짜를 계산해서 전달하는 것보다는 SQL 자체에서 계산해주는 것이 로직 면에서 더 보기 좋다고 봅니다(개인적으로). 

어제 날짜를 SQL문에 넣으려면 다음과 같이 해줍니다:

select current_date() - interval 1 day;

아니면 

SELECT DATE_ADD(CURDATE(), INTERVAL -1 DAY);

참고로, current_date()와 CURDATE()는 같은 함수입니다.

MYSQL 들었다 놨다(?) 할때 쓰는 명령입니다.아마존 aws에서는 mysql이 서비스로 돌아가기 때문에 아래와 같이 해줍니다 :

sudo service mysqld restart

어제 python 관련 글 올린 김에 많은 분들이 찾으시는 MySQL 데이터베이스 이용과 관련된 내용을 정리해드립니다.

예전에 제가 프로그래밍으로 밥 벌어먹기 시작했을 때(!)에는 여러가지 DBMS가 많았습니다만, 이제는 LAMP니 뭐니 할 정도로 MySQL이 정석이 되었습니다. 실제로 제가 있었던 회사는 MySQL로 회원 수 천만이 넘는 사이트를 무리 없이 운영하니까요. 옛날에는 MySQL로 그 정도 규모의 상업적인 서비스를 한다 그러면 주위에서는 그거로 돌리면 불안해서 밤에 잠이 오겠냐, M$나 오라클 써라 했었는데요. ㅋㅋ

다 오픈소스의 힘이라고 생각합니다. 그만큼 보완이 많이 되고, 서비스를 하면서 경험이 축적되면서 개선이 되니까요. 개인적으로는 MongoDB로 개발을 하고 있는 부분이 있는데 Mongo가 더 재미있긴 하지만요.

각설하고, python에서 MySQL 이용하는 것과 관련해서 간단하게 정리해 보겠습니다.

많이 이용되고 있는 모듈은 

_mysql, MySQLdb

con = MySQLdb.connect('localhost', 'testuser', 'mypassword', 'mydbname')

이런 식으로 해주시면 되고, 연결 후에는 cursor를 생성하여 이용하시면 됩니다.

간단하게 예를 들어보면 아래와 같습니다:

#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
import sys

try:
    con = mdb.connect('localhost', 'dbuser', 'mypassword', 'mydb')
    
    cur = con.cursor()
    sql = "SELECT ID FROM articles ORDER BY ID DESC LIMIT 10"
    cur.execute(sql)
    for i in range(cur.rowcount):
        row = cur.fetchone()
        print row[0]

except mdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit(1)

python의 dictionary type cursor를 이용하시려면 cursor 생성시에 다음과 같이 하시면 됩니다.

cur = con.cursor(mdb.cursors.DictCursor)

(Update) MySQLdb module 설치는 다음 포스팅을 참조하세요:

http://banasun.tistory.com/entry/MySQLpython-%EC%84%A4%EC%B9%98%ED%95%98%EA%B8%B0-Ubuntu

보통 페이지 나누어서 결과를 가져오는 경우

SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20

이렇게 쓰게 된다. 그러나 이 경우,10000개의 row는 그냥 버리고 그 뒤의 20개만 가져오는 문제가 생기기 때문에 퍼포먼스라는 측면에서는 고민이 될 수밖에 없다.

결국, OFFSET이 커지면 커질수록 필요없는 데이터가 메모리에 쌓였다 버려진다. 동접이 얼마 안되면야 서버가 일하는 거니 나랑 상관없다지만, 동접이 얼마 이상 된다면 병목현상이 발생할 수밖에 없고, 개발자는 회사에서 까이는 현상이 발생한다;;

해결책 - 

1. 뒷 페이지까지 이동하지 않도록 한다 (초간단 해결책!!) : 처음에 5~10페이지만 보여주고, 그 이전 데이터는 검색을 해서 찾아가는 UI를 만들자. 
2. 큰 OFFSET이 필요한 경우 WHERE절을 이용해서 어디부터 시작할지를 정해준다. (예: 직전 페이지 id를 남겨놓았다가 WHERE id > 324790 LIMIT 20 이런 식으로..)

간만에  php로 작업하다 보니 한글이 깨지네요. ㅡㅡ;;

제 서버도 아니라 설정을 바꾸고 어쩌고 할 수도 없고, PHP 버전이 낮아도 새로 깔수도 없고 해서 1시간 정도 헤매다 성공!!

이놈의 한글 문제는 십년 전이나 지금이나 변함이 없다는 게 참.. 미국서 태어났어야 하는 건가;;

PHP version 5.3.6 이전)

<?php

// ...

$dbh = new PDO($dsn, $user, $pw);

$dbh->exec("set names utf8");

// ...

?>

Stored procedure?

쉽게 설명하자면, stored procedure는 function의 역할을 하는 SQL code의 묶음입니다. 

굳이 이걸 사용해서 얻는 장점이라면.. 

application의 퍼포먼스를 높일 수 있다 - 컴파일이 되어 저장되기 때문에 일반 SQL 명령보다 빠름. 

application과 DB server 사이의 트래픽을 줄일 수 있다

정도일 것 같습니다. 물론 이것보다 많은 장점이 있겠지만 당장 피부에 와닿는 거는 이 정도구요, 싫어하시는 분들도 많습니다. 의외로 stored procedure를 사용하는 개발자는 적습니다. 

그러면, 이제 작성 방법에 대해서 적어보겠습니다.

1. DELIMITER //

mysql에서 기본으로 사용하는 delimiter(;)를 바꿔줍니다. 

끝난 후에는 다시 DELIMITER ; 로 되돌려 놓도록 합니다. 

2. BEGIN ~ END

프로시저의 구성은 BEGIN 부터 END 까지입니다. 自 BEGIN 至 END 라고나 할까.. 죄송합니다;;

여기까지 내용으로 뼈대를 만들어 보면 다음과 같습니다:

3. variable 설정

변수 선언은 DECLARE, 값 지정은 SET을 사용합니다. 

DECLARE variable_name datatype(size) DEFAULT default_value;

ex)

DECLARE x, y INT DEFAULT 0

SET x = 10;

혹은 SELECT COUNT(*) INTO x FROM products 이렇게도 사용합니다. 

variables scope는 당연히 적용이 되고.. 단, 변수 앞에 '@'를 넣으면 session 변수가 됩니다.