title: Normalization of flat file, configuring sqlalchemy- setting several many-to-one set-like relationships declaratively
body: <p>My question is very similar to a previous posting (How to reference the child of a many to one relationship in SQLAlchemy?), however the solution was lost on me owing to my inexperience with sqla, python, etc...</p>
  
  <p>I have a flat .csv file (RawData) and I'm trying to normalize this parent table into set-like child (look-up?) tables and then impose referential integrity using python2.7, sqlite3 v.3.7.7 on ubuntu 11.10.</p>
  
  <p>I also realise that my understanding and naming conventions might also be causing problems as it would appear I'm trying to make distinct sets of people, institutions, places, etc. on the unitary side of a many-to-one relationship as children, with the exception being the relation between RawGrant and Grant, which I think should be 1-to-1.</p>
  
  <p>I've a sneaking suspicion that there are a number of problems in my attempts and thought it might be more expedient to ask in the hopes of being cured of my ignorances.</p>
  
  <p>Many thanks in advance,
  christopher</p>
  
  <p>Here is the previous question I was trying to follow:
  <a href="https://stackoverflow.com/questions/7933166/how-to-reference-the-child-of-a-many-to-one-relationship-in-sqlalchemy">How to reference the child of a many to one relationship in SQLAlchemy?</a></p>
  
  <p>Here, I think, is what I'm trying to do, but am getting confused with the nomenclature btw class, table and relation:</p>
  
  <pre><code>class Parent(Base):
      __tablename__ = 'parent'
      parent_id = Column(Integer, primary_key=True)
  
      # use a set
      children = relationship(Child, collection_class=set)
  
  parent = Parent()
  child = Child()
  parent.children.add(child)
  assert child in parent.children
  </code></pre>
  
  <p>And here are my efforts so far (below).</p>
  
  <p>Apologies for the less-than-helpful earlier posting...</p>
  
  <pre><code>from sqlalchemy import create_engine, MetaData, ForeignKey
  from sqlalchemy import Table, Column, Integer, UnicodeText, Date
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker, backref, relationship
  from sqlalchemy.orm.collections import collection
  
  Base = declarative_base()
  metadata = MetaData()
  
  class RawData(Base):
      __tablename__ = 'data'
      master_id = Column(Integer,primary_key=True, autoincrement=True)
      dataset = Column(UnicodeText)
      grant_id_rcid = Column(Integer)
      grant_id_grantor = Column(UnicodeText)
      reference_number_nih = Column(UnicodeText)
      grant_type_nih = Column(UnicodeText)
      grant_activity_code_nih = Column(UnicodeText)
      project_status = Column(UnicodeText)
      area_of_research_nih = Column(UnicodeText)
      grantor_id_nih = Column(Integer)
      recipient_id_nih = Column(Integer)
      pi_id_nih = Column(Integer)
      bmrcs = Column(UnicodeText)
      title = Column(UnicodeText)
      abstract = Column(UnicodeText)
      background = Column(UnicodeText)
      project_description = Column(UnicodeText)
      anticipated_outcomes = Column(UnicodeText)
      actual_outcomes = Column(UnicodeText)
      publications = Column(UnicodeText)
      start_date = Column(Date)
      end_date = Column(Date)
      fiscal_year = Column(Date)
      funding = Column(Integer)
      currency = Column(UnicodeText)
      department = Column(UnicodeText)
      school = Column(UnicodeText)
      city = Column(UnicodeText)
      state = Column(UnicodeText)
      data_source = Column(UnicodeText)
      data_source_org = Column(UnicodeText)
      created_by = Column(UnicodeText)
      created_date = Column(Date) 
      updated_by = Column(UnicodeText)
      updated_date = Column(Date)
      country = Column(UnicodeText)
      recipient_org_type = Column(UnicodeText)
      department_combine_name = Column(UnicodeText)
      keywords = Column(UnicodeText) 
      active = Column(UnicodeText)
      access_level = Column(UnicodeText)
      pi_fullname = Column(UnicodeText)
      pi_firstname = Column(UnicodeText)
      pi_midname = Column(UnicodeText)
      pi_surname = Column(UnicodeText)
      recipient_org_name = Column(UnicodeText)
      grantor_name = Column(UnicodeText)
      grantor_name_short = Column(UnicodeText)
      co_principal_investigator = Column(UnicodeText)
      fund_type = Column(UnicodeText)
      program_area = Column(UnicodeText)
      grant_id = Column(Integer, ForeignKey('grants.grant_id_master'), onupdate="cascade")
      grant = relationship('Grant', uselist=False, backref='data', passive_updates=False,\ cascade='all', collection_class=set)
      pi_id = Column(Integer, ForeignKey('investigators.pi_id_master'),\ onupdate="cascade")    
      pi = relationship('PI', backref='data', passive_updates=False, cascade='all',\
  collection_class=set)
      grantor_id = Column(Integer, ForeignKey('grantors.grantor_id_master'),\ onupdate="cascade")
      grantor = relationship('Grantor', backref='data', passive_updates=False, cascade='all',\ collection_class=set)
      recipient_id = Column(Integer, ForeignKey('recipients.recipient_id_master'),\ onupdate="cascade")
      recipient = relationship('Recipient', backref='data', passive_updates=False,\ cascade='all', collection_class=set)
      address_id = Column(Integer, ForeignKey('addresses.address_id_master'),\ onupdate="cascade")
      address = relationship('Address', backref='data', passive_updates=False, cascade='all',\ collection_class=set)
      gac_id = Column(Integer, ForeignKey('grant_activity_codes.gac_id_master'),\ onupdate="cascade")
      gac = relationship('GAC', backref='data', passive_updates=False, cascade='all',\ collection_class=set)
      gt_id = Column(Integer, ForeignKey('grant_types.gt_id_master'), onupdate="cascade")
      gt = relationship('GT', backref='data', passive_updates=False,cascade='all',\ collection_class=set)
  
  class Grant(Base):
  ##    __emulates__ = set
      __tablename__ = 'grants'
      grant_id_master = Column(Integer, primary_key=True, autoincrement=True)
      dataset = Column(UnicodeText)
      grant_id_rcid = Column(Integer)
  
      grant_id_grantor = Column(UnicodeText)
      reference_number_nih = Column(UnicodeText)
      bmrcs = Column(UnicodeText)
      title = Column(UnicodeText)
      abstract = Column(UnicodeText)
      start_date = Column(Date)
      end_date = Column(Date)
      fiscal_year = Column(Date)
      funding = Column(Integer)
      currency = Column(UnicodeText)
      data_source = Column(UnicodeText)
      data_source_org = Column(UnicodeText)
      fund_type = Column(UnicodeText)
      program_area = Column(UnicodeText)
  
  ##    def __init__(self):
  ##        self.data = set()
  ##    def append(self, item):
  ##        self.data.add(item)
  ##    def remove(self, item):
  ##        self.data.remove(item)
  ##    def __iter__(self):
  ##        return iter(self.data)
  
  class PI(Base):
      __tablename__= "investigators"
      pi_id_master = Column(Integer, primary_key=True, autoincrement=True)
      pi_id_nih = Column(Integer)
      pi_fullname = Column(UnicodeText)
      pi_firstname = Column(UnicodeText)
      pi_midname = Column(UnicodeText)
      pi_surname = Column(UnicodeText)
  
  class Grantor(Base):
      __tablename__= "grantors"
      grantor_id_master = Column(Integer, primary_key=True, autoincrement=True)
      grantor_id_nih = Column(Integer)
      grantor_name = Column(UnicodeText)
      grantor_name_short = Column(UnicodeText)
  
  class Recipient(Base):
      __tablename__= "recipients"
      recipient_id_master = Column(Integer,primary_key=True, autoincrement=True)
      recipient_id_nih = Column(Integer)
      department = Column(UnicodeText)
      school = Column(UnicodeText)
      recipient_org_type = Column(UnicodeText)
      department_combine_name = Column(UnicodeText)
      recipient_org_name = Column(UnicodeText)
  
  class Address(Base):
      __tablename__= "addresses"
      address_id_master = Column(Integer, primary_key=True, autoincrement=True)
      city = Column(UnicodeText)
      state = Column(UnicodeText)
      country = Column(UnicodeText)
  
  class GAC(Base):
      __tablename__= "grant_activity_codes"
      gac_id_master = Column(Integer, primary_key=True, autoincrement=True)
      grant_activity_code = Column(UnicodeText)
  
  class GT(Base):
      __tablename__= "grant_types"
      gt_id_master = Column(Integer, primary_key=True, autoincrement=True)
      grant_type = Column(UnicodeText)
  
  data = RawData() # parent = Parent()
  grants = Grant()  # child = Child()
  grantors = Grantor()
  recipients = Recipient()
  investigators = PI()
  addresses = Address()
  grant_activity_codes = GAC()
  grant_types = GT()
  ## parent.children.append(Child())
  
  ##data.grant.append(Grants())
  ##data.grant.append(Grantor())
  ##data.grant.append(Recipient())
  ##data.grant.append(PI())
  ##data.grant.append(Address())
  ##data.grant.append(GAC())
  ##data.grant.append(GT())
  
  ##data.grant.append(grants)  # parent.children.add(child)
  ##assert grants in data.grant # assert child in parent.children
  ##data.grantor.append(grantors)
  ##assert grantors in data.grantor
  ##data.recipient.append(recipients)
  ##assert recipients in data.recipient
  ##data.pi.append(investigators)
  ##assert investigators in data.pi
  ##data.address.append(addresses)
  ##assert addresses in data.address
  ##data.gac.append(grant_activity_codes)
  ##assert grant_activity_codes in data.gac
  ##data.gt.append(grant_types)
  ##assert grant_types in data.gt
  
  engine = create_engine('sqlite:///cascade-empty2.db', echo=True)
  Session = sessionmaker(bind=engine)
  session = Session()
  Base.metadata.drop_all(engine)
  Base.metadata.create_all(engine)
  ##session.add_all()
  ##session.commit()
  </code></pre>