[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
mmmh. This is about underlying base-framework of a system, equivalent in complexity to ERP. So i have unknown hierarchy of classes - be them documents, entities, whatever u fancy. And they can point to each other in an unknown way. if u want some example: DocumentA1 has some data and references other such documents (e.g. parent - in a reason-consequence chain); then DocumentA2 inherits DocumentA1 and adds some more fields to fill. More, i'm dealing with this O2R stuff over 10 years and i'm sick of specificaly write something over and over if it can be automated. But just recently i saw some light in the tunnel - SAlchemy. Therefore, all this about automating the O2R mapping. Major step? can be. i will do it. On Jan 17, 11:29 am, Paul Johnston [EMAIL PROTECTED] wrote: Hi Svil, here the case where B inherits A, and A references either A or B. i cannot get this to work.What may help everyone here, is if you can give a concrete usage example of why you want to do this. It's likely there's a way to achieve what you want to do without such major steps. 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: scalar select: wrong result types
the above query you are issuing straight textual SQL. SA has no clue
what types to return and it has no say in it - the inconsistent
behavior above originates within your database/DBAPI (which you havent
told me which one it is).
I'm using Mysql5
A very simple table that give me problems:
mytest = Table('mytest', enginedb_test,
Column('id', Integer, primary_key=True, nullable=False),
Column('creation_date', DateTime, default=datetime.datetime.now),
mysql_engine='InnoDB')
you can issue textual sql using the typemap parameter to text():
s = text(some sql, typemap={'x':types.DateTime})
It doesn't work; result is a 'str' type
res = sq.text(
SELECT w2.id,
(SELECT w2t.creation_date AS creation_date
FROM mytest AS w2t where w2t.id=w2.id
LIMIT 1 OFFSET 0) as last_mod
FROM mytest AS w2
, enginedb_test, typemap={'last_mod':sq.types.DateTime}).execute().fetchone()
special. the text clause above should work better (or at least is
intended for this scenario). also the type should be propigated
through the label() youre creating above, i thought perhaps it might
not but i added a test case in 2206 that shows it does.
Yes, the test works fine!
I tried to switch db engine: it works for sqlite, it doesn't for mysql...
#works
enginedb_test = create_engine('sqlite:///database_test.db')
= (1, datetime.datetime(2007, 1, 17, 14, 33, 21, 483043))
#doen't work
enginedb_test = create_engine('mysql://name:[EMAIL PROTECTED]/dbname')
= (1L, '2007-01-17 14:30:20')
It is a mysql engine bug?
Thanks for your help
Alessandro
--
Passa a Infostrada. ADSL e Telefono senza limiti e senza canone Telecom
http://click.libero.it/infostrada17gen07
--~--~-~--~~~---~--~~
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: Postgre e pg_largeobject
On 1/17/07, Antonio [EMAIL PROTECTED] wrote:
and now, how can I retrieve the file (res.pdf.data) in a file or send it
as output in a html page (sendig the right headers) ?
Nothing magical:
file('foo.pdf', 'wb').write(res.pdf.data)
see your html framework's docs for instructions on sending back binary data...
--~--~-~--~~~---~--~~
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: Postgre e pg_largeobject
Oops, I didn't notice at first that you are using pg_largeobject...
That's not a good idea, you should really use bytea (for SA, that
means declaring filepdf as a Binary column itself rather than linking
to pg_largeobject) unless you are planning to manually seek inside the
lo (i.e. with lo_lseek, etc.), and it doesn't look like you are.
I don't believe SA really supports using pg_largeobject, they're kind
of obsoleted by bytea.
On 1/17/07, Antonio [EMAIL PROTECTED] wrote:
Hi all,
I'm trying to read a pdf file saved in a postgresql table :
pdf_tbl=sqa.Table('pg_largeobject', _mdata,
sqa.Column('loid', sqa.Integer, primary_key = True),
sqa.Column('pageno', sqa.Integer),
sqa.Column('data', sqa.Binary)
)
sqa.mapper(Pdf,pdf_tbl)
sqa.mapper(Pdf,pdf_tbl)
fax_tbl=sqa.Table('faxes', _mdata,
sqa.Column('id', sqa.Integer, primary_key = True),
sqa.Column('sender', sqa.TEXT),
sqa.Column('pages', sqa.SmallInteger),
sqa.Column('received', sqa.DateTime),
sqa.Column('filepdf', sqa.Integer,
sqa.ForeignKey('pg_largeobject.loid'))
)
sqa.mapper(Fax,fax_tbl,properties={ 'pdf': sqa.relation(Pdf) })
sess=sqa.create_session()
qry=sess.query(Fax)
res=qry.get_by(id=1)
and now, how can I retrieve the file (res.pdf.data) in a file or send it
as output in a html page (sendig the right headers) ?
tanks in advance ...
PS
sorry for my english ...
--
#include stdio.h
int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,111,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,105,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf(%s,c);return 0;}
--~--~-~--~~~---~--~~
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: ORM ramblings 2 - and announcing some o2r wrapper
svil wrote: P.S. u can remove the other repeating post from another account fo mine - seems GG has weird latenicies, hence i double posted... and first one appeared 10hrs later than second. its because google marked them all as spam. seeing as there were 5 duplicate messages, i let one through and deleted the rest. --~--~-~--~~~---~--~~ 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: Postgre e pg_largeobject
On Wed, 2007-01-17 at 11:45 +0100, Antonio wrote: Hi all, I'm trying to read a pdf file saved in a postgresql table : and now, how can I retrieve the file (res.pdf.data) in a file or send it as output in a html page (sendig the right headers) ? Antonio, I did a very similar thing (storing largish PDF's) in PostgreSQL and what I did was write an external C program that I let be called as a CGI. Although this approach seems slightly kludgy, it works well for a few reasons: 1. Takes the load off your Python web framework (which tend to be not good at serving large files). 2. Very high performance (will easily saturate 100Mbit if you let it). 3. Most importantly, psycopg (and hence everything that relies on it, e.g. SQLAlchemy) doesn't currently support large objects, so it has to be done some other way. Basically I used this by wrapping it in a simple Python CGI (although the ultimate goal was to make it directly callable from a CGI request w/o any wrapper at all). If you end up doing it this way, then you'll want to figure out a way to prevent unauthorized users from accessing it (either via HTTP AUTH or perhaps even by using one of the deep-link preventing features of your webserver if it has them). Hope this gives you a starting point. Regards, Cliff tanks in advance ... PS sorry for my english ... --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- pgcatlo.tgz Description: application/compressed-tar
[sqlalchemy] Re: portable schema
if you want the behavior to happen within SA itself in a transparent way, youd have to provide redefined versions of sqlite.SLDialect and mysql.MSDialect and friends which add these behaviors in. but personally I wouldnt want to insert those behaviors into SA itself. id rather write an app that just says: from sqlalchemy.engine import url def make_my_database(dburl, schemaname): url_obj = url.make_url(dburl) table_prefix = '' if url_obj.drivername == 'sqlite': url_obj.host=url_obj.host + schemaname + .db elif url_obj.drivername == 'mysql': table_prefix = schemaname + _ metadata = BoundMetaData(dburl) table1 = Table(table_prefix + table1, meta, ,...) table2 = Table(table_prefix + table2, meta, ) return metadata --~--~-~--~~~---~--~~ 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: scalar select: wrong result types
without seeing a full example, its looking like a mysql bug. SA's datetime implementation for MySQL doesnt do any conversion from return value since MySQLDB handles that task. whereas the sqlite dialect in SA *does* do conversion since sqlite doesnt handle that. --~--~-~--~~~---~--~~ 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: ORM ramblings 2 - and announcing some o2r wrapper
svil wrote: So i have unknown hierarchy of classes - be them documents, entities, whatever u fancy. And they can point to each other in an unknown way. so, you are looking to create an application that generates python applications, basically. if you want to use SA for that, you have to completely master SA's relationships using simple, non-dynamically generated examples first, so you can get a feel for what it does. For example, i notice in your example youre trying to send in primaryjoin conditions which are already expressed in the Table objects - that already is overly complex since SA can divine simple join conditions from the tables themselves. for each pattern you are trying to create dynamically, create a non-dynamic version first, get that to work, then figure out what about your dynamic thing is different from the simple version. also, instead of creating class objects in memory and dynamically/conditionally attaching properties/methods/bells/whistles to each one, consider using true code generation instead (i.e. create a string, then call eval() / imp.load_source()) . that way, all the dynamic/conditional stuff can remain a complex beast, but generate very simple and readable code that would be easy to debug (not to mention perform much faster, since the complexity at generation time gets discarded at runtime). i hear theres a hot new template language called Mako that does this kind of thing.* * the author is also the author of said template language --~--~-~--~~~---~--~~ 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: ORM ramblings 2 - and announcing some o2r wrapper
So i have unknown hierarchy of classes - be them documents, entities, whatever u fancy. And they can point to each other in an unknown way. so, you are looking to create an application that generates python applications, basically. hm, can be interpreted this way. just add 'and interpret them on the fly' i want to use and extend the python's self-reflection - and self-modificiation - to get a sort-of self-interpretable thing, but without generating any source. Haveing proper self-reflection can auto-generate me the source - or multiple sources for multiple target environments, e.g. idl-python-python+C+html+binary. i've done that multiple times - but i want the thing to interpret itself. i know it's slower, but more flexible. if you want to use SA for that, you have to completely master SA's relationships using simple, non-dynamically generated examples first, so you can get a feel for what it does. i've done tons of this, to get where i am now. And i keep doing it. My approach is like start always a new and try with minimal possible stuff, so any thing i am using (e.g. those primary-joins) has been required in a way, (e.g. when u have multiple references, SA wants u to explicitly specify which is what). For example, i notice in your example youre trying to send in primaryjoin conditions which are already expressed in the Table objects - that already is overly complex since SA can divine simple join conditions from the tables themselves. is it errorneus to explicitly put the (correct) joins even if they could be figured out by SA? for each pattern you are trying to create dynamically, create a non-dynamic version first, get that to work, then figure out what about your dynamic thing is different from the simple version. yes, yes. so far so good, see all the sa_*py files; and when i started combining the different viewpoints - pure inheritance, pure (cyclical) references, and pure types, the thing broke. i hear theres a hot new template language called Mako that does this kind of thing.* *hehe*. good. i have done many languages before, interpretable and compileable, but now i am basing all my language creations stuff onto python - that is, using the python as syntax interpreter, and eventualy completely replacing the actual semantix; be it on-the-fly or via the objects themselves. Two examples are the StaticType declarative structure language (creatings python objects of unchangeable structure; very usable for DBs, protocols and similar must-not-change stuff), and the expr.py, which has a way to interpret python functions into Expr-trees, which then can be translated (read code-generation) into whatever, like text or sql-alchemy column expressions. It's all like using the python to build _your_ syntax tree made of your objects, and then use the objects - or some visitor - to do code-generation/ interpretation. But this is all offtopic; i could explain much-more if anyone is interested. i really _really_ want to extend the SA to be able to automaticaly do table-decomposition and mapping of an object-hierarchy. As i said, think of activemapper that also does inheritance and queries, provided just a few hints of how user exactly wants it done. So, back to the question: inheritance and referencing together? This is what stops me now; otherwise i have about 10 diff.cases that SA goes wrong, but i've found a way around so don't bother about them _now_. Also, if u can fix that repeating return obj.__dict__[key] in orm/attributes.py InstrumentedAttribute.get()... to be just return value, will be great. line 214 or so. see, i am replacing it myself, runtime (see sahack4repeatability.py ;-), but it's not nice, changing library's source on the fly ;-). Tomorrow i'll put a version stripped of all my statictype stuff, and all of the testcases (if u want so), which may seem more nice to you... all is about 1500 lines so far. have fun. --~--~-~--~~~---~--~~ 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] MapperExtension errors
I seem to have stumbled upon a bug, but perhaps I'm just using
MapperExtension wrong. Anyway, here's some code that doesn't work:
---
from sqlalchemy import *
from sqlalchemy.orm import MapperExtension
global_session = create_session()
metadata = BoundMetaData('mysql://[EMAIL PROTECTED]/napa')
test_table = Table('test', metadata,
Column('tid', Integer, primary_key = True),
Column('modified', DateTime),
Column('foo', String(1024)))
class Test(object):
def __init__(self, foo):
self.foo = foo
class ModifiedMapper(MapperExtension):
#whenever something is changed, this will make sure the modified
is set
def before_update(self, mapper, connection, instance):
instance.modified = func.now()
testmapper = mapper(Test, test_table, extension=ModifiedMapper)
def main():
test = Test(foo)
global_session.save(test)
global_session.flush()
global_session.delete(test)
global_session.flush()
if __name__==__main__:
main()
-
And here's the stack trace:
Traceback (most recent call last):
File test.py, line 33, in ?
main()
File test.py, line 25, in main
test = Test(foo)
File build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py, line
548, in init
File build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py, line
1365, in lambda
File build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py, line
1372, in _do
TypeError: unbound method get_session() must be called with
ModifiedMapper instance as first argument (got nothing instead)
Anybody have any idea what that all means? Am I doing something
wrong or is it a bug?
-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] Re: ORM ramblings 2 - and announcing some o2r wrapper
svil wrote: is it errorneus to explicitly put the (correct) joins even if they could be figured out by SA? its not. but made your code that much harder to read (there was a lot more that made it even harder, thats just one cherry-picked example). Two examples are the StaticType declarative structure language (creatings python objects of unchangeable structure; very usable for DBs, protocols and similar must-not-change stuff), i hear Java is good for that ;). and the expr.py, which has a way to interpret python functions into Expr-trees, which then can be translated (read code-generation) into whatever, like text or sql-alchemy column expressions. are you using AST for this at least ? no need to reinvent the wheel It's all like using the python to build _your_ syntax tree made of your objects, and then use the objects - or some visitor - to do code-generation/ interpretation. sounds awfully generic, when i could just write a little AST visitor... i really _really_ want to extend the SA to be able to automaticaly do table-decomposition and mapping of an object-hierarchy. As i said, think of activemapper that also does inheritance and queries, provided just a few hints of how user exactly wants it done. well there is a project going on to produce the next gen. of activemapper and turboentity, and im sure they will add inheritance to it, since its pretty trivial. as far as querying, im guessing you are looking for an object-query-language (which is what all the pure ORM types seem to be desiring...but strangely they never get) So, back to the question: inheritance and referencing together? This is what stops me now; otherwise i have about 10 diff.cases that SA goes wrong, but i've found a way around so don't bother about them _now_. yeah...to be honest, i dont have the resources to debug an entire application, im in the business of maintaining and developing SQLAlchemy, and if theres a bug which you can illustrate with a *simple* test case of around 50 lines or less, ill fix the bug, or if theres some configuration based upon SA's *documented* usage patterns that doesnt make any sense, i can similarly illustrate how its done. reading the thousands of posts on the mailing list (including the old ones) as well as the tickets in trac can illustrate the kinds of examples that I can work with, without requiring my spending all day learning how someone's application works (because i cant spend all day, ive got paying work ive got to be doing). but if youre writing a whole big thing that is dynamically creating tables/classes which are then invalid, and it just doesnt work, thats kind of out of scope for me...just reading a single test case of yours takes me a very long time since they are not at all easy to follow...maybe some other folks on the list have some cycles to dig into the things that you are doing. you are getting SA exceptions which do have meaning and id suggest you dig in to SA itself to figure out what conditions might create those errors (such as various conflicting columns/properties seems to be a theme with what you have there). but if it turns out that youre doing something that is just wildly different from what SA's ORM basically does (such as its attribute instrumentation), im not sure if building on SA's ORM is your best bet...its already very high level and it is definitely going to be hard to build many more layers of class manipulation on top of it. you can always write your own ORM layer, feel free to borrow from SA's ORM concepts that you like and just use SA's SQL construction facilities to help you out. it might sound like a much bigger job but you would A. definitely learn SA's internals very well and B. get some deeper insight on the problems youre trying to tackle. Also, if u can fix that repeating return obj.__dict__[key] in orm/attributes.py InstrumentedAttribute.get()... to be just return value, will be great. line 214 or so. sure, changeset 2211. things like that, you should create a patch, then run the entire unittest suite to insure it doesnt break anything, then create a new ticket with the patch. also try to create distinct tickets for patches that address distinct issues. --~--~-~--~~~---~--~~ 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: MapperExtension errors
its python and its unfortunate typing error behaviorextension needs to be an instance: testmapper = mapper(Test, test_table, extension=ModifiedMapper()) --~--~-~--~~~---~--~~ 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: ORM ramblings 2 - and announcing some o2r wrapper
is it errorneus to explicitly put the (correct) joins even if they
could be figured out by SA?
its not. but made your code that much harder to read
well, this case does require them :-(
which has a way to interpret python functions into Expr-trees, which
then can be translated (read code-generation) into whatever, like text
or sql-alchemy column expressions.
are you using AST for this at least ? no need to reinvent the
wheel
AST... i don't parse it, python parses it. i just replace .__eq__()
with something else.
The funny thing is, the python compiled code of some expression can be
reinterpreted in diff.ways,
e.g. def myfunc(x,y): return x100 y.startswith('big')
..
myfunc( 23, big_balls_of_fire) - will eval the func yielding
true/false, while
myfunc( Var('x'), Var('y')) - will build you an expression (procided
Var()s have operators etc defined). And even give errors - not any
syntax will fit your new semantix.
of course, theoretically, there is some AST, and some grammar, but it's
sort-a virtual...
It's all like using the python to build _your_ syntax tree made of your
objects, and then use the objects - or some visitor - to do
code-generation/ interpretation.
sounds awfully generic, when i could just write a little AST visitor...
Each time a new grammar and AST and new visitor? and teach users to
that new grammar? and testsuite preventing the huns into rome? mmh. i
did that. couple of languages per year...
Users (be them end-customers or the other-team) know their semantix
pretty well and they can cope with just about any _sensible_ easy
grammar as far as it speaks in their lingo.
oh well. my choice is - no more grammars (lowlevel) - if i can avoid
them.
As i said, think of activemapper that also does inheritance and
queries, provided just a few hints of how user exactly wants it done.
as far as querying, im guessing you
are looking for an object-query-language (which is what all the pure
ORM types seem to be desiring...but strangely they never get)
not at all. SQL, OQL and probably all them *QL frighten me. Too
extreme. Looks like academical creation with little touch to mortal
people. i am targeting what u have with columns, but applied directly
to object attributes, and used more for filtering, than real
calculations.
Practical stuff, as u said - whatever we can do, now.
So, back to the question: inheritance and referencing together?
im in the business of maintaining and developing
SQLAlchemy, and if theres a bug which you can illustrate with a
*simple* test case of around 50 lines or less, ill fix the bug,
110. can't make it less ;-)
can?
and it is about A=Employee and B=Manager, and all Employees having a
manager.
http://linuxteam.sistechnology.com/orm/sa_B_inh_A_A_ref_AB2.py
usage:
1) $ python sa_B_inh_A_A_ref_AB2.py Alink=Manager
or
2) $ python sa_B_inh_A_A_ref_AB2.py Alink=Empluyee
same 2 errors.
if of any help, i've found that if no manager (reference) is being ever
assigned, then case 1 does not crash.
-
from sqlalchemy import *
#table_inheritance, polymorphic
def case( Alink='Manager' ):
class Employee( object):
name = 'notset'
def __str__(me):
return ' '.join( [me.__class__.__name__, str(me.id),
str(me.name), getattr( me.manager, 'name', 'none') ])
class Manager( Employee ):
bonus = 'notset'
def __str__(me):
return Employee.__str__(me) + ' ' + str(me.bonus)
db = create_engine( 'sqlite:///:memory:')
meta = BoundMetaData( db)
meta.engine.echo = 0
class tables: pass
tables.Employee = Table('Employee', meta,
Column('id', Integer, primary_key=True),
Column('name', String, ),
Column('atype', String),
Column('manager_id', Integer,
ForeignKey( Alink+'.id',
use_alter=True, name='whatever1'
)
)
)
tables.Manager = Table('Manager', meta,
Column('bonus', String, ),
Column('id', Integer,
ForeignKey( 'Employee.id'),
primary_key=True,
),
)
meta.create_all()
ajoin = {
'Employee': tables.Employee.select( tables.Employee.c.atype ==
'Employee'),
'Manager': join( tables.Employee, tables.Manager,
tables.Manager.c.id ==tables.Employee.c.id),
}
Ajoin = polymorphic_union( ajoin, None )
mA = mapper( Employee, tables.Employee,
select_table=Ajoin, polymorphic_on=Ajoin.c.atype,
polymorphic_identity='Employee'
)
mA.add_property( 'manager',
relation( Alink == 'Employee' and Employee or Manager,
primaryjoin=
tables.Employee.c.manager_id==(Alink=='Employee' and tables.Employee or
tables.Manager).c.id,
lazy=True, uselist=False, post_update=True
)
)
mB = mapper( Manager,
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
On Jan 17, 2007, at 5:09 PM, svil wrote:
110. can't make it less ;-)
can?
and it is about A=Employee and B=Manager, and all Employees having a
manager.
http://linuxteam.sistechnology.com/orm/sa_B_inh_A_A_ref_AB2.py
OK. the first attachment called test_case1.py is how I'd like you
to send me these test cases. What you do is when you have your 110
lines, continue to refactor it down, removing conditionals and code
until you have a *single* illustration of the failure. in this case
it involved replacing superfluous objects with simple variable names,
removing all the function enclosures (i.e. straight-down program
execution), and factoring all the embedded hard-to-read conditionals
into the single version that fails (in this case the Manager
version is the one that fails). The attached case illustrates the
problem succinctly and as it turns out is exactly 50 nonblank
lines :). It still includes lots of things that are not part of the
failure, such as all the polymorphic arguments, but I can handle a
little bit of extra fluff.
From that point, we have a category of issue that comes up all the
time, where what you want to do is possible, but SA never expected
exactly what youre doing and therefore does not report the problem
its having in any useful way.
the reason its not working is because the relationship between
Employee and Manager has a join condition where each column in
the join is a foreign key to one or the other side of the
relationship - so neither the direction of the relationship, nor the
proper column to populate, can be determined from the join condition
alone. SA then incorrectly determines which column in the join is
the foreign column and gets all confused later on. If you now try
this test program with the trunk, youll get as clear as an error as I
could come up with: Cant determine relation direction for 'manager'
on mapper 'Mapper|Employee|Employee' with primary join
'Employee.manager_id = Manager.id' - foreign key columns are
present in both the parent and the child's mapped tables. Specify
'foreignkey' argument.
So one way that this would work, and what SA normally expected here,
is that when you make a relation between Employee and Manager, SA
would see that as a self-referential relationship between Employee
and Employee; if you made your ForeignKey on the employee table
point to employee.id instead of manager.id, and you also update
your primaryjoin condition accordingly, the mappers detect a self-
referential condition and then it works:
Column('manager_id', Integer,
ForeignKey( 'Employee.id',
use_alter=True, name='whatever1'
)
)
But it works the way you have it as well. If you just provide a
foreignkey parameter to the join condition, limiting which columns
in the join you'd like it to consider to be the foreignkey that is
significant in this join, it also works:
properties={
'manager':relation(Manager,
primaryjoin=employee_table.c.manager_id==manager_table.c.id,
foreignkey=employee_table.c.manager_id, uselist=False, post_update=True)
}
and that is what is attached in working_version.py.
The unfortunate thing about foreignkey is that it is kind of a
messy parameter which is a little inconsistent, namely that it isnt
used for many-to-many relationships right now, and I plan on
replacing with something more comprehensive in the future (ticket
385) so that it can totally replace the usage of ForeignKey on Table
(and make life easier for MySQL folks who like to reflect their
tables). In fact until I saw this example I didnt think there was
ever a reason you'd need to use foreignkey right now provided the
Table's had proper ForeignKeys on them (self-referential mappers use
remote_side now).
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
from sqlalchemy import *
class Employee( object):
name = 'notset'
def __str__(me):
return ' '.join( [me.__class__.__name__, str(me.id),str(me.name), getattr( me.manager, 'name', 'none') ])
class Manager( Employee ):
bonus = 'notset'
def __str__(me):
return Employee.__str__(me) + ' ' + str(me.bonus)
db = create_engine( 'sqlite:///:memory:')
meta = BoundMetaData( db)
meta.engine.echo = 0
class tables: pass
employee_table = Table('Employee', meta,
Column('id', Integer, primary_key=True),
Column('name', String, ),
Column('atype', String),
Column('manager_id', Integer,
[sqlalchemy] Re: Action on object deletion
yeah im trying to keep SA as simple as possible, and whatever hooks and extensions I put in are because they are absolutely needed (like the various MapperExtension hooks are allowing you to put code in the middle of existing processes, i.e. template methods). for extending Session, current patterns you can try are straight subclassing as well as class decoration (i.e. GOF-style decoration, not python decorators). --~--~-~--~~~---~--~~ 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: Profiling mode
I finally came back to this. Here's what I ended up with: # I tried to enable profiling on a per-engine level before resorting to this # hack. (Monkey-patching classes by scanning the gc! Woot!) # # Per-engine profile turns out to totally not work because there's so many # layers of clever stuff going on (well, primarily PoolConnectionProvider # returning proxies instead of real Connections) that it's really impossible to # decorate Connections in a general manner by relying on engine.connection_provider. # Too bad, because it was rather more elegant. # # This will work no matter how many layers of proxies there are... def enable_profiling(): import gc for o in gc.get_objects(): if isinstance(o, type): if o == Connection or Connection in o.__bases__: o._execute = _profilingexecute o._executemany = _profilingexecutemany where the _profilingexecute methods do pretty much what was discussed before. On 10/30/06, Michael Bayer [EMAIL PROTECTED] wrote: id look into building this as a ProxyEngine. _execute and _executemany might be better targets for profiling but its not super- important. On Oct 30, 2006, at 6:38 PM, Jonathan Ellis wrote: For me it has been useful in the past to track overall database query speed so I could optimize the query taking the most aggregate time. (I.e., execution time * times executed.) It looks to me like this could be hooked in to SA pretty easily, with just a minor change to Connection._execute_raw, using statement as the key to aggregate on. (You could even define two _execute_raws and pick one at runtime to avoid any overhead when not in profiling mode.) This seems to work fine: start = time.time() if parameters is not None and isinstance(parameters, list) and len(parameters) 0 and (isinstance(parameters[0], list) or isinstance(parameters[0], dict)): self._executemany(cursor, statement, parameters, context=context) else: self._execute(cursor, statement, parameters, context=context) end = time.time() self._autocommit(statement) profile_data[statement] = profile_data.get(statement, 0) + (end - start) Of course, this only tells you what generated SQL is slow, not what code caused those queries to run, but it's easy enough to grab caller info from the stack. But am I missing other code paths that would have to be tracked? -- Jonathan Ellis http://spyced.blogspot.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Profiling mode
Thinking about it more, I should probably just override the methods of Connection itself and not worry about subclasses. If someone is overriding _execute*, he can do his own damn profiling. :) On 1/17/07, Jonathan Ellis [EMAIL PROTECTED] wrote: I finally came back to this. Here's what I ended up with: # I tried to enable profiling on a per-engine level before resorting to this # hack. (Monkey-patching classes by scanning the gc! Woot!) # # Per-engine profile turns out to totally not work because there's so many # layers of clever stuff going on (well, primarily PoolConnectionProvider # returning proxies instead of real Connections) that it's really impossible to # decorate Connections in a general manner by relying on engine.connection_provider. # Too bad, because it was rather more elegant. # # This will work no matter how many layers of proxies there are... def enable_profiling(): import gc for o in gc.get_objects(): if isinstance(o, type): if o == Connection or Connection in o.__bases__: o._execute = _profilingexecute o._executemany = _profilingexecutemany where the _profilingexecute methods do pretty much what was discussed before. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
