OK one more addition to the recipe, please do this:
DB_SCHEMA = "my_foo_schema"
with connectable.connect() as connection:
connection.execute(f'create schema if not exists {DB_SCHEMA}')
connection.execute(f'set search_path to {DB_SCHEMA}')
connection.dialect.default_schema_name = DB_SCHEMA
will work completely
alternatively you can use an on connect event on the engine to do the same
thing.
On Fri, Mar 20, 2020, at 10:48 AM, Brian Hill wrote:
> this is what i tried but it generated the whole schema:
> .
> with connectable.connect() as connection:
>
> connection.execute(f'create schema if not exists {DB_SCHEMA}')
> connection.execute(f'set search_path to {DB_SCHEMA}')
>
> context.configure(
> connection=connection,
> target_metadata=metadata,
> )
>
> with context.begin_transaction():
> context.run_migrations()
>
> it works when my schema is 'public', when it matches metadata. my solution
> will be to set my schema to 'public' and use this simple env.py.
>
> thanks,
>
> brian
>
> On Friday, March 20, 2020 at 9:49:52 AM UTC-4, Mike Bayer wrote:
>> I just realized that you really dont need to even use schema_translate_map
>> at all here. If you use a completely straight env.py, and simply set
>> search_path=SCHEMA, you should be able to run Alembic in entirely
>> "schemaless" mode; don't include schemas anywhere nor would you need to set
>> it for the alembic version table. Postgresql will also CREATE/ ALTER in that
>> schema as well. As long as your environment runs completely in just one
>> schema at a time, this can be set up entirely at the connection level.
>>
>>
>> On Thu, Mar 19, 2020, at 9:15 PM, Brian Hill wrote:
>>>
>>>
>>> On Thursday, March 19, 2020 at 8:20:06 PM UTC-4, Mike Bayer wrote:
>>>>
>>>>
>>>> On Thu, Mar 19, 2020, at 7:41 PM, Brian Hill wrote:
>>>>>
>>>>>
>>>>> On Thursday, March 19, 2020 at 7:19:08 PM UTC-4, Mike Bayer wrote:
>>>>>> so let me get this straight:
>>>>>>
>>>>>> 1. you have many schemas
>>>>>>
>>>>>
>>>>> yes
>>>>>
>>>>>>
>>>>>> 2. you want to run autogenerate only once
>>>>>
>>>>> yes
>>>>>
>>>>>> 3. you want your mirations genrated with None for schema
>>>>>
>>>>> yes
>>>>>
>>>>>> 4. *HOWEVER*, when you run autogenerate, you are picking *one* (random?
>>>>>> arbitrary?) schema to use as the target, is that right?
>>>>>
>>>>> yes one, but it won't be arbitrary, it will be a development schema
>>>>> generated from the latest version (prior to the change we want to
>>>>> autogenerate).
>>>>>
>>>>>>
>>>>>>
>>>>>> As it stands, your autogenerate is not going to look in any schema
>>>>>> except "public", assuming default PG search path, because you did not
>>>>>> set "include_schemas=True".
>>>>>>
>>>>>
>>>>> i had it set to True and it behaves the same. i just took it out based on
>>>>> your previous suggestion, but again, it behaves the same.
>>>>>
>>>>>>
>>>>>> If you truly want autogenerate to look at one schema and only that
>>>>>> schema, and generate everything as non-schema qualified, then I would
>>>>>> simply set the default schema in PG to that schema name using
>>>>>> search_path:
>>>>>>
>>>>>> connection.execute("SET search_path TO my_schema")
>>>>>>
>>>>>> that way everything Alembic reflects will be from "my_schema" and it
>>>>>> will see the schema as blank, and it should generate as such. you might
>>>>>> need to disable the schema translate map when autogenerate runs but try
>>>>>> it without doing that first.
>>>>>>
>>>>>
>>>>> it's still generating the entire schema in the revision file.
>>>>>
>>>>> here's the updated section and it behaves the same with/without schema
>>>>> translate map:
>>>>>
>>>>> with connectable.connect() as connection:
>>>>>
>>>>> # create DB_SCHEMA if it doesn't exist
>>>>> connection.execute(f'create schema if not exists {DB_SCHEMA}')
>>>>>
>>>>> # map metadata schema (None) to DB_SCHEMA
>>>>> # connection =
>>>>> connection.execution_options(schema_translate_map={None:DB_SCHEMA})
>>>>>
>>>>> connection.execute(f"set search_path to {DB_SCHEMA}")
>>>>>
>>>>> # set alembic version table location in DB_SCHEMA
>>>>> context.configure(
>>>>> connection=connection,
>>>>> include_schemas=True,
>>>>> target_metadata=metadata,
>>>>> version_table_schema=DB_SCHEMA,
>>>>> )
>>>>>
>>>>> with context.begin_transaction():
>>>>> context.run_migrations()
>>>>>
>>>>>
>>>>
>>>>
>>>> no, don't use "include_schemas". you want to run alembic in a mode where
>>>> it has no idea there are other schemas to use. i think
>>>> version_table_schema is OK here but don't use include_schemas. Also turn
>>>> on INFO or DEBUG logging for the sqlalchemy logger to see exactly what
>>>> tables it is reflecting.
>>>
>>> same behavior, entire schema generated. i'll look at using INFO and DEBUG
>>> tomorrow to get a better idea of what's going on, but using public as my
>>> dev schema to autogenerate against works. i just have to exclude the
>>> alembic tables.
>>>
>>> again thanks for your help in understanding what i was trying to do.
>>>
>>> brian
>>>
>>>>
>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Thu, Mar 19, 2020, at 7:09 PM, Brian Hill wrote:
>>>>>>> Here's my env.py. Thanks for the help.
>>>>>>> Brian
>>>>>>>
>>>>>>> On Thursday, March 19, 2020 at 5:37:38 PM UTC-4, Mike Bayer wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>> On Thu, Mar 19, 2020, at 5:30 PM, Brian Hill wrote:
>>>>>>>>> Are there known issues with using autogenerate with multi-tenant
>>>>>>>>> (schema_translate_map)?
>>>>>>>>
>>>>>>>> it's complicated and not one-size-fits-all, if you consider that to be
>>>>>>>> an issue
>>>>>>>>
>>>>>>>>
>>>>>>>>>
>>>>>>>>> My metadata doesn't have a schema and I my
>>>>>>>>> schema_translate_map={None:'my_schema'}.
>>>>>>>>>
>>>>>>>>> This works for migrations but when I use autogenerate the generated
>>>>>>>>> revision file is the full schema and not the diff.
>>>>>>>>>
>>>>>>>>> It's detecting the alembic version table in the tenant schema. If I
>>>>>>>>> run the autogenerate a second time with the new revision file it
>>>>>>>>> fails saying Taget database is not up to date.
>>>>>>>>
>>>>>>>> are you setting include_schemas=True in your environment? I'd probably
>>>>>>>> not do this. Can't help much more without a complete and concise
>>>>>>>> working example of your env.py, please remove all extraneous details.
>>>>>>>>
>>>>>>>>
>>>>>>>>>
>>>>>>>>> Thanks,
>>>>>>>>>
>>>>>>>>> Brian
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> You received this message because you are subscribed to the Google
>>>>>>>>> Groups "sqlalchemy-alembic" group.
>>>>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>>>>> send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>>>>>>>>> To view this discussion on the web visit
>>>>>>>>> https://groups.google.com/d/msgid/sqlalchemy-alembic/5011240c-ba09-4423-9a05-0e6d2481dadf%40googlegroups.com
>>>>>>>>>
>>>>>>>>> <https://groups.google.com/d/msgid/sqlalchemy-alembic/5011240c-ba09-4423-9a05-0e6d2481dadf%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> You received this message because you are subscribed to the Google
>>>>>>> Groups "sqlalchemy-alembic" group.
>>>>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>>>>> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>>>>>>> To view this discussion on the web visit
>>>>>>> https://groups.google.com/d/msgid/sqlalchemy-alembic/fe6fddb4-25d6-4dd1-be3a-c1a174548bb4%40googlegroups.com
>>>>>>>
>>>>>>> <https://groups.google.com/d/msgid/sqlalchemy-alembic/fe6fddb4-25d6-4dd1-be3a-c1a174548bb4%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>>>>>>
>>>>>>>
>>>>>>> *Attachments:*
>>>>>>> * env.py
>>>>>>
>>>>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google Groups
>>>>> "sqlalchemy-alembic" group.
>>>>> To unsubscribe from this group and stop receiving emails from it, send an
>>>>> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>>>>> To view this discussion on the web visit
>>>>> https://groups.google.com/d/msgid/sqlalchemy-alembic/f6dcb50d-368c-4f8d-8e0b-af6f80d10084%40googlegroups.com
>>>>>
>>>>> <https://groups.google.com/d/msgid/sqlalchemy-alembic/f6dcb50d-368c-4f8d-8e0b-af6f80d10084%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy-alembic" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an
>>> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy-alembic/ef68d038-2452-4b29-960d-923df0ae844b%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy-alembic/ef68d038-2452-4b29-960d-923df0ae844b%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy-alembic/894af588-f022-4a96-9cfb-a5152b819af4%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy-alembic/894af588-f022-4a96-9cfb-a5152b819af4%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy-alembic/ef09cf54-9b7a-4fba-b4fd-c7f65dad1ed0%40www.fastmail.com.
