[sqlalchemy] SQLAlchemy mapped classes and __new__ (ClassManager.new_instance undocumented)
Hi *, a while ago I noticed a small problem with SQLAlchemy. I was able to work around this, but I am still wondering if this should be required. I am doing synchronization between multiple databases (think distributed VCS). Basically, each outdated object on the receiving side is updated by updating its variables and committing it to the database. Now there is some required information in those objects which is checked in the __init__ method of each class. Therefore to create an object from the remote object, I am skipping the call to __init__ (like e.g. pickle does). (Interestingly, pickle creates an empty class first and goes to update __class__ afterwards. Why?!) So to create the instances for the mapped objects, I used instance = MyClass.__new__(MyClass) as in the attached example. This fails with an attribute error for _sa_instance_state. My work around is to use instance = manager_of_class(MyClass).new_instance() but I am wondering if this should be needed, especially since the ClassManager class is not documented. What should I be using instead? Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.orm.attributes import manager_of_class metadata = MetaData() class Base(object): pass base_table = Table(base, metadata, Column(id, Integer, primary_key=True), Column(name, String)) mapper(Base, base_table) b = Base() assert b.name is None b = manager_of_class(Base).new_instance() b.name = using manager_of_class b = Base.__new__(Base) b.name = using __new__
[sqlalchemy] strange commit behaviour
Hi list,
I have a turbogears controller, the model are elixir defined.
def copy(self, scenario_id, **kwargs):
copy a scenario
:param scenario_id: id of a :class:`model.Scenario`
:type media_id: string
user = request.environ['repoze.who.identity']['user']
scenario = Scenario.get(scenario_id)
clone = Scenario(owner = user)
Session.add(clone)
Session.commit()
from nose.tools import set_trace; set_trace()
details = kwargs.get('details', False)
return dict(scenario=clone, details=details)
I'm bound to a postgres database.
just before the commit, I can observe a IDLE in transaction lock
in PG (normal)
Then I commit() the output from sqla debug is something like :
2011-06-28 09:56:16,804 INFO sqlalchemy.engine.base.Engine.0x...3dcc
BEGIN (implicit)
2011-06-28 09:56:16,806 INFO sqlalchemy.engine.base.Engine.0x...3dcc
SELECT user.user_id AS user_user_id, user.user_name AS
user_user_name, user.password AS user_password,
user.password_check AS user_password_check, user.email_address AS
user_email_address, user.display_name AS user_display_name,
user.created AS user_created, user.active AS user_active,
user.permission_assoc_id AS user_permission_assoc_id
FROM user
WHERE user.user_id = %(param_1)s
and thus IDLE in transaction is back.
When I run this in test, the next thing is the tear down, that will
try to drop all tables to run next test, and everything will get
stuck
what am I missing here ?
regards
NIL
--
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] SQLAlchemy mapped classes and __new__ (ClassManager.new_instance undocumented)
On Jun 28, 2011, at 3:45 AM, Torsten Landschoff wrote: Hi *, a while ago I noticed a small problem with SQLAlchemy. I was able to work around this, but I am still wondering if this should be required. I am doing synchronization between multiple databases (think distributed VCS). Basically, each outdated object on the receiving side is updated by updating its variables and committing it to the database. Now there is some required information in those objects which is checked in the __init__ method of each class. Therefore to create an object from the remote object, I am skipping the call to __init__ (like e.g. pickle does). (Interestingly, pickle creates an empty class first and goes to update __class__ afterwards. Why?!) So to create the instances for the mapped objects, I used instance = MyClass.__new__(MyClass) as in the attached example. This fails with an attribute error for _sa_instance_state. My work around is to use instance = manager_of_class(MyClass).new_instance() but I am wondering if this should be needed, especially since the ClassManager class is not documented. What should I be using instead? Instrumentation has to establish state on a new object independent of __new__() - during pickling, the state is restored naturally as __dict__ is restored, during fetch of rows, new_instance() is used, during normal construction, __init__() is used. class_manager() is documented we'd only need to get new_instance() and the use case documented, seems to me that would be bug fixed. -- 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] strange commit behaviour
On Jun 28, 2011, at 4:29 AM, NiL wrote:
Hi list,
I have a turbogears controller, the model are elixir defined.
def copy(self, scenario_id, **kwargs):
copy a scenario
:param scenario_id: id of a :class:`model.Scenario`
:type media_id: string
user = request.environ['repoze.who.identity']['user']
scenario = Scenario.get(scenario_id)
clone = Scenario(owner = user)
Session.add(clone)
Session.commit()
from nose.tools import set_trace; set_trace()
details = kwargs.get('details', False)
return dict(scenario=clone, details=details)
commit() expires all attributes.As soon as they are accessed again, the
database must be queried, and a new transaction begins.
the real issue here is that its better to have a single commit() that encloses
a series of operations, and nothing happens outside of the commit(). Calling
commit() in an ad-hoc fashion inside of business methods is not a good pattern
and suggests the application doesnt have clear boundaries as to when
transactions begin and end.
I'm bound to a postgres database.
just before the commit, I can observe a IDLE in transaction lock
in PG (normal)
Then I commit() the output from sqla debug is something like :
2011-06-28 09:56:16,804 INFO sqlalchemy.engine.base.Engine.0x...3dcc
BEGIN (implicit)
2011-06-28 09:56:16,806 INFO sqlalchemy.engine.base.Engine.0x...3dcc
SELECT user.user_id AS user_user_id, user.user_name AS
user_user_name, user.password AS user_password,
user.password_check AS user_password_check, user.email_address AS
user_email_address, user.display_name AS user_display_name,
user.created AS user_created, user.active AS user_active,
user.permission_assoc_id AS user_permission_assoc_id
FROM user
WHERE user.user_id = %(param_1)s
and thus IDLE in transaction is back.
When I run this in test, the next thing is the tear down, that will
try to drop all tables to run next test, and everything will get
stuck
what am I missing here ?
regards
NIL
--
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.
[sqlalchemy] (OperationalError) (1066, Not unique table/alias: '...') when selecting only from a joined table
I want to have the following query in sqlalchemy: SELECT t_objects_1.tid AS t_objects_1_tid FROM t_objects AS t_objects_2 INNER JOIN c_objects AS c_objects_1 ON t_objects_2.tid = c_objects_1.tid INNER JOIN t_objects AS t_objects_1 ON t_objects_1.tid = t_objects_2.parent_id WHERE c.id = 1; this would return a list of parent_ids whose childs match a certain condition... I would go like the following: s.query(t_objects_1.tid).join((c_objects, t_objects_2.tid == c_objects.tid),(t_objects_1, t_objects_1.tid == t_objects_2.parent_id)).filter(c_objects.id == 1).all() ...but I get this error: (OperationalError) (1066, Not unique table/ alias: 't_objects_1') so sqlalchemy is putting the wrong alias in the from clause: SELECT t_objects_1.tid AS t_objects_1_tid FROM t_objects AS t_objects_1 INNER JOIN c_objects AS c_objects_1 ON t_objects_2.tid = c_objects_1.tid INNER JOIN t_objects AS t_objects_1 ON t_objects_1.tid = t_objects_2.parent_id WHERE c.id = %s' how can I bring sqlalchemy to use 't_objects_2' instead of 't_objects_1'? Due to project constraints I have to use sqlalchemy==0.5.6 if maybe this is a known bug of 0.5.6 please let me now -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] (OperationalError) (1066, Not unique table/alias: '...') when selecting only from a joined table
On Jun 28, 2011, at 12:26 PM, Oliver wrote: I want to have the following query in sqlalchemy: SELECT t_objects_1.tid AS t_objects_1_tid FROM t_objects AS t_objects_2 INNER JOIN c_objects AS c_objects_1 ON t_objects_2.tid = c_objects_1.tid INNER JOIN t_objects AS t_objects_1 ON t_objects_1.tid = t_objects_2.parent_id WHERE c.id = 1; this would return a list of parent_ids whose childs match a certain condition... I would go like the following: s.query(t_objects_1.tid).join((c_objects, t_objects_2.tid == c_objects.tid),(t_objects_1, t_objects_1.tid == t_objects_2.parent_id)).filter(c_objects.id == 1).all() ...but I get this error: (OperationalError) (1066, Not unique table/ alias: 't_objects_1') so sqlalchemy is putting the wrong alias in the from clause: SELECT t_objects_1.tid AS t_objects_1_tid FROM t_objects AS t_objects_1 INNER JOIN c_objects AS c_objects_1 ON t_objects_2.tid = c_objects_1.tid INNER JOIN t_objects AS t_objects_1 ON t_objects_1.tid = t_objects_2.parent_id WHERE c.id = %s' Its going to start the FROM chain from t_objects_1 since that's what's in the columns clause, at which point you then join *to* c_objects. query.select_from(t_objects_2).join()... will start the FROM clause instead from t_objects_2. Due to project constraints I have to use sqlalchemy==0.5.6 if maybe this is a known bug of 0.5.6 please let me now ah. It might not work in a version that old. Try the select_from(table) approach first, and if 0.5 isn't handling it , you should create your joins using the sqlalchemy.orm.join() function, then place the fully constructed join() construct into select_from() - I am loathe to reference the 0.5 docs as people keep finding them and thinking they are current, but an example of this is at http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with-joins (Note to people reading this: these are the *OLD DOCS* regarding 0.5; for current join usage please see http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] WITH (nolock) on all queries
Hi list, So I am working on a project with SQLAlchemy using MSSQL as a back-end DB and I need to add a WITH (nolock) statement to all my queries implicitly. Even for the .get(pk_id) method. Mike actually pointed me to the .with_hint() method on the Query object but I couldn't get it to work. PS: I also noticed that there was a .with_lockmode() and I was wondering if that could help as this can be passed at the Session level, which implies that it will affect all queries (whatever this does). Thanks, -- Alex | twitter.com/alexconrad -- 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] (OperationalError) (1066, Not unique table/alias: '...') when selecting only from a joined table
Michael Bayer wrote: - I am loathe to reference the 0.5 docs as people keep finding them and thinking they are current, but an example of this is at http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with- joins (Note to people reading this: these are the *OLD DOCS* regarding 0.5; for current join usage please see http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins) In the Quick Select links at the top of the 0.5 docs, there's no link to the 0.7 docs. Is this deliberate or has it just been overlooked? Would it be worth putting some sort of big banner at the top of the older docs pointing out that they are old? FWIW, I *really* appreciate that you keep the old versions of the docs around - I have an application that I maintain using SA 0.3, and just last week I needed to refer back to the docs. I hope they never go away! (I know they still exist in the repository, but the website is so convenient...) Cheers, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] (OperationalError) (1066, Not unique table/alias: '...') when selecting only from a joined table
On Jun 28, 2011, at 1:31 PM, King Simon-NFHD78 wrote: Michael Bayer wrote: - I am loathe to reference the 0.5 docs as people keep finding them and thinking they are current, but an example of this is at http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with- joins (Note to people reading this: these are the *OLD DOCS* regarding 0.5; for current join usage please see http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins) In the Quick Select links at the top of the 0.5 docs, there's no link to the 0.7 docs. Is this deliberate or has it just been overlooked? its a buglike thing. I'd have to commit new templates to the 0.5 repo. Would it be worth putting some sort of big banner at the top of the older docs pointing out that they are old? yeah Ive considered various things.. FWIW, I *really* appreciate that you keep the old versions of the docs around - I have an application that I maintain using SA 0.3, and just last week I needed to refer back to the docs. I hope they never go away! (I know they still exist in the repository, but the website is so convenient...) heh0.3 and 0.4 aren't up there ! I took them down a while ago. They don't work well with the build and I'm mortified by the prospect that someone might think they are current -- 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] WITH (nolock) on all queries
On Jun 28, 2011, at 12:55 PM, Alexandre Conrad wrote: Hi list, So I am working on a project with SQLAlchemy using MSSQL as a back-end DB and I need to add a WITH (nolock) statement to all my queries implicitly. Even for the .get(pk_id) method. Mike actually pointed me to the .with_hint() method on the Query object but I couldn't get it to work. PS: I also noticed that there was a .with_lockmode() and I was wondering if that could help as this can be passed at the Session level, which implies that it will affect all queries (whatever this does). well with_hint() should add it but if you really want to be heavyhanded you can override the compilation of sqlalchemy.expression.sql.Select to do it, using the form described at: http://www.sqlalchemy.org/docs/core/compiler.html#changing-the-default-compilation-of-existing-constructs Thanks, -- Alex | twitter.com/alexconrad -- 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] WITH (nolock) on all queries
I must have it wrong, I admit I don't quite understand the arguments of .with_hint() session.query(User).with_hint(User, 'WITH (nolock)').get(1) if that makes any sense (I wonder why I'd need to pass the User object again). 2011/6/28 Michael Bayer mike...@zzzcomputing.com: On Jun 28, 2011, at 12:55 PM, Alexandre Conrad wrote: Hi list, So I am working on a project with SQLAlchemy using MSSQL as a back-end DB and I need to add a WITH (nolock) statement to all my queries implicitly. Even for the .get(pk_id) method. Mike actually pointed me to the .with_hint() method on the Query object but I couldn't get it to work. PS: I also noticed that there was a .with_lockmode() and I was wondering if that could help as this can be passed at the Session level, which implies that it will affect all queries (whatever this does). well with_hint() should add it but if you really want to be heavyhanded you can override the compilation of sqlalchemy.expression.sql.Select to do it, using the form described at: http://www.sqlalchemy.org/docs/core/compiler.html#changing-the-default-compilation-of-existing-constructs Thanks, -- Alex | twitter.com/alexconrad -- 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. -- Alex | twitter.com/alexconrad -- 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] WITH (nolock) on all queries
On Jun 28, 2011, at 2:55 PM, Alexandre Conrad wrote: I must have it wrong, I admit I don't quite understand the arguments of .with_hint() session.query(User).with_hint(User, 'WITH (nolock)').get(1) if that makes any sense (I wonder why I'd need to pass the User object again). well HINT is a construct that on some backends is given per table like sybase, so that's why it accepts the entity. SQL server dialect doesn't have hints implemented, this patch will do it, what version are you on ? diff -r 223fc8419706 lib/sqlalchemy/dialects/mssql/base.py --- a/lib/sqlalchemy/dialects/mssql/base.py Mon Jun 27 19:25:35 2011 -0400 +++ b/lib/sqlalchemy/dialects/mssql/base.py Tue Jun 28 15:18:54 2011 -0400 @@ -766,6 +766,9 @@ return s return compiler.SQLCompiler.get_select_precolumns(self, select) +def get_from_hint_text(self, text): +return text + def limit_clause(self, select): # Limit in mssql is after the select keyword return I'm looking at some examples of WITH (nolock) and it appears to work the same way, the directive is given per table. with_hint() doesn't necessarily know that the query is against just one entity, it is not sophisticated enough to check for that right now. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] WITH (nolock) on all queries
On Jun 28, 2011, at 2:55 PM, Alexandre Conrad wrote: I must have it wrong, I admit I don't quite understand the arguments of .with_hint() session.query(User).with_hint(User, 'WITH (nolock)').get(1) if that makes any sense (I wonder why I'd need to pass the User object again). additional info, per this SO answer: http://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements/210443#210443 NOLOCK is functionally equivalent to an isolation level of READ UNCOMMITTED. The main difference is that you can use NOLOCK on some tables but not others, if you choose. If you plan to use NOLOCK on all tables in a complex query, then using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is easier, because you don't have to apply the hint to every table. why not set transaction isolation level on the connection ? this is a lot easier. A connection event can set that up on all connections. 2011/6/28 Michael Bayer mike...@zzzcomputing.com: On Jun 28, 2011, at 12:55 PM, Alexandre Conrad wrote: Hi list, So I am working on a project with SQLAlchemy using MSSQL as a back-end DB and I need to add a WITH (nolock) statement to all my queries implicitly. Even for the .get(pk_id) method. Mike actually pointed me to the .with_hint() method on the Query object but I couldn't get it to work. PS: I also noticed that there was a .with_lockmode() and I was wondering if that could help as this can be passed at the Session level, which implies that it will affect all queries (whatever this does). well with_hint() should add it but if you really want to be heavyhanded you can override the compilation of sqlalchemy.expression.sql.Select to do it, using the form described at: http://www.sqlalchemy.org/docs/core/compiler.html#changing-the-default-compilation-of-existing-constructs Thanks, -- Alex | twitter.com/alexconrad -- 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. -- Alex | twitter.com/alexconrad -- 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] WITH (nolock) on all queries
We have to use the 'WITH (nolock)' because of legacy requirements; however, we will look into the isolation level comment and see what our DBAs say. Oh, and thanks for the help Mike, we'll test that patch and see if it works and report back (along with anything we get on the isolation level). Thanks, Doug On Tue, Jun 28, 2011 at 12:39 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jun 28, 2011, at 2:55 PM, Alexandre Conrad wrote: I must have it wrong, I admit I don't quite understand the arguments of .with_hint() session.query(User).with_hint(User, 'WITH (nolock)').get(1) if that makes any sense (I wonder why I'd need to pass the User object again). additional info, per this SO answer: http://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements/210443#210443 NOLOCK is functionally equivalent to an isolation level of READ UNCOMMITTED. The main difference is that you can use NOLOCK on some tables but not others, if you choose. If you plan to use NOLOCK on all tables in a complex query, then using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is easier, because you don't have to apply the hint to every table. why not set transaction isolation level on the connection ? this is a lot easier. A connection event can set that up on all connections. 2011/6/28 Michael Bayer mike...@zzzcomputing.com: On Jun 28, 2011, at 12:55 PM, Alexandre Conrad wrote: Hi list, So I am working on a project with SQLAlchemy using MSSQL as a back-end DB and I need to add a WITH (nolock) statement to all my queries implicitly. Even for the .get(pk_id) method. Mike actually pointed me to the .with_hint() method on the Query object but I couldn't get it to work. PS: I also noticed that there was a .with_lockmode() and I was wondering if that could help as this can be passed at the Session level, which implies that it will affect all queries (whatever this does). well with_hint() should add it but if you really want to be heavyhanded you can override the compilation of sqlalchemy.expression.sql.Select to do it, using the form described at: http://www.sqlalchemy.org/docs/core/compiler.html#changing-the-default-compilation-of-existing-constructs Thanks, -- Alex | twitter.com/alexconrad -- 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. -- Alex | twitter.com/alexconrad -- 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. -- Doug Morgan http://about.me/doug.morgan -- 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: strange commit behaviour
thanks for your prompt reply still if I do something like http://pastebin.com/UMRcYjp3 (The ordering in children demo is pointless for what I care now) It freezes against a PG database, I need to drop the tables for a second test to run in isolation, and I can't manage to, the commit isn't usued. thanks for any advice NIL -- 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] SQLAlchemy 0.6.5 (and 0.6.8) SessionExtension after_flush doesn't gets called
Oh, thank you very much, that makes sense to me (and I knew I am wrong somewhere because there are even tests for those after_flush events that work). -- 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/-/SCwIxKSy1lQJ. 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: strange commit behaviour
On Jun 28, 2011, at 4:29 PM, NiL wrote: thanks for your prompt reply still if I do something like http://pastebin.com/UMRcYjp3 (The ordering in children demo is pointless for what I care now) It freezes against a PG database, I need to drop the tables for a second test to run in isolation, and I can't manage to, the commit isn't usued. the script completes for me with no issue against a Postgresql database.0.7 and 0.6. -- 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] Persist an object on multiple tables across multiple shards
Hi, Here is the scenario. I have two objects: node and edge. My DB is sharded on node ids. Edges connect two node ids. I have essentially three tables: node, inboundEdge, outboundEdge. If an edge (A,B) is created, I want it to be persisted in the inbound edge table of node B shard and in the outbound edge table of node A's shard. Is there a way to have a single class Edge that would map to two tables potentially on different shards? I can of course come up with two distinct classes InboundEdge and OutboundEdge but I would really like to abstract that replication from the application. Thank you, Arthur -- 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] Problem with DeclarativeMeta
We have a database of about 100 tables with timestamp audit columns on most,
but not all tables, and use declarative to describe the database. I am
attempting to use a metaclass to create a base class that defines the audit
columns so we can stop defining them on every class. This seems to work OK
in most cases, but when I mix classes derived from my metaclass with classes
derived from the out-of-the-box base class I get an UnmappedClassError.
I am using version 0.5.8 (we will be able to upgrade soon, but not yet), but
get the same error using a metaclass customized for 0.6.
The stripped down example below should gives the error. the interesting
thing I have seen is that when running with code for our full database, the
error does not always point at the same table and occasionally gives an
Attribute error instead of the UnmappedClassError. In this example, if you
remove the product relationship property on SubSystemModule; the error
disappears. Also, if both classes are derived from AuditBase, there is no
error.
Since we generate most of the SQLAlchemy classes directly from our data
model, we can go back to adding audit columns to every class, but we don't
really want to do that.
import datetime
from sqlalchemy import __version__ as sa_ver
from sqlalchemy import (Column, ForeignKeyConstraint, PrimaryKeyConstraint,
create_engine, MetaData, DateTime, Integer, String)
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
Base = declarative_base()
metadata = Base.metadata
class AuditMeta(DeclarativeMeta):
def __init__(cls, classname, bases, dict_):
dict_['ModificationTS'] = Column(ModificationDate,DateTime
,default=datetime.datetime.now
,onupdate=datetime.datetime.now)
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
AuditBase = declarative_base(metaclass=AuditMeta, metadata=metadata)
class Product(AuditBase):
__tablename__ = 'Product'
PID = Column(Integer)
Name = Column(String)
__table_args__ = (
PrimaryKeyConstraint('PID'),
{})
class SubSystemModule(Base):
__tablename__ = 'SubSystemModule'
SSMID = Column(Integer)
PID = Column(Integer)
Name = Column(String)
__table_args__ = (
PrimaryKeyConstraint('SSMID'),
ForeignKeyConstraint(['PID'],['Product.PID']),
{})
product = relation('Product',
backref=backref('subsystemmodule', cascade='all'),
primaryjoin='SubSystemModule.PID==Product.PID')
if __name__ == '__main__':
print 'SQLAlchemy version:',sa_ver
from sqlalchemy.orm import compile_mappers
compile_mappers()
--
Mike Conley
--
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] In case of joinedload_all how do I order by on a columns of those relations
Hi
I'm trying to order by a column from a relationship.
Taken example from:
http://www.sqlalchemy.org/docs/orm/loading.html#routing-explicit-joins-statements-into-eagerly-loaded-collections
In case of
query.options(joinedload_all('orders.items.keywords'))...
or
query.options(joinedload_all(User.orders, Order.items, Item.keywords))
I would like to do something like:
query.options(joinedload_all('orders.items.keywords')).order_by('user.orders.items.keywords.name')
Tried this above but didn't work. Searched for some sample/tutorials
but with no luck.
thanks for any direction.
marc
--
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] Slightly confusing error when session set up incorrectly
Hello all,
When using sqlalchemy 0.7 with sqlite, if I enter the path URI
incorrectly I will get an error like this:
OperationalError: (OperationalError) unable to open database file
None None
What does the None None signify? Would it be possible to change this
exception to be a bit more descriptive and a little less cryptic? eg.
Include the URI that failed?
Additionally, I notice that sqlalchemy doesn't attempt to make an
actual connection to the database until you perform the first query.
That means that code like this will appear to work:
Session = sessionmaker()
engine = create_engine('sqlite:///%s' % invalid_filename)
Base.metadata.bind = engine
db_session = Session(bind=engine)
Yet eventually, when you make a query within your app code, you'll get
an exception because the URI was wrong. In my case, due to the
exception text being a little vague, I thought this was a problem with
my app's db access patterns (as I do things like delete the database
file manually) when it was just a problem with the initial connection.
I found a way to trigger this error earlier, by issuing
engine.connect() in the above routine, but I notice that this isn't
explicitly documented in Using The Session (http://
www.sqlalchemy.org/docs/orm/session.html). I expect I am not unusual
in wanting an error in session configuration to fail as soon as
possible, so if it's not possible or efficient to do this
automatically as part of creating the session, perhaps this part of
the docs could be clarified so that new users in future will know
exactly what to call to test this configuration? (In fact, this part
of the docs is a bit confusing in general - there seem to be a fair
few permutations of how to approach it - pass arguments to
sessionmaker? or to Session.configure? or to Session's constructor? -
and it's not clear why they all have to exist.)
--
Ben Sizer
--
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] Slightly confusing error when session set up incorrectly
On Jun 28, 2011, at 9:34 PM, Ben Sizer wrote:
Hello all,
When using sqlalchemy 0.7 with sqlite, if I enter the path URI
incorrectly I will get an error like this:
OperationalError: (OperationalError) unable to open database file
None None
What does the None None signify? Would it be possible to change this
exception to be a bit more descriptive and a little less cryptic? eg.
Include the URI that failed?
That error is raised by SQLite and we just propagate it out. DBAPI does not
have any standardized error codes and there's no clean way for us to wrap the
exception into something new without concealing what the original exception was
- we wrap it in a sqlalchemy.exc.DBAPIError so that an application can catch
DBAPI exceptions in a generic way (otherwise you'd have to import
sqlite3.OperationalError), but other than that we don't mess with it.Python
3 allows exceptions to be chained but Python 2 doesn't have a terrific way to
do it. The None None is part of DBAPIError's behavior, it illustrates the
statement and parameters which took place.We can consider not displaying
this if the statement is None, i agree it's not pleasing to the eye but nobody
has ever mentioned it before.
Additionally, I notice that sqlalchemy doesn't attempt to make an
actual connection to the database until you perform the first query.
That means that code like this will appear to work:
Session = sessionmaker()
engine = create_engine('sqlite:///%s' % invalid_filename)
Base.metadata.bind = engine
db_session = Session(bind=engine)
Yet eventually, when you make a query within your app code, you'll get
an exception because the URI was wrong. In my case, due to the
exception text being a little vague, I thought this was a problem with
my app's db access patterns (as I do things like delete the database
file manually) when it was just a problem with the initial connection.
That is true, all connections/transactions are lazy initializing.Sorry this
was a surprise, though I don't think this is much of an issue once you get used
to dealing with lazy initializing objects.
The formal pattern at play with SQLAlchemy's connection pool, Engine and
Session behavior is called the Proxy Pattern, a decent description is at
http://sourcemaking.com/design_patterns/proxy .
I found a way to trigger this error earlier, by issuing
engine.connect() in the above routine, but I notice that this isn't
explicitly documented in Using The Session (http://
www.sqlalchemy.org/docs/orm/session.html).
the lazy initializing behavior of the Session is documented:
http://www.sqlalchemy.org/docs/orm/session.html#unitofwork-transaction
But that doesn't have anything to do with the Engine, which is its own thing.
As far as the Engine, the docs currently use the term connect in conjunction
with create_engine() which is for simplicities' sake, but is technically
inaccurate, perhaps come up with some term other than connect, configure a
connection source perhaps. We can add an explicit sentence to the top of
http://www.sqlalchemy.org/docs/core/connections.html.
I expect I am not unusual
in wanting an error in session configuration to fail as soon as
possible,
Nobody has ever raised this issue before to my recollection. I'm not sure
lots of users are phased whether the stack trace starts at the
Session.configure() line or if it starts later as soon as their first unit test
tries to hit the database - they get the same error, see that the connection
URL is bad, and fix it.
so if it's not possible or efficient to do this
automatically as part of creating the session,
I suppose inefficiency is the only issue but its so unnecessarily inefficient,
most people would consider it to be wrong behavior.A Session may be
configured such that depending on what's requested of it, it can connect to any
number of different engines - connecting to several/dozens/hundreds of engines
unconditionally upon construction is not an option. Its usage is such that
once rollback or commit is called, it's essentially dormant, not using any
resources, until it's called upon again to do something. The lazy
initialization model makes the Session very easy to use as they are cheap to
create and only establish state with the database as needed, release it as soon
as it's not.It's a very user-friendly usage model, as long as one is
comfortable with lazy initialization.
perhaps this part of
the docs could be clarified so that new users in future will know
exactly what to call to test this configuration?
Feel free to suggest what verbiage you're looking for, it's not hitting me
strongly what the confusion is - the application failed to connect, you get an
error describing the problem, the stack trace shows it happened upon connect()
inside the pool. An application should always have unit tests which will
reveal basic issues like this
