By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

object-relational mappers

P: n/a
I've been poking around the world of object-relational
mappers and it inspired me to coin a corellary to the
the famous quote on regular expressions:

"You have objects and a database: that's 2 problems.
So: get an object-relational mapper:
now you have 2**3 problems."

That is to say I feel that they all make me learn
so much about the internals and features of the
O-R mapper itself that I would be better off rolling
my own queries on an as-needed basis without
wasting so many brain cells.

comments?

-- Aaron Watters

===
http://www.xfeedme.com/nucular/pydis...nential+growth
Apr 1 '08 #1
Share this Question
Share on Google+
17 Replies


P: n/a
On Apr 1, 1:40 pm, Aaron Watters <aaron.watt...@gmail.comwrote:
I've been poking around the world of object-relational
mappers and it inspired me to coin a corellary to the
the famous quote on regular expressions:

"You have objects and a database: that's 2 problems.
So: get an object-relational mapper:
now you have 2**3 problems."

That is to say I feel that they all make me learn
so much about the internals and features of the
O-R mapper itself that I would be better off rolling
my own queries on an as-needed basis without
wasting so many brain cells.

comments?

-- Aaron Watters

===http://www.xfeedme.com/nucular/pydistro.py/go?FREETEXT=mild+exponenti...
You're going to have to learn how any of the OR mappers work to get
anything reasonable out of them, and you are going to need to commit
and invest the time to learn how one works. I would argue that you
should try making a prototype using one or two OR mappers (or just use
SQLAlchemy/Elixir and be done with it) with your existing database and
see which most efficiently does what you need it to. If you get to the
point where the queries are getting too complex to reasonably manage
as python code, then yeah, use raw SQL because that is what it's good
for. Most OR mappers will allow you to sprinkle in raw SQL as needed.

I think it's natural to be paralyzed by all the choices you have, but
just start writing some code and go from there.
Apr 1 '08 #2

P: n/a
Aaron Watters <aa***********@gmail.comwrote:
I've been poking around the world of object-relational
mappers and it inspired me to coin a corellary to the
the famous quote on regular expressions:
"You have objects and a database: that's 2 problems.
So: get an object-relational mapper:
now you have 2**3 problems."
That is to say I feel that they all make me learn
so much about the internals and features of the
O-R mapper itself that I would be better off rolling
my own queries on an as-needed basis without
wasting so many brain cells.
That is the conclusion I have come to.

When a difficult question comes up, you end up having to know the exact
requirements and behaviour of the underlying database anyway. Then once
you know what sequence of commands you need to be issued, you have to
figure out how to persuade the ORM to do it (and not something similar
but subtly wrong). At this stage it's getting in your way.

-M-
Apr 1 '08 #3

P: n/a
On Apr 1, 5:40 pm, Aaron Watters <aaron.watt...@gmail.comwrote:
I've been poking around the world of object-relational
mappers and it inspired me to coin a corellary to the
the famous quote on regular expressions:

"You have objects and a database: that's 2 problems.
So: get an object-relational mapper:
now you have 2**3 problems."

That is to say I feel that they all make me learn
so much about the internals and features of the
O-R mapper itself that I would be better off rolling
my own queries on an as-needed basis without
wasting so many brain cells.

comments?
Try Rails' ActiveRecord. Your problems should reduce to (lg lg
2)^(1/12).

Seriously, you'll forget there's a relational database below. (there
are even intefaces for "relational lists", "trees", etc.)

I won't post a code sample here, it would be heretic.

:-)
>
-- Aaron Watters

===http://www.xfeedme.com/nucular/pydistro.py/go?FREETEXT=mild+exponenti...
Apr 1 '08 #4

P: n/a
hdante a écrit :
On Apr 1, 5:40 pm, Aaron Watters <aaron.watt...@gmail.comwrote:
>I've been poking around the world of object-relational
mappers and it inspired me to coin a corellary to the
the famous quote on regular expressions:

"You have objects and a database: that's 2 problems.
So: get an object-relational mapper:
now you have 2**3 problems."

That is to say I feel that they all make me learn
so much about the internals and features of the
O-R mapper itself that I would be better off rolling
my own queries on an as-needed basis without
wasting so many brain cells.

comments?

