�yFirebird�z�X�֔ԍ���DB�̍쐬 ./dl_ZipCodeJp.py (original) (raw)

�@�u�yPython��CGI�z���[�U���g�ɂ�郆�[�U�o�^��ʂ��l���Ď����v�Ŗ{�l���m�F�̈׋L�^�\��̎s�撬���R�[�h���̎擾�ɂ́A�X�֔ԍ��̓��͂œ��{�X�ւ̗X�֔ԍ���f�[�^��������̂��œK���Ǝv���B�����Ŗ{���̂���Ƃ��ẮA���̃f�[�^���茳�̃f�[�^�x�[�X�Ɏ捞�ނ��Ƃɂ���B

�@�ڍׂ́A����Ɍf�ڂ���\�[�X�R�[�h�������Ă��炦�Δ���Ղ��Ǝv�����A�J�����_�ł̊‹��y�ѐ���͎��̒ʂ肾�B��{�I�ɕ��ʂ�CGI�v���O�����`���Ȃ̂ŁA�����‹����قȂ��Ă��A�͂��Ȕ������œ��삷�锤���B

1. OS : Linux=CentOS5.4 (Windows�ւ̈ڐA�͗e��)
2. ���� : Python2.6 (2.5�ȍ~�ł���Α������̂܂܉B)
3. RDBMS : Firebird2.5RC2
4. HTTPD : Apache2.2 (CentOS5.4�o���h����)
5. �� : LHA (�X�֔ԍ���f�[�^��.LZH�`���Byum����Install��)
6. charset: UTF8 (�X�֔ԍ���f�[�^��Shift_JIS)

�@�Z�p�I�ȃ|�C���g�͊T�ˎ��̒ʂ�ł������B

1. �X�֔ԍ���f�[�^��DL: �������ɍX�V�����̂ŁAwget�̃~���[�����O�X�N���v�g��Pyhton�X�N���v�g����ďo���l�ɂ��A����𖈌��A������cron�Ŏ��s������B����Ɠ����Ɏ蓮�ł̎��s���”\�ɂ���B
2. �����ݒ�l�́AINI�t�@�C���`���̐ݒ�t�@�C���ɋL�q���A������擾����`�Ƃ���B
3. exec_sql()����Firebird�p��SQL����N�G�����s�̊֐��̐����B
4. ���p�����R�[�h�́AShift_JIS�ł��\��Ȃ����AUTF-8�ɓ���B�R�[�h�ϊ��͊�{�I��Python��Ŏ��{�B
5. DB�捞�ݎ���commit��Connection�J�‚͊J�n�ƏI���ɍs���l�ɂ��ACPU���ׂ��ߑ�ɑ��������Ȃ��l�ɂ���B
6. DB�X�V���ł��^�]�”\�ɂ���ׁA�V�K�f�[�^��}����ɋ��f�[�^���폜����`�ɂ���B

�@�\�[�X�R�[�h�͈ȉ��̒ʂ�B���A���R�Ȃ���ADB�Ƃ��̒��̃e�[�u���́A���̑��ۂ𒲂ׂĖ������ɂ͎����ō쐬����l�ɂȂ��Ă���B

    /etc/ZipCodeJp/ZipCodeJP.conf

[DATABASE]
ip = 127.0.0.1
db_file = /home/fb/DB/ZipCodeJp.fdb
user = ZIP_ADM
password = <�����̃p�X���[�h>
sql_tbl = /* ���{�X�ւ����J���Ă���X�֔ԍ��f�[�^���捞�ރe�[�u���B primary key�Ȃ� */
CREATE TABLE ZIPCODE_JP(
LPO_CODE VARCHAR(5) CHARACTER SET ASCII DEFAULT '' NOT NULL,
OLD_ZIP VARCHAR(5) CHARACTER SET ASCII DEFAULT '' NOT NULL,
ZIP_CODE VARCHAR(7) CHARACTER SET ASCII DEFAULT '' NOT NULL,
PREF_KANA VARCHAR(8) DEFAULT '' NOT NULL,
CITY_KANA VARCHAR(24) DEFAULT '' NOT NULL,
TOWN_KANA VARCHAR(64) DEFAULT '' NOT NULL,
PREF_NAME VARCHAR(4) DEFAULT '' NOT NULL,
CITY_NAME VARCHAR(12) DEFAULT '' NOT NULL,
TOWN_NAME VARCHAR(38) DEFAULT '' NOT NULL,
MULTI_ZIP VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,
PER_KOAZA VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,
HAS_CHOME VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,
MULTI_AREA VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,
CHANGE_FLAG VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,
CHANGE_CODE VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,
OLD_FLAG INTEGER);

sql_flg = UPDATE ZIPCODE_JP SET OLD_FLAG = OLD_FLAG + 1;

sql_ins = INSERT INTO ZIPCODE_JP(
LPO_CODE , OLD_ZIP , ZIP_CODE , PREF_KANA , CITY_KANA, TOWN_KANA,
PREF_NAME, CITY_NAME , TOWN_NAME , MULTI_ZIP , PER_KOAZA,
HAS_CHOME, MULTI_AREA, CHANGE_FLAG, CHANGE_CODE)VALUES(
? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? ,
? , ? , ? , ?);

sql_del = DELETE FROM ZIPCODE_JP WHERE OLD_FLAG>0;

[SELECT]
sql_qry = "SELECT LPO_CODE,OLD_ZIP,ZIP_CODE,PREF_KANA,CITY_KANA,TOWN_KANA,
PREF_NAME,CITY_NAME,TOWN_NAME,MULTI_ZIP,PER_KOAZA,
HAS_CHOME,MULTI_AREA,CHANGE_FLAG,CHANGE_CODE,OLD_FLAG
FROM ZIPCODE_JP
WHERE ZIP_CODE=?
ORDER BY OLD_FLAG DESC;
"

[SEARCH]
sql_city = "SELECT LPO_CODE,PREF_NAME,CITY_NAME,PREF_KANA,CITY_KANA
FROM ZIPCODE_JP
WHERE ZIP_CODE=?
ORDER BY OLD_FLAG DESC;
"

    ./dl_ZipCodeJp.py

#!/usr/local/bin/python2.6

-- coding: UTF-8 --

"""
���{�X�ւ��X�֔ԍ����Download���茳��ZipCodeJP���X�V
"""

author = "Mire in Japan"
version = '0.0.0'
copyright = 'Copyright (c) 2010 Mire'
license = 'GPL'
url = 'http://pythonlife.seesaa.net/article/148047991.html'

from mire.std import conf_section_items

debug = 1 # �^�p���ɂ�0�ɂ��邱��
waiting = 0 # display_err()��global�ϐ��Ƃ��ė��p: ����Loop����Wait�������e�Ղ�debug������

conf_file = '/etc/ZipCodeJp/ZipCodeJP.conf'
section = 'DATABASE'
conf = conf_section_items(conf_file, section, debug=debug)
dsn='%s:%s' % (conf['ip'], conf['db_file'])
sql_tbl = conf['sql_tbl']

�f�[�^�o�^�}������SQL��

sql_flg = conf['sql_flg']
sql_ins = conf['sql_ins']
sql_del = conf['sql_del']

�X�֔ԍ���e�[�u���\��

#sql_tbl="""

/* �X�V���ɂ�OLD_FLAG��1�𑫂�����ŐV�K�f�[�^��}�����Â����̂��폜 */

/* �X�V����OLD_FLAG���Ⴂ���̂�D�旘�p����N�G�����g���p���^�p��}�� */

CREATE TABLE ZIPCODE_JP(

LPO_CODE VARCHAR(5) CHARACTER SET ASCII DEFAULT '' NOT NULL,

OLD_ZIP VARCHAR(5) CHARACTER SET ASCII DEFAULT '' NOT NULL,

ZIP_CODE VARCHAR(7) CHARACTER SET ASCII DEFAULT '' NOT NULL,

PREF_KANA VARCHAR(24) CHARACTER SET UTF8 DEFAULT '' NOT NULL,

CITY_KANA VARCHAR(66) CHARACTER SET UTF8 DEFAULT '' NOT NULL,

TOWN_KANA VARCHAR(76) CHARACTER SET UTF8 DEFAULT '' NOT NULL,

PREF_NAME VARCHAR(12) CHARACTER SET UTF8 DEFAULT '' NOT NULL,

CITY_NAME VARCHAR(30) CHARACTER SET UTF8 DEFAULT '' NOT NULL,

TOWN_NAME VARCHAR(111) CHARACTER SET UTF8 DEFAULT '' NOT NULL,

MULTI_ZIP VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,

PER_KOAZA VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,

HAS_CHOME VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,

MULTI_AREA VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,

CHANGE_FLAG VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,

CHANGE_CODE VARCHAR(1) CHARACTER SET ASCII DEFAULT '' NOT NULL,

OLD_FLAG INTEGER DEFAULT 0 NOT NULL);

"""

�f�[�^�o�^�}������SQL��

�Â��f�[�^�̏���

#sql_del = "DELETE FROM ZIPCODE_JP WHERE OLD_FLAG>0;"

�S���ڃN�G���[

sql_sel="""
/* �X�V����OLD_FLAG���Ⴂ���̂�D�旘�p����N�G�����g���p���^�p��}�� */
SELECT LPO_CODE,OLD_ZIP,ZIP_CODE,PREF_KANA,CITY_KANA,TOWN_KANA,
PREF_NAME,CITY_NAME,TOWN_NAME,MULTI_ZIP,PER_KOAZA,
HAS_CHOME,MULTI_AREA,CHANGE_FLAG,CHANGE_CODE,OLD_FLAG
FROM ZIPCODE_JP
WHERE ZIP_CODE=?
ORDER BY OLD_FLAG DESC;
"""

�f�[�^�̍X�V�o�^�}���p�֐�

def exec_sql(sql, params=[], conn=None, cur=None, dsn=dsn, user=conf['user'], password=conf['password'], charset='UTF8', debug=0):
"""
SQL�����s�֐�
Database�Ƃ�Connection�̈��p������ꍇ�ɂ́A����𗬗p��
���ACommit��Com\nnection��‚���������Ȃ��B
�� �A�����[�v���ł̕p�ɂ�Connection�̐����ƕ��A������
Commit��CPU���ׂ��傫���Ȃ�B���̏ꍇ�̈��p�����ɗL��
�A��CPU���ׂ����ɂȂ�Ȃ��ꍇ�ɂ́A�P���SQL���s����
Connection�̐����ƕ�������Commit�����{���������悢�B
������s����SQL��Commit����O�������Ŕ��f����Ȃ��”\��
�����邩��ł���B���̉”\�������������ꍇ���ɂ́Aconn��
None�����āA��O�������Ȃ����̂�L���ɂ��邱�Ƃ��o����B
�ʓr�A�֐����N�����ׂ������A�Ⴆ�΁A���������e�[�u����`
�𒴂����ꍇ�̉��}�Ώ��Ƃ��Ă��L�����낤�B
"""
if conn==None or cur==None: # Connection�̈��p���Ȃ��Ƃ���
from kinterbasdb import connect
conn = connect(dsn=dsn, user=user, password=password,charset=charset) # connect �I�u�W�F�N�g���쐬��
cur = conn.cursor() # cursor ���N����
not_connected = 1 # Connection���Ȃ������̂� 1
else:
not_connected = 0 # Connection���������̂� 0
try:
if params == []:
cur.execute(sql)
else:
cur.execute(sql,tuple(params))
#conn.commit()
except:
if debug>0:
from mire.std import display_err
display_err(locals(),title='Error! [ZipCodeJp.py insert()]')
else:
print 'Please, Contact Your System Administrator.'
raise
finally:
if not_connected == 1:
conn.commit()
conn.close()

def query(sql, params=[], conn=None, cur=None, dsn=dsn, user=conf['user'], password=conf['password'], charset='UTF8', mode='all', size=None, debug=0):
"""
�N�G���nSQL�����s�֐�
exec_sql()���l��Connection�̈��p���”\
sql�ɂ�SELECT�Ŏn�܂�N�G�����݂̂̎w���z��
mode�ɂ��Afetchone(), fetchmany(), fetchall()��
���ʂ̂����ꂩ��Ԃ����Ƃ��o����Bfetchall()�ȊO
�̖��擾���ɂ‚��ẮAconn��cur�����p�����ꍇ��
�̂݁A���̊֐��̊O�Ŏ擾�”\�ƂȂ�B
"""
if conn==None or cur==None: # Connection�̈��p���Ȃ��Ƃ���
from kinterbasdb import connect
conn = connect(dsn=dsn, user=user, password=password,charset=charset) # connect �I�u�W�F�N�g���쐬��
cur = conn.cursor() # cursor ���N����
not_connected = 1 # Connection���Ȃ������̂�1
else:
not_connected = 0 # Connection���������̂� 0

try:  
    if params == []:                                                       # �������Ȃ��ꍇ��  
        cur.execute(sql)                                                   # �����Ȃ���SQL�������s  
    else:  
        cur.execute(sql,tuple(params))                                     # ��������Ȃ�����t��

    if mode == 'one':  
        datas = cur.fetchone()                 # count sum ���ł͂���  
    elif mode == 'many':  
        if size==None:  
            datas = cur.fetchmany()            # �g�������ƂȂ�����Ȃ�  
        else:  
            datas = cur.fetchmany(size=size)   # �@�\����̂��m��Ȃ���!  
    else:  
        datas = cur.fetchall()                 # ���ʂ͂���ł��傤

except:  
    if debug>0:  
        from mire.std import display_err  
        display_err(locals(),title='Error! [ZipCodeJp.py insert()]')  
    else:  
        print 'Please, Contact Your System Administrator.'  
        raise  
finally:  
    if not_connected == 1:                                                 # Connection���Ȃ������Ƃ���  
        conn.commit()                                                      # �֐��I������commit��  
        conn.close()                                                       #             close ���s�Ȃ�  
return datas                                                               # �N�G������Ԃ�

�X�֔ԍ��f�[�^�̍X�V�}��

def dl_zipcode(cmd='./ZipCodeJp.sh', log='/var/log/ZipCodeJp.log', charset='cp932',
dsn=dsn, user=conf['user'], password=conf['password'],
tbl_name='ZIP_CODE_JP', sql_tbl=sql_tbl, sql_ins=sql_ins, debug=1):
"""
[5, 5, 7, 21, 66, 215, 12, 30, 111, 1, 1, 1, 1, 1, 1] Shift_JIS����bite��
[5, 5, 7, 7, 18, 48, 3, 8, 36, 1, 1, 1, 1, 1, 1] Unicode����̕�����
[5, 5, 7, 8, 24, 64, 4, 12, 38, 1, 1, 1, 1, 1, 1] �񍀖ڂ̕�����
1. �S���n�������c�̃R�[�h(JIS X0401�AX0402)�c�c�c�@ ���p���� 5
2. (��)�X�֔ԍ�(5��)�c�c�c�c�c�c�c�c�c�c�c�c�c�c�c�@���p���� 5
3. �X�֔ԍ�(7��)�c�c�c�c�c�c�c�c�c�c�c�c�c�c�c�@ ���p���� 7
4. �s���{�����@�c�c�c�c�@���p�J�^�J�i(�R�[�h���Ɍf��)�@(��1) 21
5. �s�撬�����@�c�c�c�c�@���p�J�^�J�i(�R�[�h���Ɍf��)�@(��1) 66
6. ���於�@�c�c�c�c�c�c�@���p�J�^�J�i(�܏\�����Ɍf��)�@(��1) 215 varchar(76)
7. �s���{�����@�c�c�c�c�@����(�R�[�h���Ɍf��)�@ (��1,2) 12
8. �s�撬�����@�c�c�c�c�@����(�R�[�h���Ɍf��)�@ (��1,2) 30
9. ���於�@�c�c�c�c�c�c�@����(�܏\�����Ɍf��)�@ (��1,2) 111 38
10. �꒬�悪��ȏ�̗X�֔ԍ��ŕ\�����ꍇ�̕\���@ (��3) 1
* �u1�v�͊Y��
* �u0�v�͊Y������
11. �������ɔԒn���N�Ԃ���Ă��钬��̕\���@ (��4) 1
* �u1�v�͊Y��
* �u0�v�͊Y������
12. ���ڂ�L���钬��̏ꍇ�̕\���@ 1
* �u1�v�͊Y��
* �u0�v�͊Y������
13. ��‚̗X�֔ԍ��œ�ȏ�̒����\���ꍇ�̕\���@ (��5) 1
* �u1�v�͊Y��
* �u0�v�͊Y������
14. �X�V�̕\�� �i��6�j 1
* �u0�v�ύX�Ȃ�
* �u1�v�ύX����
* �u2�v�p��i�p��f�[�^�̂ݎg�p�j
15. �ύX���R 1
* �u0�v�͕ύX�Ȃ�
* �u1�v�s���E�搭�E�����E����E���ߎw��s�s�{�s
* �u2�v�Z���\���̎��{
* �u3�v��搮��
* �u4�v�X�֋撲����
* �u5�v����
* �u6�v�p�(�p��f�[�^�̂ݎg�p)

   * ��1 �����R�[�h�ɂ́AMS�����R�[�h(SHIFT JIS)���g�p���Ă��܂��B  
   * ��2 �����Z�b�g�Ƃ��āAJIS X0208-1983���g�p���A�K�肳��Ă��Ȃ������͂Ђ炪�Ȃŕ\�L���Ă��܂��B  
   * ��3 �u�꒬�悪��ȏ�̗X�֔ԍ��ŕ\�����ꍇ�̕\���v�Ƃ́A����݂̂ł͗X�֔ԍ�������ł����A  
         ���ځA�Ԓn�A�����Ȃǂɂ��ԍ����قȂ钬��̂��Ƃł��B  
   * ��4 �u�������ɔԒn���N�Ԃ���Ă��钬��̕\���v�Ƃ́A�X�֔ԍ���ݒ肵������(�厚)�������̏�����L���Ă���A  
         �e�������ɔԒn���N�Ԃ���Ă��邽�߁A����(�X�֔ԍ�)�ƔԒn�����ł͏Z��������ł��Ȃ�����̂��Ƃł��B  
   * ��5 �u��‚̗X�֔ԍ��œ�ȏ�̒����\���ꍇ�̕\���v�Ƃ́A��‚̗X�֔ԍ��ŕ����̒�����܂Ƃ߂ĕ\���Ă���A  
         �X�֔ԍ��ƔԒn�����ł͏Z��������ł��Ȃ����Ƃ��������̂ł��B  
   * ��6 �u�ύX����v�Ƃ͒lj�����яC���ɂ��X�V���ꂽ�f�[�^���������̂ł��B  
   * ��7 �S�p�ƂȂ��Ă��钬�於�̕�������38�����𒴂���ꍇ�A�܂��A���p�J�^�J�i�ƂȂ��Ă��钬�於�̃t���K�i��  
         76�������z����ꍇ�ɂ́A�������R�[�h�ɕ������Ă��܂��B  
"""

try:  
    from os import popen  
    from csv import reader  
    from codecs import open  
    from types import StringType  
    from kinterbasdb import connect  
    from mire.std import str2unicode  
    from mire.fb import create_db, create_tbl, exec_sqls  
    ppi=popen('%s >> %s' % (cmd, log), 'r')  
    reader = reader(open('./ken_all.csv', 'r'))  
    create_db(dsn=dsn, user=conf['user'], password=conf['password'], charset='UTF8', debug=debug)  
    create_tbl(sql=sql_tbl, dsn=dsn, user=conf['user'], password=conf['password'], debug=debug)  
    conn = connect(dsn=dsn, user=conf['user'], password=conf['password'], charset='UTF8')  
    cur = conn.cursor()  
    exec_sql(sql=sql_flg, params=[], dsn=dsn, user=conf['user'], password=conf['password'], charset='UTF8', debug=debug)  
    mx =[0,0,0,0,0, 0,0,0,0,0, 0,0,0,0,0]  
    n=0  
    for rcd in reader:  
        i = 0  
        for r in rcd:  
            if type(r) is StringType:  
                #rcd[i] = u'%s' % (unicode(str2unicode(r,charset=charset, charsets=['Shift_JIS','EUC-JP','iso-2022-jp','UTF-8'], debug=0)))  
                rcd[i] = unicode(r, 'Shift_JIS')  
            i = i+1  
        #exec_sql(sql=sql_flag, params=rcd, dsn=dsn, user=conf['user'], password=conf['password'], charset='UTF8', debug=debug)  
        exec_sql(sql=sql_ins, params=rcd, conn=conn, cur=cur, dsn=dsn, user=conf['user'], password=conf['password'], charset='UTF8', debug=debug)  
        i = 0  
        for r in rcd:  
            mx[i]=max(mx[i],len(r))  
            i = i+1  
        n=n+1  
    print mx  
    try:  
        conn.commit()  
        conn.close()  
    except:  
        pass  
    exec_sql(sql=sql_del, params=[], dsn=dsn, user=conf['user'], password=conf['password'], charset='UTF8', debug=debug)  
except:  
    from mire.std import display_err  
    display_err(locals(),title='Error! [ZipCodeJp.py dl_zipcode()]')  
finally:  
    #fpo.close()  
    ppi.close()

if name == 'main':
dl_zipcode()

    ./ZipCodeJp.sh

#!/bin/sh
cd /var/www/html/ZipCodeJP
#cp ken_all.lzh ken_all_p.lzh
wget -m -t 12 -w 3600 --append-output=/var/log/ZipCodeJp.log http://www.post.japanpost.jp/zipcode/dl/kogaki/lzh/ken_all.lzh
lha xf ./www.post.japanpost.jp/zipcode/dl/kogaki/lzh/ken_all.lzh

| | | | ------------------------------------------------------------------------------------- |

�yFIREBIRD �֘A���e�z