Connecting to ArcadeDB with Python

[lima-bravo]

I'm trying to connect to ArcadeDB version 22.2.1, with Postgres plugin installed and enabled and mapped to port 5433, using Python 3.10.

Using psycopg2 and psycopg2.connect(database='test', user='root', password='whatevernow', host='localhost', port=5433) I get the error message: error with status PGRES_TUPLES_OK and no message from the libpq

Using asyncpg and asyncpg.connect('postgresql://root@localhost:5433/practice&password=whatevernow'), I get a timeout error.

In both cases there are no error messages in the terminal where I started ArcadeDB.

Has anyone successfully connected to ArcadeDB using Python?

[lima-bravo]

I've made further progress by switching to pg8000.native, I can now connect to the database, but when I execute a query, I get the error:

'utf-8' codec can't decode byte 0x80 in position 3: invalid start byte

Is there anyone out there who has a working python connection to ArcadeDB and can share an example?

[LLuke]

Failure with psycopg2 is a big deal since this is the default driver, especially with SQLAlchemy. I'd like to share the script here that reproduces the issues with latest 23.10.1:

import psycopg2

with psycopg2.connect(
                    user=os.environ['DB_USER'],
                    password=os.environ['DB_PASS'],
                    host='localhost',
                    port='3333',
                    dbname='arcade_testdb',
                    sslmode='disable'
                    ) as connection:
    connection.autocommit = True

    with connection.cursor() as cursor:
        cursor.execute('select * from schema:database')
        results = cursor.fetchall()
        print(results)

Connection attempt with asyncpg also fails and I updated the issue at #668

Pg8000 is the closest and I was actually able to get the following working

import pg8000

conn = pg8000.connect(host="192.168.20.157", port="3333", ssl_context=None, database="arcade_testdb", user="root", password=os.environ['DB_PASS'])
conn.autocommit = True

cursor = conn.cursor()

cursor.execute("""
    select * from schema:database
""")

# Fetch all the rows
results = cursor.fetchall()
for row in results:
    for column in row:
        print(str(column) + '\n')
    
conn.close()

I'm trying to connect to ArcadeDB version 22.2.1, with Postgres plugin installed and enabled and mapped to port 5433, using Python 3.10.

Using psycopg2 and psycopg2.connect(database='test', user='root', password='whatevernow', host='localhost', port=5433) I get the error message: error with status PGRES_TUPLES_OK and no message from the libpq

Using asyncpg and asyncpg.connect('postgresql://root@localhost:5433/practice&password=whatevernow'), I get a timeout error.

In both cases there are no error messages in the terminal where I started ArcadeDB.

Has anyone successfully connected to ArcadeDB using Python?

[lvca]

I tried to run your script (psycopg2) with the latest version (with the management of H message) and I get this error:

Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/client.py", line 3, in <module>
    with psycopg2.connect(
         ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.DatabaseError: error with status PGRES_TUPLES_OK and no message from the libpq

[LLuke]

This is the exact error message I got and reported by @lima-bravo when he started the discussion. I guess that it is a wire protocol handling error which you can fix. The same code was works for PostgreSQL with queries that are supported by PostgreSQL server.

BTW I submitted an issue sqlalchemy/sqlalchemy#10656 and a pull request for sqlalchemy for you. Before they are willing to incorporate the changes and maintain them, anyone interested in using sqlalchemy with pg8000 can get my fix at https://github.com/LLuke/sqlalchemy/tree/arcadedb_23.10.1_patch

I tried to run your script (psycopg2) with the latest version (with the management of H message) and I get this error:

Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/client.py", line 3, in <module>
    with psycopg2.connect(
         ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.DatabaseError: error with status PGRES_TUPLES_OK and no message from the libpq