[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 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: fetchmany() vs sqlite
On Mar 6, 11:37 pm, Michael Bayer [EMAIL PROTECTED] wrote: OK who can try Mysql, MS-SQL, Oracle, Firebird for me ? while it seems like it should work positionally for all of them, it sort of seems it should work keyword-wise as well if the DBAPI authors actually read the spec they were developing for. MSSQL with pyodbc gives: Traceback (most recent call last): File stdin, line 1, in ? File c:\work_in_progress\sqlalchemy\lib\sqlalchemy\engine\base.py, line 982, in fetchmany rows = self.cursor.fetchmany(size=size) TypeError: fetchmany() takes no keyword arguments MSSQL with adodbapi works ok MSSQL with pymssql works ok The pyodbc cursor.fetchmany *does* allow the rowcount as a positional argument: code from sqlalchemy import * db = create_engine (MSSQL://VODEV1/DEV) q = db.raw_connection ().cursor () q.execute (SELECT * FROM wb_parcels) q.fetchmany (2) # [pyodbc.Row object at 0x00A7A728, pyodbc.Row object at 0x00A7A368] /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
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] MSSQL identity inserts [was: pyodbc and tables with triggers]
I've looked through the mailing list and can't see
this issue raised there so...
There is a known issue with retrieving the id of the
last inserted row under MSSQL where IDENTITY cols are
used and there are triggers involved. It's pretty easy
to demonstrate. If I have this construction:
db
CREATE TABLE
test
(
id INT NOT NULL IDENTITY PRIMARY KEY,
code VARCHAR (10) NOT NULL UNIQUE
)
GO
CREATE TABLE
test_audit
(
id INT NOT NULL IDENTITY (100, 1) PRIMARY KEY,
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
and insert something into test:
db
INSERT INTO test (code) VALUES ('ABC')
SELECT @@IDENTITY
SELECT * FROM test
/db
The last id is 100 (the IDENTITY col from [test_audit])
while, as far as the user's concerned, [test] was the
only table affected. In sqlalchemy terms, this means
that the last_inserted_ids () could return misleading
values:
python
from sqlalchemy import *
db = BoundMetaData (mssql://VODEV1/TimHolding)
test = Table (test, db, autoload=True)
r = test.insert ().execute (code=DEF)
print r.last_inserted_ids ()
# = [101]
list (test.select ().execute (id=101))
# = []
/python
What are the alternatives? Well, there are two:
IDENT_CURRENT ('tablename')
gives the last identity value assigned to this table
*in any session* (alertrace condition/alert)
or
SCOPE_IDENTITY ()
which seems to be what we're after here; it's like @@IDENTITY
but for the same scope (not a widely-used term in SQL
Server circles, as far as I know). The documentation
specifically gives this case as an example.
Looks to me like this would be the best bet for sqlalchemy's
purposes, but I'm sure there's a downside somewhere ;)
Comments?
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: MSSQL identity inserts [was: pyodbc and tables with triggers]
Rick Morrison wrote: OK, I replied to the other thread already, which is really the same issue. See my response there about backward-compatibility. At any rate, we could make it a connection variable like auto_identity_insert. Patches welcome. I'm happy to provide a patch. Not sure about the connection variable. Ah, I see, you mean because of backwards compat. But isn't the problem that if we just leave the @@IDENTITY as now, it's a danger waiting to happen, especially if the returned id happens to be a valid id for the table you *think* it's for? Not really sure what to offer here: 1) I can provide a patch, replacing @@IDENTITY by SCOPE_IDENTITY throughout. 2) I can provide a patch allowing connection-level determination of whether @@IDENTITY or SCOPE_IDENTITY is to be used. (Which assumes the client module knows what that's about). 3) I can provide a patch which attempts to work out which one would be allowed from some DB context (or just trying it to see!) The problem with (1) is that, if Rick's right, it won't work with MSSQL = 7. The problem with any of (2) or (3) where @@IDENTITY ends up being used is that we might be silently and dangerously returning wrong data. 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] MSSQL Trusted Connections?
I've gone through the archives, but there doesn't appear to have been any discussion on the use of trusted connections to MSSQL servers. (aka NT authentication, passthrough etc.) If I've overlooked a previous discussion and am rehashing things then please do point it out. Of the three db interfaces on offer (pymssql, adodbapi, pyodbc) only the latter two offer the possibility of a trusted connection. I looked at the code of pymssql a while ago and it doesn't do anything useful with a null username (which is the way in which Object Craft's now-stale MSSQL module handles passthrough). The ADO ODBC connections use those mildly unwieldy connection strings which do at least allow for NT authentication. Assuming there were to be some agreement on the matter, I'd be quite happy to offer a patch. But I'm not sure whether the preference would be for an implicit trusted connection (pass an empty username) or for an explicit option on the create_engine (trusted_connection=True or whatever). Picking up briefly on comments by Rick Morrison -- to whom many thanks for making the MSSQL stuff happen -- it is a shame that there's no easy answer to the question: What do I use with Python to access MS SQL Server?. I used the Object Craft module for years until they effectively stopped releases for newer versions. I occasionally fall back to Py2.3 just to use it as it's the most robust of the options. pyodbc looks good but lacks .nextset support, which is central to a widely used (internally) sql2xl library I knocked up years ago. adodbapi does offer this but has been unmaintained for a while and is just a little flaky. pymssql is maintained but uses the deprecated db-lib interface (as does the Object Craft offering) and doesn't handle Unicode especially well, at least on Windows. With all that, then, I'm very glad that sqlalchemy gives me the choice! 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: MSSQL Trusted Connections?
Paul Johnston wrote: I think having a null user name imply a trusted connection would be fine. If you want to progress this, I suggest you create a ticket and supply a patch to do this, at least for adodbapi and pyodbc. Will do. As for What do I use with Python to access MS SQL Server? - I agree with your concerns, but there is light at the end of the tunnel. I think pretty soon pyodbc will be the clear winner. I suggest you raise a ticket on their tracker for the nextset() issue. Good idea. Thanks 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 -~--~~~~--~~--~--~---
