gistfile1.py

#!/usr/bin/env python
     
import csv
reader = csv.reader(open("xalph.txt", "rb"))
     
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, Session
     
# Set up the database.
engine = create_engine('sqlite:///cpc_roll.db', echo=True)
engine.execute('pragma foreign_keys=on')
Base = declarative_base(bind=engine)
     
class Person(Base):
    __tablename__ = 'people'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    forenames = Column(String)
    surname = Column(String)
    address_id = Column(Integer, ForeignKey('addresses.id'))
    address = relationship('Address', backref='people')
     
class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    house = Column(String)
    street = Column(String)
    district_id = Column(Integer, ForeignKey('districts.id'))
    district = relationship('District', backref='addresses')
     
class District(Base):
    __tablename__ = 'districts'
    id = Column(Integer, primary_key=True)
    label = Column(String)
     
Base.metadata.create_all()
session = Session()
     
# initialize storage lists
addresses = {}
districts = {}
     
for row in reader:
    # isolate address and district
    locus = row[1].strip().split(' ')
     
    # isolate district
    d  = locus.pop().strip('()')
    if d in districts:
        district = districts[d]
    else:
        district = districts[d] = District(label=d)
        session.add(district)
     
    # isolate house and street
    house = str(locus.pop(0))
    street = ' '.join(locus)
    key = (house, street)
    if key in addresses:
        address = addresses[key]
    else:
        address = addresses[key] = Address(house=house, street=street, district=district)
     
    # isolate person name
    name = row[0].strip().split(' ')
    surname = name.pop().strip()
    title = name.pop(0)
    forenames = ' '.join(name)
    Person(title=title, forenames=forenames, surname=surname, address=address)
     
session.commit()
     
print "Database created and populated!"