Sqlalchemy shows "Code 516 Authentication failed" when trying to connect to clickhouse db

Question

I have connected to a clickhouse db with dbeaver and installed sqlalchemy v1.3.13 and clickhouse-sqlalchemy 0.1.3 for python 3.7.

When I tried to connect with

from sqlalchemy import create_engine 
engine_clickhouse = create_engine('clickhouse://use:pass@host:port/db')
engine_clickhouse.raw_connection()

I got

Exception: Code: 516, e.displayText() = DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name (version 20.3.4.10 (official build))

Does anybody know why? I didn't find a similar issue.

Answer 1

This issue is well-known - look at sqlalchemy-clickhouse Issue-45 or sqlalchemy-clickhouse Issue-49.

To fix it need to explicitly downgrade the package infi.clickhouse_orm up to version 1.0.4:

requirements.txt

...
infi.clickhouse_orm==1.0.4

It allows using the _build_params-function with the behavior expected by sqlalchemy-clickhouse (following versions of infi.clickhouse_orm aren't passed the password that leads to 'Authentication failed..'-error).

Or alternatively can be used the fork where fixed this error, for example adaiboy fork.

---

I would avoid using the official sqlalchemy-clickhouse because:

• fixes not committed to master ("Latest commit was on 23 Jan 2019" !!)
• new featured not adding
• there are some issues with pandas etc

clickhouse-driver is the greatest alternative for sqlalchemy-clickhouse.

Answer 2

According to @vladimir reply.I tried

pip uninstall infi.clickhouse_orm
pip install infi.clickhouse_orm==1.0.4

It worked for me. Thanks