[sqlalchemy] Re: How to specify NOLOCK queries in SA (mssql)
Thanks for your speedy response Michael :) Yes, after more searching it does rather look like this is an issue that has to do with MSSQL, rather than SQLA. This post (http:// forums.microsoft.com/MSDN/ShowPost.aspx?PostID=570896SiteID=1) seems to point to a similar opinion, points the finger at db connector parameters. For anyone else who reads this looking for an answer to their own issue, so you know, we have decided to make changes to our database configuration options to remove the default locking of records during a query, as that looks like it may resolve our particular issue. We have the luxury of not having our data used in a way that requires locking. This approach may not be appropriate for other users, but it does suit our needs, so we'll try this out... On May 14, 11:53 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 13, 2008, at 9:46 PM, BruceC wrote: Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL Server 2005 db via PYODBC. I'm getting a lot of database locks, the statements appear to be like this: SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF I cant locate the string FMTONLY within the MSSQL dialect at all. So this is not something SQLA is issuing. I would like to explicitly make this a NOLOCK select, to prevent these queries from generating locks, as these locks appear to be impacting our application performance server stability. Can anyone point me in the direction of where such a change could be made? our MSSQL devs might be able to help here but also you might want to see what the source of the locking is (i.e. some (Py)ODBC thing); SQLA is not issuing any locking. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to specify NOLOCK queries in SA (mssql)
Just to be a bit more specific, these are the changes we will try in our mssql db: ALTER DATABASE MyDBName SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDBName SET READ_COMMITTED_SNAPSHOT ON :) On May 14, 4:36 pm, BruceC [EMAIL PROTECTED] wrote: Thanks for your speedy response Michael :) Yes, after more searching it does rather look like this is an issue that has to do with MSSQL, rather than SQLA. This post (http:// forums.microsoft.com/MSDN/ShowPost.aspx?PostID=570896SiteID=1) seems to point to a similar opinion, points the finger at db connector parameters. For anyone else who reads this looking for an answer to their own issue, so you know, we have decided to make changes to our database configuration options to remove the default locking of records during a query, as that looks like it may resolve our particular issue. We have the luxury of not having our data used in a way that requires locking. This approach may not be appropriate for other users, but it does suit our needs, so we'll try this out... On May 14, 11:53 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 13, 2008, at 9:46 PM, BruceC wrote: Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL Server 2005 db via PYODBC. I'm getting a lot of database locks, the statements appear to be like this: SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF I cant locate the string FMTONLY within the MSSQL dialect at all. So this is not something SQLA is issuing. I would like to explicitly make this a NOLOCK select, to prevent these queries from generating locks, as these locks appear to be impacting our application performance server stability. Can anyone point me in the direction of where such a change could be made? our MSSQL devs might be able to help here but also you might want to see what the source of the locking is (i.e. some (Py)ODBC thing); SQLA is not issuing any locking. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] LIKE doesn't work in direct SQL queries
Hi. I want to execute ready SQL query using SA's engine: engine.execute(r''' SELECT * FROM City WHERE name LIKE 'a%' ''') I get this strange error: /usr/share/python2.5/site-packages/SQLAlchemy-0.4.6dev_r4720-py2.5.egg/ sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context) 128 129 def do_execute(self, cursor, statement, parameters, context=None): -- 130 cursor.execute(statement, parameters) 131 132 def is_disconnect(self, e): TypeError: 'dict' object is unindexable I'm using Python 2.5.2, PostgreSQL 8.3.1, psycopg2 2.0.7, SQLAlchemy 0.4.5 and latest from trunk (0.4 branch). What is not working is the percent sign: when I don't use them, everything is OK. Underscores work. I suspect that it's an error in psycopg2, which thinks that percents are for parameters. It's strange that such a simple thing doesn't work - or am I doing something wrong? Regards, Artur --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query filtering like isinstance()
Michael Bayer [EMAIL PROTECTED] writes: If I want to query on containers and on area, I can simply do q = Container.query().filter(...) but, if I receive a query on Item and a base class, say either Item, Container or Area, how can I filter() my query to receive only the sub-items from this base class? filter on type_.in([area, container]) is one approach. Easier though is session.query(Container); it'll load from the join of items/ containers so you wouldn't get any non-Container objects. Sounds good. I didn't find how to get the polymorphic_identity of a mapped class. Is it possible to retried it if I have only the class object? This is not a big problem since I can use Item.__name__ as the polymorphic_identity. -- Yannick Gingras --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query filtering like isinstance()
On May 14, 2008, at 10:56 AM, Yannick Gingras wrote: Michael Bayer [EMAIL PROTECTED] writes: If I want to query on containers and on area, I can simply do q = Container.query().filter(...) but, if I receive a query on Item and a base class, say either Item, Container or Area, how can I filter() my query to receive only the sub-items from this base class? filter on type_.in([area, container]) is one approach. Easier though is session.query(Container); it'll load from the join of items/ containers so you wouldn't get any non-Container objects. Sounds good. I didn't find how to get the polymorphic_identity of a mapped class. Is it possible to retried it if I have only the class object? This is not a big problem since I can use Item.__name__ as the polymorphic_identity. class_mapper(cls).polymorphic_identity should work --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Pre-commit hooks
Greetings Alchemists, Is it possible to define a hook in a mapped class that will be called to test the sanity of an instance before it gets committed? As an example: class Item(object): def _pre_commit(self): assert (self.dry_weight + self.fluids) 50 mapper(Item, items_table) I don't want to put the test mutators of dry_weight or fluids since it's OK to have a temporary inconsistent state as long as the state is consistent at commit time. I see that some of this functionality if covered by MapperExtention but since the test is only related to Item I'd rather put the test in it. -- Yannick Gingras --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pre-commit hooks
On May 14, 2008, at 11:07 AM, Yannick Gingras wrote: Greetings Alchemists, Is it possible to define a hook in a mapped class that will be called to test the sanity of an instance before it gets committed? As an example: class Item(object): def _pre_commit(self): assert (self.dry_weight + self.fluids) 50 mapper(Item, items_table) I don't want to put the test mutators of dry_weight or fluids since it's OK to have a temporary inconsistent state as long as the state is consistent at commit time. I see that some of this functionality if covered by MapperExtention but since the test is only related to Item I'd rather put the test in it. easy enough to build yourself a generic MapperExtension that scans incoming objects for a _pre_commit() method. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query filtering like isinstance()
Michael Bayer [EMAIL PROTECTED] writes: Sounds good. I didn't find how to get the polymorphic_identity of a mapped class. Is it possible to retried it if I have only the class object? This is not a big problem since I can use Item.__name__ as the polymorphic_identity. class_mapper(cls).polymorphic_identity should work It does. Thanks! -- Yannick Gingras --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pre-commit hooks
Yannick Gingras wrote: Greetings Alchemists, Is it possible to define a hook in a mapped class that will be called to test the sanity of an instance before it gets committed? As an example: class Item(object): def _pre_commit(self): assert (self.dry_weight + self.fluids) 50 mapper(Item, items_table) I don't want to put the test mutators of dry_weight or fluids since it's OK to have a temporary inconsistent state as long as the state is consistent at commit time. I see that some of this functionality if covered by MapperExtention but since the test is only related to Item I'd rather put the test in it. The way I've done this in the past is to define a fairly generic MapperExtension that calls hook methods on my classes. Here's an example: -- import sqlalchemy as sa class EventExtension(sa.orm.MapperExtension): def handle_event(self, event, mapper, connection, instance): name = '__%s__' % event handler = getattr(instance, name, None) if handler: handler() def _add_handler(event): def handle_event(self, *args, **kwargs): self.handle_event(event, *args, **kwargs) return sa.orm.EXT_CONTINUE setattr(EventExtension, event, handle_event) for _name in ['before_insert', 'after_insert', 'before_update', 'after_update', 'before_delete', 'after_delete']: _add_handler(_name) event_extension = EventExtension() --- Now if your mapped class has __before_insert__, __after_insert__ etc. methods, they will be called at the appropriate point. (Remember to include event_extension when you map the class) Hope that helps, 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: LIKE doesn't work in direct SQL queries
try two percent signs to escape it - %%. On May 14, 2008, at 10:50 AM, Artur Siekielski wrote: Hi. I want to execute ready SQL query using SA's engine: engine.execute(r''' SELECT * FROM City WHERE name LIKE 'a%' ''') I get this strange error: /usr/share/python2.5/site-packages/SQLAlchemy-0.4.6dev_r4720- py2.5.egg/ sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context) 128 129 def do_execute(self, cursor, statement, parameters, context=None): -- 130 cursor.execute(statement, parameters) 131 132 def is_disconnect(self, e): TypeError: 'dict' object is unindexable I'm using Python 2.5.2, PostgreSQL 8.3.1, psycopg2 2.0.7, SQLAlchemy 0.4.5 and latest from trunk (0.4 branch). What is not working is the percent sign: when I don't use them, everything is OK. Underscores work. I suspect that it's an error in psycopg2, which thinks that percents are for parameters. It's strange that such a simple thing doesn't work - or am I doing something wrong? Regards, Artur --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: LIKE doesn't work in direct SQL queries
It works :). Ah, I was sure I have tried it :). Thanks for fast reply. On 14 Maj, 18:42, Michael Bayer [EMAIL PROTECTED] wrote: try two percent signs to escape it - %%. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Savepoints not generated with MySQL?
Hi,
I'm testing out the below script with MySQL 5.0.51a and SA 0.4.6.
Based on the documentation I expected to see a SAVEPOINT be created
(at some point) and rolled-back to but instead I get the following:
satest.doit()
2008-05-14 12:42:37,813 INFO sqlalchemy.engine.threadlocal.TLEngine.
0x..cc BEGIN
2008-05-14 12:42:37,814 INFO sqlalchemy.engine.threadlocal.TLEngine.
0x..cc INSERT INTO test_table (name) VALUES (%s)
2008-05-14 12:42:37,814 INFO sqlalchemy.engine.threadlocal.TLEngine.
0x..cc ['outer']
2008-05-14 12:42:37,815 INFO sqlalchemy.engine.threadlocal.TLEngine.
0x..cc INSERT INTO test_table (name) VALUES (%s)
2008-05-14 12:42:37,815 INFO sqlalchemy.engine.threadlocal.TLEngine.
0x..cc ['inner']
2008-05-14 12:42:37,816 INFO sqlalchemy.engine.threadlocal.TLEngine.
0x..cc COMMIT
Am I doing something wrong? I looked thru this mailing-list, bug
reports and documentation but couldn't see anything obvious.
Thanks!
-- Jacob
The script is:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('mysql://[EMAIL PROTECTED]/test', echo=True,
strategy='threadlocal')
meta = MetaData()
meta.bind = engine
test_table = Table('test_table', meta,
Column('test_id', Integer, primary_key=True),
Column('name', String(40)),
mysql_engine='InnoDB')
meta.create_all()
class MyTest(object):
def __init__(self, test_name):
self.name = test_name
mapper(MyTest, test_table)
Session = sessionmaker(transactional=False, autoflush=True,
bind=engine)
def doit():
sess = Session(transactional=False)
sess.begin()
t = MyTest(outer)
sess.save(t)
sess.flush()
def inner():
sess.begin_nested()
t2 = MyTest(inner)
sess.save(t2)
sess.rollback()
inner()
sess.commit()
if __name__ == '__main__':
doit()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Savepoints not generated with MySQL?
Hi,
Those changes fixed it! It's a pretty minor issue, but would it make
sense for SA to log a warning when you try to use begin_nested() in a
scenario that doesn't support it?
Also, is the 'threadlocal' mode likely ever to support
begin_nested()? I'm not sure I really will end up needing it, but I
was thinking I might need to mix in some 'implicit session' type code
at some point.
Thanks,
-- Jacob
On May 14, 1:12 pm, Michael Bayer [EMAIL PROTECTED] wrote:
two problems:
1. the threadlocal mode, not a widely used option, currently does
not support begin_nested() (which is somewhat of a surprise to me)
2. the inner() method issues no SQL. add a sess.flush() in there,
and do away with threadlocal to see it work.
On May 14, 2008, at 3:45 PM, Jacob Gabrielson wrote:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('mysql://[EMAIL PROTECTED]/test', echo=True,
strategy='threadlocal')
meta = MetaData()
meta.bind = engine
test_table = Table('test_table', meta,
Column('test_id', Integer, primary_key=True),
Column('name', String(40)),
mysql_engine='InnoDB')
meta.create_all()
class MyTest(object):
def __init__(self, test_name):
self.name = test_name
mapper(MyTest, test_table)
Session = sessionmaker(transactional=False, autoflush=True,
bind=engine)
def doit():
sess = Session(transactional=False)
sess.begin()
t = MyTest(outer)
sess.save(t)
sess.flush()
def inner():
sess.begin_nested()
t2 = MyTest(inner)
sess.save(t2)
sess.rollback()
inner()
sess.commit()
if __name__ == '__main__':
doit()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Savepoints not generated with MySQL?
On May 14, 2008, at 5:05 PM, Jacob Gabrielson wrote: Hi, Those changes fixed it! It's a pretty minor issue, but would it make sense for SA to log a warning when you try to use begin_nested() in a scenario that doesn't support it? yea I added an NotImplementedError for that in r4758 Also, is the 'threadlocal' mode likely ever to support begin_nested()? I'm not sure I really will end up needing it, but I was thinking I might need to mix in some 'implicit session' type code at some point. it can be done but we'd have to rework the internals of the Threadlocal module to work like the SessionTransaction. The Session is already doing pretty much the same work as the Threadlocal module in this case (i.e. a single point of transaction control with nesting). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: database definitions - was sqlalchemy migration/schema creation
On Fri, May 9, 2008 at 10:14 AM, [EMAIL PROTECTED] wrote:
On Friday 09 May 2008 16:32:25 Lukasz Szybalski wrote:
On Fri, May 9, 2008 at 4:46 AM, [EMAIL PROTECTED] wrote:
On Friday 09 May 2008 03:05, Lukasz Szybalski wrote:
Do you guys know what would give me column definition of table?
do u want it as generated source-text or what?
Yes. The Final output I would like is the txt version of db
definitions.
autoload --- sqlalchemy.Column('Address_Sid', sqlalchemy.Integer,
primary_key=True),
well, then see that one.
have a look at dbcook/dbcook/misc/metadata/autoload.py
at dbcook.sf.net
running 'python autoload.py dburl' will dump the db-metadata into
src-text,
I keep getting this error:
python autoload.py mysql://user:[EMAIL PROTECTED]/mytable
Traceback (most recent call last):
File autoload.py, line 204, in ?
assert 0, 'unsupported engine.dialect:'+str( engine.dialect)
AssertionError: unsupported
engine.dialect:sqlalchemy.databases.mysql.MySQLDialect object at
0x2af7f6f522d0
(ENV)[EMAIL PROTECTED]:~/tmp/metadata$ python autoload.py
mysql://root:[EMAIL PROTECTED]/mysql
mysql://root:[EMAIL PROTECTED]/mysql
Traceback (most recent call last):
File autoload.py, line 204, in ?
assert 0, 'unsupported engine.dialect:'+str( engine.dialect)
AssertionError: unsupported
engine.dialect:sqlalchemy.databases.mysql.MySQLDialect object at
0x2b11f2dcb2d0
Any ideas?
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
