object-relational mappers

[Aaron Watters]

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+exponential+growth

 

[Jason Scheirer]

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.

 

[Matthew Woodcraft]

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]

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.

 

[Bruno Desthuilliers]

hdante a écrit :

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]

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.

 

[hdante]

hdante a écrit :





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.

Why is that so bad ?

"But wait !, you cry. Shouldn't the primary key of my orders table be
the order number or some other meaningful column ? Why use an
artificial primary key such as id ? The reason is largely a practical
one - the format of external data may change over time."
(...)
"Normally, Active Record takes care of creating new primary key
values for records that you create and add to the database - they'll
be ascending integers (possibily with some gaps in the sequence).
However, if you override the primary key column's name, you also take
on the responsibility of setting the primary key to a unique value
before you save a new row."
-- AWDWR

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

"Some object-relational mappers seek to eliminate the use of SQL
entirely, hoping for object-oriented purity by forcing all queries
through an OO layer. Active Record does not. It was built on the
notion that SQL is neither dirty nor bad, just verbose in the trivial
cases. (...) Therefore, you shouldn't feel guilty when you use
find_by_sql to handle either performance bottlenecks or hard queries.
Start out using the object-oriented interface for productivity and
pleasure, and then dip beneath the surface for a close-to-the-metal
experience when you need to do so."
-- AWDWR

PS. That's okay to use a RDBMS. What I don't want is to use two
programming paradigms, especially, considering the "object-relational
impedance mismatch".

 

[Aaron Watters]

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]

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

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

 

[castironpi]

 Why is that so bad ?

 "But wait !, you cry. Shouldn't the primary key of my orders table be
the order number or some other meaningful column ? Why use an
artificial primary key such as id ? The reason is largely a practical
one - the format of external data may change over time."
 (...)
 "Normally, Active Record takes care of creating new primary key
values for records that you create and add to the database - they'll
be ascending integers (possibily with some gaps in the sequence).
However, if you override the primary key column's name, you also take
on the responsibility of setting the primary key to a unique value
before you save a new row."
                                   -- AWDWR





 "Some object-relational mappers seek to eliminate the use of SQL
entirely, hoping for object-oriented purity by forcing all queries
through an OO layer. Active Record does not. It was built on the
notion that SQL is neither dirty nor bad, just verbose in the trivial
cases. (...) Therefore, you shouldn't feel guilty when you use
find_by_sql to handle either performance bottlenecks or hard queries.
Start out using the object-oriented interface for productivity and
pleasure, and then dip beneath the surface for a close-to-the-metal
experience when you need to do so."
                                   -- AWDWR

 PS. That's okay to use a RDBMS. What I don't want is to use two
programming paradigms, especially, considering the "object-relational
impedance mismatch".

I think a shelf can accomplish everything a RDMS can. Just set up
everything as a map from a real number, remove and extract at will
(between numbers), and use XML tags.

shelf[ 0.1 ]= '<data/>', 'code code code'
shelf[ 0.125 ]= '<name/>', 'castironpi'
shelf[ 0.05 ]= '<modifier/>', 'oddly enough'

-=>

<data> code code code </data>
<name> castironpi </name>
<modifier> oddly enough </modifier>

and

shelf[ 0.1 ]= '<data/>', 'code code code'
shelf[ 0.125 ]= '<name>', 'castironpi'
shelf[ 0.05 ]= '<modifier>', 'oddly enough'

-=>

<data> code code code </data>
<name> castironpi
<modifier> oddly enough </modifier>
</name>

Plus you can't have text and subnodes anyway.

 

[Paul Boddie]

[Quoting hdante]

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.

That's two candidates for quote of the week in the same thread!

I agree with those who question why you'd want to treat a relational
database like a big dictionary, and although the interface between
queries, results and program data structures can often seem quite
awkward, I've come to realise that most object-relational mappers are
solving the wrong problems: they pretend that the database is somehow
the wrong representation whilst being a fast enough black box for
holding persistent data (although I doubt that many people push the
boundaries enough to see that it's not possible to ignore all details
of such a database whilst preserving performance), or they pretend
that languages like SQL (which can be cumbersome, admittedly) are
inconvenient for querying whilst replicating a less concise mechanism
for querying using client language mechanisms.

I'm more encouraged by the idea of "query templating", which might
sound like a recipe for all sorts of problems, but if done right could
provide more effective ways of working with relational databases than
pretending that different things in the database are somehow "objects"
in the client language sense.

Paul

 

[Tim Golden]

... I've come to realise that most object-relational mappers are
solving the wrong problems: they pretend that the database is somehow
the wrong representation whilst being a fast enough black box for
holding persistent data (although I doubt that many people push the
boundaries enough to see that it's not possible to ignore all details
of such a database whilst preserving performance), or they pretend
that languages like SQL (which can be cumbersome, admittedly) are
inconvenient for querying whilst replicating a less concise mechanism
for querying using client language mechanisms.

<bit-of-a-rant>
Well at the risk of oversimplifying (!) I find there are two kinds
of programmers using databases: those, like me, for whom the database
is the application and who can happily envisage any number of
interfaces, human and otherwise, to the data; and those, like 70% of the
people I've ever interviewed for a job as a SQL developer, for whom
the interface is the application and who simply throw things at whatever
database they're presented with.

The former will more likely tend to reach first for SQL to retrieve
their data efficiently before passing it on to the front end for
presentation or manipulation. The latter (and I've seen this far
too often in interviews) will basically do "SELECT * FROM x WHERE y"
to pull everything back into their VB.Net app where they feel more
at home. Or, in the case of Python, reach for an ORM.

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.
</bit-of-a-rant>

TJG

 

[Luis M. González]

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

 

[Marco Mariani]

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]

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

 

[Bruno Desthuilliers]

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.

 

[Marco Mariani]

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]

Jarek Zgoda a écrit :

Bruno Desthuilliers napisa³(a):


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.

 

[Diez B. Roggisch]

Jarek Zgoda a écrit :

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

 

[Luis M. González]

Yes, webpy's db api can be used in stand-alone scripts if you want.
See below:

import web
db = web.database(dbn='mysql', db='northwind', user='root')
x = db.select('employees')
....

Another good thing is that, since queries return Storage objects
(similar to dictionaries), they are much more flexible.
Suppose that you get the results of a form sent via a POST method, and
you want to insert this data into your database.
You would simple write:

i = web.input()
db.insert('orders', **i)

So everything related to CRUD operations are is easy to do, without
having to mess with objects.
I think this sticks strictly to the KISS principle, keeping it simple,
with less overhead, less layers of abstraction and therefore, less
bugs and complications.
And it matchs perfectly webpy's philosofy for creating web apps.

Luis