Is there something easier than ORM?

[Ò»Ê×Ê«]

Hi all,

Recently I am studying some python ORM libraries, such as sqlalchemy.

These are very powerful technologies to handle database. But I think
my project are not complicated to enough to benefit from a complete
ORM system.

What I really want, is some easy ways to load data from database, and
change rows of data to list of named tuple, then I could send these
data to my client application.

I don't think I want these subtle behavior such as lazy load, auto
update, ect. in ORM.

So is there some libraries like that?

Or is there some tools that could generate code from database scheme
as I want?

 

[Kottiyath]

Hi all,

Recently I am studying some python ORM libraries, such as sqlalchemy.

These are very powerful technologies to handle database. But I think
my project are not complicated to enough to benefit from a complete
ORM system.

What I really want, is some easy ways to load data from database, and
change rows of data to list of named tuple, then I could send these
data to my client application.

I don't think I want these subtle behavior such as lazy load, auto
update, ect. in ORM.

So is there some libraries like that?

Or is there some tools that could generate code from database scheme
as I want?

If ORM is not used, you might have to contend with different tools for
different databases - and will have to write SQL commands.
Luckily everything follows DB-API2 properly. http://www.python.org/dev/peps/pep-0249/.
So you mostly dont have to change the commands to access DB.

For the different tools, this http://wiki.python.org/moin/DatabaseInterfaces
might be a good starting point.
For example - psycopg2 for postgresql is almost the default etc.

 

[Diez B. Roggisch]

Hi all,

Recently I am studying some python ORM libraries, such as sqlalchemy.

These are very powerful technologies to handle database. But I think
my project are not complicated to enough to benefit from a complete
ORM system.

What I really want, is some easy ways to load data from database, and
change rows of data to list of named tuple, then I could send these
data to my client application.

I don't think I want these subtle behavior such as lazy load, auto
update, ect. in ORM.

So is there some libraries like that?

Or is there some tools that could generate code from database scheme
as I want?

Sqlalchemy. You don't need to use the ORM-layer, and you can use
reflection to create schema-objects like tables.

Then you can use that to create SQL-queries simple & powerful, whilst
being DB-agnostic and having a road to start using the ORM if you
discover it is useful for you.

To be honest: if you can control the schema, I'd still go for an orm. I
for example use elixir. It makes the easy things *really* easy, and the
complicated ones ar still possible.


Diez

 

[Michele Simionato]

Hi all,

Recently I am studying some python ORM libraries, such as sqlalchemy.

These are very powerful technologies to handle database. But I think
my project are not complicated to enough to benefit from a complete
ORM system.

What I really want, is some easy ways to load data from database, and
change rows of data to list of named tuple, then I could send these
data to my client application.

I don't think I want these subtle behavior such as lazy load, auto
update, ect. in ORM.

So is there some libraries like that?

Or is there some tools that could generate code from database scheme
as I want?

I think there is room for a poor man toolkit, something in between
SQLAlchemy and raw DB API.
However, I am not aware of any, and for the moment I am using a custom
made solution.

 

[一首诗]

Thanks for your reply.

With sqlalchemy, an mapped must living in a session, you have no way
to disconnect it with its session.

For example :

#-------------------------------------
user = session.query(User).first()
session.expunge(user)
print user.name #Error here
#-------------------------------------

I just want to get an read-only copy of user disconnected with session
to avoid unexpected database operation.
But after expunge, properties of user is not accessible anymore.

BTW : why you choose elixir instead of sqlalchemy's own schema
definition style?
Doesn't including another library means more chances of bugs?

 

[Mike Driscoll]

Thanks for your reply.

With sqlalchemy, an mapped must living in a session, you have no way
to disconnect it with its session.

For example :

#-------------------------------------
user = session.query(User).first()
session.expunge(user)
print user.name   #Error here
#-------------------------------------

I just want to get an read-only copy of user disconnected with session
to avoid  unexpected database operation.
But after expunge, properties of user is not accessible anymore.

