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

Question

I am trying to connect to redshift from my python code. my pip installed:

psycopg2==2.6.1
redshift-sqlalchemy==0.4.1
SQLAlchemy==1.0.9

and my virtual machine has:

libpq-dev
python-psycopg2

But I am still getting

 engine = create_engine('redshift+psycopg2://{}:{}@{}'.format(username, password, url))
  File "/opt/project/env/local/lib/python2.7/site-packages/sqlalchemy/engine/__init__.py", line 386, in create_engine
    return strategy.create(*args, **kwargs)
  File "/opt/project/env/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 51, in create
    entrypoint = u._get_entrypoint()
  File "/opt/project/env/local/lib/python2.7/site-packages/sqlalchemy/engine/url.py", line 131, in _get_entrypoint
    cls = registry.load(name)
  File "/opt/project/env/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 205, in load
    (self.group, name))
NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:redshift.psycopg2

With the same config, I am able to run from my laptop (mac), but on linux, I guess some packages still missing? Any suggestion will be appreciated, thanks!

Answer 1

I found some tutorials that had the driver listed as: postgres+psycopg2:// ...and when using that, i received that error:

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

simply changing it to:

postgresql+psycopg2://
        ^^

fixed it immediately.

Answer 2

I faced same issue and bellow resolution solved it smoothly .

Environment :

Python 3.7

Conda 4.6.14

Dependency :

sudo apt-get install python-pip

sudo apt-get install libpq-dev

pip install --user psycopg2

pip install --user sqlalchemy

pip install --user sqlalchemy-redshift

Connection string :

connection_string = sa_url.URL(
        drivername='postgresql+psycopg2',
        username='admin',
        password='admin',
        host='redshiftone.********.us-west-2.redshift.amazonaws.com',
        port='5439',
        database='redshiftone')

RDS Code Sample,Python

Answer 3

A boneheaded way to get NoSuchModule exception is that the db connection string is in the wrong format. In my case I got this error when I changed

DB_URI = 'postgresql://...'

to

DB_URI = 'pg8000://...'

but should be:

DB_URI = 'postgresql+pg8000://