Try Rails' ActiveRecord. Your problems should reduce to (lg lg
2)^(1/12).
Correct me if I'm wrong, but IIRC ActiveRecord requires you use numeric
auto_increment fields for primary key. As far as I'm concerned, this is
a definitive no-no.
Seriously, you'll forget there's a relational database below.
Why on earth are you using a RDBMS if you don't want it ? I for one *do*
care about using a *relational* database, and *don't* want to hide it
away. What I don't want is to have to build my queries as raw strings.
And that's where SQLAlchemy shines : it's not primarily an "ORM", it's
an higher-level Python/SQL integration tool that let you build your
queries as Python objects (and also, eventually, build an ORM if you
want to...).
Apr 2 '08 #5

P: n/a
Aaron Watters a écrit :
I've been poking around the world of object-relational
mappers and it inspired me to coin a corellary to the
the famous quote on regular expressions:

"You have objects and a database: that's 2 problems.
So: get an object-relational mapper:
now you have 2**3 problems."

That is to say I feel that they all make me learn
so much about the internals and features of the
O-R mapper itself that I would be better off rolling
my own queries on an as-needed basis without
wasting so many brain cells.

comments?
If you're ok with building your queries as raw string and handling your
resultsets as lists of tuples, then you're right, don't waste you brain
cells learning anything else than SQL and the DB-API.

Now my own experience is that whenever I tried this approach for
anything non-trivial, I ended up building an "ad-hoc,
informally-specified bug-ridden slow implementation of half of "
SQLAlchemy. Which BTW is not strictly an ORM, but primarily an attempt
at a better integration of SQL into Python. So while it may feel like
learning the inner complexities of SQLALchemy (or Django's ORM which is
not that bad either) is "wasting brain cells", MVHO is that it's worth
the time spent. But YMMV of course - IOW, do what works best for you.

Apr 2 '08 #6

P: n/a
Try Rails' ActiveRecord. Your problems should reduce to (lg lg
2)^(1/12).
python(log(log(2)))**(1.0/12.0)
Traceback (most recent call last):
File "<stdin>", line 1, in ?
ValueError: negative number cannot be raised to a fractional power

So you are saying the problems will get really complex? :)
Seriously, you'll forget there's a relational database below. (there
are even intefaces for "relational lists", "trees", etc.)
My experience with this sort of thing is that it is a bit
like morphine. It can feel really good, and in emergencies
it can save you a lot of pain. But if you use it too often
and too seriously you end up with really big problems.

-- Aaron Watters

===
http://www.xfeedme.com/nucular/pydis...erious+objects

Apr 2 '08 #7

P: n/a
On Apr 2, 10:50 am, Aaron Watters <aaron.watt...@gmail.comwrote:
Try Rails' ActiveRecord. Your problems should reduce to (lg lg
2)^(1/12).

python(log(log(2)))**(1.0/12.0)
Traceback (most recent call last):
File "<stdin>", line 1, in ?
ValueError: negative number cannot be raised to a fractional power

So you are saying the problems will get really complex? :)
lg(x) == log_2(x)
lg(lg(2))^(1/12) == 0. (fortunately I didn't write 3 lg's). :-P
>
Seriously, you'll forget there's a relational database below. (there
are even intefaces for "relational lists", "trees", etc.)

My experience with this sort of thing is that it is a bit
like morphine. It can feel really good, and in emergencies
I don't have this much experience on either. ;-)
it can save you a lot of pain. But if you use it too often
and too seriously you end up with really big problems.

-- Aaron Watters

===http://www.xfeedme.com/nucular/pydistro.py/go?FREETEXT=mysterious+obj...
Apr 2 '08 #8

P: n/a
I have come to the same conclusion.
ORMs make easy things easier, but difficult things impossible...

The best approach I've seen so far is webpy's (if we are talking of
web apps).
It isn't an ORM, it is just a way to make the database api easier to
use.
Queries don't return objects, they return something similar to
dictionaries, which can be used with dot notation ( for example,
result.name is equal to result['name'] ).

