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.
