0

I am trying to connect to an existing PostgreSQL database. I am able to connect using the psql command-line interface, as well as pgAdmin. However, when I try to connect a python environment (JupyterLab and DataSpell), I am unable to connect.

Version Information:

  • psycopg2 version: 2.9.3
  • sqlalchemy version: 2.0.2
  • ipython-sql version: 0.3.9
  • postgres version: 15.3
%load_ext sql

%env DATABASE_URL=postgresql://retail_user:retail@localhost/retail_db

%%sql
SELECT * FROM orders LIMIT 10

The above code outputs:

__init__() got an unexpected keyword argument 'bind' Connection info needed in SQLAlchemy format, example: postgresql://username:password@hostname/dbname or an existing connection: dict_keys([])

After some investigation, I discovered that downgrading SQLAlchemy to version 1.4.22 resolved this issue.

Leslie
  • 1
  • 4
  • Are you using [ipython-sql](https://github.com/catherinedevlin/ipython-sql) or [JupySQL](https://github.com/ploomber/jupysql). If the latter look at [Db connection](https://jupysql.ploomber.io/en/latest/connecting.html) , in particular `from os import environ ... environ["DATABASE_URL"] = f"postgresql://user:{password}@localhost/database"` from the section *Connecting via an environment variable*. – Adrian Klaver Aug 21 '23 at 16:46
  • I'm using ipython-sql. I've tried connecting with the psycopg2 driver with no avail. – Leslie Aug 21 '23 at 16:57
  • 1) Follow the instructions here [ipython sql](https://github.com/catherinedevlin/ipython-sql) for making a connection. FYI, they don't include `env`. 2) Add the `psycopg2` code you tried and the error message as **text** update to your question. 3) Add version information for ipython-sql, Postgres, SQLAlchemy and psycopg2 as update to question. – Adrian Klaver Aug 21 '23 at 18:30
  • After following your advice and carefully reviewing the instructions for establishing a connection using ipython-sql, I realized that the version of SQLAlchemy I was using (2.0.2) was causing the problem. Following your suggestion, I downgraded to SQLAlchemy version 1.4.22, and that resolved the issue completely. – Leslie Aug 24 '23 at 14:55

1 Answers1

0

The error was not due to incorrect connection details, but rather a compatibility issue between the versions of SQLAlchemy and ipython-sql I was using. I was initially using SQLAlchemy version 2.0.2, which resulted in this error.

To resolve the issue, I downgraded SQLAlchemy to version 1.4.22. After making this change, the connection worked as expected and I was able to execute SQL queries using ipython-sql without encountering the error.

For those who might face a similar problem:

  • Check the versions of SQLAlchemy and ipython-sql you are using.
  • If you are using a version of SQLAlchemy higher than 1.4.22, consider downgrading it to version 1.4.22.
  • Re-run your code after the downgrade to confirm that the issue is resolved.

Thank you to Adrian Klaver for suggesting the steps that ultimately led to the solution.

Leslie
  • 1
  • 4
  • Per [ipython-sql](https://github.com/catherinedevlin/ipython-sql): *IPython-SQL's functionality and maintenance have been eclipsed by JupySQL, a fork maintained and developed by the Ploomber team. Future work will be directed into JupySQL - please file issues there, as well!*. I would think about migrating to [JupySQL](https://github.com/ploomber/jupysql). – Adrian Klaver Aug 24 '23 at 16:56