Hello experts, Apologies if this question has been asked before, I couldn't find the right question on internet which reflects my problem.
In my project i receive SQLITE file and I need to query information. Though the Database Schema is bit unorthodox. For example, Database will contain *n* tables in - *Main, PropertyReference * and *n-2 Property%d* tables. *Main* contains Key and value columns and *PropertyReference *contains *Id* and *PropertyName *(Property Table name may not always be Property%d). Hence I need to read the Property table name before I create any mapping for actual *Property%d*. I could create the *automap_base *class for *Main *and *PropertyReference * easily*. *I created Table object for each *Property%d *and mapped them using mapper (in *get_property_tables*). while creating table I created ForeignKey with *Main.Key* for one-to-one unidirectional mapping. Though I couldn't figure out how to create relationship while creating Table object. Hence i am using *join* API of *Query* to explicitly join these classes. Since i am fairly new to Sqlalchemy, my concern is, am i doing it right way? Is there any better way to solve this problem? I was hoping to create a relationship for my Property%d tables with Main so I don't have to use *join* explicitly. Thanks in advance!!! Sample Code:: I have not attached the dummy.dB I created. Let me know if that is needed. from sqlalchemy.ext.automap import automap_basefrom sqlalchemy.orm import Session, mapperfrom sqlalchemy import create_enginefrom sqlalchemy import Integer, Textfrom sqlalchemy import ForeignKey, Column, Table Base = automap_base() class Main(Base): __tablename__ = "Main" key = Column("Key", Text) value = Column("value", Integer) class PropertyReference(Base): __tablename__ = "PropertyReference" id = Column(Integer, primary_key=True) tablename = Column(Text) def get_property_tables(session): ref_tables_names = session.query( PropertyReference.tablename ).all() ref_table = {} for table in ref_tables_names: temp_table = Table( table[0], Base.metadata, Column('Key', Text, ForeignKey(Main.key)), Column('Value', Text), Column('Status', Text), extend_existing=True, ) class TempTable(object): pass mapper(TempTable, temp_table, primary_key=temp_table.c.Key) ref_table[table[0]] = temp_table return ref_table engine = create_engine(r'sqlite:///C:\Temp\dummy.dB') Base.prepare(engine, reflect=True) session = Session(engine) property_tables = get_property_tables(session) failed_keys = []for name, table in property_tables.items(): failed_keys.append(session.query(table, Main).filter(table.c.Status=="FAIL").join(Main).all()) -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.