In Flask-migrate ValueError: invalid interpolation

Question

I am using flask migrate to for database creation & migration in flask with flask-sqlalchemy.

Everything was working fine until I changed my database user password contains '@' then it stopped working so, I updated my code based on Writing a connection string when password contains special characters

It working for application but not for flask-migration, Its showing error while migrating

i.e on python manage.py db migrate

ValueError: invalid interpolation syntax in u'mysql://user:p%40ssword@localhost/testdb' at position 15

Here password is p@ssword and its escaped by urlquote (see above question link).

Full error stack:

Traceback (most recent call last):
  File "manage.py", line 20, in <module>
    manager.run()
  File "/usr/local/lib/python2.7/dist-packages/flask_script/__init__.py", line 412, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
  File "/usr/local/lib/python2.7/dist-packages/flask_script/__init__.py", line 383, in handle
    res = handle(*args, **config)
  File "/usr/local/lib/python2.7/dist-packages/flask_script/commands.py", line 216, in __call__
    return self.run(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/flask_migrate/__init__.py", line 177, in migrate
    version_path=version_path, rev_id=rev_id)
  File "/usr/local/lib/python2.7/dist-packages/alembic/command.py", line 117, in revision
    script_directory.run_env()
  File "/usr/local/lib/python2.7/dist-packages/alembic/script/base.py", line 407, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python2.7/dist-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python2.7/dist-packages/alembic/util/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "migrations/env.py", line 22, in <module>
    current_app.config.get('SQLALCHEMY_DATABASE_URI'))
  File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 218, in set_main_option
    self.set_section_option(self.config_ini_section, name, value)
  File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 245, in set_section_option
    self.file_config.set(section, name, value)
  File "/usr/lib/python2.7/ConfigParser.py", line 752, in set
    "position %d" % (value, tmp_value.find('%')))
ValueError: invalid interpolation syntax in u'mysql://user:p%40ssword@localhost/testdb' at position 15

Please help

Answer 1

You may want to look at http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql-unicode I was having the same issue with my password and the mysql connector. using the mysql+pymysql connector allowed me to connect in application and in migration scripts.

Answer 2

In the migrations/env.py file, you will find the code that is responsible for this issue.

config.set_main_option('sqlalchemy.url',
                       current_app.config.get('SQLALCHEMY_DATABASE_URI'))

If there are % signs in the SQLALCHEMY_DATABASE_URI, this will cause an error.

You can solve this by editing the migrations/env.py file, and changing the offending line as follows

db_url_escaped = current_app.config.get('SQLALCHEMY_DATABASE_URI').replace('%', '%%')
config.set_main_option('sqlalchemy.url', db_url_escaped)

Also see the documentation of set_main_option:

Note that this value is passed to ConfigParser.set, which supports variable interpolation using pyformat (e.g. %(some_value)s). A raw percent sign not part of an interpolation symbol must therefore be escaped, e.g. %%. The given value may refer to another value already in the file using the interpolation format.

Answer 3

I have a solution for this issue after experiencing it as well.

There's an issue with '%' (percent signs) in the db connection URI after you urlencode the string.

I tried substituting the percent sign with double percent signs ('%%') which gets me past the interpolation error. However, that resulted in not being able to connect to the database because of an incorrect password.

Solution I'm going with for now is to avoid using '%' in my db password. Not a satisfactory solution, but will do for now. I'll make a note in "alembic"'s github of the issue. Seems using RawConfigParser in their package could help avoid this issue.