[sqlalchemy] Re: pyodbc and tables with triggers
Sorry for the delay ;-) No objections. I was just trying to figure out
what was happening. (didn't get at first that module selection is a
bit ugly also meant that it didn't work if you specified the module)
But you already have a patch it seems. I'll try and test it out.
Steven
On 15 mrt, 15:43, Rick Morrison [EMAIL PROTECTED] wrote:
Sorry, Stephen, I replied too early; your second email arrived before the
first. A whole day before the first.
So until we get a real cleanup, you're looking to try modules in this order:
['pyodbc', 'adodbapi', 'pymssql']
Sounds OK to me -- any objections out there?
Rick
On 3/14/07, Rick Morrison [EMAIL PROTECTED] wrote:
It's the second case, that is, it sniffs out what modules are installed.
As I said before, this
(along with other modules that effectively do the same thing), is up for a
clean-up soon, see ticket #480.
Rick
On 3/14/07, polaar [EMAIL PROTECTED] wrote:
{'pyodbc': use_pyodbc, 'adodbapi': use_adodbapi, 'pyodbc':
use_pyodbc}.get(module.__name__, use_default)()
Sorry, should be pymssql instead of pyodbc twice, but I guess you got
that...
--~--~-~--~~~---~--~~
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: pyodbc and tables with triggers
Tim, I committed a patch from ticket #480 today from that adds some improved module-switching code to the MSSQL interface. See how that works for you. On 3/16/07, Tim Golden [EMAIL PROTECTED] wrote: Rick Morrison wrote: Sorry, Stephen, I replied too early; your second email arrived before the first. A whole day before the first. So until we get a real cleanup, you're looking to try modules in this order: ['pyodbc', 'adodbapi', 'pymssql'] Sounds OK to me -- any objections out there? Looks good to me. I got slightly confused somewhere through this thread. When I was putting a test together for the passthrough patch, I ended up using an Import hook to force a particular dbapi module to be used programatically (given that I have all three installed). Obviously there are variations on that (manually renaming one etc) but have I missed anything more sophisticated using SA itself? Didn't look like it to me from the code. TJG --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
Rick Morrison wrote: Tim, I committed a patch from ticket #480 today from that adds some improved module-switching code to the MSSQL interface. See how that works for you. I'll have a look when I get near an MSSQL-connected machine (tomorrow earliest). Thanks v. much. TJG --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
Rick Morrison wrote: Sorry, Stephen, I replied too early; your second email arrived before the first. A whole day before the first. So until we get a real cleanup, you're looking to try modules in this order: ['pyodbc', 'adodbapi', 'pymssql'] Sounds OK to me -- any objections out there? Looks good to me. I got slightly confused somewhere through this thread. When I was putting a test together for the passthrough patch, I ended up using an Import hook to force a particular dbapi module to be used programatically (given that I have all three installed). Obviously there are variations on that (manually renaming one etc) but have I missed anything more sophisticated using SA itself? Didn't look like it to me from the code. TJG --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
I don't think you missed anything; that more sophisticated approach is exactly what we're discussing. Seeing as how you have all three modules installed and switch between them, you probably have your own ideas about how it should work. Please pick up the discussion on ticket #480 and give us your input. Rick Rick Morrison wrote: Sorry, Stephen, I replied too early; your second email arrived before the first. A whole day before the first. So until we get a real cleanup, you're looking to try modules in this order: ['pyodbc', 'adodbapi', 'pymssql'] Sounds OK to me -- any objections out there? Looks good to me. I got slightly confused somewhere through this thread. When I was putting a test together for the passthrough patch, I ended up using an Import hook to force a particular dbapi module to be used programatically (given that I have all three installed). Obviously there are variations on that (manually renaming one etc) but have I missed anything more sophisticated using SA itself? Didn't look like it to me from the code. TJG --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
Sorry, Stephen, I replied too early; your second email arrived before the
first. A whole day before the first.
So until we get a real cleanup, you're looking to try modules in this order:
['pyodbc', 'adodbapi', 'pymssql']
Sounds OK to me -- any objections out there?
Rick
On 3/14/07, Rick Morrison [EMAIL PROTECTED] wrote:
It's the second case, that is, it sniffs out what modules are installed.
As I said before, this
(along with other modules that effectively do the same thing), is up for a
clean-up soon, see ticket #480.
Rick
On 3/14/07, polaar [EMAIL PROTECTED] wrote:
{'pyodbc': use_pyodbc, 'adodbapi': use_adodbapi, 'pyodbc':
use_pyodbc}.get(module.__name__, use_default)()
Sorry, should be pymssql instead of pyodbc twice, but I guess you got
that...
--~--~-~--~~~---~--~~
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: pyodbc and tables with triggers
On 12 mrt, 21:47, polaar [EMAIL PROTECTED] wrote:
FYI, specifying module=pyodbc didn't seem to help wrt the
ConcurrentModificationError. Didn't have very much time, had a (very)
quick look at the code in mssql.py, and at first sight, it would seem
that sane_rowcount is a global variable that is only set in the
use_pyodbc() (resp. adodbapy/pymssql) function, which in turn is only
called from use-default(), this would seem to mean only when you don't
specify a module... Either I'm completely wrong (which is very well
possible ;-), as I said, I only took a quick look, and I'm not
familiar with the code), or this means that you may not have adodbapi
(or pymssql) installed in order to use pyodbc correctly???
Update: it indeed seems to work like that. I tried changing the order
of preference in mssql.py so that it first tries pydobc, and that
seems to work: the ConcurrentModificationError no longer occurs. I now
also get a warning about using pyodbc that I didn't get before. (by
the way: I did have to keep the 'set nocount on' in order to prevent
the invalid cursor state problem)
I guess something could be done with changing the following line from
the __init__ method of class MSSQLDialect:
self.module = module or dbmodule or use_default()
to something that calls use_pyodbc/use_pymssql/use_adodbapi based on
module.__name__? (I'm not sure though: use_default seems to be called
already when the mssql is imported and it sets the global dbmodule, so
I'm not confident that this is where it should be done*)
Something like this?
{'pyodbc': use_pyodbc, 'adodbapi': use_adodbapi, 'pyodbc':
use_pyodbc}.get(module.__name__, use_default)()
Steven
* can't test it at home (using linux), and as using python at work is
mostly 'under the radar', I can't spend a lot of time on it there, so
sorry if I can't provide you with a well-tested patch ;-)
--~--~-~--~~~---~--~~
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: pyodbc and tables with triggers
It's the second case, that is, it sniffs out what modules are installed.
As I said before, this
(along with other modules that effectively do the same thing), is up
for a clean-up soon, see ticket #480.
Rick
On 3/14/07, polaar [EMAIL PROTECTED] wrote:
{'pyodbc': use_pyodbc, 'adodbapi': use_adodbapi, 'pyodbc':
use_pyodbc}.get(module.__name__, use_default)()
Sorry, should be pymssql instead of pyodbc twice, but I guess you got
that...
--~--~-~--~~~---~--~~
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: pyodbc and tables with triggers
FYI, specifying module=pyodbc didn't seem to help wrt the ConcurrentModificationError. Didn't have very much time, had a (very) quick look at the code in mssql.py, and at first sight, it would seem that sane_rowcount is a global variable that is only set in the use_pyodbc() (resp. adodbapy/pymssql) function, which in turn is only called from use-default(), this would seem to mean only when you don't specify a module... Either I'm completely wrong (which is very well possible ;-), as I said, I only took a quick look, and I'm not familiar with the code), or this means that you may not have adodbapi (or pymssql) installed in order to use pyodbc correctly??? On 9 mrt, 23:29, polaar [EMAIL PROTECTED] wrote: Yes, but I'm starting to think I'm doing something wrong ;-) I suppose I should call create_engine with the module=pyodbc? I was just using the creator argument (as I was doing already because I needed to change the connectionstring to use integrated security anyway), and just switched that from adodbapi to pyodbc. So maybe it's still using the default adodbapi settngs... Hmm, seems to make sense... oops... (well, it's not really clear from the docs that this is used for anything else than determining which module to use to create the connection, which seems unnecessary if you create it yourself) I'll try it on monday... On 9 mrt, 22:08, Rick Morrison [EMAIL PROTECTED] wrote: This is still with pyodbc? The MSSQL module should already set sane_rowcount to False for that dialect, as per the pyodbc site, they don't implement rowcount. Rick --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
I needed to change the connectionstring to use integrated security anyway), FWIW if someone were to be able to review / commit my patch on ticket 488 (http://www.sqlalchemy.org/trac/ticket/488) the integrated security would be there anyway. Haven't got round to patching the SCOPE_IDENTITY stuff yet. TJG --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
Module selection in MSSQL is a bit ugly right now. Mike has proposed a clean-up of the way that DB-API modules are loaded and used, so this will get better soon, I hope. I'll have a look at the patch. Rick On 3/9/07, polaar [EMAIL PROTECTED] wrote: Yes, but I'm starting to think I'm doing something wrong ;-) I suppose I should call create_engine with the module=pyodbc? I was just using the creator argument (as I was doing already because I needed to change the connectionstring to use integrated security anyway), and just switched that from adodbapi to pyodbc. So maybe it's still using the default adodbapi settngs... Hmm, seems to make sense... oops... (well, it's not really clear from the docs that this is used for anything else than determining which module to use to create the connection, which seems unnecessary if you create it yourself) I'll try it on monday... On 9 mrt, 22:08, Rick Morrison [EMAIL PROTECTED] wrote: This is still with pyodbc? The MSSQL module should already set sane_rowcount to False for that dialect, as per the pyodbc site, they don't implement rowcount. Rick --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
Hmmm, seems the set nocount on trick now causes problems on deletes: ConcurrentModificationError is thrown because Updated rowcount -1 does not match number of objects updated 1. Which seems strange because I thought rowcount -1 simply meant that the count cannot be determined, not that there is something wrong. There seems to be a supports_sane_rowcount check (for MySQL according to the docs), wouldn't it make sense to treat a rowcount of -1 the same? Or should one just never use set nocount on when using the orm (which would mean back to the original problem)? Steven On Mar 7, 5:28 pm, polaar [EMAIL PROTECTED] wrote: On Mar 7, 3:29 pm, Tim Golden [EMAIL PROTECTED] wrote: code from sqlalchemy import * metadata = BoundMetaData (mssql://VODEV1/TimHolding) test = Table (test, metadata, autoload=True) result = test.insert ().execute (code = ABC) print result.last_inserted_ids () # = [1] /code which is what I expected. If I explicitly set NOCOUNT OFF for my session (in case it's on by default) using: metadata.engine.raw_connection ().execute (SET NOCOUNT OFF) then it still works. Is my case the situation you're describing? Or have I misunderstood somthing? My fault: I forgot to tell you that I was using a mapped class, and it's the sqlalchemy-generated 'select @@identity' that causes the problem. (you can see that it does that in the log output) --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
This is still with pyodbc? The MSSQL module should already set sane_rowcount to False for that dialect, as per the pyodbc site, they don't implement rowcount. Rick On 3/9/07, polaar [EMAIL PROTECTED] wrote: Hmmm, seems the set nocount on trick now causes problems on deletes: ConcurrentModificationError is thrown because Updated rowcount -1 does not match number of objects updated 1. Which seems strange because I thought rowcount -1 simply meant that the count cannot be determined, not that there is something wrong. There seems to be a supports_sane_rowcount check (for MySQL according to the docs), wouldn't it make sense to treat a rowcount of -1 the same? Or should one just never use set nocount on when using the orm (which would mean back to the original problem)? Steven On Mar 7, 5:28 pm, polaar [EMAIL PROTECTED] wrote: On Mar 7, 3:29 pm, Tim Golden [EMAIL PROTECTED] wrote: code from sqlalchemy import * metadata = BoundMetaData (mssql://VODEV1/TimHolding) test = Table (test, metadata, autoload=True) result = test.insert ().execute (code = ABC) print result.last_inserted_ids () # = [1] /code which is what I expected. If I explicitly set NOCOUNT OFF for my session (in case it's on by default) using: metadata.engine.raw_connection ().execute (SET NOCOUNT OFF) then it still works. Is my case the situation you're describing? Or have I misunderstood somthing? My fault: I forgot to tell you that I was using a mapped class, and it's the sqlalchemy-generated 'select @@identity' that causes the problem. (you can see that it does that in the log output) --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
polaar wrote:
I recently tried out sqlalchemy with mssql via pyodbc (after being
bitten by the adodbapi bug with the truncated parameters), and noticed
the following problem:
On inserting records into tables with triggers, pyodbc fails on the
'select @@identity as lastrowid' statement with an 'invalid cursor
state' error.
OK, I can't reproduce this (and there's a follow-on issue which
I'll pick up later). Just to clarify, I have this structure
compiled on the database:
db
IF OBJECT_ID ('test_audit') IS NOT NULL
DROP TABLE test_audit
GO
IF OBJECT_ID ('test') IS NOT NULL
DROP TABLE test
GO
CREATE TABLE
test
(
id INT NOT NULL IDENTITY PRIMARY KEY,
code VARCHAR (10) NOT NULL UNIQUE
)
GO
CREATE TABLE
test_audit
(
test_id INT NOT NULL FOREIGN KEY REFERENCES test (id),
inserted_on DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
inserted_by VARCHAR (60) NOT NULL DEFAULT SYSTEM_USER
)
GO
CREATE TRIGGER tr_test_i ON test FOR INSERT AS
INSERT INTO test_audit (test_id) SELECT id FROM inserted
GO
/db
That's a main table (test) an audit table (test_audit)
into which test-INSERTs are triggered. Now, in sqlalchemy:
code
from sqlalchemy import *
metadata = BoundMetaData (mssql://VODEV1/TimHolding)
test = Table (test, metadata, autoload=True)
result = test.insert ().execute (code = ABC)
print result.last_inserted_ids ()
# = [1]
/code
which is what I expected. If I explicitly set NOCOUNT OFF
for my session (in case it's on by default) using:
metadata.engine.raw_connection ().execute (SET NOCOUNT OFF)
then it still works.
Is my case the situation you're describing? Or have I
misunderstood somthing?
TJG
--~--~-~--~~~---~--~~
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: pyodbc and tables with triggers
On Mar 7, 3:29 pm, Tim Golden [EMAIL PROTECTED] wrote: code from sqlalchemy import * metadata = BoundMetaData (mssql://VODEV1/TimHolding) test = Table (test, metadata, autoload=True) result = test.insert ().execute (code = ABC) print result.last_inserted_ids () # = [1] /code which is what I expected. If I explicitly set NOCOUNT OFF for my session (in case it's on by default) using: metadata.engine.raw_connection ().execute (SET NOCOUNT OFF) then it still works. Is my case the situation you're describing? Or have I misunderstood somthing? My fault: I forgot to tell you that I was using a mapped class, and it's the sqlalchemy-generated 'select @@identity' that causes the problem. (you can see that it does that in the log output) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
