小开

So what appears to work is reimplementing engine creation in env.py, which is apparently a place for doing this kind of customizing Instead of using the sqlalchemy connect string in the ini:

engine = engine_from_config(
config.get_section(config.config_ini_section),
prefix='sqlalchemy.',
poolclass=pool.NullPool)

You can replace and specify your own engine configuration:

import store
engine = store.engine

Indeed the docs seems to imply this is ok:

sqlalchemy.url - A URL to connect to the database via SQLAlchemy. This key is in fact only referenced within the env.py file that is specific to the “generic” configuration; a file that can be customized by the developer. A multiple database configuration may respond to multiple keys here, or may reference other sections of the file.

小开

Another solution is to create a template alembic.ini.dist file and to track it with your versionned code, while ignoring alembic.ini in your VCS.

Do not add any confidential information in alembic.ini.dist:

sqlalchemy.url = ...

When deploying your code to a platform, copy alembic.ini.dist to alembic.ini (this one won't be tracked by your VCS) and modify alembic.ini with the platform's credentials.

小开

I had the very same problem yesterday and found a following solution to work. I do the following in alembic/env.py:

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config


# this will overwrite the ini-file sqlalchemy.url path
# with the path given in the config of the main code
import config as ems_config
config.set_main_option('sqlalchemy.url', ems_config.config.get('sql', 'database'))

ems_config is an external module that holds my configuration data.

config.set_main_option(...) essentially overwrites the sqlalchemy.url key in the [alembic] section of the alembic.ini file. In my configuration I simply leave it black.

小开

As Doug T. said you can edit env.py to provide URL from somewhere else than ini file. Instead of creating new engine you can pass an additional url argument to the engine_from_config function (kwargs are later merged to options taken from ini file). In that case you could e.g. store encrypted password in ini file and decrypt it in runtime by passphrase stored in ENV variable.

connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix='sqlalchemy.',
poolclass=pool.NullPool,
url=some_decrypted_endpoint)

小开

Alembic documentation suggests using create_engine with the database URL (instead of modifying sqlalchemy.url in code).

Also you should modify run_migrations_offline to use the new URL. Allan Simon has an example on his blog, but in summary, modify env.py to:

Provide a shared function to get the URL somehow (here it comes from the command line):

def get_url():
url = context.get_x_argument(as_dictionary=True).get('url')
assert url, "Database URL must be specified on command line with -x url=<DB_URL>"
return url

Use the URL in offline mode:

def run_migrations_offline():
...
url = get_url()
context.configure(
url=url, target_metadata=target_metadata, literal_binds=True)
...

Use the URL in online mode by using create_engine instead of engine_from_config:

def run_migrations_online():
...
connectable = create_engine(get_url())
with connectable.connect() as connection:
...

小开

I was looking for a while how to manage this for mutli-databases

Here is what I did. I have two databases : logs and ohlc

According to the doc, I have setup the alembic like that

alembic init --template multidb

alembic.ini

databases = logs, ohlc
[logs]
sqlalchemy.url = postgresql://botcrypto:botcrypto@localhost/logs
[ohlc]
sqlalchemy.url = postgresql://botcrypto:botcrypto@localhost/ohlc

env.py

[...]
# 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)
logger = logging.getLogger('alembic.env')


# overwrite alembic.ini db urls from the config file
settings_path = os.environ.get('SETTINGS')
if settings_path:
with open(settings_path) as fd:
settings = conf.load(fd, context=os.environ) # loads the config.yml
config.set_section_option("ohlc", "sqlalchemy.url", settings["databases"]["ohlc"])
config.set_section_option("logs", "sqlalchemy.url", settings["databases"]["logs"])
else:
logger.warning('Environment variable SETTINGS missing - use default alembic.ini configuration')
[...]

config.yml

databases:
logs: postgresql://botcrypto:botcrypto@127.0.0.1:5432/logs
ohlc: postgresql://botcrypto:botcrypto@127.0.0.1:5432/ohlc

usage

