def test_oracle(self): from sqlalchemy.dialects import oracle dialect = oracle.dialect() query = str(self.group_concat_md.compile(dialect=dialect)) expected = ('''SELECT metric_dimension.dimension_set_id, LISTAGG(metric_dimension.name ''' '''|| '=' || metric_dimension.value, ',') WITHIN GROUP (ORDER BY ''' '''metric_dimension.name || '=' || metric_dimension.value) AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) query = str(self.group_concat_md_order.compile(dialect=dialect)) expected = ('''SELECT metric_dimension.dimension_set_id, LISTAGG(metric_dimension.name ''' '''|| '=' || metric_dimension.value, ',') WITHIN GROUP (ORDER BY ''' '''metric_dimension.name ASC) AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) expected = ("""SELECT metric_dimension.dimension_set_id \n""" """FROM metric_dimension ORDER BY CASE WHEN metric_dimension.dimension_set_id='A'""" """ THEN 0 WHEN metric_dimension.dimension_set_id='B' THEN 1 WHEN""" """ metric_dimension.dimension_set_id='C' THEN 2 ELSE 3 END ASC""") query = str(self.order_by_field.compile(dialect=dialect)) self.assertEqual(expected, query)
def test_oracle(self): from sqlalchemy.dialects import oracle dialect = oracle.dialect() query = str(self.group_concat_md.compile(dialect=dialect)) expected = ( '''SELECT metric_dimension.dimension_set_id, LISTAGG(metric_dimension.name ''' '''|| '=' || metric_dimension.value, ',') WITHIN GROUP (ORDER BY ''' '''metric_dimension.name || '=' || metric_dimension.value) AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) query = str(self.group_concat_md_order.compile(dialect=dialect)) expected = ( '''SELECT metric_dimension.dimension_set_id, LISTAGG(metric_dimension.name ''' '''|| '=' || metric_dimension.value, ',') WITHIN GROUP (ORDER BY ''' '''metric_dimension.name ASC) AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) expected = ( """SELECT metric_dimension.dimension_set_id \n""" """FROM metric_dimension ORDER BY CASE WHEN metric_dimension.dimension_set_id='A'""" """ THEN 0 WHEN metric_dimension.dimension_set_id='B' THEN 1 WHEN""" """ metric_dimension.dimension_set_id='C' THEN 2 ELSE 3 END ASC""") query = str(self.order_by_field.compile(dialect=dialect)) self.assertEqual(expected, query)
def test_generic_now(self): assert isinstance(func.now().type, sqltypes.DateTime) for ret, dialect in [('CURRENT_TIMESTAMP', sqlite.dialect()), ('now()', postgresql.dialect()), ('now()', mysql.dialect()), ('CURRENT_TIMESTAMP', oracle.dialect())]: self.assert_compile(func.now(), ret, dialect=dialect)
def test_generic_random(self): assert func.random().type == sqltypes.NULLTYPE assert isinstance(func.random(type_=Integer).type, Integer) for ret, dialect in [('random()', sqlite.dialect()), ('random()', postgresql.dialect()), ('rand()', mysql.dialect()), ('random()', oracle.dialect())]: self.assert_compile(func.random(), ret, dialect=dialect)
def test_generic_now(self): assert isinstance(func.now().type, sqltypes.DateTime) for ret, dialect in [ ('CURRENT_TIMESTAMP', sqlite.dialect()), ('now()', postgresql.dialect()), ('now()', mysql.dialect()), ('CURRENT_TIMESTAMP', oracle.dialect()) ]: self.assert_compile(func.now(), ret, dialect=dialect)
def test_generic_random(self): assert func.random().type == sqltypes.NULLTYPE assert isinstance(func.random(type_=Integer).type, Integer) for ret, dialect in [ ('random()', sqlite.dialect()), ('random()', postgresql.dialect()), ('rand()', mysql.dialect()), ('random()', oracle.dialect()) ]: self.assert_compile(func.random(), ret, dialect=dialect)
def test_column_datatype_to_string(self): test_cases = ( (DATE(), "DATE"), (VARCHAR(length=255), "VARCHAR(255 CHAR)"), (VARCHAR(length=255, collation="utf8"), "VARCHAR(255 CHAR)"), (NVARCHAR(length=128), "NVARCHAR2(128)"), ) for original, expected in test_cases: actual = OracleEngineSpec.column_datatype_to_string( original, oracle.dialect()) self.assertEqual(actual, expected)
def _benchmark(start_time, result_set): """Benchmark utility""" if hasattr(result_set, 'statement'): oracle_dialect = oracle.dialect(max_identifier_length=30) raw_sql = str(result_set.statement.compile(dialect=oracle_dialect)) print(raw_sql) count = 0 for row in result_set: if count >= 50: break print(row) count += 1 print('raw query time:', time.time() - start_time, ' number of results >=:', count)
def test_oracle_time_expression_reserved_keyword_1m_grain(self): col = column("decimal") expr = OracleEngineSpec.get_timestamp_expr(col, None, "P1M") result = str(expr.compile(dialect=oracle.dialect())) self.assertEqual(result, "TRUNC(CAST(\"decimal\" as DATE), 'MONTH')")
def test_match_5(self): self.assert_compile(self.table1.c.myid.match('somstr'), "CONTAINS (mytable.myid, :myid_1)", dialect=oracle.dialect())
def test_postgresql(): from mixer.backend.sqlalchemy import TypeMixer from sqlalchemy.dialects.postgresql import UUID, JSONB base = declarative_base() class Test(base): __tablename__ = 'test' id = Column(Integer, primary_key=True) uuid = Column(UUID, nullable=False) jsonb = Column(JSONB, nullable=False) mixer = TypeMixer(Test) test = mixer.blend() assert test.uuid @pytest.mark.parametrize('dialect, expected', [ (mssql.dialect(), 'RAND()'), (mysql.dialect(), 'RAND()'), (oracle.dialect(), 'DBMS_RANDOM.VALUE'), (postgresql.dialect(), 'RANDOM()'), (sqlite.dialect(), 'RANDOM()'), ]) def test_random_compiled(dialect, expected): from mixer.backend.sqlalchemy import random compiled = random().compile(dialect=dialect) assert text_type(compiled) == expected
# sqlite print( "sqlite sql statement: " + str(expr) ) # have to type explicitely to str because + operator is overloaded for expressions print("sqlite sql statement: " + str(expr.compile())) # explicit compiling # mysql from sqlalchemy.dialects import mysql print("mysql sql statement: " + str(expr.compile(dialect=mysql.dialect()))) # user.name = %s # oracle from sqlalchemy.dialects import oracle print("oracle sql statement: " + str(expr.compile(dialect=oracle.dialect()))) # "user".name = :name_1 # expression left and right parameters print(repr(expr.left)) print(repr(expr.right)) print( repr(expr.operator) ) # native python build-in function. example: import operator; operation.eq(x,y) # compiled object comp = expr.compile() # get parameters of compiled expression (in expression it self the param was replaced by bound params :name_1) print(comp.params) # {'name_1': 'slavo'} result = engine.execute(