SQLAlchemy is convoluted?

Question

This may seems rather argumentative, but I just went through SQLAlchemy's ORM tutorial and ended up with the following code:

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)

metadata = MetaData()
users_table = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
    Column('password', String)
)

metadata.create_all(engine)

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
       return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

users_table = User.__table__
metadata = Base.metadata

Session = sessionmaker(bind=engine)
Session = sessionmaker()
Session.configure(bind=engine)  # once engine is available
session = Session()

# actually using the ORM isn't too bad..
ed_user = User('ed', 'Ed Jones', 'edspassword')
session.add(ed_user)

our_user = session.query(User).filter_by(name='ed').first() 
print our_user

session.add_all([
    User('wendy', 'Wendy Williams', 'foobar'),
    User('mary', 'Mary Contrary', 'xxg527'),
    User('fred', 'Fred Flinstone', 'blah')])

ed_user.password = 'f8s7ccs'

print session.dirty
print session.new
session.commit()

for instance in session.query(User).order_by(User.id): 
    print instance.name, instance.fullname

for name, fullname in session.query(User.name, User.fullname): 
    print name, fullname

This seems incredibly complicated for effectively a Hello World table, especially compared to the roughly similar SQLObject code:

from sqlobject import SQLObject, StringCol, sqlhub, connectionForURI

sqlhub.processConnection = connectionForURI('sqlite:/:memory:')

class Person(SQLObject):
    fname = StringCol()
    mi = StringCol(length=1, default=None)
    lname = StringCol()

Person.createTable()

p = Person(fname="John", lname="Doe")
p.mi = 'Q'
p2 = Person.get(1)
print p2
print p2 is p

I understand SQLAlchemy is "more powerful", but that power seems to come at a cost, or am I missing something?

Answer 1

you say "convoluted".... someone else might say "flexible". Sometimes you need it sometimes you don't. Isn't it awesome that you have a choice?

Answer 2

Well, SQLAlchemy is divided into different parts, the main core part simply handles the DB, transforming your ptyhon built queries into the appropriate SQL language for the underlying DB. Then there is the support for sessions, the orm, and the new declarative syntax.

Looks like SQLObject (I can't say for sure, haven't used it in many years, and even then, only once) skips most of it and does the ORM part straight away. This often makes things easier for simple data (which you can get away with in most cases), but SQLAlchemy allows for more complex db layouts, and get down and dirty with the db if you really need it.

Answer 3

Well, there is one thing you are missing: the tutorial you mention doesn't "build" a complete example, the different snippets of code are not meant to be concatenated into one source file. Rather, they describe the different ways the library can be used. No need to try and do the same thing over and over again yourself.

Leaving out the actually-using-the-orm part from your example, the code could look like this:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(engine))

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

Base.metadata.create_all()

The "declarative" extension takes care of defining the table and mapping it to your class, so you don't need to declare the users_table yourself. The User class will also allow instantiating with keyword arguments, like User(name="foo"), (but not positional arguments though). I've also added use of scoped_session, which means you can directly use Session without actually having to instantiate it (it will instantiate a new session if there isn't already one present in the current thread, or reuse the existing one otherwise)

Answer 4

The code examples you give aren't apples-to-apples. The SQLAlchemy version could be pared down a bit:

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String)
    fullname = Column('fullname', String)
    password = Column('password', String)

    def __repr__(self):
       return "" % (self.name, self.fullname, self.password)

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# actually using the ORM isn't too bad..
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)

our_user = session.query(User).filter_by(name='ed').first()

session.add_all([
    User(name='wendy', fullname='Wendy Williams', password='foobar'),
    User(name='mary', fullname='Mary Contrary', password='xxg527'),
    User(name='fred', fullname='Fred Flinstone', password='blah')])

ed_user.password = 'f8s7ccs'

session.flush()

for instance in session.query(User).order_by(User.id):
    print instance.name, instance.fullname

for name, fullname in session.query(User.name, User.fullname):
    print name, fullname

You might also find Elixir more like SQLObject (but since I haven't used either, that's just a guess).

Not having used SQLObject at all, I can't comment on what exactly SA does better. But I have had great experiences with SA, especially when dealing with complicated, real-world, legacy schemas. It does a good job of coming up with good SQL queries by default, and has lots of ways to tune them.

I've found SQLAlchemy author's elevator pitch to hold up pretty well in practice.

Answer 5

Having used SQLObject (and only read about SQLAlchemy), I can say that one of SQLObject's strengths is the ease and simplicity with which you can get things done. Also, excellent support is provided by the email group (https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss) that gets answers back to you pretty quickly.