[sqlalchemy] Re: MSSQL, pyodbc linux
Hi, eng = sqlalchemy.create_engine (mssql:///?dsn=mydsn,UID=myusername,PWD=mypass,module=pyodbc) Try this: eng = sqlalchemy.create_engine (mssql://myusername:mypass@/?dsn=mydsn,module=pyodbc) Paul --~--~-~--~~~---~--~~ 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, pyodbc linux
On Tue, Mar 25, 2008 at 9:21 AM, Paul Johnston [EMAIL PROTECTED] wrote: Hi, eng = sqlalchemy.create_engine(mssql:///?dsn=mydsn,UID=myusername,PWD=mypass,module=pyodbc) Try this: eng = sqlalchemy.create_engine(mssql://myusername:mypass@/?dsn=mydsn,module=pyodbc) Still the same error. File /usr/lib/python2.4/site-packages/sqlalchemy/pool.py, line 111, in create_connection return _ConnectionRecord(self) File /usr/lib/python2.4/site-packages/sqlalchemy/pool.py, line 149, in __init__ self.connection = self.__connect() File /usr/lib/python2.4/site-packages/sqlalchemy/pool.py, line 174, in __connect connection = self.__pool._creator() File /usr/lib/python2.4/site-packages/sqlalchemy/engine/strategies.py, line 57, in connect raise exceptions.DBAPIError(Connection failed, e) sqlalchemy.exceptions.DBAPIError: (Connection failed) (TypeError) function takes at least 1 argument (0 given) Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: post-populate extension (2)
we have a new hook that will be coming soon called something like __reconstitute__(), which will be called on instances after they've been created and had their initial population from the result row. Note that eagerly-loaded collections might not be fully loaded at this stage. at the moment the populate_instance() hook is what works in the current version of 0.4. On Mar 25, 2008, at 7:05 AM, Nebur wrote: I'm going to post-populate an instance, and there's a thread which is exactly about my question but fairly old: http://groups.google.de/group/sqlalchemy/browse_thread/thread/7467f8e9d86b1749/ Is there a post_populate hook in the current SA 0.4.x, or is Ricks way (using populate_instance) still the best ? Ruben --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to specify owner.table.column
On Mar 25, 2008, at 10:45 AM, vkuznet wrote: Hi Mike, now everything seems to work fine. Many thanks to you and all others who contribute to the threads and solving the problem. But, there is a but. The schema is present everywhere, in Table and in Table object of the column and foreign keys, however the resulting queries seems slightly mixed with table.column and schema.table.column when I used foreign keys and their parent/column. Here is example: SELECT DISTINCT primarydataset.name AS primarydataset_name FROM cms_dbs_prod_global.processeddataset JOIN cms_dbs_prod_global.block ON processeddataset.id = block.dataset JOIN cms_dbs_prod_global.primarydataset ON processeddataset.primarydataset = primarydataset.id WHERE block.path LIKE :block_path_1 here cms_dbs_prod_global is a schema, while processeddataset, primarydataset, block are tables I think in some cases SQLAlchemy uses name and in another fullname while doing joins. thats current defined behavior; the schema is used only when a table is evaulated in a FROM context, not in a column qualification context.its easy enough to change but I'd want to ensure that no current databases break when all columns are qualified with schema + tablename. do you actually have a case where identical tablenames from different schemas are conflicting ? --~--~-~--~~~---~--~~ 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] Sqlalchemy connection pool question
Hello, I am using sqlalchemy 4.0 in my application to connect to mysql database. I am running quries through session object which is instantiated as a (bind=engine, Transactional = True). I am not using threadlocal strategy. My question is that If I open 10 connection simultaneouslty then checked-out connection will be equal to 10. Now if I call commit method then 10 connections will be returned to the pool. Now if my application exits then pool will internally close these connections from MYSQL SERVER or not? Thanks. -- Best Regards, Ahmad --~--~-~--~~~---~--~~ 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: Sqlalchemy connection pool question
On Mar 25, 2008, at 1:36 PM, Ahmad Hassan wrote: Hello, I am using sqlalchemy 4.0 in my application to connect to mysql database. I am running quries through session object which is instantiated as a (bind=engine, Transactional = True). I am not using threadlocal strategy. My question is that If I open 10 connection simultaneouslty then checked-out connection will be equal to 10. Now if I call commit method then 10 connections will be returned to the pool. Now if my application exits then pool will internally close these connections from MYSQL SERVER or not? when an application exits, all existing database connections are closed unconditionally. Theres no remaining process left to maintain the socket connection. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to specify owner.table.column
Great, I was just wondering. But glad to see confirmation. Yes we do have a use case when the same tablenames cause weird behavior in ORACLE. Below is a message from our DBA in response to hick-up when we occasionally got: ORA-00942: table or view does not exist error. Valentin. -- MESSAGE from DBA Unfortunately it seems that your application is affected by the bug 5686711 described in the note 5686711.8 on Metalink: Bug 5686711 Wrong cursor may be executed if schemas have objects with same names Description A session may use the wrong copy of a shared cursor and hence access / update data in the wrong schema if objects have the same names in different schemas and users have permissions on the other schemas objects. Workaround: To avoid the problem always prefix objects with the schema name. If this should be seen then flush the shared pool so that cursors get rebuilt from scratch. What happens in your case is the following: 1. you have many copies of your schema inside the CMSR database 2. you use different reader, writer and admin accounts to access different schemas. 3. you use synonyms in your queries so the text of SQL statements executed from different accounts is exactly the same. 4. Oracle confuses cashed cursors created by different users and sometimes tries to execute a wrong one 5. since you grant select privileges on your tables only to relevant reader, writer and admin accounts, Oracle returns ORA-00942 during an attempt to execute this erroneously picked up cursor. The fix for the bug is included in the patchset 10.2.0.4 which hopefully will be applied on CMSR before data challenges in May. The problem disappeared several minutes ago because I have flushed the shared pool as advised in the workaround section of the Metalink ink note. As it is not for the first time that Oracle has bugs leading to cursor confusion I would strongly recommend that you modify your queries to use fully qualified names (schema name prefixes) instead of using synonyms. --~--~-~--~~~---~--~~ 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: using limit w/ distinct on ORACLE (revise bug #536)
Ok, here is an example in ORACLE.
Table schema (for simplicity I removed unnecessary columns):
CREATE TABLE Block
(
IDinteger,
Name varchar(500) unique not null,
Path varchar(500) not null,
primary key(ID)
);
So the path column can contains repeated values. Here is the code
using limit/offset:
t = self.getTable(dbAlias,'Block','tblk')
sel =
sqlalchemy.select([t.c.path],from_obj=[t],distinct=True,limit=10,offset=0)
print sel
res = con.execute(sel)
for item in res:
print result from Block,item
and here is result:
SELECT path
FROM (SELECT DISTINCT tblk.path AS path, ROW_NUMBER() OVER (ORDER BY
tblk.id) AS ora_rn
FROM cms_dbs_int_global.block tblk)
WHERE ora_rn0 AND ora_rn=10
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW',)
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW',)
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW',)
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW',)
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW',)
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW',)
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW',)
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW',)
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW',)
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW',)
It does not select 10 different distinct paths.
While if I'll do
sel = sqlalchemy.select([t.c.path],from_obj=[t],distinct=True)
tmp = sel.alias('tmp')
q = sqlalchemy.select(['tmp.*','rownum as
rnum'],from_obj=[tmp])
sel = sqlalchemy.select(['*'],from_obj=[q])
sel.append_whereclause( 'rnum between %s and %s'%(0,10) )
print sel
res = con.execute(sel)
for item in res:
print result from Block,item
I'll get 10 different distinct paths:
SELECT *
FROM (SELECT tmp.*, rownum as rnum
FROM (SELECT DISTINCT tblk.path AS path
FROM cms_dbs_int_global.block tblk) tmp)
WHERE rnum between 0 and 10
result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN-
SIM-DIGI-RAW', 1)
result from Block ('/GammaJetIsoPi0_Pt55to65_ptHat60/CMSSW_1_6_7-
CSA07-4067/GEN-SIM-DIGI-RAW', 2)
result from Block ('/LM3_isasdkpyt/CMSSW_1_6_5-FastSim-SUSYBSM-1234/
AODSIM', 3)
result from Block ('/LM7_isasdkpyt/CMSSW_1_6_5-FastSim-SUSYBSM-1234/
AODSIM', 4)
result from Block ('/LM6_isasdkpyt/CMSSW_1_6_5-FastSim-SUSYBSM-1234/
AODSIM', 5)
result from Block ('/RS1GravitonZZ4Mu_1500GeV_01/CMSSW_1_6_7-
CSA07-3199/GEN-SIM-DIGI-RAW', 6)
result from Block ('/SingleMuPlusPt100To400/CMSSW_1_6_7-HLT-1193394942/
GEN-SIM-DIGI-RECO', 7)
result from Block ('/LM2_isasdkpyt/CMSSW_1_6_5-FastSim-SUSYBSM-1234/
AODSIM', 8)
result from Block ('/LM8_isasdkpyt/CMSSW_1_6_5-FastSim-SUSYBSM-1234/
AODSIM', 9)
result from Block ('/RS1GravitonZZ4Mu_1500GeV_01/CMSSW_1_4_6-
CSA07-2644/GEN-SIM', 10)
Valentin.
--~--~-~--~~~---~--~~
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: using limit w/ distinct on ORACLE (revise bug #536)
My research shows that ROW_NUMBER() OVER (ORDER BY somecol) is the standard SQL approach to producing the LIMIT/OFFSET functions, which is from this article: http://troels.arvin.dk/db/rdbms/#select-limit I used to use the select rownum directly approach you've outlined below, but the three levels of subquery are less than ideal and I have vague recollections of other issues arising with rownum not always being available, coming out as NULL, etc. What I need for the ticket is an example which illustrates LIMIT/ OFFSET passing for a DB which supports simple LIMIT/OFFSET keywords such as SQLite, but fails for our Oracle approach. On Mar 25, 2008, at 2:51 PM, vkuznet wrote: Hi, I reported a bug #536 almost a year ago, but looks like it didn't catch too much attention within new 0.4 branch. I want to revise it. The problem only occur in ORACLE instance where someone want to do pagination and select distinct rows in chunks. The correct way to do this in ORACLE is the following: select * from (SELECT x.*, rownum as rnum FROM (query) x) where rnum between min and max; For those who are interesting this link provides full discussion. http://progcookbook.blogspot.com/2006/02/using-rownum-properly-for-pagination.html Thank you, Valentin. --~--~-~--~~~---~--~~ 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: using limit w/ distinct on ORACLE (revise bug #536)
Yes this works too. So you'll accept/fix the bug :)? Thanks a lot Valentin. --~--~-~--~~~---~--~~ 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: using limit w/ distinct on ORACLE (revise bug #536)
can you update the ticket please with a summary of this ? also #999 is your schema issue. On Mar 25, 2008, at 5:03 PM, vkuznet wrote: Yes this works too. So you'll accept/fix the bug :)? Thanks a lot Valentin. --~--~-~--~~~---~--~~ 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] Spaces in Column Names (MSSQL)
(sqlalchemy 0.4.4/pymssql/windows/pylons)
Our MS SQL legacy database has column names with spaces and sometimes
other special characters. I'm able to get sqlalchemy to work with it
by having every column object include quote=True, and by having the
mapper() call include:
,properties= {'NameJammed':TableObj.c['Name Jammed'],
'AnotherCol':TableObj.c['Another (Col)']} #etc
I can use .NameJammed to access the column in my code just fine.
While I can do this, it's unfortunate under the DRY (Don't Repeat
Yourself) philosophy, as we have a long mapper call for each table
with each column name in there essentially twice (besides the initial
definition.)
Mysteries:
1) the key= parameter on the Column() declaration doesn't seem to have
any effect. orm/__init__.py's mapper function says under the
'properties' parameter:
Note that the columns in the mapped table are automatically converted
into ``ColumnProperty`` instances based on the `key` property of each
``Column`` (although they can be overridden using this dictionary)
2) Column's quote=True should not be required according to schema.py/
Column/__init__ 'quote' parameter:
indicates that the Column identifier must be properly escaped and
quoted before being sent to the database. This flag should normally
not be required as dialects can auto-detect conditions where quoting
is required
Perhaps I need to update mssql.py to override the right methods, which
is pretty intimidating.
Can you confirm that, for your database, column names with spaces are
automatically quoted (no quote=True required) and that Column's key=
works to give a python-identifier name to that column name (no mapper
properties required)?
Any other illumination? I'd love to have a function auto-generate the
column name without spaces identifier that I use in my code, too,
for even less Repeating Myself.
--~--~-~--~~~---~--~~
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] using persisted objects in a new session
hi all, I am a newbie with sqlalchemy so bear with me,this is what i am trying to do my app has a client and server part on the client i am retreiving data using sqlalchemy and transfering the query objects on to the server this goes fine but when i try to open a new session on the server and try to save the object transfered from the client after making certain changes to it nothing happens. what could i be doing wrong. TIA --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