If you don't want any unexpected database operations, don't call flush
() or commit() or just call rollback() BEFORE you do any real
operations.

There is a good sqlalchemy mailing list where even the developers hang
out and answer questions. I'm sure they could point you in the right
direction too.

Mike

 

[Philip Semanchuk]

Hi all,

Recently I am studying some python ORM libraries, such as sqlalchemy.

These are very powerful technologies to handle database. But I think
my project are not complicated to enough to benefit from a complete
ORM system.

What I really want, is some easy ways to load data from database, and
change rows of data to list of named tuple, then I could send these
data to my client application.

I don't think I want these subtle behavior such as lazy load, auto
update, ect. in ORM.

So is there some libraries like that?

Or is there some tools that could generate code from database scheme
as I want?

As others have suggested, there are lower-level libraries for database
access, like the sqlite library included with Python >= 2.5.

The API wrapper I'm most familiar with (psycopg2) has some nice
features like returning Postgres text as Python strings and Postgres
ints as Python ints and Postgres arrays as Python lists. It can also
return rows as dictionaries keyed by column names. You get some nice
things for free.

I read someone's advice that one doesn't need an ORM for simple
projects but they become more useful as projects grow in complexity.
I'm not so sure of that. I felt like the opposite could well be true
-- the limitations inherent in a general purpose tool like an ORM are
less likely to be a problem in a small, simple project than a larger
one.

In short, I gather that others on this list are a lot more fond of
SqlAlchemy and ORMs in general than I am. Granted, my experience is
very limited. I tried to integrate SqlAlchemy in one project,
struggled for a long time to express how I wanted my tables joined,
and finally found that performance was bad compared to our homegrown
SQL. My boss and I were both very comfortable with SQL and were happy
to go back to writing our own SQL statements and coding a data access
layer.

I don't intend this as a criticism of SqlAlchemy. On the contrary I am
impressed by what it does. But I often see people promoting ORM as the
solution to all database access problems, and I certainly don't feel
like it is.

Good luck,
Philip

 

[Michele Simionato]

I don't intend this as a criticism of SqlAlchemy. On the contrary I am  
impressed by what it does. But I often see people promoting ORM as the  
solution to all database access problems, and I certainly don't feel  
like it is.

I am also not a big fan of ORM, especially in situations where you
have
performance issues and you are using database specific features. In
such situations
you don't care about portability, but you care about having your SQL
explicit,
so that you can run it directly under the profiler.

 

[Tim Golden]

Philip Semanchuk wrote:
[... snip comments on SqlAlchemy which could likewise apply
to other similar offerings ...]

I don't intend this as a criticism of SqlAlchemy. On the contrary I am
impressed by what it does. But I often see people promoting ORM as the
solution to all database access problems, and I certainly don't feel
like it is.

I'm with you: I'm very glad that so many people find SA (and Storm
and Mother & SQLObject etc. useful). Personally, I earn my living
designing, maintaining and coding for relational databases and I'm
simply more at home in native SQL.

