�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
- SQL RDBMS FIREBIRD��b ������ (��)
- SQL RDBMS FIREBIRD�ƃf�[�g ���ʗ�����
- SQL RDBMS FIREBIRD��2.5�Ŏn�߂悤 +�ڑ���Q�ؕ���
- Firebird-2.5.0.RC2 ��Install
- �yCentOS�zFirebird-2.5.0.RC2 ��Superclassic�N����
- �yFirebird�zDB/TABLE�쐬�pPython���W���[��
- �yFirebird�zDB/TABLE�쐬�pPython���W���[��2
- �yFirebird�z�X�֔ԍ���DB�̍쐬 ./dl_ZipCodeJp.py
- �yFirebird�z�X�֔ԍ���DB�̍쐬(2) ���p���W���[�� mire.fb
- �yFirebird�z�X�֔ԍ���DB�̍쐬(3) conf_section_items in mire.std
- �yFirebird�z�X�֔ԍ���DB�̍쐬(4) �����t�H�[���Ŋm�F
- �yFirebird�z�X�֔ԍ���DB�̍쐬(5) mire.htm �� str_input_form()