Re: [sqlalchemy] textcolumn.contains() escaping, the lack of icontains(), and possible bug?
Converting strings to lower case and comparing them is not the same as a true case-insensitive comparison. Python 3.3 adds a str.casefold method for this reason. The docs for that method give a good explanation of the distinction: Casefolding is similar to lowercasing but more aggressive because it is intended to remove all case distinctions in a string. For example, the German lowercase letter 'ß' is equivalent to ss. Since it is already lowercase, lower() would do nothing to 'ß'; casefold() converts it to ss. The casefolding algorithm is described in section 3.13 of the Unicode Standard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Relationship configuration that isn't functioning as I expect
I am trying to create a relationship to work with a legacy schema and am
having trouble configuring it to behave as I want. As a disclaimer, I
understand why the schema here is not the schema one would use if starting
from scratch.
Anyway, I have simplied the situation down to the following example:
I have users and have two different types of objects that users can be
related to. For this example, those are street addresses and email
addresses. Rather than having a secondary table for each relationship,
there is a single secondary table. It has a foreign key column that will
either function as a foreign key to the street addresses table or the email
addresses table. There is a discriminator column that indicates which of
those tables is being referenced. The secondary table has its own primary
key and has an associated mapped class. The problem I am trying to solve is
setting up a relationship between the secondary table and the address
tables.
This is what I have ended up with:
class Affiliation(Base):
__tablename__ = 'affiliations'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
address_type = Column(String, nullable=False)
address_id = Column(Integer, nullable=False)
user = relationship(User, backref='affiliations')
email_address = relationship(
'EmailAddress',
primaryjoin=and_(address_type == 'email',
address_id == EmailAddress.id),
foreign_keys=[address_id],
viewonly=True)
street_address = relationship(
'StreetAddress',
primaryjoin=and_(address_type == 'street',
address_id == StreetAddress.id),
foreign_keys=[address_id],
viewonly=True)
The users, email_addresses and street_addresses tables/ classes are trivial
so I am omitting them from this post, but I have a full working example
here:
https://gist.github.com/bobbyi/5593984
For a given affiliation, I want affiliation.email_address to be
the appropriate email address if this affiliation's type is 'email'.
Otherwise, it should be None.
Given the following:
affiliation = session.query(Affiliation).get(1)
print affiliation.email_address
The SQL generated by the second line is:
SELECT email_addresses.id AS email_addresses_id, email_addresses.address AS
email_addresses_address
FROM email_addresses, affiliations
WHERE affiliations.address_type = 'email' AND 1 = email_addresses.id
Note that this is a cartesian join.
I hoped to end up with something like:
SELECT email_addresses.id AS email_addresses_id, email_addresses.address AS
email_addresses_address
FROM email_addresses, affiliations
WHERE affiliations.address_type = 'email' AND affiliations.address_id =
email_addresses.id AND affiliations.id = 1
Two questions:
1) Is the behavior I am seeing here expected given my relationship
configuration?
2) How can I configure my relationship to exhibit the desired behavior in
the situation shown above?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: SQLServer + Datetimes returning odd results?
I think this value is coming from the underlying database driver rather
than SQLAlchemy. If you execute the stored proc directly using the driver
(I guess this is pymssql), do you see the same behavior?
On Thursday, May 16, 2013 12:37:56 PM UTC-7, John Anderson wrote:
I have a SQLServer DB with a table that has a column as datetime and its
default value is `getutcdate()` (on the server).
We are using stored procedures and are running DBSession.execute('sproc')
and to do a select on the table and the rows that are returned have weird
datetime values:
datetime.datetime(2013, 16, 136, 29, 231, 4, 3888128)
It seems be be completely missing its month. In the db the value is:
2013-05-16 19:29:29.487
Any ideas what might cause this?
It is currently using SQLAlchemy 0.7.6
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] textcolumn.contains() escaping, the lack of icontains(), and possible bug?
Sounds like a useful feature.
Regarding case sensitivity, perhaps it would better if each of these
methods (even like() and contains()) took a keyword argument along the
lines of col.endswith('foo', case_sensitive=False) rather than adding extra
methods with weird names like iendswith.
On Monday, May 13, 2013 3:44:38 PM UTC-7, Michael Bayer wrote:
On May 13, 2013, at 6:30 PM, Daniel Grace thisgen...@gmail.comjavascript:
wrote:
So today I identified a small bug in my code and then, while trying to
resolve it, came to a few realizations:
1. column.contains(str) does not escape characters in str such as % and _.
Presumably, column.startswith(str) and column.endswith(str) have the same
behavior.
this will be called autoescape and is ticket 2694:
http://www.sqlalchemy.org/trac/ticket/2694 .if someone wants to work
on a patch for this it would be v. helpful. It's a little late to turn on
the escaping for all users now as it would break existing workarounds.
2. There is a distinct lack of column.icontains(str), though the current
implementation means it's identical to column.ilike('%' + str + '%')
since we do have ilike() as an operator icontains() would be
appropriate at this point (also startswith,endswith).
3. There is no builtin function (that I found, please correct me if I'm
wrong!) for escaping a string being passed to any functions in this family.
will be 2694
While I think that column.like and column.ilike should definitely /not/
escape their argument (you know you're trying for a pattern match here, and
that you're matching against a pattern), I think that the
.contains/.startswith/.endswith family of functions probably should perform
this escaping transparently. Between DBAPI 2.0, SQLAlchemy and
parameterized querying I don't need to worry about escaping input, so why
should I have to pay attention to that detail when using .contains? Also,
case insensitive versions of the above would probably be useful.
That said, a proper fix might be complicated since it could inadvertently
break existing code that relies on the current behavior of .contains()
-- Daniel
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Relationship configuration that isn't functioning as I expect
That makes sense. I am seeing one problem with this approach and it causes
the asserts in original gist to still fail. The problem is that the
identity map remembers that I constructed my objects as instances of the
parent class even across commit and expire boundaries. So the following
assert fails:
street_affiliation = Affiliation(user=user, address_type='street',
address_id=1)
session.add(street_affiliation)
session.flush()
session.commit()
street_affiliation =
session.query(Affiliation).filter_by(address_type='street').one()
assert isinstance(street_affiliation, StreetAffiliation)
And as a result the asserts in the original gist still fail.
I understand why this happens (the identity map uses weakrefs but there is
a still a reference to the old object of type Affiliation) and how to work
around it (delete the reference to the old affiliation before querying or
construct the affiliation object as a StreetAffiliation instead of as an
Affiliation).
It is still somewhat surprising since I am used to assuming that once a
commit/ expire_all has happened, fetching from the database will give the
right results, but in this case the affiliation I fetch after the
flush incorrectly tells me that its street_address is None.
However, I don't see this being a problem in production and I think this
will work for our application. Thanks.
On Thursday, May 16, 2013 12:49:17 PM UTC-7, Michael Bayer wrote:
On May 16, 2013, at 3:02 PM, Bobby Impollonia
bob...@gmail.comjavascript:
wrote:
I hoped to end up with something like:
SELECT email_addresses.id AS email_addresses_id,
email_addresses.address AS email_addresses_address
FROM email_addresses, affiliations
WHERE affiliations.address_type = 'email' AND affiliations.address_id =
email_addresses.id AND affiliations.id = 1
Two questions:
1) Is the behavior I am seeing here expected given my relationship
configuration?
2) How can I configure my relationship to exhibit the desired behavior
in the situation shown above?
Ok well lets think of it in terms of joins. What if you wanted to load
all the affiliations and EmailAddresses together? the join would be:
select * from affiliations JOIN email_addresses ON affiliations.address_id=
email_addresses.id AND affiliations.address_type='email'
above, there's not really a space for AND affiliations.id = ...something
?, unless maybe if it were equated to itself.
So maybe, this kind of thing would be possible if you could say:
email_address = relationship(
'EmailAddress',
primaryjoin=and_(address_type == 'email',
id == lazy(id),
address_id == EmailAddress.id),
foreign_keys=[address_id],
viewonly=True)
the lazy load would need to figure out to set up a bind for one of the
slot there (that's the hypothetical lazy() annotation). its funny we're
a lot closer to that sort of thing, since we do have situations where we
have things like column == remote(column) now, but not quite in that
arrangement.
But I don't think we need to get into any of that here since your class
has a discriminator anyway, we can just use inheritance so that your
different Affiliation objects know what to do, see below.
#!/usr/bin/env python
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine,
and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref,
contains_eager, remote
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
class StreetAddress(Base):
__tablename__ = 'steet_addresses'
id = Column(Integer, primary_key=True)
address = Column(String, nullable=False)
class EmailAddress(Base):
__tablename__ = 'email_addresses'
id = Column(Integer, primary_key=True)
address = Column(String, nullable=False)
class Affiliation(Base):
__tablename__ = 'affiliations'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'), nullable = False)
address_type = Column(String, nullable=False)
address_id = Column(Integer, nullable=False)
user = relationship(User, backref='affiliations')
street_address = None
email_address = None
__mapper_args__ = dict(polymorphic_on=address_type)
class EmailAffiliation(Affiliation):
email_address = relationship(
'EmailAddress',
primaryjoin=Affiliation.address_id == EmailAddress.id,
foreign_keys=[Affiliation.address_id],
viewonly=True)
__mapper_args__ = dict(polymorphic_identity='email')
class StreetAffiliation(Affiliation):
street_address = relationship
Re: [sqlalchemy] contains_eager and ordering
Thanks! Expiring the collection before querying worked. The orderinglist extension looks interesting as well. My current use case is that I wanted to apply the ordering on a per-query basis, so I was specifically looking for a solution that didn't involve changing the relationship definition, however that might be useful in the future. On Tuesday, May 7, 2013 8:09:53 AM UTC-7, Michael Bayer wrote: the User.addresses collection will not refresh itself unless the collection is expired. At the point of your flush, each User.addresses is already populated with the unordered Address objects.If you either Session.commit(), or Session.expire_all(), or Session.expire(user, ['addresses']), then they will reload in the correct order. An extension we have to ensure that ordering of items is also synchronized on the Python side is orderinglist: http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/orderinglist.htmland it works pretty well when you aren't dealing with the primary key. On May 7, 2013, at 1:03 AM, Bobby Impollonia bob...@gmail.comjavascript: wrote: I am executing a query with contains_eager to load objects and their related objects from a different table. I would like to control the order of the related objects within each InstrumentedList. I had hoped this could be done through the ordering in the query. For example, with a query like: session.query(User).outerjoin(User.addresses).order_by(Users.id, Address.name, Address.more).options(contains_eager(User.addresses)) I had hoped then when I looped over each user, and then looped over user.addresses, I would find the addresses were sorted by (name, more). However, this does not seem to be the case. I have a full example here: https://gist.github.com/bobbyi/5530250 Is there a way that I can get the objects to keep the ordering from the database? I want to use the collation types, etc., as defined in the database so I'd rather avoid sorting the items again in Python. Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] contains_eager and ordering
I am executing a query with contains_eager to load objects and their related objects from a different table. I would like to control the order of the related objects within each InstrumentedList. I had hoped this could be done through the ordering in the query. For example, with a query like: session.query(User).outerjoin(User.addresses).order_by(Users.id, Address.name, Address.more).options(contains_eager(User.addresses)) I had hoped then when I looped over each user, and then looped over user.addresses, I would find the addresses were sorted by (name, more). However, this does not seem to be the case. I have a full example here: https://gist.github.com/bobbyi/5530250 Is there a way that I can get the objects to keep the ordering from the database? I want to use the collation types, etc., as defined in the database so I'd rather avoid sorting the items again in Python. Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Backref confusion
Thank you! That is exactly the function I needed. On Tuesday, November 13, 2012 9:16:47 PM UTC-8, Michael Bayer wrote: On Nov 13, 2012, at 11:03 PM, Bobby Impollonia wrote: I have constructed a sample program consisting of two mapped classes (using sqlalchemy.ext.declarative) that have a relationship/ backref between them. At runtime the program does the following: 1) Print whether the parent class has an attribute for its relationship to the child (declared as the backref) 2) Construct a child object 3) Repeat step 1 The result (with SQLA 0.7.9) is that it prints 'False' during step 1 and then 'True' during step 3. I would expect True to be printed both times. Here is the full source of the program: https://gist.github.com/4070161 Why does the property not exist when the first print statement executes? addresses is generated on the Person class when the mappers enter the configuration step, which is an automatically invoked process which occurs when a mapping is first used. this process is deferred until a point at which it's safe to assume all mappings are present, so that relationship() directives, which refer to other mappings, can proceed to reconcile the mappings they point to - otherwise by definition one of the mappings/classes (if using declarative) doesn't exist yet for relationship/backref. the process can be manually invoked via configure_mappers(): if __name__ == '__main__': from sqlalchemy.orm import configure_mappers configure_mappers() print hasattr(Person, 'addresses') Address() print hasattr(Person, 'addresses') Thanks for any guidance. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/SRZzsLt7qb0J. To post to this group, send email to sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. 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 view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/-lPqaGoaWcYJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] ObjectDeletedError when query.delete() hits an expired item
Hi. With SQLA 0.6.6, the program below fails on the last line with
ObjectDeletedError. Is this expected or a bug?
from sqlalchemy import create_engine, MetaData, Column, Unicode
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///:memory:')
metadata = MetaData(bind = engine)
session = sessionmaker(bind = engine)()
Base = declarative_base(metadata = metadata)
class Entity(Base):
__tablename__ = 'entity'
name = Column(Unicode(128), primary_key = True)
metadata.create_all()
e = Entity(name = u'hello')
session.add(e)
session.flush()
session.expire(e)
session.query(Entity).filter_by(name = u'hello').delete()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Warning: Incorrect datetime value
I am constructing queries involving MSTimeStamp fields in MySQL and am being receiving Incorrect datetime value warnings even in situations where my queries are valid in MySQL. How do I modify the following query so that sqlalchemy will accept it without warning: session.query(MyClass.id).filter(MyClass.timestamp (func.now() - 2)).first() ? The generated sql is SELECT my_table.id AS my_table_id FROM my_table WHERE my_table.timestamp now() - %s LIMIT 0, 1 The warning I get is /usr/lib/pymodules/python2.5/sqlalchemy/engine/default.py:123: Warning: Incorrect datetime value: '20100209953011.00' for column 'timestamp' at row 1 cursor.execute(statement, parameters) Why is sqlalchemy even seeing the value '20100209953011.00', which is the outcome of the now() - 2 operation inside the query? I tried changing (func.now() - 2) to func.timestamp (func.now() - 2) but I still get the same warning. If I change 2 to 200, I don't get warned. Why would this be? It works with no warning if I change it to session.query(MyClass.id).filter(func.now() - MyClass.timestamp 2).first() , but that is not an acceptable solution because now MySQL can't use my index on MyClass.timestamp. I am using SQLA .5.5 -- 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] Warning: Incorrect datetime value
I am constructing queries involving MSTimeStamp fields in MySQL and am being receiving Incorrect datetime value warnings even in situations where my queries are valid in MySQL. How do I modify the following query so that sqlalchemy will accept it without warning: session.query(MyClass.id).filter(MyClass.timestamp (func.now() - 2)).first() ? The generated sql is SELECT my_table.id AS my_table_id FROM my_table WHERE my_table.timestamp now() - %s LIMIT 0, 1 The warning I get is /usr/lib/pymodules/python2.5/sqlalchemy/engine/default.py:123: Warning: Incorrect datetime value: '20100209953011.00' for column 'timestamp' at row 1 cursor.execute(statement, parameters) Why is sqlalchemy even seeing the value '20100209953011.00', which is the outcome of the now() - 2 operation inside the query? I tried changing (func.now() - 2) to func.timestamp (func.now() - 2) but I still get the same warning. If I change 2 to 200, I don't get warned. Why would this be? It works with no warning if I change it to session.query(MyClass.id).filter(func.now() - MyClass.timestamp 2).first() , but that is not an acceptable solution because now MySQL can't use my index on MyClass.timestamp. I am using SQLA .5.5 -- 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: Multi table select?
You can also create a view mapped to that union and use that as a virtual table so that you don't have to repeat the union specification for every query: http://www.w3schools.com/Sql/sql_view.asp I don't know if that helps for SQLAlchemy though. On Mon, Oct 26, 2009 at 5:59 PM, AF allen.fow...@yahoo.com wrote: On Oct 26, 8:48 pm, AF allen.fow...@yahoo.com wrote: Hello, I don't know if this is even possible is SQL, so please bear with me :) There are a couple a tables (say, a b) that are used as logs for two different processes. They both have the same simple structure. (id, time_stamp, user_id, message) I would like to create a query that merges the data and returns following results: time_stamp, user_id, a_or_b, message (where a_or_b is a value that indicates which table the data row came from) Can this be done in SQL/SQLAlchemy. Thank you, :) p.s. Alternatively, the message columns do not need to be merged though I guess time_stamp / user would still need to be. That is: time_stamp, user_id, message_a, message_b I don't know if that makes any easier... OK: http://www.w3schools.com/Sql/sql_union.asp Doh. OK, so now I have an SQL statement I wrote by hand that works fine, but I still have two questions: 1) Can this be done via the SQA ORM? 2) If not, how should I I build this using non-ORM SQA? Thank you, :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: aggregation with count and webhelpers.paginate
You could also use a label to avoid the repetition:
from sqlalchemy.sql import desc
meta.Session.query(m.Hit.referer, func.count(m.Hit.id).label('count'))\
.group_by(m.Hit.referer)\
.order_by(desc('count'))
On Mon, Jun 22, 2009 at 2:22 AM, King
Simon-NFHD78simon.k...@motorola.com wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Hollister
Sent: 20 June 2009 02:15
To: sqlalchemy
Subject: [sqlalchemy] Re: aggregation with count and
webhelpers.paginate
Well, that worked great:
q = meta.Session.query(m.Hit.referer, func.count(m.Hit.id))\
.group_by(m.Hit.referer)\
.order_by(func.count(m.Hit.id).desc())
Thanks!
ps: Is there a better way to specify the count in the order_by?
If it's just that you don't like repeating yourself, you should be able
to save the result of func.count (untested):
hit_count = func.count(m.Hit.id)
q = (meta.Session.query(m.Hit.referer, hit_count)
.group_by(m.Hit.referer)
.order_by(hit_count.desc())
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Random value for field by default?
default = lambda: random.randrange(1000,1) On Sun, Jun 21, 2009 at 1:32 PM, AF allen.fow...@yahoo.com wrote: Hello, Perhaps this is more of a Python question that SQLalchemy.. but... How can I assign a random number to a DB field by default? I tried: default = random.randrange(1000,1) on the table definition, but I get the same number each time? Ideas? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Random value for field by default?
Yes, it is safe. Python's underlying random number generation is threadsafe. There is no need to create a new RNG each time to generate a single number. On Sun, Jun 21, 2009 at 1:53 PM, allen.fowlerallen.fow...@yahoo.com wrote: default = lambda: random.randrange(1000,1) Seems we crossed in the interwebs.. :) Is it safe to do this, or do you need to do default = lambda: random.Random()randrange(1000,1) ? I ask since I have several tables that this needs to be applied to. Thank you --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Relation Bug in SA?
The error is complaining about the backref (on relation
Royalty.owner), so it makes sense that it would go away if you remove
the backref.
The error says that you need to specify foreign_keys for the backref,
so you should try that. i.e, change
backref='owner'
to
backref=backref('owner', foreign_keys = [owners_table.c.owner_id])
On Fri, Jun 12, 2009 at 12:31 PM, Affectahmed.alsa...@gmail.com wrote:
Hello:
When I use the foreign_keys argument to the relation function in the
mapper of SA, I get the following error:
ArgumentError: Could not determine relation direction for primaryjoin
condition 'drm_owners.owner_id = drm_contract_royalties.contract_id',
on relation Royalty.owner. Specify the 'foreign_keys' argument to
indicate which columns on the relation are foreign.
This error only shows when the 'backref' argument is specified and not
otherwise! Is this a bug in alchemy or am I missing something?
Here's the mapper conf:
===
mapper(Royalty, royalties_table)
mapper(Owner, owners_table,
properties = {
'works': relation(Work, backref='owner'),
'royalty': relation(Royalty,
primaryjoin=owners_table.c.owner_id==royalties_table.c.contract_id,
foreign_keys=[royalties_table.c.contract_id],
backref='owner')
})
So, if I remove the 'backref' argument from the call to 'relation',
the relation works, but of course I lose the backref 'owner'.
Thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Small note on reading SA docs
otherwise if you have any advice on how to get 0.4/0.3 delisted from such a prominent place on Google, that would be appreciated. The simplest thing to do is to append: Disallow: /docs/04/ Disallow: /docs/03/ to the file: http://www.sqlalchemy.org/robots.txt This tells google (and all well-behaved search engines) not to index those urls (and anything under them). The next time the googlebot comes through, it will see the new robots.txt and remove those pages from its index. This will take a couple weeks at most. You can learn more about robots.txt here: http://www.robotstxt.org/ The disadvantage to doing it that way is that you will lose the google juice (pagerank) for inbound links to the old documentation. An alternative approach that gets around this to use a link rel=canonical ... tag in the head of each page of the 04 and 03 documentation pointing to the corresponding page of 05 documentation as its canonical url. By doing this, you are claiming that the 04/ 03 documentation pages are duplicates of the corresponding 05 pages. Google juice from inbound links to an old documentation page will accrue to the appropriate 05 documentation page instead. However, strictly speaking, the different versions aren't quite duplicates, so you might be pushing the boundaries of what is allowed a bit by claiming they are. Here is more info on rel=canonical from google: http://googlewebmastercentral.blogspot.com/2009/02/specify-your-canonical.html A similar approach would be to do a 301 redirect from each old documentation page to the corresponding 05 documentation page, but only if the visitor is the googlebot. This is straightforward to implement with mod_rewrite (the googlebot can be recognized by its user-agent string), but probably a bad idea since google usually considers it cloaking to serve different content to the googlebot than to regular visitors. You should also consider submitting an XML sitemap to google via the google webmaster tools. This allows you to completely spell out for them the structure of the site and what you want indexed. I also noticed that your current robots.txt file disallows indexing of anything under /trac/. It would nice to let google index bugs in trac so that someone who searches google for sqlalchemy help can come across an extant bug describing their problem. In addition, you have links on the front page (changelog and what's new) that go to urls under /trac/ , so google will not follow those links due to your robots.txt. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Small note on reading SA docs
Rereading what you posted, by such a prominent place on Google, did you mean specifically the .4 and .3 links that show up below sqlalchemy when www.sqlalchemy.org is returned in the search results? Those are what google calls sitelinks. You can tell them not to use certain pages as sitelinks via the google webmaster tools. They'll remove them, but they don't get replaced so you will have two fewer sitelinks then. On Thu, May 21, 2009 at 8:03 PM, Bobby Impollonia bob...@gmail.com wrote: otherwise if you have any advice on how to get 0.4/0.3 delisted from such a prominent place on Google, that would be appreciated. The simplest thing to do is to append: Disallow: /docs/04/ Disallow: /docs/03/ to the file: http://www.sqlalchemy.org/robots.txt This tells google (and all well-behaved search engines) not to index those urls (and anything under them). The next time the googlebot comes through, it will see the new robots.txt and remove those pages from its index. This will take a couple weeks at most. You can learn more about robots.txt here: http://www.robotstxt.org/ The disadvantage to doing it that way is that you will lose the google juice (pagerank) for inbound links to the old documentation. An alternative approach that gets around this to use a link rel=canonical ... tag in the head of each page of the 04 and 03 documentation pointing to the corresponding page of 05 documentation as its canonical url. By doing this, you are claiming that the 04/ 03 documentation pages are duplicates of the corresponding 05 pages. Google juice from inbound links to an old documentation page will accrue to the appropriate 05 documentation page instead. However, strictly speaking, the different versions aren't quite duplicates, so you might be pushing the boundaries of what is allowed a bit by claiming they are. Here is more info on rel=canonical from google: http://googlewebmastercentral.blogspot.com/2009/02/specify-your-canonical.html A similar approach would be to do a 301 redirect from each old documentation page to the corresponding 05 documentation page, but only if the visitor is the googlebot. This is straightforward to implement with mod_rewrite (the googlebot can be recognized by its user-agent string), but probably a bad idea since google usually considers it cloaking to serve different content to the googlebot than to regular visitors. You should also consider submitting an XML sitemap to google via the google webmaster tools. This allows you to completely spell out for them the structure of the site and what you want indexed. I also noticed that your current robots.txt file disallows indexing of anything under /trac/. It would nice to let google index bugs in trac so that someone who searches google for sqlalchemy help can come across an extant bug describing their problem. In addition, you have links on the front page (changelog and what's new) that go to urls under /trac/ , so google will not follow those links due to your robots.txt. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] relation that works in .48 but fails in .53
I am porting some code from SQLA .48 to .53 . I have a relation that
works in .48, but not in .53.
At the end of this post is a test program demonstrating the problem.
The program works in .48 but fails in .53 trying to understand the
child1_object relation . The error message says to add foreign_keys to
the relation, but that doesn't seem to actually help.
It does however work if I change the relation to
child1_object = relation('Child1', backref='others', primaryjoin =
child1_id == Child1.__table__.c.id)
Is making this change the recommended solution? Is the behavior I am
seeing here expected? Is the message telling me to use foreign_keys
bogus?
Here is the code:
#!/usr/bin/python -u
from sqlalchemy import Column, Integer, create_engine, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relation
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
_cls = Column('cls', String(50))
__mapper_args__ = dict(polymorphic_on = _cls )
class Child1(Parent):
__tablename__ = 'child1'
__mapper_args__ = dict(polymorphic_identity = 'child1')
id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
class Child2(Parent):
__tablename__ = 'child2'
__mapper_args__ = dict(polymorphic_identity = 'child2')
id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
class Other(Base):
__tablename__ = 'other'
id = Column(Integer, primary_key=True)
child1_id = Column(Integer, ForeignKey('child1.id'))
child1_object = relation('Child1', backref='others', primaryjoin =
child1_id == Child1.id)
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
def main():
child1 = Child1()
child2 = Child2()
other = Other()
child1.others = [other]
session.add(child1)
session.add(child2)
session.add(other)
session.flush()
assert 2 == session.query(Parent).count()
assert child1 == session.query(Other).one().child1_object
if __name__ == '__main__':
main()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: relation that works in .48 but fails in .53
Is there a video or slides from that pycon talk available online? I
see the overview on the pycon site
(http://us.pycon.org/2009/tutorials/schedule/2PM4/) and it looks very
interesting.
I looked more at what you said about the parent id column taking
precedence and that does seem to be what happens. In particular, I see
that the sql generated for
session.query(Child1).filter( Child1.id.in_( range(5))) .count()
uses the IN condition on parent.id in .53, whereas it was on the
child1.id in .48. The .48 behavior seems better here. I can't think of
a situation where I would say Child1.id and want it to use the column
from the parent table instead of the child.
Regardless, now that I understand what is happening, I will be able to
get my application working on .53.
Thanks for your help.
On Wed, Apr 8, 2009 at 6:04 PM, Michael Bayer mike...@zzzcomputing.com wrote:
this is a side effect of declarative which I covered in my pycon
tutorial. Child1.id is a map of both child1.id and parent.id, since
they are mapped under the same name. you can see this if you say
Child1.id.property.columns. the non-foreign key parent.id takes
precedence.
the easy solution is to split them up:
class Child1(Parent):
__tablename__ = 'child1'
__mapper_args__ = dict(polymorphic_identity = 'child1')
child_id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
class Other(Base):
__tablename__ = 'other'
id = Column(Integer, primary_key=True)
child1_id = Column(Integer, ForeignKey('child1.id'))
child1_object = relation('Child1', backref='others', primaryjoin =
child1_id == Child1.child_id)
Bobby Impollonia wrote:
I am porting some code from SQLA .48 to .53 . I have a relation that
works in .48, but not in .53.
At the end of this post is a test program demonstrating the problem.
The program works in .48 but fails in .53 trying to understand the
child1_object relation . The error message says to add foreign_keys to
the relation, but that doesn't seem to actually help.
It does however work if I change the relation to
child1_object = relation('Child1', backref='others', primaryjoin =
child1_id == Child1.__table__.c.id)
Is making this change the recommended solution? Is the behavior I am
seeing here expected? Is the message telling me to use foreign_keys
bogus?
Here is the code:
#!/usr/bin/python -u
from sqlalchemy import Column, Integer, create_engine, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relation
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
_cls = Column('cls', String(50))
__mapper_args__ = dict(polymorphic_on = _cls )
class Child1(Parent):
__tablename__ = 'child1'
__mapper_args__ = dict(polymorphic_identity = 'child1')
id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
class Child2(Parent):
__tablename__ = 'child2'
__mapper_args__ = dict(polymorphic_identity = 'child2')
id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
class Other(Base):
__tablename__ = 'other'
id = Column(Integer, primary_key=True)
child1_id = Column(Integer, ForeignKey('child1.id'))
child1_object = relation('Child1', backref='others', primaryjoin =
child1_id == Child1.id)
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
def main():
child1 = Child1()
child2 = Child2()
other = Other()
child1.others = [other]
session.add(child1)
session.add(child2)
session.add(other)
session.flush()
assert 2 == session.query(Parent).count()
assert child1 == session.query(Other).one().child1_object
if __name__ == '__main__':
main()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: When to create new sessions?
Now the decorator swallows exceptions silently. You have to reraise the exception after rolling back like Michael did. I believe the correct form is: def transaction(f): def wrapper(*args, **kwargs): try: value = f(*args, **kwargs) except: session.rollback() raise else: session.commit() return value return wrapper On Wed, Apr 8, 2009 at 7:12 AM, Diez B. Roggisch de...@web.de wrote: On Wednesday 08 April 2009 05:53:12 Michael Bayer wrote: On Apr 7, 2009, at 6:07 PM, rintin...@googlemail.com wrote: Hey Everyone, I'm new to SQLAlchemy (used to using Django's ORM but need to switch) and there's just one thing I'm struggling with, which is when am I supposed to create Sessions? I am of course creating scoped sessions. I feel like a real dunce for not being able to get my head around it. Do I create one per-request and pass it around? That just doesn't feel quite right to me. Or can I create them at module-level when I need them? per-request is the most natural approach. The point of the scopedsession is that you can use it as a global object, there's no need to pass it around. It automatically routes operations to a thread-local session. I'm sure django does something similar. the chapter on sessions includes a discussion on integrating scopedsession within a web application, you should check it out. Also, is it okay to call commit() more than once on the same session? absolutely. On a per-function basis even (seems like an awful lot of boilerplate code in each function though… surely not?!) depending on what you're doing , this may or may not be appropriate. boilerplate can be cut down using a decorator, such as: @commits def do_some_stuff(...): the decorator: def commits(fn): def go(*args, **kw): try: return fn(*args, **kw) Session.commit() Not to be to nitpicky... but this commit is never reached. And dangling transactions can be very irritating. I'd go for this (untetsted) def transaction(f) def _wrapper(*args, **kwargs): commit = True try: return f(*args, **kwargs) except: commit = False finally: (session.commit if commit else session.rollback)() Diez --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Self Join via Join Table ?
I am doing something similar. The following code works for me in SQLA .4.8
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
bar_table = Table('bar', Base.metadata,
Column('parent_id', Integer, ForeignKey('foo.id'), nullable=False),
Column('child_id', Integer, ForeignKey('foo.id'), nullable=False))
Foo.children = relation(Foo, secondary = bar_table, primaryjoin =
bar_table.c.parent_id == Foo.id, secondaryjoin=bar_table.c.child_id ==
Foo.id, backref='parents')
On Wed, Feb 25, 2009 at 3:12 PM, Stephen Telford stelford1...@gmail.com wrote:
Okay, that sounds like a plan but., not to sound too much like a broken
record, does anyone have an -actual- example ? looking at pages with a lot
of API's is not really going to help me too much :(
This maybe slightly off-topic and it's really NOT meant as flamebait but.. I
remember a while ago playing around with DBIx (the perl ORM) and one of the
things that -really- made it easy to get to grips with as the
DBIx::Cookbook. It maybe a good idea to fling something similiar into the
sqlalchemy documentation...
if I had more experience I would write it but.. yes. It definitely is the
quickest ORM I have seen/used, but, all the speed is for naught if you hit
the 20% wall.
Regards
Stef
On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
check out the association proxy extension if you're looking to have Bar
be hidden as an association object. it will ultimately use Foo/Bar for
querying but attribute access would be proxied through the names you
confgure.
On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:
Hello Az,
Yes, Bar is the association table of Foo to Foo. In essence, this is a
self join through a join table.. I have tried and hit my head on this for
(quite literally) hours. In the end, and for the record, I ended up creating
a method on the model itself such as ;
def children(self):
childFoo=Foo.__table__.alias()
return
object_session(self).query(Foo).filter(and_(childFoo.c.deleted == 0,
childFoo.id self.id, self.id == Bar.parent_id))
Not the prettiest way, nor what I would expect, but in lieu of an
actual example, and to help anyone who ends up treading the same path as me,
I hope this helps.
Regards
Stef
On Wed, Feb 25, 2009 at 4:29 AM, a...@svilendobrev.com wrote:
u mean, the Bar is an association table of Foo to Foo?
u have to use secondary_table and/or secondary_join in the relation
setup. And probably specify remote_side or it may not know which Foo
is what.
On Wednesday 25 February 2009 03:39:20 Stef wrote:
Hello Everyone,
First of all, kudos on SQLAlchemy.. the speed is pretty amazing
- I am coming from the SQLObject world and there is a definite
difference. Excellent work. I am also getting to grips with it
pretty quickly, using object_session and all that good stuff. This
said, I have hit that 20% problem, and am hoping someone can shine
a light on it.
I have a table, lets call it Foo and another table Bar. Foo
should be able to get a list of it's parents via Bar or it's
children via Bar. I am also using the declarative_base system
rather than table/ mapper defined seperately.
class Foo(Base):
id = Column(Integer, primary_key=True)
class Bar(Base):
parent_id = Column(Integer, default=0)
child_id = Column(Integer, default=0)
So, I thought something like ; children = relation(Foo,
backref=backref('parents'), primaryjoin=and_(Foo.id==Bar.parent_id)
But that's where I hit the 'wall' as it were, is there a way to
setup a synonym for Foo in the primaryjoin clause ? Am I missing
something stupid ? (I am okay with that ;)
Regards
Stef
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Best way to count( some_relation )
I think you have to use group by with a count(). Something like Account.query.join(Account.users).group_by(Account.id).filter(func.count(User.id) 1) On Mon, Jan 26, 2009 at 12:59 PM, Jon Nelson jnel...@jamponi.net wrote: Let's assume I have a 1:many relationship between Accounts and Users. What I want (for example) is a list of Accounts with 1 User. Ideally, I'd do this: Account.query().filter( len(Account.users) 1 ).all() but of course that doesn't work. Instead of describing the myriad ways I've tried, I thought I'd ask instead: What's the easiest/best way to go about that without lots of hoop-jumping? I'm using 0.5.2 -- Jon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM base class for 0.5?
The declarative extension (sqlalchemy.ext.declarative) provides a
__init__ that takes keyword args for attributes (at least it does in
0.4).
On Tue, Jan 13, 2009 at 4:02 AM, Christoph Haas em...@christoph-haas.de wrote:
Thanks for the code. For those who might also be interested in an ORM base
class providing __init__, update and __repr__ - this is what I use now with
0.5 (comments welcome):
=
import sqlalchemy as sql
from sqlalchemy import orm
class MyOrm(object):
def __init__(self, **kw):
Create a mapped object with preset attributes
for key, value in kw.iteritems():
if hasattr(self, key):
setattr(self, key, value)
elif not ignore_missing_columns:
raise AttributeError('Cannot set attribute which is not column
in mapped table: %s' % (key,))
def update(self, update_dict, ignore_missing_columns=True):
Update an object's attributes from a dictionary
for key, value in update_dict.iteritems():
if hasattr(self, key):
setattr(self, key, value)
elif not ignore_missing_columns:
raise AttributeError('Cannot set attribute which is not column
in mapped table: %s' % (key,))
def __repr__(self):
Return a decent printable representation of a mapped object and
its attributes.
atts = []
columns = orm.object_mapper(self).mapped_table.c
for column in columns:
key = column.key
if hasattr(self, key):
col = columns.get(key)
if not (getattr(col, 'server_default', None) is not None or
isinstance(getattr(col, 'default', None),
sql.PassiveDefault) or
getattr(self, key) is None):
atts.append( (key, getattr(self, key)) )
return self.__class__.__name__ + '(' + ', '.join(x[0] + '=' +
repr(x[1]) for x in atts) + ')'
=
Would be nice if mapped objects could automatically get such methods
assigned. Not sure if SQLAlchemy can or should provide that or if it
broke other functionality.
Cheers
Christoph
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] retrying queries and 'Lost connection to MySQL server'
I occasionally have a query fail with 'Lost connection to MySQL server during query' which gets converted into a sqlalchemy.exceptions.OperationalError. I have not been able to figure out why it happens, but the server should always be available. I would like to tell sqlalchemy that if a query fails with this error, it should wait a few seconds and then retry the query (and probably give up if it fails again). Does SQLA provide some sort of hooks that would allow me to do this without gnarly monkey patching? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: retrying queries and 'Lost connection to MySQL server'
This code isn't using transactions so retrying a failed query should be as simple as creating a new connection to replace the failed one and executing the query again. Still, I would much prefer to figure out the real cause, as you say. I had sort of given up on that because after a little while researching this error, I couldn't find much helpful info. It's hard to debug because the issue happens in a daily cron job, but it happens less than once a month and the rest of the time everything works fine. I have no way of consistently reproducing the problem or knowing if I've fixed it. I'm pretty sure there is no way that 8 hours could have gone by between the last query and the one that blew up. The basic structure of the cron job is: 1) It start up, does some sql stuff. 2) It forks a worker process using the python processing module. 3a) The worker calls metadata.bind.dispose() so that it won't try to reuse the connection it inherited from the parent. Worker then does some sql stuff. Worker always finishes successfully. 3b) Parent process goes into a loop doing sql stuff. Parent usually finishes successfully, but occasionally dies with the aforementioned MySQL error. I can't tell from the traceback whether it happens during the first iteration of the loop immediately after spawning the child or if it happens later. In principle, this structure is safe, right? 3a and 3b are happening in parallel, so it is indeterminate whether the worker calls dispose() before or during the sql stuff going on in the parent, but that shouldn't mater, right? Is it possible that the call to dispose() is somehow closing the connection in a way that sabotages the parent? On Sun, Dec 21, 2008 at 11:32 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 21, 2008, at 11:24 PM, Bobby Impollonia wrote: I occasionally have a query fail with 'Lost connection to MySQL server during query' which gets converted into a sqlalchemy.exceptions.OperationalError. I have not been able to figure out why it happens, but the server should always be available. I would like to tell sqlalchemy that if a query fails with this error, it should wait a few seconds and then retry the query (and probably give up if it fails again). Does SQLA provide some sort of hooks that would allow me to do this without gnarly monkey patching? you'd have to organize your code such that the desired operation can be attempted again when this exception is raised. This is a pretty tough road to travel, though, since if the connection is lost, so is your entire transaction and everything you've loaded/ persisted within it. A better approach would be to isolate the cause of the error.This error is commonly caused by a MySQL client timeout (usually on a connection that's been idle for 8 hours) and is allevated using the pool_recycle=some number of seconds option. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
Yeah, with .4 there isn't really a way have an ORM query that doesn't select at least one ORM object (possibly with additional columns/ objects added via add_column/ add_entity). You can use the select() construct instead if pulling all the columns of the mapped class is unacceptable. On Wed, Nov 19, 2008 at 1:25 PM, Moshe C. [EMAIL PROTECTED] wrote: 0.4.6 On Nov 19, 11:12 pm, Bobby Impollonia [EMAIL PROTECTED] wrote: What version of SQLA are you using? In .5 , you can pass individual columns instead of a mapped class to session.query. On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. [EMAIL PROTECTED] wrote: For Query there is an add_column() method, but I do not see a remove column method. Initializing a Query requires a full mapped class, so how can I select on only a subset of the columns. I want to do this for ding a DISTINCT query on only a couple of columns. TIA Moshe --~--~-~--~~~---~--~~ 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: ORM: Retrieving table from mapped class
Are you using declarative? If so, your class will have a property called __table__ On Sun, Nov 16, 2008 at 4:04 PM, Moshe C. [EMAIL PROTECTED] wrote: Hi, Given a mapped ORM class, is it possible to retrieve from it the Table instabce to which it was mapped? TIA Moshe --~--~-~--~~~---~--~~ 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: select where field=max(field)
If you are okay with only getting one record in the case of ties you can do session.query(Snapshot).order_by(Snapshot.totalqty.desc()).first() On Fri, Nov 7, 2008 at 12:22 PM, John Hunter [EMAIL PROTECTED] wrote: I am having trouble writing a sqlalchemy query which selects all rows where a field equals the max for that field, eg q = session.query(Snapshot).filter(Snapshot.totalqty==func.max(Snapshot.totalqty)) When I try and get the results of the query, I get the error below. How should I use func.max here? __version__ = 0.5.0rc3 In [54]: len(q.all()) Traceback (most recent call last): File ipython console, line 1, in ? File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 994, in all return list(self) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 1082, in __iter__ return self._execute_and_instances(context) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 1085, in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none(), _state=self._refresh_state) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/session.py, line 749, in execute return self.__connection(engine, close_with_result=True).execute( File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 826, in execute return Connection.executors[c](self, object, multiparams, params) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 877, in execute_clauseelement return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) 1), distilled_params=params) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 889, in _execute_compiled self.__execute_raw(context) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 898, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 942, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 924, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) ProgrammingError: (ProgrammingError) (, 'Invalid use of group function') u'SELECT snapshot.symbol AS snapshot_symbol, snapshot.strategy AS snapshot_strategy, snapshot.longshort AS snapshot_longshort, snapshot.datetime AS snapshot_datetime, snapshot.date AS snapshot_date, snapshot.year AS snapshot_year, snapshot.month AS snapshot_month, snapshot.qty AS snapshot_qty, snapshot.totalqty AS snapshot_totalqty, snapshot.price AS snapshot_price, snapshot.possize AS snapshot_possize, snapshot.pnl AS snapshot_pnl, snapshot.realized AS snapshot_realized, snapshot.pnl_hedged AS snapshot_pnl_hedged, snapshot.is_stop AS snapshot_is_stop, snapshot.stop_flag AS snapshot_stop_flag \nFROM snapshot \nWHERE snapshot.totalqty = max(snapshot.totalqty)' [] --~--~-~--~~~---~--~~ 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: NOT IN in orm queries
from sqlalchemy import not_
session.query(Region).filter(not_(Region.id.in_( (1,2,3) )))
alternately,
session.query(Region).filter(~ Region.id.in_( (1,2,3) ))
On Tue, Sep 2, 2008 at 6:59 AM, Artur Siekielski
[EMAIL PROTECTED] wrote:
Hi.
How can I use ...WHERE sth NOT IN (1, 4 ,5) queries when using ORM
quering? There is '_in' only and I don't see how can I pass negation
to it. I tried to use general 'op' but I get these errors:
Session.query(Region).filter(Region.id.op('NOT IN')( [1,2,3] )).all()
ProgrammingError: (ProgrammingError) syntax error at or near ARRAY
LINE 3: WHERE Region.id NOT IN ARRAY[1, 2, 3] ORDER BY Region.id
^
'SELECT Region.id AS Region_id, Region.name AS Region_name
\nFROM Region \nWHERE Region.id NOT IN %(id_1)s ORDER BY
Region.id' {'id_1': [1, 2, 3]}
(using tuple instead of a list)
Session.query(Region).filter(Region.id.op('NOT IN')( (1,2,3) )).all()
(ProgrammingError) can't adapt 'SELECT Region.id AS Region_id,
Region.name AS Region_name \nFROM Region \nWHERE Region.id NOT
IN %(id_1)s ORDER BY Region.id' {'id_1': (1, 2, 3)}
(using string instead of a tuple)
Session.query(Region).filter(Region.id.op('NOT IN')( '(1,2,3)' )).all()
ProgrammingError: (ProgrammingError) syntax error at or near
E'(1,2,3)'
LINE 3: WHERE Region.id NOT IN E'(1,2,3)' ORDER BY Region.id
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Run basic query
The sqlalchemy update statement is documented here:
http://www.sqlalchemy.org/docs/05/sqlexpression.html#sql_update
Basically, you want something like
conn.execute(foo.update(values={foo.c.bar: 0 }))
On Tue, Jul 22, 2008 at 10:49 AM, Heston James - Cold Beans
[EMAIL PROTECTED] wrote:
Guys,
I want to run a query which doesn't return any objects, just simply modifies
all records in the table, like so:
UPDATE foo
SET bar = 0
How can I do this using SQLAlchemy? Is it possible and 'proper' for me to
just pass this query as a string to be executed? Or is there a better
'sqlalchemy' style of doing this? Should I be pulling all the records from
the db, modifying them and then resaving them? Seems like a heavy workload.
Cheers,
Heston
--~--~-~--~~~---~--~~
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: Boolean, Declerative, MySQL 5.2
I am using Column(Boolean) with declarative and MySQL and it is working fine. In MySQL itself the type is 'tinyint(1)' but they provide 'bool' and 'boolean' as synonyms if you prefer. On Sat, Jul 19, 2008 at 7:48 AM, Heston James - Cold Beans [EMAIL PROTECTED] wrote: Hello Guys, I'm looking to store a Boolean value in a MySQL 5.2 database. I'm then going to describe a class for the table using declarative and have a couple of questions on this: What Datatype should my table column be set to in MySQL? And likewise, when declaring the column using declarative, which data type should I use? Column(Boolean)? Cheers guys, Heston --~--~-~--~~~---~--~~ 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: result as the dict()
MyBase = type(MyBase, (Base, MyMixin), {})
Is this any different than just doing
class MyBase(Base, MyMixin): pass
?
--~--~-~--~~~---~--~~
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: Searching in all fields
If you do them as double percent signs ( '%%') then the python string formatting will replace them with single percent signs. On Fri, Jun 27, 2008 at 9:12 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi. I want to do robust algorithm for searching in tables...the simplest example is with table with no relations: stmt = u'SELECT * FROM ' stmt += str(b.clients.name) stmt += ' WHERE ' for c in b.Client.c: stmt += str(c)+' like \'%value%\' or ' clients = session.query(Client).from_statement(stmt).all() There is one big problem using the '%' sign, because python is using it to replace values in string like: 'Welcom %s to my site' % 'john' Afterwards I want to search in tables with relations, like: session.query(Client).add_entity(Address).. Can anyone help me with this problem? What is the sqlalchemy way to make multisearch ?? Thx in advance m_ax --~--~-~--~~~---~--~~ 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: Multiple encodings in my database
If I am using the mysql-specific Column constructs with the charset
option, will things be automatically encoded/ decoded by SA using that
charset? Or is the charset option only used for Create Table?
On Thu, Jun 26, 2008 at 7:20 PM, Michael Bayer [EMAIL PROTECTED] wrote:
first of all, the stack trace suggests you have not set the encoding
parameter on create_engine() as it's still using UTF-8.
If you mean that a single database column may have different encodings
in different rows, you want to do your own encoding/decoding with
encoding errors set to something liberal like ignore. You also
need to use your own custom type, as below:
from sqlalchemy import types
class MyEncodedType(types.TypeDecorator):
impl = String
def process_bind_param(self, value, dialect):
assert isinstance(value, unicode)
return value.encode('latin-1')
def process_result_value(self, value, dialect):
return value.decode('latin-1', 'ignore')
then use MyEncodedType() as the type for all your columns which
contain random encoding. No convert_unicode setting should be used
on your engine as this type replaces that usage.
On Jun 26, 2008, at 6:55 PM, Hermann Himmelbauer wrote:
Hi,
I'm trying to access a database via SA, which contains varchars with
different, arbitrary encodings. Most of them are ascii or ISO-8859-2
encoded,
however, many are windows-1252 encoded and there are also some other
weird
ones.
In my engine setup, I set the encoding to latin1 and set
convert_unicode to
True, as I my application requires the database values in unicode
format.
If SA now tries to retrieve such a key, the following traceback
occurs:
--
File /home/dusty/prog/python_modules/sqlalchemy/engine/base.py,
line 1605,
in _get_col
return processor(row[index])
File /home/dusty/prog/python_modules/sqlalchemy/databases/
maxdb.py, line
112, in process
return value.decode(dialect.encoding)
File /local/home/dusty/python/Python-2.4.4/lib/python2.4/encodings/
utf_8.py,
line 16, in decode
return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode bytes in position 3-6:
invalid
data
-
What can I do? It's not so important that all characters are correctly
displayed, but it's vital that such improper encodings do not crash my
application. Perhaps, there's some universal encoding that is able
to deal
with such problems?
Best Regards,
Hermann
--
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
--~--~-~--~~~---~--~~
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: how to do manualy incremented counter
In mysql you can declare a integer column to be auto_increment and it will handle for you giving each row a different number. Other databases have similar mechanisms. It sounds like the invoice number is the primary key for the invoice table? If you have a integer primary key in sqlalchemy, it assumes that you want to make it auto increment so this should all happen automatically. On Tue, Jun 24, 2008 at 10:13 AM, [EMAIL PROTECTED] wrote: hi lets have, say, invoice-numbers. a completely new invoice has new invoice-number. same invoice may have many versions - the invoice-number does not change. how to safely generate a new number for each new document (lets say it is simple number-incrementing)? one safe and simple way i've just invented is to have a separate table of one column - the invoice-numbers - and have a foreign key to it. First version of new invoice will create a row in the table and link to it, other versions of the invoice keep the link. Besides the simplicity this allows for any numbering scheme - whatever one puts there; even the table may have just primary id if just rowids are enough. More space-savvy way is to have just one table with a row per numbering-type (one for invoice-numbers, one for account-numbers, employee-numbers etc), and for a new document increment the respective row and get its value. If there are sequences, this seems alright, the whole operation is atomic. But if there are no sequences, i cannot make this multi-access safe. atomic updates are alright for avoiding races, but reading the value after that - no guarantee it is right one, another atomic update may have sneaked in meanwhile. Any help here? or should i use locking? im trying to avoid it... ciao svilen --~--~-~--~~~---~--~~ 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] Is it possible to have a relation on string?
Does SA support the following scenario? : I have a class (let's call in User). I have a many-to-many relationship between Users and urls where a url is just a string. So I want to have a secondary table where one column is a foreign key on User and the other is a string. If the second column where a foreign key on a mapped table, I could use a relation so that for a given user I could append, remove, etc. the related items via an instrumented list. With them being raw strings instead of foreign keys on something else, is something like this still possible? Is there a way to say that I want an instrumented list of strings? --~--~-~--~~~---~--~~ 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: Create polymorphic mapped instance from type discriminator
The mapper for the base class has a property called polymorphic_map which is a dictionary whose keys are the discriminator values and whose values are the mappers of the associated classes (you can get from there to the actual class via the mapper's class_ property). I can't speak to whether this technique is API stable. On Fri, Jun 6, 2008 at 11:43 AM, Rick Morrison [EMAIL PROTECTED] wrote: Is there an API-stable way to create a polymorphic instance from only the type discriminator key? I've got a case where I need to create a mapped instance from some JSON data that contains the type discriminator, but I'd rather get the (key -- mapped class) from the sqla map and not maintain my own, which may fall out of sync as the data model changes over time. --~--~-~--~~~---~--~~ 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: ordering
You can add .order_by(None) to the query to remove the default ordering. On Fri, May 30, 2008 at 12:25 PM, Geoff [EMAIL PROTECTED] wrote: Hi! I've noticed that a very simple query has an ordering applied to it even though I haven't asked for one. Is there a way to stop it doing that? the query: Session.query(User).set_shard(shard).filter_by(uuid=uuid).all() Thanks! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Using multiple filter()s with contains() in a query
Hi. If I have a many-to-many relation between Class1 and Class2, the following query: Class1.query.filter(Class1.class2s.contains(obj1)).filter(Class1.class2s.contains(obj2)) , where obj1 and obj2 are instances of Class2, generates the following sql in .4.6: SELECT lots of columns FROM class1, secondary WHERE class1.id = secondary.class1_id AND %s = secondary.class2_id AND class1.id = secondary.class1_id AND %s = secondary.class2_id Note that the secondary table isn't being aliased for each contains(), so this will never return anything unless obj1==obj2. I think this used to use separate subqueries (with Exists) for each contains(), so this query worked before .4.6. (I only have .4.6 installed, so I can't verify). Also, if obj1 and obj2 aren't actually instances of type Class2 but are instead instances of some other mapped class that don't belong in this relation, SQLA happily uses them in the above query if they have a column with the same name as Class2's primary key. I would think that throwing an exception in that case would be better behavior? --~--~-~--~~~---~--~~ 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: Using multiple filter()s with contains() in a query
http://www.sqlalchemy.org/trac/ticket/1058 I found the query I had that worked with .4.5 and it is different because it is using not_ on each contains() which generates separate subselects (with Exists), so that doesn't have aliasing problems. That still works in .4.6. On Wed, May 21, 2008 at 12:25 PM, Michael Bayer [EMAIL PROTECTED] wrote: this might be a bug introduced in 0.4.6, it would be helpful if you could post a full test case as a trac ticket. Though im not sure how this would have worked in 0.4.5 either since I don't think we have any coverage for this exact scenario and there wasn't any aliasing logic removed AFAIK. On May 21, 2008, at 12:07 PM, Bobby Impollonia wrote: Hi. If I have a many-to-many relation between Class1 and Class2, the following query: Class1 .query .filter (Class1.class2s.contains(obj1)).filter(Class1.class2s.contains(obj2)) , where obj1 and obj2 are instances of Class2, generates the following sql in .4.6: SELECT lots of columns FROM class1, secondary WHERE class1.id = secondary.class1_id AND %s = secondary.class2_id AND class1.id = secondary.class1_id AND %s = secondary.class2_id Note that the secondary table isn't being aliased for each contains(), so this will never return anything unless obj1==obj2. I think this used to use separate subqueries (with Exists) for each contains(), so this query worked before .4.6. (I only have .4.6 installed, so I can't verify). Also, if obj1 and obj2 aren't actually instances of type Class2 but are instead instances of some other mapped class that don't belong in this relation, SQLA happily uses them in the above query if they have a column with the same name as Class2's primary key. I would think that throwing an exception in that case would be better behavior? --~--~-~--~~~---~--~~ 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: SQLAlchemy 0.4.6 released
Any idea when a .deb will be available? http://packages.debian.org/unstable/python/python-sqlalchemy is still .4.5 On Wed, May 14, 2008 at 3:02 PM, Michael Bayer [EMAIL PROTECTED] wrote: SQLAlchemy 0.4.6 is now available at: http://www.sqlalchemy.org/download.html This release includes some fixes for some refactorings in 0.4.5, introduces a new collate() expression construct, and improves the behavior of contains_eager(), a useful ORM option. The 0.4 series is now in bugfix mode as we put the new features into 0.5, which is in the current trunk. changelog: - orm - A fix to the recent relation() refactoring which fixes exotic viewonly relations which join between local and remote table multiple times, with a common column shared between the joins. - Also re-established viewonly relation() configurations that join across multiple tables. - contains_eager(), the hot function of the week, suppresses the eager loader's own generation of the LEFT OUTER JOIN, so that it is reasonable to use any Query, not just those which use from_statement(). - Added an experimental relation() flag to help with primaryjoins across functions, etc., _local_remote_pairs=[tuples]. This complements a complex primaryjoin condition allowing you to provide the individual column pairs which comprise the relation's local and remote sides. Also improved lazy load SQL generation to handle placing bind params inside of functions and other expressions. (partial progress towards [ticket:610]) - repaired single table inheritance such that you can single-table inherit from a joined-table inherting mapper without issue [ticket:1036]. - Fixed concatenate tuple bug which could occur with Query.order_by() if clause adaption had taken place. [ticket:1027] - Removed an ancient assertion that mapped selectables require alias names - the mapper creates its own alias now if none is present. Though in this case you need to use the class, not the mapped selectable, as the source of column attributes - so a warning is still issued. - Fixes to the exists function involving inheritance (any(), has(), ~contains()); the full target join will be rendered into the EXISTS clause for relations that link to subclasses. - Restored usage of append_result() extension method for primary query rows, when the extension is present and only a single- entity result is being returned. - Fixed Class.collection==None for m2m relationships [ticket:4213] - Refined mapper._save_obj() which was unnecessarily calling __ne__() on scalar values during flush [ticket:1015] - Added a feature to eager loading whereby subqueries set as column_property() with explicit label names (which is not necessary, btw) will have the label anonymized when the instance is part of the eager join, to prevent conflicts with a subquery or column of the same name on the parent object. [ticket:1019] - Same as [ticket:1019] but repaired the non-labeled use case [ticket:1022] - Adjusted class-member inspection during attribute and collection instrumentation that could be problematic when integrating with other frameworks. - Fixed duplicate append event emission on repeated instrumented set.add() operations. - set-based collections |=, -=, ^= and = are stricter about their operands and only operate on sets, frozensets or subclasses of the collection type. Previously, they would accept any duck-typed set. - added an example dynamic_dict/dynamic_dict.py, illustrating a simple way to place dictionary behavior on top of a dynamic_loader. - sql - Added COLLATE support via the .collate(collation) expression operator and collate(expr, collation) sql function. - Fixed bug with union() when applied to non-Table connected select statements - Improved behavior of text() expressions when used as FROM clauses, such as select().select_from(text(sometext)) [ticket:1014] - Column.copy() respects the value of autoincrement, fixes usage with Migrate [ticket:1021] - engines - Pool listeners can now be provided as a dictionary of callables or a (possibly partial) duck-type of PoolListener, your choice. - Added reset_on_return option to Pool which will disable the database state cleanup step (e.g. issuing a rollback()) when connections are returned to the pool. -extensions - set-based association proxies |=, -=, ^= and = are stricter about their operands and only operate on sets, frozensets or other association proxies. Previously, they would accept any duck-typed set. -
[sqlalchemy] Re: trunk is now on 0.5
I dont see how this: cls.query.left_bracket().filter_or(cls.y == 17).filter_or(cls.x==27).right_bracket() is clearer than this: cls.query.filter(or_(cls.y == 17, cls.x==27)) Also, another vote for cutting off python 2.3. Seriously, it's 2008. On Mon, May 12, 2008 at 11:58 AM, [EMAIL PROTECTED] wrote: On Monday 12 May 2008 17:01:23 Michael Bayer wrote: what does q.filter(x==5).filter_or(x==9).filter(y=17).filter_or(x==27) do ? (x=5 or x=9) and (y=17 or x=27) ? ((x=5 or x=9) and y=17) or x=27 ? etc .. what pythons/C x==5 or x==9 and y==17 or x==27 does? i know... the parenthesises. cant we invent something? it's not for tomorrow... the resetjoinpoint is one possibility, and some left_bracket()/right_bracket() is another. another way is to be able to do boolean arithmetics over whole queries, maybe thats even better? query.or_( query.filter(this).join(that), query.filter(that).join(this) ) On May 12, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: one thing that might go in a wishlist - query.filter_or() http://groups.google.com/group/sqlalchemy/browse_thread/thread/f6 798eb5ef2c0bfe should i make it into a ticket? as you might have noticed we've merged 0.5 into the trunk. --~--~-~--~~~---~--~~ 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: The IN Construct
in_ is a method that exists on a column. You pass it the list of things that the column value should be in. For example, if you have a class called MyClass that is mapped to a table and has a column called id, you can do: session.query(MyClass).filter(MyClass.id.in_( [ 3, 4] )).all() On Fri, May 9, 2008 at 10:35 AM, Googli S [EMAIL PROTECTED] wrote: Hello, I would like to use the IN construct in one of my queries: i.e. WHERE c.id IN (..subquery here) But I can't find any sqlalchemy support for It. It's hvery hard to search for :( Anyone know? Thanks --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: relation that only fails with a backref
That worked, except that I had to skip the secondary kwarg on the
backref. If I include it, I get:
type 'exceptions.TypeError': __init__() got multiple values for
keyword argument 'secondary'
So it seems that it is assuming that I will use the same secondary
table for the backref but not that I am using the same (or, rather,
flipped) join conditions? That doesn't make too much sense to me
(although practically speaking, it is certainly not that big a deal to
have to specify the join conditions again in the backref). I will put
a bug in trac later today to track this issue for .5
Also, this is my first time using a relation with uselist set to false
and I was surprised that if multiple objects meet the condition it
just hands me the first one. Since getting the value of a relation
that has uselist=False is (in my mind) the moral equivalent of using
one() on a query, I had been hoping it would raise if multiple rows
were returned.
On Wed, May 7, 2008 at 8:22 PM, Michael Bayer [EMAIL PROTECTED] wrote:
On May 7, 2008, at 7:23 PM, Bobby Impollonia wrote:
secondary_table = Table('secondary', Base.metadata,
Column('left_id', Integer, ForeignKey('parent.id'),
nullable=False),
Column('right_id', Integer, ForeignKey('parent.id'),
nullable=False))
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
cls = Column(String(50))
__mapper_args__ = dict(polymorphic_on = cls )
class Child1(Parent):
__tablename__ = 'child1'
id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
__mapper_args__ = dict(polymorphic_identity = 'child1',
inherit_condition=Parent.id==id)
class Child2(Parent):
__tablename__ = 'child2'
id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
__mapper_args__ = dict(polymorphic_identity = 'child2')
Child1.left_child2 = relation(Child2, secondary = secondary_table,
primaryjoin = Child1.c.id == secondary_table.c.right_id,
secondaryjoin = Child2.c.id == secondary_table.c.left_id,
uselist = False,
foreign_keys = [secondary_table.c.left_id,
secondary_table.c.right_id],
backref = 'the_backref')
The first time I try to create an object or do a query, I get:
class 'sqlalchemy.exceptions.ArgumentError': Could not determine
relation direction for primaryjoin condition 'child2.id =
secondary.left_id', on relation Child2.the_backref (Child1). Specify
the foreign_keys argument to indicate which columns on the relation
are foreign.
when you specify primaryjoin/secondaryjoin to relation(), those
arguments are not copied into the backref automatically, since you've
gone explicit (perhaps this should be adjusted in 0.5). So you need
to use backref=backref('the_backref', primaryjoin=join,
secondaryjoin=otherjoin, secondary=table, foreign_keys=keys) in
this case.
--~--~-~--~~~---~--~~
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: Multiple SQLAlchemy DB usage in TurboGears
I'd try bypassing their SQLA integration altogether if thats possible It isn't possible if you are relying on the turbogears identity system (cookie-based visitor tracking and access control). --~--~-~--~~~---~--~~ 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: relation that only fails with a backref
Another issue with this relation is that it doesn't like being eagerloaded.
Using the same model from my first post (with the broken backref
removed or fixed):
session.query(Child1).options(eagerload('left_child2')).first()
Generates the sql:
SELECT anon_1.child1_id AS anon_1_child1_id, anon_1.parent_id AS
anon_1_parent_id, anon_1.parent_cls AS anon_1_parent_cls,
anon_2.child2_id AS anon_2_child2_id, anon_2.parent_id AS
anon_2_parent_id, anon_2.parent_cls AS anon_2_parent_cls
FROM (SELECT child1.id AS child1_id, parent.id AS parent_id,
parent.cls AS parent_cls, parent.oid AS parent_oid
FROM parent JOIN child1 ON parent.id = child1.id ORDER BY parent.oid
LIMIT 1 OFFSET 0) AS anon_1 LEFT OUTER JOIN secondary AS secondary_1
ON anon_1.child1_id = secondary_1.right_id LEFT OUTER JOIN (SELECT
anon_1.child1_id AS anon_1_child1_id, anon_1.parent_id AS
anon_1_parent_id, anon_1.parent_cls AS anon_1_parent_cls, child2.id AS
child2_id
FROM (SELECT child1.id AS child1_id, parent.id AS parent_id,
parent.cls AS parent_cls, parent.oid AS parent_oid
FROM parent JOIN child1 ON parent.id = child1.id ORDER BY parent.oid
LIMIT 1 OFFSET 0) AS anon_1 JOIN child2 ON anon_1.parent_id =
child2.id) AS anon_2 ON anon_2.child2_id = secondary_1.left_id ORDER
BY anon_1.oid, secondary_1.oid
Which fails with:
(OperationalError) no such column: anon_2.parent_id
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] relation that only fails with a backref
secondary_table = Table('secondary', Base.metadata,
Column('left_id', Integer, ForeignKey('parent.id'), nullable=False),
Column('right_id', Integer, ForeignKey('parent.id'), nullable=False))
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
cls = Column(String(50))
__mapper_args__ = dict(polymorphic_on = cls )
class Child1(Parent):
__tablename__ = 'child1'
id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
__mapper_args__ = dict(polymorphic_identity = 'child1',
inherit_condition=Parent.id==id)
class Child2(Parent):
__tablename__ = 'child2'
id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
__mapper_args__ = dict(polymorphic_identity = 'child2')
Child1.left_child2 = relation(Child2, secondary = secondary_table,
primaryjoin = Child1.c.id == secondary_table.c.right_id,
secondaryjoin = Child2.c.id == secondary_table.c.left_id,
uselist = False,
foreign_keys = [secondary_table.c.left_id,
secondary_table.c.right_id],
backref = 'the_backref')
The first time I try to create an object or do a query, I get:
class 'sqlalchemy.exceptions.ArgumentError': Could not determine
relation direction for primaryjoin condition 'child2.id =
secondary.left_id', on relation Child2.the_backref (Child1). Specify
the foreign_keys argument to indicate which columns on the relation
are foreign.
However, if I remove the backref, the left_child2 property works fine,
so this only seems to be failing in one direction.
--~--~-~--~~~---~--~~
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: Duplication of rows in many-to-many relationship
I thought that defining relation with a backref was just a convenient shorthand for defining two relations. This makes it sound like are practical differences between the two techniques. Is this true? What are the differences? Also, does having the unique key that you recommend stop SA from trying to add the duplicate? Or will it try anyway and then get a SQL exception due to the violated constraint? I am often doing if a not in b.as: b.as.append(a) and I have been wondering if there is a way to just do: b.as.append(a) and have SA automatically check if it was already in collection and shouldn't be added again. On Sun, May 4, 2008 at 4:40 PM, Barry Hart [EMAIL PROTECTED] wrote: By chance, in your mappers, are you declaring two relationships instead of one relation with a backref? As a side note, once you straighten this out, you may want to declare the composite (a_id, b_id) as a unique key on the relation table. Barry - Original Message From: Karlo Lozovina [EMAIL PROTECTED] To: sqlalchemy sqlalchemy@googlegroups.com Sent: Sunday, May 4, 2008 4:31:55 PM Subject: [sqlalchemy] Duplication of rows in many-to-many relationship Let's say I have two classes A and B, and I want instances of both classes, to have a list of each other, that is, many-to-many relationship. For a shorthand, a means instance of A, and b is an instance of B. For example: a.bs is a list, full of instances of class B. Similarly, b.as is a list, full of instances of class A. In modelling that relationship I use three tables, one for As, one for Bs, and one for their relationship. If I only append instances of B to some a.bs, then save all those objects, everything works fine. But if I append instances of A and B, both to a.bs and b.as, then save, I get double rows in the third table. Is there a way around that? P.S. In a very likely case I haven't been completely understood, I'll attach some code to demonstrate my point ;). Thanks all. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. --~--~-~--~~~---~--~~ 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: join inheritance and mutual foreign keys behind tables
Awesome. For now, the inherit_condition mapper arg is working fine, so thanks for that. On Thu, May 1, 2008 at 9:03 PM, Michael Bayer [EMAIL PROTECTED] wrote: On May 1, 2008, at 6:25 PM, Bobby Impollonia wrote: It no longer works. In particular, I get class 'sqlalchemy.exceptions.InvalidRequestError': Could not find table 'child2' with which to generate a foreign key I actually have a fix for this in r4614, which is essentially similar to the workaround I gave you. --~--~-~--~~~---~--~~ 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] possible bug with join inheritance and contains()
I have a class that I will call Child which inherits via join inheritance from a class I will call Parent. There is a many-to-many relationship between Child and a class that I will call Other. A query that looks like this: child = some child Other.query.filter(not_(other.child_objects.contains(child))).first() Generates sql that looks like: SELECT other columns FROM other WHERE NOT (EXISTS (SELECT 1 FROM other_child, child, parent WHERE other.id = other_child.other_id AND child.id = other_child.child_id AND parent.id = %s)) ORDER BY other.id LIMIT 0, 1 The subquery (unnecessarily) includes the parent table. However, it does not join the parent table with the child table, so the subquery will always be true as long as there is any entry for the other in other_child, even if it is not with the child we care about. --~--~-~--~~~---~--~~ 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] join inheritance and mutual foreign keys behind tables
Hi. I have two tables that have foreign keys on each other. The
following works fine, where Base is a declarative base:
class Child1(Base):
__tablename__ = 'child1'
id = Column('id', Integer, primary_key=True)
related_child2 = Column('c2', Integer, ForeignKey('child2.id',
use_alter = True, name='c2_key'))
class Child2(Base):
__tablename__ = 'child2'
id = Column('id', Integer, primary_key=True)
related_child1 = Column('c1', Integer, ForeignKey('child1.id'))
I would like these tables to both be children of a common parent table
using join inheritance, but I still want them to reference each other
directly. If I change the model to this:
class Parent(Base):
__tablename__ = 'parent'
id = Column('id', Integer, primary_key=True)
tp = Column('type', String(50))
__mapper_args__ = dict(polymorphic_on = tp)
class Child1(Parent):
__tablename__ = 'child1'
id = Column('id', Integer, ForeignKey('parent.id'), primary_key=True)
related_child2 = Column('c2', Integer, ForeignKey('child2.id',
use_alter = True, name='c2_key'))
__mapper_args__ = dict(polymorphic_identity = 'child1')
class Child2(Parent):
__tablename__ = 'child2'
id = Column('id', Integer, ForeignKey('parent.id'), primary_key=True)
related_child1 = Column('c1', Integer, ForeignKey('child1.id'))
__mapper_args__ = dict(polymorphic_identity = 'child2')
It no longer works. In particular, I get
class 'sqlalchemy.exceptions.InvalidRequestError': Could not find
table 'child2' with which to generate a foreign key
Why was it able to find the table in the first model but not in the second?
--~--~-~--~~~---~--~~
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: Creating multiple tables based on the same class
Yes, you can have the same class be associated with different tables at different times. Sqlalchemy uses a 'maper' to associate a class with a table. You can define multiple mappers for the same class that map it to different tables. One of these will be the primary mapper and will be used by default when you don't specify which mapper to use. To use the other mapper, you use the keyword argument entity_name and specify the mapper by name. Most session functions support that keyword arg. Here is the documentation: http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_multiple On Mon, Apr 7, 2008 at 9:37 AM, GK [EMAIL PROTECTED] wrote: This is another I'm new to SQLAlchemy kind of question... I have two datasets that have exactly the same structure, and I wish to create separate database tables for these that are mapped to/from the structure of same underlying Python class. I looked for discussion of this in the SQLAlchemy manual, but the references I found (e.g. [1]) seem to discuss spreading a class over several tables rather than multiple instances/datasets. It appears that the mapper structure always associates a given python class with a single table. Is this correct? The pattern I'm contemplating to support multiple datasets is to define a subclass of the main class for each one, then map each subclass to its own table. Is there a better way? #g [1] http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_joins --~--~-~--~~~---~--~~ 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: what = declarative_base
I like having the base of my models be mypackage.model.Base . That name does a good job of describing what role the class plays; it is the common base on which each model is built. If I were mixing declarative and non-declarative models, then I could understand wanting the declarative ones to be distinguished as such, but as long as I have a common base for my models (which is also the case in the declarative documentation), I like it being called model.Base rather than being named after an implementation detail. Declarative is also longer and more annoying to type. On Wed, Apr 2, 2008 at 5:21 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 2, 2:41 pm, J. Cliff Dyer [EMAIL PROTECTED] wrote: It makes me twitch when I see the following: Base = declarative_base(metadata=metadata) class Spam(Base): ... Base is a singularly undescriptive name to use for the base class of a declarative table class. People are doing this because it's in the documentation. If it were changed there, I think people would generally follow along. Would others be in favor of changing the documentation to something like this? Declarative = declarative_base(metadata=metadata) class Spam(Declarative): ... I'd be happy to implement the change throughout the declarative docs if there's support for the idea. Its widely known that I defer all naming decisions to othersdo we like Declarative, or something more ActiveRecord-y ? --~--~-~--~~~---~--~~ 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] Circular table dependencies and ext.declarative
Hi. I am using sqlalchemy with a database that has two tables that
each have foreign keys on the other.
The situation this is modeling is a tree where the levels alternate
between two different types of node. Each Node1 has a parent of type
Node2 (or null for the root) and each Node2 has a parent of type
Node1.
(These aren't the actual names but I am trying to simplify it down for
exposition).
Using ext.declarative I have this (removing the irrelevant parts):
Base = declarative_base(metadata=metadata)
class Node1(Base):
__tablename__ = 'node1'
id = Column('id', Integer, primary_key=True)
parent_node2_id = Column('parent_node2_id', Integer, ForeignKey('node2.id'))
class Node2(Base):
__tablename__ = 'node2'
id = Column('id', Integer, primary_key=True)
parent_node1_id = Column('parent_node1_id', Integer, ForeignKey('node1.id'))
Node2.parent_node1 = relation(Node1, primaryjoin = Node1.id ==
Node2.parent_node1_id, backref = 'children')
Node1.parent_node2 = relation(Node2, primaryjoin = Node2.id ==
Node1.parent_node2_id, backref = 'children')
This fails (as expected) because of the circular dependency. Before I
switched to declarative, I had solved this by using a
ForeignKeyConstraint with use_alter.
I can't see how to do that with declarative. I tried changing Node2 to:
class Node2(Base):
__tablename__ = 'node2'
id = Column('id', Integer, primary_key=True)
parent_node1_id = Column('parent_node1_id', Integer)
fk = ForeignKeyConstraint(['parent_node1_id'], ['node1.guid'],
use_alter = True, name='parent_key')
SA now complains that my Node2.parent_node1 relation doesn't have a
foreign key, so it looks like the constraint didn't take.
How do I make this work?
Also, I think that I have to put the relations outside of the class
definitions (as above) because the primaryjoin arguments references
the class itself. Is there a way around this?
Thanks
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Circular table dependencies and ext.declarative
Awesome, putting use_alter on one of the foreign keys fixed the problem.
Would it be hard to just assume that when there is a circular
dependency that one of the keys should be use_alter'ed? Is there
actually a case where that be the wrong thing to do (or any reason I
would care which of the keys is the one to have use_alter)?
On Tue, Apr 1, 2008 at 6:54 PM, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 1, 2008, at 5:59 PM, Bobby Impollonia wrote:
This fails (as expected) because of the circular dependency. Before I
switched to declarative, I had solved this by using a
ForeignKeyConstraint with use_alter.
I can't see how to do that with declarative. I tried changing Node2
to:
ForeignKey takes the use_alter argument as well:
class Node2(Base):
__tablename__ = 'node2'
id = Column('id', Integer, primary_key=True)
parent_node1_id = Column('parent_node1_id', Integer,
ForeignKey('node1.id', use_alter=True, name='foo'))
Also, I think that I have to put the relations outside of the class
definitions (as above) because the primaryjoin arguments references
the class itself. Is there a way around this?
One way might be to go half the old route and create Table objects
before you define the classes, setting them on the declarative using
__table__. then you'd use the table objects for the expressions.
This is kind of the reason I've been into datamapper for so long.
But, you can actually get pretty wacky with the combination of mapper
and declarative. check this one out:
node1_id = Column('id', Integer, primary_key=True)
node2_id = Column('id', Integer, primary_key=True)
class Node1(Base):
__tablename__ = 'node1'
id = node1_id
parent_node2_id = Column('parent_node2_id', Integer,
ForeignKey('node2.id'))
parent_node2 = relation(Node2, primaryjoin = node2_id ==
parent_node2_id, backref = 'children')
class Node2(Base):
__tablename__ = 'node2'
id = node2_id
parent_node1_id = Column('parent_node1_id', Integer,
ForeignKey('node1.id', use_alter=True, name='foo'))
parent_node1 = relation(Node1, primaryjoin = node1_id ==
parent_node1_id, backref = 'children')
metadata.create_all()
I don't know if the above is the best way to do this but it is very
enjoyable.
Theres various ways, using callables or eval'ed strings, that it can
be completely inlined even for a bi-directionally-dependent case
like this one, but I'm not really sure they're worth it. The point of
declarative is to simplify the large majority of *simple* mappings.
If you have a complicated one like this, you have to drop into mapper
concepts a little bit.
--~--~-~--~~~---~--~~
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] sqlalchemy.ext.declarative and scoped session
Hi. I recently switched a sqlalchemy project from using manual mapping
of classes to ext.declarative.
This is in a turbogears project and I am using their metadata and
mapper. Before I had:
from turbogears.database import metadata, mapper
users_table = sqlalchemy.Table('users', metadata, ... )
class User(object): pass
mapper(User, users_table)
And now I have:
from turbogears.database import metadata, mapper
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(metadata=metadata)
class User(Base):
__tablename__ = 'users'
__mapper__ = mapper
...
I had thought that these approaches were different syntaxes for the
same thing and would produce equivalent classes. However, this isn't
the case. In particular, I used to have a User.query property (which I
believe is a side effect of a class being tied to a scoped session?)
and now it is gone. I have to use session.query(User) instead.
Any idea how I can get User.query back and have my classes
automatically associated with my session again? I really like
declarative and it would be disappointing to have to go back to manual
mapping of class just for that.
Thanks for any suggestions.
--~--~-~--~~~---~--~~
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: using a model inherits from dict
If the only dict behavior you need is accessing elements with [], another approach is that you could implement __getitem__ in your model but not inherit from dict. On Sat, Mar 29, 2008 at 3:08 PM, Mike Bernson [EMAIL PROTECTED] wrote: I am trying to use models that inherit from dict. The models use attribute access for the sqlachemy attributes and dict style access for gui stuff. This allow me to do things like model[column_name].editable for gui to see if item should be grayed out and model.column_name for access the column value. I am getting 'TypeError: dict objects are unhashable' so I created a __hash__ method as that is def __hash__(self): return id(self) Will this cause me other problem with Sqlalchemy. I can not use the primary key as the has because it may not alway exist. Some primary keys are auto increment. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
