On Fri, Jul 28, 2017 at 12:44 AM, Saurabh Bhandiwad
<[email protected]> wrote:
> 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_base
> from sqlalchemy.orm import Session, mapper
> from sqlalchemy import create_engine
> from sqlalchemy import Integer, Text
> from 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())
>
I haven't run your code, so this answer may not be appropriate, but in
general, you can create a relationship when using classical mappings
by passing a "properties" parameter to the mapper() function,
something like this:
mapper(TempTable,
temp_table,
primary_key=temp_table.c.Key,
properties={'main': relationship(Main, backref='property_%s' %
table[0])},
)
http://docs.sqlalchemy.org/en/latest/orm/mapping_styles.html#classical-mappings
Hope that helps,
Simon
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
