[sqlalchemy] Re: concurent modification
Thanks a lot, seems I've managed resolve problem with concurrent
modifications by commit(), clear() and close() at each thread, but
stuck with another one:
"""
Exception in thread Thread-62:
Traceback (most recent call last):
File "threading.py", line 442, in __bootstrap
self.run()
File "./camper.py", line 109, in run
theone = s.query(User).filter_by(username=user).first()
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py",
line 627, in first
ret = list(self[0:1])
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py",
line 656, in __iter__
return self._execute_and_instances(context)
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py",
line 659, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self.mapper,
instance=self._refresh_instance)
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py",
line 528, in execute
return self.__connection(engine,
close_with_result=True).execute(clause, params or {})
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py",
line 510, in __connection
return self.transaction.get_or_add(engine)
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py",
line 188, in get_or_add
c = bind.contextual_connect()
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 1160, in contextual_connect
return Connection(self, self.pool.connect(),
close_with_result=close_with_result, **kwargs)
File "sqlalchemy/pool.py", line 163, in connect
File "sqlalchemy/pool.py", line 296, in __init__
File "sqlalchemy/pool.py", line 173, in get
File "sqlalchemy/pool.py", line 571, in do_get
TimeoutError: QueuePool limit of size 5 overflow 10 reached,
connection timed out, timeout 30
"""
Perhaps problem it is caused by long running threads that locking
table. So another threads lines up in queue and exception appears
after limit is reached.
The question is it exists a way to resolve this problem not touching
default values like size of queue or timeout ?
--~--~-~--~~~---~--~~
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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Hi,
>
>
Ok, you got my attention :-) Not at my best right now after being out
drinking, but hey...
After a little tweak to the code (removing autoload=True, adding
metadata.create_all() ) I get this:
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) ('42000',
'[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion
failed when converting from a character string to uniqueidentifier.
(8169)') u'INSERT INTO jobs (identifier, section, start, stop, station)
VALUES (?, ?, ?, ?, ?)' ['TEST1', None, datetime.datetime(2007, 12, 10,
23, 40, 30,593000), None, None]
So, follow Rick's advice on fixing it. This does work with SQLite, but
that's an accident of SQLite's funky type system more than anything.
Paul
--~--~-~--~~~---~--~~
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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
> I did not get any exception... doh! :) What kind of exception did
> you get?
The traceback I get is below. If you're not getting one, it may be a pyodbc
issue, which I don't have installed right now.
Traceback (most recent call last):
File "test.py", line 31, in ?
sa_session.commit()
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/session.py", line 484, in commit
self.transaction = self.transaction.commit()
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/session.py", line 211, in commit
self.session.flush()
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/session.py", line 684, in flush
self.uow.flush(self, objects)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/unitofwork.py", line 207, in flush
flush_context.execute()
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/unitofwork.py", line 434, in execute
UOWExecutor().execute(self, head)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/unitofwork.py", line 1053, in
execute
self.execute_save_steps(trans, task)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/unitofwork.py", line 1067, in
execute_save_steps
self.save_objects(trans, task)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/unitofwork.py", line 1058, in
save_objects
task.mapper.save_obj(task.polymorphic_tosave_objects, trans)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/mapper.py", line 1129, in save_obj
c = connection.execute(statement.values(value_params), params)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/engine/base.py", line 796, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/engine/base.py", line 847, in
execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/engine/base.py", line 859, in
_execute_compiled
self.__execute_raw(context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/engine/base.py", line 871, in
__execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-
0.4.2dev_r3844-py2.4.egg/sqlalchemy/engine/base.py", line 887, in
_cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) internal error: SQL
Server message 245, severity 16, state 1, line 1:
Conversion failed when converting the varchar value 'TEST1' to data type
int.
DB-Lib error message 20018, severity 5:
General SQL Server error: Check messages from the SQL Server.
'INSERT INTO jobs (identifier, section, start, stop, station) VALUES
(%(identifier)s, %(section)s, %(start)s, %(stop)s, %(station)s)' {'start':
datetime.datetime(2007, 12, 10, 18, 15, 23, 170889), 'section': None,
'station': None, 'stop': None, 'identifier': 'TEST1'}
--~--~-~--~~~---~--~~
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: Lazy ID Fetching/generation
> But another thing, is that the whole idea of "save/update/save-or- > update", which we obviously got from hibernate, is something ive been > considering ditching, in favor of something more oriented towards a > "container" like add(). since i think even hibernate's original idea > of save/update has proven to be naive (for example, this is why they > had to implement saveOrUpdate()). we like to keep things explicit as > much as possible since thats a central philosophical tenet of Python. Hmm, that sounds interesting. Would it have similar flush() semantics like .save(), or would it be a kind of auto-flush thing? The issues with any implicit kind of flush() are tricky. Maybe not so much for the instance being .add() ed or .save() ed, those are usually somewhat stratightforward. The tricky parts are the related instances. Would relation()-based instances also be auto-flushed() and etc. --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
Rick Morrison wrote: > Wouldn't a flavor of .save() that always flush()'ed work for this case? > > say, Session.persist(obj) > > Which would then chase down the relational references and persist the > object graph of that object...and then add the now-persisted object to > the identity map. > > ...something like a 'mini-flush'. Almost, except I would want it to only flush if I tried to access a db-generated attribute. The normal "lazy" behavior otherwise makes perfect sense to me. -Adam Batkin --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
Wouldn't a flavor of .save() that always flush()'ed work for this case? say, Session.persist(obj) Which would then chase down the relational references and persist the object graph of that object...and then add the now-persisted object to the identity map. ...something like a 'mini-flush'. --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
On Dec 10, 2007, at 4:53 PM, Adam Batkin wrote: > > My mental model has always been that once you save() something, it > will > be at _least_ Pending, but might transition at any moment to > Persistent. > This is only important because in my opinion, once you save() an > instance, if you then poke at it's id attribute, you probably > shouldn't > be disappointed if the id is suddenly not None (even though you never > issued an explicit flush()). (for example if you issue a query that > involves that table) yeah SQLAlchemy started, like Rick was getting at, with a much "closer to the metal" idea than that, that if you made a new object and put it in the session, youd "know" that it wasnt flushed yet. My experience with hibernate is identical, actually, nothing gets generated or anything in our environment over here until the flush happens. But another thing, is that the whole idea of "save/update/save-or- update", which we obviously got from hibernate, is something ive been considering ditching, in favor of something more oriented towards a "container" like add(). since i think even hibernate's original idea of save/update has proven to be naive (for example, this is why they had to implement saveOrUpdate()). we like to keep things explicit as much as possible since thats a central philosophical tenet of Python. --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
Michael Bayer wrote: > > On Dec 10, 2007, at 4:12 PM, Adam Batkin wrote: > >> No, I strongly disagree. Once you save() an object, there are >> absolutely >> no guarantees about when it will be flushed (other than that it will >> happen the transaction is actually committed). >> > > you can guarantee an object is flushed by saying flush(). or if you > just retrieved it from a Query, youre similarly guaranteed that its > flushed. whats the speciic use case you have that youre concerned > about ? nobodys ever had this concern before AFAIK. > >> I can't find any place where sqlalchemy makes any guarantees regarding >> the transition from Pending to Persistent state. Which is why I think >> that objects in the Pending state should function (to the best of >> sqlalchemy's ability) in as close to the same way as possible. > > why is flush() not a guarantee ? it seems really simple to me... Sorry, I should have been clearer. Obviously once you've actually flush()'d there are no problems, and the docs make it clear that a flush() does in fact ensure that everything transitions from Pending to Persistent. My mental model has always been that once you save() something, it will be at _least_ Pending, but might transition at any moment to Persistent. This is only important because in my opinion, once you save() an instance, if you then poke at it's id attribute, you probably shouldn't be disappointed if the id is suddenly not None (even though you never issued an explicit flush()). (for example if you issue a query that involves that table) Regarding the other e-mail you just responded to... >> I know for a fact that Hibernate does it this way (not that sqlalchemy >> has to do everything Hibernate does), and I can't imagine a use case >> where doing what's needed to retrieve database-generated fields on an >> as-needed basis would be considered incorrect behavior. >> > > ive never seen that one before, i.e. the id generator being called in > direct reaction to calling myentity.getId(). In our hibernate apps we > often check for the id being null in order to check if the entity is > persisted yet. if you only mean that hibernate generates its own IDs > externally to INSERTs, yes thats true. SQLAlchemy would not use that Here's what I have seen in Hibernate (at least on Oracle, and I assume Postgres would be similar, assuming db sequences are used for pk generation): Create an object, save it. No db activity yet. Call object.getId(). Nothing inserted, but you can see a value IS pulled from the database sequence. I should test Hibernate with mysql to see if it actually performs a full insert of the row. > 2. I can show you the less-than-public API we use to put "value > generation" callables on attributes, i.e. the same one that issues > deferred loads and lazy relation loads. this would be a little more > like the start of an actual feature. > 3. building on #2 would be some feature to SA called "eager-fetch-ids" > or something like that, which looks at the Column for a > DefaultGenerator that is executable (Sequences, for example, are > executable DefaultGenerator objects). it would probably just take a > list of keys and apply to any columns you want, not just pk cols. That'd be lovely. Although I don't generally use databases that lack sequences very often, perhaps someone who does would also find useful so it might be interesting to see what the implications of having to flush a whole object to the DB if a DefaultGenerator column is accessed after a save() but before a flush() (or implicit flush due to a query). -Adam Batkin --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Thanks... i'll try changing to numeric... On 10 Dic, 19:57, "Rick Morrison" <[EMAIL PROTECTED]> wrote: > I did get an exception, that's how I knew to change the type! > I did not get any exception... doh! :) What kind of exception did you get? --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
On Dec 10, 2007, at 4:11 PM, Michael Bayer wrote: > > Another example, which we actually support right now...but its because > a flush occurs: > > x = Foo() > x.id = 7 > for a in x.addresses: # lazy load of "where x=7". >> sorry, scratch that one, its a little nonsensical, replace with: a = Address() x = Foo() x.id = 7 x.addreses.append(a) assert a.foo_id == 7 which above, is actually doable without a flush. however to make the above work without a flush would be extermely complicating to SA's internals right now, since we have built around the model of "no ids are available until flush". which is necessary due to the no native ID generators available issue (but also because its nice for ids and sequence generators to not fire off before the record is persisted). --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
On Dec 10, 2007, at 4:12 PM, Adam Batkin wrote: > > No, I strongly disagree. Once you save() an object, there are > absolutely > no guarantees about when it will be flushed (other than that it will > happen the transaction is actually committed). > you can guarantee an object is flushed by saying flush(). or if you just retrieved it from a Query, youre similarly guaranteed that its flushed. whats the speciic use case you have that youre concerned about ? nobodys ever had this concern before AFAIK. > I can't find any place where sqlalchemy makes any guarantees regarding > the transition from Pending to Persistent state. Which is why I think > that objects in the Pending state should function (to the best of > sqlalchemy's ability) in as close to the same way as possible. why is flush() not a guarantee ? it seems really simple to me... --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
Rick Morrison wrote: > > > Having to > > call flush before doing anything that might require the ID seems > > excessive and too low-level for code like that. > > Why? To me, having to work around the implications of an implicit > persisting of the object for nothing more than a simple attribute access > is much worse. For example, I have code that examines the instance id > attribute of such objects to determine, for example, whether an item > discount needs to be recalculated (not saved) or a differential discount > needs to be calculated (item already saved). It's natural to simply > examine the PK attribute to see if the item is persisted to make the > decision. In this case, an object.is_saved() method could stand in, but > imagine passing your object instance to some other Python function that > just happened to sniff around attributes -- such behavior could cause > the item to flush() without explicit permission? Yuck. I think you missed the part where I said that the object in question was already save()'d, so at any moment it could be flushed and the id magically filled-in. Once you save() an object, you never know when it will be flushed. > > "As far as the application is concerned, objects in the Pending and > > Persistent states should function identically." > > To me, this is a fallacy of how ORMs work, and ignores the particulars > of what happens, or what could happen, during a database save > round-trip. You could have default columns. You could have triggers. > Your could have DRI violations. The database engine could do implicit > type conversion. You simply cannot expect an unchanged object on a > round-trip to a relational database in a real-world case. To always > expect this is to invite huge complexity issues that have been the > downfall of other ORM attempts. For pure unchanged round-trip behavior, > you want a real OO database, not an ORM. No, I strongly disagree. Once you save() an object, there are absolutely no guarantees about when it will be flushed (other than that it will happen the transaction is actually committed). An example: In sqlalchemy, if I create an object and save() it, it won't be flushed (yet. probably). If I then execute some arbitrary query (say, session.query(Something).filter(Something.c.name=='foo').all()) then sqlalchemy WILL flush that object I just saved! And that's the correct behavior because it would be nearly impossible for sqlalchemy to correctly determine if the object I just saved should be returned with the query. In other words, I never flushed, but it got flushed anyway. I'm not saying that sqlalchemy should always flush every object once you start touching any of its attributes. But for attributes that we KNOW need to be fetched, they should be treated exactly the same way that arbitrary queries treat Pending objects. I can't find any place where sqlalchemy makes any guarantees regarding the transition from Pending to Persistent state. Which is why I think that objects in the Pending state should function (to the best of sqlalchemy's ability) in as close to the same way as possible. Just some thoughts, -Adam Batkin --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
On Dec 10, 2007, at 3:07 PM, Adam Batkin wrote:
> Can you suggest other alternatives for the above use case? Having to
> call flush before doing anything that might require the ID seems
> excessive and too low-level for code like that.
>
> I know for a fact that Hibernate does it this way (not that sqlalchemy
> has to do everything Hibernate does), and I can't imagine a use case
> where doing what's needed to retrieve database-generated fields on an
> as-needed basis would be considered incorrect behavior.
>
ive never seen that one before, i.e. the id generator being called in
direct reaction to calling myentity.getId(). In our hibernate apps we
often check for the id being null in order to check if the entity is
persisted yet. if you only mean that hibernate generates its own IDs
externally to INSERTs, yes thats true. SQLAlchemy would not use that
method by default since most of our users want to use their databases
native ID generation utilities, which on sqlite, mysql and others
only occur during INSERT, but we have no issue with providing that
method of id generation as an option if people want it (and of course
we have the hooks for user-defined default generation functions).
So, im assuming the desired feature here (before getting into your
furher comments below) is, upon access to a primary key attribute, get
the id, *without* issuing an INSERT or a flush, and assign it to the
attribute. Thats pretty easy to do provided you dont mind using a non-
native id generation method on certain databases.
So, first to get the id, either:
1. use a sequence, dont use mysql or sqlite
2. use an external id generation function. if youd like to provide
ones that work with databases like mysql and sqlite, and they are
atomic/threadsafe/all that good stuff, we can add them as features.
i dont get into those myself because of the various issues with
atomcity but ive no issue with using one that is properly constructed.
So if either ID generation function of #1 or #2 is available, they can
easily be installed on a mapped class. Theres three ways this can be
done, and the first two you can play with without us modifying
SQLAlchemy at all.
1. just build a class property which checks for none, calls the id
generator and applies.0.4.2 has an easier syntax for setting up
"synonyms" which allow you to decorate the behavior of a class
attribute, but you can do this with any version of SA (just that it
works better in 0.4.2).
2. I can show you the less-than-public API we use to put "value
generation" callables on attributes, i.e. the same one that issues
deferred loads and lazy relation loads. this would be a little more
like the start of an actual feature.
3. building on #2 would be some feature to SA called "eager-fetch-ids"
or something like that, which looks at the Column for a
DefaultGenerator that is executable (Sequences, for example, are
executable DefaultGenerator objects). it would probably just take a
list of keys and apply to any columns you want, not just pk cols.
I dont consider the above behavior, or lack of it, to be "correct" or
"incorrect". i think most of our users would be surprised by it and
would prefer to leave it off, but if some people prefer it thats fine
by me.
now the next comment, much deeper can of worms:
> "As far as the application is concerned, objects in the Pending and
> Persistent states should function identically."
OK, above we are talking just about primary key generation...its not
that big a deal. But here, the difference between pending and
persistent is huge. If your proposal is, "pending acts just like
persistent, without flushing", i would ask how you'd approach these
examples:
x = Foo()
print x.id # generates id, prints 1
enhedgine.execute("select * from table where id=" + x.id)
above, the query is directly to the DB.
Another example, which we actually support right now...but its because
a flush occurs:
x = Foo()
x.id = 7
for a in x.addresses: # lazy load of "where x=7".
> (it's possible that this feature would be difficult to implement, in
> which case that's a good answer and maybe it can go on to a far-off
> wishlist, or I can try to implement it or something, I just don't
> see a
> way for it to be considered incorrect behavior)
like i said none of this is "incorrect", but if you are getting at the
idea that we'd start querying for things within the session that
havent been flushed, i.e. effectively building our own "select" engine
in python, that would get really hairy. we will eventually implement
some invalidation of persistent objects based on criterion, but thats
about it, and that can still issue SQL if it needs to since it only
acts on persistent instances.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Goog
[sqlalchemy] Re: Lazy ID Fetching/generation
> Having to > call flush before doing anything that might require the ID seems > excessive and too low-level for code like that. Why? To me, having to work around the implications of an implicit persisting of the object for nothing more than a simple attribute access is much worse. For example, I have code that examines the instance id attribute of such objects to determine, for example, whether an item discount needs to be recalculated (not saved) or a differential discount needs to be calculated (item already saved). It's natural to simply examine the PK attribute to see if the item is persisted to make the decision. In this case, an object.is_saved() method could stand in, but imagine passing your object instance to some other Python function that just happened to sniff around attributes -- such behavior could cause the item to flush() without explicit permission? Yuck. > "As far as the application is concerned, objects in the Pending and > Persistent states should function identically." To me, this is a fallacy of how ORMs work, and ignores the particulars of what happens, or what could happen, during a database save round-trip. You could have default columns. You could have triggers. Your could have DRI violations. The database engine could do implicit type conversion. You simply cannot expect an unchanged object on a round-trip to a relational database in a real-world case. To always expect this is to invite huge complexity issues that have been the downfall of other ORM attempts. For pure unchanged round-trip behavior, you want a real OO database, not an ORM. --~--~-~--~~~---~--~~ 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: From arbitrary SELECT to Query
On Dec 7, 2007, at 2:39 PM, Artur Siekielski wrote: > The problem is that I get normal Python list, which eats much > resources when database is big. Much better would be Query object > which supports lazy loading. Note that I cannot use > Query.filter(compoundSelect._whereclause) because CompundSelect > doesn't have _whereclause. id just point out also that, we havent decided against the Query object yielding results as theyre received. i pointed out earlier in this thread that its complicated, but this is something we might finally try to tackle soon. --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
>> I hate to disagree here, and I can see what you're getting at, but
>> honestly, the "INSERT on save()" approach is exactly the naive active-
>> record-like pattern that SQLAlchemy's ORM was designed to get away from.
>>
>> The way the unit of work functions, we dont generate ids until a flush
>> occurs. Flushes dont occur unless you say flush(), or if you have
>
> I'm not saying flush on save. I'm saying flush at the last possible
> moment (which is what it does now) but I want "last possible moment" to
> include "program tried to access a database-generated field"
>
> s1 = Something('foo1')
> session.save(s1)
> s2 = Something('foo2')
> session.save(s2)
> # Nothing flushed yet
> s3 = Something('foo3')
> session.save(s3)
> url_for_foo = "/something?id=%d" % s3.id
> # s3 should be flushed, nothing else though (since s3.id was accessed)
Can you suggest other alternatives for the above use case? Having to
call flush before doing anything that might require the ID seems
excessive and too low-level for code like that.
I know for a fact that Hibernate does it this way (not that sqlalchemy
has to do everything Hibernate does), and I can't imagine a use case
where doing what's needed to retrieve database-generated fields on an
as-needed basis would be considered incorrect behavior.
I think what I'm asking for can be summarized this way:
"As far as the application is concerned, objects in the Pending and
Persistent states should function identically."
(it's possible that this feature would be difficult to implement, in
which case that's a good answer and maybe it can go on to a far-off
wishlist, or I can try to implement it or something, I just don't see a
way for it to be considered incorrect behavior)
Thanks,
-Adam Batkin
--~--~-~--~~~---~--~~
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: Question about using python expressions to create a GIS query
On Dec 10, 2007 1:11 PM, Chris M <[EMAIL PROTECTED]> wrote:
>
> use bound_box = sa.text("'BOX3D(5 5, 25 25)'::box3d")
Thanks, that worked perfectly.
Now I am going to write a method that will build an SA clause element
for this given a bounding box and a column to test.
Thanks for the help.
-Allen
>
> On Dec 10, 12:12 pm, "Allen Bierbaum" <[EMAIL PROTECTED]> wrote:
>
> > On Dec 10, 2007 9:08 AM, Allen Bierbaum <[EMAIL PROTECTED]> wrote:
> >
> >
> >
> > > I am trying to figure out how to best use SA to create a GIS query.
> > > In my application I am actually using ORM objects and mappers, but to
> > > keep my question focused on clauses and python expressions, I am just
> > > trying to test this out without the ORM first.
> >
> > > The SQL query I would like to generate is this:
> >
> > > select AsText(the_geom), *
> > > from pt
> > > where SetSRID('BOX3D(-95.0 28.5, -95.8 28.8)'::box3d,4326) && the_geom and
> > > contains(SetSRID('BOX3D(-95.0 28.5, -95.8 28.8)'::box3d,4326),
> > > the_geom)
> > > limit 100;
> >
> > > So far the best I have been able to come up with is this:
> >
> > > pt.select(
> > >sa.and_(
> > > pt.c.pos.op('&&')(func.SetSRID("'BOX3D(-95.0 28.5, -95.8
> > > 28.8)'::box3d",4326)),
> > > func.contains(func.SetSRID("'BOX3D(-95 28.5, -95.8
> > > 28.8)'::box3d",4326), pt.c.pos)
> > > )
> > > )
> >
> > > Not the most readable way to represent it, but it seems to work. I
> > > have a couple questions though.
> >
> > > - I reuse "func.SetSRID("'BOX3D(-95 28.5, -95.8 28.8)'::box3d",4326)"
> > > twice. Is there a way to split this out into something I can just
> > > reuse?
> >
> > > - Is there any way to write an extension "operator" or something that
> > > could generate this for me? If I had my way, I would want the query
> > > to look like this:
> >
> > > pt.select( smart_contains( ((-95 28.5, -95.8 28.82), 4326), pt.c.pos))
> >
> > > - Can anyone point out a better way I could construct this query? Is
> > > there anything I am missing?
> >
> > I apologize for replying to my own posting, but I have some additional
> > information/problems.
> >
> > I tried to convert this over to an ORM query like this:
> >
> >bound_box= "'BOX3D(5 5, 25 25)'::box3d"
> >
> >spatial_q = session.query(Entity).filter_by(sa.and_(
> > Entity.c.pos.op('&&')(sa.func.SetSRID(bound_box, 4326)),
> > sa.func.Contains(sa.func.SetSRID(bound_box, 4326), Entity.c.pos)
> > ))
> >recs = spatial_q.all()
> >
> > I get an error because the SQL generated has a WHERE clause like this:
> >
> > WHERE (pt.the_geom && SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326))
> > AND Contains(SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326),
> > pt.the_geom) ORDER BY pt.gid
> >
> > I am not sure if you can see it clearly in e-mail, but the BOX3D text
> > is surrounded by apostrophes.
> >
> > What it should be is:
> >
> > SetSRID('BOX3D(5 5, 25 25)'::box3d, 4326)
> >
> > But instead it is sent to postgres as:
> >
> > SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326)
> >
> > Can anyone tell me why this is happening and what to do to fix it? I
> > already tried sa.literal(..) and that has the same issue. I tried
> > using sa.cast(..), but this doesn't work because it doesn't know about
> > the 'box3d' type.
> >
> > Thanks,
> > Allen
> >
>
--~--~-~--~~~---~--~~
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: Matching a DateTime-field
Hello!
Thanks for the pointers.
Here is the solution for MySQL :
session.query(List).filter(and_(func.DATE_FORMAT(List.expire,'%Y')
==2007 ,func.DATE_FORMAT(List.expire,"%m") == 12)).all()
On Dec 10, 6:08 pm, "Rick Morrison" <[EMAIL PROTECTED]> wrote:
> Yeah, it was a "for instance" answer, you'll need to use the correct MySql
> syntax of course.
>
> On 12/10/07, Adam B <[EMAIL PROTECTED]> wrote:
>
>
>
> > On Dec 10, 1:16 am, "Rick Morrison" <[EMAIL PROTECTED]> wrote:
> > > Any query using sql expressions is going to want to use correctly typed
> > data
> > > -- you're trying to query a date column with a string value. The LIKE
> > > operator is for string data.
>
> > > I'm not up on my mssql date expressions, but the answer is going to
> > resemble
> > > something like this:
>
> > > .filter(and_(func.datepart('year', List.expire) == 2007,
> > > func.datepart('month', List.expire) == the_month_number))
>
> > Ok, isnt this mssql specifik? I only find datepart in various
> > VB / .net documentation/solutions.
--~--~-~--~~~---~--~~
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: Question about using python expressions to create a GIS query
use bound_box = sa.text("'BOX3D(5 5, 25 25)'::box3d")
On Dec 10, 12:12 pm, "Allen Bierbaum" <[EMAIL PROTECTED]> wrote:
> On Dec 10, 2007 9:08 AM, Allen Bierbaum <[EMAIL PROTECTED]> wrote:
>
>
>
> > I am trying to figure out how to best use SA to create a GIS query.
> > In my application I am actually using ORM objects and mappers, but to
> > keep my question focused on clauses and python expressions, I am just
> > trying to test this out without the ORM first.
>
> > The SQL query I would like to generate is this:
>
> > select AsText(the_geom), *
> > from pt
> > where SetSRID('BOX3D(-95.0 28.5, -95.8 28.8)'::box3d,4326) && the_geom and
> > contains(SetSRID('BOX3D(-95.0 28.5, -95.8 28.8)'::box3d,4326),
> > the_geom)
> > limit 100;
>
> > So far the best I have been able to come up with is this:
>
> > pt.select(
> >sa.and_(
> > pt.c.pos.op('&&')(func.SetSRID("'BOX3D(-95.0 28.5, -95.8
> > 28.8)'::box3d",4326)),
> > func.contains(func.SetSRID("'BOX3D(-95 28.5, -95.8
> > 28.8)'::box3d",4326), pt.c.pos)
> > )
> > )
>
> > Not the most readable way to represent it, but it seems to work. I
> > have a couple questions though.
>
> > - I reuse "func.SetSRID("'BOX3D(-95 28.5, -95.8 28.8)'::box3d",4326)"
> > twice. Is there a way to split this out into something I can just
> > reuse?
>
> > - Is there any way to write an extension "operator" or something that
> > could generate this for me? If I had my way, I would want the query
> > to look like this:
>
> > pt.select( smart_contains( ((-95 28.5, -95.8 28.82), 4326), pt.c.pos))
>
> > - Can anyone point out a better way I could construct this query? Is
> > there anything I am missing?
>
> I apologize for replying to my own posting, but I have some additional
> information/problems.
>
> I tried to convert this over to an ORM query like this:
>
>bound_box= "'BOX3D(5 5, 25 25)'::box3d"
>
>spatial_q = session.query(Entity).filter_by(sa.and_(
> Entity.c.pos.op('&&')(sa.func.SetSRID(bound_box, 4326)),
> sa.func.Contains(sa.func.SetSRID(bound_box, 4326), Entity.c.pos)
> ))
>recs = spatial_q.all()
>
> I get an error because the SQL generated has a WHERE clause like this:
>
> WHERE (pt.the_geom && SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326))
> AND Contains(SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326),
> pt.the_geom) ORDER BY pt.gid
>
> I am not sure if you can see it clearly in e-mail, but the BOX3D text
> is surrounded by apostrophes.
>
> What it should be is:
>
> SetSRID('BOX3D(5 5, 25 25)'::box3d, 4326)
>
> But instead it is sent to postgres as:
>
> SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326)
>
> Can anyone tell me why this is happening and what to do to fix it? I
> already tried sa.literal(..) and that has the same issue. I tried
> using sa.cast(..), but this doesn't work because it doesn't know about
> the 'box3d' type.
>
> Thanks,
> Allen
--~--~-~--~~~---~--~~
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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
I did get an exception, that's how I knew to change the type!
On 12/10/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
>
>
> On Dec 10, 2007, at 12:47 PM, Rick Morrison wrote:
>
> > This works here on MSSQL/pymssql with a small change:
> >
> > -- j = Job("TEST1", datetime.datetime.now())
> >
> > ++ j = Job(1, datetime.datetime.now())
> >
> > MSSQL (and most other db engines) are going to enforce type on the
> > 'identifier' column. In the new code, it's an int, so...no strings
> > allowed. The original example user "uniqueidentifier", which is a
> > rather odd duck, and I'm not sure would support an arbitrary string
> > as a key. Unless you need real GUID keys for some reason, I would
> > suggest using a normal string or int surrogate key like the new
> > example does.
> >
> how come no exception is thrown ? silent failure is the party pooper.
>
>
>
>
>
> >
>
--~--~-~--~~~---~--~~
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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
On Dec 10, 2007, at 12:47 PM, Rick Morrison wrote:
> This works here on MSSQL/pymssql with a small change:
>
> -- j = Job("TEST1", datetime.datetime.now())
>
> ++ j = Job(1, datetime.datetime.now())
>
> MSSQL (and most other db engines) are going to enforce type on the
> 'identifier' column. In the new code, it's an int, so...no strings
> allowed. The original example user "uniqueidentifier", which is a
> rather odd duck, and I'm not sure would support an arbitrary string
> as a key. Unless you need real GUID keys for some reason, I would
> suggest using a normal string or int surrogate key like the new
> example does.
>
how come no exception is thrown ? silent failure is the party pooper.
--~--~-~--~~~---~--~~
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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
This works here on MSSQL/pymssql with a small change:
-- j = Job("TEST1", datetime.datetime.now())
++ j = Job(1, datetime.datetime.now())
MSSQL (and most other db engines) are going to enforce type on the
'identifier' column. In the new code, it's an int, so...no strings allowed.
The original example user "uniqueidentifier", which is a rather odd duck,
and I'm not sure would support an arbitrary string as a key. Unless you need
real GUID keys for some reason, I would suggest using a normal string or int
surrogate key like the new example does.
--~--~-~--~~~---~--~~
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: Question about using python expressions to create a GIS query
On Dec 10, 2007 9:08 AM, Allen Bierbaum <[EMAIL PROTECTED]> wrote:
> I am trying to figure out how to best use SA to create a GIS query.
> In my application I am actually using ORM objects and mappers, but to
> keep my question focused on clauses and python expressions, I am just
> trying to test this out without the ORM first.
>
> The SQL query I would like to generate is this:
>
> select AsText(the_geom), *
> from pt
> where SetSRID('BOX3D(-95.0 28.5, -95.8 28.8)'::box3d,4326) && the_geom and
> contains(SetSRID('BOX3D(-95.0 28.5, -95.8 28.8)'::box3d,4326), the_geom)
> limit 100;
>
> So far the best I have been able to come up with is this:
>
> pt.select(
>sa.and_(
> pt.c.pos.op('&&')(func.SetSRID("'BOX3D(-95.0 28.5, -95.8
> 28.8)'::box3d",4326)),
> func.contains(func.SetSRID("'BOX3D(-95 28.5, -95.8
> 28.8)'::box3d",4326), pt.c.pos)
> )
> )
>
> Not the most readable way to represent it, but it seems to work. I
> have a couple questions though.
>
> - I reuse "func.SetSRID("'BOX3D(-95 28.5, -95.8 28.8)'::box3d",4326)"
> twice. Is there a way to split this out into something I can just
> reuse?
>
> - Is there any way to write an extension "operator" or something that
> could generate this for me? If I had my way, I would want the query
> to look like this:
>
> pt.select( smart_contains( ((-95 28.5, -95.8 28.82), 4326), pt.c.pos))
>
> - Can anyone point out a better way I could construct this query? Is
> there anything I am missing?
I apologize for replying to my own posting, but I have some additional
information/problems.
I tried to convert this over to an ORM query like this:
bound_box= "'BOX3D(5 5, 25 25)'::box3d"
spatial_q = session.query(Entity).filter_by(sa.and_(
Entity.c.pos.op('&&')(sa.func.SetSRID(bound_box, 4326)),
sa.func.Contains(sa.func.SetSRID(bound_box, 4326), Entity.c.pos)
))
recs = spatial_q.all()
I get an error because the SQL generated has a WHERE clause like this:
WHERE (pt.the_geom && SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326))
AND Contains(SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326),
pt.the_geom) ORDER BY pt.gid
I am not sure if you can see it clearly in e-mail, but the BOX3D text
is surrounded by apostrophes.
What it should be is:
SetSRID('BOX3D(5 5, 25 25)'::box3d, 4326)
But instead it is sent to postgres as:
SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326)
Can anyone tell me why this is happening and what to do to fix it? I
already tried sa.literal(..) and that has the same issue. I tried
using sa.cast(..), but this doesn't work because it doesn't know about
the 'box3d' type.
Thanks,
Allen
--~--~-~--~~~---~--~~
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: Matching a DateTime-field
Yeah, it was a "for instance" answer, you'll need to use the correct MySql
syntax of course.
On 12/10/07, Adam B <[EMAIL PROTECTED]> wrote:
>
>
> On Dec 10, 1:16 am, "Rick Morrison" <[EMAIL PROTECTED]> wrote:
> > Any query using sql expressions is going to want to use correctly typed
> data
> > -- you're trying to query a date column with a string value. The LIKE
> > operator is for string data.
> >
> > I'm not up on my mssql date expressions, but the answer is going to
> resemble
> > something like this:
> >
> > .filter(and_(func.datepart('year', List.expire) == 2007,
> > func.datepart('month', List.expire) == the_month_number))
> >
>
> Ok, isnt this mssql specifik? I only find datepart in various
> VB / .net documentation/solutions.
>
>
>
> >
>
--~--~-~--~~~---~--~~
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: Matching a DateTime-field
On Dec 10, 1:16 am, "Rick Morrison" <[EMAIL PROTECTED]> wrote:
> Any query using sql expressions is going to want to use correctly typed data
> -- you're trying to query a date column with a string value. The LIKE
> operator is for string data.
>
> I'm not up on my mssql date expressions, but the answer is going to resemble
> something like this:
>
> .filter(and_(func.datepart('year', List.expire) == 2007,
> func.datepart('month', List.expire) == the_month_number))
>
Ok, isnt this mssql specifik? I only find datepart in various
VB / .net documentation/solutions.
--~--~-~--~~~---~--~~
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: Matching a DateTime-field
On Dec 10, 1:16 am, "Rick Morrison" <[EMAIL PROTECTED]> wrote:
> Any query using sql expressions is going to want to use correctly typed data
> -- you're trying to query a date column with a string value. The LIKE
> operator is for string data.
>
> I'm not up on my mssql date expressions, but the answer is going to resemble
> something like this:
>
> .filter(and_(func.datepart('year', List.expire) == 2007,
> func.datepart('month', List.expire) == the_month_number))
>
>
Ah yes, i had no idea how to match the dates the way you presented.
Many Thanks !
br
Adam
--~--~-~--~~~---~--~~
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: Slow relation based assignment.
hey mike, Just to confirm - trunk fixes problem with deletion. Additionally, I have removed the lazy loading condition and it maintains the speed of the query. Thanks again to the team, Martin On Dec 7, 4:14 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > hey martin - > > this bug is fixed in trunk r3868, so if you use the svn trunk you can > either keep using the dynamic or go back to the regular relation, you > should be good in both cases. > > - mike --~--~-~--~~~---~--~~ 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] Question about using python expressions to create a GIS query
I am trying to figure out how to best use SA to create a GIS query.
In my application I am actually using ORM objects and mappers, but to
keep my question focused on clauses and python expressions, I am just
trying to test this out without the ORM first.
The SQL query I would like to generate is this:
select AsText(the_geom), *
from pt
where SetSRID('BOX3D(-95.0 28.5, -95.8 28.8)'::box3d,4326) && the_geom and
contains(SetSRID('BOX3D(-95.0 28.5, -95.8 28.8)'::box3d,4326), the_geom)
limit 100;
So far the best I have been able to come up with is this:
pt.select(
sa.and_(
pt.c.pos.op('&&')(func.SetSRID("'BOX3D(-95.0 28.5, -95.8
28.8)'::box3d",4326)),
func.contains(func.SetSRID("'BOX3D(-95 28.5, -95.8
28.8)'::box3d",4326), pt.c.pos)
)
)
Not the most readable way to represent it, but it seems to work. I
have a couple questions though.
- I reuse "func.SetSRID("'BOX3D(-95 28.5, -95.8 28.8)'::box3d",4326)"
twice. Is there a way to split this out into something I can just
reuse?
- Is there any way to write an extension "operator" or something that
could generate this for me? If I had my way, I would want the query
to look like this:
pt.select( smart_contains( ((-95 28.5, -95.8 28.82), 4326), pt.c.pos))
- Can anyone point out a better way I could construct this query? Is
there anything I am missing?
Thanks,
Allen
--~--~-~--~~~---~--~~
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: Possible to build a query object from a relation property?
Thanks. This looks like it should work. I will give it a try. -Allen On Dec 9, 2007 10:39 PM, Michael Bayer <[EMAIL PROTECTED]> wrote: > > > On Dec 9, 2007, at 10:55 PM, Allen Bierbaum wrote: > > > > > I am using SA 0.3.11 and I would like to know if there is a way to get > > a query object from a relation property. I have several one-to-many > > relationships in my application. These are all setup and work very > > well, but I find that I often want to perform further filtering of the > > objects in the relationship list property. I could write python code > > to do it, but if I could get SA to do it on the server, then all the > > better. > > it is the "dynamic" relation that you want, but for 0.3 you can write > your own read-only property via: > > class MyClass(object): > def _get_prop(self): > return > object_session(self).query(ChildClass).with_parent(self, > 'attributename') > attributename = property(_get_prop) > > > > > > > --~--~-~--~~~---~--~~ 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: SessionExtension and Transactions: how to "coordinate" all SessionExtension funcs
Alle lunedì 10 dicembre 2007, Marco Mariani ha scritto:
> Stefano Bartaletti wrote:
> > I need to gather IDs in after_commit because theID is a serial Postgres
> > value that is available only after flush()
>
> Not really... in postgres, you can ask to consume the next sequence
> value with SELECT NEXTVAL('sequence_name') and explicitly set that as
> primary key value.
Thanks I already thougt of this solution, but if I need to do the same for
different attributes that are not valued until flush I'm in trouble again
--
Cordialmente
Stefano Bartaletti
Responsabile Software
G.Tosi Spa Tintoria
Skype account: stefano.bartaletti
ICQ contact : 1271960
Viale dell'Industria 61
21052 Busto Arsizio (VA)
Tel. +39 0331 34 48 11
Fax +39 0331 35 21 23
--~--~-~--~~~---~--~~
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: SessionExtension and Transactions: how to "coordinate" all SessionExtension funcs
Stefano Bartaletti wrote:
> I need to gather IDs in after_commit because theID is a serial Postgres value
> that is available only after flush()
>
Not really... in postgres, you can ask to consume the next sequence
value with SELECT NEXTVAL('sequence_name') and explicitly set that as
primary key value.
--
This e-mail (and any attachment(s)) is strictly confidential and for use only
by intended recipient(s). Any use, distribution, reproduction or disclosure by
any other person is strictly prohibited. The content of this e-mail does not
constitute a commitment by the Company except where provided for in a written
agreement between this e-mail addressee and the Company.
If you are not an intended recipient(s), please notify the sender promptly and
destroy this message and its attachments without reading or saving it in any
manner.
Any non authorized use of the content of this message constitutes a violation
of the obligation to abstain from learning of the correspondence among other
subjects, except for more serious offence, and exposes the person responsible
to the relevant consequences.
--~--~-~--~~~---~--~~
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: SessionExtension and Transactions: how to "coordinate" all SessionExtension funcs
> The only correct "link" I found it in an obscure (to me) > _SessionTransaction__parent, so I suspect this is not the "correct" way to > get to my goal. Is there a cleaner way to do such a thing? Just one thing: I know the syntax _class__attribute to access "hidden" python attirbutes, obscure is the way I'm trying to use this, is it good or not? :-) -- Cordialmente Stefano Bartaletti Responsabile Software G.Tosi Spa Tintoria Skype account: stefano.bartaletti ICQ contact : 1271960 Viale dell'Industria 61 21052 Busto Arsizio (VA) Tel. +39 0331 34 48 11 Fax +39 0331 35 21 23 --~--~-~--~~~---~--~~ 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] SessionExtension and Transactions: how to "coordinate" all SessionExtension funcs
Foreword: sqlalchemy is really amazing! Hello, I'm trying to build a database where users become aware of what has been changed by other users: there is a SessionExtension that collects info about changes and then dispatch some messages with a Pyro Event Server. I'm trying to understand what can be my link point between flush, commit and rollback operations to store the single IDs into a per-transaction dictionary and then, when there is a commit on a non-nested transaction, gather all and dispatch the message through Pyro I thought I could use id(session.transaction) as a dictionary key, but - it changes between before_flush and after_flush - remains the same between before_flush, after_commit and rollback I need to gather IDs in after_commit because theID is a serial Postgres value that is available only after flush() The only correct "link" I found it in an obscure (to me) _SessionTransaction__parent, so I suspect this is not the "correct" way to get to my goal. Is there a cleaner way to do such a thing? Thanks in advance, here is some code: in mySessionExtension I put: def after_rollback(self, session): print "after_rollback", id(session.transaction) def after_commit(self, session): print "after_commit", id(session.transaction) def before_flush(self, session, flush_context, objects): print "before_flush", id(session.transaction) def after_flush(self, session, flush_context): print "after_flush", id(session.transaction), id(session.transaction._SessionTransaction__parent) In the main I made: ss.begin() rep = dict((x.codice, x) for x in mappers.Reparto.query.all()) r = rep['3'] r.descrizione += '!' ss.flush() if 1: ss.begin_nested() r = rep['2'] r.descrizione += '!' ss.flush() ss.rollback() ss.commit() The resulting dump was before_flush 147722732 after_flush 149425612 147722732 before_flush 149423788 after_flush 149489964 149423788 after_rollback 149423788 after_commit 147722732 I'm working with Python 2.5, SQA 0.4.1 with scoped_session (transactional=False, autoflush=False) Thanks -- Cordialmente Stefano Bartaletti Responsabile Software G.Tosi Spa Tintoria Skype account: stefano.bartaletti ICQ contact : 1271960 Viale dell'Industria 61 21052 Busto Arsizio (VA) Tel. +39 0331 34 48 11 Fax +39 0331 35 21 23 --~--~-~--~~~---~--~~ 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: Design: mapped objects everywhere?
On Monday 10 December 2007 12:12:19 Paul-Michael Agapow wrote: > Yowser. Thanks to both of you - that's exactly what I mean. Any > pointers on where I can find an example of a class that is > "unaware" if it is in the db? Or is there a good example of the > second solution, of "a single class that does the what and why, > and an interchangeable layer/context that does load/saving"? I'm > digging through dbcook.sf.net but haven't found anything just yet. well... good example - no. there is a bad example: dbcook/dbcook/usage/example/example1.py The classes are plain classes (.Base can be anything/object), with some DB-related declarations/metainfo in them. they do not have to know that they are DB-related. if u dont give them to dbcook.builder.Builder, they will not become such. If u give them, they will become SA-instrumented etc, but u still do not have to change anything - as long as your methods do not rely (too much) on being (or not being) DB. see dbcook.expression as attempt to wrap some queries in independent manner. more, if u redefine the Reflector u can have different syntax for db-metainfo - or get it from different place, not at all inside the class. So u can plug that in and out whenever u decide to (no example on this, its theoretical ;-). Still, the final class (or object) will be always aware about being in the db or not; it is _you_ who should know when u do not care (95%) and when u do (5%). All this is "proper design and then self-discipline" issue: u have to keep the things separate (and i tell u, it is NOT easy) if u start putting it any db-stuff in the classes, no framework will help u. complete opaque separation is probably possible, but will probably mean having 2 paralel class hierarchies instead of one. > On 2007 Dec 7, at 22:07, [EMAIL PROTECTED] wrote: > > Paul Johnston wrote: > >>> "A Sample may be created by the web application or fetched from > >>> the database. Later on, it may be disposed of, edited or > >>> checked back into > >>> the db." > >> > >> Sounds like you want your app to be mostly unaware of whether a > >> class is > >> saved in the db or not (i.e. persistent)? If so, I'd use a > >> single class, > >> design the properties so they work in non-persistent mode, and > >> then they'll work in persistent mode as well. > > > > or like a single class that does the what and why, and an > > interchangeable > > layer/context that does load/saving (and the relations!). > > in such situations declarative programming helps a lot, so u dont > > bind your > > self to (the) db (or whatever persistency). Check dbcook.sf.net. > > My own > > latest experience is about turning a project that was thought for > > db/using > > dbcook into non-db simple-file-based persistency. The change was > > relatively > > small, like 5-10 lines per class - as long as there are > > Collections etc > > similar notions so Obj side of ORM looks same. > > -- > Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health > [EMAIL PROTECTED] / [EMAIL PROTECTED] > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Design: mapped objects everywhere?
Yowser. Thanks to both of you - that's exactly what I mean. Any pointers on where I can find an example of a class that is "unaware" if it is in the db? Or is there a good example of the second solution, of "a single class that does the what and why, and an interchangeable layer/context that does load/saving"? I'm digging through dbcook.sf.net but haven't found anything just yet. On 2007 Dec 7, at 22:07, [EMAIL PROTECTED] wrote: > Paul Johnston wrote: >>> "A Sample may be created by the web application or fetched from the >>> database. Later on, it may be disposed of, edited or checked back >>> into >>> the db." >> >> Sounds like you want your app to be mostly unaware of whether a >> class is >> saved in the db or not (i.e. persistent)? If so, I'd use a single >> class, >> design the properties so they work in non-persistent mode, and then >> they'll work in persistent mode as well. > > or like a single class that does the what and why, and an > interchangeable > layer/context that does load/saving (and the relations!). > in such situations declarative programming helps a lot, so u dont > bind your > self to (the) db (or whatever persistency). Check dbcook.sf.net. My > own > latest experience is about turning a project that was thought for > db/using > dbcook into non-db simple-file-based persistency. The change was > relatively > small, like 5-10 lines per class - as long as there are > Collections etc > similar notions so Obj side of ORM looks same. -- Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health [EMAIL PROTECTED] / [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
On 10 Dic, 03:11, Michael Bayer <[EMAIL PROTECTED]> wrote:
> I cant reproduce your problem, although i dont have access to MSSQL
> here and there may be some issue on that end. Attached is your script
> using an in-memory sqlite database, with the update inside of a while
> loop, and it updates regularly.A few things to try on the MSSQL
> side, if the issue is due to some typing issue, try not using
> autoload=True, try using generic types instead of the MSSQL specific
> ones, etc., in an effort to narrow down what might be the problem.
I've redefined the table using only generic types:
jobs = sa.Table('jobs', metadata,
sa.Column('identifier', sa.VARCHAR(20),
primary_key=True),
sa.Column('section', sa.VARCHAR(20)),
sa.Column("start",sa.DATETIME, primary_key=True),
sa.Column("stop",sa.DATETIME),
sa.Column("station", sa.VARCHAR(20)),
autoload=False)
and also autoload=False made no difference. I'll trying changing
something else...
>
> also ive added "MSSQL/pyodbc" to the subject line here in case any of
> the MSSQL crew wants to try out your script with pyodbc.
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
-~--~~~~--~~--~--~---
