Attaching a second database to a connection

[Richard Damon]

SQLite allows a program to attach multiple databases to a single
connection, and you are able to reference tables in these additional
databases with things like schema.table as the name of a table.

Is there a way to do this in SQLAlchemy?

I am working on an application that will want to import data from
another database (that uses basically the same schema, maybe just a
subset of the schema of the main database), and get the updates needed
to perform by using a join on unique keys (that aren't necessarily the
primary key).

After finishing the update, and pulling the information in (remapping
rowid/primary keys <-> foreign keys that didn't match between the
databases) I would then detach this database (which ideally I opened as
a read only connection).

I can see how to establish multiple engines and sessions, but then I
can't do the join between the databases which would let me do a lot of
the work down in the database engine. I also have found being able to
bind different sets of tables into different engines, but in my case the
database will have the same set of tables, so this doesn't look to work.

--
Richard Damon

[Mike Bayer]

On Mon, Jul 6, 2020, at 11:19 PM, Richard Damon wrote:

SQLite allows a program to attach multiple databases to a single

connection, and you are able to reference tables in these additional

databases with things like schema.table as the name of a table.

Is there a way to do this in SQLAlchemy?

sure, you use SQLite's ATTACH DATABASE command, usually using an event so it occurs for all connections automatically, here is code from our test suite:

    from sqlalchemy import event

    engine = create_engine("sqlite://")

    @event.listens_for(engine, "connect")

    def connect(dbapi_connection, connection_record):

            dbapi_connection.execute(

                'ATTACH DATABASE "test_schema.db" AS test_schema'

            )

then you reference the attached database as a schema, Table(..., schema="test_schema")

I am working on an application that will want to import data from

another database (that uses basically the same schema, maybe just a

subset of the schema of the main database), and get the updates needed

to perform by using a join on unique keys (that aren't necessarily the

primary key).

After finishing the update, and pulling the information in (remapping

rowid/primary keys <-> foreign keys that didn't match between the

databases) I would then detach this database (which ideally I opened as

a read only connection).

I can see how to establish multiple engines and sessions, but then I

can't do the join between the databases which would let me do a lot of

the work down in the database engine. I also have found being able to

bind different sets of tables into different engines, but in my case the

database will have the same set of tables, so this doesn't look to work.

-- 

Richard Damon

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/93932507-f4b8-d378-8db6-28636d7a0825%40Damon-Family.org.

[Richard Damon]

Ok, I guess I knew you could execute explicit SQL but wasn't thinking
about it or coming across it in my searches. In my case I wouldn't want
to automatically connect, as it will be done at a specific time for a
specific operation, so I could do the ATTACH specifically.

Will I need to explicitly recreate all the schema for the tables? This
second database will have an identical schema to the main (in fact, it
will be generated by the program, as this is a way to bring in updates),
though maybe I won't bother setting up a full ORM model and be using
more explicit SQL to get the data.

Richard Damon

[Mike Bayer]

On Tue, Jul 7, 2020, at 8:11 AM, Richard Damon wrote:

Ok, I guess I knew you could execute explicit SQL but wasn't thinking

about it or coming across it in my searches. In my case I wouldn't want

to automatically connect, as it will be done at a specific time for a

specific operation, so I could do the ATTACH specifically.

OK, you can do the ATTACH on the database connection from the engine as you get it.   SQLite uses NullPool in any case so that command would be transitory once you close that connection.

Will I need to explicitly recreate all the schema for the tables? This

second database will have an identical schema to the main (in fact, it

will be generated by the program, as this is a way to bring in updates),

though maybe I won't bother setting up a full ORM model and be using

more explicit SQL to get the data.

if the database you're attaching already has the tables in it, then that's your "schema", i dont see what you would need to "recreate" assuming you mean emitting "CREATE TABLE" statements. 

oh, but if you are ATTACHing a blank database and you *want* it to have those tables, then yes, you need to emit CREATE TABLE for all of those.

SQLAlchemy isn't doing anything automatic here it just emits the SQL commands you tell it to, so at the general level think of this as working with the sqlite3 module directly, just that you have a tool to help you write some of the commands.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/037e7fb9-e6f1-df4f-6749-2d218d58dd5a%40Damon-Family.org.