Re: [sqlalchemy] Alembic and postgresql multiple schema question
On Sat, Sep 1, 2018 at 12:50 PM, sector119 wrote:
>
>> if you want individual migration sections for each schema, with or
>> without "schema" written in, there's ways to do all that also but that
>> doesn't seem necessary if you are sharing a single model with multiple
>> identical schemas.
>
>
> The problem is that some one might alter some tables at some schemas with
> plain sql in psql ((
> So it would be perfect to have individual migration sections for each
> schema, _with_ "schema" written in.
OK I had wondered if you would actually want that. but that means
if schemas have been altered manually like that, then the next
migration is going to show those changes being reversed, if you are OK
with that.
What we do now is start using techniques from the "multidb" template
that's included with Alembic.
Assume you added this to alembic.ini:
schema_names = test_schema, test_schema_2
then you can genrerate migrations for each schema individually like this:
import re
schema_names = re.split(r',\s*', config.get_main_option('schema_names'))
def run_migrations_online():
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix='sqlalchemy.',
poolclass=pool.NullPool)
with connectable.connect() as connection:
for schema_name in schema_names:
connection.execute("SET search_path TO %s" % schema_name)
connection.dialect.default_schema_name = schema_name
context.configure(
connection=connection,
target_metadata=target_metadata,
upgrade_token="%s_upgrades" % schema_name,
downgrade_token="%s_downgrades" % schema_name,
)
with context.begin_transaction():
context.run_migrations(schema_name=schema_name)
you also need to modify script.py.mako to render these sections:
def upgrade(schema_name):
globals()["upgrade_%s" % schema_name]()
def downgrade(schema_name):
globals()["downgrade_%s" % schema_name]()
<%
import re
schema_names = re.split(r',\s*', config.get_main_option('schema_names'))
%>
% for schema_name in schema_names:
def upgrade_${schema_name}():
${context.get("%s_upgrades" % schema_name, "pass")}
def downgrade_${schema_name}():
${context.get("%s_downgrades" % schema_name, "pass")}
% endfor
When you run an autogenerate, you will get this:
def upgrade(schema_name):
globals()["upgrade_%s" % schema_name]()
def downgrade(schema_name):
globals()["downgrade_%s" % schema_name]()
def upgrade_test_schema():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('t1',
sa.Column('id', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
def downgrade_test_schema():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('t1')
# ### end Alembic commands ###
def upgrade_test_schema_2():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('t1',
sa.Column('id', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
def downgrade_test_schema_2():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('t1')
# ### end Alembic commands ###
note that the "schema=" is still not present, but is not needed
since your env.py sets the search path to just that single schema, the
"system" schema is not included (and is not needed).When you run
migrations, that same logic occurs for migrations being run and you
each set of DDL will run within the scope of that SEARCH_PATH. I have
tested this and can confirm it works.
Now if you really *want* the "schema=" anyway, you can add it in
with a rewriter, like this:
with connectable.connect() as connection:
def process_revision_directives(context, revision, directives):
script = directives[0]
schema_name = context.opts['x_schema_name']
upgrade_ops = script.upgrade_ops_list[-1]
downgrade_ops = script.downgrade_ops_list[-1]
for op in upgrade_ops.ops + downgrade_ops.ops:
op.schema = schema_name
if hasattr(op, "ops"):
for sub_op in op.ops:
sub_op.schema = schema_name
for schema_name in ['test_schema', 'test_schema_2']:
conn = connection
conn.execute("SET search_path TO %s" % schema_name)
conn.dialect.default_schema_name = schema_name
context.configure(
connection=conn,
target_metadata=target_metadata,
Re: [sqlalchemy] Alembic and postgresql multiple schema question
> if you want individual migration sections for each schema, with or > without "schema" written in, there's ways to do all that also but that > doesn't seem necessary if you are sharing a single model with multiple > identical schemas. > The problem is that some one might alter some tables at some schemas with plain sql in psql (( So it would be perfect to have individual migration sections for each schema, _with_ "schema" written in. Thank you -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Alembic and postgresql multiple schema question
I've spent a long time evaluating this use case and there are a lot of
ways to do it, but ultimately you are saying each schema is identical,
and originally, you just wanted to set "search_path" to each schema,
which is probably how this should be done if each schema is
independent of each other and has the identical tables. This means
that when you autogenerate, you should only "autogenerate" for the
first schema since they are all identical. Then when migrations run,
you don't need to have "schema" set in your create_table because you
are setting search path, that will be the "default" schema.
for that exact use case, this works:
def run_migrations_online():
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix='sqlalchemy.',
poolclass=pool.NullPool)
with connectable.connect() as connection:
for schema_name in ['test_schema', 'test_schema_2']:
connection.execute("SET search_path TO %s" % schema_name)
connection.dialect.default_schema_name = schema_name
context.configure(
connection=connection,
target_metadata=target_metadata
)
with context.begin_transaction():
context.run_migrations()
if context.config.cmd_opts.cmd[0].__name__ == 'revision':
break
note I had to set the default_schema_name on the dialect since we are
changing the search path without re-initializing an engine.
if you want individual migration sections for each schema, with or
without "schema" written in, there's ways to do all that also but that
doesn't seem necessary if you are sharing a single model with multiple
identical schemas.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Alembic and postgresql multiple schema question
from __future__ import with_statement
from alembic import context
from sqlalchemy import engine_from_config, pool
from logging.config import fileConfig
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
from epsilon.models.meta import metadata
target_metadata = metadata
# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
schema_names = config.get_main_option('schemas')
def run_migrations_offline():
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url, target_metadata=target_metadata, literal_binds=True)
with context.begin_transaction():
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix='sqlalchemy.',
poolclass=pool.NullPool)
with connectable.connect() as connection:
for schema_name in schema_names.split():
conn = connection.execution_options(schema_translate_map={None:
schema_name})
print("Migrating schema %s" % schema_name)
context.configure(
connection=conn,
target_metadata=target_metadata
)
with context.begin_transaction():
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
четверг, 30 августа 2018 г., 16:09:35 UTC+3 пользователь Mike Bayer написал:
>
> On Thu, Aug 30, 2018 at 7:11 AM, sector119 > wrote:
> > Mike, but in run_migrations_online() I use conn =
> > connection.execution_options(schema_translate_map={None: schema_name})
> > But I get no schemas at resulting alembic/versions/file.py
>
> can you share your env.py
>
> >
> >
> > среда, 29 августа 2018 г., 20:46:07 UTC+3 пользователь Mike Bayer
> написал:
> >>
> >> On Wed, Aug 29, 2018 at 5:12 AM, sector119 wrote:
> >> > Hello
> >> >
> >> > I have N schemas with the same set of tables, 1 system schema with
> >> > users,
> >> > groups, ... tables and 6 schemas with streets, organizations,
> >> > transactions,
> >> > ... tables.
> >> > On those schemas tables I don't set __table_args__ = ({'schema':
> >> > SCHEMA},)
> >> > I just call dbsession.execute('SET search_path TO system, %s' %
> SCHEMA)
> >> > before sql queries.
> >> >
> >> > When I make some changes in my model structures I want to refactor
> table
> >> > in
> >> > all schemas using Alembic, how can I do that?
> >> > Maybe I can make some loop over my schemas somewhere?
> >>
> >> setting the search path is going to confuse SQLAlchemy's table
> >> reflection process, such that it assumes a Table of a certain schema
> >> does not require a "schema" argument, because it is already in the
> >> search path.
> >>
> >> Keep the search path set to "public", see
> >>
> >>
> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path.
>
>
> >> There is an option to change this behavior mentioned in that
> >> section called postgresql_ignore_search_path, however it isn't
> >> guaranteed to suit all use cases. if that makes your case work, then
> >> that would be all you need. if not, then read on...
> >>
> >> For the officially supported way to do this, you want to have the
> >> explicit schema name inside the SQL - but this can be automated for a
> >> multi-tenancy application. Use the schema translation map feature:
> >>
> >>
> http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execution_options#schema-translating.
>
>
> >>
> >>
> >> >
> >> >
> >> > Thanks
> >> >
> >> > --
> >> > 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
Re: [sqlalchemy] Alembic and postgresql multiple schema question
On Thu, Aug 30, 2018 at 7:11 AM, sector119 wrote:
> Mike, but in run_migrations_online() I use conn =
> connection.execution_options(schema_translate_map={None: schema_name})
> But I get no schemas at resulting alembic/versions/file.py
can you share your env.py
>
>
> среда, 29 августа 2018 г., 20:46:07 UTC+3 пользователь Mike Bayer написал:
>>
>> On Wed, Aug 29, 2018 at 5:12 AM, sector119 wrote:
>> > Hello
>> >
>> > I have N schemas with the same set of tables, 1 system schema with
>> > users,
>> > groups, ... tables and 6 schemas with streets, organizations,
>> > transactions,
>> > ... tables.
>> > On those schemas tables I don't set __table_args__ = ({'schema':
>> > SCHEMA},)
>> > I just call dbsession.execute('SET search_path TO system, %s' % SCHEMA)
>> > before sql queries.
>> >
>> > When I make some changes in my model structures I want to refactor table
>> > in
>> > all schemas using Alembic, how can I do that?
>> > Maybe I can make some loop over my schemas somewhere?
>>
>> setting the search path is going to confuse SQLAlchemy's table
>> reflection process, such that it assumes a Table of a certain schema
>> does not require a "schema" argument, because it is already in the
>> search path.
>>
>> Keep the search path set to "public", see
>>
>> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path.
>> There is an option to change this behavior mentioned in that
>> section called postgresql_ignore_search_path, however it isn't
>> guaranteed to suit all use cases. if that makes your case work, then
>> that would be all you need. if not, then read on...
>>
>> For the officially supported way to do this, you want to have the
>> explicit schema name inside the SQL - but this can be automated for a
>> multi-tenancy application. Use the schema translation map feature:
>>
>> http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execution_options#schema-translating.
>>
>>
>> >
>> >
>> > Thanks
>> >
>> > --
>> > 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 post to this group, send email to sqlal...@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Alembic and postgresql multiple schema question
Mike, but in run_migrations_online() I use conn =
connection.execution_options(schema_translate_map={None: schema_name})
But I get no schemas at resulting alembic/versions/file.py
среда, 29 августа 2018 г., 20:46:07 UTC+3 пользователь Mike Bayer написал:
>
> On Wed, Aug 29, 2018 at 5:12 AM, sector119 > wrote:
> > Hello
> >
> > I have N schemas with the same set of tables, 1 system schema with
> users,
> > groups, ... tables and 6 schemas with streets, organizations,
> transactions,
> > ... tables.
> > On those schemas tables I don't set __table_args__ = ({'schema':
> SCHEMA},)
> > I just call dbsession.execute('SET search_path TO system, %s' % SCHEMA)
> > before sql queries.
> >
> > When I make some changes in my model structures I want to refactor table
> in
> > all schemas using Alembic, how can I do that?
> > Maybe I can make some loop over my schemas somewhere?
>
> setting the search path is going to confuse SQLAlchemy's table
> reflection process, such that it assumes a Table of a certain schema
> does not require a "schema" argument, because it is already in the
> search path.
>
> Keep the search path set to "public", see
>
> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path.
>
>
> There is an option to change this behavior mentioned in that
> section called postgresql_ignore_search_path, however it isn't
> guaranteed to suit all use cases. if that makes your case work, then
> that would be all you need. if not, then read on...
>
> For the officially supported way to do this, you want to have the
> explicit schema name inside the SQL - but this can be automated for a
> multi-tenancy application. Use the schema translation map feature:
>
> http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execution_options#schema-translating.
>
>
>
>
> >
> >
> > Thanks
> >
> > --
> > 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 post to this group, send email to sqlal...@googlegroups.com
> .
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Alembic and postgresql multiple schema question
On Wed, Aug 29, 2018 at 5:12 AM, sector119 wrote:
> Hello
>
> I have N schemas with the same set of tables, 1 system schema with users,
> groups, ... tables and 6 schemas with streets, organizations, transactions,
> ... tables.
> On those schemas tables I don't set __table_args__ = ({'schema': SCHEMA},)
> I just call dbsession.execute('SET search_path TO system, %s' % SCHEMA)
> before sql queries.
>
> When I make some changes in my model structures I want to refactor table in
> all schemas using Alembic, how can I do that?
> Maybe I can make some loop over my schemas somewhere?
setting the search path is going to confuse SQLAlchemy's table
reflection process, such that it assumes a Table of a certain schema
does not require a "schema" argument, because it is already in the
search path.
Keep the search path set to "public", see
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path.
There is an option to change this behavior mentioned in that
section called postgresql_ignore_search_path, however it isn't
guaranteed to suit all use cases. if that makes your case work, then
that would be all you need. if not, then read on...
For the officially supported way to do this, you want to have the
explicit schema name inside the SQL - but this can be automated for a
multi-tenancy application. Use the schema translation map feature:
http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execution_options#schema-translating.
>
>
> Thanks
>
> --
> 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 post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Alembic and postgresql multiple schema question
Hello
I have N schemas with the same set of tables, 1 system schema with users,
groups, ... tables and 6 schemas with streets, organizations, transactions,
... tables.
On those schemas tables I don't set __table_args__ = ({'schema': SCHEMA},)
I just call dbsession.execute('SET search_path TO system, %s' % SCHEMA)
before sql queries.
When I make some changes in my model structures I want to refactor table in
all schemas using Alembic, how can I do that?
Maybe I can make some loop over my schemas somewhere?
Thanks
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
