3

When doing SELECT * FROM test_table; inside clickhouse-client I get N rows as expected, but only N-2 when doing engine.execute('SELECT * FROM test_table;') from python code that uses sqlalchemy.

Steps to reproduce:

  1. clickhouse-server is running on localhost.
  2. The following commands are executed in clickhouse-client:
CREATE TABLE test_table (id INTEGER, created Date) ENGINE = MergeTree(created, (id), 8192);
INSERT INTO test_table (id, created) VALUES (1, 11345678);
INSERT INTO test_table (id, created) VALUES (2, 12345678);
INSERT INTO test_table (id, created) VALUES (3, 13345678);
INSERT INTO test_table (id, created) VALUES (4, 14345678);
SELECT * FROM test_table;

Results:

SELECT *
FROM test_table

┌─id─┬────created─┐
│  4 │ 2106-02-07 │
└────┴────────────┘
┌─id─┬────created─┐
│  3 │ 2084-08-20 │
└────┴────────────┘
┌─id─┬────created─┐
│  2 │ 2038-03-15 │
└────┴────────────┘
┌─id─┬────created─┐
│  1 │ 1991-10-08 │
└────┴────────────┘

4 rows in set. Elapsed: 0.004 sec.

Okay, 4 rows as expected.

  1. The following python script is executed:
from sqlalchemy import create_engine


connection_string = 'clickhouse://default:@localhost/default'
engine = create_engine(connection_string)
result = list(engine.execute('SELECT * FROM test_table;'))
print(len(result))
print(result)

Results:

2
[('4', '2106-02-07'), ('2', '2038-03-15')]

Definitely not as expected. So, what is going on here?

sqlalchemy version: 1.3.11

clickhouse version (both server and client): 19.17.4.11

sanyassh
  • 8,100
  • 13
  • 36
  • 70
  • 1
    what version of CH ODBC driver do you use? Try https://github.com/mymarilyn/clickhouse-driver – Denny Crane Nov 29 '19 at 15:43
  • I'm just curious but I cannot reproduce this problem. What version of *sqlalchemy-clickhouse* did you use? Your query is ended by *;* that should lead to the error *Syntax error (Multi-statements are not allowed): failed at position 25 (end of query): ; FORMAT TabSeparatedWithNamesAndTypes.* - how did you manage to suppress it? – vladimir Apr 10 '20 at 14:07
  • @vladimir as you can see from the question, I used _sqlalchemy version: 1.3.11_ and _clickhouse version (both server and client): 19.17.4.11_. I didn't use _sqlalchemy-clickhhouse_ package if such exists. – sanyassh Apr 10 '20 at 15:05
  • yes but I reproduced exactly your environment and got error *sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:clickhouse*. So what module did you use for clickhouse-dialect or maybe you write custom implementation? Could you provide the list of used modules or *requirements.txt*? – vladimir Apr 10 '20 at 23:39

1 Answers1

2

modify connection_string = 'clickhouse+native://default:@localhost/default'

refer to: https://github.com/xzkostyan/clickhouse-sqlalchemy/issues/10

guaifish
  • 21
  • 2