SQLAlchemy is convoluted? [closed]

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

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 2

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.