Thread: little_pger.py (a thin layer a tad above SQL and psycopg2)

little_pger.py (a thin layer a tad above SQL and psycopg2)

From
Christian Jauvin
Date:
Hi,

A couple of years ago, when I first studied the possibility of using a
Python ORM for a project, I rapidly got the impression that they were
(1) too heavy and complicated and (2) abstracting away SQL in an
"unhealthy" way. I think I was also influenced by this blog article:

http://database-programmer.blogspot.ca/2008/06/why-i-do-not-use-orm.html

Maybe that judgment was a bit quick, but as I always liked pure SQL
anyway, I decided to stay happy with psycopg2, and I never looked
back.

However, as I was developing CRUD web apps, where you're basically
exchanging JSON data between the UI and the database, I began hiding
the manipulations required to compose my queries with simple
functions, all operating on plain data structures, and using psycopg2:

https://github.com/cjauvin/little_pger

If someone has time and interest for it, I'd really appreciate some
opinion about such an approach. Does it make sense, is it useful? Are
there use cases for it, beside mine? As I've always considered the
idea almost trivial, I spent some time searching for an equivalent
implementation, but never really found one, to my surprise. I don't
know about it much, but I got the impression that SQLAlchemy Core
would be the closest (and quite more powerful of course), but I don't
really know yet.

Thanks for your time,

Christian


Re: little_pger.py (a thin layer a tad above SQL and psycopg2)

From
"A.M."
Date:
On Dec 18, 2012, at 12:27 PM, Christian Jauvin wrote:

> Hi,
>
> A couple of years ago, when I first studied the possibility of using a
> Python ORM for a project, I rapidly got the impression that they were
> (1) too heavy and complicated and (2) abstracting away SQL in an
> "unhealthy" way. I think I was also influenced by this blog article:
>
> http://database-programmer.blogspot.ca/2008/06/why-i-do-not-use-orm.html
>
> Maybe that judgment was a bit quick, but as I always liked pure SQL
> anyway, I decided to stay happy with psycopg2, and I never looked
> back.
>
> However, as I was developing CRUD web apps, where you're basically
> exchanging JSON data between the UI and the database, I began hiding
> the manipulations required to compose my queries with simple
> functions, all operating on plain data structures, and using psycopg2:
>
> https://github.com/cjauvin/little_pger


I think this is very similar in spirit to SQLAlchemy's SQL expression library
http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.htmland esqueleto http://hackage.haskell.org/package/esqueleto
whichare domain-specific languages which compile to SQL. I, too, have been looking for something lightweight and quick
whichwould allow nearly-automatic JSON generation from database rows. The trickier part is recursing a model attribute
treewhich always leads me back to SQLAlchemy. 

Cheers,
M





Re: little_pger.py (a thin layer a tad above SQL and psycopg2)

From
Rory Campbell-Lange
Date:
On 19/12/12, A.M. (agentm@themactionfaction.com) wrote:
> ...I, too, have been looking for something lightweight and quick which
> would allow nearly-automatic JSON generation from database rows. The
> trickier part is recursing a model attribute tree which always leads
> me back to SQLAlchemy.

Since psycopg can return namedtuples and simplejson serialises
namedtuples, this should not be a difficult thing to do.

Rory


Re: little_pger.py (a thin layer a tad above SQL and psycopg2)

From
"A.M."
Date:
On Dec 19, 2012, at 11:53 AM, Rory Campbell-Lange wrote:

> On 19/12/12, A.M. (agentm@themactionfaction.com) wrote:
>> ...I, too, have been looking for something lightweight and quick which
>> would allow nearly-automatic JSON generation from database rows. The
>> trickier part is recursing a model attribute tree which always leads
>> me back to SQLAlchemy.
>
> Since psycopg can return namedtuples and simplejson serialises
> namedtuples, this should not be a difficult thing to do.


That doesn't help when there are columns or subselects which need to become part of a one-to-many or many-to-many
relationshiprepresented as arrays attached to the main model in JSON. That is what I meant by "recursing the model
attributetree". 

Cheers,
M





Re: little_pger.py (a thin layer a tad above SQL and psycopg2)

From
Rory Campbell-Lange
Date:
On 19/12/12, A.M. (agentm@themactionfaction.com) wrote:
>
> On Dec 19, 2012, at 11:53 AM, Rory Campbell-Lange wrote:
>
> > On 19/12/12, A.M. (agentm@themactionfaction.com) wrote:
> >> ...I, too, have been looking for something lightweight and quick which
> >> would allow nearly-automatic JSON generation from database rows. The
> >> trickier part is recursing a model attribute tree which always leads
> >> me back to SQLAlchemy.
> >
> > Since psycopg can return namedtuples and simplejson serialises
> > namedtuples, this should not be a difficult thing to do.
>
>
> That doesn't help when there are columns or subselects which need to
> become part of a one-to-many or many-to-many relationship represented
> as arrays attached to the main model in JSON. That is what I meant by
> "recursing the model attribute tree".

Ah -- we do that sort of thing in the database.