[sqlalchemy] get_col_spec behavior has changed from 0.5.8 to 0.6.X
hi there, up to now I did use something like: c = tblCompany.__table__.columns COMPANY_FIELDS = [(k, c[k].type.get_col_spec()) for k in c.keys() if not k=='id'] to build some dynamic forms for a web application. now this breaks with an error: AttributeError: 'VARCHAR' object has no attribute 'get_col_spec' it seems to work still with MySQL but not with postgres. (maybe MySQL uses a slightly older version out of the 6.xx serie) what is the correct way to build a list of fields a table provides ? thanks robert -- 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] Custom type does not seem to honour is_mutable
I have created a custom type in order to store denormalized PKs in a TEXT field. The idea is that the text is converted back and forth from a set of integers: http://paste.pocoo.org/show/249784/ This seems to work OK, however if you make a change to the set it's not picked up by SQLAlchemy on commit. For example, given the following model: class Post(DeclarativeBase): __tablename__ = posts id = Column(Integer, primary_key=True) votes = Column(DenormalizedText) def __init__(self, *args, **kwargs): super(Post, self).__init__(*args, **kwargs) self.votes = self.votes or set() If I do this: post = Post() post.votes.add(3) session.add(post) session.commit() The value '3' is committed to the 'votes' column as expected. However if I then try to modify: post.votes.add(5) session.commit() The change to the set is not saved to the DB, i.e. it's still 3. -- 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] MySQL INSERT ... ON DUPLICATE KEY UPDATE
http://github.com/bedwards/sqlalchemy_mysql_ext -- 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] get_col_spec behavior has changed from 0.5.8 to 0.6.X
On Aug 13, 2010, at 8:19 AM, robert rottermann wrote: hi there, up to now I did use something like: c = tblCompany.__table__.columns COMPANY_FIELDS = [(k, c[k].type.get_col_spec()) for k in c.keys() if not k=='id'] to build some dynamic forms for a web application. now this breaks with an error: AttributeError: 'VARCHAR' object has no attribute 'get_col_spec' it seems to work still with MySQL but not with postgres. (maybe MySQL uses a slightly older version out of the 6.xx serie) what is the correct way to build a list of fields a table provides ? get_col_spec() was removed in 0.6 and replaced with the compiler structure. the string form of a type is available if you compile it against a dialect, like type.compile(dialect=engine.dialect). its not a great idea to rely upon the string form of a type for anything other than issuing SQL since it changes based on dialect involved and arguments. if you're needing a hash key or some kind of discriminator, use the type object or its class by itself. -- 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] Custom type does not seem to honour is_mutable
On Aug 13, 2010, at 10:01 AM, Dan wrote: I have created a custom type in order to store denormalized PKs in a TEXT field. The idea is that the text is converted back and forth from a set of integers: http://paste.pocoo.org/show/249784/ this is unrelated, but the code is incorrect there, should be def process(value): if value is not None: items = [str(item).strip() for item in value] value = self.separator.join(item for item in items if item) otherwise, you must implement copy_value() on your type. Here, the value isn't being copied so there's nothing to compare to. Usually you're supposed to mixin MutableType which will raise notimplemented for copy_value(). I guess still more docs are needed since you were misled by the is_mutable() method. This seems to work OK, however if you make a change to the set it's not picked up by SQLAlchemy on commit. For example, given the following model: class Post(DeclarativeBase): __tablename__ = posts id = Column(Integer, primary_key=True) votes = Column(DenormalizedText) def __init__(self, *args, **kwargs): super(Post, self).__init__(*args, **kwargs) self.votes = self.votes or set() If I do this: post = Post() post.votes.add(3) session.add(post) session.commit() The value '3' is committed to the 'votes' column as expected. However if I then try to modify: post.votes.add(5) session.commit() The change to the set is not saved to the DB, i.e. it's still 3. -- 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: Custom type does not seem to honour is_mutable
On Aug 13, 3:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 13, 2010, at 10:01 AM, Dan wrote: I have created a custom type in order to store denormalized PKs in a TEXT field. The idea is that the text is converted back and forth from a set of integers: http://paste.pocoo.org/show/249784/ this is unrelated, but the code is incorrect there, should be def process(value): if value is not None: items = [str(item).strip() for item in value] value = self.separator.join(item for item in items if item) otherwise, you must implement copy_value() on your type. Here, the value isn't being copied so there's nothing to compare to. Yes, sorry for the typo. Realized myself once I'd posted. Usually you're supposed to mixin MutableType which will raise notimplemented for copy_value(). I guess still more docs are needed since you were misled by the is_mutable() method. I've tried the same thing with the MutableType mixin with the same result, i.e: class DenormalizedText(types.TypeDecorator, types.MutableType): -- 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] MySQL INSERT ... ON DUPLICATE KEY UPDATE
nice job. I didn't think MySQL supported RETURNING ? that can all go away
if so. Also how do i specify an UPDATE clause distinct from the INSERT ? The
mysql docs seem to indicate this is possible (i.e. it would UPDATE c=c+1
instead of inserting 5 columns). Generative would be nice:
table.insert().values(a=1, b=2, c=3).on_duplicate_update(c=table.c.c + 1)
you'd be monkeypatching the Insert construct instead of Table with the
above, adding on_duplicate_update() that would copy the original Insert into
your construct. A default for everything might be on_duplicate_update('*').
MySQL is confusing here...there's REPLACE also and I always thought that was
their INSERT or UPDATE solution. This one seems much more useful.
On Aug 13, 2010, at 12:37 AM, brian wrote:
http://github.com/bedwards/sqlalchemy_mysql_ext
--
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: Custom type does not seem to honour is_mutable
On Aug 13, 2010, at 10:24 AM, Dan wrote:
On Aug 13, 3:17 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 13, 2010, at 10:01 AM, Dan wrote:
I have created a custom type in order to store denormalized PKs in a
TEXT field. The idea is that the text is converted back and forth from
a set of integers:
http://paste.pocoo.org/show/249784/
this is unrelated, but the code is incorrect there, should be
def process(value):
if value is not None:
items = [str(item).strip() for item in value]
value = self.separator.join(item for item in items if item)
otherwise, you must implement copy_value() on your type. Here, the value
isn't being copied so there's nothing to compare to.
Yes, sorry for the typo. Realized myself once I'd posted.
Usually you're supposed to mixin MutableType which will raise notimplemented
for copy_value(). I guess still more docs are needed since you were misled
by the is_mutable() method.
I've tried the same thing with the MutableType mixin with the same
result, i.e:
class DenormalizedText(types.TypeDecorator, types.MutableType):
MutableType would be first. But again this only just so the
NotImplementedError lets you know copy_value() is needed.I could make the
default copy_value() raise if is_mutable() is true...though it pains me to add
more method calls...
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *
Base = declarative_base()
metadata = Base.metadata
engine = create_engine('sqlite://', echo=True)
from sqlalchemy import types
class DenormalizedText(types.TypeDecorator):
Stores denormalized primary keys that can be
accessed as a set.
:param coerce: coercion function that ensures correct
type is returned
:param separator: separator character
impl = types.Text
def __init__(self, coerce=int, separator= , **kwargs):
self.coerce = coerce
self.separator = separator
super(DenormalizedText, self).__init__(**kwargs)
def bind_processor(self, dialect):
def process(value):
if value is not None:
items = [str(item).strip() for item in value]
value = self.separator.join(item for item in items if item)
return value
return process
def result_processor(self, dialect, coltype):
def process(value):
if not value:
return set()
return set(self.coerce(item) \
for item in value.split(self.separator))
return process
def copy_value(self, value):
return set(value)
def is_mutable(self):
return True
class Post(Base):
__tablename__ = posts
id = Column(Integer, primary_key=True)
votes = Column(DenormalizedText)
def __init__(self, *args, **kwargs):
super(Post, self).__init__(*args, **kwargs)
self.votes = self.votes or set()
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
post = Post()
post.votes.add(3)
session.add(post)
session.commit()
print ---
post.votes.add(5)
session.commit()
--
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: Custom type does not seem to honour is_mutable
Unfortunately still getting the same result:
http://paste.pocoo.org/show/249801/
The test snippet shows that the modified set is not actually saved to
the database.
On Aug 13, 3:29 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 13, 2010, at 10:24 AM, Dan wrote:
On Aug 13, 3:17 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 13, 2010, at 10:01 AM, Dan wrote:
I have created a custom type in order to store denormalized PKs in a
TEXT field. The idea is that the text is converted back and forth from
a set of integers:
http://paste.pocoo.org/show/249784/
this is unrelated, but the code is incorrect there, should be
def process(value):
if value is not None:
items = [str(item).strip() for item in value]
value = self.separator.join(item for item in items if item)
otherwise, you must implement copy_value() on your type. Here, the value
isn't being copied so there's nothing to compare to.
Yes, sorry for the typo. Realized myself once I'd posted.
Usually you're supposed to mixin MutableType which will raise
notimplemented for copy_value(). I guess still more docs are needed
since you were misled by the is_mutable() method.
I've tried the same thing with the MutableType mixin with the same
result, i.e:
class DenormalizedText(types.TypeDecorator, types.MutableType):
MutableType would be first. But again this only just so the
NotImplementedError lets you know copy_value() is needed. I could make the
default copy_value() raise if is_mutable() is true...though it pains me to
add more method calls...
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *
Base = declarative_base()
metadata = Base.metadata
engine = create_engine('sqlite://', echo=True)
from sqlalchemy import types
class DenormalizedText(types.TypeDecorator):
Stores denormalized primary keys that can be
accessed as a set.
:param coerce: coercion function that ensures correct
type is returned
:param separator: separator character
impl = types.Text
def __init__(self, coerce=int, separator= , **kwargs):
self.coerce = coerce
self.separator = separator
super(DenormalizedText, self).__init__(**kwargs)
def bind_processor(self, dialect):
def process(value):
if value is not None:
items = [str(item).strip() for item in value]
value = self.separator.join(item for item in items if item)
return value
return process
def result_processor(self, dialect, coltype):
def process(value):
if not value:
return set()
return set(self.coerce(item) \
for item in value.split(self.separator))
return process
def copy_value(self, value):
return set(value)
def is_mutable(self):
return True
class Post(Base):
__tablename__ = posts
id = Column(Integer, primary_key=True)
votes = Column(DenormalizedText)
def __init__(self, *args, **kwargs):
super(Post, self).__init__(*args, **kwargs)
self.votes = self.votes or set()
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
post = Post()
post.votes.add(3)
session.add(post)
session.commit()
print ---
post.votes.add(5)
session.commit()
--
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
athttp://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: Custom type does not seem to honour is_mutable
On Aug 13, 2010, at 10:45 AM, Dan wrote:
Unfortunately still getting the same result:
http://paste.pocoo.org/show/249801/
The test snippet shows that the modified set is not actually saved to
the database.
that code snippet is not complete (doesn't create a Session, doesn't add Post
to it, doesn't commit() or flush() the session but then removes it so I guess
maybe its a scoped_session, don't know) so I don't actually know what you're
doing. The test case below adds your assertion, uses the Session properly,
and works fine. The previous test I pasted also works (if I bothered to write
out a full test for it, you can be sure I ran it).
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *
Base = declarative_base()
metadata = Base.metadata
engine = create_engine('sqlite://', echo=True)
from sqlalchemy import types
class DenormalizedText(types.TypeDecorator):
Stores denormalized primary keys that can be
accessed as a set.
:param coerce: coercion function that ensures correct
type is returned
:param separator: separator character
impl = types.Text
def __init__(self, coerce=int, separator= , **kwargs):
self.coerce = coerce
self.separator = separator
super(DenormalizedText, self).__init__(**kwargs)
def bind_processor(self, dialect):
def process(value):
if value is not None:
items = [str(item).strip() for item in value]
value = self.separator.join(item for item in items if item)
return value
return process
def result_processor(self, dialect, coltype):
def process(value):
if not value:
return set()
return set(self.coerce(item) \
for item in value.split(self.separator))
return process
def copy_value(self, value):
return set(value)
def is_mutable(self):
return True
class Post(Base):
__tablename__ = posts
id = Column(Integer, primary_key=True)
votes = Column(DenormalizedText)
def __init__(self, *args, **kwargs):
super(Post, self).__init__(*args, **kwargs)
self.votes = self.votes or set()
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
post = Post()
assert post.votes == set([])
session.add(post)
session.commit()
post.votes.add(1)
assert 1 in post.votes
session.commit()
post_id = post.id
# close out transaction, session entirely, even
# though commit expires everything anyway
session.close()
post = session.query(Post).get(post_id)
assert 1 in post.votes
On Aug 13, 3:29 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 13, 2010, at 10:24 AM, Dan wrote:
On Aug 13, 3:17 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 13, 2010, at 10:01 AM, Dan wrote:
I have created a custom type in order to store denormalized PKs in a
TEXT field. The idea is that the text is converted back and forth from
a set of integers:
http://paste.pocoo.org/show/249784/
this is unrelated, but the code is incorrect there, should be
def process(value):
if value is not None:
items = [str(item).strip() for item in value]
value = self.separator.join(item for item in items if item)
otherwise, you must implement copy_value() on your type. Here, the value
isn't being copied so there's nothing to compare to.
Yes, sorry for the typo. Realized myself once I'd posted.
Usually you're supposed to mixin MutableType which will raise
notimplemented for copy_value(). I guess still more docs are needed
since you were misled by the is_mutable() method.
I've tried the same thing with the MutableType mixin with the same
result, i.e:
class DenormalizedText(types.TypeDecorator, types.MutableType):
MutableType would be first. But again this only just so the
NotImplementedError lets you know copy_value() is needed.I could make
the default copy_value() raise if is_mutable() is true...though it pains me
to add more method calls...
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *
Base = declarative_base()
metadata = Base.metadata
engine = create_engine('sqlite://', echo=True)
from sqlalchemy import types
class DenormalizedText(types.TypeDecorator):
Stores denormalized primary keys that can be
accessed as a set.
:param coerce: coercion function that ensures correct
type is returned
:param separator: separator character
impl = types.Text
def __init__(self, coerce=int, separator= , **kwargs):
self.coerce = coerce
self.separator = separator
super(DenormalizedText, self).__init__(**kwargs)
def bind_processor(self, dialect):
[sqlalchemy] Re: Custom type does not seem to honour is_mutable
that code snippet is not complete (doesn't create a Session, doesn't add Post to it, doesn't commit() or flush() the session but then removes it so I guess maybe its a scoped_session, don't know) so I don't actually know what you're doing. The test case below adds your assertion, uses the Session properly, and works fine. The previous test I pasted also works (if I bothered to write out a full test for it, you can be sure I ran it). Sorry for lack of context - had to strip out test from a whole lot of other code - not an excuse I know. There must be some other issue here, I'll investigate further and let you know. -- 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: Custom type does not seem to honour is_mutable
The issue appeared to be removing the session instance (session.remove()) - I was trying to get a clean session for testing. Anyway, works fine now - thanks for your help and sorry for wasting your time. On Aug 13, 4:21 pm, Dan danjac...@gmail.com wrote: that code snippet is not complete (doesn't create a Session, doesn't add Post to it, doesn't commit() or flush() the session but then removes it so I guess maybe its a scoped_session, don't know) so I don't actually know what you're doing. The test case below adds your assertion, uses the Session properly, and works fine. The previous test I pasted also works (if I bothered to write out a full test for it, you can be sure I ran it). Sorry for lack of context - had to strip out test from a whole lot of other code - not an excuse I know. There must be some other issue here, I'll investigate further and let you know. -- 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: Custom type does not seem to honour is_mutable
hey no problem glad you're back in business. On Aug 13, 2010, at 11:35 AM, Dan wrote: The issue appeared to be removing the session instance (session.remove()) - I was trying to get a clean session for testing. Anyway, works fine now - thanks for your help and sorry for wasting your time. On Aug 13, 4:21 pm, Dan danjac...@gmail.com wrote: that code snippet is not complete (doesn't create a Session, doesn't add Post to it, doesn't commit() or flush() the session but then removes it so I guess maybe its a scoped_session, don't know) so I don't actually know what you're doing. The test case below adds your assertion, uses the Session properly, and works fine. The previous test I pasted also works (if I bothered to write out a full test for it, you can be sure I ran it). Sorry for lack of context - had to strip out test from a whole lot of other code - not an excuse I know. There must be some other issue here, I'll investigate further and let you know. -- 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: How to link one table to itself?
I've tried many things and I always get similar errors. I'm sure all the foreign keys are OK. Could not determine relationship direction for primaryjoin condition 'users.id = user_channels.user_id', on relationship User.channels. Do the columns in 'foreign_keys' represent only the 'foreign' columns in this join condition ?. This error came up from another table and all the tables are working properly without this relation. Almost all the tables are related to each other. Maybe, this relation affects the rest of them in some way. Any idea of How I can solve this problem? Thanks! On Aug 12, 8:36 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 5:43 PM, Alvaro Reinoso wrote: I'm trying that, but I got different error: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. As I can see, the error is related to the users table which I don't use in this class. However, user table is related to media_groups. This is the user's table: class User(rdb.Model): Represents the user rdb.metadata(metadata) rdb.tablename(users) id = Column(id, Integer, primary_key=True) name = Column(name, String(50)) email = Column(email, String(50)) channels = relationship(Channel, secondary=user_channels, order_by=Channel.titleView, backref=users) mediaGroups = relationship(MediaGroup, secondary=user_media_groups, order_by=MediaGroup.title, backref=users) Do I need to add something else to that table? user_media_groups needs to have a ForeignKey that points to users. Thanks!!! On Aug 12, 5:15 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 5:09 PM, Michael Bayer wrote: On Aug 12, 2010, at 4:58 PM, Alvaro Reinoso wrote: I'm still working on the solution. I've found out some stuff in internet. I guess I'm close to, but I haven't got it yet. I'm using this for the relation: mediaGroups = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref='media_groups', foreign_keys = [media_group_groups.groupA_id, media_group_groups.groupB_id], primaryjoin = MediaGroup.id == media_group_groups.groupA_id, secondaryjoin = MediaGroup.id == media_group_groups.groupB_id) I'm playing with the parameters, but I usually get this error: ArgumentError: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. media_group_groups is not available when primaryjoin is evaluated as a string, nor within foreign_keys which is not necessary here since your meta_group_groups already has ForeignKey objects on it, so use a non-string format for primaryjoin. i will add additional examples to the declarative docs. scratch part of that, tablenames are available in the string eval as long as they're from the same MetaData: mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups, primaryjoin=MediaGroup.id==media_group_groups.c.groupA_id, secondaryjoin=MediaGroup.id==media_group_groups.c.groupB_id also the error for the exampe you have above should be 'Table' object has no attribute 'groupA_id'. Thank you! On Aug 12, 1:08 pm, Alvaro Reinoso alvrein...@gmail.com wrote: Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when
Re: [sqlalchemy] How to link one table to itself?
On Aug 12, 2010, at 1:08 PM, Alvaro Reinoso wrote: Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when I add any element to it. Any idea??? Thanks in advance! because you're having a particularly large amount of difficulty here, I'd very much like to determine what about the documentation or behavior of relationships is continuously leading you down the wrong path - this becuase I myself am not familiar with that many ways to get an error without eventually hitting upon the right solution. Ive taken the effort to reconstruct every table and relationship you've expressed in this thread. Below you will see an example of everything you've expressed, pretty much using code snippets you've already illustrated plus reconstructions, leading into a persistence scenario that inserts into all seven tables. I did not come across any of the issues you describe except the initial one expressing that primary/secondaryjoin is needed for MediaGroup.mediaGroup. Please start by running it as is, against the given sqlite database, so you can see how it works. Then, I need you to determine what about your setup is different than this, which would pinpoint the source of the issue- you do this by blending the code below with your app, changing one part at a time until the difference between emitting the error and working properly is localized. Then, I need to know how to detect the mistake you've been making and to raise a more informative error message. The foreign_keys argument is virtually never needed and at the very least I think I am going to remove the mention of that argument from the message. Let me know what you come up with. from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import * Base = declarative_base() metadata = Base.metadata media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) user_media_groups = Table( user_media_groups, metadata, Column(user_id, Integer, ForeignKey(users.id)), Column(media_group_id, Integer, ForeignKey(media_groups.id)), ) user_channels = Table( user_channels, metadata, Column(user_id, Integer, ForeignKey(users.id)), Column(channel_id, Integer, ForeignKey(channel.id)) ) media_group_channels = Table( media_group_channels, metadata, Column(media_group_id, Integer, ForeignKey(media_groups.id)), Column(channel_id, Integer, ForeignKey(channel.id)) ) class Channel(Base): __tablename__ = 'channel' id = Column(id, Integer, primary_key=True) titleView = Column(title, String(100)) class MediaGroup(Base): __tablename__ = media_groups id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, primaryjoin=id==media_group_groups.c.groupA_id, secondaryjoin=id==media_group_groups.c.groupB_id, backref=media_groups) class User(Base): __tablename__ = users id = Column(id, Integer, primary_key=True) name =
[sqlalchemy] Re: SQLite: Rolling back DDL requests
On Jun 24, 1:23 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 24, 2010, at 12:35 PM, Torsten Landschoff wrote:
That's what I thought but it does not cure my problem.
e.raw_connect().isolation_levelis in fact None, but the rollback is not
done anyway. :-(
its passing it through.dont know what else we can do there
I ran into this myself today and after struggling for a few hours I
came across this thread. I then ended up creating a test case to
reproduce the problem and it suggests the problem stems from some
behaviour of transactions or of the engine.base.Connection class. I
don't quite know what to make of it yet, but I think it shows that the
effect of passing it through is being counteracted by something
else. I ran this on Python 2.6.5 with SA 0.6.3 on Windows.
'''Test showing unexpected behaviour with sqlalchemy and sqlite3 and
isolation_level settings. The test creates one table outside of
a transaction (to detect potential problems with the test not
executing
properly) then creates a second table inside a transaction which it
immediately rolls back.
test01() fails basically as expected, since the sqlite3 DBAPI layer
appears to need isolation_level==None to properly roll back DDL
statements.
test02() succeeds because isolation_level==None now. This test and
test01()
both use a connection from engine.raw_connection(), which is a
sqlalchemy.pool._ConnectionFairy() object.
test03() tries again with isolation_level==None but using a
transaction
created from a connection returned by engine.connect(), which is a
sqlalchemy.engine.base.Connection() object. This test fails in spite
of the isolation_level setting.
'''
import unittest
from sqlalchemy import create_engine
DBPATH = 'sqlite://'
DDL = 'create table %s (id integer primary key)'
class TestCase(unittest.TestCase):
def setup(self, **kwargs):
self.engine = create_engine(DBPATH, **kwargs)
self.engine.execute(DDL % 'foo')
def rollback_raw(self):
conn = self.engine.raw_connection()
try:
conn.execute('begin')
conn.execute(DDL % 'bar')
raise ValueError
except ValueError:
conn.rollback()
def rollback_txn(self):
conn = self.engine.connect()
try:
txn = conn.begin()
conn.execute(DDL % 'bar')
raise ValueError
except ValueError:
txn.rollback()
def get_table_names(self):
conn = self.engine.raw_connection()
return [x[0] for x in conn.execute('select name from
sqlite_master')]
def test01(self):
'''use engine with default isolation_level'''
self.setup()
self.rollback_raw()
self.assertEqual(['foo'], self.get_table_names())
def test02(self):
'''use raw_connection with isolation_level None'''
self.setup(connect_args={'isolation_level': None})
self.rollback_raw()
self.assertEqual(['foo'], self.get_table_names())
def test03(self):
'''use transaction with isolation_level None'''
self.setup(connect_args={'isolation_level': None})
self.rollback_txn()
self.assertEqual(['foo'], self.get_table_names())
if __name__ == '__main__':
unittest.main()
--
Peter Hansen
Engenuity Corporation
--
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.
