[sqlalchemy] Re: Weird error using SQLAlchemy 0.7.2, MySQL, Python3, SqlSoup and relate
If anybody else is experience the same problem, I have opened a Bug
Request @ SQLA Trac, you can follow it through here:
http://www.sqlalchemy.org/trac/ticket/2260
On Aug 18, 1:56 am, Ygor Lemos opti...@gmail.com wrote:
Oh, sorry about that, I copied from a previous declaration I've been
testing using Table() objects... I did remove the ,'s and all worked
fine... The relationships are normal both in py3k and py2 with the
latest SQLA. So the problem really lies on the relate() method of
SqlSoup.
Thanks again for your time.
On Aug 18, 1:25 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 17, 2011, at 10:15 PM, Ygor Lemos wrote:
I tried the following for manually mapping the tables:
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy import dialects
from sqlalchemy import sql
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import *
engine = create_engine(mysql+oursql://:XXX@XX/
XXX?charset=utf8use_unicode=Trueautoping=True, echo=True)
metadata = MetaData(engine)
Base = declarative_base()
class User(Base):
__tablename__ = users
id = Column(Integer, primary_key=True),
login = Column(String(25)),
name = Column(String(50)),
passwd = Column(String(100)),
email = Column(String(100)),
atype = Column(String(50)),
active = Column(Boolean),
customers_id = Column('customers_id', Integer,
ForeignKey('customers.id')),
all of those commas at the end of each line results in the class having a
tuple called id in it, rather than a set of attributes id, login,
name etc which declarative can interpret as mapping directives.
--
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] Re: Weird error using SQLAlchemy 0.7.2, MySQL, Python3, SqlSoup and relate
If anybody else is experiencing this same problem, I have opened a Bug
Request @ SQLA Trac and you can follow it through here:
http://www.sqlalchemy.org/trac/ticket/2260
On Aug 18, 1:56 am, Ygor Lemos opti...@gmail.com wrote:
Oh, sorry about that, I copied from a previous declaration I've been
testing using Table() objects... I did remove the ,'s and all worked
fine... The relationships are normal both in py3k and py2 with the
latest SQLA. So the problem really lies on the relate() method of
SqlSoup.
Thanks again for your time.
On Aug 18, 1:25 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 17, 2011, at 10:15 PM, Ygor Lemos wrote:
I tried the following for manually mapping the tables:
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy import dialects
from sqlalchemy import sql
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import *
engine = create_engine(mysql+oursql://:XXX@XX/
XXX?charset=utf8use_unicode=Trueautoping=True, echo=True)
metadata = MetaData(engine)
Base = declarative_base()
class User(Base):
__tablename__ = users
id = Column(Integer, primary_key=True),
login = Column(String(25)),
name = Column(String(50)),
passwd = Column(String(100)),
email = Column(String(100)),
atype = Column(String(50)),
active = Column(Boolean),
customers_id = Column('customers_id', Integer,
ForeignKey('customers.id')),
all of those commas at the end of each line results in the class having a
tuple called id in it, rather than a set of attributes id, login,
name etc which declarative can interpret as mapping directives.
--
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] event.remove failure
Hi, I started to play with events to port a library to sqla 0.7. I managed to use the 'listen' function but I failed on 'remove'. Looking at the signatures they seem to be just the same, but here is what I get: In [7]: event.listen(obj.__class__.title, 'set', listen_cb) In [8]: event.remove(obj.__class__.title, 'set', listen_cb) --- TypeError Traceback (most recent call last) /home/misc/src/hg/py/sqlkit-pub/demo/sql/demo.py in module() 1 2 3 4 5 /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/event.pyc in remove(target, identifie 69 70 for evt_cls in _registrars[identifier]: --- 71 for tgt in evt_cls._accept_with(target): 72 tgt.dispatch._remove(identifier, tgt, fn, *args, **kw) 73 return TypeError: 'InstrumentedAttribute' object is not iterable Did I misundertand the syntax or what else? TIA sandro *:-) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/SqqNPsbu8DsJ. 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] event.remove failure
remove() isn't implemented yet.While the simple operation you see below
would be fine for a single listener on a single target, the targets we have
which propagate to subclasses (mapper events, attribute events) would require a
more elaborate system that can revisit everywhere the event has been propagated
and remove it from there as well.
this is why remove isn't published in the docs right now.
In our own tests I use a hack to remove an entire set of listeners at once, if
this is a testing teardown scenario you're dealing with.
Otherwise, ad-hoc removal on a per operation basis ? I knew someone would
try it though damned if I could imagine what possible use there could be for
that. If this is the case here, care to entertain me ?
On Aug 18, 2011, at 10:10 AM, sandro dentella wrote:
Hi,
I started to play with events to port a library to sqla 0.7. I managed to use
the 'listen' function but I failed on 'remove'. Looking at the signatures
they seem to be just the same, but here is what I get:
In [7]: event.listen(obj.__class__.title, 'set', listen_cb)
In [8]: event.remove(obj.__class__.title, 'set', listen_cb)
---
TypeError Traceback (most recent call last)
/home/misc/src/hg/py/sqlkit-pub/demo/sql/demo.py in module()
1
2
3
4
5
/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/event.pyc in remove(target,
identifie
69
70 for evt_cls in _registrars[identifier]:
--- 71 for tgt in evt_cls._accept_with(target):
72 tgt.dispatch._remove(identifier, tgt, fn, *args, **kw)
73 return
TypeError: 'InstrumentedAttribute' object is not iterable
Did I misundertand the syntax or what else?
TIA
sandro
*:-)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/SqqNPsbu8DsJ.
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
[sqlalchemy] InsertFromSelect on SQL server
Hi everyone, I'm trying to implement an InsertFromSelect workaround to handle the identity insert issue of SQL server. This is more or less what I'm doing: class InsertFromSelect(Executable, ClauseElement) : def __init__(self, table, select) : self.table = table self.select = select @compiler.compiles(InsertFromSelect, mssql) def visit_insert_from_select(element, compiler, **kw) : tab_name = compiler.process(element.table, asfrom=True) q = SET IDENTITY_INSERT %s ON; %(tab_name) q += INSERT INTO %s (%s) %s; % (tab_name, ', '.join([compiler.process(c) for c in element.table.c]), compiler.process(element.select)) q += SET IDENTITY_INSERT %s OFF; %(tab_name) return q insert_from_select = InsertFromSelect(new_table, old_table)) engine.execute(insert_from_select) The code is executed without error, but 'new_table' is not filled. Important: in my code I cannot use the session to execute the query. Can anyone point me out what I'm doing wrong? Thanks! -- 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] InsertFromSelect on SQL server
On Aug 18, 2011, at 12:24 PM, Massi wrote:
Hi everyone, I'm trying to implement an InsertFromSelect workaround to
handle the identity insert issue of SQL server. This is more or less
what I'm doing:
class InsertFromSelect(Executable, ClauseElement) :
def __init__(self, table, select) :
self.table = table
self.select = select
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
tab_name = compiler.process(element.table, asfrom=True)
q = SET IDENTITY_INSERT %s ON; %(tab_name)
q += INSERT INTO %s (%s) %s; % (tab_name,
', '.join([compiler.process(c) for c in element.table.c]),
compiler.process(element.select))
q += SET IDENTITY_INSERT %s OFF; %(tab_name)
return q
it would be better to use the built in IDENTITY_INSERT facilities of the MSSQL
dialect itself.
Set the isinsert flag on your compiled object, and also set a new flag I can
add called _mssql_requires_identity_insert:
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
compiler.isinsert = True
compiler._mssql_requires_identity_insert = True
patch for testing:
diff -r 44e239751af8 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Wed Aug 17 14:55:21 2011 -0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 12:32:22 2011 -0400
@@ -655,7 +655,9 @@
seq_column = tbl._autoincrement_column
insert_has_sequence = seq_column is not None
-if insert_has_sequence:
+if getattr(self.compiled._mssql_requires_identity_insert, False):
+self._enable_identity_insert = True
+elif insert_has_sequence:
self._enable_identity_insert = \
seq_column.key in self.compiled_parameters[0]
else:
if this works for you I can commit it with a short test and it will be in 0.7.3.
insert_from_select = InsertFromSelect(new_table, old_table))
engine.execute(insert_from_select)
easier issue here, your construct also needs autocommit enabled if you are
going to use connectionless execution:
class InsertFromSelect(...):
_execution_options = \
Executable._execution_options.union({'autocommit': True})
or better yet just subclass UpdateBase instead of (Executable, ClauseElement).
will add a doc
--
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] Re: InsertFromSelect on SQL server
I changed the code to (SA 0.7.2 with pyodbc2.1.9):
class InsertFromSelect(Executable, ClauseElement) :
_execution_options =\
Executable._execution_options.union({'autocommit': True})
def __init__(self, table, select) :
self.table = table
self.select = select
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
compiler.isinsert = True
compiler._mssql_requires_identity_insert = True
tab_name = compiler.process(element.table, asfrom=True)
q = INSERT INTO %s (%s) %s; % (tab_name,
', '.join([compiler.process(c) for c in element.table.c]),
compiler.process(element.select))
return q
applied the patch and I got the following error:
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 2285, in execute
return connection.execute(statement, *multiparams, **params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1399, in execute
params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1599, in _execute_context
None, None)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1595, in _execute_context
context = constructor(dialect, self, conn, *args)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\default.py, line 418, in _init_compiled
self._is_explicit_returning = compiled.statement._returning
StatementError: 'InsertFromSelect' object has no attribute
'_returning' (original cause: AttributeError: 'InsertFromSelect'
object has noattribute '_returning') 'INSERT INTO...'
On 18 Ago, 18:34, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 18, 2011, at 12:24 PM, Massi wrote:
Hi everyone, I'm trying to implement an InsertFromSelect workaround to
handle the identity insert issue of SQL server. This is more or less
what I'm doing:
class InsertFromSelect(Executable, ClauseElement) :
def __init__(self, table, select) :
self.table = table
self.select = select
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
tab_name = compiler.process(element.table, asfrom=True)
q = SET IDENTITY_INSERT %s ON; %(tab_name)
q += INSERT INTO %s (%s) %s; % (tab_name,
', '.join([compiler.process(c) for c in element.table.c]),
compiler.process(element.select))
q += SET IDENTITY_INSERT %s OFF; %(tab_name)
return q
it would be better to use the built in IDENTITY_INSERT facilities of the
MSSQL dialect itself.
Set the isinsert flag on your compiled object, and also set a new flag I
can add called _mssql_requires_identity_insert:
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
compiler.isinsert = True
compiler._mssql_requires_identity_insert = True
patch for testing:
diff -r 44e239751af8 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Wed Aug 17 14:55:21 2011 -0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 12:32:22 2011 -0400
@@ -655,7 +655,9 @@
seq_column = tbl._autoincrement_column
insert_has_sequence = seq_column is not None
- if insert_has_sequence:
+ if getattr(self.compiled._mssql_requires_identity_insert, False):
+ self._enable_identity_insert = True
+ elif insert_has_sequence:
self._enable_identity_insert = \
seq_column.key in self.compiled_parameters[0]
else:
if this works for you I can commit it with a short test and it will be in
0.7.3.
insert_from_select = InsertFromSelect(new_table, old_table))
engine.execute(insert_from_select)
easier issue here, your construct also needs autocommit enabled if you are
going to use connectionless execution:
class InsertFromSelect(...):
_execution_options = \
Executable._execution_options.union({'autocommit': True})
or better yet just subclass UpdateBase instead of (Executable, ClauseElement).
will add a doc
--
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
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] Re: InsertFromSelect on SQL server
On Aug 18, 2011, at 1:06 PM, Massi wrote:
I changed the code to (SA 0.7.2 with pyodbc2.1.9):
class InsertFromSelect(Executable, ClauseElement) :
_execution_options =\
Executable._execution_options.union({'autocommit': True})
def __init__(self, table, select) :
self.table = table
self.select = select
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
compiler.isinsert = True
compiler._mssql_requires_identity_insert = True
tab_name = compiler.process(element.table, asfrom=True)
q = INSERT INTO %s (%s) %s; % (tab_name,
', '.join([compiler.process(c) for c in element.table.c]),
compiler.process(element.select))
return q
applied the patch and I got the following error:
It's just checking if theres a RETURNING clause added, which your clause does
not yet support. Add _returning = False to your construct for now.
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 2285, in execute
return connection.execute(statement, *multiparams, **params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1399, in execute
params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1599, in _execute_context
None, None)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1595, in _execute_context
context = constructor(dialect, self, conn, *args)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\default.py, line 418, in _init_compiled
self._is_explicit_returning = compiled.statement._returning
StatementError: 'InsertFromSelect' object has no attribute
'_returning' (original cause: AttributeError: 'InsertFromSelect'
object has noattribute '_returning') 'INSERT INTO...'
On 18 Ago, 18:34, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 18, 2011, at 12:24 PM, Massi wrote:
Hi everyone, I'm trying to implement an InsertFromSelect workaround to
handle the identity insert issue of SQL server. This is more or less
what I'm doing:
class InsertFromSelect(Executable, ClauseElement) :
def __init__(self, table, select) :
self.table = table
self.select = select
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
tab_name = compiler.process(element.table, asfrom=True)
q = SET IDENTITY_INSERT %s ON; %(tab_name)
q += INSERT INTO %s (%s) %s; % (tab_name,
', '.join([compiler.process(c) for c in element.table.c]),
compiler.process(element.select))
q += SET IDENTITY_INSERT %s OFF; %(tab_name)
return q
it would be better to use the built in IDENTITY_INSERT facilities of the
MSSQL dialect itself.
Set the isinsert flag on your compiled object, and also set a new flag I
can add called _mssql_requires_identity_insert:
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
compiler.isinsert = True
compiler._mssql_requires_identity_insert = True
patch for testing:
diff -r 44e239751af8 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Wed Aug 17 14:55:21 2011
-0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 12:32:22 2011
-0400
@@ -655,7 +655,9 @@
seq_column = tbl._autoincrement_column
insert_has_sequence = seq_column is not None
-if insert_has_sequence:
+if getattr(self.compiled._mssql_requires_identity_insert,
False):
+self._enable_identity_insert = True
+elif insert_has_sequence:
self._enable_identity_insert = \
seq_column.key in self.compiled_parameters[0]
else:
if this works for you I can commit it with a short test and it will be in
0.7.3.
insert_from_select = InsertFromSelect(new_table, old_table))
engine.execute(insert_from_select)
easier issue here, your construct also needs autocommit enabled if you are
going to use connectionless execution:
class InsertFromSelect(...):
_execution_options = \
Executable._execution_options.union({'autocommit': True})
or better yet just subclass UpdateBase instead of (Executable,
ClauseElement).
will add a doc
--
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,
[sqlalchemy] Re: InsertFromSelect on SQL server
I got:
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 2285, in execute
return connection.execute(statement, *multiparams, **params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1399, in execute
params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1599, in _execute_context
None, None)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1595, in _execute_context
context = constructor(dialect, self, conn, *args)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\default.py, line 433, in _init_compiled
self.postfetch_cols = self.compiled.postfetch
StatementError: 'MSSQLCompiler' object has no attribute
'postfetch' (original cause: AttributeError: 'MSSQLCompiler' object
has no attribute 'postfetch') 'INSERT INTO...'
On 18 Ago, 19:08, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 18, 2011, at 1:06 PM, Massi wrote:
I changed the code to (SA 0.7.2 with pyodbc2.1.9):
class InsertFromSelect(Executable, ClauseElement) :
_execution_options =\
Executable._execution_options.union({'autocommit': True})
def __init__(self, table, select) :
self.table = table
self.select = select
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
compiler.isinsert = True
compiler._mssql_requires_identity_insert = True
tab_name = compiler.process(element.table, asfrom=True)
q = INSERT INTO %s (%s) %s; % (tab_name,
', '.join([compiler.process(c) for c in element.table.c]),
compiler.process(element.select))
return q
applied the patch and I got the following error:
It's just checking if theres a RETURNING clause added, which your clause does
not yet support. Add _returning = False to your construct for now.
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 2285, in execute
return connection.execute(statement, *multiparams, **params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1399, in execute
params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1599, in _execute_context
None, None)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1595, in _execute_context
context = constructor(dialect, self, conn, *args)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\default.py, line 418, in _init_compiled
self._is_explicit_returning = compiled.statement._returning
StatementError: 'InsertFromSelect' object has no attribute
'_returning' (original cause: AttributeError: 'InsertFromSelect'
object has no attribute '_returning') 'INSERT INTO...'
On 18 Ago, 18:34, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 18, 2011, at 12:24 PM, Massi wrote:
Hi everyone, I'm trying to implement an InsertFromSelect workaround to
handle the identity insert issue of SQL server. This is more or less
what I'm doing:
class InsertFromSelect(Executable, ClauseElement) :
def __init__(self, table, select) :
self.table = table
self.select = select
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
tab_name = compiler.process(element.table, asfrom=True)
q = SET IDENTITY_INSERT %s ON; %(tab_name)
q += INSERT INTO %s (%s) %s; % (tab_name,
', '.join([compiler.process(c) for c in element.table.c]),
compiler.process(element.select))
q += SET IDENTITY_INSERT %s OFF; %(tab_name)
return q
it would be better to use the built in IDENTITY_INSERT facilities of the
MSSQL dialect itself.
Set the isinsert flag on your compiled object, and also set a new flag I
can add called _mssql_requires_identity_insert:
@compiler.compiles(InsertFromSelect, mssql)
def visit_insert_from_select(element, compiler, **kw) :
compiler.isinsert = True
compiler._mssql_requires_identity_insert = True
patch for testing:
diff -r 44e239751af8 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Wed Aug 17 14:55:21 2011
-0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 12:32:22 2011
-0400
@@ -655,7 +655,9
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] Re: InsertFromSelect on SQL server
On Aug 18, 2011, at 1:19 PM, Massi wrote:
I got:
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 2285, in execute
return connection.execute(statement, *multiparams, **params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1399, in execute
params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1599, in _execute_context
None, None)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1595, in _execute_context
context = constructor(dialect, self, conn, *args)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\default.py, line 433, in _init_compiled
self.postfetch_cols = self.compiled.postfetch
StatementError: 'MSSQLCompiler' object has no attribute
'postfetch' (original cause: AttributeError: 'MSSQLCompiler' object
has no attribute 'postfetch') 'INSERT INTO...'
here's another patch that should default all that stuff appropriately:
diff -r 113a7ed95335 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:03:30 2011 -0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:31:16 2011 -0400
@@ -655,7 +655,9 @@
seq_column = tbl._autoincrement_column
insert_has_sequence = seq_column is not None
-if insert_has_sequence:
+if getattr(self.compiled._mssql_requires_identity_insert, False):
+self._enable_identity_insert = True
+elif insert_has_sequence:
self._enable_identity_insert = \
seq_column.key in self.compiled_parameters[0]
else:
diff -r 113a7ed95335 lib/sqlalchemy/sql/compiler.py
--- a/lib/sqlalchemy/sql/compiler.pyThu Aug 18 13:03:30 2011 -0400
+++ b/lib/sqlalchemy/sql/compiler.pyThu Aug 18 13:31:16 2011 -0400
@@ -198,6 +198,10 @@
# driver/DB enforces this
ansi_bind_rules = False
+postfetch = ()
+prefetch = ()
+returning = ()
+
def __init__(self, dialect, statement, column_keys=None,
inline=False, **kwargs):
Construct a new ``DefaultCompiler`` object.
diff -r 113a7ed95335 lib/sqlalchemy/sql/expression.py
--- a/lib/sqlalchemy/sql/expression.py Thu Aug 18 13:03:30 2011 -0400
+++ b/lib/sqlalchemy/sql/expression.py Thu Aug 18 13:31:16 2011 -0400
@@ -4792,6 +4792,8 @@
Executable._execution_options.union({'autocommit': True})
kwargs = util.immutabledict()
+_returning = None
+
def _process_colparams(self, parameters):
if isinstance(parameters, (list, tuple)):
pp = {}
--
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 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.
[sqlalchemy] Weird bug
Hello,
I have found a case where instantiating a select statement during the
declaration of the parent class in a one-to-many pair can cause a
downstream failure to generate well formed sql when performing a query
with a subqueryload option. I've boiled down a minimal example
(pasted below) which will produce the bug.
Note that the select must be called *during* the declaration (eg when
defining a custom column property) of the parent class and must refer
to a column of that class. Removing the id reference or moving the
select anywhere outside the scope of the parent class declaration will
make the bug disappear.
Cheers,
Brian Hawthorne
Amyris, Inc.
#--
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
select([id])
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
parent = relationship(Parent, backref='children')
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
session.add(Parent())
session.commit()
# Malformed SQL!
session.query(Parent).options(subqueryload('children')).all()
#--
Executing the above produces the following traceback:
Traceback (most recent call last):
File test.py, line 25, in module
session.query(Parent).options(subqueryload('children')).all()
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py, line 1729, in all
return list(self)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py, line 1960, in instances
rows = [process[0](row, None) for row in fetch]
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/mapper.py, line 2481, in _instance
eager_populators
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/mapper.py, line 2664, in _populators
self, row, adapter)):
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/interfaces.py, line 326, in
create_row_processor
reduced_path, mapper, row, adapter)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/strategies.py, line 890, in
create_row_processor
lambda x:x[1:]
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py, line 1839, in __iter__
return self._execute_and_instances(context)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py, line 1854, in
_execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py, line 1399, in execute
params)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py, line 1532, in
_execute_clauseelement
compiled_sql, distilled_params
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py, line 1640, in
_execute_context
context)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py, line 1633, in
_execute_context
context)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/default.py, line 325, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) no such column:
parent.id u'SELECT child.id AS child_id, child.parent_id AS
child_parent_id, anon_1.parent_id AS anon_1_parent_id \nFROM (SELECT
parent.id AS parent_id) AS anon_1 JOIN child ON parent.id =
child.parent_id ORDER BY anon_1.parent_id' ()
--
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] Weird bug
the issue is quite simple as can be seen in this demonstration:
from sqlalchemy import *
# two columns. Nothing up my sleeve !
c1 = Column('c1', Integer)
c2 = Column('c2', Integer)
# put one of them into a Select.
# generate _from_objects collection of c1 too early
s = select([c1])
t = Table('t', MetaData(), c1, c2)
# c1 has the wrong _from_objects
assert c1._from_objects == []
# c2 has the correct one
assert c2._from_objects == [t]
# see it here
assert str(select([c1])) == SELECT t.c1
assert str(select([c2])) == SELECT t.c2 \nFROM t
what to do about it, unsure. Removing the caching from _from_objects is the
immediate fix.
However, the original select() is still wrong. _from_objects gave it the wrong
data, period.How to guard against this issue, a potentially expensive reorg
of Select internals. As well as future issues of this sort, using Column
objects which generates cached state, then mutating the Column.
On Aug 18, 2011, at 3:12 PM, brianhawthorne wrote:
Hello,
I have found a case where instantiating a select statement during the
declaration of the parent class in a one-to-many pair can cause a
downstream failure to generate well formed sql when performing a query
with a subqueryload option. I've boiled down a minimal example
(pasted below) which will produce the bug.
Note that the select must be called *during* the declaration (eg when
defining a custom column property) of the parent class and must refer
to a column of that class. Removing the id reference or moving the
select anywhere outside the scope of the parent class declaration will
make the bug disappear.
Cheers,
Brian Hawthorne
Amyris, Inc.
#--
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
select([id])
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
parent = relationship(Parent, backref='children')
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
session.add(Parent())
session.commit()
# Malformed SQL!
session.query(Parent).options(subqueryload('children')).all()
#--
Executing the above produces the following traceback:
Traceback (most recent call last):
File test.py, line 25, in module
session.query(Parent).options(subqueryload('children')).all()
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py, line 1729, in all
return list(self)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py, line 1960, in instances
rows = [process[0](row, None) for row in fetch]
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/mapper.py, line 2481, in _instance
eager_populators
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/mapper.py, line 2664, in _populators
self, row, adapter)):
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/interfaces.py, line 326, in
create_row_processor
reduced_path, mapper, row, adapter)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/strategies.py, line 890, in
create_row_processor
lambda x:x[1:]
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py, line 1839, in __iter__
return self._execute_and_instances(context)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py, line 1854, in
_execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py, line 1399, in execute
params)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py, line 1532, in
_execute_clauseelement
compiled_sql, distilled_params
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py, line 1640, in
_execute_context
context)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py, line 1633, in
_execute_context
context)
File /usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/default.py, line 325, in do_execute
cursor.execute(statement,
Re: [sqlalchemy] Weird bug
On Aug 18, 2011, at 4:24 PM, Michael Bayer wrote: However, the original select() is still wrong. _from_objects gave it the wrong data, period.How to guard against this issue, a potentially expensive reorg of Select internals. As well as future issues of this sort, using Column objects which generates cached state, then mutating the Column. this is ticket 2261 http://www.sqlalchemy.org/trac/ticket/2261 , a patch fixing the issue is present there to defer the generation of Select._froms as well as warn within Column when you attach to a table post-maturely, the patch will be available in release 0.7.3 as well as 0.6.9. -- 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] Re: event.remove failure
On 18 Ago, 16:52, Michael Bayer mike...@zzzcomputing.com wrote: remove() isn't implemented yet. While the simple operation you see below would be fine for a single listener on a single target, the targets we have which propagate to subclasses (mapper events, attribute events) would require a more elaborate system that can revisit everywhere the event has been propagated and remove it from there as well. this is why remove isn't published in the docs right now. Thanks, I wasn't carefull enought to realize it was not in the docs. I guessed there was such a function and I found it, so I tried to use it... In our own tests I use a hack to remove an entire set of listeners at once, if this is a testing teardown scenario you're dealing with. No really I would need it in a different setup. I have many GTK widgets that show some data that are in a session and I need to update the GUI whenever the data change. Otherwise, ad-hoc removal on a per operation basis ? I knew someone would try it though damned if I could imagine what possible use there could be for that. If this is the case here, care to entertain me ? Not sure what you mean exactly here, but if the point is: why I want to use 'remove'. It's just that when I destroy the GUI widget that displays data I'd like to remove the listener to be sure no reference tries to keep my object in memory. As of now I see that callbacks are still called. sandro *:-) -- 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] Repetitive Fields in declarative
I want to create a table that has several similar fields. For example, assume the fields are field1, field2, ... Is there a way in the declarative class that I can do something like: for i in range(10): 'field%d' % i = Column( ... ) Thanks, Mark -- 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] Repetitive Fields in declarative
On Aug 18, 2011, at 6:06 PM, Mark Erbaugh wrote:
want to create a table that has several similar fields. For example, assume
the fields are field1, field2, ...
Is there a way in the declarative class that I can do something like:
for i in range(10):
'field%d' % i = Column( ... )
Thanks,
Mark
Figured it out:
after the class definition:
for i in range(10):
class.__table__.append_column(Column('field%d' % i, ...))
Mark
--
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] Repetitive Fields in declarative
Me again (see below):
On Aug 18, 2011, at 7:01 PM, Mark Erbaugh wrote:
On Aug 18, 2011, at 6:06 PM, Mark Erbaugh wrote:
want to create a table that has several similar fields. For example, assume
the fields are field1, field2, ...
Is there a way in the declarative class that I can do something like:
for i in range(10):
'field%d' % i = Column( ... )
Thanks,
Mark
Figured it out:
after the class definition:
for i in range(10):
class.__table__.append_column(Column('field%d' % i, ...))
Some of the fields that I am adding this way are foreign keys to another table.
Is there a way to specify a relationship based on these foreign key fields?
Mark
--
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.
