So I laid them out like this:
class Run(Base):
# For autoincrementing run IDs
# Allows addition of more information to a run
__tablename__ = 'run'
id = Column(Integer, primary_key=True)
timestamp = Column(DateTime, nullable=False)
# comment = Column(UnicodeText(100), nullable=False)
trials = relationship('Trial',
back_populates='run',
order_by=lambda: Trial.id.asc())
class Trial(Base):
# Having a separate table here is of dubious value, but hey it
makes the
# relationships a bit nicer!
__tablename__ = 'trial'
__table_args__ = (PrimaryKeyConstraint('run_id', 'id'), {})
run_id = Column(Integer, ForeignKey('run.id'))
id = Column(Integer)
run = relationship('Run', back_populates='trials')
sim_allocs = relationship('SimAllocation', back_populates='trial')
class SimAllocation(Base):
#
__tablename__ = 'sim_alloc'
__table_args__ = (PrimaryKeyConstraint('run_id', 'trial_id',
'stud_id'),
ForeignKeyConstraint(['run_id', 'trial_id'],
['trial.run_id',
'trial.id']),
{})
run_id = Column(Integer)
trial_id = Column(Integer)
stud_id = Column(Integer)
trial = relationship('Trial', back_populates='sim_allocs')
def __init__(self, ident, uid, session_id, stud_id, alloc_proj_rank):
self.ident = ident
self.uid = uid
self.session_id = session_id
self.stud_id = stud_id
self.alloc_proj = None
self.alloc_proj_ref = None
self.alloc_proj_rank = alloc_proj_rank
def __repr__(self):
return str(self)
def __str__(self):
return "Row: %s UID: %s - %s: Student: %s (Project: %s - Rank:
%s)" %
(self.ident, self.uid, self.session_id, self.stud_id, self.alloc_proj,
self.alloc_proj_rank)
#####
The original mapping was:
simulation_allocation = Table('sim_alloc', metadata,
Column('ident', Integer),
Column('uid', String, primary_key=True),
Column('session_id', Integer, primary_key=True),
Column('stud_id', Integer, ForeignKey('studs.ee_id'),
primary_key=True),
Column('alloc_proj', Integer, ForeignKey('projs.proj_id')),
Column('alloc_proj_rank', Integer)
)
mapper(SimAllocation, simulation_allocation, properties={'stud' :
relation(StudentDBRecord), 'proj' : relation(Project)})
Of course, I'd get rid of the project relationship since an
allocated_project and allocated_proj_ref *can* be a NoneType....
(realised that right now!)
Additionally, I'd like to maintain the ForeignKey relationship with
the StudentDRRecord table for pulling in info about a student.
Also, I've not got rid of ident because I don't know how else to map
SimAllocation to a dictionary as well. The only thing I could use for
keys was the IDENT before but now that we have a composite key, what
happens to the dictionary? However, the dictionary will just hold
information for the current run really.
On Jun 10, 6:33 pm, Az <[email protected]> wrote:
> The pprintout was:
>
> {<type 'collections.defaultdict'>: 156,
> <type 'bool'>: 2,
> <type 'float'>: 1,
> <type 'int'>: 538,
> <type 'list'>: 1130,
> <type 'dict'>: 867,
> <type 'NoneType'>: 1,
> <type 'set'>: 932,
> <type 'str'>: 577,
> <type 'tuple'>: 1717,
> <type 'type'>: 5,
> <class 'sqlalchemy.util.symbol'>: 1,
> <class 'sqlalchemy.orm.state.InstanceState'>: 236,
> <class 'ProjectParties.Student'>: 156,
> <class 'ProjectParties.Supervisor'>: 39,
> <class 'ProjectParties.Project'>: 197}
>
> I think the InstanceStates come from the Supervisor and Project
> classes (197+39 = 236)
>
>
>
> > Sounds pretty ugly. What if you add extra tables to represent runs
> > and/or trials?
>
> > class Run(Base):
> > # Having a separate table here gives you nice auto-incrementing run ids
> > # and lets you attach additional information to a run, such as
> > timestamp,
> > # human-supplied comment, etc.
> > __tablename__ = 'run'
> > id = Column(Integer, primary_key=True)
> > timestamp = Column(DateTime, nullable=False)
> > # comment = Column(UnicodeText(100), nullable=False)
>
> > trials = relationship('Trial',
> > back_populates='run',
> > order_by=lambda: Trial.id.asc())
>
> > class Trial(Base):
> > # Having a separate table here is of dubious value, but hey it makes the
> > # relationships a bit nicer!
> > __tablename__ = 'trial'
> > __table_args__ = (PrimaryKeyConstraint('run_id', 'id'), {})
> > run_id = Column(Integer, ForeignKey('run.id'))
> > id = Column(Integer)
>
> > run = relationship('Run', back_populates='trials')
> > sim_allocs = relationship('SimAllocation', back_populates='trial')
>
> > class SimAllocation(Base):
> > ...
> > __table_args__ = (PrimaryKeyConstraint('run_id', 'trial_id', 'stud_id'),
> > ForeignKeyConstraint(['run_id', 'trial_id'],
> > ['trial.run_id', 'trial.id']),
> > {})
>
> > run_id = Column(Integer)
> > trial_id = Column(Integer)
> > stud_id = Column(Integer)
>
> > trial = relationship('Trial', back_populates='sim_allocs')
>
> Ah true, my solution was rather hacky and not very elegant.
>
> Your class definitions... are you defining both table and Class in one
> go? Would I have to change the way my monteCarloBasic creates
> instances of SimAllocation?
>
> On Jun 9, 9:46 pm, Conor <[email protected]> wrote:
>
> > On 06/09/2010 02:45 PM, Az wrote:
>
> > >> Expected: students, supervisors, projects, dictionaries of said objects,
> > >> and other attribute values (strings, ints, lists, etc.). Unexpected:
> > >> anything else, especially sessions, InstanceState objects, or other ORM
> > >> support objects.
>
> > > Actually got some stuff like the following (copy-pasting bits from my
> > > print output):
>
> > > (<class 'sqlalchemy.orm.state.InstanceState'>,)
> > > {'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at
> > > 0x2d5beb0>, 'proj_id': 1100034, 'postsim_probs': [], 'proj_sup': 1291,
> > > 'presim_pop': 0, 'own_project': False, 'allocated': False,
> > > 'proj_name': 'MPC on a Chip', 'blocked': False}
>
> > > Stuff like that :S
>
> > I'm not sure what that printout indicates. Try this as your debug printout:
>
> > def get_memo_type_count(memo):
> > retval = {}
> > for obj in memo.itervalues():
> > type_ = obj.__class__
> > retval[type_] = retval.get(type_, 0) + 1
> > return retval
>
> > [perform deep copies]
> > type_count = get_memo_type_count(memo)
> > import pprint
> > pprint.pprint(type_count)
>
> > This will tell you, e.g. how may Student objects were copied, how many
> > InstanceState objects were copied, etc. Remember that you will have to
> > override __deepcopy__ on your mapped classes or use the
> > use-case-specific copy function to prevent ORM attributes (such as
> > _sa_instance_state) from being copied.
>
> > > [...]
> > >> The most likely cause is if you call session.add(temp_alloc) after
> > >> calling session.merge(temp_alloc) for the same temp_alloc object. I
> > >> noticed your original monteCarloBasic had two calls to
> > >> session.add(temp_alloc); did both get changed to
> > >> session.merge(temp_alloc)? If that doesn't work, can you verify that
> > >> SQLAlchemy's primary key for SimAllocation matches the database's
> > >> primary key for sim_alloc? What column type are you using for uid? Which
> > >> call to session.merge is failing (line 163 according to your traceback),
> > >> the one inside your "for rank in ranks" loop or the one outside?
>
> > > Oh yeah good point, they're separate calls. Basically for the one in
> > > "for rank in ranks"
> > > adds for a student getting a project, the other adds if a student
> > > doesn't get a project since we want
> > > to track all students (allocated or not, since the state of being
> > > unallocated is what gives
> > > us motivation to optimise the results).
>
> > Your original monteCarloBasic definition had this:
>
> > for rank in ranks:
> > proj = random.choice(list(student.preferences[rank]))
> > if not (proj.allocated or proj.blocked or proj.own_project):
> > [...]
> > session.add(temp_alloc) # #1
> > break
>
> > ident += 1
> > session.add(temp_alloc) # #2
>
> > session.add #1 is redundant since #2 gets called regardless of whether
> > the student gets allocated a project or not (ignoring exceptions). Just
> > a minor nitpick.
>
> > > Anyway, session.merge() is for overwriting previously existing values
> > > right? Now thanks to the UUID I can add multiple calls to
> > > monteCarloBasic() to my physical database :)
>
> > session.merge gives you "find or create" behavior: look for an existing
> > object in the database, or create a new one if no existing object is
> > found. Note that session.merge requires you to completely fill in the
> > object's primary key whereas session.add does not.
>
> > > I basically wrote a small function that, for everytime the
> > > monteCarloBasic() is called, will append the UUID, the number of
> > > trials ran and the date-time to a text file. My supervisor would have
> > > to copy paste that into a GUI text field or the command line but it's
> > > not that much of a hassle, given the usefulness of the database.
>
> > Sounds pretty ugly. What if you add extra tables to represent runs
> > and/or trials?
>
> > class Run(Base):
> > # Having a separate table here gives you nice auto-incrementing run ids
> > # and lets you attach additional information to a run, such as
> > timestamp,
> > # human-supplied comment, etc.
> > __tablename__ = 'run'
> > id = Column(Integer, primary_key=True)
> > timestamp = Column(DateTime, nullable=False)
> > # comment = Column(UnicodeText(100), nullable=False)
>
> > trials = relationship('Trial',
> > back_populates='run',
> > order_by=lambda: Trial.id.asc())
>
> > class Trial(Base):
> > # Having a separate table here is of dubious value, but hey it makes the
> > # relationships a bit nicer!
> > __tablename__ = 'trial'
> > __table_args__ = (PrimaryKeyConstraint('run_id', 'id'), {})
> > run_id = Column(Integer, ForeignKey('run.id'))
> > id = Column(Integer)
>
> > run = relationship('Run', back_populates='trials')
> > sim_allocs = relationship('SimAllocation', back_populates='trial')
>
> > class SimAllocation(Base):
> > ...
> > __table_args__ = (PrimaryKeyConstraint('run_id', 'trial_id', 'stud_id'),
> > ForeignKeyConstraint(['run_id', 'trial_id'],
> > ['trial.run_id', 'trial.id']),
> > {})
>
> > run_id = Column(Integer)
> > trial_id = Column(Integer)
> > stud_id = Column(Integer)
>
> > trial = relationship('Trial', back_populates='sim_allocs')
>
> > -Conor
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