SETTINGS=config.yml alembic upgrade head

Hope it can helps !

小开

The simplest thing I could come up with to avoid commiting my user/pass was to a) add in interpolation strings to the alembic.ini file, and b) set these interpolation values in env.py

alembic.ini

sqlalchemy.url = postgresql://%(DB_USER)s:%(DB_PASS)s@35.197.196.146/nozzle-website

env.py

import os


from logging.config import fileConfig


from sqlalchemy import engine_from_config
from sqlalchemy import pool


from alembic import context


# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config


# here we allow ourselves to pass interpolation vars to alembic.ini
# fron the host env
section = config.config_ini_section
config.set_section_option(section, "DB_USER", os.environ.get("DB_USER"))
config.set_section_option(section, "DB_PASS", os.environ.get("DB_PASS"))


...

小开

I've tried all the answer here, but not working. Then I try to deal by myself, as below:

.ini file:

# A generic, single database configuration.


[alembic]
# path to migration scripts
script_location = alembic


# template used to generate migration files
file_template = %%(rev)s_%%(year)d_%%(month).2d_%%(day).2d_%%(hour).2d_%%(minute).2d_%%(second).2d


# timezone to use when rendering the date
# within the migration file as well as the filename.
# string value is passed to dateutil.tz.gettz()
# leave blank for localtime
# timezone =


# max length of characters to apply to the
# "slug" field
#truncate_slug_length = 40


# set to 'true' to run the environment during
# the 'revision' command, regardless of autogenerate
# revision_environment = false


# set to 'true' to allow .pyc and .pyo files without
# a source .py file to be detected as revisions in the
# versions/ directory
# sourceless = false


# version location specification; this defaults
# to alembic/versions.  When using multiple version
# directories, initial revisions must be specified with --version-path
# version_locations = %(here)s/bar %(here)s/bat alembic/versions


# the output encoding used when revision files
# are written from script.py.mako
# output_encoding = utf-8


databases = auth_engine




[auth_engine]
sqlalchemy.url = mysql+mysqldb://{}:{}@{}:{}/auth_db


# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic


[handlers]
keys = console


[formatters]
keys = generic


[logger_root]
level = WARN
handlers = console
qualname =


