Re: [sqlalchemy] Disabling implicit lazy loading
Thanks, you are as always very helpful. I hope I'll be able to compose a PR soon. On Thursday, July 23, 2015 at 5:39:52 PM UTC+3, Michael Bayer wrote: On 7/23/15 10:09 AM, Mike Bayer wrote: On 7/23/15 1:24 AM, Yegor Roganov wrote: Hi all! Is there a way to disable implicit loading of relationships? For example, I want an exception to be thrown if I try to access 'address.user' unless user was explicitly loaded via options address = query(Address).options(joinedload(Address.user)).filter_by(id=id).first(); address.user # OK address = query(Address).get(id); address.user # should throw At first I thought that `noload` option is what I need, but it seems it disables event explicit loading. noload is how you'd disable implicit loading. As far as throwing on a lazyload, the easiest way is just to detach the objects from their parent Session so they no longer have any connectivity using session.expunge(object), but then you're no longer in the session. Otherwise, it seems the problem you are actually trying to solve is raising on unexpected SQL. lazy loading of relationships is not the only thing that goes on, there are loads of unloaded columns, columns that had server defaults emitted on the last flush, loads of joined-inheritance rows, all kinds. this is why the best approach is to just do real profiling of your applications using SQL logging, or perhaps using SQL events like before_execute() / before_cursor_execute() so that you can build yourself a with assert_no_sql(session): -style context manager for critical blocks that should have no SQL emitted. Guessing that's not what you want. Feel free to write your own NoLoader that just raises, example: whoops. Let's try that again, this one actually works: from sqlalchemy.orm import properties from sqlalchemy.orm import strategies from sqlalchemy.orm import state @properties.RelationshipProperty.strategy_for(lazy=raise) class RaiseLoader(strategies.NoLoader): note: this is *very SQLAlchemy 1.0 specific*!! it will need to be reviewed for 1.1 def create_row_processor( self, context, path, loadopt, mapper, result, adapter, populators): def invoke_no_load(state, passive): raise Exception(boom) set_lazy_callable = state.InstanceState.\ _instance_level_callable_processor( mapper.class_manager, invoke_no_load, self.key ) populators[new].append((self.key, set_lazy_callable)) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] set vs after_flush events
will this answer my second question?
obj._sa_instance_state.committed_state
{'batch_status': STARTED(db=1),
'updated_by': 24769797950537744L,
'updated_on': Arrow [2015-07-24T14:02:03.360479-03:00]}
cheers,
richard.
On 07/24/2015 11:13 AM, Richard Gerd Kuesters wrote:
hi!
first, /yes/, set and after_flush are quite different events :) but
here's what i'm trying to accomplish:
one object have an attribute, like 'state', and i would like to
monitor and trigger some other methods if (given scenarios):
1. the program sets a new value to a state that is equal to it's
actual state (and/or value), i can use 'set' but, does it reflect only
the value set or when it gets commited?
1.1. and, if set to the same value, returning the old value (set with
retval=True) will make this object dirty (supposed to be the only
value modified) ?
2. after_flush, if one of these entity get's modified, can i track
down what was modified, including my state column if possible?
cheers,
richard.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
attachment: richard.vcf
Re: [sqlalchemy] set vs after_flush events
On 7/24/15 10:13 AM, Richard Gerd Kuesters wrote: hi! first, /yes/, set and after_flush are quite different events :) but here's what i'm trying to accomplish: one object have an attribute, like 'state', and i would like to monitor and trigger some other methods if (given scenarios): 1. the program sets a new value to a state that is equal to it's actual state (and/or value), i can use 'set' but, does it reflect only the value set or when it gets commited? you set the value to the attribute, the set event receives it, if the attribute already *had* that value loaded then the history is a no-net change, no UPDATE is emitted. If the attribute did not already have a value loaded, and the attribute does not specify active_history, then we don't know the old value and the history will show that the attribute has changed, from a previous value of NO_VALUE. I don't know what does it reflect only the value set or when it gets committed means, hopefully the above answers your question. 1.1. and, if set to the same value, returning the old value (set with retval=True) will make this object dirty (supposed to be the only value modified) ? the object goes into session.dirty as soon as any attributes are set. however, in the flush, we go through the effort to look for actual net changes. If there's no net change on the attribute, no UPDATE will be emitted in the flush, the object moves back to clean at that point. 2. after_flush, if one of these entity get's modified, can i track down what was modified, including my state column if possible? yes you can look in session.dirty to find all the objects that are dirty, then looking in committed_state will illustrate those attribute that had something happen to them; though the latter part here is not a public system - it will work for now but I can't guarantee that in a future major version bump that system wouldn't change. We can add public API to return .modified, e.g. a list of keys that received a modified event. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] set vs after_flush events
On 07/24/2015 12:59 PM, Mike Bayer wrote: On 7/24/15 10:13 AM, Richard Gerd Kuesters wrote: hi! first, /yes/, set and after_flush are quite different events :) but here's what i'm trying to accomplish: one object have an attribute, like 'state', and i would like to monitor and trigger some other methods if (given scenarios): 1. the program sets a new value to a state that is equal to it's actual state (and/or value), i can use 'set' but, does it reflect only the value set or when it gets commited? you set the value to the attribute, the set event receives it, if the attribute already *had* that value loaded then the history is a no-net change, no UPDATE is emitted. If the attribute did not already have a value loaded, and the attribute does not specify active_history, then we don't know the old value and the history will show that the attribute has changed, from a previous value of NO_VALUE. I don't know what does it reflect only the value set or when it gets committed means, hopefully the above answers your question. yup, it does answer it! i'm aware of the active_history, I was just wondering when an updated is stated, but it seems that you already took care of this job :) 1.1. and, if set to the same value, returning the old value (set with retval=True) will make this object dirty (supposed to be the only value modified) ? the object goes into session.dirty as soon as any attributes are set. however, in the flush, we go through the effort to look for actual net changes. If there's no net change on the attribute, no UPDATE will be emitted in the flush, the object moves back to clean at that point. 2. after_flush, if one of these entity get's modified, can i track down what was modified, including my state column if possible? yes you can look in session.dirty to find all the objects that are dirty, then looking in committed_state will illustrate those attribute that had something happen to them; though the latter part here is not a public system - it will work for now but I can't guarantee that in a future major version bump that system wouldn't change. We can add public API to return .modified, e.g. a list of keys that received a modified event. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
[sqlalchemy] sessions and threads
Hi all, Seems I can't do session.query in anything other than the main thread. I'm using SQLite, and I'm not sure if it's a problem with that or SQLAlchemy it's self. Is there any kind of work around for this? Cheers, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] sessions and threads
On 7/24/15 4:14 PM, 'Chris Norman' via sqlalchemy wrote: Hi all, Seems I can't do session.query in anything other than the main thread. I'm using SQLite, and I'm not sure if it's a problem with that or SQLAlchemy it's self. Is there any kind of work around for this? Unfortunately the description can't do session.query isn't very helpful. Can you be more specific ? Cheers, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] set vs after_flush events
obj._sa_instance_state.committed_state.get('key') ==
obj._sa_instance_state.dict.get('key')
False
is this all that's necessary to track down what's modified and the past
state (i believe to be sa_instance_state.dict) ?
cheers,
richard.
On 07/24/2015 11:34 AM, Richard Gerd Kuesters wrote:
will this answer my second question?
obj._sa_instance_state.committed_state
{'batch_status': STARTED(db=1),
'updated_by': 24769797950537744L,
'updated_on': Arrow [2015-07-24T14:02:03.360479-03:00]}
cheers,
richard.
On 07/24/2015 11:13 AM, Richard Gerd Kuesters wrote:
hi!
first, /yes/, set and after_flush are quite different events :) but
here's what i'm trying to accomplish:
one object have an attribute, like 'state', and i would like to
monitor and trigger some other methods if (given scenarios):
1. the program sets a new value to a state that is equal to it's
actual state (and/or value), i can use 'set' but, does it reflect
only the value set or when it gets commited?
1.1. and, if set to the same value, returning the old value (set with
retval=True) will make this object dirty (supposed to be the only
value modified) ?
2. after_flush, if one of these entity get's modified, can i track
down what was modified, including my state column if possible?
cheers,
richard.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it,
send an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
attachment: richard.vcf
Re: [sqlalchemy] UTC timestamps for Column's server_default?
So helpful! Thanks.
*Randy Syring*
Chief Executive Developer
Direct: 502.276.0459
Office: 812.285.8766
Level 12 https://www.level12.io/
On 07/24/2015 03:45 PM, Mike Bayer wrote:
On 7/24/15 3:17 PM, Randy Syring wrote:
I have some generic timestamp columns as part of a mixin. I'd like
for these columns to have server defaults of the current UTC time.
If I wanted local time, I could just do:
created_ts = Column(DateTime, ...,
server_default=sasql.text('CURRENT_TIMESTAMP'))
The problem I'm running into is that the DB servers all have a
different way of getting UTC time:
SQLITE: select CURRENT_TIMESTAMP
Microsoft SQL: select GETUTCDATE()
PostgreSQL: select (now() at time zone 'utc')
So how do I set the server default in a db specific way when I want
to be able to define the columns in a library that will be used on
different DB servers?
yup this is a common one, so much that here it is right here:
http://docs.sqlalchemy.org/en/rel_1_0/core/compiler.html#utc-timestamp-function
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it,
send an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/NpK5n59QbV8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: set vs after_flush events
well, as a general non-specific view yes, it can be another approach. but, for the piece of code that drove me to this question, i really need to use after_flush :) cheers, richard. On 07/24/2015 02:15 PM, Jonathan Vanasco wrote: Couldn't you handle much of this with the Descriptors/Hybrids pattern? http://docs.sqlalchemy.org/en/latest/orm/mapped_attributes.html#using-descriptors-and-hybrids -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
[sqlalchemy] Syntax Checking
Because this is my first SQLAlchemy project and the schema file has 657 lines I would like to check for syntax errors before proceeding with the next step in application development. I find no index in the SA manual and cannot find the string 'syntax check' in the PDF file. Web search turns up nothing (which might be due to wrong search phrase). Is there a way to check for proper syntax prior to having sufficient code to try running the application? Rich
[sqlalchemy] Re: performance of SA
On Fri, Jul 24, 2015 at 1:25 PM, Jonathan Vanasco jvana...@gmail.com wrote: Are you comparing the speed of SqlAlchemy Core operations or SqlAlchemy ORM operations? The ORM is considerably slower. The core engine is much faster. Core. -- Jon Nelson Dyn / Senior Software Engineer p. +1 (603) 263-8029 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: set vs after_flush events
On Friday, July 24, 2015 at 1:20:15 PM UTC-4, Richard Kuesters wrote: well, as a general non-specific view yes, it can be another approach. but, for the piece of code that drove me to this question, i really need to use after_flush :) Well I mean... you could use that pattern to catch and annotate the object with I've changed! info, then do your cleanup in the after_flush. What popped into my mind as another use-case is this: touching an object's property to mark it dirty (even if SqlAlchemy doesn't interpret it as such, because the value is the same), then if it's not updated in the flush event, send the update anyways -- so a db-side stored procedure runs. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Syntax Checking
On 7/24/15 1:49 PM, Rich Shepard wrote: Because this is my first SQLAlchemy project and the schema file has 657 lines I would like to check for syntax errors before proceeding with the next step in application development. I find no index in the SA manual and cannot find the string 'syntax check' in the PDF file. Web search turns up nothing (which might be due to wrong search phrase). Is there a way to check for proper syntax prior to having sufficient code to try running the application? Python syntax or SQL syntax? Typically in Python we rely on linters and runtime checks for this, same idea with SQL. If the SQL syntax is off, the database will tell you by sending an error to the driver which will result in an exception raise. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: set vs after_flush events
Couldn't you handle much of this with the Descriptors/Hybrids pattern? http://docs.sqlalchemy.org/en/latest/orm/mapped_attributes.html#using-descriptors-and-hybrids -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: set vs after_flush events
well, application-wise it is really to run other procedures, not from the database or python side, but from a message broker that's expecting anything to happen to that value -- even if it's just a touch :) err ... it's quite a specific architecture for dumb clients, so i'm just taking some extra security measures ;) On 07/24/2015 02:52 PM, Jonathan Vanasco wrote: On Friday, July 24, 2015 at 1:20:15 PM UTC-4, Richard Kuesters wrote: well, as a general non-specific view yes, it can be another approach. but, for the piece of code that drove me to this question, i really need to use after_flush :) Well I mean... you could use that pattern to catch and annotate the object with I've changed! info, then do your cleanup in the after_flush. What popped into my mind as another use-case is this: touching an object's property to mark it dirty (even if SqlAlchemy doesn't interpret it as such, because the value is the same), then if it's not updated in the flush event, send the update anyways -- so a db-side stored procedure runs. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
[sqlalchemy] Re: performance of SA
Are you comparing the speed of SqlAlchemy Core operations or SqlAlchemy ORM operations? The ORM is considerably slower. The core engine is much faster. There is also this: http://docs.sqlalchemy.org/en/latest/faq/performance.html -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] set vs after_flush events
On 7/24/15 10:45 AM, Richard Gerd Kuesters wrote:
obj._sa_instance_state.committed_state.get('key') ==
obj._sa_instance_state.dict.get('key')
False
is this all that's necessary to track down what's modified and the
past state (i believe to be sa_instance_state.dict) ?
I'd prefer you use the public APIs but sure, those are the mechanics of
it right now.
cheers,
richard.
On 07/24/2015 11:34 AM, Richard Gerd Kuesters wrote:
will this answer my second question?
obj._sa_instance_state.committed_state
{'batch_status': STARTED(db=1),
'updated_by': 24769797950537744L,
'updated_on': Arrow [2015-07-24T14:02:03.360479-03:00]}
cheers,
richard.
On 07/24/2015 11:13 AM, Richard Gerd Kuesters wrote:
hi!
first, /yes/, set and after_flush are quite different events :) but
here's what i'm trying to accomplish:
one object have an attribute, like 'state', and i would like to
monitor and trigger some other methods if (given scenarios):
1. the program sets a new value to a state that is equal to it's
actual state (and/or value), i can use 'set' but, does it reflect
only the value set or when it gets commited?
1.1. and, if set to the same value, returning the old value (set
with retval=True) will make this object dirty (supposed to be the
only value modified) ?
2. after_flush, if one of these entity get's modified, can i track
down what was modified, including my state column if possible?
cheers,
richard.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it,
send an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it,
send an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] set vs after_flush events
yes, a public api would be awesome, perhaps for a future version? :)
for now, i'll stick to that -- since it works, heh.
cheers,
richard.
On 07/24/2015 12:59 PM, Mike Bayer wrote:
On 7/24/15 10:45 AM, Richard Gerd Kuesters wrote:
obj._sa_instance_state.committed_state.get('key') ==
obj._sa_instance_state.dict.get('key')
False
is this all that's necessary to track down what's modified and the
past state (i believe to be sa_instance_state.dict) ?
I'd prefer you use the public APIs but sure, those are the mechanics
of it right now.
cheers,
richard.
On 07/24/2015 11:34 AM, Richard Gerd Kuesters wrote:
will this answer my second question?
obj._sa_instance_state.committed_state
{'batch_status': STARTED(db=1),
'updated_by': 24769797950537744L,
'updated_on': Arrow [2015-07-24T14:02:03.360479-03:00]}
cheers,
richard.
On 07/24/2015 11:13 AM, Richard Gerd Kuesters wrote:
hi!
first, /yes/, set and after_flush are quite different events :) but
here's what i'm trying to accomplish:
one object have an attribute, like 'state', and i would like to
monitor and trigger some other methods if (given scenarios):
1. the program sets a new value to a state that is equal to it's
actual state (and/or value), i can use 'set' but, does it reflect
only the value set or when it gets commited?
1.1. and, if set to the same value, returning the old value (set
with retval=True) will make this object dirty (supposed to be the
only value modified) ?
2. after_flush, if one of these entity get's modified, can i track
down what was modified, including my state column if possible?
cheers,
richard.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it,
send an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it,
send an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it,
send an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
attachment: richard.vcf
[sqlalchemy] set vs after_flush events
hi! first, /yes/, set and after_flush are quite different events :) but here's what i'm trying to accomplish: one object have an attribute, like 'state', and i would like to monitor and trigger some other methods if (given scenarios): 1. the program sets a new value to a state that is equal to it's actual state (and/or value), i can use 'set' but, does it reflect only the value set or when it gets commited? 1.1. and, if set to the same value, returning the old value (set with retval=True) will make this object dirty (supposed to be the only value modified) ? 2. after_flush, if one of these entity get's modified, can i track down what was modified, including my state column if possible? cheers, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
Re: [sqlalchemy] performance of SA
On 7/24/15 2:19 PM, Jonathon Nelson wrote: I should preface this by saying I'm a huge fan of SQLAlchemy. Huge! However, when trying to extol the virtues of SQLAlchemy I inevitably run into this issue: But it's slow! My usual response to that is that, yes, it is somewhat slower than raw MySQL or PostgreSQL or whatever DB-API you are using, but that performance hit (if it's present at all) is outweighed by the eleventy billion awesome things listed here. I wrote some code (which I can send if it's useful) that compares fetching 10 million rows from a table with 2 integer columns. The comparison is between raw psycopg2 and two variations using SQLAlchemy: stream=False, stream=True. raw psycopg2 is consistently in the 5.5 to 6.0 second range SQLAlchemy is in the 20 second range. The C extensions are in use, and this is with SQALchemy 1.0.6. The run looks like this: SQLAlchemy version: 1.0.6 psycopg2 version: 2.5.2 (dt dec pq3 ext) PostgreSQL version: PostgreSQL 9.3.6 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.3 20140627 [gcc-4_8-branch revision 212064], 64-bit sqlalchemy.cresultproxy is available. using psycopg2: 1000 rows in 5.78s (1729246.93 row/s) using sqlalchemy, with stream=False: 1000 rows in 17.71s (564795.64 row/s) using sqlalchemy, with stream=True: 1000 rows in 20.65s (484226.33 row/s) I would wager that any low-hanging performance fruit has already been picked, but what might a plan of attack look like here? 1. No idea what stream is, I guess you mean stream_results; that won't help speed, only memory usage, and even then only theoretically 2. Read everything in http://docs.sqlalchemy.org/en/rel_1_0/faq/performance.html 3. Read, analyze, and run everything in http://docs.sqlalchemy.org/en/rel_1_0/orm/examples.html#examples-performance 4. datatypes, datatypes, datatypes. they matter, a lot. The profiling you'll do in #2 will reveal if any of these are getting in the way 5. Fetching 1000 rows all at once is not a realistic use case; it will take up a large amount of memory which in turn adds a lot of overhead to the interpreter, because the driver buffers results, and you can see turning that off is not very helpful as server side cursors have their own overhead, so you should be batching. 6. To get perspective on pure C vs. pure Python, try comparing your psycopg2 script to the identical script, but drop in pg8000 instead. 7. Try your script in SQLA 0.7, 0.8, and 0.9. You should be able see I've not been entirely idle in this area of concern. -- Jon Nelson Dyn / Senior Software Engineer p. +1 (603) 263-8029 tel:%2B1%20%28603%29%20263-8029 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: set vs after_flush events
yeah, that's basically what i'm doing: gathering information about what's happening and sending a response as quick as i can, since most of the clients are step machines (they still exists), so ... :) On 07/24/2015 04:01 PM, Jonathan Vanasco wrote: On Friday, July 24, 2015 at 2:06:15 PM UTC-4, Richard Kuesters wrote: well, application-wise it is really to run other procedures, not from the database or python side, but from a message broker that's expecting anything to happen to that value -- even if it's just a touch :) err ... it's quite a specific architecture for dumb clients, so i'm just taking some extra security measures ;) It's not really that dump of an architecture. I picked up on the value/importance of a simple touch. Just throwing out some more ideas... We have a caching system in place for public data for a pyramid app using SqlAlchemy and Dogpile(redis). When objects are fetched form the cache, a `postcache` hook is performed and... if the object requires a lot of processing... it can register the object and an action into a global pool. We then use an event in Pyramid to pop and process everything in the pool. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
Re: [sqlalchemy] Syntax Checking
On Fri, 24 Jul 2015, Mike Bayer wrote: Python syntax or SQL syntax? Mike, The former. Typically in Python we rely on linters and runtime checks for this, same idea with SQL. Have not used a lint before with Python, but will run it on my SQLAlchemy code. I know that SQLite and postgres let me know in no uncertain terms when my SQL is off. And thanks for yesterday's reply. It forced me out of my rut to look for the proper way to relate all the information ... which I found. Regards, Rich
Re: [sqlalchemy] UTC timestamps for Column's server_default?
On 7/24/15 3:17 PM, Randy Syring wrote:
I have some generic timestamp columns as part of a mixin. I'd like
for these columns to have server defaults of the current UTC time. If
I wanted local time, I could just do:
created_ts = Column(DateTime, ...,
server_default=sasql.text('CURRENT_TIMESTAMP'))
The problem I'm running into is that the DB servers all have a
different way of getting UTC time:
SQLITE: select CURRENT_TIMESTAMP
Microsoft SQL: select GETUTCDATE()
PostgreSQL: select (now() at time zone 'utc')
So how do I set the server default in a db specific way when I want to
be able to define the columns in a library that will be used on
different DB servers?
yup this is a common one, so much that here it is right here:
http://docs.sqlalchemy.org/en/rel_1_0/core/compiler.html#utc-timestamp-function
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Syntax Checking
flake8 is super simple - it checks your code for mistakes (undeclared vars, non-runnable code, etc) and pushes you to write pep8 style code. the only things you need to do really are: * write a .cfg for various projects, so you can turn off some warnings * get in the habit of running it before checkins and ALWAYS before merge/deploy. In terms of unit tests, SqlAlchemy implements a lot -- as do other packages you use. Take a look at their source repos -- it's the easiest way to learn. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Syntax Checking
In terms of linters, `flake8` (https://pypi.python.org/pypi/flake8) catches most mistakes I've made with SqlAlchemy. It's also useful to start writing Unit Tests that will interact with your SqlAlchemy models in predicted ways -- in addition to continually checking core functionality. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] performance of SA
On 7/24/15 2:44 PM, Mike Bayer wrote: On 7/24/15 2:19 PM, Jonathon Nelson wrote: I should preface this by saying I'm a huge fan of SQLAlchemy. Huge! However, when trying to extol the virtues of SQLAlchemy I inevitably run into this issue: But it's slow! My usual response to that is that, yes, it is somewhat slower than raw MySQL or PostgreSQL or whatever DB-API you are using, but that performance hit (if it's present at all) is outweighed by the eleventy billion awesome things listed here. I wrote some code (which I can send if it's useful) that compares fetching 10 million rows from a table with 2 integer columns. The comparison is between raw psycopg2 and two variations using SQLAlchemy: stream=False, stream=True. raw psycopg2 is consistently in the 5.5 to 6.0 second range SQLAlchemy is in the 20 second range. The C extensions are in use, and this is with SQALchemy 1.0.6. The run looks like this: SQLAlchemy version: 1.0.6 psycopg2 version: 2.5.2 (dt dec pq3 ext) PostgreSQL version: PostgreSQL 9.3.6 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.3 20140627 [gcc-4_8-branch revision 212064], 64-bit sqlalchemy.cresultproxy is available. using psycopg2: 1000 rows in 5.78s (1729246.93 row/s) using sqlalchemy, with stream=False: 1000 rows in 17.71s (564795.64 row/s) using sqlalchemy, with stream=True: 1000 rows in 20.65s (484226.33 row/s) I'm also seeing absolutely nothing like those times. Are you sure that you are actually iterating the rows you get back from psycopg2? Here are the results on OSX with 500K rows using the large resultsets sample suite: classics-MacBook-Pro:sqlalchemy classic$ python -m examples.performance --dburl postgresql://scott:tiger@localhost/test large_resultsets --num 50 Running setup once... Tests to run: test_orm_full_objects_list, test_orm_full_objects_chunks, test_orm_bundles, test_orm_columns, test_core_fetchall, test_core_fetchmany_w_streaming, test_core_fetchmany, test_dbapi_fetchall_plus_append_objects, test_dbapi_fetchall_no_object test_orm_full_objects_list : Load fully tracked ORM objects into one big list(). (50 iterations); total time 12.009639 sec test_orm_full_objects_chunks : Load fully tracked ORM objects a chunk at a time using yield_per(). (50 iterations); total time 9.584631 sec test_orm_bundles : Load lightweight bundle objects using the ORM. (50 iterations); total time 3.525947 sec test_orm_columns : Load individual columns into named tuples using the ORM. (50 iterations); total time 2.946797 sec test_core_fetchall : Load Core result rows using fetchall. (50 iterations); total time 2.176137 sec test_core_fetchmany_w_streaming : Load Core result rows using fetchmany/streaming. (50 iterations); total time 2.268198 sec test_core_fetchmany : Load Core result rows using Core / fetchmany. (50 iterations); total time 1.860707 sec test_dbapi_fetchall_plus_append_objects : Load rows using DBAPI fetchall(), generate an object for each row. (50 iterations); total time 2.121502 sec test_dbapi_fetchall_no_object : Load rows using DBAPI fetchall(), don't make any objects. (50 iterations); total time 1.779020 sec The very fastest for psycopg2 is 1.7 seconds, and a plain old Core load does it in 2.17 seconds. That is only 20% slower, rather than the 400% slower you are claiming. Basically if you have folks claiming SQLA is slow, the answer is simple: it's not. I would wager that any low-hanging performance fruit has already been picked, but what might a plan of attack look like here? 1. No idea what stream is, I guess you mean stream_results; that won't help speed, only memory usage, and even then only theoretically 2. Read everything in http://docs.sqlalchemy.org/en/rel_1_0/faq/performance.html 3. Read, analyze, and run everything in http://docs.sqlalchemy.org/en/rel_1_0/orm/examples.html#examples-performance 4. datatypes, datatypes, datatypes. they matter, a lot. The profiling you'll do in #2 will reveal if any of these are getting in the way 5. Fetching 1000 rows all at once is not a realistic use case; it will take up a large amount of memory which in turn adds a lot of overhead to the interpreter, because the driver buffers results, and you can see turning that off is not very helpful as server side cursors have their own overhead, so you should be batching. 6. To get perspective on pure C vs. pure Python, try comparing your psycopg2 script to the identical script, but drop in pg8000 instead. 7. Try your script in SQLA 0.7, 0.8, and 0.9. You should be able see I've not been entirely idle in this area of concern. -- Jon Nelson Dyn / Senior Software Engineer p. +1 (603) 263-8029 tel:%2B1%20%28603%29%20263-8029 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to
Re: [sqlalchemy] Re: set vs after_flush events
On Friday, July 24, 2015 at 2:06:15 PM UTC-4, Richard Kuesters wrote: well, application-wise it is really to run other procedures, not from the database or python side, but from a message broker that's expecting anything to happen to that value -- even if it's just a touch :) err ... it's quite a specific architecture for dumb clients, so i'm just taking some extra security measures ;) It's not really that dump of an architecture. I picked up on the value/importance of a simple touch. Just throwing out some more ideas... We have a caching system in place for public data for a pyramid app using SqlAlchemy and Dogpile(redis). When objects are fetched form the cache, a `postcache` hook is performed and... if the object requires a lot of processing... it can register the object and an action into a global pool. We then use an event in Pyramid to pop and process everything in the pool. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] UTC timestamps for Column's server_default?
I have some generic timestamp columns as part of a mixin. I'd like for
these columns to have server defaults of the current UTC time. If I wanted
local time, I could just do:
created_ts = Column(DateTime, ...,
server_default=sasql.text('CURRENT_TIMESTAMP'))
The problem I'm running into is that the DB servers all have a different
way of getting UTC time:
SQLITE: select CURRENT_TIMESTAMP
Microsoft SQL: select GETUTCDATE()
PostgreSQL: select (now() at time zone 'utc')
So how do I set the server default in a db specific way when I want to be
able to define the columns in a library that will be used on different DB
servers?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Syntax Checking
On Fri, 24 Jul 2015, Jonathan Vanasco wrote: In terms of linters, `flake8` (https://pypi.python.org/pypi/flake8) catches most mistakes I've made with SqlAlchemy. It's also useful to start writing Unit Tests that will interact with your SqlAlchemy models in predicted ways -- in addition to continually checking core functionality. Jonathan, Sound advice and I'll take both. Haven't used lint since I left C for Python, and understand the value of unit testing while I know nothing about the details of implementing them. Will learn (and apply) flake8 and learn about unit testing before proceeding further. Much appreciated, Rich
[sqlalchemy] performance of SA
I should preface this by saying I'm a huge fan of SQLAlchemy. Huge! However, when trying to extol the virtues of SQLAlchemy I inevitably run into this issue: But it's slow! My usual response to that is that, yes, it is somewhat slower than raw MySQL or PostgreSQL or whatever DB-API you are using, but that performance hit (if it's present at all) is outweighed by the eleventy billion awesome things listed here. I wrote some code (which I can send if it's useful) that compares fetching 10 million rows from a table with 2 integer columns. The comparison is between raw psycopg2 and two variations using SQLAlchemy: stream=False, stream=True. raw psycopg2 is consistently in the 5.5 to 6.0 second range SQLAlchemy is in the 20 second range. The C extensions are in use, and this is with SQALchemy 1.0.6. The run looks like this: SQLAlchemy version: 1.0.6 psycopg2 version: 2.5.2 (dt dec pq3 ext) PostgreSQL version: PostgreSQL 9.3.6 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.3 20140627 [gcc-4_8-branch revision 212064], 64-bit sqlalchemy.cresultproxy is available. using psycopg2: 1000 rows in 5.78s (1729246.93 row/s) using sqlalchemy, with stream=False: 1000 rows in 17.71s (564795.64 row/s) using sqlalchemy, with stream=True: 1000 rows in 20.65s (484226.33 row/s) I would wager that any low-hanging performance fruit has already been picked, but what might a plan of attack look like here? -- Jon Nelson Dyn / Senior Software Engineer p. +1 (603) 263-8029 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
