Re: [sqlalchemy] Mapping a CTE
I am interested in any feedback on the code at
https://gist.github.com/1156683 . The code works for me but it's a hacked
solution and I'm not sure if this will work generally. I am not sure if
there is a better way to handle gather the CTE's, I simply tacked the found
CTE onto the compiler object. Also this should support multiple CTE's.
print Date.range('2011-01-01 00:00:00', '2012-02-01
00:00:00').filter("date>='2011-01-15 00:00:00'").order_by(Date.date.desc())
WITH all_dates(date) AS (
SELECT CAST(? AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd, ?, date)
AS [DATEADD_1]
FROM all_dates
WHERE DATEADD(dd, ?, date) <= CAST(? AS DATETIME)
)
SELECT date AS date
FROM (SELECT * FROM all_dates) as x
WHERE date>='2011-01-15 00:00:00' ORDER BY date DESC
OPTION (MAXRECURSION 0)
On Thu, Aug 18, 2011 at 2:20 PM, Michael Bayer wrote:
>
> On Aug 18, 2011, at 2:01 PM, Marc DellaVolpe wrote:
>
> I understand what you are saying about this being an uphill battle on
> mssql.
>
> If we ignore the CTE for the moment and pretend that all_dates is a regular
> table, shouldn't there be no difference for generation. You can subquery
> as needed and all that really needs to happen is to prepend the CTE to the
> rest of the SQL before execution. Maybe this approach
> isn't generally applicable, CTE's already make my head hurt :)
>
>
> yeah that could work as well but its not clear to me if the CTE can be up
> above everything, then referenced within a nested structure like that.I
> haven't really worked with CTEs to have that level of experience.
>
>
> Searching the mailing list found a thread (
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f380e277af/fb179a515bf48868)
> where you used a regular expression to shim in a special comment. This
> seems hackish but could a similar approach work on mssql? Is it possible to
> hook into only the top-level/last Select's compile()?
>
>
> if it works, then yes you'd get your Select compiler to dig in, look for
> CTEs, put the WITH all the way on top, assuming the Select is the
> "outermost". You can tell if you're the "outermost" select by checking
> "bool(compiler.stack)", which is a stack of SELECT constructs.
>
>
> --
> 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.
>
--
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.
Re: [sqlalchemy] Mapping a CTE
I understand what you are saying about this being an uphill battle on mssql. If we ignore the CTE for the moment and pretend that all_dates is a regular table, shouldn't there be no difference for generation. You can subquery as needed and all that really needs to happen is to prepend the CTE to the rest of the SQL before execution. Maybe this approach isn't generally applicable, CTE's already make my head hurt :) Searching the mailing list found a thread ( http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f380e277af/fb179a515bf48868) where you used a regular expression to shim in a special comment. This seems hackish but could a similar approach work on mssql? Is it possible to hook into only the top-level/last Select's compile()? Thanks for all of your help. -Marc On Thu, Aug 18, 2011 at 1:37 PM, Michael Bayer wrote: > > On Aug 18, 2011, at 1:28 PM, Marc DellaVolpe wrote: > > > There seems to be a problem with the custom compilation. > > > > >>> print Session.query(Date).order_by(Date.date.desc()) > > > > WITH all_dates(date) AS ( > > SELECT CAST(:start AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd, > :step, date) AS "DATEADD_1" > > FROM all_dates > > WHERE DATEADD(dd, :step, date) <= CAST(:stop AS DATETIME) > > ) > > SELECT * FROM all_dates > > OPTION (MAXRECURSION 0) > > Heh. This is what I was saying - Query is going to produce "SELECT x, y, z > FROM (your custom statement) ORDER BY ".You blow away that > SELECT on the outside you lose the ORDER BY also and anything else.The > only path here would involve a much more concerted effort with the > overriding of the Select construct to pull off everything it does and apply > it to the thing that's being wrapped.Each new thing you'd like to do, > like JOIN to it, etc., means your magic compiler would need to work it out. > > The key issue here is that SQL Server doesn't supply a generically useful > form of CTE since it cannot be nested. Very uphill. > > > -- > 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. > > -- 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.
Re: [sqlalchemy] Mapping a CTE
There seems to be a problem with the custom compilation.
>>> print Session.query(Date).order_by(Date.date.desc())
WITH all_dates(date) AS (
SELECT CAST(:start AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd,
:step, date) AS "DATEADD_1"
FROM all_dates
WHERE DATEADD(dd, :step, date) <= CAST(:stop AS DATETIME)
)
SELECT * FROM all_dates
OPTION (MAXRECURSION 0)
On Thu, Aug 18, 2011 at 1:08 PM, Marc DellaVolpe
wrote:
> From what I can tell from
> http://msdn.microsoft.com/en-us/library/ms190766.aspx and experimentally,
> you can only put CTE's at the top:
>
> -- Works
> WITH all_dates(date) AS (
> SELECT CAST('2011-01-01' AS DATETIME) AS anon_1 UNION ALL SELECT
> DATEADD(dd, 1, date) AS "DATEADD_1"
> FROM all_dates
> WHERE DATEADD(dd, 1, date) <= CAST('2012-01-01' AS DATETIME)
> )
> select * from (SELECT * FROM all_dates) as x
> OPTION (MAXRECURSION 0)
>
> -- Generates an invalid syntax error
> select * from (
> WITH all_dates(date) AS (
> SELECT CAST('2011-01-01' AS DATETIME) AS anon_1 UNION ALL SELECT
> DATEADD(dd, 1, date) AS "DATEADD_1"
> FROM all_dates
> WHERE DATEADD(dd, 1, date) <= CAST('2012-01-01' AS DATETIME)
> )
> ) as x
> OPTION (MAXRECURSION 0)
>
>
> Thank you for the solution. One approach I was considering was subclassing
> Select, adding a method to attach a CTE to the select and then generating
> custom SQL for the subclass but I wasn't completely familiar of the
> compilation workings to determine if this was workable solution to force the
> CTE to the top of the generated SQL.
>
> -Marc
>
> On Thu, Aug 18, 2011 at 12:13 PM, Michael Bayer
> wrote:
>
>>
>> On Aug 18, 2011, at 11:56 AM, Marc DellaVolpe wrote:
>>
>> I should have mentioned, I modified the CTE demo to work on SQL Server and
>> I believe (I will double check this...) that on SQL Server the "with" of the
>> CTE needs to be at the top of the statement and referenced in subqueries
>> below. The generated SQL "SELECT FROM (WITH ...)" is invalid on SQL
>> Server.
>>
>>
>> Just to confirm, the "WITH RECURSIVE" can never be nested inside of any
>> kind of subquery with SQL Server (which would not be surprising given SQL
>> Servers standard MO), is that correct ?
>>
>> That blows away a large amount of "generations" right there with Query
>> since subqueries are a core part of its operation.
>>
>> As the ticket states, the CTE logic would probably need to be inside of
>> the compilation of Select itself.
>>
>> The mapper itself only knows how to select "columns" from a selectable
>> given, such as a table or other SELECT statement. So if "SELECT
>> myexpr.date FROM (WITH RECURSIVE)" is impossible, either you have to stick
>> to using your from_statement() approach, or you'd need to modify the
>> compilation of Select() such that it generates *nothing* if the thing being
>> selected from is a CTE, which is quite awkward, surprising, and I can't see
>> us ever having it do that by default, but here's that:
>>
>>
>> from sqlalchemy.sql.expression import Select
>>
>> @compiles(Select)
>> def _dont_render_outside_of_cte(element, compiler, **kw):
>> if element._froms:
>> expr = element._froms[0]
>> else:
>> expr = None
>> if isinstance(expr, SelectFromCTE):
>> return compiler.process(expr, **kw)
>> else:
>> return compiler.visit_select(element, **kw)
>>
>> the mapping + query:
>>
>> class Date(Base):
>> __table__ = all_dates
>> __mapper_args__ = {'primary_key':(all_dates.c.date)}
>>
>> @classmethod
>> def range(cls, start, stop, step=1):
>> return Session().query(Date).params(start=start, stop=stop,
>> step=step)
>>
>> Session = scoped_session(sessionmaker())
>> print Date.range('2011-01-01 00:00:00', '2021-01-01 00:00:00')
>>
>> produces:
>>
>> WITH RECURSIVE all_dates(date) AS
>> SELECT CAST(:start AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd,
>> :step, all_dates.date) AS "DATEADD_1"
>> FROM all_dates
>> WHERE DATEADD(dd, :step, all_dates.date) <= CAST(:stop AS DATETIME)
>>
>> SELECT * FROM all_dates
>>
>>
>>
>>
>>
>>
>> The goal is to render SQL similar to:
>>
>> WITH all_dates(date) AS (
>> SELECT CAST(:start AS DATETIME) AS anon_1 UNION ALL SELECT
Re: [sqlalchemy] Mapping a CTE
>From what I can tell from
http://msdn.microsoft.com/en-us/library/ms190766.aspx and experimentally,
you can only put CTE's at the top:
-- Works
WITH all_dates(date) AS (
SELECT CAST('2011-01-01' AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd,
1, date) AS "DATEADD_1"
FROM all_dates
WHERE DATEADD(dd, 1, date) <= CAST('2012-01-01' AS DATETIME)
)
select * from (SELECT * FROM all_dates) as x
OPTION (MAXRECURSION 0)
-- Generates an invalid syntax error
select * from (
WITH all_dates(date) AS (
SELECT CAST('2011-01-01' AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd,
1, date) AS "DATEADD_1"
FROM all_dates
WHERE DATEADD(dd, 1, date) <= CAST('2012-01-01' AS DATETIME)
)
) as x
OPTION (MAXRECURSION 0)
Thank you for the solution. One approach I was considering was subclassing
Select, adding a method to attach a CTE to the select and then generating
custom SQL for the subclass but I wasn't completely familiar of the
compilation workings to determine if this was workable solution to force the
CTE to the top of the generated SQL.
-Marc
On Thu, Aug 18, 2011 at 12:13 PM, Michael Bayer wrote:
>
> On Aug 18, 2011, at 11:56 AM, Marc DellaVolpe wrote:
>
> I should have mentioned, I modified the CTE demo to work on SQL Server and
> I believe (I will double check this...) that on SQL Server the "with" of the
> CTE needs to be at the top of the statement and referenced in subqueries
> below. The generated SQL "SELECT FROM (WITH ...)" is invalid on SQL
> Server.
>
>
> Just to confirm, the "WITH RECURSIVE" can never be nested inside of any
> kind of subquery with SQL Server (which would not be surprising given SQL
> Servers standard MO), is that correct ?
>
> That blows away a large amount of "generations" right there with Query
> since subqueries are a core part of its operation.
>
> As the ticket states, the CTE logic would probably need to be inside of the
> compilation of Select itself.
>
> The mapper itself only knows how to select "columns" from a selectable
> given, such as a table or other SELECT statement. So if "SELECT
> myexpr.date FROM (WITH RECURSIVE)" is impossible, either you have to stick
> to using your from_statement() approach, or you'd need to modify the
> compilation of Select() such that it generates *nothing* if the thing being
> selected from is a CTE, which is quite awkward, surprising, and I can't see
> us ever having it do that by default, but here's that:
>
>
> from sqlalchemy.sql.expression import Select
>
> @compiles(Select)
> def _dont_render_outside_of_cte(element, compiler, **kw):
> if element._froms:
> expr = element._froms[0]
> else:
> expr = None
> if isinstance(expr, SelectFromCTE):
> return compiler.process(expr, **kw)
> else:
> return compiler.visit_select(element, **kw)
>
> the mapping + query:
>
> class Date(Base):
> __table__ = all_dates
> __mapper_args__ = {'primary_key':(all_dates.c.date)}
>
> @classmethod
> def range(cls, start, stop, step=1):
> return Session().query(Date).params(start=start, stop=stop,
> step=step)
>
> Session = scoped_session(sessionmaker())
> print Date.range('2011-01-01 00:00:00', '2021-01-01 00:00:00')
>
> produces:
>
> WITH RECURSIVE all_dates(date) AS
> SELECT CAST(:start AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd,
> :step, all_dates.date) AS "DATEADD_1"
> FROM all_dates
> WHERE DATEADD(dd, :step, all_dates.date) <= CAST(:stop AS DATETIME)
>
> SELECT * FROM all_dates
>
>
>
>
>
>
> The goal is to render SQL similar to:
>
> WITH all_dates(date) AS (
> SELECT CAST(:start AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd,
> :step, date) AS "DATEADD_1"
> FROM all_dates
> WHERE DATEADD(dd, :step, date) <= CAST(:stop AS DATETIME)
> )
> SELECT * FROM all_dates
> OPTION (MAXRECURSION 0)
>
> In order for this to work on SQL Server as a mapped class I believe it
> would need to be rendered similar to:
>
> WITH RECURSIVE all_dates(date) AS
> (SELECT CAST(%(start)s AS TIMESTAMP WITHOUT TIME ZONE) AS anon_2 UNION ALL
> SELECT DATEADD(dd, %(step)s, all_dates.date) AS "DATEADD_1"
> FROM all_dates
> WHERE DATEADD(dd, %(step)s, all_dates.date) <= CAST(%(stop)s AS TIMESTAMP
> WITHOUT TIME ZONE))
> SELECT anon_1.date AS anon_1_date
> FROM (SELECT * FROM all_dates) AS anon_1
>
> -Marc
>
> On Thu, Aug 18, 2011 at 11:47 AM, Michael Bayer
> wrote:
>
>>
>> On Aug 18, 2011, at 11:23 AM, Marc DellaVolpe wrote:
>>
Re: [sqlalchemy] Mapping a CTE
I should have mentioned, I modified the CTE demo to work on SQL Server and I
believe (I will double check this...) that on SQL Server the "with" of the
CTE needs to be at the top of the statement and referenced in subqueries
below. The generated SQL "SELECT FROM (WITH ...)" is invalid on SQL
Server.
The goal is to render SQL similar to:
WITH all_dates(date) AS (
SELECT CAST(:start AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd,
:step, date) AS "DATEADD_1"
FROM all_dates
WHERE DATEADD(dd, :step, date) <= CAST(:stop AS DATETIME)
)
SELECT * FROM all_dates
OPTION (MAXRECURSION 0)
In order for this to work on SQL Server as a mapped class I believe it would
need to be rendered similar to:
WITH RECURSIVE all_dates(date) AS
(SELECT CAST(%(start)s AS TIMESTAMP WITHOUT TIME ZONE) AS anon_2 UNION ALL
SELECT DATEADD(dd, %(step)s, all_dates.date) AS "DATEADD_1"
FROM all_dates
WHERE DATEADD(dd, %(step)s, all_dates.date) <= CAST(%(stop)s AS TIMESTAMP
WITHOUT TIME ZONE))
SELECT anon_1.date AS anon_1_date
FROM (SELECT * FROM all_dates) AS anon_1
-Marc
On Thu, Aug 18, 2011 at 11:47 AM, Michael Bayer wrote:
>
> On Aug 18, 2011, at 11:23 AM, Marc DellaVolpe wrote:
>
> > I found the following CTE demo (http://www.sqlalchemy.org/trac/
> > attachment/ticket/1859/cte_demo.py) and I was wondering if there was
> > any way to map these selects.
> >
> > I have built a CTE based select to generate a dates table on the fly
> > and I would love to be able to map this and use generative selects to
> > transform queries. I have only been able to make the mapping work
> > with .from_statement() however this does not allow for
> > transformations.
> >
> > Any thoughts?
> >
> > Thanks,
> > -Marc
> >
> > with CommonTableExpression.create('all_dates', ['date']) as all_dates:
> >
> >start_exp = cast(bindparam('start'), DateTime)
> >end_exp = cast(bindparam('stop'), DateTime)
> >
> >exp = func.DATEADD( literal_column('dd'), bindparam('step'),
> > all_dates.c.date )
> >
> >s1 = select([start_exp])
> >s2 = select([exp], from_obj=all_dates).where(exp <= end_exp)
> >
> >s = s1.union_all(s2)
> >
> > all_dates = SelectFromCTE(all_dates, s)
> >
> > class Date(object):
> >query = Session.query_property()
> >
> >@classmethod
> >def range(cls, start, stop, step=1):
> >return
> > cls.query.from_statement(str(all_dates)).params(start=start,
> > stop=stop, step=step)
> >
> > mapper(Date, all_dates, primary_key=[all_dates.c.date])
> >
> > print Date.range('2011-01-01 00:00:00', '2021-01-01 00:00:00').all()
>
> this maps fine for me (it's best to apply alias() to all_dates before
> mapping), I just get a statement that doesn't work:
>
> SELECT anon_1.date AS anon_1_date
> FROM (WITH RECURSIVE all_dates(date) AS
> (SELECT CAST(%(start)s AS TIMESTAMP WITHOUT TIME ZONE) AS anon_2 UNION ALL
> SELECT DATEADD(dd, %(step)s, all_dates.date) AS "DATEADD_1"
> FROM all_dates
> WHERE DATEADD(dd, %(step)s, all_dates.date) <= CAST(%(stop)s AS TIMESTAMP
> WITHOUT TIME ZONE))
>
> SELECT * FROM all_dates) AS anon_1
>
>
> (ProgrammingError) column "dd" does not exist
>
> what should "dd" be here ?
>
>
>
>
>
>
>
> >
> >
> > --
> > 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.
> >
>
> --
> 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.
>
>
--
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.
[sqlalchemy] Mapping a CTE
I found the following CTE demo (http://www.sqlalchemy.org/trac/
attachment/ticket/1859/cte_demo.py) and I was wondering if there was
any way to map these selects.
I have built a CTE based select to generate a dates table on the fly
and I would love to be able to map this and use generative selects to
transform queries. I have only been able to make the mapping work
with .from_statement() however this does not allow for
transformations.
Any thoughts?
Thanks,
-Marc
with CommonTableExpression.create('all_dates', ['date']) as all_dates:
start_exp = cast(bindparam('start'), DateTime)
end_exp = cast(bindparam('stop'), DateTime)
exp = func.DATEADD( literal_column('dd'), bindparam('step'),
all_dates.c.date )
s1 = select([start_exp])
s2 = select([exp], from_obj=all_dates).where(exp <= end_exp)
s = s1.union_all(s2)
all_dates = SelectFromCTE(all_dates, s)
class Date(object):
query = Session.query_property()
@classmethod
def range(cls, start, stop, step=1):
return
cls.query.from_statement(str(all_dates)).params(start=start,
stop=stop, step=step)
mapper(Date, all_dates, primary_key=[all_dates.c.date])
print Date.range('2011-01-01 00:00:00', '2021-01-01 00:00:00').all()
--
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.
