sqlalchemy.exc.ArgumentError: Can't load plugin: sqlalchemy.dialects:driver

Question

I am trying to run alembic migration and when I run

alembic revision --autogenerate -m "Added initial tables"

It fails saying

sqlalchemy.exc.ArgumentError: Can't load plugin: sqlalchemy.dialects:driver

the database url is

postgresql+psycopg2://dev:passwd@localhost/db

and I even have psycopg2 installed in my virtualenv

$yolk -l
Flask-Login     - 0.1.3        - active
Flask-SQLAlchemy - 0.16         - active
Flask           - 0.9          - active
Jinja2          - 2.6          - active
Mako            - 0.7.3        - active
MarkupSafe      - 0.15         - active
Python          - 2.7.2        - active development (/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/lib-dynload)
SQLAlchemy      - 0.8.0        - active
Werkzeug        - 0.8.3        - active
alembic         - 0.4.2        - active
antiorm         - 1.1.1        - active
appscript       - 1.0.1        - active
distribute      - 0.6.27       - active
envoy           - 0.0.2        - active
osascript       - 0.0.4        - active
pep8            - 1.4.5        - active
pip             - 1.1          - active
psycopg2        - 2.4.6        - active
wsgiref         - 0.1.2        - active development (/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7)
yolk            - 0.4.3        - active

Whay could be causing this issue?

Coelenterate

change driver:// to mysql:// or sqlite:// on sqlalchemy.url – Monotheism

I found that the error included "sqlalchemy.dialects:driver" when the alembic.ini / sqlalchemy.url started with "driver:// ...", instead of an actual driver name like "postgresql". – Convent

Answer 1

Here's how to produce an error like that:

>>> from sqlalchemy import *
>>> create_engine("driver://")
Traceback (most recent call last):
... etc
sqlalchemy.exc.ArgumentError: Can't load plugin: sqlalchemy.dialects:driver

so I'd say you aren't actually using the postgresql URL you think you are - you probably are calling upon a default-generated alembic.ini somewhere.

Lakin

You are right, I was messing up somewhere(not sure exactly where), I am able to move the process forward, although held up #15661176, let me know if you have any ideas. THANK YOU very much for your help – Coelenterate

Didn't notice that alembic.ini got created in the parent directory of the alembic directory. Thanks! – Bouldin

To set the db URL dynamically, see this answer : https://mcmap.net/q/166497/-is-it-possible-to-store-the-alembic-connect-string-outside-of-alembic-ini – Musselman

Answer 2

For those who haven't noticed it, the "default-generated alembic.ini" zzzzeek refers to is in the root directory of the project.

The whole problem is one of setting the sqlalchemy.url config parameter in the alembic.ini file. Also, it can be set programmatically as explained in https://mcmap.net/q/297042/-alembic-autogenerate-producing-empty-migration.

Colon

the alembic.ini can be anywhere. And Mike's nick has only 3 zs :d – Riggs

Answer 3

Notice that the scheme doesn't actually specify the driver but the dialect: the scheme is of form dialect:// or dialect+driver://.

For example the correct urls to connect to a PostgreSQL database would start with for example postgresql:// (which defaults to using psycopg2), or choosing a driver explicitly (postgresql+psycopg2://, or with another driver).

If you happen to specify only psycopg2 you will get the error

sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:psycopg2

Michal

For Airflow (Celery) users: https://mcmap.net/q/297043/-unable-to-start-airflow-worker-flower-and-need-clarification-on-airflow-architecture-to-confirm-that-the-installation-is-correct – Twelfth

@snakecharmerb thanks, I am not sure if it was ever right :'D – Riggs

Answer 4

To programmatically override the default value in alembic.ini, you can add a line or two of code in alembic/env.py:

+ import app

[...snip...]

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

+ config.set_section_option(
    config.config_ini_section, "sqlalchemy.url", app.settings.SQLALCHEMY_DATABASE_URL
)

where import app and app.settings.SQLALCHEMY_DATABASE_URL are replaced by your own application-specific code to retrieve the URL.

Mediator

Answer 5

I resolved this by simply opening the alembic.ini in notepad++ and then amending the variable sqlachemy.url (on about line 38) to the url in my project file. The error is most likely caused because it has driver at the beginning.

i.e. renaming this line to

sqlalchemy.url = sqlite:///name_of_my_database.db

Peavey

Answer 6

If you were following the correct syntax for creating an engine, depending on your database of choice, create_engine(f"<dialect_name>+<driver_name>://{self.user}:{self.password}@{self.host}:{self.port}/{self.database}) and none of the other suggestions here fix your problem, you can try the solution outlined in the next paragraph.

The issue, NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:\<dialect_name\>.\<driver_name\>, can be caused by running an SQLAlchemy version that does support the driver you need. For example, SQLAlchemy 1.4.26 supports these PostgreSQL drivers. So, the solution:

1. Check the documentation for supported drivers, for the dialect you need.
2. Then uninstall SQLAlchemy or the problematic driver pip uninstall SQLAlchemy
3. Force pip to install specific version of SQLAlchemy or an alternative driver, pip install SQLAlchemy==1.0.14

Holeproof

Answer 7

connection_url=""  
#your database connection string here pulled from either environment variable , vaults or can be set directly

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 = connection_url 
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    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.

    """
    configdict=config.get_section(config.config_ini_section)
    configdict.update({"sqlalchemy.url":connection_url})
    connectable = engine_from_config(
        configdict,
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata
        )

        with context.begin_transaction():
            context.run_migrations()

if you want to read the connection url from your python file itself without refering to default

alembic.ini

config file.

Immersion

Answer 8

Try those commands to install missing packages:

sudo apt-get install libpq-dev 
sudo pip install psycopg2 
sudo pip install redshift-sqlalchemy 
sudo pip install sqlparse

Hershberger

Answer 9

To get the Teradata Queries to run on the .exe produced by Pyinstaller. I changed my engine from SQLAlchemy to Teradata

From :

import sqlalchemy as sa
user, pasw, hostname = UserName,Password, 'myurl.com'
# connect
td_engine = sa.create_engine('teradata://{}:{}@{}:22/'.format(user,pasw,hostname),echo=True)
df = pd.read_sql_query(query1,connect)

To:

import teradata
user, pasw, hostname = UserName,Password, 'myurl.com'
td = teradata.UdaExec (appName="test", version="1.0", logConsole=True)
td_engine = td.connect(method="odbc",system=hostname, username=user,password=pasw,driver="Teradata") 

Uxmal

Answer 10

I know this question is quite old and has many answers. I was recently trying to setup alembic for one of the recent projects.

• If you change in url =config.get_main_option(db_config) in env.py file generated by alembic it doesn't work since under the hood it will try to use the config mentioned in the next point to fetch the URL. If you want to do so you need to change it programmatically using config.set_main_option("sqlalchemy.url", conn_string) using it in your preferred migration function or the top of the script before all the functions in env.py.
• If you are using default generated albemic configurations, change sqlalchemy.url = connection urlin alembic.ini file.
• Make sure your connection URL is in the form postgresql+driver://..

Dash

Answer 11

In my case, inside the file alembic/env.py I mistakenly used

config.get_main_option

instead of

config.set_main_option

PS:

config = context.config

Jame

Answer 12

I did,

pip install ibm_db_sa

it fixed problem

Oria

Answer 13

uninstall anaconda if you have it. it is installing your mysql connector in the anaconda path and your code is probably looking in the python path.

Prototherian