6

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.

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
Steve.Gao
  • 177
  • 3
  • 11
  • try to check the file */etc/clickhouse-server/users.xml* on the server where ClickHouse installed - this file contains the description of all available credentials – vladimir Apr 10 '20 at 11:33
  • Hi vladimir thanks! I'm pretty sure my credentials is correctly, I can use it to connect to this db with dbeaver – Steve.Gao Apr 10 '20 at 11:44
  • Hi, did you get this solved? Same problem here, I could log with the clickhouse-client command with password flag, but can not use it with python – mingchau Apr 19 '20 at 18:31
  • 2
    @mingchau No I didn’t. Problem could be sqlalchemy or others, it works with clickhouse_driver try this package instead – Steve.Gao Apr 24 '20 at 09:27

2 Answers2

7

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.

vladimir
  • 13,428
  • 2
  • 44
  • 70
4

According to @vladimir reply.I tried

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

It worked for me. Thanks

samshan
  • 51
  • 4