[sqlalchemy] Re: DB Redundancy
On May 7, 4:15 pm, goo...@venix.com goo...@venix.com wrote: MySQL has a mechanism for a database to read the log from a master database and replay the commands. This provides a loose coupling with near real-time backup of the data. Should the backup server stop or lose contact, the primary server is unaffected. When the backup server regains contact, it restarts the log processing from the point where it left off. MySQL log based replication is asynchronous. It's only useful if losing some transactions in case of a crash isn't a problem. --~--~-~--~~~---~--~~ 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] session.begin_nested() (=Savepoints) on SQLite
Hi, I'm currently trying to use savepoints on SQLite, however, there are some problems with it which I don't understand. The following traceback occurs quite often: - snip -- File /home/dusty/prog/bsp/buildout/src/bsp/bsp/tests/__init__.py, line 156, in checkbooking session.rollback() File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/session.py, line 649, in rollback self.transaction.rollback() File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/session.py, line 404, in rollback transaction._rollback_impl() File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/orm/session.py, line 415, in _rollback_impl t[1].rollback() File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py, line 1028, in rollback self._do_rollback() File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py, line 1068, in _do_rollback self.connection._rollback_to_savepoint_impl(self._savepoint, self._parent) File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py, line 763, in _rollback_to_savepoint_impl self.engine.dialect.do_rollback_to_savepoint(self, name) File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/default.py, line 113, in do_rollback_to_savepoint connection.execute(expression.RollbackToSavepointClause(name)) File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py, line 824, in execute return Connection.executors[c](self, object, multiparams, params) File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py, line 874, in _execute_clauseelement return self.__execute_context(context) File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py, line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py, line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File /home/dusty/prog/bsp/buildout/eggs/SQLAlchemy-0.5.3-py2.4.egg/sqlalchemy/engine/base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) OperationalError: (OperationalError) no such savepoint: sa_savepoint_1 u'ROLLBACK TO SAVEPOINT sa_savepoint_1' [] - snip -- The very same code works well with SAPDB, so I'm unsure if the reason for that lies in my code. Any hints? Best Regards, Hermann -- herm...@qwer.tk GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7 --~--~-~--~~~---~--~~ 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: ForeignKey schema and Table schema
Sorry, I missed an example in my first message. The problem arises when you try to query across two databases: Session.query(MainUser).join((OtherUser, OtherUser.id == MainUser.id)) Would normally products something like: SELECT * FROM MainUser INNER JOIN OtherUser ON OtherUser.id = MainUser.id When you really need the schema for the table that is in the other database: SELECT * FROM MainUser INNER JOIN other.OtherUser ON OtherUser.id = MainUser.id On May 7, 9:46 am, Michael Bayer mike...@zzzcomputing.com wrote: Aculeus wrote: This has a severe problem having to hard set the schema when that value should be part of configuration. Instead the table should assume the schema of the engine that it's metadata is bound to and automatically appear in queries where there is a table from a different schema than the one the query is being ran through. if your engine() connects using a certain schema as the default schema, then no explicit schema argument is necessary for tables that are accessed by that engine within that schema. schema is only used when accessing a non-default schema from a single engine. --~--~-~--~~~---~--~~ 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] How to run a stored procedure?
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
Thanks
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to run a stored procedure?
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to run a stored procedure?
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ForeignKey schema and Table schema
Aculeus wrote: Sorry, I missed an example in my first message. The problem arises when you try to query across two databases: Session.query(MainUser).join((OtherUser, OtherUser.id == MainUser.id)) Would normally products something like: SELECT * FROM MainUser INNER JOIN OtherUser ON OtherUser.id = MainUser.id When you really need the schema for the table that is in the other database: SELECT * FROM MainUser INNER JOIN other.OtherUser ON OtherUser.id = MainUser.id If i understand correctly, you'd like a single Table object to dynamically change its schema based on which engine its used with. The only way to achieve something like this is to make a copy of the table against a different schema using table.tometadata(someothermetadata, schema='someschema'). Tables do not assume to be associated with any one engine, so your feature request of the Table automatically setting its schema to the default schema of some particular engine is not possible. If you'd like to achieve this yourself, create a Table function of your own: def Table(*args, **kw): kw['schema'] = someschema return sqlalchemy.schema.Table(*args, **kw) --~--~-~--~~~---~--~~ 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: How to run a stored procedure?
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases, running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to run a stored procedure?
On mssql, when I run the query, either calling it by name or calling
EXEC and then the procedure name, it returns the result. The problem
is that if I try to call result.fetchall(), it give this error:
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
I know that when I execute the query in the MSSQL environment it
returns a result set, but sqlalchemy doesn't seem to get the result
set.
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases, running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to run a stored procedure?
Daniel wrote:
On mssql, when I run the query, either calling it by name or calling
EXEC and then the procedure name, it returns the result. The problem
is that if I try to call result.fetchall(), it give this error:
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
I know that when I execute the query in the MSSQL environment it
returns a result set, but sqlalchemy doesn't seem to get the result
set.
its again a DBAPI interaction issue which either some of the MSSQL folks
here could chime in to help with or if you could figure out how to get
what you want from a raw pyodbc connection
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases,
running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use
it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as
follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm
not
sure how to access the return values? They exactly match one of
the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to run a stored procedure?
Michael,
I'm not sure if this helps, but I've noticed that if my stored
procedure returns all null values, then I can fetch them. If they are
non-null values I get an error:
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
1668, in fetchone
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
(None, None, None, None, None)
Any idea why this would be?
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases, running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to run a stored procedure?
I've just been looking through the code in mssql.py and the change
mentioned in the changeset I mentioned isn't there anymore. I also
can't see that's it's been abstracted to a parent class. Is there a
possibility that this bug has crept back in?
If so, let me know where the sensible place would be to include the
EXEC keyword in order to return result sets for MSSQL stored
procedures, or if there would be a better approach.
Thanks.
On May 8, 11:24 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I just found this
thread:http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html
which corresponds to this
changeset:http://www.sqlalchemy.org/trac/changeset/4159
It seems that this issue has come up in the past. I've tried the
following modified query:
result = conn.execute('EXEC claim_highest_priority_work')
which should satisfy the regexp, but it still produces the closed
cursor error.
Not sure if this helps you help me...
On May 8, 11:09 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I'm not sure if this helps, but I've noticed that if my stored
procedure returns all null values, then I can fetch them. If they are
non-null values I get an error:
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
1668, in fetchone
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
(None, None, None, None, None)
Any idea why this would be?
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases, running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Building an or_ filter in loop
I can't figure out a clean way of adding a bunch of filter terms to a
query in a loop joined by an OR clause. Successive calls to filter
join the expressions by AND. I would like to do something like the
following, but have the expressions joined by OR
terms = ['apple', 'orange', 'peach']
q = Session.query(Fruit)
for term in terms:
q = q.filter(Fruit.name.like('%' + term + '%')
Desired pseudo-sql:
SELECT * FROM fruit WHERE name like '%apple%' OR name like '%orange%'
OR name like '%peach%'
--~--~-~--~~~---~--~~
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: Building an or_ filter in loop
On Fri, 8 May 2009 12:52:09 -0700 (PDT)
Bryan bryanv...@gmail.com wrote:
I can't figure out a clean way of adding a bunch of filter terms to a
query in a loop joined by an OR clause. Successive calls to filter
join the expressions by AND. I would like to do something like the
following, but have the expressions joined by OR
terms = ['apple', 'orange', 'peach']
q = Session.query(Fruit)
for term in terms:
q = q.filter(Fruit.name.like('%' + term + '%')
Desired pseudo-sql:
SELECT * FROM fruit WHERE name like '%apple%' OR name like '%orange%'
OR name like '%peach%'
I think this might do what you want:
cond = or_(*[ Fruit.name.like('%' + term + '%') for term in terms ])
q = Session.query(Fruit).filter(cond)
-Kyle
--~--~-~--~~~---~--~~
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: Building an or_ filter in loop
That worked, thanks
On May 8, 12:59 pm, Kyle Schaffrick k...@raidi.us wrote:
On Fri, 8 May 2009 12:52:09 -0700 (PDT)
Bryan bryanv...@gmail.com wrote:
I can't figure out a clean way of adding a bunch of filter terms to a
query in a loop joined by an OR clause. Successive calls to filter
join the expressions by AND. I would like to do something like the
following, but have the expressions joined by OR
terms = ['apple', 'orange', 'peach']
q = Session.query(Fruit)
for term in terms:
q = q.filter(Fruit.name.like('%' + term + '%')
Desired pseudo-sql:
SELECT * FROM fruit WHERE name like '%apple%' OR name like '%orange%'
OR name like '%peach%'
I think this might do what you want:
cond = or_(*[ Fruit.name.like('%' + term + '%') for term in terms ])
q = Session.query(Fruit).filter(cond)
-Kyle
--~--~-~--~~~---~--~~
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: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
If you change the test case to indicate that supports_unicode and supports_unicode_statements = False, then it runs just fine with the fix. Without the fix it fails as well, which indicates to me the issue is in FreeTDS. I'd like to commit this but I want to have a discussion with Mike Bayer first to be sure he's okay with it. I had a discussion with Mike Bayer and he expressed that he was uncomfortable committing a hack that just hides the problem instead of figuring out and fixing the problem properly. As we got into the code we began to question some of the design choices surrounding that bit of code, specifically the use of the ; select scope_identity() part. I spent quite a bit of time last night digging into the whole issue and here are my findings. First I removed the pyodbc specific code and just tried to use the base dialect code which doesn't do the ; select scope_identity() hack but instead actually calls a separate execute in the post_exec to get the identity value. This resulted in returning None values every time. I thought it was an issue with pyodbc since they indicate so in their documentation, but it turns out a raw pyodbc script produces the correct results. I finally discovered that the reason we're getting None in this case is do to the prepared queries. Basically the prepared query is in a different scope than the post_exec so it can't get the identity value. Changing this to not use scope_identity but to use @@identity works properly. Now clearly that's not the desired solution since that will be affected by triggers, etc..., and likely the reason for the odd implementation we see of ; select scope_identity. This ensured that the identity was retrieved in the same scope, prepared statement, as the initial insert. I say all the above just more for reference documentation and not as a solution to the problem. Once I got passed the identity issue I was able to get back to the initial Invalid Cursor State problem. After lots of traces it's clear that this cursor problem is a result of something that FreeTDS is doing when retrieving the identity, but only in the case of a transaction. The problem is related to the fact that in those cases the cursor is returning more than one result. That particular cursor error occurs when you try to select an identity but have not fully selected al the results from the cursor. The perplexing part is that the return value of the second result is always None which indicates that there are no more results. Here's a breakdown of what I saw: 1. fetchall()[0] - this will cause the problem to go away as indicated above because it's fully selecting all results before the identity is retrieved. 2. fetchone; fetchone() - if I add two fetchone() statements it will also cause the problem to go away. This clearly indicates that there is a second result. 3. session.commit() - Adding a session.commit() following each insert also causes the problem to go away. So clearly it's being influenced by the open transaction. I proved this by writing raw pyodbc outside of a transaction which worked fine. So the end result of all this is that I know the situation under which it's happening, I'm pretty confident the problem is related to FreeTDS and transactions (it doesn't happen on pure pyodbc on Windows at all), but I don't know the actual statement causing it nor the proper solution. I've also been unable to produce a pure pyodbc script that reproduces this problem, but I haven't explored everything there. I did find this thread where Rick Morrison identified the same problem. http://markmail.org/message/z4egbaof35j67dgt#query:+page:1+mid:z4egbaof35j67dgt+state:results If Rick has more information about this I'd love to hear it. That thread then went off in another direction. So at this point we don't have a solution. If we decide to get rid of the ; select scope_identity() business then that opens us up to identity problems where triggers are involved. The work around at this point is to commit following each insert. Michael --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
