事务和 sqlalchemy [英] Transactions and sqlalchemy

问题描述

我想弄清楚如何在 Python 3 中使用 SQLAlchemy 将许多(以 100k 的顺序)记录插入到数据库中.一切都指向使用事务.但是，我对如何做到这一点感到有些困惑.

I am trying to figure out how to insert many (in the order of 100k) records into a database using SQLAlchemy in Python 3. Everything points to using transactions. However, I am slightly confused as to how that is done.

有些页面说你从 connection.begin() 得到一个交易，其他地方说它是 session.begin() 而这个页面 here 说它是不存在的 session.create_transaction() .

Some pages state that you get a transaction from connection.begin(), others places say it is session.begin() and this page here says it is session.create_transaction() which doesn't exist.

这是我想要做的:

def addToTable(listOfRows):
    engine = create_engine('postgresql+pypostgresql:///%s' % db,echo = False)
    Session = sessionmaker(bind = engine)
    session = Session()
    table = myTable(engine,session)

    for row in listOfRows:
       table.add(row)
    table.flush() ### ideally there would be a counter and you flush after a couple of thousand records


class myTable:

    def __init__(self,engine,session):
       self.engine  = engine
       self.session = session
       self.transaction =createTransaction()# Create transaction code here

   def add(self,row):
       newRow = tableRow(row) ## This just creates a representation of a row in the DB
       self.transaction.add(newRow)
       self.transaction.flush()

   def flush(self):
       self.transaction.commit()

推荐答案

我强烈建议您在继续使用 SQLAlchemy 之前完成这两个教程.他们真的很有帮助并解释了许多概念.之后，我建议您阅读使用会话，然后继续解释会话如何适合进入所有这些.

I highly suggest that you do both tutorials before continuing on your trip with SQLAlchemy. They are really helpful and explain many concepts. Afterwards, I suggest you read Using the Session as this then goes on to explain how the session fits into all of this.

对于您的问题，有两种解决方案:一种使用 ORM，另一种使用 Core.前者更容易，后者更快.我们先走容易的路.事务仅用于将所有语句包装到单个操作中.也就是说，如果某些事情失败了，您可以中止所有事情，并且不会在两者之间留下任何东西.所以你很可能想要一个交易，但没有交易它也能工作.这是最快的方法:

To your problem, there are two solutions: One using the ORM and the other using the Core. The former is easier, the latter is faster. Let's take the easy road first. A transaction is only used to wrap all your statements into a single operation. That is, if something fails, you can abort all of it and are not left with something somewhere in between. So you most likely want a transaction, but it would work without one. Here is the quickest way:

with session.begin():
    session.add_all([tableRow(row) for row in listOfRows])

根据您的数据，SQLAlchemy 甚至可以优化您的 INSERT 语句，使其一次执行多个.这是发生了什么:

Depending on your data SQLAlchemy might even be able to optimize your INSERT statement in such a way that it executes multiple at a time. Here is what's going on:

• 使用 session.begin
启动事务
• 添加数据(使用add_all，但有多个add 的循环也可以)
• 会话已提交.如果此处出现问题，事务将中止，您可以修复错误.

• A transaction is started using session.begin
• The data is added (using add_all, but a loop with multiple add would also be fine)
• The session is committed. If something goes wrong here, the transaction will be aborted and you can fix the error.

所以这显然是一个好方法，但它不是最快的方法，因为 SQLAlchemy 必须通过所有可能产生一些开销的 ORM 算法.如果这是一次性数据库初始化，则可以避免使用 ORM.在这种情况下，不是创建 ORM 类 (tableRow)，而是创建一个包含所有键的字典(如何取决于数据).您可以再次使用上下文管理器:

So this is clearly a good way, but it is not the fastest way, because SQLAlchemy has to go through all the ORM algorithms which can produce somewhat of an overhead. If this is a one-time database initialization, you can avoid the ORM. In that case, instead of creating an ORM class (tableRow), you create a dictionary with all keys (how depends on the data). Again you can use a context manager:

with engine.begin() as connection:
    connection.execute(tableRow.__table__.insert().
                       values([row_to_dict(row) for row in listOfRows]))

这很可能会稍微快一点，但也不太方便.它的工作方式与上面的会话相同，只是它从 Core 而不是 ORM 构造语句.

This would most likely be slightly faster but also less convenient. It works the same way as the session above only that it constructs the statement from the Core and not the ORM.

这篇关于事务和 sqlalchemy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！