[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine


[logger_alembic]
level = INFO
handlers =
qualname = alembic


[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic


[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

.env file(it is in the root folder of my project):

DB_USER='root'
DB_PASS='12345678'
DB_HOST='127.0.0.1'
DB_PORT='3306'

env.py file:

from __future__ import with_statement


import os
import re
import sys
from logging.config import fileConfig


from sqlalchemy import engine_from_config
from sqlalchemy import pool


from alembic import context


DB_USER = os.getenv("DB_USER")
DB_PASS = os.getenv("DB_PASS")
DB_HOST = os.getenv("DB_HOST")
DB_PORT = os.getenv("DB_PORT")


# 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)


# gather section names referring to different
# databases.  These are named "engine1", "engine2"
# in the sample .ini file.
db_names = config.get_main_option('databases')


# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata


sys.path.append(os.path.join(os.path.dirname(__file__), "../../../"))
from db_models.auth_db import auth_db_base


target_metadata = {
'auth_engine': auth_db_base.auth_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.




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.


"""
engines = {}
for name in re.split(r',\s*', db_names):
engines[name] = rec = {}
section = context.config.get_section(name)
url = section['sqlalchemy.url'].format(DB_USER, DB_PASS, DB_HOST, DB_PORT)
section['sqlalchemy.url'] = url
rec['url'] = url
# rec['url'] = context.config.get_section_option(name, "sqlalchemy.url")


for name, rec in engines.items():
print("Migrating database %s" % name)
file_ = "%s.sql" % name
print("Writing output to %s" % file_)
with open(file_, 'w') as buffer:
context.configure(url=rec['url'], output_buffer=buffer,
target_metadata=target_metadata.get(name),
compare_type=True,
compare_server_default=True
)


with context.begin_transaction():
context.run_migrations(engine_name=name)




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.


"""
engines = {}
for name in re.split(r',\s*', db_names):
engines[name] = rec = {}
section = context.config.get_section(name)
url = section['sqlalchemy.url'].format(DB_USER, DB_PASS, DB_HOST, DB_PORT)
section['sqlalchemy.url'] = url
rec['engine'] = engine_from_config(
section,
prefix='sqlalchemy.',
poolclass=pool.NullPool)


for name, rec in engines.items():
engine = rec['engine']
rec['connection'] = conn = engine.connect()
rec['transaction'] = conn.begin()


try:
for name, rec in engines.items():
print("Migrating database %s" % name)
context.configure(
connection=rec['connection'],
upgrade_token="%s_upgrades" % name,
downgrade_token="%s_downgrades" % name,
target_metadata=target_metadata.get(name),
compare_type=True,
compare_server_default=True
)
context.run_migrations(engine_name=name)


for rec in engines.values():
rec['transaction'].commit()
except:
for rec in engines.values():
rec['transaction'].rollback()
raise
finally:
for rec in engines.values():
rec['connection'].close()




if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()

Wish can help someone else.

小开

I was bumping into this problem as well since we're running our migrations from our local machines. My solution is to put environment sections in the alembic.ini which stores the database config (minus the credentials):

[local]
host = localhost
db = dbname


[test]
host = x.x.x.x
db = dbname


[prod]
host = x.x.x.x
db = dbname

Then I put the following in the env.py so the user can pick their environment and be prompted for the credentials:

from alembic import context
from getpass import getpass


...


envs = ['local', 'test', 'prod']


print('Warning: Do not commit your database credentials to source control!')
print(f'Available migration environments: {", ".join(envs)}')


env = input('Environment: ')


if env not in envs:
print(f'{env} is not a valid environment')
exit(0)


env_config = context.config.get_section(env)
host = env_config['host']
db = env_config['db']


username = input('Username: ')
password = getpass()
connection_string = f'postgresql://{username}:{password}@{host}/{db}'


context.config.set_main_option('sqlalchemy.url', connection_string)

You should store your credentials in a password manager that the whole team has access to, or whatever config/secret store you have available. Though, with this approach the password is exposed to your local clip board - an even better approach would be to have env.py directly connect to your config/secret store API and pull out the username/password directly but this adds a third party dependency.

小开

In env.py just add

config.set_main_option('sqlalchemy.url', os.environ['DB_URL'])

after

config = context.config

like

config = context.config
config.set_main_option('sqlalchemy.url', os.environ['DB_URL'])

and then execute like that:

DB_URL="mysql://atuamae:de4@127.0.0.1/db" \
alembic upgrade head

小开

env.py:

from alembic.config import Config


alembic_cfg = Config()
alembic_cfg.set_main_option("sqlalchemy.url", getenv('PG_URI'))

https://alembic.sqlalchemy.org/en/latest/api/config.html

小开

In the case of MultiDB settings (the same for SingleDB), you can use config.set_section_option('section_name', 'variable_name', 'db_URL') to modify the values of the database URL in the alembic.ini file.

For example:

alembic.init

[engine1]
sqlalchemy.url =


[engine2]
sqlalchemy.url =

Then,

env.py

config = context.config


config.set_section_option('engine1', 'sqlalchemy.url', os.environ.get('URL_DB1'))
config.set_section_option('engine2', 'sqlalchemy.url', os.environ.get('URL_DB2'))

小开

An option that worked for me was to use set_main_option and leave the sqlalchemy.url = blank in alembic.ini

from config import settings


config.set_main_option(
"sqlalchemy.url", settings.database_url.replace("postgres://", "postgresql+asyncpg://", 1))

sttings is a class in config file that I use to get variables in env file check this os.environ.get() does not return the Environment Value in windows? for more detail, another option is to use os.environ.get but make sure that you export the varibale to prevent errors like sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string

是否可以将 alembic.ini 外部的 alembic 连接字符串存储起来？

alembic.ini

env.py

config.yml

env.py: