I am relatively new both to python and to sqlalchemy. I am a hobbiest,
trying to learn this for fun. Have been using a Sunday tea rota as a
simple project by which to learn the rudiments of sqla. The rota
consists of 16 people and the number of days on which tea is offered
amounts to 47. Although I realised that using sqla for such a thing is
like using a sledge-hammer to crack a nut, I thought it would help me to
learn. My input data is in the form of a .csv file, whose records have
two fields: the date, expressed in the form yy-mm-dd and the name of
the volunteer. I show below the basic script that I have been trying to
construct with various bits of help along the way. As my attempts so
far have not been entirely successful, I am now wondering if I need an
m-m rather than an m-1? If anyone can show me where I am going wrong,
I would be most grateful, as I am feeling somewhat discouraged. The csv
file content is shown below the script. I know that it is very trivial
and basic compared to what I see being discussed here but I would
appreciate help. Thanks!
#!/usr/bin/env python
from sqlalchemy import *
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, mapper, backref,
subqueryload
import cgi
import csv
#import cgitb; cgitb.enable() # for troubleshooting
print Content-type: text/html
print
reader = csv.reader(open(cpc_tea2012.csv, rb))
# initialise storage lists
volunteers = {} #volunteers
duties = {} #duties
# Set up the database.
engine = create_engine('sqlite:///teapot.db', echo=False)
Base = declarative_base(bind=engine)
Session = (sessionmaker(engine))
class Volunteer(Base):
__tablename__ = 'volunteers'
id = Column(Integer, primary_key=True)
fore = Column(String)
surn = Column(String)
name = Column(String)
dates = association_proxy('duties', 'date')
class Duty(Base):
__tablename__ = 'duties'
id = Column(Integer, primary_key=True)
date = Column(String)
volunteer_id = Column(Integer, ForeignKey('volunteers.id'))
volunteer = relationship('Volunteer', backref=backref('duties'))
Base.metadata.create_all()
session = Session()
for row in reader:
date = row[0]
name = row[1].strip()
(fore, surn) = name.split(' ')
if date not in duties:
duty = Duty(date=date)
duties[date] = duty
if name not in volunteers:
volunteer = Volunteer(fore=fore, surn=surn, name=name)
volunteers[name] = volunteer
volunteer.duties.append(duty)
session.add_all(volunteers.values())
session.commit()
'''This gives the correct output of 47 dates'''
#for x, duties in enumerate(session.query(Duty).order_by(Duty.date)):
#print x+1, duties.date
#print
'''This prints 8 people out of the sixteen'''
#for x, volunteers in
enumerate(session.query(Volunteer).join(Volunteer.duties).order_by(Volunteer.surn)):
#print x+1, volunteers.fore,volunteers.surn
'''This gives 16 names but 1 or 0 dates for each person. There should
be about 4 or 5, or so, per person'''
for person in
session.query(Volunteer).options(subqueryload(Volunteer.duties)):
print person.name
for d in person.duties:
print d.date
The .csv data is as shown below
2012-01-08,Jane MacKay
2012-01-08,Morag Gordon
2012-01-15,Gail Monteith
2012-01-15,Irene Mailer
2012-01-22,Margaret Barclay
2012-01-22,Jean Gray
2012-01-29,Sybil Baxter
2012-01-29,Marie Morrison
2012-02-12,Liz MacMillan
2012-02-12,Ruth Haining
2012-02-19,Anne Noble
2012-02-19,Audrey Thomson
2012-02-26,Liz Kerr
2012-02-26,Elaine Butler
2012-03-04,Moyra Noble
2012-03-04,Elizabeth Batchelor
2012-03-11,Irene Mailer
2012-03-11,Margaret Barclay
2012-03-18,Jean Gray
2012-03-18,Gail Monteith
2012-03-25,Marie Morrison
2012-03-25,Jane MacKay
2012-04-01,Liz MacMillan
2012-04-01,Sybil Baxter
2012-04-08,Ruth Haining
2012-04-08,Anne Noble
2012-04-15,Audrey Thomson
2012-04-15,Morag Gordon
2012-04-22,Moyra Noble
2012-04-22,Elaine Butler
2012-04-29,Liz Kerr
2012-04-29,Elizabeth Batchelor
2012-05-06,Margaret Barclay
2012-05-06,Marie Morrison
2012-05-13,Gail Monteith
2012-05-13,Liz MacMillan
2012-05-20,Jane MacKay
2012-05-20,Jean Gray
2012-05-27,Sybil Baxter
2012-05-27,Irene Mailer
2012-06-10,Anne Noble
2012-06-10,Elaine Butler
2012-06-17,Elizabeth Batchelor
2012-06-17,Audrey Thomson
2012-06-24,Ruth Haining
2012-06-24,Liz Kerr
2012-07-01,Morag Gordon
2012-07-01,Moyra Noble
2012-07-08,Marie Morrison
2012-07-08,Gail Monteith
2012-07-15,Irene Mailer
2012-07-15,Sybil Baxter
2012-07-22,Liz MacMillan
2012-07-22,Margaret Barclay
2012-07-29,Morag Gordon
2012-07-29,Elizabeth Batchelor
2012-08-05,Elaine Butler
2012-08-05,Jane MacKay
2012-08-12,Ruth Haining
2012-08-12,Jean Gray
2012-08-19,Anne Noble
2012-08-19,Liz Kerr
2012-08-26,Moyra Noble
2012-08-26,Audrey Thomson
2012-09-02,Gail Monteith
2012-09-02,Margaret Barclay
2012-09-09,Irene Mailer
2012-09-09,Marie
