[sqlalchemy] Extracting metadata about results
Hi, I am using sqlalchemy with pylons and I am trying to extract some metadata about the set of relationships between the objects I have defined. Specifically, I am trying to work out how to tell what kind of objects are held in a one to many relationship. e.g. Two classes - person and phone number. Both have seperate tables and an intermediate table for the join. Map the two together using assign_mapper, where the phone_numbers attribute is a list of phone number objects If I select a person from the database, how can I inspect phone_numbers and find that the object it is a relation to is a phone number? Another question is how would I extract the column type of the different attributes which make up a class e.g name is a string, age is an int, etc? Thanks for any help, BJPirt --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Many to many optimization
I actually tried to use query.instances, but it behaved quite oddly. I didn't debug or even echo the SQL calls yet, but it made accessing those instances very slow. The actual instances call was quick, but when accessing the objects from the resulting list it slowed down to crawl. I will recreate that situtation later and see where to slowdown is. I'll also give some more implementation details so that you can see if there just a stupid mistake i have made that makes SA slow. -- K On Apr 12, 6:17 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Apr 12, 2007, at 3:30 AM, Kaali wrote: > > > > > Thanks for the answers. > > > I implemented message loading with find_members() kind of method, as > > shown in the documentation link you gave, and it got twice as fast. > > But it's still nowhere near the speed without the ORM. > > i get the impression youre trying to do a partial eager load. any > query that you can execute by itself can be fed into the ORM and > turned into object-mapped results, including the (partial, > incomplete, whatever) fulfillment of whatever relationships you like, > using query.instances(). have you looked into that ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Bit strange - no expunge required?
I think SessionContext makes senses especially for architecture involving multiple layers like mine where manager <-> DAO interaction happens. Thx Michael. On 4/12/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > > > > On Apr 11, 2007, at 11:15 PM, Arun Kumar PG wrote: > > > Hi Michael, > > > > So how can I prevent this as I can't access the lazyload attributes > > in my manager class once I get the result set from DAO as i get no > > parent session/contextual session exists exception. > > > > should I maintain a reference to the session object in the DAO > > class so that it is not garbage collected ? > > heres the approaches in descending order of inconvenience: > > you can, as a workaround, immediately access all the lazy load > relations in your getResults() method...i do this with hibernate a lot. > > otherwise, one option is to explicitly keep a Session around that > doesnt get garbage collected, like you mention. > > but what a lot of people do is use the SessionContext extension with > your mappers. that way when the lazy loader fires off, it looks for > the Session, but if it cant find it, calls mapper.get_session() which > then calls the SessionContextSessionContext then creates a new > session if one does not exist already for the current thread. so > using SessionContext is kind of like replacing the weakly-referenced > Session with a Session that is bound to a thread. > > > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Column to default to itself
On Apr 13, 2:47 am, Jorge Godoy <[EMAIL PROTECTED]> wrote: > IF you insist on doing that at your code, make the column UNIQUE (or a > PK...) and write something like this pseudocode: > > def save_data(): > def insert_data(): >try: >unique_column_value = get_max_from_unique_column >Class(unique_column_value + 1, 'other data') >except YourDBExceptionForConstraintViolation: >sleep(random.random()) >insert_data() > > The 'sleep(random.random())' is there to avoid constant clashes and to > be "fair" to all connections that are inserting data on your table. To get an uninterrupted number sequence you need to serialize your inserts to that specific entity, for which you basically need locking. The quoted approach is optimistic locking, where you hope that no one tries to insert another row between when you use the get_max_from_unique_column and do the database commit, but are ready to retry if that expectation fails. Another way would be to use pessimistic locking, by doing the get_max_from_unique_column query with lockmode='update'. Then any other thread trying to insert another row while you're busy inserting yours will have to wait. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Column to default to itself
"Koen Bok" <[EMAIL PROTECTED]> writes: > I need to have a uninterrupted number sequence in my table for > invoices. I was trying to do it like this, but I can't get it to work. > Can anyone give me a hint? Let your database do the job. It is always aware of all connections made to it, their contexts, their priorities, what transaction isolation level is being used, etc. It will be better on this task. IF you insist on doing that at your code, make the column UNIQUE (or a PK...) and write something like this pseudocode: def save_data(): def insert_data(): try: unique_column_value = get_max_from_unique_column Class(unique_column_value + 1, 'other data') except YourDBExceptionForConstraintViolation: sleep(random.random()) insert_data() The 'sleep(random.random())' is there to avoid constant clashes and to be "fair" to all connections that are inserting data on your table. -- Jorge Godoy <[EMAIL PROTECTED]> --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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] Column to default to itself
I need to have a uninterrupted number sequence in my table for
invoices. I was trying to do it like this, but I can't get it to work.
Can anyone give me a hint?
request_table = Table('request', metadata,
Column('id', Integer, primary_key=True),
Column('number', Integer, unique=True, nullable=True))
request_table.c.number.default =
default=func.coalesce(func.max(request_table.c.number), 0).op('+')(1)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: Data from mysql db not returned by query
an engine uses a connection pool in all cases for all connections. On Apr 12, 2007, at 4:29 PM, vinjvinj wrote: > >> conn = mysql.db.connect() >> >> >> >> conn.close() > > > Thanks. That fixed the problem. Is sqlalchemy using a connection pool > when I do this or is a new connection opened and closed each time? > > Vineet > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: selecting from a relation
there you go. On Apr 12, 2007, at 4:31 PM, ml wrote: > > I'm such a moron. I downloaded the SA source into a "sqlalchemy3" > directory but the SA expects it in a "sqlalchemy" package so it was > internaly loading the old 0.2.8 Ubuntu version. > > Sorry! :-) > > > Michael Bayer napsal(a): >> >> On Apr 12, 2007, at 12:53 PM, ml wrote: >> >>> I tried s.query(Address).select_by(user=u) as I found similar in the >>> documentation >>> (http://www.sqlalchemy.org/docs/ >>> datamapping.html#datamapping_selectrelations_relselectby) >>> but SA raises: >>> AttributeError: 'LazyLoader' object has no attribute 'columns' >>> >> >> >> works for me, cant reproduce. please attach a full reproducing test >> case. >> >>> >> > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: selecting from a relation
it runs for me with 0.3.6 and the trunk.
the end of the output is:
2007-04-12 16:57:20,733 INFO sqlalchemy.engine.base.Engine.0x..b0
SELECT addresses.id_user AS addresses_id_user, addresses.id AS
addresses_id, addresses.addr AS addresses_addr
FROM addresses, users
WHERE (users.id = ?) AND users.id = addresses.id_user ORDER BY
addresses.oid
2007-04-12 16:57:20,735 INFO sqlalchemy.engine.base.Engine.0x..b0 [1]
bob's house
bob's flat
send the stacktrace youre getting, thatll tell all.
On Apr 12, 2007, at 4:25 PM, ml wrote:
> See attachment. Tested against 0.3.6.
>
>
>
> Michael Bayer napsal(a):
>>
>> On Apr 12, 2007, at 12:53 PM, ml wrote:
>>
>>> I tried s.query(Address).select_by(user=u) as I found similar in the
>>> documentation
>>> (http://www.sqlalchemy.org/docs/
>>> datamapping.html#datamapping_selectrelations_relselectby)
>>> but SA raises:
>>> AttributeError: 'LazyLoader' object has no attribute 'columns'
>>>
>>
>>
>> works for me, cant reproduce. please attach a full reproducing test
>> case.
>>
>>>
>>
>
> >
> #!/usr/bin/python2.4
> # -*- coding: utf-8 -*-
>
> from sqlalchemy3 import *
> import datetime, pickle, sys
>
> engine = create_engine("sqlite://", echo=True)
>
> metadata = BoundMetaData(engine)
>
> users_table = Table("users", metadata,
> Column("id", Integer, primary_key=True),
> Column("user_name", String(16))
> )
>
> addresses_table = Table("addresses", metadata,
> Column("id", Integer, primary_key=True),
> Column("id_user", Integer, ForeignKey("users.id")),
> Column("addr", String(100))
> )
>
> class User(object):
> def __init__(self, user_name):
> self.user_name = user_name
>
> class Address(object):
> def __init__(self, addr):
> self.addr = addr
>
> mapper(Address, addresses_table)
> mapper(User, users_table, properties = {
> "addresses" : relation(Address, cascade="all, delete-orphan",
>backref=backref("user")),
> }
> )
>
> metadata.create_all(engine)
>
> s = create_session(bind_to=engine)
>
> u1 = User("bob")
> a1 = Address("bob's house")
> a2 = Address("bob's flat")
> u1.addresses.append(a1)
> u1.addresses.append(a2)
> s.save(u1)
>
> u2 = User("alice")
> a3 = Address("alice's house")
> a4 = Address("alice's flat")
> u2.addresses.append(a3)
> u2.addresses.append(a4)
> s.save(u2)
>
> s.flush()
>
> u = s.query(User).get_by_user_name("bob")
> for i in s.query(Address).select_by(user=u):
> print i.addr
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: selecting from a relation
I'm such a moron. I downloaded the SA source into a "sqlalchemy3" directory but the SA expects it in a "sqlalchemy" package so it was internaly loading the old 0.2.8 Ubuntu version. Sorry! :-) Michael Bayer napsal(a): > > On Apr 12, 2007, at 12:53 PM, ml wrote: > >> I tried s.query(Address).select_by(user=u) as I found similar in the >> documentation >> (http://www.sqlalchemy.org/docs/ >> datamapping.html#datamapping_selectrelations_relselectby) >> but SA raises: >> AttributeError: 'LazyLoader' object has no attribute 'columns' >> > > > works for me, cant reproduce. please attach a full reproducing test > case. > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Data from mysql db not returned by query
> conn = mysql.db.connect() > > > > conn.close() Thanks. That fixed the problem. Is sqlalchemy using a connection pool when I do this or is a new connection opened and closed each time? Vineet --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: selecting from a relation
See attachment. Tested against 0.3.6.
Michael Bayer napsal(a):
>
> On Apr 12, 2007, at 12:53 PM, ml wrote:
>
>> I tried s.query(Address).select_by(user=u) as I found similar in the
>> documentation
>> (http://www.sqlalchemy.org/docs/
>> datamapping.html#datamapping_selectrelations_relselectby)
>> but SA raises:
>> AttributeError: 'LazyLoader' object has no attribute 'columns'
>>
>
>
> works for me, cant reproduce. please attach a full reproducing test
> case.
>
> >
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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
-~--~~~~--~~--~--~---
#!/usr/bin/python2.4
# -*- coding: utf-8 -*-
from sqlalchemy3 import *
import datetime, pickle, sys
engine = create_engine("sqlite://", echo=True)
metadata = BoundMetaData(engine)
users_table = Table("users", metadata,
Column("id", Integer, primary_key=True),
Column("user_name", String(16))
)
addresses_table = Table("addresses", metadata,
Column("id", Integer, primary_key=True),
Column("id_user", Integer, ForeignKey("users.id")),
Column("addr", String(100))
)
class User(object):
def __init__(self, user_name):
self.user_name = user_name
class Address(object):
def __init__(self, addr):
self.addr = addr
mapper(Address, addresses_table)
mapper(User, users_table, properties = {
"addresses" : relation(Address, cascade="all, delete-orphan",
backref=backref("user")),
}
)
metadata.create_all(engine)
s = create_session(bind_to=engine)
u1 = User("bob")
a1 = Address("bob's house")
a2 = Address("bob's flat")
u1.addresses.append(a1)
u1.addresses.append(a2)
s.save(u1)
u2 = User("alice")
a3 = Address("alice's house")
a4 = Address("alice's flat")
u2.addresses.append(a3)
u2.addresses.append(a4)
s.save(u2)
s.flush()
u = s.query(User).get_by_user_name("bob")
for i in s.query(Address).select_by(user=u):
print i.addr
[sqlalchemy] Re: splitting a relation into multiple properties
On Apr 12, 2007, at 2:03 PM, jason kirtland wrote:
>
>
> The 'analysis' relation is backed by a dict-like
> collection_class keyed by the type of instance, and storing them
> in a special list type that updates the ordering attribute.
> 'analysis' isn't accessed directly by user code.
>
> # looks kinda like
> an_idea.analysis == \
> { : [, ], : [] }
>
> Then I'm mapping 'pros' and 'cons' properties on the Idea class
> to connect to the right partition on the 'analysis' collection.
>
> This does work, but because relation updates are happening
> outside of the InstrumentedList (i.e. not on 'analysis' directly),
> I'm losing the events that would normally be triggered. I don't
> think I can manually manage them either, as they're private
> __methods on InstrumentedList.
some ideas which im not sure if theyd work, one is to not use
"collection_class" and to go with an approach that is more like the
AssociationProxy - i.e. "pro" and "con" have special collections on
them which proxy to the utlimate "associations" colleciton, but on
top of the InstrumentedList instead of underneath it the way
collection_class does.
another option is to go with the distinct "pro" and "con" relations -
set their loading to lazy=None and manually populate them yourself,
perhaps using a MapperExtension that manually inserts instances.
this would be tricky to implement since youd have to send along a
query that includes the JOIN that you want...although you could still
have the third "analysis" relation as eager loading and viewonly=True
to get that part of it going.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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] splitting a relation into multiple properties
I'm trying to map a single relation onto multiple properties.
Let's say I'm tracking "ideas", and a numbered list of pros and
cons for each idea:
Idea: beer
Pros:
1. tastes great
2. less filling
Cons:
1. warning: may not be true
Or in SQL:
Table('Ideas', metadata,
Column('id', Integer, primary_key=True),
Column('text', String))
Table('Analysis', metadata,
Column('id', Integer, primary_key=True),
Column('idea_id', Integer, ForeignKey('Ideas.id')),
Column('type', String(1)),
Column('position', Integer),
Column('text', String))
Pros and cons are stored in the analysis table, and have a 'P'
or 'C' type indicator. I'd like to do a polymorphic mapping on
the 'type' column, and have separate properties on the 'Idea'
class for each type of analysis, e.g.:
i = Idea()
i.pros = [Pro('tastes great'), Pro('less filling')]
i.cons = [Con('warning: may not be true')]
...and have the 'position' attribute maintained automatically by
its index in the Python list.
The first approach I took was with two relations:
# ... polymorphic mappings for Pro and Con ...
mapper(Idea, idea_table, properties={
'pros': relation(Pro, lazy=False,
primaryjoin=and_(ideas_table.c.id==ana_table.c.idea_id,
ana_table.c.type=='P'),
order_by=[ana_table.c.position],
collection_class=OrderingList),
'cons': relation(Con, lazy=False,
primaryjoin=and_(ideas_table.c.id==ana_table.c.idea_id,
ana_table.c.type=='C'),
order_by=[ana_table.c.position],
collection_class=OrderingList)
})
That works, but needs two separate JOINs to load both types. I
always want both types of analysis if I want any at all, so I
want to take the join load off the database and move it into
Python. I was inspired by the associationproxy approach of
providing an enhanced view on top of a regular SA relation
property:
mapper(Idea, idea_table, properties={
'analysis': relation(Analysis, lazy=False) # load all types
})
class Idea(object):
# some kind of mapping to 'analysis', e.g.
pros = some_magic_property_for('analysis')
cons = some_magic_property_for('analysis')
What I've done to use a single relation is working, but is much
more complicated than separate relations, mostly because of the
constraint that ordering attributes be synced with the Python
list. I'm wondering if maybe there isn't an alternate approach.
The 'analysis' relation is backed by a dict-like
collection_class keyed by the type of instance, and storing them
in a special list type that updates the ordering attribute.
'analysis' isn't accessed directly by user code.
# looks kinda like
an_idea.analysis == \
{ : [, ], : [] }
Then I'm mapping 'pros' and 'cons' properties on the Idea class
to connect to the right partition on the 'analysis' collection.
This does work, but because relation updates are happening
outside of the InstrumentedList (i.e. not on 'analysis' directly),
I'm losing the events that would normally be triggered. I don't
think I can manually manage them either, as they're private
__methods on InstrumentedList.
Anyone mapping anything similar, or have any thoughts about
other approaches?
-jek
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: selecting from a relation
On Apr 12, 2007, at 12:53 PM, ml wrote: > I tried s.query(Address).select_by(user=u) as I found similar in the > documentation > (http://www.sqlalchemy.org/docs/ > datamapping.html#datamapping_selectrelations_relselectby) > but SA raises: > AttributeError: 'LazyLoader' object has no attribute 'columns' > works for me, cant reproduce. please attach a full reproducing test case. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: query().filter_by(boolean)
please file a ticket for this. (Query should raise exceptions for non-ClauseElements passed) On Apr 12, 2007, at 12:02 PM, Marco Mariani wrote: > > I'm not trying the trunk, and it's the first time I use filter_by, > but I > guess: > > MappedClass.query().filter_by( MappedClass.column_name == 'Foo' ) > > equates to filter_by(False), because the .c is missing and it's > comparing an UOWProperty to a string, instead of a Column object to > a string > > Actually, in my case it gives me > > MappedClass.column_name < 'foo' == True > > and > > MappedClass.column_name > 'foo' == False > > > > In SA 0.3.6, the query runs and returns all rows, possibly spoiling > "something", "somewhere" > > If we do the same with MappedClass.select > (MappedClass.column_name=='Foo') > > it raises AttributeError: 'bool' object has no attribute > 'get_children' > > which, if not really explicit, let us know where to look > > Since instrumented attributes cannot be used in filter_by and friends > (http://www.mail-archive.com/[EMAIL PROTECTED]/ > msg03470.html) > can an exception be raised? > > tnx > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Many to many optimization
it needs to fire off the query to see whats changed when you go to flush(). otherwise it has no ability to know what needs to be saved. On Apr 12, 2007, at 11:43 AM, svilen wrote: > > while on the same subject, how do i copy one object's relatives to > another object without loading them all? > user1.addresses = user2.addreses does not work, it makes them share > the same InstrList > user1.addresses = user2.addreses[:] does work, but fires a full query > (maybe with obj-instantiation) > This is in the case of implicit association, using secondary=table > > On Thursday 12 April 2007 18:17:11 Michael Bayer wrote: >> On Apr 12, 2007, at 3:30 AM, Kaali wrote: >>> Thanks for the answers. >>> >>> I implemented message loading with find_members() kind of method, >>> as shown in the documentation link you gave, and it got twice as >>> fast. But it's still nowhere near the speed without the ORM. >> >> i get the impression youre trying to do a partial eager load. any >> query that you can execute by itself can be fed into the ORM and >> turned into object-mapped results, including the (partial, >> incomplete, whatever) fulfillment of whatever relationships you >> like, using query.instances(). have you looked into that ? >> >> > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Data from mysql db not returned by query
just do this: conn = mysql.db.connect() conn.close() On Apr 12, 2007, at 11:34 AM, vinjvinj wrote: > mysql.conn = mysql.db.connect() --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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] selecting from a relation
Hi!
Lets have:
##
users_table = Table("users", metadata,
Column("id", Integer, primary_key=True),
Column("user_name", String(16))
)
addresses_table = Table("addresses", metadata,
Column("id", Integer, primary_key=True),
Column("id_user", Integer, ForeignKey("users.id")),
Column("addr", String(100))
)
class User(object): pass
class Address(object): pass
mapper(Address, addresses_table)
mapper(User, users_table, properties = {
"addresses" : relation(Address, cascade="all, delete-orphan",
backref=backref("user")),
}
)
##
I have a user:
user = session.query(User).get_by_user_name("bob")
and I want some of his addresses using some criterion (e.g. all
beginning on "b") so I can't use user.addresses.
I know I can do s.query(Address).select_by(id_user=u.id) but that is not
very clean.
I tried s.query(Address).select_by(user=u) as I found similar in the
documentation
(http://www.sqlalchemy.org/docs/datamapping.html#datamapping_selectrelations_relselectby)
but SA raises:
AttributeError: 'LazyLoader' object has no attribute 'columns'
Any suggestion?
Thanks.
David
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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] sqlite + timezone oddities
I'm having issues with sqlite and DateTime objects with a timezone
attached (python 2.5, SQA 0.3.6, pysqlite-2.3.3,
OS X) crashing, but the code works with postgres. I get the same
issue with Python 2.4.
Given the test code below, with postgres I get
# Via object
1970-01-01 06:30:34+00:00
# Via dict
1970-01-01 07:30:34+01:00
But sqlite crashes:
# Via object
1970-01-01 06:30:34+00:00
# Via dict
Traceback (most recent call last):
File "test.py", line 40, in
print t.ts
File "build/bdist.macosx-10.3-fat/egg/sqlalchemy/engine/base.py",
line 1097, in __getattr__
File "build/bdist.macosx-10.3-fat/egg/sqlalchemy/engine/base.py",
line 917, in _get_col
File "build/bdist.macosx-10.3-fat/egg/sqlalchemy/databases/
sqlite.py", line 65, in convert_result_value
File "build/bdist.macosx-10.3-fat/egg/sqlalchemy/databases/
sqlite.py", line 58, in _cvt
File "/Library/Frameworks/Python.framework/Versions/2.5/lib/
python2.5/_strptime.py", line 313, in strptime
data_string[found.end():])
ValueError: unconverted data remains: +00:00
Code below:
import pytz
from datetime import datetime
from sqlalchemy import *
from sqlalchemy.ext.activemapper import metadata
TZ = pytz.timezone('UTC')
test_table = Table("testing", metadata,
Column("id", Integer, primary_key=True),
Column("ts", DateTime(timezone=True)),
)
class TestingObject(object):
def __init__(self, ts):
self.ts = ts
mapper(TestingObject, test_table)
db = create_engine('sqlite:///')
metadata.connect(db)
metadata.create_all()
session = create_session()
t = TestingObject(ts=datetime.fromtimestamp(23434, TZ))
session.save(t)
session.flush()
print '# Via object'
t2 = session.query(TestingObject).select()[0]
print t2.ts
print '# Via dict'
d = { 'ts' : datetime.fromtimestamp(23434, TZ)}
test_table.insert(d).execute()
t = test_table.select(test_table.c.id==2).execute().fetchone()
print t.ts
--
Arthur Clune
"Anyone who says they understand TCP/IP, doesn't" - Van Jacobsen
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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] Building multiple filter criteria through relations for a Query object
(Apologies for the somewhat long-winded subject line; should cover
the whole question though.)
Hi folks,
I'm new to SQLAlchemy, and rather new to SQL in general, so I've been
stumbling along trying to get a Pylons project of the ground (using
the SQLAlchemy setup as outlined at
http://www.rexx.com/~dkuhlman/pylons_quick_site.html).
The thing I've now come across, and to which I cannot find a fully
satisfying solution: how can I create multiple filter criteria using
relations, preferably step by step (ie, depending on certain
conditions/if blocks)?
An example hopefully clarifies the question. Say I have my example
database containing 3 tables. The 'computer' table has id, name and
price columns, as well as 2 foreign keys (disk_id & cpu_id), pointing to
'disk' and 'cpu' tables. 'disk' has id and size columns, and 'cpu'
has id
and speed columns.
My mapping is set up as follows:
computers = Table('computer', metadata, autoload=True)
disks = Table('disk', metadata, autoload=True)
cpus = Table('cpu', metadata, autoload=True)
class Computer(object):
pass
class Disk(object):
pass
class Cpu(object):
pass
mapper(Disk, disks)
mapper(Cpu, cpus)
mapper(Computer, computers, properties=dict(
disk=relation(Disk, backref='computer'),
cpu=relation(Cpu, backref='cpu')))
So I can now do the following, filtering on columns in the relations:
comps = session.query(Computer)
if True: # or some other condition
comps = comps.filter(and_(Disk.c.size>100,
Computer.c.disk_id==Disk.c.id))
if True: # or any condition
comps = comps.filter(and_(Cpu.c.speed>2,
Computer.c.cpu_id==Cpu.c.id))
# Sorting for nicely displaying
comps = comps.order_by(Disk.c.size).order_by(Computer.c.price)
for c in comps:
print c.price, c.name, c.cpu.speed, c.disk.size
print
But using the ids explicitly seems to defy the relations defined with
the mapper somewhat. I would like to be able to do (showing only the
filter() statements):
comps = comps.filter(Disk.c.size>100)
comps = comps.filter(Cpu.c.speed>2)
but for that I apparently need to explicitly define a join(), which
seems to work only when using one relation:
comps = comps.join('disk')
comps = comps.filter(Disk.c.size>100)
The above works, but adding the following to the above results in an
SQL error, because the 'computer' table is joined twice to the SQL
statement:
comps = comps.join('cpu')
comps = comps.filter(Cpu.c.speed>100)
filter_by() would do the job, but I can only use that with equal
statements [comps =
session.query(Computer).filter_by(size=100).comps.filter_by(speed=2.0)]
(not that I would actually compare floats with an equal sign anyway).
Any suggestions or pointers to what I might have missed in the docs?
Or is this (yet) simply not possible?
Thanks,
Evert
Python version 2.4.4; SQLAlchemy version 0.3.6; Pylons version0.9.4.1;
Postgres 8.2.3
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: IN() bug bit hard
On Apr 12, 6:37 pm, svilen <[EMAIL PROTECTED]> wrote: > how about whatever.in() -> (whatever and False) > maybe problem with Null then? (null and false) gives false, so not(null and false) is true. This means that not_(col.in_()) returns all rows, while not_(col.in_(nonexistantvalue)) returns all that are not null. One other option would be to return _BooleanExpression(self._compare_self(), null(), '=', negate='IS NOT'). The only problem is and edge case when someone uses an in_ with an comparison expression like so: col.in_() == False. This should return a list of non-null entries to be consistent, but instead returns an empty list. IMHO the expected behaviour is quite clear in all cases - just use the standard SQL idioms and relational idioms, comparison with null is null and no value is in an empty list. The CASE WHEN expr IS null THEN null ELSE false END expresses it quite nicely. The comparison is there to satisfy Oracle Firebird and Mssql, which don't like plain case expressions in where. I'd say that it makes the API conceptually simpler by removing a special case at the expense of some minor (compared to some other constructs that SA ORM emits) confusion when deciphering produced SQL statements. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Many to many optimization
sorry, ignore this, started a new thread --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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] many2many: how to copy one object's relatives to another object ?
how do i copy one object's relatives to another object, and if possible without loading them all? 1. user1.addresses = user2.addreses does not work, it makes them share the same InstrList 2. user1.addresses = user2.addreses[:] does work, but fires a full query (maybe with obj-instantiation) This is in the case of implicit association, using secondary=table 3. The case with intermediate association object is not so easy. Just shallow copying of any form does not help - now i have to make copies of the intermediate objects too! Any pointer? else i will just find my own way around it, like a special copy() on InstrumentedLists that should be smart enough (when given the info) to make difference between implicit assoc and explicit assoc.. svil --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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] query().filter_by(boolean)
I'm not trying the trunk, and it's the first time I use filter_by, but I guess: MappedClass.query().filter_by( MappedClass.column_name == 'Foo' ) equates to filter_by(False), because the .c is missing and it's comparing an UOWProperty to a string, instead of a Column object to a string Actually, in my case it gives me MappedClass.column_name < 'foo' == True and MappedClass.column_name > 'foo' == False In SA 0.3.6, the query runs and returns all rows, possibly spoiling "something", "somewhere" If we do the same with MappedClass.select(MappedClass.column_name=='Foo') it raises AttributeError: 'bool' object has no attribute 'get_children' which, if not really explicit, let us know where to look Since instrumented attributes cannot be used in filter_by and friends (http://www.mail-archive.com/[EMAIL PROTECTED]/msg03470.html) can an exception be raised? tnx --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Many to many optimization
while on the same subject, how do i copy one object's relatives to another object without loading them all? user1.addresses = user2.addreses does not work, it makes them share the same InstrList user1.addresses = user2.addreses[:] does work, but fires a full query (maybe with obj-instantiation) This is in the case of implicit association, using secondary=table On Thursday 12 April 2007 18:17:11 Michael Bayer wrote: > On Apr 12, 2007, at 3:30 AM, Kaali wrote: > > Thanks for the answers. > > > > I implemented message loading with find_members() kind of method, > > as shown in the documentation link you gave, and it got twice as > > fast. But it's still nowhere near the speed without the ORM. > > i get the impression youre trying to do a partial eager load. any > query that you can execute by itself can be fed into the ORM and > turned into object-mapped results, including the (partial, > incomplete, whatever) fulfillment of whatever relationships you > like, using query.instances(). have you looked into that ? > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
how about whatever.in() -> (whatever and False) maybe problem with Null then? On Thursday 12 April 2007 18:23:38 Michael Bayer wrote: > On Apr 12, 2007, at 9:46 AM, Ants Aasma wrote: > > On Apr 12, 1:59 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > >> agreed, as long as we know that saying "somecolumn != > >> somecolumn" is valid and produces False on all dbs (including > >> frequent-offenders firebird and ms-sql) ? (thats how you > >> interpreted IN (), right ?) > > > > It works (almost) ok in MSSQL-8, Sqlite-2/3, PostgreSQL 8.1/8.2, > > MySQL 5.0, Oracle 10g and Firebird 1.5.3. It works with literals, > > columns, expressions, subselect expressions, no rows subselects > > in all of them. It fails when the expression is a volatile > > function or a function with side effects (e.g. > > func.random().in_()). The latter two cases will work ok, if you > > compile it as ((CASE WHEN expr IS NULL THEN NULL ELSE 0 END) = 1) > > see, now i am not liking this approach so much. if someone says > somecolumn.in_(), and it produces "(CASE WHEN IS NULL > THEN NULL ELSE 0 END) = 1", thats a *big* surprise. i really dont > want SA to be a very "thick" layer of guessage and > fixing-the-users- mistakes. > > i would almost say we compile somecolumn.in() to just... "0" (if > that even works, havent tried)...but even then, if someone executes > a function that is expected to have side effects (extremely rare > situation), we may be overstepping. > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Data from mysql db not returned by query
> each query, or just returning it to the connection pool via close()
> (which also calls rollback()) and then re-acquiring it from the pool
> as needed.
I have a wrapper function called execute() whcih traps any errors and
then recreates the mysql engine object and tries to resubmit the qry:
def initialize_connection():
mysql.db = create_engine('mysql://%s:[EMAIL PROTECTED]/%s' %
(config.DB_USER,
config.DB_PASS, config.DB_HOST, config.DB_NAME))
mysql.conn = mysql.db.connect()
initialize_connection()
def execute(qry):
try:
_rows = mysql.conn.execute(qry)
except:
initialize_connection()
_rows = mysql.conn.execute(qry)
rows = []
for i, row in enumerate(_rows):
row_dict = attrdict()
if i == 0:
column_headers = row.keys()
for key in column_headers:
row_dict[key] = getattr(row, key)
rows.append(row_dict)
return rows
Should I add:
mysql.conn = mysql.db.connect()
mysq.close()
at the beginning and end of the function?
I convert the result set to a list of dicts since I get not compress
and pickle the objects being returned by sqlalchemy.
Thanks,
Vineet
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: Data from mysql db not returned by query
On Apr 12, 2007, at 10:31 AM, vinjvinj wrote: > The problem is that the data is not seen by the second application > server. When I log into to mysql from my desktop I CAN see the data. > The problem goes away when I restart the application server with sql- > alchemy. I'm caching the mysql.db connection object. its possible that the connection youve retrieved, once used to query a table, now has an open transaction on it and is not reading the actual results. consider calling rollback() on the connection after each query, or just returning it to the connection pool via close() (which also calls rollback()) and then re-acquiring it from the pool as needed. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
On Apr 12, 2007, at 9:46 AM, Ants Aasma wrote: > > On Apr 12, 1:59 am, Michael Bayer <[EMAIL PROTECTED]> wrote: >> agreed, as long as we know that saying "somecolumn != somecolumn" is >> valid and produces False on all dbs (including frequent-offenders >> firebird and ms-sql) ? (thats how you interpreted IN (), right ?) > > It works (almost) ok in MSSQL-8, Sqlite-2/3, PostgreSQL 8.1/8.2, MySQL > 5.0, Oracle 10g and Firebird 1.5.3. It works with literals, columns, > expressions, subselect expressions, no rows subselects in all of them. > It fails when the expression is a volatile function or a function with > side effects (e.g. func.random().in_()). The latter two cases will > work ok, if you compile it as ((CASE WHEN expr IS NULL THEN NULL ELSE > 0 END) = 1) see, now i am not liking this approach so much. if someone says somecolumn.in_(), and it produces "(CASE WHEN IS NULL THEN NULL ELSE 0 END) = 1", thats a *big* surprise. i really dont want SA to be a very "thick" layer of guessage and fixing-the-users- mistakes. i would almost say we compile somecolumn.in() to just... "0" (if that even works, havent tried)...but even then, if someone executes a function that is expected to have side effects (extremely rare situation), we may be overstepping. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
On Apr 12, 2007, at 3:32 AM, svilen wrote: > it, but if it's once in a blue moon, u'll get one more disappointed > SA user ;0(. right, and then i dont find out until i meet them at Pycon ;) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Many to many optimization
On Apr 12, 2007, at 3:30 AM, Kaali wrote: > > Thanks for the answers. > > I implemented message loading with find_members() kind of method, as > shown in the documentation link you gave, and it got twice as fast. > But it's still nowhere near the speed without the ORM. i get the impression youre trying to do a partial eager load. any query that you can execute by itself can be fed into the ORM and turned into object-mapped results, including the (partial, incomplete, whatever) fulfillment of whatever relationships you like, using query.instances(). have you looked into that ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Bit strange - no expunge required?
On Apr 11, 2007, at 11:15 PM, Arun Kumar PG wrote: > Hi Michael, > > So how can I prevent this as I can't access the lazyload attributes > in my manager class once I get the result set from DAO as i get no > parent session/contextual session exists exception. > > should I maintain a reference to the session object in the DAO > class so that it is not garbage collected ? heres the approaches in descending order of inconvenience: you can, as a workaround, immediately access all the lazy load relations in your getResults() method...i do this with hibernate a lot. otherwise, one option is to explicitly keep a Session around that doesnt get garbage collected, like you mention. but what a lot of people do is use the SessionContext extension with your mappers. that way when the lazy loader fires off, it looks for the Session, but if it cant find it, calls mapper.get_session() which then calls the SessionContextSessionContext then creates a new session if one does not exist already for the current thread. so using SessionContext is kind of like replacing the weakly-referenced Session with a Session that is bound to a thread. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: just what does 'delete-orphan' bring us ?
Thanks very much, all clear now. -f --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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.orm.attributes.InstrumentedList
same way, recursively: check 1st element if mylist and isinstance( mylist[0], yourlisttype): ... or check all elements, or... On Thursday 12 April 2007 17:52:28 Disrupt07 wrote: > Thanks. 'if isinstance(your_object, > sqlalchemy.orm.attributes.InstrumentedList):' was helpful to me. > > Now I want to check if the given list is either a list of elements > or a list of lists. > Example: > list1 = ['a', 'b', 'c'] > list2 = [['a', 'b'], ['c', 'd'], ['e']] > > How can I check for lists within a list so that I would know what > type of list I am processing? > > Thanks > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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.orm.attributes.InstrumentedList
Thanks. 'if isinstance(your_object, sqlalchemy.orm.attributes.InstrumentedList):' was helpful to me. Now I want to check if the given list is either a list of elements or a list of lists. Example: list1 = ['a', 'b', 'c'] list2 = [['a', 'b'], ['c', 'd'], ['e']] How can I check for lists within a list so that I would know what type of list I am processing? Thanks --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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] Data from mysql db not returned by query
Hi Guys,
I'm stumped with this and would appreciate any insight. I'm using
mysql.db = create_engine('mysql://%s:[EMAIL PROTECTED]/%s' % (config.DB_USER,
config.DB_PASS, config.DB_HOST, config.DB_NAME))
mysql.conn = mysql.db.connect()
mysql.conn.execute(qry)
I have three servers. Two application servers and one db server. One
application server has written to the db.
The problem is that the data is not seen by the second application
server. When I log into to mysql from my desktop I CAN see the data.
The problem goes away when I restart the application server with sql-
alchemy. I'm caching the mysql.db connection object.
Any ideas why I'm not seeing the data from sql-alchemy. I understand
that sql-alchemy does not cache data so that cannot be the problem.
Thanks,
Vineet
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: IN() bug bit hard
On Apr 12, 1:59 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > agreed, as long as we know that saying "somecolumn != somecolumn" is > valid and produces False on all dbs (including frequent-offenders > firebird and ms-sql) ? (thats how you interpreted IN (), right ?) It works (almost) ok in MSSQL-8, Sqlite-2/3, PostgreSQL 8.1/8.2, MySQL 5.0, Oracle 10g and Firebird 1.5.3. It works with literals, columns, expressions, subselect expressions, no rows subselects in all of them. It fails when the expression is a volatile function or a function with side effects (e.g. func.random().in_()). The latter two cases will work ok, if you compile it as ((CASE WHEN expr IS NULL THEN NULL ELSE 0 END) = 1) Ants --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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.orm.attributes.InstrumentedList
Disrupt07 wrote > > What is sqlalchemy.orm.attributes.InstrumentedList? > > I need to use the sqlalchemy.orm.attributes.InstrumentedList type in > my Python controller methods and need to check if the type of another > object is of type sqlalchemy.orm.attributes.InstrumentedList. How can > I do this? (e.g. using the type() function and what shall I import in > my controller file?) > > Thanks. > Well, you could import sqlalchemy.orm.attributes, and then when you want to check the type of an object you would say 'if isinstance(your_object, sqlalchemy.orm.attributes.InstrumentedList):' Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: 'PropertyLoader' object has no attribute 'strategy'
Hi Michael, As Simon suggested, I am now using log.except() instead log.error(), so I hope the next exception will be followed by more information. Thanks On 4/11/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > > > On Apr 11, 2007, at 1:46 AM, Roger Demetrescu wrote: > > > > > Hi all, > > > > I have a daemon with 2 threads to control upload / download of some > > files (they use SQLAlchemy to find out which files must be worked). > > > > Once a week, my daemon's logging system sends me an email with this > > message: > > > > 'PropertyLoader' object has no attribute 'strategy' > > > > > > > > After that, I receive another email with this message: > > > > global name 'anxnews_urllocal' is not defined > > > > where 'anxnews_urllocal' is a field from a table. > > > > > > > > I usually don't need to touch this daemon... it still works fine even > > after this alert. > > > > Any hints about what could be causing this exception ? > > > > one or both of thread synchronization / module import based issues. > id be curious to know if the PropertyLoader exception occurs within > the Mapper.compile() method. I do have a mutex on that system to > avoid concurrency issues when the mapper first compiles (its one of > the only mutexes within all of SA), but you know how grumpy mutexes > can be. > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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.orm.attributes.InstrumentedList
What is sqlalchemy.orm.attributes.InstrumentedList? I need to use the sqlalchemy.orm.attributes.InstrumentedList type in my Python controller methods and need to check if the type of another object is of type sqlalchemy.orm.attributes.InstrumentedList. How can I do this? (e.g. using the type() function and what shall I import in my controller file?) Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
just my point of view: generatively, i would probably do .in_( somelist ), where somelist is a variable, and it will work fine until that list gets empty. If that happens frequently, okay, i'll know soon about it and fix it/avoid it, but if it's once in a blue moon, u'll get one more disappointed SA user ;0(. And if that list is a bindparam, it gets even harder to check/guess. > On Apr 11, 2007, at 6:13 PM, Ants Aasma wrote: > > b) produce IN () SQL > > + 1:1 mapping between python and sql > > - most databases will give obscure errors, possibly far away from > > source of error > > well i dont think it would be a hard error to track down. the ORM > doesnt use in_() so if an in_() happens, its because a user used it > in their own code. > > > - different behaviour between databases > > it would raise an error on most but SA tries to support DB > idiosyncracies, like SQLite's accepting it > > > c) do a natural extension of SQL IN syntax > > + makes user code simpler in non negligible amount of cases > > agreed > > > + behaves the same in all databases > > agreed, as long as we know that saying "somecolumn != somecolumn" > is valid and produces False on all dbs (including > frequent-offenders firebird and ms-sql) ? (thats how you > interpreted IN (), right ?) > > > - some one could possibly expect different semantics > > yup, thats what im hoping to avoid. im still concerned we're just > "guessing" here, and when users are surprised, its SA's job (i.e. > mine, usually...) to then explain/justify > > but overall, i dont care much either way so leave my vote out of > this one. > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Many to many optimization
Thanks for the answers.
I implemented message loading with find_members() kind of method, as
shown in the documentation link you gave, and it got twice as fast.
But it's still nowhere near the speed without the ORM. Makes me a bit
sad, as i really liked the ORM system. Maybe if i remove any automatic
relations and manually get them it would be faster.
Or maybe i should use ORM normally, but work without it on
bottlenecks.
--
K
On Apr 2, 8:20 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> sqlalchemy relationships are currently only "fully" loading - meaning
> you cant directly filter on an instances collection attribute.
>
> however you can construct your own query based on the relationship
> and use that, and theres plenty of tools to make that easy.
>
> such as, if you set up a bi-directional relationship:
>
> class A(object):pass
> class B(object):pass
>
> mapper(B, b_table)
> mapper(A, a_table, properties={
> "b_collection" : relation(A, secondary=a_to_b_table,
> backref="a_collection")
>
> })
>
> if you load an instance of "A":
>
> mya = session.query(A).select(a.id=7)
>
> you can query the "B"s on an "A" via the backref:
>
> result = session.query(B).filter_by(a_collection=mya).filter
> (A.c.somecriterion=='foo').list()
>
> theres also some patterns for dealing with large collections at:
>
> http://www.sqlalchemy.org/docs/
> adv_datamapping.html#advdatamapping_properties_working
>
> On Apr 2, 2007, at 7:44 AM, Kaali wrote:
>
>
>
> > Can i use ORM many-to-many relations and filter SQL -side? If i can't,
> > can you give me an example on how i should use many-to-many relations
> > with filtering in SQLAlchemy?
>
> > --
> > K
>
> > On Apr 2, 1:36 pm, svilen <[EMAIL PROTECTED]> wrote:
> >>> After getting the results, i will filter them with Python filters,
> >>> as i don't know how to filter many-to-many queries directly.
>
> >>> Should i somehow make custom queries that handles many-to-many
> >>> relationships etc. or is there something else i'm missing that
> >>> makes the system slow? I have ran the bench with MySQL and
> >>> PostgreSQL engines, the result is the same. When running with a
> >>> profiler, at least ~90% of the time is taken by SQLAlchemy.
>
> >> one general suggestion, try to move the filtering on the sql side;
> >> thus u'll have less data transferred then instantiated then
> >> filtered - which is probably eating most of the time.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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
-~--~~~~--~~--~--~---
