def test_table_create_on_cluster(self):
create_sql = (
'CREATE TABLE t1 ON CLUSTER test_cluster '
'(x Int32) ENGINE = Memory'
)
with mocked_engine() as engine:
table = Table(
't1', self.metadata(session=engine.session),
Column('x', types.Int32, primary_key=True),
engines.Memory(),
clickhouse_cluster='test_cluster'
)
table.create()
self.assertEqual(engine.history, [create_sql])
self.assertEqual(
self.compile(CreateTable(table)),
create_sql
)
def test_escape_binary_mod(self):
query = session.query(literal(1) % literal(2))
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT 1 %% 2 AS anon_1')
table = Table('t', self.metadata(),
Column('x', types.Int32, primary_key=True),
engines.Memory())
query = session.query(table.c.x % table.c.x)
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT x %% x AS anon_1 FROM t')
class BooleanCompilationTestCase(CompilationTestCase):
table = Table('test', CompilationTestCase.metadata(), Column('x', Boolean),
engines.Memory())
def test_create_table(self):
self.assertEqual(self.compile(CreateTable(self.table)),
'CREATE TABLE test (x UInt8) ENGINE = Memory')
def test_literals(self):
query = self.session.query(self.table.c.x).filter(self.table.c.x)
self.assertEqual(self.compile(query),
'SELECT test.x AS test_x FROM test WHERE test.x = 1')
def test_select_format_clause(self):
metadata = self.metadata()
bind = self.session.bind
bind.cursor = lambda: None
table = Table('t1', metadata, Column('x',
types.Int32,
primary_key=True))
statement = self.compile(self.session.query(table.c.x), bind=bind)
self.assertEqual(statement, 'SELECT t1.x AS t1_x FROM t1')
def test_reflect(self):
"""
checking, that after call metadata.reflect()
we have clickohouse-specific table, which have overridden join methods
"""
unbound_metadata = MetaData(bind=native_session.bind)
table = CHTable('test_reflect', unbound_metadata,
Column('x', types.Int32), engines.Log())
table.drop(native_session.bind, if_exists=True)
table.create(native_session.bind)
std_metadata = self.metadata()
self.assertFalse(std_metadata.tables)
std_metadata.reflect(only=[table.name])
table = std_metadata.tables.get(table.name)
assert table is not None
self.assertTrue(isinstance(table, CHTable))
query = table.select().join(text('another_table'),
table.c.x == 'xxx',
type='INNER',
strictness='ALL',
distribution='GLOBAL')
self.assertEqual(
self.compile(query), "SELECT x FROM test_reflect "
"GLOBAL ALL INNER JOIN another_table "
"ON x = %(x_1)s")
def test_yield_per_and_execution_options(self):
numbers = Table(
'numbers', self.metadata(),
Column('number', types.UInt64, primary_key=True),
)
query = self.session.query(numbers.c.number).limit(100).yield_per(15)
query = query.execution_options(foo='bar')
self.assertIsNotNone(query._yield_per)
self.assertEqual(
query._execution_options,
{'stream_results': True, 'foo': 'bar', 'max_row_buffer': 15}
)
def test_parse_func_count(self):
mock.add(
mock.POST, self.url, status=200,
body='count_1\nUInt64\n42\n'
)
table = Table(
't1', self.metadata(),
Column('x', types.Int32, primary_key=True)
)
rv = self.session.query(func.count()).select_from(table).scalar()
self.assertEqual(rv, 42)
def test_rowcount_return(self):
table = Table('test', self.metadata(),
Column('x', types.Int32, primary_key=True),
engines.Memory())
table.drop(if_exists=True)
table.create()
rv = self.session.execute(table.insert(), [{'x': x} for x in range(5)])
self.assertEqual(rv.rowcount, 5)
self.assertEqual(
self.session.query(func.count()).select_from(table).scalar(), 5)
def test_server_default(self):
metadata = self.metadata()
args = ([
Column('x1', sa_types.String),
Column('x2', sa_types.String, server_default='')
] + [engines.Memory()])
table = Table('t', metadata, *args)
with self.create_table(table):
self.assertEqual([
c['default'] for c in inspect(metadata.bind).get_columns('t')
], [None, "''"])
class DateTime64CompilationTestCaseTimezone(CompilationTestCase):
table = Table(
'test', CompilationTestCase.metadata(),
Column(
'x',
types.DateTime64(4, 'Pacific/Honolulu'),
primary_key=True,
), engines.Memory())
def test_create_table_precision(self):
self.assertEqual(
self.compile(CreateTable(self.table)), 'CREATE TABLE test ('
'x DateTime64(4, \'Pacific/Honolulu\')'
') ENGINE = Memory')
def test_text_engine_columns(self):
table = Table(
't1', self.metadata(),
Column('date', types.Date, primary_key=True),
Column('x', types.Int32),
Column('y', types.String),
engines.MergeTree('date', ('date', 'x')),
)
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE t1 (date Date, x Int32, y String) '
'ENGINE = MergeTree(date, (date, x), 8192)'
)
def test_create_table_nested_nullable(self):
table = Table(
't1', self.metadata(),
Column('x', types.Int32, primary_key=True),
Column('y', types.Array(types.Nullable(types.String))),
engines.Memory()
)
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE t1 '
'(x Int32, y Array(Nullable(String))) '
'ENGINE = Memory'
)
def test_create_table_column_all_defaults(self):
table = Table(
't1', self.metadata(), Column('x', types.Int8),
Column('dt',
types.DateTime,
server_default=func.now(),
clickhouse_materialized=func.now(),
clickhouse_alias=func.now()), engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 ('
'x Int8, '
'dt DateTime DEFAULT now()) '
'ENGINE = Memory')
def test_drop_table_clause(self):
table = Table(
't1', self.metadata(),
Column('x', types.Int32, primary_key=True)
)
self.assertEqual(
self.compile(DropTable(table)),
'DROP TABLE t1'
)
self.assertEqual(
self.compile(DropTable(table, if_exists=True)),
'DROP TABLE IF EXISTS t1'
)
def _type_round_trip(self, *types):
metadata = self.metadata()
args = (
[Column('t%d' % i, type_) for i, type_ in enumerate(types)] +
[engines.Memory()]
)
table = Table('t', metadata, *args)
with self.create_table(table):
return [
c['type'] for c in
inspect(metadata.bind).get_columns('t')
]
class NumericTypeTestCase(TypesTestCase):
table = Table(
'test', TypesTestCase.metadata(),
Column('x', Numeric(10, 2)),
engines.Memory()
)
def test_create_table(self):
self.assertEqual(
self.compile(CreateTable(self.table)),
'CREATE TABLE test (x Decimal(10, 2)) ENGINE = Memory'
)
def test_select_insert(self):
x = Decimal('123456789.12')
with self.create_table(self.table):
self.session.execute(self.table.insert(), [{'x': x}])
self.assertEqual(self.session.query(self.table.c.x).scalar(), x)
def test_insert_truncate(self):
value = Decimal('123.129999')
expected = Decimal('123.12')
with self.create_table(self.table):
self.session.execute(self.table.insert(), [{'x': value}])
self.assertEqual(self.session.query(self.table.c.x).scalar(),
expected)
def test_insert_overflow(self):
value = Decimal('12345678901234567890.1234567890')
with self.create_table(self.table):
with self.assertRaises(DatabaseException) as ex:
self.session.execute(self.table.insert(), [{'x': value}])
self.assertIn('out of range', str(ex.exception))
def test_create_table_decimal_symlink(self):
table = Table(
'test', TypesTestCase.metadata(),
Column('x', types.Decimal(10, 2)),
engines.Memory()
)
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE test (x Decimal(10, 2)) ENGINE = Memory'
)
def test_create_table(self):
table = Table(
't1', self.metadata(),
Column('x', types.Int32, primary_key=True),
Column('y', types.String),
Column('z', types.String(10)),
engines.Memory()
)
# No NOT NULL. And any PKS.
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE t1 (x Int32, y String, z FixedString(10)) '
'ENGINE = Memory'
)
class DateTimeTypeTestCase(TypesTestCase):
table = Table('test', TypesTestCase.metadata(),
Column('x', types.DateTime, primary_key=True),
engines.Memory())
def test_select_insert(self):
dt = datetime(2018, 1, 1, 15, 20)
with self.create_table(self.table):
self.session.execute(self.table.insert(), [{'x': dt}])
self.assertEqual(self.session.query(self.table.c.x).scalar(), dt)
def test_create_table(self):
self.assertEqual(self.compile(CreateTable(self.table)),
'CREATE TABLE test (x DateTime) ENGINE = Memory')
def test_parse_float_types(self):
types_ = ['Float32', 'Float64']
columns = ['a', 'b']
mock.add(mock.POST,
self.url,
status=200,
body=('\t'.join(columns) + '\n' + '\t'.join(types_) + '\n' +
'\t'.join(['42'] * len(types_)) + '\n'))
table = Table('t1', self.metadata(),
*[Column(col, types.Float) for col in columns])
rv = session.query(*table.c).first()
self.assertEqual(rv, tuple([42.0] * len(columns)))
def test_text_engine_columns(self):
table = Table(
't1',
self.metadata(),
Column('date', types.Date, primary_key=True),
Column('x', types.Int32),
Column('y', types.String),
engines.MergeTree(partition_by='date', order_by=('date', 'x')),
)
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE t1 (date Date, x Int32, y String) '
'ENGINE = MergeTree() '
'PARTITION BY date '
'ORDER BY (date, x)')
class NumericCompilationTestCase(CompilationTestCase):
table = Table('test', CompilationTestCase.metadata(),
Column('x', Numeric(10, 2)), engines.Memory())
def test_create_table(self):
self.assertEqual(
self.compile(CreateTable(self.table)),
'CREATE TABLE test (x Decimal(10, 2)) ENGINE = Memory')
def test_create_table_decimal_symlink(self):
table = Table('test', CompilationTestCase.metadata(),
Column('x', types.Decimal(10, 2)), engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE test (x Decimal(10, 2)) ENGINE = Memory')
def test_types_check(self):
table = Table('test', self.metadata(),
Column('x', types.UInt32, primary_key=True),
engines.Memory())
table.drop(if_exists=True)
table.create()
with self.assertRaises(DatabaseException) as ex:
self.session.execute(
table.insert().execution_options(types_check=True), [{
'x': -1
}])
self.assertIn('-1 for column "x"', str(ex.exception.orig))
with self.assertRaises(DatabaseException) as ex:
self.session.execute(table.insert(), [{'x': -1}])
self.assertIn('Repeat query with types_check=True for detailed info',
str(ex.exception.orig))
class NumericHttpTestCase(HttpSessionTestCase):
table = Table('test', HttpSessionTestCase.metadata(),
Column('x', Numeric(10, 2)), engines.Memory())
def test_insert_truncate(self):
value = Decimal('123.129999')
expected = Decimal('123.12')
with self.create_table(self.table):
if self.server_version >= (20, 5, 2):
self.session.execute(self.table.insert(), [{'x': value}])
self.assertEqual(
self.session.query(self.table.c.x).scalar(), expected)
else:
with self.assertRaises(DatabaseException) as ex:
self.session.execute(self.table.insert(), [{'x': value}])
self.assertIn('value is too small', str(ex.exception))
class NumericTypeTestCase(TypesTestCase):
table = Table('test', TypesTestCase.metadata(),
Column('x', Numeric(10, 2)), engines.Memory())
def test_create_table(self):
self.assertEqual(
self.compile(CreateTable(self.table)),
'CREATE TABLE test (x Decimal(10, 2)) ENGINE = Memory')
def test_select_insert(self):
x = Decimal('12345678.12')
with self.create_table(self.table):
self.session.execute(self.table.insert(), [{'x': x}])
self.assertEqual(self.session.query(self.table.c.x).scalar(), x)
def test_insert_truncate(self):
value = Decimal('123.129999')
expected = Decimal('123.12')
with self.create_table(self.table):
self.session.execute(self.table.insert(), [{'x': value}])
self.assertEqual(
self.session.query(self.table.c.x).scalar(), expected)
def test_insert_overflow(self):
value = Decimal('12345678901234567890.1234567890')
with self.create_table(self.table):
with self.assertRaises(DatabaseException) as ex:
self.session.execute(self.table.insert(), [{'x': value}])
# 'Too many digits' is written in the CH response;
# 'out of range' is raised from `struct` within
# `clickhouse_driver`,
# before the query is sent to CH.
self.assertTrue('out of range' in str(ex.exception)
or 'Too many digits' in str(ex.exception))
def test_create_table_decimal_symlink(self):
table = Table('test', TypesTestCase.metadata(),
Column('x', types.Decimal(10, 2)), engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE test (x Decimal(10, 2)) ENGINE = Memory')
def reflect_table(self, table, *args, **kwargs):
# This check is necessary to support direct instantiation of
# `clickhouse_sqlalchemy.Table` and then reflection of it.
if not isinstance(table, Table):
table.metadata.remove(table)
ch_table = Table._make_from_standard(
table, _extend_on=kwargs.get('_extend_on'))
else:
ch_table = table
super(ClickHouseInspector, self).reflect_table(ch_table, *args,
**kwargs)
with self._operation_context() as conn:
schema = conn.schema_for_object(ch_table)
self._reflect_engine(ch_table.name, schema, ch_table)
def test_create_table_nested(self):
table = Table(
't1', self.metadata(), Column('x', types.Int32, primary_key=True),
Column(
'parent',
types.Nested(
Column('child1', types.Int32),
Column('child2', types.String),
)), engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 ('
'x Int32, '
'parent Nested('
'child1 Int32, '
"child2 String"
')'
') ENGINE = Memory')
def test_parse_date_types(self, patched_server_info):
mock.add(
mock.POST, self.url, status=200,
body=(
'a\n' +
'Date\n' +
'2012-10-25\n'
)
)
table = Table(
't1', self.metadata(),
Column('a', types.Date)
)
rv = self.session.query(*table.c).first()
self.assertEqual(rv, (date(2012, 10, 25), ))
def test_basic(self):
numbers = Table('numbers',
self.metadata(),
Column('number', types.UInt64, primary_key=True),
schema='system')
q = iter(self.session.query(numbers.c.number).yield_per(1).limit(3))
ret = []
ret.append(next(q))
ret.append(next(q))
ret.append(next(q))
with self.assertRaises(StopIteration):
next(q)
self.assertEqual(ret, [(0, ), (1, ), (2, )])
def test_create_reflected(self):
metadata = self.metadata()
table = Table('test_reflect', metadata, Column('x', types.Int32),
engines.MergeTree(partition_by='x', order_by='x'))
with self.create_table(table):
metadata.clear() # reflect from clean state
self.assertFalse(metadata.tables)
table = Table('test_reflect', metadata, autoload=True)
exists_query = 'EXISTS TABLE test_reflect'
table.drop()
exists = self.session.execute(exists_query).fetchall()
self.assertEqual(exists, [(0, )])
table.create()
exists = self.session.execute(exists_query).fetchall()
self.assertEqual(exists, [(1, )])
def reflecttable(self, connection, table, include_columns, exclude_columns,
resolve_fks, **opts):
"""
Hack to ensure the autoloaded table class is
`clickhouse_sqlalchemy.Table`
(to support CH-specific features e.g. joins).
"""
# This check is necessary to support direct instantiation of
# `clickhouse_sqlalchemy.Table` and then reflection of it.
if not isinstance(table, Table):
table.metadata.remove(table)
ch_table = Table._make_from_standard(
table, _extend_on=opts.get('_extend_on'))
else:
ch_table = table
return super(ClickHouseDialect,
self).reflecttable(connection, ch_table, include_columns,
exclude_columns, resolve_fks, **opts)
