[sqlalchemy] how to make unique constrain within ORM
Hi,
I created ORM classes and can't find out a way to make an
UniqueConstraint for two columns. Do we have an example elsewhere?
When I used UniqueConstraint from sqlachemy.schema inside of ORM class
it does nothing, so it's not defined in a table. In particular here is
my class
class DASMap(Base):
DASMap ORM
__tablename__ = 'dasmaps'
__table_args__ = {'mysql_engine':'InnoDB'}
id = Column(Integer, primary_key=True)
system_id = Column(Integer, ForeignKey('systems.id'))
api_id = Column(Integer, ForeignKey('apis.id'))
daskey_id = Column(Integer, ForeignKey('daskeys.id'))
primary_key = Column(String(30), nullable=False, unique=True)
system = relation(System, order_by=System.id)
api = relation(Api, order_by=Api.id)
daskey = relation(DASKey, order_by=DASKey.id)
UniqueConstraint('api_id', 'daskey_id', name='uix_1')
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: loading tables is very slow
/local/lib/python2.4/
site-packages/sqlalchemy/engine/base.py:993(reflecttable)
10.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/queue.py:185(_full)
20.0000.0000.0000.000 /opt/local/Library/
Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_compile.py:
469(isstring)
10.0000.0000.0000.000 /opt/local/Library/
Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
178(__init__)
10.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/schema.py:1939(_bind_or_error)
10.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/pool.py:362(checkout)
10.0000.0000.0000.000 /opt/local/Library/
Frameworks/Python.framework/Versions/2.4/lib/python2.4/UserDict.py:
19(__delitem__)
30.0000.0000.0000.000 /opt/local/Library/
Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
126(__len__)
10.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/pool.py:230(get_connection)
10.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/engine/base.py:1531(__iter__)
10.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/queue.py:189(_put)
20.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/schema.py:279(_export_columns)
10.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/engine/threadlocal.py:12(__init__)
10.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/engine/threadlocal.py:31(_conn_closed)
10.0000.0000.0000.000 /opt/local/Library/
Frameworks/Python.framework/Versions/2.4/lib/python2.4/threading.py:
143(_is_owned)
10.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/schema.py:1418(bind)
10.0000.0000.0000.000 /opt/local/Library/
Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
67(__init__)
10.0000.0000.0000.000 /opt/local/lib/python2.4/
site-packages/sqlalchemy/queue.py:181(_empty)
10.0000.0000.0000.000 /opt/local/Library/
Frameworks/Python.framework/Versions/2.4/lib/python2.4/weakref.py:
53(__getitem__)
00.000 0.000 profile:0(profiler)
On Aug 27, 5:16 pm, Michael Bayer [EMAIL PROTECTED] wrote:
I have never observed 5 seconds to reflect a single table even with
Oracle which tends to be the slowest for schema-level operations.
The numbers you posted are too coarse grained to indicate where the
time is being spent, and if it were within SA reflection code then
these kinds of speed issues would be apparent across all dialects.
Can you post some profile results for your tests ?
On Aug 27, 2008, at 3:57 PM, vkuznet wrote:
Hi,
I've been trying to profile why loading a single table from remote
ORACLE DB takes more then 5 seconds.
Here is some numbers:
create engine 0.0740728378296
engine.connect 2.05604815483
SELECT table_name FROM all_tables WHERE owner='ZZZ'
get tables 0.18466091156
Loading 'triggerpathdescription' table
5.85890698433
Load tables manually ['triggerpathdescription']
load table 5.85950708389
It looks like SQLAlchemy takes 25 fetchone calls in order to autoload
table, but I tweak a code for ORACLE engine to use fetchmany and just
slighly reduced the total time. To me it's WAY to long and as far as I
can tell it internals of building foreign-key relationships. So I
wonder if there is a way to speed up such auto load.
I'm attaching a simple test code below.
Thanks,
Valentin.
#!/usr/bin/env python
import sqlalchemy,types,sys,os,time
def loadTables(tableNames):
for tName in tableNames:
t = tName[0].lower().split(.)[-1]
print Loading '%s' table%t
t1=time.time()
tables
[t
]=
sqlalchemy
.Table
(t
,dbMeta
,autoload=True,schema=schema,oracle_renyms=True,useexisting=True)
print time.time()-t1
break
print Load tables manually,tables.keys()
schema = 'ZZZ'
t1 = time.time()
engine = sqlalchemy.create_engine('oracle://
XXX:[EMAIL PROTECTED]',strategy='threadlocal',threaded=True)
print create engine,time.time()-t1
t1 = time.time()
con = engine.connect()
print engine.connect,time.time()-t1
t1 = time.time()
tables={}
dbMeta = sqlalchemy.MetaData()
dbMeta.bind = engine
idx = 0
query=SELECT table_name FROM all_tables WHERE owner='%s'%schema
print query
tableNames=con.execute(query)
print get tables,time.time()-t1
t1 = time.time()
loadTables(tableNames)
print load table,time.time()-t1
t1 = time.time
[sqlalchemy] loading tables is very slow
Hi,
I've been trying to profile why loading a single table from remote
ORACLE DB takes more then 5 seconds.
Here is some numbers:
create engine 0.0740728378296
engine.connect 2.05604815483
SELECT table_name FROM all_tables WHERE owner='ZZZ'
get tables 0.18466091156
Loading 'triggerpathdescription' table
5.85890698433
Load tables manually ['triggerpathdescription']
load table 5.85950708389
It looks like SQLAlchemy takes 25 fetchone calls in order to autoload
table, but I tweak a code for ORACLE engine to use fetchmany and just
slighly reduced the total time. To me it's WAY to long and as far as I
can tell it internals of building foreign-key relationships. So I
wonder if there is a way to speed up such auto load.
I'm attaching a simple test code below.
Thanks,
Valentin.
#!/usr/bin/env python
import sqlalchemy,types,sys,os,time
def loadTables(tableNames):
for tName in tableNames:
t = tName[0].lower().split(.)[-1]
print Loading '%s' table%t
t1=time.time()
tables[t]=sqlalchemy.Table(t,dbMeta,autoload=True,schema=schema,oracle_renyms=True,useexisting=True)
print time.time()-t1
break
print Load tables manually,tables.keys()
schema = 'ZZZ'
t1 = time.time()
engine = sqlalchemy.create_engine('oracle://
XXX:[EMAIL PROTECTED]',strategy='threadlocal',threaded=True)
print create engine,time.time()-t1
t1 = time.time()
con = engine.connect()
print engine.connect,time.time()-t1
t1 = time.time()
tables={}
dbMeta = sqlalchemy.MetaData()
dbMeta.bind = engine
idx = 0
query=SELECT table_name FROM all_tables WHERE owner='%s'%schema
print query
tableNames=con.execute(query)
print get tables,time.time()-t1
t1 = time.time()
loadTables(tableNames)
print load table,time.time()-t1
t1 = time.time()
--~--~-~--~~~---~--~~
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] method to retrieve selectable columns
Hi, is there any way to ask select object what is suppose to select? There is a method locate_all_froms which return FROM part of select, but I'm interesting in select part of SQL statement. So, something like: s = select([table1.c.a,table2.c.b]) listOfSelectedColumns = s.get_selectable_columns() which will return set or list of [table1.c.a,table2.c.b]. Thanks, 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: method to retrieve selectable columns
it doesn't seem to work for me, I just did two print statements print type(query) print query.inner_columns() and got the following traceback class 'sqlalchemy.sql.expression.Select' Traceback (most recent call last): print query.inner_columns() TypeError: 'generator' object is not callable On May 21, 12:21 pm, Michael Bayer [EMAIL PROTECTED] wrote: calling inner_columns() on the select() returns what actually gets rendered. The exported columns, i.e. those which you'd use when using the select() as a subquery, are accessible via the .c. attribute on the select() which has a dictionary interface. On May 21, 2008, at 12:01 PM, vkuznet wrote: Hi, is there any way to ask select object what is suppose to select? There is a method locate_all_froms which return FROM part of select, but I'm interesting in select part of SQL statement. So, something like: s = select([table1.c.a,table2.c.b]) listOfSelectedColumns = s.get_selectable_columns() which will return set or list of [table1.c.a,table2.c.b]. Thanks, 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] query on stdout
Hi, I just noticed that both 0.3.x and 0.4.x versions of SQLAlchemy print compiled query for MySQL without binded parameters, so typical printout for MySQL looks like SELECT DISTINCT block.`Path` AS `block_Path` FROM tier0.block WHERE block.`Path` LIKE %s while doing the same with ORACLE SELECT DISTINCT block.path AS block_path FROM block WHERE block.path LIKE :block_path Is there are any reason not to print binded parameters for MySQL or it is a bug? Thanks 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: 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)
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] how to add whereclause string
Hi,
I'm developing a web application where users are allowed to specify
where statement. So I can capture it as a string, e.g.
( T1.C1=1 OR T1.C1=5) AND T3.C3 like 'test%'
where T1 is table 1 and C1 is column 1, and so on. Now the hard
question is how to add such string to sqlalchemy query? I've look at a
code and one possible way I see is to created a TextClause with binded
parameters. But even if I can, the hard part would be to preserve
brackets and AND/OR between different conditions. So, it should be a
way to substitue Table.Column with sqlalchemy.Column (which I can do),
and rvalues with binded names, and create a dict of binded parameters,
and just pass a string like
( T1.C1 = :param1 OR T1.C1 = :param2 ) AND T3.C3 like :param3
{'param1':1, 'param2':5,'param3':'test%'}
to given query
Is there any way to address this issue. I'll be glad if someone will
give me some guidelines.
Thanks,
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: how to add whereclause string
Regarding attack,
I'm doing read-only application using read-only account, so insert/
updates are forbidden to users, as well as I'm not passing blindly
such query. As I said only where clause is exposed to the user not
query itself. And such where clause in my hand before I'm passing it
to
underneath query, so whereclause validation is in place.
Thanks for reference.
Valentin.
On Apr 19, 1:20 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 19, 2007, at 12:37 PM, vkuznet wrote:
Hi,
I'm developing a web application where users are allowed to specify
where statement. So I can capture it as a string, e.g.
( T1.C1=1 OR T1.C1=5) AND T3.C3 like 'test%'
where T1 is table 1 and C1 is column 1, and so on. Now the hard
question is how to add such string to sqlalchemy query? I've look at a
code and one possible way I see is to created a TextClause with binded
parameters. But even if I can, the hard part would be to preserve
brackets and AND/OR between different conditions.
wouldnt they be typing the brackets? those can be in the text()
clause too. but just FYI, this database will be 1000% wide open for
SQL injection attacks. i would even call it an injection, just an
anyone can type anything attack... :)
So, it should be a
way to substitue Table.Column with sqlalchemy.Column (which I can do),
and rvalues with binded names, and create a dict of binded parameters,
and just pass a string like
( T1.C1 = :param1 OR T1.C1 = :param2 ) AND T3.C3 like :param3
{'param1':1, 'param2':5,'param3':'test%'}
to given query
Is there any way to address this issue. I'll be glad if someone will
give me some guidelines.
if I were building this application, id parse the string into an
expression tree and have the tree generate a ClauseElement from
that. looks likehttp://pyparsing.wikispaces.com/even includes a
SQL parsing example, so there you go.
--~--~-~--~~~---~--~~
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: AS for Oracle
Thank you Mike,
but I'm out of ideas with my problem, the resulting query return non-
zero result in sqlplus prompt, but SQLAlchemy
return me 0. So, what I'm looking for is number of found rows.
SELECT count(DISTINCT tad.name)
FROM analysisdataset tad LEFT OUTER JOIN processeddataset tprd ON
tad.processedds = tprd.id LEFT OUTER JOIN procdstier tpds ON
tpds.dataset = tprd.id LEFT OUTER JOIN primarydataset tpm ON
tprd.primarydataset = tpm.id LEFT OUTER JOIN block tblk ON
tblk.dataset = tprd.id LEFT OUTER JOIN analysisdstype tadt ON tad.type
= tadt.id LEFT OUTER JOIN analysisdsstatus tads ON tad.status =
tads.id LEFT OUTER JOIN analysisdsdef tadd ON tad.definition = tadd.id
LEFT OUTER JOIN physicsgroup tpg ON tad.physicsgroup = tpg.id LEFT
OUTER JOIN person tp1 ON tad.createdby = tp1.id LEFT OUTER JOIN person
tp2 ON tad.lastmodifiedby = tp2.id
WHERE tad.name IS NOT NULL ORDER BY tp2.lastmodifiedby DESC
and my result=select().execute()
looks like this
{'engine': sqlalchemy.engine.threadlocal.TLEngine object at
0xb6e1698c, 'dialect': sqlalchemy.databases.oracle.OracleDialect
object at 0xb6e166cc, '_ResultProxy__echo': False,
'_ResultProxy__key_cache': {}, 'keys': ['name)'],
'_ResultProxy__executioncontext':
sqlalchemy.databases.oracle.OracleExecutionContext object at
0xb6ab398c, 'cursor': sqlalchemy.pool._CursorFairy object at
0xb6ab39cc, 'rowcount': 0, 'connection':
sqlalchemy.engine.base.Connection object at 0xb6a9324c, 'closed':
False, 'props': {0: (NullTypeEngine(), 0), 'name)': (NullTypeEngine(),
0)}, 'columns': {'name':
Column('name',OracleString(length=500),nullable=False)}}
Any ideas, what's wrong?
Valentin.
On Apr 14, 5:25 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 14, 2007, at 3:14 PM, vkuznet wrote:
Hi,
I'm trying to debug what's going on with ORACLE queries and found the
following. The SQLAlchemy constructs queries in a form:
select tad.name from Table AS tad;
but if I'll place this query directly into sqlplus it complains with
the following error:
ORA-00933: SQL command not properly ended (complain exactly on
keywords AS)
from sqlalchemy.databases import oracle
str(select(...).compile(dialect=oracle.dialect()))
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to [EMAIL PROTECTED]
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: AS for Oracle
Sorry for spam,
it's a bug in my program.
Thanks for your support and prompt responses.
Valentin.
On Apr 14, 8:46 pm, vkuznet [EMAIL PROTECTED] wrote:
Thank you Mike,
but I'm out of ideas with my problem, the resulting query return non-
zero result in sqlplus prompt, but SQLAlchemy
return me 0. So, what I'm looking for is number of found rows.
SELECT count(DISTINCT tad.name)
FROM analysisdataset tad LEFT OUTER JOIN processeddataset tprd ON
tad.processedds = tprd.id LEFT OUTER JOIN procdstier tpds ON
tpds.dataset = tprd.id LEFT OUTER JOIN primarydataset tpm ON
tprd.primarydataset = tpm.id LEFT OUTER JOIN block tblk ON
tblk.dataset = tprd.id LEFT OUTER JOIN analysisdstype tadt ON tad.type
= tadt.id LEFT OUTER JOIN analysisdsstatus tads ON tad.status =
tads.id LEFT OUTER JOIN analysisdsdef tadd ON tad.definition = tadd.id
LEFT OUTER JOIN physicsgroup tpg ON tad.physicsgroup = tpg.id LEFT
OUTER JOIN person tp1 ON tad.createdby = tp1.id LEFT OUTER JOIN person
tp2 ON tad.lastmodifiedby = tp2.id
WHERE tad.name IS NOT NULL ORDER BY tp2.lastmodifiedby DESC
and my result=select().execute()
looks like this
{'engine': sqlalchemy.engine.threadlocal.TLEngine object at
0xb6e1698c, 'dialect': sqlalchemy.databases.oracle.OracleDialect
object at 0xb6e166cc, '_ResultProxy__echo': False,
'_ResultProxy__key_cache': {}, 'keys': ['name)'],
'_ResultProxy__executioncontext':
sqlalchemy.databases.oracle.OracleExecutionContext object at
0xb6ab398c, 'cursor': sqlalchemy.pool._CursorFairy object at
0xb6ab39cc, 'rowcount': 0, 'connection':
sqlalchemy.engine.base.Connection object at 0xb6a9324c, 'closed':
False, 'props': {0: (NullTypeEngine(), 0), 'name)': (NullTypeEngine(),
0)}, 'columns': {'name':
Column('name',OracleString(length=500),nullable=False)}}
Any ideas, what's wrong?
Valentin.
On Apr 14, 5:25 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 14, 2007, at 3:14 PM, vkuznet wrote:
Hi,
I'm trying to debug what's going on with ORACLE queries and found the
following. The SQLAlchemy constructs queries in a form:
select tad.name from Table AS tad;
but if I'll place this query directly into sqlplus it complains with
the following error:
ORA-00933: SQL command not properly ended (complain exactly on
keywords AS)
from sqlalchemy.databases import oracle
str(select(...).compile(dialect=oracle.dialect()))
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to [EMAIL PROTECTED]
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] count and distinct
Hi, I found that if I do select([func.count(table.c.column)],distinct=True).execute() the resulting query is select distinct count(column) from table but it's not what I wanted. If my column has duplicates you got counting them, rather then count unique names. The proper SQL query would be select count(distinct column) from table How I can make it with SQLAlchemy syntax? Thanks, Valentin. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] 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] weird ORACLE behaviour with limit/offset
Hi, I added to my query the limit and offset (using ORACLE). To my surprise results ARE varying if I'll print my select or not before executing query. What I mean is the following sel = select () #print sel sel.execute() so, if I will not print my select, I'll get *smaller* number of results wrt if I'll uncomment my print statement. As I said this behaviour only appears if I add limit/offset to my select query using ORACLE. The query is quite complex, but here is what I see when I enable print statement: SELECT tblk_path FROM (SELECT DISTINCT tblk.path AS tblk_path, ROW_NUMBER() OVER (ORDER BY tblk.path DESC) AS ora_rn FROM processeddataset tprd LEFT OUTER JOIN block tblk ON tblk.dataset = tprd.id LEFT OUTER JOIN procdstier tpds ON tpds.dataset = tprd.id LEFT OUTER JOIN primarydataset tpm ON tprd.primarydataset = tpm.id LEFT OUTER JOIN seblock tseb ON tseb.blockid = tblk.id LEFT OUTER JOIN storageelement tse ON tseb.seid = tse.id LEFT OUTER JOIN procalgo tpal ON tpal.dataset = tprd.id LEFT OUTER JOIN algorithmconfig talc ON tpal.algorithm = talc.id LEFT OUTER JOIN appexecutable tape ON talc.executablename = tape.id LEFT OUTER JOIN appversion tapv ON talc.applicationversion = tapv.id LEFT OUTER JOIN appfamily tapf ON talc.applicationfamily = tapf.id LEFT OUTER JOIN physicsgroup tpg ON tprd.physicsgroup = tpg.id WHERE tpg.physicsgroupname = 0) WHERE ora_rn0 AND ora_rn=25 Any ideas??? Thanks, Valentin. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] 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] adding joins to existing select
Hi, is there are any way to add additional joins to a given select object? My problem is the following, I need to join the same table multiple times. How many times I don't know in advance and in addition I need to apply where clause while adding this join. Thanks, 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: how to display all the tables of my DB
Hi, I've used slightly different approach: sel=SELECT table_name FROM all_tables WHERE owner='XXX' # ORACLE sel=show tables # MySQL sel=SELECT name FROM SQLITE_MASTER WHERE type='table' # SQLite con=dbengine.connect() metadata=DynamicMetaData() tList = con.execute(sel) tables=[] for t in tList: tables.append(Table(t[0], metadata, autoload=True)) that will give you a list of auto-loaded Table objects. Valentin. On Mar 22, 3:59 pm, Mando [EMAIL PROTECTED] wrote: I launched it, but I receive this error message: Traceback (most recent call last): File autocode.py, line 20, in module tbl = Table(tname, metadata, schema=schema, autoload=True); File build/bdist.macosx-10.3-fat/egg/sqlalchemy/schema.py, line 143, in __call__ File build/bdist.macosx-10.3-fat/egg/sqlalchemy/engine/base.py, line 505, in reflecttable File build/bdist.macosx-10.3-fat/egg/sqlalchemy/databases/ postgres.py, line 385, in reflecttable KeyError: 'information_schema.cardinal_number' Somes ideas? thanks again! --~--~-~--~~~---~--~~ 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: ORACLE db name in table definitions
Thanks,
it works.
On Mar 23, 10:56 am, Michael Bayer [EMAIL PROTECTED] wrote:
dont stick foo.bar in your table name. use the schema=DBNAME
parameter on your Table.
On Mar 23, 2007, at 9:41 AM, vkuznet wrote:
Hi,
I've got a new DB to handle and the account is setup in a way that I
need to specify dbname for selects, e.g. select * from DBNAME.T
So, I created a table
T=Table('DBNAME.T')
and once I used it
res=select([T]).execute()
the sqlalchemy constructed the following query with *quotes*
select DBNAME.T.id from DBNAME.T
the problem is that ORACLE doesn't accept it, but if I manually drop
*quotes* from this select and pass it to sqlplus everything works,
i.e.
select DBNAME.T.id from DBNAME.T
How to disable *quoting* for SQLAlchemy?
Thanks,
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] table name/Foreign key
Hi,
I have a dump question about naming conventions for foreign keys.
Using ORACLE as back-end all table names are in capital letters. So
Table object looks like:
Table('BRANCH',DynamicMetaData(),Column('id',OracleInteger(),primary_key=True,nullable=False),
Column('name',OracleString(length=500),nullable=False),
Column('lastmodifiedby',OracleInteger(),
ForeignKey('person.id')),
Column('lastmodificationdate',OracleTimestamp(timezone=False),
default=PassiveDefault(sqlalchemy.sql._TextClause object at
0xb6b142ac)),
Column('creationdate',OracleTimestamp(timezone=False),
default=PassiveDefault(sqlalchemy.sql._TextClause object at
0xb6b1430c)),
Column('createdby',OracleInteger(),ForeignKey('person.id')),schema=None)
The question is why Foreign key is in lower case, since the table
person name is actually in capital
Table('PERSON',DynamicMetaData())
Thanks,
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] connection pool
Hi,
I just came across Documentation and it's not clear to me how to use
connection pooling.
When invoked
db=create_engine()
the pool parameter is set to None by default, right?
In Connection pooling section of docs, it's said
For most cases, explicit access to the pool module is not required
So that's why I'm confused. Does the pooling is turn on by default or
not. If it is not, does the following example is the right default
approach to use:
def getconn():
return MySQLdb.connect(user='ed', dbname='mydb')
engine = create_engine('mysql://', pool=pool.QueuePool(getconn,
pool_size=20, max_overflow=40))
con = engine.connect()
In this case when I invoke multiple times con=engine.connect(), does
the connection will be take from pool? If connection will timeout,
does pool guarantee to make a new one?
Thanks,
Valentin.
P.S. Even Documentation is very well written and very comprehensive,
it provides so many options and examples, that it's not clear what
average user should use for most common cases.
--~--~-~--~~~---~--~~
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: connection pool
Hi, it's not obvious, nothing said in a docs about default pool setup and The Database options section has: pool=None - an actual pool instance. that's why I conclude that pool is NOT setup by default. I just want to confirm that. Valentin. On Mar 14, 1:34 pm, Sébastien LELONG [EMAIL PROTECTED] securities.fr wrote: So that's why I'm confused. Does the pooling is turn on by default or not ? IIRC, a default pool is set according to the type of engine (eg. SingletonThreadPool for sqlite, QueuePool for MySQL, or the like...). So it's set by default, but you can of course override this with your own pool, as you described. In this case when I invoke multiple times con=engine.connect(), does the connection will be take from pool? If connection will timeout, does pool guarantee to make a new one? Use pool_recycle parameter so prevent any timeout. Hope it helps. Cheers Seb -- Sébastien LELONG sebastien.lelong[at]sirloon.nethttp://www.sirloon.net --~--~-~--~~~---~--~~ 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] how to get column names in result
Hi, a very simple question which I cannot find in documentation. How to get column names together with result. This is useful for web presentation of results using templates. I used use_labels=True, indeed it construct query with names, but my final result contains only column values. What I wanted is to get as a first row the column names. Thanks, 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] cross DB development, columns lower/upper case letters
Hi, I'm trying to develop a cross-DB application which works with ORACLE and MySQL back-ends. Both DBs has the same schema, but of course there is a caveat. ORACLE has Tables and Columns in upper case and MySQL does not. That leads to the following problem. When I construct select(table.c.column) I face out with problem that I need to specify lower case for column in MySQL and upper case letters for ORACLE. With table names it's easy I can use table aliases. But how to avoid problem with columns names. Thanks, 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: arbitrary join/select from a given list
SQLAlchemy rocks!!! Thanks. P.S. Even though it can't figure out relationship if one table is missing in a chain of relationship, I think I can do some tricks about it. FYI, I just did a test between 4 tables tableA id tableB id, tableA_id tableC id, tableB_id, tableD_id tableD id and did select([...],from_obj=[tableA.join(tableB).join(tableD)]) If I'll find any api to tell me about relationship of table I'm done, I can figure out missing relationship and add tables to the join. It would be nice to cover this case as well (or may be I'm still missing something). On Feb 5, 3:22 pm, Michael Bayer [EMAIL PROTECTED] wrote: select([x.c.col1, y.c.col2, z.c.col3, ...], from_obj=[x.join(y).join(z).join(q)...]) On Feb 5, 2:27 pm, vkuznet [EMAIL PROTECTED] wrote: Hi, I've trying to solve the following problem. I've given a list of tables and list of columns to look at. All tables are auto-loaded from DB. So, I don't know a priory (I don't want to know) the relationships and table schema. Now, how to build a general join among tables and retrieve only the list of given columns names. As an example, there is an explicit join call and I can do join(tableObj1, tableObj2).select() but selection will be done for all columns and as of my understanding join can do only 2 tables. Can it be generalized to a list of tables and can selection be done only on given column names? Thanks, 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 -~--~~~~--~~--~--~---
