[sqlalchemy] Re: Nested joins duplication
Hi Mike! Thank you very much for your help. It hasn indeed solved my problem. On Aug 10, 4:23 pm, Michael Bayer mike...@zzzcomputing.com wrote: query=session.query(Address) query=query.join((User, Address.user)) query=query.join((Group, User.group)) Yes, and I indeed need this form to explicitly specify the resource. In fact I use automatically generated aliases in my RUM application. Users can specify abitrary nested query parameters in RUM like occasion.type.name and RUM generates them automatically. http://hg.python-rum.org/RumAlchemy/rev/08e2338b4729 I am happy, that SA is such a very precise tool. Chees, Michael -- 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: Nested joins duplication
just for the typo It hasn indeed solved my problem. It has solved my problem. Everything works fine now :-). Cheers, Michael -- 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] Nested joins duplication
Hi!
SQLAlchemy is really awesome (I really love it)
and I am still working on the RUM web frontend for it.
I have the problem, that I would like to make some nested join:
query=session.query(Address)
query=query.join(User, Address.user)
query=query.join(Group, User.group)
Is it legal to the join this way?
The clauses seem to be duplicated:
SELECT addresses.id AS addresses_id, addresses.email_address AS
addresses_email_address, addresses.user_id AS addresses_user_id
FROM addresses
JOIN users ON users.id = addresses.user_id
JOIN users ON users.id = addresses.user_id
JOIN groups ON groups.id = users.group_id
JOIN groups ON groups.id = users.group_id
I attach a full example to this mail.
I got the same behaviour with all versions I tried (0.5.8 (the example is for
0.6.x), 0.6.1, and 0.6.3.
Thank you very much in advance.
Michael
---
Michael Brickenstein
Mathematisches Forschungsinstitut Oberwolfach gGmbH
Schwarzwaldstr. 9 - 11
77709 Oberwolfach
Tel.: 07834/979-31
Fax: 07834/979-38
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref, sessionmaker
Base = declarative_base()
metadata = Base.metadata
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
group_id=Column(Integer, ForeignKey('groups.id'))
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship(User, backref=backref('addresses', order_by=id))
class Group(Base):
__tablename__ = 'groups'
id = Column(Integer, primary_key=True)
name = Column(String)
users= relationship(User, backref='group')
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData(bind=engine)
metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
query=session.query(Address)
query=query.join(User, Address.user)
query=query.join(Group, User.group)
print str(query)
--
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] postgresql column default
Hi!
I have reflected my table from the db (Postgresql 8.4.2, tried psyco
2.0.12 and 2.0.14) using
Table(name, metadata, autoload=True, autoload_with=engine)
After that I map the table.
When later in my app, I try to access the default values from the DB,
I use
the columnproperty:
from owconf.model import Participation
from rumalchemy.util import get_mapper
prop = get_mapper(Participation).get_property('remark')
prop
sqlalchemy.orm.properties.ColumnProperty object at 0xac5dcec
c=prop.columns[0]
repr(c.default)
repr(c.server_default)
prop = get_mapper(Participation).get_property('number_of_companions')
This is the reflection of a simple text field:
remark | text| not null default ''::text
In former times, I used
prop.server_default to find out, that there is an default of ''.
This does not exist any more at that place, so I have inspected the
property:
c=prop.columns[0]
repr(c.default)
- None
repr(c.server_default)
- None
The same occurs with integer fields like that:
number_of_companions| integer | not null default 0
Which is now the proper way to find out the right default for the
column?
The functionality is very useful for the RUM CRUD application.
http://python-rum.org/
Cheers,
Michael
--
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: postgresql column default
Sorry, forgot to mention, I had the same behaviour with SA 0.5.6 and
0.5.8.
Cheers,
Michael
On 15 Apr., 12:27, Michael Brickenstein brickenst...@mfo.de wrote:
Hi!
I have reflected my table from the db (Postgresql 8.4.2, tried psyco
2.0.12 and 2.0.14) using
Table(name, metadata, autoload=True, autoload_with=engine)
After that I map the table.
When later in my app, I try to access the default values from the DB,
I use
the columnproperty:
from owconf.model import Participation
from rumalchemy.util import get_mapper
prop = get_mapper(Participation).get_property('remark')
prop
sqlalchemy.orm.properties.ColumnProperty object at 0xac5dcec
c=prop.columns[0]
repr(c.default)
repr(c.server_default)
prop = get_mapper(Participation).get_property('number_of_companions')
This is the reflection of a simple text field:
remark | text | not null default ''::text
In former times, I used
prop.server_default to find out, that there is an default of ''.
This does not exist any more at that place, so I have inspected the
property:
c=prop.columns[0]
repr(c.default)
- None
repr(c.server_default)
- None
The same occurs with integer fields like that:
number_of_companions | integer | not null default 0
Which is now the proper way to find out the right default for the
column?
The functionality is very useful for the RUM CRUD application.
http://python-rum.org/
Cheers,
Michael
--
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: postgresql column default
On 15 Apr., 13:03, Michael Brickenstein brickenst...@mfo.de wrote: Sorry, forgot to mention, I had the same behaviour with SA 0.5.6 and 0.5.8. Cheers, In former times, I used prop.server_default to find out, that there is an default of ''. Sorry, that's wrong it has always been the columns[0], that I have to inspect. Nevertheless, the question for an official way remains, so far the situation is the following: c.server_default - None c2=iter(c.base_columns).next() - DefaultClause(sqlalchemy.sql.expression._TextClause object at 0xa2507ac, for_update=False) Is that a bug or a feature? Cheers, Michael -- 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] Re: postgresql column default
Hi!
Thanks for your very prompt
answer.
Unluckily, my mails were a little bit unprecise:
So, I wondered why the following happened:
prop = get_mapper(Participation).get_property('remark')
c=prop.columns[0]
c
Column(u'remark', PGText(length=None, convert_unicode=False,
assert_unicode=None), table=%(188392684 participation)s, nullable=False)
gives me a column
c.server_default
no server default
c2=iter(c.base_columns).next()
c2
Column(u'remark', PGText(length=None, convert_unicode=False,
assert_unicode=None), table=participation, nullable=False,
server_default=DefaultClause(sqlalchemy.sql.expression._TextClause object at
0xb3b17cc, for_update=False))
c2.server_default
DefaultClause(sqlalchemy.sql.expression._TextClause object at 0xb3b17cc,
for_update=False)
Somehow in base_columns the server default is detected.
It is a little bit unclear to me, why the server_default is detected in c2, but
not in c.
In fact I used the base_columns now as a workaround for RUM, but it feels quite
hacky.
By the way, I simply *love* SA.
Moreover, I am very impressed,
that (while of course unable to use them) my postgresql db contains partial
indices.
That's a very good sign for the reflection code.
Cheers,
Michael
Am 15.04.2010 um 15:19 schrieb Michael Bayer:
On Apr 15, 2010, at 7:26 AM, Michael Brickenstein wrote:
On 15 Apr., 13:03, Michael Brickenstein brickenst...@mfo.de wrote:
Sorry, forgot to mention, I had the same behaviour with SA 0.5.6 and
0.5.8.
Cheers,
In former times, I used
prop.server_default to find out, that there is an default of ''.
Sorry, that's wrong it has always been the columns[0], that I have to
inspect.
Nevertheless, the question for an official way remains, so far the
situation is the following:
c.server_default
- None
c2=iter(c.base_columns).next()
- DefaultClause(sqlalchemy.sql.expression._TextClause object at
0xa2507ac, for_update=False)
Is that a bug or a feature?
server_default is where we put whatever server side defaults were detected
during reflection. They reflect as a sqlalchemy.schema.DefaultClause, arg
is a TextClause representing what was received, and calling str() on that
gives you the ultimate value.The TextClause is so that we can
differentiate it from a literal python-defined value that would require
quoting.
--
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] Re: postgresql column default
Hi! Thanks, that was very helpful for me. Cheers, Michael Am 15.04.2010 um 18:17 schrieb Michael Bayer: this column is against an alias, which suggests you've mapped to an alias() or select() of some kind. To get at full table metadata you need to be talking to Column objects that are directly present on a Table. -- 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] Re: Orphans not deleted using cascade parameter
and here is the new traceback ;-) Traceback (most recent call last): File foo.py, line 38, in module DBSession.flush() File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/scoping.py, line 106, in do return getattr(self.registry(), name)(*args, **kwargs) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/session.py, line 1409, in flush flush_context.execute() File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 265, in execute UOWExecutor().execute(self, tasks) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 753, in execute self.execute_save_steps(trans, task) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 771, in execute_save_steps self.execute_dependencies(trans, task, True) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 783, in execute_dependencies self.execute_dependency(trans, dep, True) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 765, in execute_dependency dep.execute(trans, isdelete) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 722, in execute self.processor.process_dependencies(self.targettask, [elem.state for elem in self.targettask.polymorphic_todelete_elements], trans, delete=True) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ dependency.py, line 181, in process_dependencies self._synchronize(state, child, None, True, uowcommit) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ dependency.py, line 251, in _synchronize sync.clear(dest, self.mapper, self.prop.synchronize_pairs) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/sync.py, line 28, in clear raise AssertionError(Dependency rule tried to blank-out primary key column '%s' on instance '%s' % (r, mapperutil.state_str(dest))) AssertionError: Dependency rule tried to blank-out primary key column 'project_programming_language.programming_language_id' on instance '[EMAIL PROTECTED]' --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Orphans not deleted using cascade parameter
Dear Michael! Thanks, I got it and understand the difference now. Thank you very much for your help and your time. Michael Am 04.09.2008 um 14:35 schrieb Michael Bayer: On Sep 4, 2008, at 4:15 AM, Michael Brickenstein wrote: AssertionError: Dependency rule tried to blank-out primary key column 'project_programming_language.programming_language_id' on instance '[EMAIL PROTECTED]' the project_programming_language table's primary key is programming_language_id, and this column is a foreign key to programming_language's primary key column. You can't delete a row from programming_language without also deleting the row from project_programming_language. Set cascade='all, delete-orphan' on the project_languages relation. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Orphans not deleted using cascade parameter
Hi!
Thanks for the answers.
I have some problems, reproducing it in a small piece of code.
It occurs here.
http://toscawidgets.org/trac/rum/ticket/31
I will provide you with details, when I have isolated the problems.
Michael
On 29 Aug., 17:06, Michael Bayer [EMAIL PROTECTED] wrote:
Unfortunately, without an illustration of your usage pattern, we
can't assist with your issue. Here's the same test case again from
earlier in the thread. Can you modify it to look like your failing
condition ?
rom sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
Base = declarative_base()
class PublicationElement(Base):
__tablename__ = 'publication'
publication_id = Column(Integer, primary_key=True)
name = Column(Unicode(255))
class SectionElement(Base):
__tablename__ = 'section'
section_id = Column(Integer, primary_key=True)
publication_id = Column(Integer,
ForeignKey('publication.publication_id'), nullable=False)
publication = relation('PublicationElement', cascade=all, delete-
orphan, backref='sections')
name = Column(Unicode(255))
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
sec1 = SectionElement(name='s1',
publication=PublicationElement(name='p1'))
sess = Session()
sess.add(sec1)
sess.commit()
assert sess.query(SectionElement).one().publication.name == 'p1'
sess.delete(sec1)
sess.commit()
assert engine.execute(select count(1) from publication).scalar() == 0
assert engine.execute(select count(1) from section).scalar() == 0
On Aug 29, 2008, at 1:44 AM, Michael Brickenstein wrote:
Hi!
Hi have a similar problem using
table reflection a la sqlsoup. My DB Backend ist postgresql 8.3.
I have a many to many relation:
orms5=# \d project_programming_language
Table public.project_programming_language
Column | Type | Modifiers
-+-+---
project_id | integer | not null
programming_language_id | integer | not null
Indexes:
projprogpkconstraint PRIMARY KEY, btree (project_id,
programming_language_id)
Foreign-key constraints:
programmierspracheconstraint FOREIGN KEY
(programming_language_id) REFERENCES
programming_language(programming_language_id) ON DELETE CASCADE
projektconstraint FOREIGN KEY (project_id) REFERENCES
project(project_id) ON DELETE CASCADE
I got the same message, when delete an object of the table
programming_language:
Dependency rule tried
to blank-out primary key column
As you can see, the foreign key constraints in the db work fine:
I can drop the row via a
DELETE
statement in sql.
Michael
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Orphans not deleted using cascade parameter
Hi! Hi have a similar problem using table reflection a la sqlsoup. My DB Backend ist postgresql 8.3. I have a many to many relation: orms5=# \d project_programming_language Table public.project_programming_language Column | Type | Modifiers -+-+--- project_id | integer | not null programming_language_id | integer | not null Indexes: projprogpkconstraint PRIMARY KEY, btree (project_id, programming_language_id) Foreign-key constraints: programmierspracheconstraint FOREIGN KEY (programming_language_id) REFERENCES programming_language(programming_language_id) ON DELETE CASCADE projektconstraint FOREIGN KEY (project_id) REFERENCES project(project_id) ON DELETE CASCADE I got the same message, when delete an object of the table programming_language: Dependency rule tried to blank-out primary key column As you can see, the foreign key constraints in the db work fine: I can drop the row via a DELETE statement in sql. Michael --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Orphans not deleted using cascade parameter
I forgot to mention, that I use 0.5.0beta 3. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
