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 mike...@zzzcomputing.comwrote:
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.
[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.
Re: [sqlalchemy] Mapping a CTE
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.
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 mike...@zzzcomputing.comwrote:
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.
Re: [sqlalchemy] Mapping a CTE
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 mike...@zzzcomputing.com
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
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 mike...@zzzcomputing.comwrote:
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
mike...@zzzcomputing.comwrote:
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)
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
marc.dellavo...@gmail.comwrote:
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
mike...@zzzcomputing.comwrote:
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 mike...@zzzcomputing.com
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
Re: [sqlalchemy] Mapping a CTE
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 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.
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 mike...@zzzcomputing.comwrote: 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 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.
