python SQLAlchemy 很复杂？

SQLAlchemy is convoluted?

提问by dbr

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

这可能看起来相当有争议，但我刚刚浏览了 SQLAlchemy 的ORM 教程并最终得到了以下代码：

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:

这对于有效的 Hello World 表来说似乎非常复杂，尤其是与大致相似的 SQLObject 代码相比：

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?

我知道 SQLAlchemy “更强大”，但这种功能似乎是有代价的，还是我错过了什么？

回答by Steven

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:

从您的示例中省略实际使用的 orm 部分，代码可能如下所示：

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_tableyourself. 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 Sessionwithout 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)

“声明性”扩展负责定义表并将其映射到您的类，因此您不需要users_table自己声明。User 类还允许使用关键字参数进行实例化，例如User(name="foo"),（但不是位置参数）。我还添加了对 scoped_session 的使用，这意味着您可以直接使用Session而无需实际实例化它（如果当前线程中尚不存在会话，它将实例化一个新会话，否则将重用现有会话）

回答by Jacob Gabrielson

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

您提供的代码示例不是苹果对苹果的。SQLAlchemy 版本可以减少一点：

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 Elixirmore like SQLObject (but since I haven't used either, that's just a guess).

您可能还会发现Elixir更像 SQLObject（但由于我没有使用过，这只是猜测）。

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.

根本没有使用过 SQLObject，我无法评论 SA 究竟在哪些方面做得更好。但是我在 SA 方面有着丰富的经验，尤其是在处理复杂的、真实的、遗留的模式时。默认情况下，它在提出良好的 SQL 查询方面做得很好，并且有很多方法可以调整它们。

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

我发现 SQLAlchemy 作者的电梯演讲在实践中非常有效。

回答by Joe L.

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.

使用过 SQLObject（并且只阅读了 SQLAlchemy）后，我可以说 SQLObject 的优势之一是您可以轻松简单地完成任务。此外，电子邮件组 ( https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss)提供了出色的支持，可以很快得到答复。

回答by Tyler Long

Try Quick ORM, it is even simpler:

试试Quick ORM，它更简单：

from quick_orm.core import Database
from sqlalchemy import Column, String

class User(object):
    __metaclass__ = Database.DefaultMeta
    name = Column(String(30))

if __name__ == '__main__':
    database = Database('sqlite://')
    database.create_tables()

    user = User(name = 'Hello World')
    database.session.add_then_commit(user)

    user = database.session.query(User).get(1)
    print 'My name is', user.name

Quick ORMis built upon SQLAlchemy, so we could say that SQLAlchemy could be as simple as SQLObject.

Quick ORM是建立在 SQLAlchemy 之上的，所以我们可以说 SQLAlchemy 可以像 SQLObject 一样简单。

回答by Nico

Well, SQLAlchemy is divided into different parts, the main core part simply handles the DB, transforming your python 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.

好吧，SQLAlchemy 分为不同的部分，主要的核心部分只是处理 DB，将您的 Python 构建的查询转换为底层 DB 的适当 SQL 语言。然后是对会话、orm 和新的声明性语法的支持。

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.

看起来像 SQLObject（我不能肯定地说，很多年没有使用它，即使那样，也只使用过一次）跳过了大部分并立即执行 ORM 部分。这通常使简单数据的事情变得更容易（在大多数情况下你可以逃脱），但 SQLAlchemy 允许更复杂的数据库布局，如果你真的需要它，可以使用数据库。

回答by Tom Willis

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?

你说“令人费解”......其他人可能会说“灵活”。有时你需要它有时你不需要。你有选择是不是很棒？