Re: [sqlalchemy] money type for Postgresql
I use Float for money at the moment. I am moving from Mysql to Postgres and have not had any issues but i'm not sure if Float actually works correctly. Floats are being used for both broken number values and for money values. should I change to numeric for Postgres as I do not see that Column Type mentioned in this tread. Martijn On Dec 27, 2011, at 20:39 , dgardner wrote: Quick hack, figured I would share since there seemed to be other people asking about it. I couldn't get it to work with autoload=True for table reflection. --- from sqlalchemy import types from decimal import Decimal class Money(types.UserDefinedType): def get_col_spec(self): return 'money' def result_processor(self, dialect, coltype): def process(value): # Strip off the currency symbol return Decimal(value[1:]) return process -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] money type for Postgresql
Using FLOAT for monetary amounts is an extremely bad idea because of the inexactness of storage and arithmetic .. Using MONEY is discouraged because it is too locale-sensitive NUMERIC should be used instead On 12/28/2011 11:48, Martijn Moeling wrote: I use Float for money at the moment. I am moving from Mysql to Postgres and have not had any issues but i'm not sure if Float actually works correctly. Floats are being used for both broken number values and for money values. should I change to numeric for Postgres as I do not see that Column Type mentioned in this tread. Martijn On Dec 27, 2011, at 20:39 , dgardner wrote: Quick hack, figured I would share since there seemed to be other people asking about it. I couldn't get it to work with autoload=True for table reflection. --- from sqlalchemy import types from decimal import Decimal class Money(types.UserDefinedType): def get_col_spec(self): return 'money' def result_processor(self, dialect, coltype): def process(value): # Strip off the currency symbol return Decimal(value[1:]) return process -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: 0.7 event migration
On 12/27/2011 5:34 PM, Michael Bayer wrote: On Dec 27, 2011, at 5:21 PM, Kent wrote: So see what happens if you, for the moment, just monkeypatch over orm.session._state_session to do a lookup in a global context if state.session_id isn't set. If that solves the problem of I want detached objects to load stuff, for you and everyone else who wants this feature, then whatever - I'm not at all thrilled about this use case but if it's just one trivial hook that I don't need to encourage, then there you go. Please give me a lesson in monkeypatching 101. This isn't being invoked: = import sqlalchemy.orm.session as session_module sqla_state_session = session_module._state_session def _state_session(state): for transient/detached objects, so we can automatically query these related objects return sqla_state_session(state) or DBSession() setattr(session_module, '_state_session', _state_session) = I presume there are already references to _state_session before I change it. Hm I would have guessed, but mapper.py and strategies.py seem to be calling it relative to the module. What does pdb tell you if you post mortem into where it raises the Detached error ? Also, will this cause other side effects, such as obj in DBSession reporting True when it used to report False or the orm internals being confused by the return of this value? maybe ? if it doesn't just work then yes. The new logic here only needs to take place at the point at which it loads an attribute so if we made it local to those areas, there shouldn't be any big issues. I'm basically making you a developer here to help me test this out. ok, it was never making it that far because my main use case for this is to work with transient instances (detached are secondary). I was testing with a transient object and it never got past the first if statement in _load_for_state(), which was using session_id instead of the return value from _state_session(). So we'd have to move the state_session API invocation up (patch attached). Then this works as a proof of concept for both detached and transient objects. I'm not sure if it was important for the return attributes.ATTR_EMPTY to have precedence over return attributes.PASSIVE_NO_RESULT so I kept them in that order to not disturb anything (otherwise we could delay the session lookup until after the return attributes.PASSIVE_NO_RESULT test). Those cases shouldn't be common anyway, right? We'd also need to make this very local to the loading of attributes, as you mentioned, because object_session() also invokes _state_session()... we can't have that.. it messes up too much. (For example, transient objects appear pending and detached objects appear persistent, depending on your method of inspection.) Off topic, but from a shell prompt I sometimes find myself naturally attempting this: session.detach(instance) and then when that fails, I remember: session.expunge(instance) I'm not asking for a change here, but quite curious: you think 'detach' is a better/more natural term? = diff -U10 -r sqlalchemy-default/lib/sqlalchemy/orm/strategies.py sqlalchemy-default.kb/lib/sqlalchemy/orm/strategies.py --- sqlalchemy-default/lib/sqlalchemy/orm/strategies.py 2011-12-15 11:42:50.0 -0500 +++ sqlalchemy-default.kb/lib/sqlalchemy/orm/strategies.py 2011-12-27 17:48:54.0 -0500 @@ -450,41 +450,42 @@ self._rev_bind_to_col, \ self._rev_equated_columns criterion = sql_util.adapt_criterion_to_null(criterion, bind_to_col) if adapt_source: criterion = adapt_source(criterion) return criterion def _load_for_state(self, state, passive): -if not state.key and \ -(not self.parent_property.load_on_pending or not state.session_id): +prop = self.parent_property +pending = not state.key +session = sessionlib._state_session(state) + +if pending and \ +(not prop.load_on_pending or not session): return attributes.ATTR_EMPTY instance_mapper = state.manager.mapper -prop = self.parent_property key = self.key prop_mapper = self.mapper -pending = not state.key if ( (passive is attributes.PASSIVE_NO_FETCH or \ passive is attributes.PASSIVE_NO_FETCH_RELATED) and not self.use_get ) or ( passive is attributes.PASSIVE_ONLY_PERSISTENT and pending ): return attributes.PASSIVE_NO_RESULT -session = sessionlib._state_session(state) if not session: raise orm_exc.DetachedInstanceError( Parent instance %s is not bound to a Session; lazy
[sqlalchemy] InvalidRequestError: Can't reconnect until invalid transaction is rolled back error during SELECT query
I've made web application using Pyramid 1.2.5 + Python 2.7.1 + SQLAlchemy
0.7.4 and occasionally encountered the following error.
Traceback (most recent call last):
File /usr/local/lib/python2.7/site-packages/pyramid/router.py, line
176, in __call__
response = self.handle_request(request)
File
/usr/local/lib/python2.7/site-packages/pyramid_debugtoolbar-0.9.7-py2.7.egg/pyramid_debugtoolbar/toolbar.py,
line 103, in toolbar_tweenreturn handler(request)
File /usr/local/lib/python2.7/site-packages/pyramid/tweens.py, line 17,
in excview_tween
response = handler(request)
File
/usr/local/lib/python2.7/site-packages/pyramid_tm-0.3-py2.7.egg/pyramid_tm/__init__.py,
line 61, in tm_tween
response = handler(request) File
/usr/local/lib/python2.7/site-packages/pyramid/router.py, line 153, in
handle_request
response = view_callable(context, request)
File /usr/local/lib/python2.7/site-packages/pyramid/config/views.py,
line 187, in _secured_view
return view(context, request)
File /usr/local/lib/python2.7/site-packages/pyramid/config/views.py,
line 320, in viewresult_to_response
result = view(context, request)
File /usr/local/lib/python2.7/site-packages/pyramid/config/views.py,
line 403, in _requestonly_view
response = view(request)
File /home/ec2-user/work/DP-MGMT/dp_mgmt/views/users.py, line 57, in
users
for user, snsuser in query.all():
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1947,
in all
return list(self)
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 2057,
in __iter__
return self._execute_and_instances(context)
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 2072,
in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 1405,
in execute
params)
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 1538,
in _execute_clauseelement
compiled_sql, distilled_params
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 1605,
in _execute_context
None, None)
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 1599,
in _execute_context
conn = self._revalidate_connection()
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 1018,
in _revalidate_connection
Can't reconnect until invalid
StatementError: Can't reconnect until invalid transaction is rolled back
(original cause: InvalidRequestError: Can't reconnect until invalid
transaction is rolled back) 'SELECT dp_user.user_id AS dp_user_user_id,
dp_user.email AS dp_user_email, dp_user.new_email AS dp_user_new_email,
dp_sns_user.`SNS_name` AS `dp_sns_user_SNS_name` \\nFROM dp_user,
dp_sns_user \\nWHERE dp_user.status = %s AND dp_user.user_id =
dp_sns_user.user_id AND dp_user.signup_date = %s AND dp_user.signup_date
%s ORDER BY dp_user.signup_date DESC' [immutabledict({})]
All queries what my web app is doing is SELECT. So I don't think I need to
explicitly call session.commit() at all. I can't understand why invalid
transaction ever occurred.
mysqld's wait_timeout is 28800 and I create sqlalchemy engine with
pool_recycle of 3600.
after I restarted apache, which run my web app through wsgi, it starts
working again.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/EmuiK-3NR1gJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] InvalidRequestError: get() can only be used against a single mapped class.
Was it your intention to no longer allow this type of query().get()? session.query(cls.orderid).get(orderid) I get InvalidRequestError: get() can only be used against a single mapped class. but the wording is such that I'm not sure you intended to limit that use case (there is only a single mapped class in that query). I'll change such queries, just wanted to bring it up to see if you intended it that way. (Admittedly, if I recall correctly, when I first added it, I think I was slightly surprised it worked as I expected...) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: 0.7 event migration
On Dec 28, 2011, at 9:18 AM, Kent wrote: Off topic, but from a shell prompt I sometimes find myself naturally attempting this: session.detach(instance) and then when that fails, I remember: session.expunge(instance) I'm not asking for a change here, but quite curious: you think 'detach' is a better/more natural term? I'll agree I hate the term expunge(). evict() is often what I think of. detach(), also nice.consider that it's the opposite of add(). unfortunately remove() is already taken. i guess the patch is interacting with that load_on_pending stuff, which I probably added for you also. It would be nice to really work up a new SQLAlchemy feature: detached/transient object loading document that really describes what it is we're trying to do here.If you were to write such a document, what example would you give as the rationale ?I know that's the hard part here, but this is often very valuable, to look at your internal system and genericize it into something universally desirable.It would make it clearer what we'd do with the flush() issue from yesterday too. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] InvalidRequestError: get() can only be used against a single mapped class.
On Dec 28, 2011, at 11:34 AM, Kent wrote: Was it your intention to no longer allow this type of query().get()? session.query(cls.orderid).get(orderid) it was ! yes. I get InvalidRequestError: get() can only be used against a single mapped class. but the wording is such that I'm not sure you intended to limit that use case (there is only a single mapped class in that query). From the POV of that message there's just a Column, and no mapped class, actually being passed. I knew this wouldn't necessarily be very clear but there was no better message I could think of. I'll change such queries, just wanted to bring it up to see if you intended it that way. (Admittedly, if I recall correctly, when I first added it, I think I was slightly surprised it worked as I expected...) amazingly it took me just one second to find the originating issue, which was that the identity map was still being searched, thereby causing the wrong result: http://www.sqlalchemy.org/trac/ticket/2144 When something is used in a way I didn't anticipate, my first instinct is often to first make that unanticipated case raise an error. That means it's covered and people won't do it. Later, if we decide it should do something, that can be re-introduced. It's always easier to unlock a dead end with a well thought out approach, than to make multiple, small modifications to an existing behavior. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Order by the sequence in_ ?
On Dec 27, 2011, at 8:37 PM, Vlad K. wrote: Hi all. I need to select some rows where pkey is in a sequence. How do I order by that very sequence? images_all = session.query(AdImage).filter(AdImage.image_id.in_(images)).order_by( ? ).all() Postgresql backend. typically with case(): order_by( case([ (Adimage.id == 3, A), (Adimage.id == 1, B), (Adimage.id == 9, C), ]) ) unless you can use a simpler transformation on AdImage.id that converts it into a sortable value. The above can be generalized: case([(AdImage.id == value, literal(index)) for index, value in enumerate(images)]) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] InvalidRequestError: Can't reconnect until invalid transaction is rolled back error during SELECT query
On Dec 27, 2011, at 11:58 PM, Josh Ha-Nyung Chung wrote:
I've made web application using Pyramid 1.2.5 + Python 2.7.1 + SQLAlchemy
0.7.4 and occasionally encountered the following error.
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 1599,
in _execute_context
conn = self._revalidate_connection()
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 1018,
in _revalidate_connection
Can't reconnect until invalid
StatementError: Can't reconnect until invalid transaction is rolled back
(original cause: InvalidRequestError: Can't reconnect until invalid
transaction is rolled back) 'SELECT dp_user.user_id AS dp_user_user_id,
dp_user.email AS dp_user_email, dp_user.new_email AS dp_user_new_email,
dp_sns_user.`SNS_name` AS `dp_sns_user_SNS_name` \\nFROM dp_user, dp_sns_user
\\nWHERE dp_user.status = %s AND dp_user.user_id = dp_sns_user.user_id AND
dp_user.signup_date = %s AND dp_user.signup_date %s ORDER BY
dp_user.signup_date DESC' [immutabledict({})]
All queries what my web app is doing is SELECT. So I don't think I need to
explicitly call session.commit() at all. I can't understand why invalid
transaction ever occurred.
mysqld's wait_timeout is 28800 and I create sqlalchemy engine with
pool_recycle of 3600.
after I restarted apache, which run my web app through wsgi, it starts
working again.
this can only happen if an error is emitted from a query or other SQL operation
inside of a transaction, and the connection continues to be reused subsequent
to that error condition without any attendance being given to the invalid
transaction.
The restarting of apache step as the only solution suggests you're sharing a
single transaction between multiple requests, and the error was emitted in a
previous request.You'd need to ensure that the Session is completely closed
out at the end of each request, or at least rollback() is called, so that any
remaining transactional state is released.Pyramid suggests integrating with
the ZopeTransactionExtension which I believe should handle this (you should
check on the Pyramid list).
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: 0.7 event migration
On 2011-12-28 10:58 AM, Michael Bayer wrote: detach(), also nice. This seems most descriptive of what is actually taking place. I poured over the docs for some time looking for the detach() method. Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: InvalidRequestError: get() can only be used against a single mapped class.
On Dec 28, 12:07 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 28, 2011, at 11:34 AM, Kent wrote: Was it your intention to no longer allow this type of query().get()? session.query(cls.orderid).get(orderid) it was ! yes. I get InvalidRequestError: get() can only be used against a single mapped class. but the wording is such that I'm not sure you intended to limit that use case (there is only a single mapped class in that query). From the POV of that message there's just a Column, and no mapped class, actually being passed. I knew this wouldn't necessarily be very clear but there was no better message I could think of. I'll change such queries, just wanted to bring it up to see if you intended it that way. (Admittedly, if I recall correctly, when I first added it, I think I was slightly surprised it worked as I expected...) amazingly it took me just one second to find the originating issue, which was that the identity map was still being searched, thereby causing the wrong result: http://www.sqlalchemy.org/trac/ticket/2144 I suspected that subsequent .get() invocations would return just the column instead of the object, is that what was happening? When something is used in a way I didn't anticipate, my first instinct is often to first make that unanticipated case raise an error. That means it's covered and people won't do it. Later, if we decide it should do something, that can be re-introduced. It's always easier to unlock a dead end with a well thought out approach, than to make multiple, small modifications to an existing behavior. That is the right approach, I'm convinced. I always feel I'm fighting an eternal battle at work against the other guys' mindset of make the program not crash if something unexpected happens, so at least there is a chance it will keep running... Can't stand it. Things like try: catch Exception: pass. ! It's far better to blow up before doing damage, even if that damage is only speculative because it wasn't used in the designed use case. Later, when that case comes up you can analyze it and open the dead end, agreed. Thanks, I've certainly got no issues with the semantics of get() meaning get the instance. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: InvalidRequestError: get() can only be used against a single mapped class.
in fact, I modified our Query class after .first() was being abused out of laziness: def first(self): raise ProgrammingError(Never use .first(); please use .get() or .one()\n .one() makes sure there is only one return and .get() returns None if doesn't exist.\n .get() is probably better since it avoids database roundtrip when the object is already loaded.) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
