python - 如何在 sqlalchemy 中删除表并重新创建它

Question

我正在尝试将表从远程 mysql 数据库复制到另一个数据库。这是第一次工作，但如果我再次运行它（这应该定期运行），表会被正确删除，但重新创建它会引发异常：

(MySQLdb._exceptions.ProgrammingError) (1146, "Table 'dbname.item_copy' doesn't exist")

这似乎有点奇怪，因为这正是我试图（重新）创建它的原因！我怎样才能解决这个问题？

这是完整的代码：

from sqlalchemy import create_engine, MetaData
from sqlalchemy import Column, Table
from sqlalchemy.orm import sessionmaker, close_all_sessions

REMOTE_MYSQL_HOST = "example.com"
REMOTE_MYSQL_USER = "-"
REMOTE_MYSQL_PASSWD = "-"
REMOTE_MYSQL_DB = "dbname"

LOCAL_MYSQL_USER = "-"
LOCAL_MYSQL_PASSWD = "-"
LOCAL_MYSQL_PORT = 3306
LOCAL_MYSQL_DB = "dbname"


def copy_table(source_session, dest_engine, dest_session, source_table):
    dest_table_name = f"{source_table.name}_copy"
    src_table_name = source_table.name
    print(f"copy from {src_table_name}")

    query = source_session.query(source_table)
    source_session.commit()

    metadata = MetaData(bind=dest_engine)
    columns = [Column(desc["name"], desc["type"]) for desc in query.column_descriptions]
    table = Table(dest_table_name, metadata, *columns)
    if table.exists():
        table.drop()  # Make sure we delete a previous copy if it exists!
    dest_session.commit()
    print(f"creating table {dest_table_name}")

    # This fails if the table was created before even
    # if the table was sucseffuly dropped above.
    table.create(dest_engine)
    dest_session.commit()

    print("copying rows..")
    N = 1000
    for row in query:
        dest_session.execute(table.insert(row))
        N -= 1
        if N == 0:
            dest_session.commit()
            N = 1000
    dest_session.commit()


def main():

    source_engine = create_engine(
        f"mysql://{REMOTE_MYSQL_USER}:{REMOTE_MYSQL_PASSWD}@{REMOTE_MYSQL_HOST}/{REMOTE_MYSQL_PORT}",
        encoding="utf8",
    )
    SourceSession = sessionmaker(bind=source_engine)
    dest_engine = create_engine(
        f"mysql://{LOCAL_MYSQL_USER}:{LOCAL_MYSQL_PASSWD}@127.0.0.1:{LOCAL_MYSQL_PORT}/{LOCAL_MYSQL_DB}",
        encoding="utf8",
    )
    DestSession = sessionmaker(bind=dest_engine)
    source_session = SourceSession()
    dest_session = DestSession()

    meta = MetaData()
    meta.reflect(bind=source_engine, views=True)

    copy_table(source_session, dest_engine, dest_session, meta.tables["Item"])

    close_all_sessions()


if __name__ == "__main__":
    main()

标签： pythonmysqlsqlalchemy