table.insert().from_select() from one engine bind to another?

[GM1]

Hi there,

I am attempting to replicate one SQL Server DB (db_a) in another SQL Server instance (db_b).
I have succeeded in creating engine objects for both dbs, created required Metadata and can replicate the table definitions from db_a to db_b correctly. The issue arises when I try to replicate the data using the following code:

tables = db_a.metadata.sorted_tables #Bound to (db_a)

for table in tables
    stmt = (table.insert(bind=db_b.engine).from_select(names=table.columns, 
    select=table.select()))
    db_b.engine.execute(stmt)

Does changing the bind above, in the insert() method change the bind of the table so that the subsequent select statement in from_select, is selecting from the empty target db_b?

Sorry if this question is easily answered but I am new to SQLAlchemy and can't quite get my head around this bit.

Any advice or feedback is greatly appreciated.

Thank you

[zzzeek]

SQLAlchemy sends SQL commands to the DBAPI connection and cursor objects using the cursor.execute() method seen here: https://www.python.org/dev/peps/pep-0249/#id15

this cursor and connection is connected to only one database at a time. While most databases support some means of linking two servers together so that you can communicate with multiple database servers over one connection, SQLAlchemy does not have any knowledge of that, but if you do have links between these two servers SQLAlchemy can be made to refer to them, if that's what you want to do.

The use of "bound metadata" here does not affect any of that, all it has done here is made the issue more confusing which is why bound metadata is being removed from SQLAlchemy. This feature has no real use and is an artifact of SQLAlchemy's earliest versions 15 years ago and I would stop using it entirely.

So what this means is that you can't run a single SQL statement on one connection and have it move data between two different database servers, unless you've arranged things for there to be a server-to-server "link" between these databases. if there is a "link" set up, you would use just one engine and then make use of the schema name feature to refer to one link or another, such as in this example: https://stackoverflow.com/a/11009289

Otherwise, for a "normal" scenario where you just want to move data between two databases without there being any link,, the best way to do this is to use SQL dump / restore utilities. To approximate this in SQLAlchemy with two Engine objects as you have stated, you would run the SELECT on one engine, get all the rows back into memory, and then run a separate INSERT with that data on the other engine.

[GM1]

Thanks for your prompt response and explanation. That makes sense, I had coded up a working solution with SQLAlchemy using your final suggested approach. I had hoped that I could save myself some code but it makes sense now that you can't use that approach. Thanks again