Re: [sqlalchemy] possible bug with SQL CASE
Manlio Perillo wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Michael Bayer ha scritto: >> Manlio Perillo wrote: >>> However, now I'm not sure to understand the example here: >>> http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?highlight=exists#sqlalchemy.sql.expression.exists >>> >>> # use on an existing select() >>> s = select([table.c.col1]).where(table.c.col2==5) >>> s = exists(s) >> >> wow, look at that. I'm glad exists() accepts that, since it seemed >> awkward that one would be required to say exists([someselect]). your >> call to as_scalar() was throwing it off. It is accepted in the same way >> now as a plain select() in r6702. >> > > By the way; with another query (but still using exists and case) I get > this error: > > TypeError: self_group() takes exactly 2 arguments (1 given) > /usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py, > line 2411: > (_literal_as_binds(c).self_group(), _literal_as_binds(r)) for (c, r) in > whens > > > It is probable that this query is broken, too. > Or it may be a bug in SQLAlchemy. heard about that one the other day, just one of the self_groups() had a bad signature, committed that in r6703. > > If required, I can try to write a minimal example that reproduces the > problem. > > > Regards Manlio > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iEYEARECAAYFAktiBZsACgkQscQJ24LbaUQgQACeJWYpAlkSloQZD/jBqUfNYApQ > 7iMAn2eWtTI3dTBqjUCj13DOmjY6rPBN > =RIh0 > -END PGP SIGNATURE- > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] possible bug with SQL CASE
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: > Manlio Perillo wrote: >> However, now I'm not sure to understand the example here: >> http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?highlight=exists#sqlalchemy.sql.expression.exists >> >> # use on an existing select() >> s = select([table.c.col1]).where(table.c.col2==5) >> s = exists(s) > > wow, look at that. I'm glad exists() accepts that, since it seemed > awkward that one would be required to say exists([someselect]). your > call to as_scalar() was throwing it off. It is accepted in the same way > now as a plain select() in r6702. > By the way; with another query (but still using exists and case) I get this error: TypeError: self_group() takes exactly 2 arguments (1 given) /usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py, line 2411: (_literal_as_binds(c).self_group(), _literal_as_binds(r)) for (c, r) in whens It is probable that this query is broken, too. Or it may be a bug in SQLAlchemy. If required, I can try to write a minimal example that reproduces the problem. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktiBZsACgkQscQJ24LbaUQgQACeJWYpAlkSloQZD/jBqUfNYApQ 7iMAn2eWtTI3dTBqjUCj13DOmjY6rPBN =RIh0 -END PGP SIGNATURE- -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] possible bug with SQL CASE
Manlio Perillo wrote: > > However, now I'm not sure to understand the example here: > http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?highlight=exists#sqlalchemy.sql.expression.exists > > # use on an existing select() > s = select([table.c.col1]).where(table.c.col2==5) > s = exists(s) wow, look at that. I'm glad exists() accepts that, since it seemed awkward that one would be required to say exists([someselect]). your call to as_scalar() was throwing it off. It is accepted in the same way now as a plain select() in r6702. > > > > Regards Manlio > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iEYEARECAAYFAkth+0EACgkQscQJ24LbaUTf0QCfTWwPMRvTYMAH7R/mU2CCb2fQ > DaYAn0EoxWUCkFv8e/omK0A+tx8MY2S0 > =i3I0 > -END PGP SIGNATURE- > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] possible bug with SQL CASE
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
> [...]
> from sqlalchemy import schema, types, sql, create_engine
>
> metadata = schema.MetaData()
> test = schema.Table(
> 'test', metadata,
> schema.Column('x', types.Integer, primary_key=True)
> )
>
> engine = create_engine('sqlite://')
>
> subquery = sql.exists(test.select().as_scalar())
> clause = sql.case([(True, subquery.select().as_scalar)])
> query = clause.select()
>
> engine.execute(query)
>
>> its more SQLA not warning you about an argument it doesn't like and then
>> it leading into problems. it should raise an error earlier. basically
>> exists() is like a select() and accepts the list of column expressions as
>> a list. if you were to pass your select inside of a [], you'd get a
>> valid SQL expression, though with two levels of "select".
>> sql.exists([test]) or exists().select_from(test) would be more effective.
>
Ok, thanks.
However, now I'm not sure to understand the example here:
http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?highlight=exists#sqlalchemy.sql.expression.exists
# use on an existing select()
s = select([table.c.col1]).where(table.c.col2==5)
s = exists(s)
Regards Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkth+0EACgkQscQJ24LbaUTf0QCfTWwPMRvTYMAH7R/mU2CCb2fQ
DaYAn0EoxWUCkFv8e/omK0A+tx8MY2S0
=i3I0
-END PGP SIGNATURE-
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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] possible bug with SQL CASE
Michael Bayer wrote:
>>
>> engine.execute(query)
>
> its more SQLA not warning you about an argument it doesn't like and then
> it leading into problems. it should raise an error earlier. basically
> exists() is like a select() and accepts the list of column expressions as
> a list. if you were to pass your select inside of a [], you'd get a
> valid SQL expression, though with two levels of "select".
> sql.exists([test]) or exists().select_from(test) would be more effective.
>
> ill try to catch that issue now.
more informative message in r6701
>
>
>>
>>
>> I get an error:
>>
>> Traceback (most recent call last):
>> File "case.py", line 13, in
>> subquery = sql.exists(test.select().as_scalar())
>> File
>> "/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
>> line 520, in exists
>> return _Exists(*args, **kwargs)
>> File
>> "/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
>> line 2688, in __init__
>> s = select(*args, **kwargs).as_scalar().self_group()
>> File
>> "/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
>> line 238, in select
>> return Select(columns, whereclause=whereclause, from_obj=from_obj,
>> **kwargs)
>> File
>> "/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
>> line 3492, in __init__
>> if columns:
>> File
>> "/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
>> line 1289, in __nonzero__
>> raise TypeError("Boolean value of this clause is not defined")
>> TypeError: Boolean value of this clause is not defined
>>
>>
>> I'm not sure if my code is correct.
>> If it is not correct, what it the correct method to define that query?
>>
>>
>>
>> Thanks Manlio
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v1.4.10 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>>
>> iEYEARECAAYFAkth7S4ACgkQscQJ24LbaUQEXgCggBUV3orSFAPPK155cVHplpb4
>> 3U0An1djFkgt3LE6LHQOCHrepVt4F0Vc
>> =hL1p
>> -END PGP SIGNATURE-
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalch...@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 sqlalch...@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 sqlalch...@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] possible bug with SQL CASE
Manlio Perillo wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hi.
>
> It seems there is a bug with SQL CASE support in SQLAlchemy (I'm testing
> using trunk).
>
> Here is the original SQL statements:
>
> CREATE TABLE test (
> x INTEGER PRIMARY KEY
> );
>
> SELECT
> CASE WHEN 'true'
> THEN (SELECT EXISTS (SELECT x FROM test))
> END;
>
>
> And here is the Python code:
>
> from sqlalchemy import schema, types, sql, create_engine
>
> metadata = schema.MetaData()
> test = schema.Table(
> 'test', metadata,
> schema.Column('x', types.Integer, primary_key=True)
> )
>
> engine = create_engine('sqlite://')
>
> subquery = sql.exists(test.select().as_scalar())
> clause = sql.case([(True, subquery.select().as_scalar)])
> query = clause.select()
>
> engine.execute(query)
its more SQLA not warning you about an argument it doesn't like and then
it leading into problems. it should raise an error earlier. basically
exists() is like a select() and accepts the list of column expressions as
a list. if you were to pass your select inside of a [], you'd get a
valid SQL expression, though with two levels of "select".
sql.exists([test]) or exists().select_from(test) would be more effective.
ill try to catch that issue now.
>
>
> I get an error:
>
> Traceback (most recent call last):
> File "case.py", line 13, in
> subquery = sql.exists(test.select().as_scalar())
> File
> "/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
> line 520, in exists
> return _Exists(*args, **kwargs)
> File
> "/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
> line 2688, in __init__
> s = select(*args, **kwargs).as_scalar().self_group()
> File
> "/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
> line 238, in select
> return Select(columns, whereclause=whereclause, from_obj=from_obj,
> **kwargs)
> File
> "/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
> line 3492, in __init__
> if columns:
> File
> "/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
> line 1289, in __nonzero__
> raise TypeError("Boolean value of this clause is not defined")
> TypeError: Boolean value of this clause is not defined
>
>
> I'm not sure if my code is correct.
> If it is not correct, what it the correct method to define that query?
>
>
>
> Thanks Manlio
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkth7S4ACgkQscQJ24LbaUQEXgCggBUV3orSFAPPK155cVHplpb4
> 3U0An1djFkgt3LE6LHQOCHrepVt4F0Vc
> =hL1p
> -END PGP SIGNATURE-
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@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 sqlalch...@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] possible bug with SQL CASE
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
It seems there is a bug with SQL CASE support in SQLAlchemy (I'm testing
using trunk).
Here is the original SQL statements:
CREATE TABLE test (
x INTEGER PRIMARY KEY
);
SELECT
CASE WHEN 'true'
THEN (SELECT EXISTS (SELECT x FROM test))
END;
And here is the Python code:
from sqlalchemy import schema, types, sql, create_engine
metadata = schema.MetaData()
test = schema.Table(
'test', metadata,
schema.Column('x', types.Integer, primary_key=True)
)
engine = create_engine('sqlite://')
subquery = sql.exists(test.select().as_scalar())
clause = sql.case([(True, subquery.select().as_scalar)])
query = clause.select()
engine.execute(query)
I get an error:
Traceback (most recent call last):
File "case.py", line 13, in
subquery = sql.exists(test.select().as_scalar())
File
"/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
line 520, in exists
return _Exists(*args, **kwargs)
File
"/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
line 2688, in __init__
s = select(*args, **kwargs).as_scalar().self_group()
File
"/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
line 238, in select
return Select(columns, whereclause=whereclause, from_obj=from_obj,
**kwargs)
File
"/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
line 3492, in __init__
if columns:
File
"/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py",
line 1289, in __nonzero__
raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined
I'm not sure if my code is correct.
If it is not correct, what it the correct method to define that query?
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkth7S4ACgkQscQJ24LbaUQEXgCggBUV3orSFAPPK155cVHplpb4
3U0An1djFkgt3LE6LHQOCHrepVt4F0Vc
=hL1p
-END PGP SIGNATURE-
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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] How to translate to SA "select 1 from"
I made a bit of progress, as I couldn't get the orm version to work I
tried creating it using select.
I used aliases and made two separate selects to better be able to debug
things.
a1 = db.Somecode_T.__table__.alias('a1')
a2 = db.Somecode_T.__table__.alias('a2')
sa = db.sa.select([a2.c.id], db.sa.and_(a2.c.code_id == a1.c.code_id,
a2.c.lang_code5 == 'FR_fr'))
s = db.sa.select([a1], db.sa.or_(a1.c.lang_code5 == 'FR_fr',
db.sa.and_(a1.c.lang_code5 == 'EN_en',
db.sa.not_(db.sa.exists(sa)
This gives the result I am looking for, so then I went back to the orm
version which I still can't get to work.
stAlias1 = db.sao.aliased(db.Somecode_T)
stAlias2 = db.sao.aliased(db.Somecode_T)
sa = session.query(stAlias2.id).filter(db.sa.and_(stAlias2.code_id ==
stAlias1.code_id,
stAlias2.lang_code5
== 'FR_fr'))
s1 = session.query(stAlias1).filter(db.sa.or_(stAlias1.lang_code5 ==
'FR_fr',
db.sa.and_(stAlias1.lang_code5 == 'EN_en',
db.sa.not_(db.sa.exists(sa)
The above gives me the following exception:
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py:2901:
SAWarning: Column '(4,)' on table 'Select object' being replaced by
another column with the same key. Consider use_labels for select()
statements.
selectable.columns[c.name] = c
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py:2901:
SAWarning: Column '(6,)' on table 'Select object' being replaced by
another column with the same key. Consider use_labels for select()
statements.
selectable.columns[c.name] = c
Traceback (most recent call last):
File "saTest.py", line 116, in
db.sa.not_(db.sa.exists(sa)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py",
line 515, in exists
return _Exists(*args, **kwargs)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py",
line 2466, in __init__
s = select(*args, **kwargs).as_scalar().self_group()
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py",
line 3014, in as_scalar
return _ScalarSelect(self)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py",
line 3117, in __init__
raise exc.InvalidRequestError("Scalar select can only be created "
sqlalchemy.exc.InvalidRequestError: Scalar select can only be created
from a Select object that has exactly one column expression.
Can a kind soul point me in the right direction.
Werner
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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: orderinglist and delete-orphan cascade
On Jan 29, 3:52 am, "Michael Bayer" wrote: > you > need to delete the object individually and flush before altering the > collection. Thanks for the advice, but I can't do that because I'm working with objects (generically) in the detached state and committing later. I'll find another way of maintaining the order. a. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Problem with CircularDependencyError
Aleksander Siewierski wrote:
> Hi!,
>
> I have two entities: Channel and Track. Track has a reference to
> Channel (channel field - Track is played on this Channel), and Channel
> has a reference to Track (last_track field - last planned Track for
> Channel). So definition of tables, classes and mappers is:
>
> """
> import sqlalchemy
> from sqlalchemy.orm import *
> from sqlalchemy.schema import *
> from sqlalchemy.types import *
>
> sqlalchemy_config = {
> 'url' : 'mysql://...',
> 'echo': True,
> 'pool_recycle': 600,
> 'convert_unicode' : True,
> }
>
> ENGINE = sqlalchemy.engine.engine_from_config(sqlalchemy_config, '')
> METADATA = sqlalchemy.schema.MetaData()
> METADATA.bind = ENGINE
> session_maker = sqlalchemy.orm.sessionmaker(autoflush=True,
> transactional=True, bind=ENGINE)
> SESSION = sqlalchemy.orm.scoped_session(session_maker)
>
> class Channel(object):
> def __init__(self, name):
> self.name = name
>
> class Track(object):
> def __init__(self, title):
> self.title = title
>
> channel_t = Table(
> 'channels',
> METADATA,
> Column('id', Integer, primary_key=True),
> Column('name', Unicode(256)),
> Column('last_track_id', Integer, ForeignKey('tracks.id')),
> mysql_engine='InnoDB',
> mysql_charset='utf8'
> )
>
> track_t = Table(
> 'tracks',
> METADATA,
> Column('id', Integer, primary_key=True),
> Column('channel_id', Integer, ForeignKey('channels.id'),
> nullable=False),
> Column('title', Unicode(256)),
> mysql_engine='InnoDB',
> mysql_charset='utf8'
> )
>
> mapper(Channel, channel_t, properties = {
>'last_track': relation(Track,
> primaryjoin=channel_t.c.last_track_id == track_t.c.id),
>}
> )
>
> mapper(Track, track_t, properties={
>'channel': relation(Channel,
> primaryjoin=track_t.c.channel_id == channel_t.c.id),
>}
> )
this pattern is described here:
http://www.sqlalchemy.org/docs/mappers.html#rows-that-point-to-themselves-mutually-dependent-rows
>
> channel1 = SESSION.query(Channel).get(1)
> track1 = Track('Track 1')
> track1.channel = channel1
> channel1.last_track = track1
>
> SESSION.add(track1)
> SESSION.commit()
> """
>
> and when I try to commit this, I have following error:
> Traceback (most recent call last):
> File "cml.py", line 75, in
> SESSION.commit()
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/scoping.py", line 127, in do
> return getattr(self.registry(), name)(*args, **kwargs)
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/session.py", line 671, in commit
> self.transaction.commit()
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/session.py", line 378, in commit
> self._prepare_impl()
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/session.py", line 362, in _prepare_impl
> self.session.flush()
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/session.py", line 1354, in flush
> self._flush(objects)
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/session.py", line 1432, in _flush
> flush_context.execute()
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/unitofwork.py", line 258, in execute
> tasks = self._sort_dependencies()
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/unitofwork.py", line 299, in
> _sort_dependencies
> for t in task._sort_circular_dependencies(self,
> [self.get_task_by_mapper(i) for i in cycles]):
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/orm/unitofwork.py", line 582, in
> _sort_circular_dependencies
> head = topological.sort_as_tree(tuples,
> object_to_original_task.iterkeys())
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/topological.py", line 59, in sort_as_tree
> return _organize_as_tree(_sort(tuples, allitems,
> allow_cycles=with_cycles))
> File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
> py2.6.egg/sqlalchemy/topological.py", line 209, in _sort
> raise CircularDependencyError("Circular dependency detected " +
> repr(edges) + repr(queue))
> sqlalchemy.exc.CircularDependencyError: Circular dependency detected
> [(,
> ),
> (,
> )][]
>
> what is wrong with this definitions? Documentation says that
> CircularDependencyError should appear only when I do topological
> sorting.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email t
Re: [sqlalchemy] orderinglist and delete-orphan cascade
avdd wrote:
> Is this a bug or am I doing something wrong?
>
> $ cat testordlist.py ; python testordlist.py
these are just known limitations of orderinglist, when it is asked to sort
by primary key. I don't care for this extension very much since it makes
promises that the ORM is not able to keep when it is given primary or
unique key to sort on - the ORM can't magically figure out when object A
with PK 1 is being deleted, and object B is taking over PK 1 in the same
flush, that it needs to change the order of updates/deletes in that case.
the ORM isn't able to do deletes before updates at all, currently. you
need to delete the object individually and flush before altering the
collection.
>
> import sqlalchemy as sql
> from sqlalchemy import orm
> from sqlalchemy.ext.orderinglist import ordering_list
> from sqlalchemy.ext.declarative import declarative_base
>
> engine = sql.create_engine("sqlite:///:memory:")
> metadata = sql.MetaData(bind=engine)
> db = orm.create_session(bind=engine)
>
> T = declarative_base(metadata=metadata)
>
> class A(T):
> __tablename__ = 'a'
> id = sql.Column(sql.Integer, primary_key=True)
> uc = orm.relation('UC', order_by=('i',), cascade='all,delete-
> orphan')
> oc = orm.relation('OC', order_by=('i',), cascade='all,delete-
> orphan',
> collection_class=ordering_list('i'))
> class UC(T):
> __tablename__ = 'uc'
> a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
> primary_key=True)
> i = sql.Column(sql.Integer, primary_key=True)
>
> class OC(T):
> __tablename__ = 'oc'
> a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
> primary_key=True)
> i = sql.Column(sql.Integer, primary_key=True)
>
> metadata.create_all()
>
> a = A(id=1)
> a.oc = [OC(), OC()]
> a.uc = [UC(i=1), UC(i=2)]
>
> with db.begin():
> db.add(a)
> with db.begin():
> del db.query(A).first().uc[0]
> with db.begin():
> del db.query(A).first().oc[0]
>
> Traceback (most recent call last):
> File "testordlist.py", line 40, in
> del db.query(A).first().oc[0]
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
> session.py", line 449, in __exit__
> self.commit()
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
> session.py", line 378, in commit
> self._prepare_impl()
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
> session.py", line 362, in _prepare_impl
> self.session.flush()
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
> session.py", line 1354, in flush
> self._flush(objects)
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
> session.py", line 1432, in _flush
> flush_context.execute()
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
> unitofwork.py", line 261, in execute
> UOWExecutor().execute(self, tasks)
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
> unitofwork.py", line 753, in execute
> self.execute_save_steps(trans, task)
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
> unitofwork.py", line 768, in execute_save_steps
> self.save_objects(trans, task)
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
> unitofwork.py", line 759, in save_objects
> task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/mapper.py",
> line 1417, in _save_obj
> c = connection.execute(statement.values(value_params), params)
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
> base.py", line 835, in execute
> return Connection.executors[c](self, object, multiparams, params)
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
> base.py", line 885, in _execute_clauseelement
> return self.__execute_context(context)
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
> base.py", line 907, in __execute_context
> self._cursor_execute(context.cursor, context.statement,
> context.parameters[0], context=context)
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
> base.py", line 961, in _cursor_execute
> self._handle_dbapi_exception(e, statement, parameters, cursor,
> context)
> File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
> base.py", line 942, in _handle_dbapi_exception
> raise exc.DBAPIError.instance(statement, parameters, e,
> connection_invalidated=is_disconnect)
> sqlalchemy.exc.IntegrityError: (IntegrityError) columns a_id, i are
> not unique u'UPDATE oc SET i=? WHERE oc.a_id = ? AND oc.i = ?' [0, 1,
> 1]
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@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
[sqlalchemy] Problem with CircularDependencyError
Hi!,
I have two entities: Channel and Track. Track has a reference to
Channel (channel field - Track is played on this Channel), and Channel
has a reference to Track (last_track field - last planned Track for
Channel). So definition of tables, classes and mappers is:
"""
import sqlalchemy
from sqlalchemy.orm import *
from sqlalchemy.schema import *
from sqlalchemy.types import *
sqlalchemy_config = {
'url' : 'mysql://...',
'echo': True,
'pool_recycle': 600,
'convert_unicode' : True,
}
ENGINE = sqlalchemy.engine.engine_from_config(sqlalchemy_config, '')
METADATA = sqlalchemy.schema.MetaData()
METADATA.bind = ENGINE
session_maker = sqlalchemy.orm.sessionmaker(autoflush=True,
transactional=True, bind=ENGINE)
SESSION = sqlalchemy.orm.scoped_session(session_maker)
class Channel(object):
def __init__(self, name):
self.name = name
class Track(object):
def __init__(self, title):
self.title = title
channel_t = Table(
'channels',
METADATA,
Column('id', Integer, primary_key=True),
Column('name', Unicode(256)),
Column('last_track_id', Integer, ForeignKey('tracks.id')),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
track_t = Table(
'tracks',
METADATA,
Column('id', Integer, primary_key=True),
Column('channel_id', Integer, ForeignKey('channels.id'),
nullable=False),
Column('title', Unicode(256)),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
mapper(Channel, channel_t, properties = {
'last_track': relation(Track,
primaryjoin=channel_t.c.last_track_id == track_t.c.id),
}
)
mapper(Track, track_t, properties={
'channel': relation(Channel,
primaryjoin=track_t.c.channel_id == channel_t.c.id),
}
)
channel1 = SESSION.query(Channel).get(1)
track1 = Track('Track 1')
track1.channel = channel1
channel1.last_track = track1
SESSION.add(track1)
SESSION.commit()
"""
and when I try to commit this, I have following error:
Traceback (most recent call last):
File "cml.py", line 75, in
SESSION.commit()
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/orm/scoping.py", line 127, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/orm/session.py", line 671, in commit
self.transaction.commit()
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/orm/session.py", line 378, in commit
self._prepare_impl()
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/orm/session.py", line 362, in _prepare_impl
self.session.flush()
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/orm/session.py", line 1354, in flush
self._flush(objects)
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/orm/session.py", line 1432, in _flush
flush_context.execute()
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/orm/unitofwork.py", line 258, in execute
tasks = self._sort_dependencies()
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/orm/unitofwork.py", line 299, in
_sort_dependencies
for t in task._sort_circular_dependencies(self,
[self.get_task_by_mapper(i) for i in cycles]):
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/orm/unitofwork.py", line 582, in
_sort_circular_dependencies
head = topological.sort_as_tree(tuples,
object_to_original_task.iterkeys())
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/topological.py", line 59, in sort_as_tree
return _organize_as_tree(_sort(tuples, allitems,
allow_cycles=with_cycles))
File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.8-
py2.6.egg/sqlalchemy/topological.py", line 209, in _sort
raise CircularDependencyError("Circular dependency detected " +
repr(edges) + repr(queue))
sqlalchemy.exc.CircularDependencyError: Circular dependency detected
[(,
),
(,
)][]
what is wrong with this definitions? Documentation says that
CircularDependencyError should appear only when I do topological
sorting.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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] orderinglist and delete-orphan cascade
Is this a bug or am I doing something wrong?
$ cat testordlist.py ; python testordlist.py
import sqlalchemy as sql
from sqlalchemy import orm
from sqlalchemy.ext.orderinglist import ordering_list
from sqlalchemy.ext.declarative import declarative_base
engine = sql.create_engine("sqlite:///:memory:")
metadata = sql.MetaData(bind=engine)
db = orm.create_session(bind=engine)
T = declarative_base(metadata=metadata)
class A(T):
__tablename__ = 'a'
id = sql.Column(sql.Integer, primary_key=True)
uc = orm.relation('UC', order_by=('i',), cascade='all,delete-
orphan')
oc = orm.relation('OC', order_by=('i',), cascade='all,delete-
orphan',
collection_class=ordering_list('i'))
class UC(T):
__tablename__ = 'uc'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)
class OC(T):
__tablename__ = 'oc'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)
metadata.create_all()
a = A(id=1)
a.oc = [OC(), OC()]
a.uc = [UC(i=1), UC(i=2)]
with db.begin():
db.add(a)
with db.begin():
del db.query(A).first().uc[0]
with db.begin():
del db.query(A).first().oc[0]
Traceback (most recent call last):
File "testordlist.py", line 40, in
del db.query(A).first().oc[0]
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
session.py", line 449, in __exit__
self.commit()
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
session.py", line 378, in commit
self._prepare_impl()
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
session.py", line 362, in _prepare_impl
self.session.flush()
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
session.py", line 1354, in flush
self._flush(objects)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
session.py", line 1432, in _flush
flush_context.execute()
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
unitofwork.py", line 261, in execute
UOWExecutor().execute(self, tasks)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
unitofwork.py", line 753, in execute
self.execute_save_steps(trans, task)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
unitofwork.py", line 768, in execute_save_steps
self.save_objects(trans, task)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
unitofwork.py", line 759, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/mapper.py",
line 1417, in _save_obj
c = connection.execute(statement.values(value_params), params)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
base.py", line 835, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
base.py", line 885, in _execute_clauseelement
return self.__execute_context(context)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
base.py", line 907, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
base.py", line 961, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
base.py", line 942, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.IntegrityError: (IntegrityError) columns a_id, i are
not unique u'UPDATE oc SET i=? WHERE oc.a_id = ? AND oc.i = ?' [0, 1,
1]
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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] Interface to (very old) Sybaase?
Adrian von Bidder wrote: > Heyho! > > Has anybody worked with a Sybase Anywhere (ASA 9 -- yes, very old ...) > database? I may need to build a simple CRUD (actually onnly R and U ;-) > frontend to some legacy application. (I probably will give TurbeGears a > try > for this.) > > I do have a JDBC driver, and I *think* ODBC should work (when I tried it > some time ago), but I haven't worked (much) with either. (I think I > remember having seen a jdbc bridge for either Perl or Python, but I'm not > sure anywhere and at least I can't find Debian packages right now.) the sybase dialect that's currently in 0.5 and such was developed for sybase anywhere, which in fact is not really sybase.its not a platform we're able to support but you're free to try your luck with what's there. > > Thanks in advance > -- vbi > > -- > Je n'ai pas souvent assisté à des course de spermatozoïdes, mais j'ai > donné beaucoup de départs. > -+- Olivier de Kersauson -+- > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] How to translate to SA "select 1 from"
On 28/01/2010 11:05, werner wrote: I have one more variation on my translation stuff. Trying to translate this sql: select * from MyTable M1 where M1.LANG_CODE5 = 'FR_fr' or ( M1.LANG_CODE5 = 'EN_en' AND NOT EXISTS( SELECT 1 FROM MyTable M2 where M2.CODE_ID = M1.CODE_ID and M2.LANG_CODE5 = 'FR_fr' ) ) to SA, I can't figure out how to represent "select 1 from mytable". May I ask again for some tips on this. Forgot to include what I have so far: s1 = session.query(db.Somecode_T).filter(db.sa.or_(db.Somecode_T.lang_code5 == 'FR_fr', db.sa.and_(db.Somecode_T.lang_code5 == 'EN_en', db.sa.not_(db.sa.exists( session.query(stAlias1).filter(db.sa.and_( db.Somecode_T.code_id == stAlias1.code_id, stAlias1.lang_code5 == 'FR_fr'))) But the "limit(1) isn't what is needed :-[ . So, I tried this: s1 = session.query(db.Somecode_T).filter(db.sa.or_(db.Somecode_T.lang_code5 == 'FR_fr', db.sa.and_(db.Somecode_T.lang_code5 == 'EN_en', db.sa.not_(db.sa.exists( session.query(stAlias1.id).filter(db.sa.and_( db.Somecode_T.code_id == stAlias1.code_id, stAlias1.lang_code5 == 'FR_fr'))) But here I get an exception: "sqlalchemy.exc.InvalidRequestError: Scalar select can only be created from a Select object that has exactly one column expression." Werner Werner -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] How to translate to SA "select 1 from"
I have one more variation on my translation stuff. Trying to translate this sql: select * from MyTable M1 where M1.LANG_CODE5 = 'FR_fr' or ( M1.LANG_CODE5 = 'EN_en' AND NOT EXISTS( SELECT 1 FROM MyTable M2 where M2.CODE_ID = M1.CODE_ID and M2.LANG_CODE5 = 'FR_fr' ) ) to SA, I can't figure out how to represent "select 1 from mytable". May I ask again for some tips on this. Thanks Werner -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Translating a sql union statement into SA problem
Michael, On 27/01/2010 19:37, Michael Bayer wrote: werner wrote: q2 = session.query(stAlias2).outerjoin((stAlias2, stAlias2.code_id==stAlias3.code_id), this is going to render select * from left outer join on .code_id==.code_id i.e. renders twice in the FROM Thanks for the pointer. It took me a long time to figure out the "onclause" for this but this is what I ended up with and it works for me. stAlias1 = db.sao.aliased(db.Somecode_T) q1 = session.query(stAlias1).filter(stAlias1.lang_code5=='FR_fr') stAlias2 = db.sao.aliased(db.Somecode_T) stAlias3 = db.sao.aliased(db.Somecode_T) q2 = session.query(stAlias2).outerjoin((stAlias3, db.sa.and_( stAlias2.code_id == stAlias3.code_id, stAlias3.lang_code5 == 'FR_fr'))).filter(db.sa.and_( stAlias2.lang_code5 == 'EN_en', stAlias3.id == db.sa.null())) q3 = q1.union(q2) Thanks Werner -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
