[sqlalchemy] SQL_CALC_FOUND_ROWS and FOUND_ROWS(). how?
Hi everyone. How can I specify parameter SQL_CALC_FOUND_ROWS in SELECT query (I'm using mysql 5.0)? Is there any (engine-independant) solution to determine, how many rows where matched with whereclauses in complex select query? ResultProxy.rowcount holds the number of returned rows, limited by the LIMIT statement, it is not what I need. Thanks, sorry for my terrible english. ___ Всем привет. Как я могу передать select-запросу параметр SQL_CALC_FOUND_ROWS (использую mysql 5.0)? Есть может какой-то другой (быть может, даже движково-независимый) способ определения, сколько строк удовлетворяет условиям отбора в сложном запросе? В ResultProxy.rowcount лежит число отобранных записей, не более параметра LIMIT, это совсем не то, что мне нужно. Спасибо, прошу прощения за свой бестолковый анлгийский. --~--~-~--~~~---~--~~ 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: Explicit column in a SelectResults qry.
Glauco ha scritto: CUT The simplest example is to specify columns to select on a generated qry (not all field of all tables involved in the generated qry). does the sqlalchemy.ext.selectresults.SelectResults object have something like column clause parameter of select function? Thank's Glauco -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software® [EMAIL PROTECTED] Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054 ++ --~--~-~--~~~---~--~~ 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: Problems with select() :-(
Hi Jonathan and All, first of all, thank you for your answer. On 3/6/07, Jonathan Ellis wrote: it sounds like you inserted a bunch of new objects, but didn't flush, so select() doesn't see them. get() does see it because it checks the identity map before querying the db. Well, at the beginning this is what I thought, but I put flush() everywhere and I still get the same result. So, I thought to modify the byroot_tree.py demo in the examples/adjacencytree directory to show a couple of issues I have been fighting for 2 days. The problems you will see are: 1) No matter how/when/where I flush(), select returns always an empty list (see the attached file when you run the simple demo); 2) Using a physical database (not using an in-memory database), baffles me: in the attached file, at the bottom, just set the variable: newDataBase = True The first time to create the database: this just creates few random nodes and saves them into the database. Then, set: newDataBase = False To try to load the data from the database. I get an impossible error from the TreeLoader class: Traceback (most recent call last): File C:\Documents and Settings\gavana\Desktop\SQLAlchemy-0.3.5\SQLAlchemy-0.3 .5\examples\adjacencytree\byroot_tree_1.py, line 206, in module main() File C:\Documents and Settings\gavana\Desktop\SQLAlchemy-0.3.5\SQLAlchemy-0.3 .5\examples\adjacencytree\byroot_tree_1.py, line 203, in main engineclass = TheEngine(newDataBase) File C:\Documents and Settings\gavana\Desktop\SQLAlchemy-0.3.5\SQLAlchemy-0.3 .5\examples\adjacencytree\byroot_tree_1.py, line 157, in __init__ self.LoadNodes() File C:\Documents and Settings\gavana\Desktop\SQLAlchemy-0.3.5\SQLAlchemy-0.3 .5\examples\adjacencytree\byroot_tree_1.py, line 193, in LoadNodes parentnode = query.get(ii) File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 61, in get File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 376, in _get File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 384, in _select_sta tement File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 316, in execute File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 335, in instances File build\bdist.win32\egg\sqlalchemy\orm\mapper.py, line 1258, in _instance File build\bdist.win32\egg\sqlalchemy\orm\mapper.py, line 1439, in append_re sult File build\bdist.win32\egg\sqlalchemy\orm\mapper.py, line 1456, in _do File build\bdist.win32\egg\sqlalchemy\orm\mapper.py, line 1439, in append_re sult File build\bdist.win32\egg\sqlalchemy\orm\mapper.py, line 1456, in _do File C:\Documents and Settings\gavana\Desktop\SQLAlchemy-0.3.5\SQLAlchemy-0.3 .5\examples\adjacencytree\byroot_tree_1.py, line 91, in append_result parentnode = selectcontext.identity_map[mapper.identity_key(instance.parent_ id)] KeyError: (class '__main__.TreeNode', (1,), None) I know I am doing something really stupid, but I don't know how to fix it. I obviously need a physical database, not an in-memory, and I would like to be able to load the data after I saved them. I am sorry for my poor knowledge of SQLAlchemy, I just started. Thank you for your suggestions. Andrea. Imagination Is The Only Weapon In The War Against Reality. http://xoomer.virgilio.it/infinity77/ --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- a more advanced example of basic_tree.py. treenodes can now reference their root node, and introduces a new selection method which selects an entire tree of nodes at once, taking advantage of a custom MapperExtension to assemble incoming nodes into their correct structure. import os from sqlalchemy import * from sqlalchemy.util import OrderedDict class TreeData(object): def __init__(self, value=None): self.id = None self.value = value def __repr__(self): return TreeData(%s, %s) % (repr(self.id), repr(self.value)) class NodeList(OrderedDict): subclasses OrderedDict to allow usage as a list-based property. def append(self, node): self[node.name] = node def __iter__(self): return iter(self.values()) class TreeNode(object): a hierarchical Tree class, which adds the concept of a root node. The root is the topmost node in a tree, or in other words a node whose parent ID is NULL. All child nodes that are decendents of a particular root, as well as a root node itself, reference this root node. this is useful as a way to identify all nodes in a tree as belonging to a single identifiable root. Any node can return its root node and therefore the tree that it belongs to, and entire
[sqlalchemy] identity map not cleared upon session.clear()?
I currently have a setup of multiple table polymorphic inheritance which looks like A - B - C I insert items in C, then flush. If i select C items from A at that point everything is fine. But if i clear the session something odd happens, the session's identity map still has keys pointing to the instances I added before the flush but with some members now unititialized (specifically members of C). So when I then select C items from A i get the a list of C instances but with none of their members initialized, even though the actual select in the database yielded the correct values in the RowProxy. I'm using ActiveState Python 2.4 along with PySqlite and the latest SQLAlchemy egg. Thanks in advance, Mathieu Rouleau --~--~-~--~~~---~--~~ 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: Cannot connect to Oracle DB: AttributeError: 'module' object has no attribute 'NCLOB'
Thanks that solved the problem. Vineet On Mar 6, 6:34 pm, Michael Bayer [EMAIL PROTECTED] wrote: old bug, upgrade to sqlalchemy 0.3.5 On Mar 6, 2007, at 5:51 PM, vinjvinj wrote: I get the following error: File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 266, in execute File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 271, in execute_t ext File build\bdist.win32\egg\sqlalchemy\databases\oracle.py, line 326, in crea te_result_proxy_args AttributeError: 'module' object has no attribute 'NCLOB' When I try to connect to the oracle DB. I have cx_oracle module installed. Any ideas what I'm doing wrong. Thanks, VJ --~--~-~--~~~---~--~~ 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: Polymorphic collections / ticket #500
im not sure if i understand the use case youre describing ? I also stuck the previously mentioned flag in the trunk, the exception message will tell you about it. On Mar 6, 2007, at 11:08 PM, Rick Morrison wrote: Mike: I think I've seen a few requests here on the list over the past months for some kind of default or unspecified or other catch- all for the occasional one-off exception to what are otherwise polymorphic collections. I think it's actually a fairly common use- case. I'll try some of Simon's suggestions (thanks), but I think real support for something like this would make sense -- could you think about the issue a bit? Thanks, Rick On 3/6/07, Michael Bayer [EMAIL PROTECTED] wrote: yeah, i didnt like adding this particular error message, but since it leads to a bigger problem later i sort of had to. i am sure a lot of people are going to hit this one. so i guess ill put a flag in for this onei think people shoud me made aware that they are allowing a potentially error-causing behavior to occur. On Mar 5, 2007, at 8:12 PM, Rick Morrison wrote: The fix for ticket #500 breaks a pattern I've been using. It's most likely an anti-pattern, but I don't see a way to get what I want in SA otherwise. I've got a series of entities class Person(): pass class Manager(Person): def __init__(self): # do manager stuff class Demigod(Person): def __init__(self): # do demigod stuff etc. there are mappers for each of these entities that inherit from Person(), so all of the normal Person() properties exist, but Person () itself is not polymorphic. That's on purpose, and because the class hierarchy of Manager(), etc, is not exhaustive, and I occasionally want to save instances of Person() directly. If I make the Person() class polymorphic on a column of say typ, then SA clears whatever typ I may have tried to set directly, and seems to make me specify an exhaustive list of sub-types. And so I leave Person() as non-polymorphic. I also have a collection of Person() objects on a different mapper, which can load entity objects of any type. Before rev #2382, I could put a Manager() in a Person() collection, and it would flush OK. Now it bitches that it wants a real polymorphic mapper. I don't want to use a polymorphic mapper, because I don't want to specify an exhaustive list of every class that I'm ever going to use. What to do? Thanks, 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: SQL_CALC_FOUND_ROWS and FOUND_ROWS(). how?
you can trick it like this if you want. suppose your table has a column id in it: s = select([SQL_CALC_FOUND_ROWS id, sometable], from_obj=[sometable]) that or just use text(select SQL_CALC_FOUND_ROWS, foo, bar, lala from sometable) ill look into adding a prefix() construct for this kind of thing. On Mar 7, 2007, at 3:32 AM, [EMAIL PROTECTED] wrote: Hi everyone. How can I specify parameter SQL_CALC_FOUND_ROWS in SELECT query (I'm using mysql 5.0)? Is there any (engine-independant) solution to determine, how many rows where matched with whereclauses in complex select query? ResultProxy.rowcount holds the number of returned rows, limited by the LIMIT statement, it is not what I need. Thanks, sorry for my terrible english. ___ Всем привет. Как я могу передать select-запросу параметр SQL_CALC_FOUND_ROWS (использую mysql 5.0)? Есть может какой-то другой (быть может, даже движково-независимый) способ определения, сколько строк удовлетворяет условиям отбора в сложном запросе? В ResultProxy.rowcount лежит число отобранных записей, не более параметра LIMIT, это совсем не то, что мне нужно. Спасибо, прошу прощения за свой бестолковый анлгийский. --~--~-~--~~~---~--~~ 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
OK its looking like having it as positional might just be what they all commonly had in mind... On Mar 7, 2007, at 5:15 AM, Tim Golden wrote: 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
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: MSSQL identity inserts [was: pyodbc and tables with triggers]
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.
Rick
On 3/7/07, Tim Golden [EMAIL PROTECTED] wrote:
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] Re: fetchmany() vs sqlite
I filed a ticket with a patch: http://www.sqlalchemy.org/trac/ticket/505 JP On Mar 7, 11:17 am, Michael Bayer [EMAIL PROTECTED] wrote: OK its looking like having it as positional might just be what they all commonly had in mind... On Mar 7, 2007, at 5:15 AM, Tim Golden wrote: 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] MS Sql Server: Cannot create new connection because in manual or distributed
My first query works but the second query gets this error. I'm using the db_conn.execute function to execute direct sql. I also tried to create a session object and explicitly create a transaction and commit the transaction thinking that might solve the problem. However, that did not fix the problem. VJ Exception: adoRetVal=self.cmd.Execute() File COMObject ADODB.Command, line 3, in Execute File C:\Python24\Lib\site-packages\win32com\client\dynamic.py, line 258, in _ApplyTypes_ result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes ) + args) com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server', 'Cannot create new connection because in manual or distributed transaction mode.', None, 0, -2147467259), None) --~--~-~--~~~---~--~~ 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: identity map not cleared upon session.clear()?
so, brand new underlying UnitOfWork, brand new dictionary. Since i
dont understand the case you are describing (how do you select
items of class C from class A ? i dont understand what that means),
you might want to attach a reproducible test case.
okay just ran some tests and it happens when i have A-B-C-D
inheritance
I'm using multiple table polymorphic inheritance (as described in the
docs)
so i can do A.select() and get a list of A,B,C and D instances, which
works fine
except when i clear the session, instances of D have their members
unitialized
here's a sample test:
from sqlalchemy import *
metadata = BoundMetaData('sqlite:///')
class A(object):
def __init__(self, a):
self.a = a
def __repr__(self):
return 'A a=%s' % self.a
class B(A):
def __init__(self, a, b):
self.a = a
self.b = b
def __repr__(self):
return 'B a=%s b=%s' % (self.a, self.b)
class C(B):
def __init__(self, a, b, c):
self.a = a
self.b = b
self.c = c
def __repr__(self):
return 'C a=%s b=%s c=%s' % (self.a, self.b, self.c)
class D(C):
def __init__(self, a, b, c, d):
self.a = a
self.b = b
self.c = c
self.d = d
def __repr__(self):
return 'D a=%s b=%s c=%s d=%s' % (self.a, self.b, self.c,
self.d)
a_table = Table('a_table', metadata,
Column('id', Integer, primary_key=True),
Column('a', String(32)),
Column('type', String(30)))
b_table = Table('b_table', metadata,
Column('id', Integer, ForeignKey('a_table.id'),
primary_key=True),
Column('b', String(32)))
c_table = Table('c_table', metadata,
Column('id', Integer, ForeignKey('b_table.id'),
primary_key=True),
Column('c', String(32)))
d_table = Table('d_table', metadata,
Column('id', Integer, ForeignKey('c_table.id'),
primary_key=True),
Column('d', String(32)))
abcd_union = polymorphic_union(
{
'd':a_table.join(b_table).join(c_table).join(d_table),
'c':a_table.join(b_table).join(c_table),
'b':a_table.join(b_table),
'a':a_table.select(a_table.c.type=='a')
}, None)
bcd_union = polymorphic_union(
{
'd':a_table.join(b_table).join(c_table).join(d_table),
'c':a_table.join(b_table).join(c_table),
'b':a_table.join(b_table),
}, None)
cd_union = polymorphic_union(
{
'd':a_table.join(b_table).join(c_table).join(d_table),
'c':a_table.join(b_table).join(c_table),
}, None)
a_mapper = mapper(A, a_table, select_table=abcd_union,
polymorphic_on=abcd_union.c.type, polymorphic_identity='a')
b_mapper = mapper(B, b_table, select_table=bcd_union,
inherits=a_mapper, polymorphic_on=bcd_union.c.type,
polymorphic_identity='b')
c_mapper = mapper(C, c_table, select_table=cd_union,
inherits=b_mapper, polymorphic_on=cd_union.c.type,
polymorphic_identity='c')
mapper(D, d_table, inherits=c_mapper, polymorphic_identity='d')
a_table.create()
b_table.create()
c_table.create()
d_table.create()
session = create_session()
session.save(A(a='aaa'))
session.save(B(a='aaa',b='bbb'))
session.save(C(a='aaa',b='bbb',c='ccc'))
session.save(D(a='aaa',b='bbb',c='ccc',d='ddd'))
session.flush()
#comment this clear for success
session.clear()
l = session.query(A).select()
print l
l = session.query(D).select()
assert l[0].d == 'ddd'
--~--~-~--~~~---~--~~
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: MS Sql Server: Cannot create new connection because in manual or distributed
This link explains the problem: http://support.microsoft.com/default.aspx?scid=kb;en-us;272358 Unfortunately, I'm not sure what the solution is. I've already tried to create a transaction in a session. VJ On Mar 7, 2:05 pm, vinjvinj [EMAIL PROTECTED] wrote: My first query works but the second query gets this error. I'm using the db_conn.execute function to execute direct sql. I also tried to create a session object and explicitly create a transaction and commit the transaction thinking that might solve the problem. However, that did not fix the problem. VJ Exception: adoRetVal=self.cmd.Execute() File COMObject ADODB.Command, line 3, in Execute File C:\Python24\Lib\site-packages\win32com\client\dynamic.py, line 258, in _ApplyTypes_ result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes ) + args) com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server', 'Cannot create new connection because in manual or distributed transaction mode.', None, 0, -2147467259), None) --~--~-~--~~~---~--~~ 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: MS Sql Server: Cannot create new connection because in manual or distributed
This is an ADODBAPI /OLEDB issue. Solution is to either use client-side cursor for the first query, or to fetch all the results from the first query before issuing another one on another connection. Just so you know, there are others working on pyodbc for the MSSQL SA module, which I think (and hope) is not subject to this problem. Neither is pymssql. Rick On 3/7/07, vinjvinj [EMAIL PROTECTED] wrote: This link explains the problem: http://support.microsoft.com/default.aspx?scid=kb;en-us;272358 Unfortunately, I'm not sure what the solution is. I've already tried to create a transaction in a session. VJ On Mar 7, 2:05 pm, vinjvinj [EMAIL PROTECTED] wrote: My first query works but the second query gets this error. I'm using the db_conn.execute function to execute direct sql. I also tried to create a session object and explicitly create a transaction and commit the transaction thinking that might solve the problem. However, that did not fix the problem. VJ Exception: adoRetVal=self.cmd.Execute() File COMObject ADODB.Command, line 3, in Execute File C:\Python24\Lib\site-packages\win32com\client\dynamic.py, line 258, in _ApplyTypes_ result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes ) + args) com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server', 'Cannot create new connection because in manual or distributed transaction mode.', None, 0, -2147467259), None) --~--~-~--~~~---~--~~ 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: identity map not cleared upon session.clear()?
changing the poly unions to look like this fixes it:
abcd_union = polymorphic_union(
{
'd':a_table.join(b_table).join(c_table).join(d_table),
'c':a_table.join(b_table).join(c_table).select
(a_table.c.type==c),
'b':a_table.join(b_table).select(a_table.c.type==b),
'a':a_table.select(a_table.c.type=='a')
}, None)
oooh i get it, the 'c' and 'b' joins were also joining with the b and
c rows that were inherited by d... right?
i shouldve though of that... but thats what you get for working late
into the night :(
thanks!
someday ill be brave enough to build these poly unions into the core
engine...they have come a long way in recent months but i still feel
like we're still learning about their quirks (hence i force everyone
to learn about them :) ).
well by the time I wrap this up in SQLElixir you won't have to :)
--~--~-~--~~~---~--~~
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: MS Sql Server: Cannot create new connection because in manual or distributed
I figured the problem out: I was doing the following: rows_from_sql1 = db_conn.execute(select1) rows_from_sql2 = db_conn.execute(select2) You can't do the above since rows_from_sql1 is an iterator. You have to iterate through all the rows before you can execute the second query. I can potentially see certain situations where you would have to be able to execute two queries before you iterate through them all. Are there any work around this? Thanks, VJ On Mar 7, 4:05 pm, Rick Morrison [EMAIL PROTECTED] wrote: This is an ADODBAPI /OLEDB issue. Solution is to either use client-side cursor for the first query, or to fetch all the results from the first query before issuing another one on another connection. Just so you know, there are others working on pyodbc for the MSSQL SA module, which I think (and hope) is not subject to this problem. Neither is pymssql. Rick On 3/7/07, vinjvinj [EMAIL PROTECTED] wrote: This link explains the problem: http://support.microsoft.com/default.aspx?scid=kb;en-us;272358 Unfortunately, I'm not sure what the solution is. I've already tried to create a transaction in a session. VJ On Mar 7, 2:05 pm, vinjvinj [EMAIL PROTECTED] wrote: My first query works but the second query gets this error. I'm using the db_conn.execute function to execute direct sql. I also tried to create a session object and explicitly create a transaction and commit the transaction thinking that might solve the problem. However, that did not fix the problem. VJ Exception: adoRetVal=self.cmd.Execute() File COMObject ADODB.Command, line 3, in Execute File C:\Python24\Lib\site-packages\win32com\client\dynamic.py, line 258, in _ApplyTypes_ result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes ) + args) com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server', 'Cannot create new connection because in manual or distributed transaction mode.', None, 0, -2147467259), None) --~--~-~--~~~---~--~~ 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: sa newbie
thanks!
On 3/6/07, Jonathan Ellis [EMAIL PROTECTED] wrote:
try
db.books.select(db.books.c.book_skus.like('abcd%'))
--~--~-~--~~~---~--~~
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] www.OutpatientSurgicare.com/video/
www.OutpatientSurgicare.com/video/ Outpatient Doctors Surgery Center is committed to offering the healthcare the community needs. We offer patients a meaningful alternative to traditional surgery. This state-of-the-art outpatient surgery center, located in the heart of Orange County, at 10900 Warner Avenue, Suite 101A, Fountain Valley, Ca 92708, offers the latest innovations in outpatient surgery and technology. Please Call For Our Special Cash Discount Toll Free: 1-877-500-2525 Please Visit Our Websites: We offer extreme cosmetic surgery makeover packages. http://www.SurgeonToTheStars.com http://www.1cosmeticsurgery.com Specializing in the cure of hyperhidrosis, sweaty palms, underarm and foot sweating. http://www.CuresweatyPalms.com http://www.ControlExcessiveSweating.com No. 1 Weight Loss Surgery Center http://www.ControlWeightLossNow.com http://www.FreeLapBandSeminar.com Hernia Treatment Center http://www.HerniaDoc.com Take care of your feet http://www.CureFootPain.com The Experts in CARPAL TUNNEL SYNDROME http://www.CureHandPain.com Accidental Urine Leaks ? End Urinary Incontinence http://www.WomanWellnessCenter.com Hemorrhoid Treatment Center http://www.hemorrhoidtreatmentcenter.com --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
