object-relational mappers

**Jason Scheirer** · Apr 1 '08, 08:56 PM

Re: object-relational mappers

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=mil d+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.

**Matthew Woodcraft** · Apr 1 '08, 10:25 PM

Re: object-relational mappers

Aaron Watters <aaron.watters@ 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-

**hdante** · Apr 1 '08, 11:25 PM

Re: object-relational mappers

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=mil d+exponenti...

**Bruno Desthuilliers** · Apr 2 '08, 11:35 AM

Re: object-relational mappers

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...).

**Bruno Desthuilliers** · Apr 2 '08, 11:45 AM

Re: object-relational mappers

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.

**Aaron Watters** · Apr 2 '08, 01:55 PM

Re: object-relational mappers

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/pydistro.py/go?FREETEXT=mysterious+objects

**hdante** · Apr 2 '08, 02:05 PM

Re: object-relational mappers

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=mys terious+obj...

**=?ISO-8859-1?Q?Luis_M=2E_Gonz=E1lez?=** · Apr 3 '08, 01:35 PM

Re: object-relational mappers

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('cust omers') or
db.select('cust omers', name='John').
But you can also resort to plain sql as follows: db.query('selec t *
from customers where name = "John"').

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

Luis

**Marco Mariani** · Apr 3 '08, 01:45 PM

Re: object-relational mappers

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.

**Tim Golden** · Apr 3 '08, 02:05 PM

Re: object-relational mappers

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

**Jarek Zgoda** · Apr 3 '08, 02:05 PM

Re: object-relational mappers

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: zgoda@jabber.as ter.pl | voice: +48228430101

"We read Knuth so you don't have to." (Tim Peters)

**Bruno Desthuilliers** · Apr 3 '08, 02:15 PM

Re: object-relational mappers

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('cust omers') or
db.select('cust omers', name='John').
But you can also resort to plain sql as follows: db.query('selec t *
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.

**Marco Mariani** · Apr 3 '08, 02:35 PM

Re: object-relational mappers

Bruno Desthuilliers wrote:

>A simple select query would be db.select('cust omers') or
>db.select('cus tomers', name='John').
>But you can also resort to plain sql as follows: db.query('selec t *
>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.

**Bruno Desthuilliers** · Apr 3 '08, 02:45 PM

Re: object-relational mappers

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.

object-relational mappers

object-relational mappers

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment