Setup: I have been learning SQL Alchemy to build a prototype (proof of concept) Flask app for our internal website. We want to replace our current site, which is made entirely of slow python CGIs and raw SQL. Our database (Postgres) is fairly large, but has some unusual table relationships, and is highly normalized in regards to data entry (BUT, I am only interested in a read only interface at the moment. I.e. queries and summaries). My goal is to see how well SQL Alchemy can handle some of these relationships. Whether, it is being able to fully define the relationships in the model, or if we have to define the basic objects and use join syntax at query time, I just want to know what's possible.
Ok, so here is one example that I haven't been able to figure out the correct way to do (There is *a lot* you can do in SQLAlchemy!). I won't go into all my failed attempts. We have a join table (several like it actually) that holds many different types of object relationships in it. You specify the correct join conditions using a type key. Say these are the tables: Marker, Allele, Reference_Assoc, Reference. You get markers for a reference by doing "Reference.ref_key = Reference_Assoc.ref_key AND Reference_Assoc.object_key=Marker.marker_key AND Reference_Assoc.type_key=1". However you can also get alleles for a reference by doing "Reference.ref_key = Reference_Assoc.ref_key AND Reference_Assoc.object_key=Allele.allele_key AND Reference_Assoce.type_key=2" I don't know a whole lot about database patterns, so I don't know what this relationship would be called. However, this database schema is not changing anytime soon, so I'm stuck with it. So.. Markers and References, or Alleles and References, are both many to many relationships through the Reference_Assoc table. Is it possible to represent this in SQL Alchemy mappings, where you could set up a mapping to have Marker.references as a property (and the backref Reference.markers)? It would be really nice to be able to define this relationship in an easy way, because we have over 30 different types of objects that can be associated this way. P.S. I have been using the declarative method of defining tables, but I can go a different route if that won't work. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
