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.
On 7/7/20 12:46 AM, Mike Bayer wrote:
>
>
> 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
>>
--
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+unsubscr...@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.