If you're on Windows, pyodbc offers you a general spread of databases
and has quite a few conveniences such as named rows returned; it's
fairly robust and is actively maintained. I imagine that most people
who do this kind of thing long ago put together a simple wrapper
module (mine's imaginatively called "sql") which does just enough
to be useful but then gets out the way.

All that said, if you don't *want* to have to think about SQL
then something like SA + Elixir is about as useful a combination
as I've found.

TJG

 

[Robert Kern]

the reason i, at least, like sqlalchemy so much is for exactly the reasons
you outlined. unlike other orm solutions it doesn't force you to use orm.
you can also use sql directly - either as simple strings or by
constructing it via python methods (which can be a very powerful way of
programatically constructing sql commands that would be a nightmare to
write by hand). that gives you the flexibility to deal with each problem
in the way that feels most natural, without having to switch between tools
(in fact, i have mixed orm and "direct" sql in a single project with no
problems using sqlalchemy - reading from one database using sql and
writing to another using objects).

Me, too! I have often used SQLAlchemy as "a better DB-API" even if I don't touch
its ORM. I really don't know what I would have done in my past projects without
SQLAlchemy's table reflection and metadata introspection capabilities.

It does suffer some from TMTOWTDI, but there's usually a valid use case floating
around somewhere for each WTDI.

Its public image definitely suffers from the impression that it's "an ORM" that
can be compared on equal terms with packages that actually are just ORMs. I
describe it as a very powerful toolkit for solving a wide variety of problems
involving SQL databases. One of those tools happens to be an ORM.

--
Robert Kern

"I have come to believe that the whole world is an enigma, a harmless enigma
that is made terrible by our own mad attempt to interpret it as though it had
an underlying truth."
-- Umberto Eco

 

[Martin v. Löwis]

So is there some libraries like that?

I always use a DB-API implementation for the database I use,
i.e. psycopg/psycopg2.

Named tuples are really easy to provide:

class NamedTuple:
def __init__(self, names, values):
for name, value in izip(names, values):
setattr(self, name, value)

person_rows = "first last age".split()
def get_person(conn, id):
conn.execute("select first, last, age from person where id=%d"
% (id,))
return NamedTuple(person_rows, conn.fetchone())

Regards,
Martin

 

[alex23]

Its public image definitely suffers from the impression that it's "an ORM" that
can be compared on equal terms with packages that actually are just ORMs. I
describe it as a very powerful toolkit for solving a wide variety of problems
involving SQL databases. One of those tools happens to be an ORM.

I'm going to have to steal that description the next time I try to
sell a co-worker on the value of SQLAlchemy. There's always a strong
reaction against the mention of ORMs, generally along the lines of it
moving the programmer too far away from the real action. But my
experience is identical to both andrew's and your's; there is far far
more of value in SQLA than the ORM alone.

 

[M.-A. Lemburg]

Hi all,

Recently I am studying some python ORM libraries, such as sqlalchemy.

These are very powerful technologies to handle database. But I think
my project are not complicated to enough to benefit from a complete
ORM system.

What I really want, is some easy ways to load data from database, and
change rows of data to list of named tuple, then I could send these
data to my client application.

I don't think I want these subtle behavior such as lazy load, auto
update, ect. in ORM.

So is there some libraries like that?

Python has a DB-API standard for direct access to databases using
a very simple cursor-based approach:

http://www.python.org/dev/peps/pep-0249/

All the DB-API compatible modules provide such an interface:

http://wiki.python.org/moin/DatabaseInterfaces

Or is there some tools that could generate code from database scheme
as I want?

I am not aware of an automated tool for generating SQL queries
in form of Python functions, but it is certainly possible to write
one by tapping into the system tables of the database of your
choice.

Our database tools mxODBC and mxODBC Connect come with a set of
catalog methods that make such introspection very easy across
databases and platforms:

http://www.egenix.com/products/python/mxODBC/
http://www.egenix.com/products/python/mxODBCConnect/

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Feb 18 2009)________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface 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
http://www.egenix.com/company/contact/

 

[Kevin Dangoor (Mozilla)]

I'm going to have to steal that description the next time I try to
sell a co-worker on the value of SQLAlchemy. There's always a strong
reaction against the mention of ORMs, generally along the lines of it
moving the programmer too far away from the real action. But my
experience is identical to both andrew's and your's; there is far far
more of value in SQLA than the ORM alone.

I just saw this thread via the weekly Python URL email and wanted to
add one bit here. When I've been selling people on using SQLAlchemy,
one argument that I make is that if you're using a relational database
for storage but your program is using objects (and good Python
programs do!), then you're doing ORM. If you're not using SQLAlchemy
(or similar), you're likely doing ORM badly.

SQLAlchemy's SQL layer definitely makes it a different beast from most
ORMs.

Kevin