On Nov 16, 2012, at 2:59 PM, Rob Crowell wrote:
>
> Thanks for the help so far Michael! I can explain a little more about
> what I'm trying to do (I'm using a fictional application here but I
> think it pretty accurately translates into my actual application).
>
> BACKGROUND
> ----------
>
> Let's say I'm writing an issue tracking application. Each issue that
> we're tracking has a type (an issue must have exactly one type), and
> each issue may have an unlimited number of user-provided labels.
>
> Each day, people browse our issue tracker and each time they do they
> generate a page view on the issue. Here's an example of one day's
> worth of data:
>
> IssueA: <Views: 1, Type: "Bug", Labels: ["login-page (id=1)",
> "show-stopper (id=2)"]>
> IssueB: <Views: 20, Type: "One-Time Task", Labels: ["disk-full
> (id=3)", "show-stopper (id=2)"]>
> IssueC: <Views: 300, Type: "Bug", Labels: ["login-page (id=1)"]>
>
> The BigCo. I'm working for is very interested in knowing which issues
> are read by the most people, and they need the ability to generate
> reports sliced by arbitrary date ranges. However, we can tolerate a
> day delay, so we are writing summary tables each night. Two of these
> summary tables are aggregated by either issue type or label, and we
> also write a third table that can be used to drill-down and see page
> visits bucketed by both type and label:
>
> CREATE TABLE `issue_type` (
> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `created` datetime NOT NULL,
> `type` enum('Bug','Feature','Improvement','One-Time Task') NOT NULL,
> `num_watchers` int(10) unsigned NOT NULL,
> PRIMARY KEY (`id`)
> )
>
> CREATE TABLE `issue_label` (
> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `created` datetime NOT NULL,
> `label_id` int(10) unsigned NOT NULL,
> `num_watchers` int(10) unsigned NOT NULL,
> PRIMARY KEY (`id`)
> )
>
> CREATE TABLE `issue_type_label` (
> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `created` datetime NOT NULL,
> `type` enum('Bug','Feature','Improvement','One-Time Task') NOT NULL,
> `label_id` int(10) unsigned NOT NULL,
> `num_visits` int(10) unsigned NOT NULL,
> PRIMARY KEY (`id`)
> )
>
> So we'd run these insert statements at midnight:
>
> INSERT INTO issue_type (created, type, num_visits) VALUES
> ("2012-11-15", "Bug", 301),
> ("2012-11-15", "One-Time Task", 20);
>
> INSERT INTO issue_labels (created, label_id, num_visits) VALUES
> ("2012-11-15", 1, 301),
> ("2012-11-15", 2, 21),
> ("2012-11-15", 3, 20);
>
> INSERT INTO issue_type_label (created, type, label_id, num_visits)
> VALUES
> ("2012-11-15", "Bug", 1, 301),
> ("2012-11-15", "Bug", 2, 1),
> ("2012-11-15", "One-Time Task", 3, 20),
> ("2012-11-15", "One-Time Task", 2, 20);
>
> Now when we want to generate the summary reports, we query one of the
> first two tables (if we're generating a report aggregated by issue
> type we hit issue_type, if we're generating a report aggregated by
> label we hit issue_label), and when we want to run a drill-down query
> both both type and label, we hit issue_type_label:
>
> # broken down by type
> SELECT type, SUM(num_visits) FROM issue_type WHERE created >=
> "2012-11-01" AND created <= "2012-11-16" GROUP BY type;
>
> # broken down by label
> SELECT label_id, SUM(num_visits) FROM issue_label WHERE created >=
> "2012-11-01" AND created <= "2012-11-16" GROUP BY label_id;
>
> # broken down by both type and label
> SELECT type, label_id, SUM(num_visits) FROM issue_type_label WHERE
> created >= "2012-11-01" AND created <= "2012-11-16" GROUP BY type,
> label_id;
>
> This works fine and is very fast for our application. However, it's a
> bit redundant since each issue has exactly one issue type, so we can
> safely ignore it when generating the label summary report. So really,
> we can get away with not having the issue_label table at all, and just
> run this instead (we must continue to have the issue_type table
> however, since a single issue can have many labels or none at all):
>
> # broken down by label
> SELECT label_id, SUM(num_visits) FROM issue_type_label WHERE
> created >= "2012-11-01" AND created <= "2012-11-16" GROUP BY label_id;
>
>
> THE ISSUE
> ---------
>
> In reality, we have many types of fields that we generate reports on
> (not just type and label), so we have more than a dozen of these
> aggregate tables rolled up by different sets of fields. While porting
> our data access layer to SQLAlchemy (we have a separate process to
> write these tables which we aren't going to move to SQLAlchemy), I've
> created a bunch of functions that use reflection on the various Model
> objects I've built to figure out what columns we need to select.
>
> Each column that we don't want to GROUP BY or filter out with a WHERE
> clause gets wrapped by func.sum() and passed to a call to
> session.query(). My columns are very consistently named so I have
> automated almost all of this using object reflection, which lets my
> code populate which columns to select, group by, and sum on
> automatically. I really don't want to write a custom call to query()
> for each table type that we have since we have so many and they are
> accessed in basically identical ways.
>
> The issue, however, comes when trying to generate the report bucketed
> by label. Since the issue_type_label table has both type and label_id
> columns, my code will always GROUP BY both of these columns unless I
> introduce various hacks to suppress selecting and grouping by these
> columns all over the place...
without having a deep understanding of your system and only time for a cursory
review of this level of detail, it seems like you just need some system of
generating a Query(), or even just a select(), programatically. You've got
naming schemes and consistent inputs/outputs, I would not even use ORM mappings
for this beyond just simple, traditional maps to each table.
> What I want to be able to do is to define 2 models that are both
> backed by the same table, but have declared different sets of Columns
> so that the reflection code I've written doesn't have to be hacked to
> shreds to exclude the type column when I don't want it;
this is likely a mis-use of ORM mappings. an ORM mapping is generally one
model for one Table, and that's it, with some very occasional use cases where
you might be selecting from more than one table at once.
If you want to select subsets of columns to select from, that's what
session.query() or select() does.
You need to write code that does this:
def my_model_one():
return query(*getattr(MyModel, colname) for colname in
get_colnames_for_model_one())
def my_model_two():
return query(*getattr(MyModel, colname) for colname in
get_colnames_for_model_two())
I wouldn't be trying to define ORM mappings that define particular subqueries,
basically.
> I will simply
> define one model that doesn't create a Column for type and the
> reflection code will ignore it when generating queries. So this is
> what I want to write, but it generates the InvalidRequestError:
>
> from sqlalchemy import Column, Date, Enum, Integer, String,
> create_engine, desc, func
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker
>
> engine = create_engine('mysql://root:password@localhost/
> issue_tracker')
> Session = sessionmaker(bind=engine)
>
> Base = declarative_base()
>
> class IssueType(Base):
> __tablename__ = 'issue_type'
> id = Column('id', Integer, primary_key=True)
> type = Column('type', Enum('Bug', 'Feature', 'Improvement', 'One-
> Time Task'))
> created = Column('created', Date)
> num_visits = Column('num_visits', Integer)
>
> class IssueLabel(Base):
> __tablename__ = 'issue_type_label'
> id = Column('id', Integer, primary_key=True)
> label_id = Column('label_id', String)
> created = Column('created', Date)
> num_visits = Column('num_visits', Integer)
>
> class IssueTypeLabel(Base):
> __tablename__ = 'issue_type_label'
> id = Column('id', Integer, primary_key=True)
> type = Column('type', Enum('Bug', 'Feature', 'Improvement', 'One-
> Time Task'))
> label_id = Column('label_id', String)
> created = Column('created', Date)
> num_visits = Column('num_visits', Integer)
>
>
> Correct me if I'm wrong, but I believe the methods you described above
> will all inherit the same column definitions for the table. However,
> that's exactly what I don't want to happen! I poked around in the
> source and noticed there is a table argument called keep_existing
> which I tried using, but its behavior seemed inconsistent and/or buggy
> with respect to what columns were reported via the reflection I was
> doing. Maybe this isn't the use case it was intended for?
the MetaData object is intended to store collections of Table objects, keyed to
their name.
If you wanted to have two ORM mappings, each one mapped to a subset of columns
on one Table, you need to map to a Table object:
t = Table('mytable', metadata,
Column('x', ...),
Column('y', ...),
Column('z', ...),
Column('q', ...),
Column('p', ...),
Column('r', ...)
)
class ClassOne(Base):
__table__ = t
__mapper_args__ = {'include_columns':['x', 'y', 'z'])
class ClassTwo(Base):
__table__ = t
__mapper_args__ = {'include_columns':['q', 'p', 'r'])
Check the docs for mapper() for more detail on include_columns, I'm working
from memory on how it works (I've never needed it).
http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html?highlight=mapper#sqlalchemy.orm.mapper
>
> Its possible that I can declare 2 different Base classes with two
> separate calls to declarative_base(),
not sure why you need two Base classes. I'd be looking to keep the usage of
the ORM traditional and simple. If you're doing reporting and need different
reports to generate different sets of columns, I'd be working with Query() or
select() directly to pass in those lists, and backing it would be custom code
on your end that keeps it organized somehow.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
