郵便番号を読み込む。
sqlalchemy と pymysql が必要。
簡単な説明：
url = "mysql+pymysql://pyramid:pyramidpass@localhost/pyramid_sqlalchemy?unix_socket=/opt/local/var/run/mysql56/mysqld.sock&charset=utf8&use_unicode=1" は、MySQL との接続のための文字列。
ユーザーが「pyramid」で、パスワードが「pyramidpass」。
データベースは「localhost/pyramid_sqlalchemy」。
接続はソケットなので「unix_socket=/opt/local/var/run/mysql56/mysqld.sock」としている。
接続オプションとして「charset=utf8&use_unicode=1」を指定している。
class Pcode(Base) は、MySQL のテーブルを構成するためのモデル定義。
def create_table_and_add_pcode() は、レコード追加の関数。テーブルがなければ作成する。
class Postalcode: は CSV データを扱うためのクラス。
郵便番号データは、こちらからダウンロードする。
#!/usr/bin/env python
# coding: UTF-8
import csv
import unicodedata
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.schema import Sequence
from sqlalchemy.orm import sessionmaker
import pymysql
url = "mysql+pymysql://pyramid:pyramidpass@localhost/pyramid_sqlalchemy?unix_socket=/opt/local/var/run/mysql56/mysqld.sock&charset=utf8&use_unicode=1"
engine = create_engine(url, echo=False)
Base = declarative_base()
Session = sessionmaker(bind=engine)
class Pcode(Base):
__tablename__ = 'pcode'
id = Column(Integer, Sequence('pcode_id_seq'), primary_key=True)
x0 = Column(String(10))
post_short = Column(String(10))
post_long = Column(String(10))
pref_kana = Column(String(255))
city_kana = Column(String(255))
jyo_kana = Column(String(255))
pref = Column(String(255))
city = Column(String(255))
jyo = Column(String(255))
x1 = Column(Integer)
x2 = Column(Integer)
x3 = Column(Integer)
x4 = Column(Integer)
x5 = Column(Integer)
x6 = Column(Integer)
def __init__(self, x0, post_short, post_long, pref_kana, city_kana, jyo_kana, pref, city, jyo, x1, x2, x3, x4, x5, x6):
self.x0 = x0
self.post_short = post_short
self.post_long = post_long
self.pref_kana = pref_kana
self.city_kana = city_kana
self.jyo_kana = jyo_kana
self.pref = pref
self.city = city
self.jyo = jyo
self.x1 = x1
self.x2 = x2
self.x3 = x3
self.x4 = x4
self.x5 = x5
self.x6 = x6
def __repr__(self):
return "<Pcode(post_long='%s', pref='%s', city='%s', jyo='%s')>" % (
self.post_long, self.pref, self.city, self.jyo)
def create_table_and_add_pcode(session, x0, post_short, post_long, pref_kana, city_kana, jyo_kana, pref, city, jyo, x1, x2, x3, x4, x5, x6):
pcode = Pcode(x0, post_short, post_long, pref_kana, city_kana, jyo_kana, pref, city, jyo, x1, x2, x3, x4, x5, x6)
try:
session.add(pcode)
result = session.query(Pcode).filter_by(post_long=pcode.post_long).first()
except:
Base.metadata.create_all(engine)
session.rollback()
session.add(pcode)
result = session.query(Pcode).filter_by(post_long=pcode.post_long).first()
session.commit()
if True:
fname = 'KEN_ALL.CSV'
else:
fname = '01HOKKAI.CSV'
class Postalcode:
"""
01102,"060 ","0600812","ホッカイドウ","サッポロシキタク","キタ12ジョウニシ(5-12チョウメ)","北海道","札幌市北区","北十二条西(5~12丁目)",1,0,1,0,0,0
"""
def __init__(self, x0, post_short, post_long, pref_kana, city_kana, jyo_kana, pref, city, jyo, x1, x2, x3, x4, x5, x6):
self.x0 = x0.decode('cp932')
self.post_short = post_short.decode('cp932')
self.post_long = post_long.decode('cp932')
self.pref_kana = unicodedata.normalize('NFKC', pref_kana.decode('cp932'))
self.city_kana = unicodedata.normalize('NFKC', city_kana.decode('cp932'))
self.jyo_kana = unicodedata.normalize('NFKC', jyo_kana.decode('cp932'))
self.pref = pref.decode('cp932')
self.city = city.decode('cp932')
self.jyo = jyo.decode('cp932')
self.x1 = int(x1)
self.x2 = int(x2)
self.x3 = int(x3)
self.x4 = int(x4)
self.x5 = int(x5)
self.x6 = int(x6)
def convert_csv():
csvfile = open(fname)
reader = csv.reader(csvfile)
session = Session()
for row in reader:
pcode = Postalcode(row[0], row[1], row[2], row[3], row[4],
row[5], row[6], row[7], row[8], row[9],
row[10], row[11], row[12], row[13], row[14])
create_table_and_add_pcode(session,
pcode.x0, pcode.post_short, pcode.post_long,
pcode.pref_kana, pcode.city_kana, pcode.jyo_kana,
pcode.pref, pcode.city, pcode.jyo,
pcode.x1, pcode.x2, pcode.x3, pcode.x4, pcode.x5, pcode.x6)
if __name__ == '__main__':
convert_csv()
