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!

Instead of struggling with redshift.psycopg2 and directly use postgresql+psycopg2, it works fine :) — lucky_start_izumi, Nov 20 '15 at 19:25
What is the exact solution for this problem ? I need to connect with redshift and postgresql+psycopg2 does not work. — Sarang Manjrekar, Dec 13 '16 at 12:22

score 18 · Answer 1 · edited Mar 27 '21 at 03:02

18

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.

edited Mar 27 '21 at 03:02

Mogsdad

44,709
21
151
275

answered Mar 27 '21 at 01:06

dave campbell

551
5
6

Asraful · Answer 2 · 2021-10-26T17:06:05.377

14

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

edited Oct 26 '21 at 17:06

answered Nov 27 '19 at 09:32

Asraful

1,241
18
31

1

what is sa_url? – joydeep bhattacharjee Dec 18 '20 at 14:40
@joydeepbhattacharjee as I recall from sqlalchemy.engine import url as sa_url – Asraful Dec 18 '20 at 16:11

score 6 · Answer 3 · answered May 17 '16 at 16:25

6

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://

answered May 17 '16 at 16:25

Brad Dre

3,580
2
19
22

paridin · Answer 4 · 2016-12-15T17:29:14.483

2

I had the same problem and I solved it when I removed the __pycache__ directory in redshift_sqlalchemy package.

Go to you site-packeges in my case is in my virtualenv.

so cdvirtualenv and cd lib/python3.5/site-packages/redshift_sqlalchemy finally rm __pycache__.

edited Dec 15 '16 at 17:29

answered Dec 14 '15 at 05:59

paridin

429
7
14

Hi, Tried removing the __pycache__ directory, but still gives the same error. Can't load plugin: sqlalchemy.dialects:redshift.psycopg2 – Sarang Manjrekar Dec 13 '16 at 12:24
did you restart the services? please, give me the steps you are following. – paridin Dec 15 '16 at 04:17
@ paridin which services to restart ? For now, I solved it by coming out of virtual environment and running it in the main Python environment. It runs just fine. – Sarang Manjrekar Dec 15 '16 at 07:15
When you start a process and it uses a port it is a service, I use iPython with notebooks to work so I need to restart that service to take a clean process also I suggest removes the `__pycache__` folder from your code, and if you have a requirements file, you should delete your environment and recreate a new one, this is the easy way, if you have time, you must look for references into the site-packages and remove them. I don't recommend use the main environment since you can break it and it will be hard to fix. – paridin Dec 15 '16 at 17:27

score 2 · Answer 5 · answered Mar 07 '22 at 06:34

2

This worked for me:

!pip install -U psycopg2-binary sqlalchemy-redshift SQLAlchemy

answered Mar 07 '22 at 06:34

Haiyingying

27
3

score 0 · Answer 6 · answered Jun 09 '23 at 15:09

This also worked for me. Instead of specifying redshift+pyscopg2 in the connection URL, you can specify redshift+redshift_connector. This will use Amazon's official Redshift Python driver, as well as their SQLAlchemy implementation, instead of psycopg2. In this case you wouldn't even need to install psycopg2 at all if only connecting to Redshift.

You would need to install these 2 modules:

pip install sqlalchemy-redshift
pip install redshift_connector

And then create the engine like so:

engine = create_engine('redshift+redshift_connector://{}:{}@{}'.format(username, password, url))

Reference: https://aws.amazon.com/blogs/big-data/use-the-amazon-redshift-sqlalchemy-dialect-to-interact-with-amazon-redshift/

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

6 Answers6

Linked