[sqlalchemy] Re: adding some sql function to several dialects
Thanks, Michael
i end with the following:
#
from sqlalchemy.sql.expression import _Function
class year( _Function):
__visit_name__ = 'year'
def __init__(self, value):
self.value = value
_Function.__init__( self, self.__visit_name__)
def _compiler_dispatch(self, compiler, **kargs):
val = compiler.preparer.format_column( self.value, use_table=
True)
if compiler.dialect.name == 'postgres':
return extract( %s from %s) % ( self.name, val)
elif compiler.dialect.name == 'sqlite':
format_char = 'Y'
pfx = 'strftime( %' + format_char + ','
return pfx + ('%s)' % val)
else:
return '%s( %s)' % ( self.name, val)
year._compiler_dispatch = _compiler_dispatch
cheers,
stefan
On 18 Март, 19:26, Michael Bayer mike...@zzzcomputing.com wrote:
we'll be adding a feature for this soon. here is a non-public way to do
it for now which will work throughout 0.5:
from sqlalchemy.sql.expression import ClauseElement
class year(ClauseElement):
__visit_name__ = 'year'
def __init__(self, value):
self.value = value
def _compiler_dispatch(self, compiler):
if compiler.dialect.name == 'postgres':
return pg_year(%s) % self.value
else:
return sqlite_year(%s) % self.value
year._compiler_dispatch = _compiler_dispatch
che wrote:
Hi,
i need to add several functions to all dialects that i'll plan to use
in order to be fully database-independent.
Does anybody have tips how to achieve this?
For example i want to add function year( date) to sqlite and postgres
dialects.
TIA,
Stefan
--~--~-~--~~~---~--~~
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] adding some sql function to several dialects
Hi, i need to add several functions to all dialects that i'll plan to use in order to be fully database-independent. Does anybody have tips how to achieve this? For example i want to add function year( date) to sqlite and postgres dialects. TIA, Stefan --~--~-~--~~~---~--~~ 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] recursive sql
hi alchemysts, as of end of march 2009 there will be version of PostgreSQL (hopefully, finally;) that will support recursive sqls (WITH RECURSIVE...) and there are also at least 4 other main SQL that already support it (DB2, MSSQL, Firebird, Oracle (syntax diff)), do you have plans for adding this useful feature to the core SQL Alchemy? regards, stefan --~--~-~--~~~---~--~~ 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: SQL execution order in the unit of work
Hi, On 11 , 04:02, Michael Bayer [EMAIL PROTECTED] wrote: On Nov 10, 2007, at 4:54 PM, Manlio Perillo wrote: Isn't it possible to just use the order used by the programmer? If I call save(A) save(B) the order of save() is signficant for instances of one class: save(A1) save(A2) will insert A1 and A2 in that order. but beyond that, the order is determined by the topological sort of all mappers. if you save objects of type A, B, C and D, B is dependent on A, D is dependent on C, and by dependent i mean they have relation()s set up; it might say for the ordering: A B C D. But you saved the objects in this order: save(C1) save(D1) save(B1) save(A1) save(C2). now the order of your save()'s is in conflict with what the topological sort requires - it *cannot* save C2 where its being saved if D1 is dependent on it - if it put D1 at the end, now D1 is being saved after A1, etc. and your ordering is out the window. Also, by default the topological sort is only sorting at the level of tables, not rows - when row-based dependencies are detected, complexity goes up and the efficiency of the flush() goes down. so no, its not at all workable for save()'s to determine the order across classes - in any realistic scenario they will conflict with the topological sort. youre basically suggesting that SA would do half of a topological sort and you'd do the other half manually, but it doesnt work that way. i had similar need to order things prev week. I thought that may be in the future there will be possible to define some artificial dependency (similar to relation) that have no its counterpart in the database in order to meet similar requrements - this will add just one, two dependencies to the topological sort and will not slow down much the commit process. what is your opinion, Michael? such requirement arose for me trying to fill some cache table (CT) when some other table(OT) is changed. Before i made the relation OT-CT it sometimes tried to update in CT before the change in OT is done. regards, stefan --~--~-~--~~~---~--~~ 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: db independent way to get id from sequence
please help --~--~-~--~~~---~--~~ 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: db independent way to get id from sequence
id = connection.execute(Sequence('my_sequence'))
thanks, Michael
--~--~-~--~~~---~--~~
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] db independent way to get id from sequence
Hi,
Are there any database independent way to get the id from some
sequence - for databases that supports it? For postgres this can be
done by issueing select nextval('name_of_the_sequence') statement,
but for other databases like oracle, firebird it is maybe different.
It seems that there is no such protocol in SA source, but maybe i am
missing something obvious.
I assume that to distinguish the databases that support sequences
metadata.bind.dialect.preexecute_sequences boolean can be used. am i
right?
regards,
stefan
p.s. currently i use the following code in postgres, but plan to use
something in other databases too:
sql = select nextval('\%s\') % name_of_the_sequence
oid =
db.connect().execute( sql.encode( db.dialect.encoding)).scalar()
print oid
--~--~-~--~~~---~--~~
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 executemany and postgresql - probably bug
Hi,
this issue (#759) seems fixed with the SA0.4b5. Thanks, Michael.
One additional question concerning last_inserted_ids() - is it
supposed to work below:
...
isql = Insert( table_Manager, values= {'name':bindparam('name'),
'duties':bindparam('duties
r2 = con.execute( isql, [
dict( name= 'torencho', duties= 'bany'),
dict( name= 'mnogoVojdMalkoIndianec', duties= 'lany'),
])
ids = r2.last_inserted_ids()
...
or i am using it improperly?
as it is now on the trunk(r3449) it gives error: AttributeError:
'PGExecutionContext' object has no attribute '_last_inserted_ids'.
regards,
stefan
--~--~-~--~~~---~--~~
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 executemany and postgresql - probably bug
Thanks Michael, it is not urgent to me. if i found time i'll look down what is causing this and eventually try to patch. regards, stefan On 25 Авг, 03:09, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 23, 2007, at 11:18 AM, che wrote: i cant reply to this issue for another week since im out of town. as a workaround, placing autoincrement=False on your primary key column for now may resolve the issue. --~--~-~--~~~---~--~~ 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] sql executemany and postgresql - probably bug
Hi, i tried suggested in other thread way of inserting many records into database table and it raised exception against postgres (psycopg2) using the latest trunk (r3412) of SA. Then i checked that in version 0.3.10 same(analogical) code works. Please tell me if there is something wrong with my usage of the 0.4 version of SA if this is not a bug. regards, stefan the sample code is below: ### from sqlalchemy import * from sqlalchemy.orm import * #db_sqlite = create_engine( 'sqlite:///testdb.db', echo =True ) if 10: import os try: r = os.system( 'dropdb testdb') r = os.system( 'createdb testdb') except OSError: pass db_postgres = create_engine( 'postgres:///testdb', echo =True ) SA_VERSION = '0.4' def checkWith( db): if SA_VERSION == '0.3': meta = BoundMetaData( db) meta.engine.echo = 1 table_Manager = Table( 'Manager', meta, Column( 'duties', type= String, ), Column( 'name', type= String, ), Column( 'id', Integer, primary_key= True, ), ) else: meta = MetaData( db) meta.bind = db meta.bind.echo = 1 table_Manager = Table( 'Manager', meta, Column( 'duties', type_= String, ), Column( 'name', type_= String, ), Column( 'id', Integer, primary_key= True, ), ) meta.create_all() con = db.connect() isql = table_Manager.insert().compile() r2 = con.execute( isql, [ dict( name= 'torencho', duties= 'bany'), dict( name= 'mnogoVojdMalkoIndianec', duties= 'lany'), ]) r2 = r2.last_inserted_ids() print 'R2: %(r2)s\n' % locals() #checkWith( db_sqlite) checkWith( db_postgres) --~--~-~--~~~---~--~~ 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 executemany and postgresql - probably bug
Hi,
On 23 Авг, 17:47, Michael Bayer [EMAIL PROTECTED] wrote:
dont compile() the insert statement yourself here; since you are only
executing it once, theres nothing to be gained by manually compiling
first.
this was the minimal code demonstrating the issue.
i planned to do this many times for bulk insert into the table in
database-independent way.
Its also the source of the error. the issue is that when the
Insert is compiled with no values clause, it produces column
entries for all three columns; but youre only sending two columns in
your argument list. this behavior is the same in 0.3.
seems there is some other prob too:
- in 0.3 it is issued 2 statements:
select nextval('Manager_id_seq')
and then:
INSERT INTO Manager (duties, name, id) VALUES (%(duties)s, %(name)s,
%(id)s)
with the ids got from the db and the other parameters.
- in 0.4 it is issued only the last insert with all ids None which
leads to the error
to compile the insert for just two columns (which again, you probably
dont need to do here), put them in the values clause:
c = Insert(values={'x':bindparam('x'), 'y':bindparam('y')}).compile()
engine.execute(c, {'x':5, 'y':7})
even in this case the error is the same in 0.4:
2007-08-23 17:59:34,573 INFO sqlalchemy.engine.base.Engine.0x..2c
INSERT INTO Manager (duties, name, id) VALUES (%(duties)s, %(name)s,
%(id)s)
2007-08-23 17:59:34,573 INFO sqlalchemy.engine.base.Engine.0x..2c
[{'name': 'torencho', 'duties': 'bany', 'id': None}, {'name':
'mnogoVojdMalkoIndianec', 'duties': 'lany', 'id': None}]
2007-08-23 17:59:34,578 INFO sqlalchemy.engine.base.Engine.0x..2c
ROLLBACK
Traceback (most recent call last):
File insertMultiple.py, line 46, in module
checkWith( db_postgres)
File insertMultiple.py, line 40, in checkWith
dict( name= 'mnogoVojdMalkoIndianec', duties= 'lany'),
File /home/stefanb/src/hor/sqlalchemy/engine/base.py, line 784, in
execute
return Connection.executors[c](self, object, multiparams, params)
File /home/stefanb/src/hor/sqlalchemy/engine/base.py, line 815, in
_execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
parameters=param), multiparams, params)
File /home/stefanb/src/hor/sqlalchemy/engine/base.py, line 826, in
_execute_compiled
self.__execute_raw(context)
File /home/stefanb/src/hor/sqlalchemy/engine/base.py, line 838, in
__execute_raw
self.__executemany(context)
File /home/stefanb/src/hor/sqlalchemy/engine/base.py, line 872, in
__executemany
raise exceptions.SQLError(context.statement, context.parameters,
e)
sqlalchemy.exceptions.IntegrityError: (IntegrityError) null value in
column id violates not-null constraint
'INSERT INTO Manager (duties, name, id) VALUES (%(duties)s, %
(name)s, %(id)s)' [{'name': 'torencho', 'duties': 'bany', 'id': None},
{'name': 'mnogoVojdMalkoIndianec', 'duties': 'lany', 'id': None}]
regards,
stefan
--~--~-~--~~~---~--~~
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: Boolean column in MS SQL
Hi,
On 31 Юли, 02:33, Paul Johnston [EMAIL PROTECTED] wrote:
Yes, please do. I think you'll find some tweak to MssqlCompiler can
achieve this.
BTW, AND/OR are not broken generally; I think it's just BIT columns they
have a problem with.
Paul
I dont know how to restrict my changes only to where clause as mssql
has bit weird behavior -
in select like this it is ok:
select * from manager where not ( tobeornot = 1)
if the boolean expression is in the select part - sorry - error:
select not ( tobeornot = 1) from manager BUMMM
anyway the patch seems to work (at least for me).
below is the patch if it can be useful for someone (also not sure that
this is the proper way)
regards,
stefan
Index: databases/mssql.py
===
--- databases/mssql.py (revision 2997)
+++ databases/mssql.py (working copy)
@@ -824,6 +824,33 @@
else:
super(MSSQLCompiler, self).visit_alias(alias)
+#TODO restrict changes in visit_unary, visit_clauselist to the
whereclause only!!!
+__BOOL_HACK = ' =1'
+
+def _isSelect( self):
+return not self.isinsert and not self.isupdate #and
isinstance( self.dialect, MSSQLDialect_pymssql)
+
+def visit_unary( self, unary):
+if (self._isSelect() and 'NOT' == getattr(unary,
'operator','')
+and isinstance( unary.element, schema.Column)
+and isinstance( unary.element.type,
sqltypes.Boolean) ):
+unary.element = sql._TextClause( str(unary.element) +
self.__BOOL_HACK)
+self.traverse( unary.element)
+super(MSSQLCompiler, self).visit_unary(unary)
+
+def visit_clauselist(self, list):
+if self._isSelect():
+column = None
+for each in list.clauses:
+if isinstance( each, schema.Column) and
isinstance( each.type, sqltypes.Boolean):
+column = each
+break
+if column and list.operator in ('AND', 'OR'):
+newClause = sql._TextClause( str(column) +
self.__BOOL_HACK)
+self.traverse( newClause)
+self.strings[ column] = str( newClause)
+super(MSSQLCompiler, self).visit_clauselist(list)
+
def visit_column(self, column):
# translate for schema-qualified table aliases
super(MSSQLCompiler, self).visit_column(column)
--~--~-~--~~~---~--~~
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: Boolean column in MS SQL
On 2 Авг, 17:00, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 2, 2007, at 5:07 AM, che wrote: you might want to try a. working with the 0.4 trunk, which has a simpler compilation scheme and b. just overriding visit_select() to pass a flag down into sub-visit_XXX calls. also what specifically do you want to convert select not (tobeornot=1) from manager to be ? seems nothing here can be done, except... to issue some proper error message. currently in such case error is issued by the mssql itself which is clear enough maybe. --~--~-~--~~~---~--~~ 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: Boolean column in MS SQL
On 31 Юли, 02:33, Paul Johnston [EMAIL PROTECTED] wrote: Hi, anyway this seems like bug to me as this will create database- dependence (also AND and OR not working too in MSSQL). i'll try to prepare some general patch to this and send back here (if someone didnot outrun me). Yes, please do. I think you'll find some tweak to MssqlCompiler can achieve this. BTW, AND/OR are not broken generally; I think it's just BIT columns they have a problem with. yes thats what i meant. AND, OR and NOT are not working with BIT columns only. stefan 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: Boolean column in MS SQL
Hi, On 27 Юли, 23:27, Paul Johnston [EMAIL PROTECTED] wrote: Hi, type. Do you have some idea how to preserve entire boolean semantics in mssql? I've not tried this but perhaps comparing to 1 does the trick, e.g. instead of a and not b do (a = 1) and not (b = 1) yes, thanks. this works. anyway this seems like bug to me as this will create database- dependence (also AND and OR not working too in MSSQL). i'll try to prepare some general patch to this and send back here (if someone didnot outrun me). regards, stefan 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: using pyODBC
Hi, unfortunately my time-limited trial license of the easysoft driver already expired so i cannot check it further. thanks anyway for your wilingness to help. regards, stefan --~--~-~--~~~---~--~~ 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: using pyODBC
hmm it gives with use_scope_identity=False and r2746:
File /home/stefanb/src/hor/sqlalchemy/engine/strategies.py, line
106, in create
raise TypeError(Invalid argument(s) %s sent to create_engine(),
using configuration %s/%s/%s. Please check that the keyword arguments
are appropriate for this combination of components. %
(','.join(['%s' % k for k in kwargs]), dialect.__class__.__name__,
pool.__class__.__name__, engineclass.__name__))
TypeError: Invalid argument(s) 'use_scope_identity' sent to
create_engine(), using configuration MSSQLDialect_pyodbc/QueuePool/
Engine. Please check that the keyword arguments are appropriate for
this combination of components.
--~--~-~--~~~---~--~~
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: using pyODBC
shame on me I omit your ...addng the keyword parameter..., i.e. db_mssql = create_engine( 'mssql://sa:[EMAIL PROTECTED] use_scope_identity=1', module= pyodbc) unfortunately with scope identity it crashes earlier - mssql.py returns None for id and rollbacks. best regards stefan --~--~-~--~~~---~--~~ 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: using pyODBC
forgot to mention that SA is at revision r2733. --~--~-~--~~~---~--~~ 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: migrate tool
Thanks Evan, Hope that soon I'll use your tool. Stefan --~--~-~--~~~---~--~~ 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] eager load with polymorphic
Hi,
I tried to eager load some data from class that have reference to
polymorhic class. The object model is: class Base inherited by both
Manager and Address, Managers has reference to Address - when I stop
here SA can eager loading the addresses of managers. The problem arise
when I add another class MailAddress that inherits Address - in this
case address of Managers are simply lazy loaded, despite that it is
explicitly set to be eager. Maybe the problem is that somehow
polymorhic and eager cannot be combined?
please advice
TIA
Stefan
Below is example of code demonstrating the behavior:
from sqlalchemy import *
db_sqlite = create_engine( 'sqlite:///:memory:')
MODEL_WITH_MAIL_ADDR = 1 # 1 - WITH MAILADDRESS, 0 - WITHOUT
MAILADDRESS
def checkWith( db):
meta = BoundMetaData( db)
meta.engine.echo = 0
table_Base = Table( 'base', meta,
Column( 'discriminator', type= String, ),
Column( 'id', Integer, primary_key= True, ),
)
table_Manager = Table( 'manager', meta,
Column( 'address_id', Integer, ForeignKey( 'address.id', name=
'address_id_fk',), ),
Column( 'name', type= String, ),
Column( 'id', Integer, ForeignKey('base.id', name=
'manager_inh_fk'), primary_key= True, ),
)
table_Address = Table( 'address', meta,
Column( 'country', type= String, ), #for case without
mailaddress
Column( 'city', type= String, ),
Column( 'id', Integer, ForeignKey('base.id', name=
'address_inh_fk'), primary_key= True, ),
)
if MODEL_WITH_MAIL_ADDR:
table_Mail = Table( 'mailaddress', meta,
Column( 'country', type= String, ),
Column( 'id', Integer, ForeignKey('base.id', name=
'address_inh_fk'), primary_key= True,
)
class Base( object):
def set( me, **kargs):
for k,v in kargs.iteritems(): setattr( me, k, v)
return me
def __str__(me): return str(me.__class__.__name__)
+':'+str(me.name)
__repr__ = __str__
class Manager( Base):
def __str__(me):
res = Base.__str__(me)+':'+str(me.address.city)
if MODEL_WITH_MAIL_ADDR:
res += ':'+str(me.address.country)
return res
__repr__ = __str__
class Address( Base): pass
class MailAddress( Address): pass
meta.create_all()
def make_mappers():
propdic = {
'manager': table_Base.join( table_Manager, onclause=
(table_Manager.c.id==table_Bas
'address': table_Base.join( table_Address, onclause=
(table_Address.c.id==table_Bas
'base':
table_Base.select( table_Base.c.discriminator=='base'),
}
if MODEL_WITH_MAIL_ADDR:
propdic.update( { 'mailaddress':
table_Base.join( table_Address.join( table_Mail
, onclause=
(table_Mail.c.id==table_Address.c.id))
, onclause=
(table_Address.c.id==table_Base.c.id)
), })
join_Address = polymorphic_union( {
'address': table_Base.join( table_Address, onclause=
(table_Address.c.id==table_Bas
'mailaddress': table_Base.join( table_Address,
onclause=
(table_Address.c.id==table_Base.c.id)).join( table_Mail,
onclause=
(table_Mail.c.id==table_Address.c.id)
),
}, None)
join_Base = polymorphic_union( propdic, None)
mapper_Base = mapper( Base, table_Base, select_table=
join_Base
, polymorphic_on= join_Base.c.discriminator,
polymorphic_identity= 'base')
mapper_Manager = mapper( Manager, table_Manager,
properties= { 'address' : relation( Address, primaryjoin=
(table_Manager.c.address_id==t
, inherits= mapper_Base
, inherit_condition= (table_Manager.c.id==table_Base.c.id)
, polymorphic_identity='man'
)mapper_Address = mapper( Address, table_Address
, inherits= mapper_Base
, inherit_condition= (table_Address.c.id==table_Base.c.id)
, polymorphic_identity='adr'
)
if MODEL_WITH_MAIL_ADDR:
mapper_Address.select_table = join_Address
mapper_MailAddress = mapper( MailAddress, table_Mail
, inherits= mapper_Address
, inherit_condition=
(table_Mail.c.id==table_Address.c.id)
, polymorphic_identity='mai'
)
make_mappers()
if not MODEL_WITH_MAIL_ADDR:
MailAddress = Address
c = Manager().set( name= 'pencho')
d = Manager().set( name= 'torencho')
e = Manager().set( name= 'mnogoVojdMalkoIndianec')
f = MailAddress().set( city= 'varna', country= 'BG')
g = MailAddress().set( city= 'sofia', country= 'RU')
h = MailAddress().set( city= 'burga', country= 'US')
c.address, d.address, e.address = f, g, h
session = create_session()
session.save(c)
session.save(d)
[sqlalchemy] Re: eager load with polymorphic
Thanks Michael, Michael Bayer написа: On Mar 19, 2007, at 8:29 AM, che wrote: Maybe the problem is that somehow polymorhic and eager cannot be combined? please advice thats correct, in many cases eager loading will degrade to lazy loading, particularly when it detects a self-referential table relationship (which happens often with polymorphic relationships). if you turn on sqlalchemy.orm logging you should see this in the logs to confirm. unfortunately you are right - in the case of only Address I get in the logs: ... * SA: DEBUG eagerload scalar instance on [EMAIL PROTECTED] ... and in the MailAddress case: ... * SA: DEBUG degrade to lazy loader on [EMAIL PROTECTED] * SA: DEBUG set instance-level lazy loader on [EMAIL PROTECTED] [* SA: DEBUG Executing lazy callable on [EMAIL PROTECTED] * SA: DEBUG lazy load attribute address on instance [EMAIL PROTECTED] ... Is it possible to workaround this behaviour somehow now (or some plans for the SA future)? regards, Stefan --~--~-~--~~~---~--~~ 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.
Hi, it would be good if SA has similar feature to return scalar, but from the point of view of objects - SA internally knows inheritance and so on ... so one doesnt have to bother of exact tables, but only the object. This can be usefull for reporting on top of SA - see for example if you have class A inherits B inherits C and table inheritance and with attributes a,b,c in the tables A, B, C correspondingly - if we have such feature - it will be possible to report just 2 columns A.a, A.b (not bothering about tables at all). But this is a bit changing of a focus of SA to objects, not SQL... Stefan --~--~-~--~~~---~--~~ 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: Get error: 'list' object has no attribute 'accept_visitor'
seems the following will do the job: group_columns = [table.c.col1, table.c.col2] select_columns = group_columns + [sqlalchemy.func.sum(table.c.col3), sqlalchemy.func.sum(table.c.col4)] statement = sqlalchemy.select( select_columns, (table.c.col1 == test), group_by=group_columns) res = statement.execute().fetchall() hth Stefan --~--~-~--~~~---~--~~ 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 0.3.4 and sequence for non-primary key column
thanks again, Michael. Stefan --~--~-~--~~~---~--~~ 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] SA 0.3.4 and sequence for non-primary key column
Hi,
I have a table with column that must use sequence generated number
(for example in Postgres), like this obj_id:
table_Manager = Table( 'Manager', meta,
Column( 'obj_id', Integer, Sequence('obj_id_seq'), ),
Column( 'duties', type= String, ),
Column( 'name', type= String, ),
Column( 'id', Integer, primary_key= True, ),
)
You see obj_id is not the primary key of the column.
What i get as a result that SA correctly gets number from its
obj_id_seq, logs shows that it even tries to insert it to the
database, but in the end it remains Null (in the DB).
Is it this my mistake or is this possible at all?
TIA
Stefan
the code below demonstrates the issue:
--
from sqlalchemy import *
import os
try:
r = os.system( 'dropdb proba')
r = os.system( 'createdb proba')
except OSError: pass
db_postgres = create_engine( 'postgres://[EMAIL PROTECTED]:5432/proba')
assert not 'FIX USERNAME in the above line and than remove me!!!'
def checkWith( db):
meta = BoundMetaData( db)
meta.engine.echo = 1
table_Manager = Table( 'Manager', meta,
Column( 'obj_id', Integer, Sequence('obj_id_seq'), ),
Column( 'duties', type= String, ),
Column( 'name', type= String, ),
Column( 'id', Integer, primary_key= True, ),
)
class Manager( object):
def set( me, **kargs):
for k,v in kargs.iteritems(): setattr( me, k, v)
return me
def __str__(me): return str(me.__class__.__name__)
+':'+str(me.name)
__repr__ = __str__
meta.create_all()
mapper_Manager = mapper( Manager, table_Manager)
import datetime
c = Manager().set( name= 'pencho', duties= 'many')
session = create_session()
session.save(c)
session.flush()
print c
print session.query( Manager).select()
d = Manager().set( name= 'torencho', duties= 'bany')
e = Manager().set( name= 'mnogoVojdMalkoIndianec', duties= 'lany')
session = create_session()
session.save(d)
session.save(e)
session.flush()
print '\n\nobjID in objects:', c.obj_id, d.obj_id, e.obj_id
res = session.query( Manager).select()
print '\nBEFORE session close'
for i in res:
print 'OBJ( Id: %s Obj_id: %s)' % (i.id, i.obj_id)
session.close()
session = create_session()
res = session.query( Manager).select()
print '\nAFTER session close'
for i in res:
print 'OBJ( Id: %s Obj_id: %s)' % (i.id, i.obj_id)
checkWith( db_postgres)
--
After session close Obj_id is 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 0.3.4 and sequence for non-primary key column
Hi,
Michael Bayer написа:
if you dont want to use the trunk for now, you can probably define
the column as:
Column('obj_id', integer, default=func.obj_id_seq.nextval())
this answers another question of mine :) that i planned to ask
...but it generates (on Postgres) this:
SELECT obj_id_seq.nextval()
and seems the proper syntax is:
SELECT nextval( 'obj_id_seq');
regards,
Stefan
--~--~-~--~~~---~--~~
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: guessing sql joins from object level
Hi, Michael Bayer написа: SA can form joins between tables automatically if the tables express the proper foreign key relationship between each other, and if there is no ambiguity in that relationship; i.e. table A and table B have only one ForeignKeyConstraint (or single ForeignKey) between each other. if you have table A and table B, a join is just: My questions was about the case when you have more then 2 tables (A-B-C-D) related, is this possible too? A.join(B) with regards to integrating those joins with mapper queries, see http://www.sqlalchemy.org/docs/datamapping.myt#datamapping_selectrelations_queryjoins . the main keyword argument to select() here is the from_obj parameter. regards, StefanB --~--~-~--~~~---~--~~ 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] guessing sql joins from object level
Hi, I am trying to translate to sqlalchemy my queries having on object level clauses like the a.b.c.d == some_value where a is instance of class A, b - instance of class B and... How such queries can be expressed in sqlalchemy code (supposing that every class is mapped to its own table)? Are there some possibility SA to automagically guess the needed joins when generating appropriate sql select? Thanks in advance StefanB --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