A simple select query would be db.select('customers') or
db.select('customers', name='John').
But you can also resort to plain sql as follows: db.query('select *
from customers where name = "John"').

Simple, effective and doesn't get in your way.

Luis
Apr 3 '08 #9

P: n/a
Tim Golden wrote:
I've recently used Elixir and found it very useful for a small-scale
database with no more than a dozen tables, well-structured and
easily understood. I'd certainly use it again for anything like that
to save me writing what would amount to boilerplate SQL. But I'd
hate to imagine it in the context of my day job: a messy, organic
and sprawling SQL Server database with over 1,000 tables, let alone
views, procedures and so on.
That's the scenario where the rest of SQLAlchemy (beyond Elixir, that
is, and with reflection turned to 11) can do mucho bueno.

Apr 3 '08 #10

P: n/a
Marco Mariani wrote:
Tim Golden wrote:
>I've recently used Elixir and found it very useful for a small-scale
database with no more than a dozen tables, well-structured and
easily understood. I'd certainly use it again for anything like that
to save me writing what would amount to boilerplate SQL. But I'd
hate to imagine it in the context of my day job: a messy, organic
and sprawling SQL Server database with over 1,000 tables, let alone
views, procedures and so on.

That's the scenario where the rest of SQLAlchemy (beyond Elixir, that
is, and with reflection turned to 11) can do mucho bueno.
Well, true (and I've done good things with it) but, ultimately
if I need to write SQL I'll write SQL: that's what I'm paid for.
And no matter how good sa's generative queries are -- and they
are good -- I've been writing complex SQL queries for 15 years
and learning a more Pythonesque equivalent doesn't really seem
to offer me anything.

Not to take away from the achievements of SqlAlchemy: I'm just
not really the target market, I think.

TJG
Apr 3 '08 #11

P: n/a
Bruno Desthuilliers napisa³(a):
Now my own experience is that whenever I tried this approach for
anything non-trivial, I ended up building an "ad-hoc,
informally-specified bug-ridden slow implementation of half of "
SQLAlchemy. Which BTW is not strictly an ORM, but primarily an attempt
at a better integration of SQL into Python. So while it may feel like
learning the inner complexities of SQLALchemy (or Django's ORM which is
not that bad either) is "wasting brain cells", MVHO is that it's worth
the time spent. But YMMV of course - IOW, do what works best for you.
I like OR mappers, they save me lot of work. The problem is, all of them
are very resource hungry, processing resultset of 300k objects one by
one can effectively kill most of commodity systems. This is where raw
SQL comes in handy.

--
Jarek Zgoda
Skype: jzgoda | GTalk: zg***@jabber.aster.pl | voice: +48228430101

"We read Knuth so you don't have to." (Tim Peters)
Apr 3 '08 #12

P: n/a
Luis M. González a écrit :
I have come to the same conclusion.
ORMs make easy things easier, but difficult things impossible...
Not my experience with SQLAlchemy. Ok, I still not had an occasion to
test it against stored procedures, but when it comes to complex queries,
it did the trick so far - and (warning: front-end developper
considerations ahead) happened to be much more usable than raw strings
to dynamically *build* the queries.
The best approach I've seen so far is webpy's (if we are talking of
web apps).
It isn't an ORM, it is just a way to make the database api easier to
use.
Queries don't return objects, they return something similar to
dictionaries, which can be used with dot notation ( for example,
result.name is equal to result['name'] ).

A simple select query would be db.select('customers') or
db.select('customers', name='John').
But you can also resort to plain sql as follows: db.query('select *
from customers where name = "John"').

Simple, effective and doesn't get in your way.
Seems nice too in another way. Is that part independant of the rest of
the framework ? If so, I'll have to give it a try at least for admin
scripts.

Apr 3 '08 #13

P: n/a
Bruno Desthuilliers wrote:
>A simple select query would be db.select('customers') or
db.select('customers', name='John').
But you can also resort to plain sql as follows: db.query('select *
from customers where name = "John"').

Simple, effective and doesn't get in your way.

Seems nice too in another way.
And no different than using SQLAlchemy's sa.select() or
engine.execute(), after all.
Is that part independant of the rest of the framework ? If so, I'll
have to give it a try at least for admin
scripts.
My admin scripts go through SQLAlchemy as well, I just have some issues
with postgres' COPY statement -- but I don't know if the DBAPI is
supposed to handle that.
Apr 3 '08 #14

P: n/a
Jarek Zgoda a écrit :
Bruno Desthuilliers napisa³(a):
>Now my own experience is that whenever I tried this approach for
anything non-trivial, I ended up building an "ad-hoc,
informally-specified bug-ridden slow implementation of half of "
SQLAlchemy. Which BTW is not strictly an ORM, but primarily an attempt
at a better integration of SQL into Python. So while it may feel like
learning the inner complexities of SQLALchemy (or Django's ORM which is
not that bad either) is "wasting brain cells", MVHO is that it's worth
the time spent. But YMMV of course - IOW, do what works best for you.

I like OR mappers, they save me lot of work. The problem is, all of them
are very resource hungry, processing resultset of 300k objects one by
one can effectively kill most of commodity systems. This is where raw
SQL comes in handy.
The problem here is not about how you build your query but about how you
retrieve your data. FWIW, SQLAlchemy provides quite a lot of "lower
level" SQL/Python integration that doesn't require the "object mapping"
part. "raw SQL" is fine, until you have to dynamically build complex
queries from user inputs and whatnot. This is where the "low-level" (ie:
non-ORM) part of SQLAlchemy shines IMHO.
Apr 3 '08 #15

P: n/a
Bruno Desthuilliers schrieb:
Jarek Zgoda a écrit :
>Bruno Desthuilliers napisa³(a):
>>Now my own experience is that whenever I tried this approach for
anything non-trivial, I ended up building an "ad-hoc,
informally-specified bug-ridden slow implementation of half of "
SQLAlchemy. Which BTW is not strictly an ORM, but primarily an attempt
at a better integration of SQL into Python. So while it may feel like
learning the inner complexities of SQLALchemy (or Django's ORM which is
not that bad either) is "wasting brain cells", MVHO is that it's worth
the time spent. But YMMV of course - IOW, do what works best for you.

I like OR mappers, they save me lot of work. The problem is, all of them
are very resource hungry, processing resultset of 300k objects one by
one can effectively kill most of commodity systems. This is where raw
SQL comes in handy.

The problem here is not about how you build your query but about how you
retrieve your data. FWIW, SQLAlchemy provides quite a lot of "lower
level" SQL/Python integration that doesn't require the "object mapping"
part. "raw SQL" is fine, until you have to dynamically build complex
queries from user inputs and whatnot. This is where the "low-level" (ie:
non-ORM) part of SQLAlchemy shines IMHO.
The same can be said for SQLObjects SQLBuilder. Even if I ended up
generating SQL for some query that didn't touch the ORM-layer, it helps
tremendously to write e.g subqueries and such using python-objects
instead of manipulating strings. They help keeping track of already
referenced tables, spit out properly escaped syntax and so forth.

Diez
Apr 3 '08 #16

P: n/a
On 2008-04-01 22:40, Aaron Watters wrote:
I've been poking around the world of object-relational
mappers and it inspired me to coin a corellary to the
the famous quote on regular expressions:

"You have objects and a database: that's 2 problems.
So: get an object-relational mapper:
now you have 2**3 problems."

That is to say I feel that they all make me learn
so much about the internals and features of the
O-R mapper itself that I would be better off rolling
my own queries on an as-needed basis without
wasting so many brain cells.

comments?
I fully agree :-)

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Apr 03 2008)
>>Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
__________________________________________________ ______________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
Apr 3 '08 #17

P: n/a
M.-A. Lemburg wrote:
On 2008-04-01 22:40, Aaron Watters wrote:
>I've been poking around the world of object-relational
mappers and it inspired me to coin a corellary to the
the famous quote on regular expressions:

"You have objects and a database: that's 2 problems.
So: get an object-relational mapper:
now you have 2**3 problems."

That is to say I feel that they all make me learn
so much about the internals and features of the
O-R mapper itself that I would be better off rolling
my own queries on an as-needed basis without
wasting so many brain cells.

comments?

I fully agree :-)
BTW: Some people implemented O/R mappers above python-ldap. All
implementations I saw up to now are falling short regarding the
complexity of the LDAP attribute sub-types, the syntactical rules for
attribute type descriptive names and attribute name aliasing. So first a
developer has also to evaluate whether a O/R mapper is really complete
before using it.

Ciao, Michael.
Apr 4 '08 #18

This discussion thread is closed

Replies have been disabled for this discussion.