Is there something easier than ORM?

Discussion in 'Python' started by Ò»Ê×Ê«, Feb 17, 2009.

  1. Ò»Ê×Ê«

    Ò»Ê×Ê« Guest

    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?
    Ò»Ê×Ê«, Feb 17, 2009
    #1
    1. Advertising

  2. Ò»Ê×Ê«

    Kottiyath Guest

    Ò»Ê×Ê« wrote:
    > 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.
    Kottiyath, Feb 17, 2009
    #2
    1. Advertising

  3. 一首诗 schrieb:
    > 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
    Diez B. Roggisch, Feb 17, 2009
    #3
  4. On Feb 17, 1:27 pm, Ò»Ê×Ê« <> wrote:
    > 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.
    Michele Simionato, Feb 17, 2009
    #4
  5. 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?

    On Feb 17, 9:24 pm, "Diez B. Roggisch" <> wrote:
    > 一首诗 schrieb:
    >
    >
    >
    > > 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
    一首诗, Feb 17, 2009
    #5
  6. On Feb 17, 8:15 am, 一首诗 <> wrote:
    > 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






    >
    > BTW : why you choose elixir instead of sqlalchemy's own schema
    > definition style?
    > Doesn't including another library means more chances of bugs?
    >
    > On Feb 17, 9:24 pm, "Diez B. Roggisch" <> wrote:
    >
    > > 一首诗 schrieb:

    >
    > > > 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
    Mike Driscoll, Feb 17, 2009
    #6
  7. On Feb 17, 2009, at 7:27 AM, 一首诗 wrote:

    > 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
    Philip Semanchuk, Feb 17, 2009
    #7
  8. On Feb 17, 5:35 pm, Philip Semanchuk <> wrote:
    >
    > 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.
    Michele Simionato, Feb 17, 2009
    #8
  9. Ò»Ê×Ê«

    Tim Golden Guest

    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
    Tim Golden, Feb 17, 2009
    #9
  10. Ò»Ê×Ê«

    Robert Kern Guest

    On 2009-02-17 10:52, andrew cooke wrote:
    > Philip Semanchuk wrote:
    >> 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.

    >
    > 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
    Robert Kern, Feb 17, 2009
    #10
  11. > 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
    Martin v. Löwis, Feb 17, 2009
    #11
  12. Ò»Ê×Ê«

    alex23 Guest

    On Feb 18, 3:10 am, Robert Kern <> wrote:
    > 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.
    alex23, Feb 18, 2009
    #12
  13. On 2009-02-17 13:27, 一首诗 wrote:
    > 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)
    >>> 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 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/
    M.-A. Lemburg, Feb 18, 2009
    #13
  14. On Feb 17, 10:28 pm, alex23 <> wrote:
    > On Feb 18, 3:10 am, Robert Kern <> wrote:
    >
    > > 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.


    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
    Kevin Dangoor (Mozilla), Feb 26, 2009
    #14
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dm1608
    Replies:
    13
    Views:
    707
    Kevin Spencer
    Feb 24, 2006
  2. b83503104
    Replies:
    5
    Views:
    569
    KC Wong
    May 25, 2004
  3. Replies:
    12
    Views:
    189
    Michele Dondi
    May 3, 2007
  4. Richard
    Replies:
    11
    Views:
    158
    Dr John Stockton
    Aug 7, 2006
  5. Replies:
    0
    Views:
    76
Loading...

Share This Page