supporting new versions of pgspecial

[edublancas]

background: https://github.com/ploomber/jupysql/issues/347 Looks like pgspecial breaks if using psycopg2, since new versions need psycopg3. So we need to tell the user how to get it working.I'm thinking the workflow should go like this:

1. User tries to run a meta-command e.g. \d
2. We show a "missing pgspecial error" but customize it depending on which psycopg2 version they're using (because if they use psycopg2, they need a lower version of pgspecial)

I think this might impact sqlalchemy asl well. unsure if sqlalchemy has support for psycopg3.

[tl3119]

For this task, the basic idea is that we need to edit in this file to check whether pgspecial will break and if breaks, we need to tell the user how should they do, such as need a lower version of pgspecial. code likes:

`python -c "import psycopg2, sqlalchemy; print('Psycopg2 version', psycopg2.__version__); print('Sqlalchemy version', sqlalchemy.__version__)"
psycopg2_version = psycopg2.__version__.split(' ')[0]
if psycopg2_version >= '2.9':
    error_msg = 'Please upgrade psycopg2 to a version that supports psycopg3.'
else:
    error_msg = 'Please install a lower version of pgspecial that supports psycopg2. '
`

is that correct or do i miss something?

[edublancas]

so this one needs a bit more investigation.

currently, jupysql supports sqlalchemy 1.x and sqlalchemy 2.x. we also support psycopg2

my conclusion from some initial research is that with this combination of versions, we can only support old versions of pgspecial. the objective is to support newer versions of pgspecial, but to support that we might need to do some extra adjustments. so the first task is to figure out what versions of sqlalchemy and psycopg we need to support new versions of pgspecial, then implement support for it. finally, we can determine if it's feasible to support both old and new versions of pgspecial

[tl3119]

For the information I investigate, we need psycopg3 (>=3.0.10) version to support new versions of pgspecial, so we may need to upgrade psycopg version in our code

[edublancas]

ok, can you see if jupysql works with psycopg3? my guess is that you'd need sqlalchemy 2 (which we support). I think let's first find out about psycopg3 and then we can continue with pgspecial

[tl3119]

I change psycopg2-binary in setup.py to psycopg3 and then activate the virtual environment. After that, I run the command pip install -e ., and all the package install successfully. Does that mean jupysql can work with psycopg3? Or do I need to do other tests to check the condition?

[edublancas]

yes, you need to connect to a database and run a few queries. you can use this as a reference: https://jupysql.ploomber.io/en/latest/integrations/postgres-connect.html just modify the connection string and ensure it uses psycopg3

[tl3119]

It seems it successfully installed psycopg3 after I activate the virtual environment.

[edublancas]

you need to connect to a database and run a few queries.

[tl3119]

you need to connect to a database and run a few queries.

Sure! I connect to a database and run the queries, it can work!

[edublancas]

ok good, now let's get new versions of pgspecial working !

[tl3119]

I update the new version for pgspecial and test it locally using the same way, connect the database and run the query and it works. I open a PR for this issue: https://github.com/ploomber/jupysql/pull/622