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.
>
> 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.
>
--
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/7b21233c-e909-4ac8-bffd-e29bb3aa5e8e%40www.fastmail.com.
