def visit_conditional_insert(element, compiler, **kwargs):
# magic copied from sqlalchemy.sql.compiler.SQLCompiler.visit_insert
compiler.isinsert = True
try:
# pylint: disable=E0611
from sqlalchemy.sql import crud
colparams = crud._get_crud_params(compiler, element)
except ImportError: # SQLAlchemy <= 1.0
colparams = compiler._get_colparams(element)
text = 'INSERT INTO %s' % compiler.process(element.table, asfrom=True)
text += ' (%s)\n' % ', '.join(compiler.preparer.format_column(c[0])
for c in colparams)
text += 'SELECT %s\n' % ', '.join(c[1] for c in colparams)
text += compiler.default_from()
# default_from() returns '' for MySQL but that's wrong, MySQL requires
# FROM DUAL if there is a following WHERE clause.
if isinstance(compiler.dialect, MySQLDialect):
text += 'FROM DUAL\n'
# We need FOR UPDATE in the inner SELECT for MySQL, to ensure we acquire an
# exclusive lock immediately, instead of acquiring a shared lock and then
# subsequently upgrading it to an exclusive lock, which is subject to
# deadlocks if another transaction is doing the same thing.
nonexistence_clause = not_(exists(Select(
columns=[sqltext('1')], from_obj=[element.table],
whereclause=element.unique_condition, for_update=True)))
text += 'WHERE ' + compiler.process(nonexistence_clause)
return text
def _group_concat_mysql(element, compiler, **kw):
if len(element.clauses) == 2:
seperator = compiler.process(element.clauses.clauses[1])
else:
seperator = '->'
print compiler.process(element.clauses.clauses[0]) + "---" +compiler.process(element.clauses.clauses[1]) + "---" + seperator
return 'GROUP_CONCAT(%s SEPARATOR %s)'.format(
compiler.process(element.clauses.clauses[0]),
seperator
)
def _group_concat_postgresql(element, compiler, **kw):
if len(element.clauses) == 2:
separator = compiler.process(element.clauses.clauses[1])
else:
separator = ','
res = 'array_to_string(array_agg({0}), \'{1}\')'.format(
compiler.process(element.clauses.clauses[0]),
separator,
)
return res
def _group_concat_postgresql(element, compiler, **kw):
if len(element.clauses) == 2:
separator = compiler.process(element.clauses.clauses[1])
else:
separator = ','
res = 'array_to_string(array_agg({0}), \'{1}\')'.format(
compiler.process(element.clauses.clauses[0]),
separator,
)
return res
def compile_group_concat(element, compiler, **kw):
if element.separator:
return "GROUP_CONCAT(%s ORDER BY %s SEPARATOR '%s')" % (
compiler.process(element.col1),
compiler.process(element.col2),
element.separator,
)
else:
return "GROUP_CONCAT(%s ORDER BY %s)" % (
compiler.process(element.col1),
compiler.process(element.col2),
)
def _group_concat_sep_sqlite(element, compiler, **kw):
"""
A work-around for an SQLite bug:
http://sqlite.1065341.n5.nabble.com/GROUP-CONCAT-with-DISTINCT-bug-td99696.html
"""
if len(element.clauses) == 2:
separator = compiler.process(element.clauses.clauses[1])
else:
separator = ','
return "REPLACE(REPLACE(GROUP_CONCAT(DISTINCT REPLACE({}, ',', '💩')), ',', {}), '💩', ',')".format(
compiler.process(element.clauses.clauses[0]),
separator,
)
def printquery(statement, bind=None):
"""
print a query, with values filled in
for debugging purposes *only*
for security, you should always separate queries from their values
please also note that this function is quite slow
"""
if isinstance(statement, Query):
if bind is None:
bind = statement.session.get_bind(statement._mapper_zero_or_none())
statement = statement.statement
elif bind is None:
bind = statement.bind
dialect = bind.dialect
compiler = statement._compiler(dialect)
class LiteralCompiler(compiler.__class__):
def visit_bindparam(self, bindparam, within_columns_clause=False, literal_binds=False, **kwargs):
return super(LiteralCompiler, self).render_literal_bindparam(
bindparam, within_columns_clause=within_columns_clause, literal_binds=literal_binds, **kwargs
)
compiler = LiteralCompiler(dialect, statement)
print(compiler.process(statement) + ";")
def printquery(statement, bind=None):
"""
print a query, with values filled in
for debugging purposes *only*
for security, you should always separate queries from their values
please also note that this function is quite slow
"""
if isinstance(statement, Query):
if bind is None:
bind = statement.session.get_bind(statement._mapper_zero_or_none())
statement = statement.statement
elif bind is None:
bind = statement.bind
dialect = bind.dialect
compiler = statement._compiler(dialect)
class LiteralCompiler(compiler.__class__):
def visit_bindparam(self,
bindparam,
within_columns_clause=False,
literal_binds=False,
**kwargs):
return super(LiteralCompiler, self).render_literal_bindparam(
bindparam,
within_columns_clause=within_columns_clause,
literal_binds=literal_binds,
**kwargs)
compiler = LiteralCompiler(dialect, statement)
print(compiler.process(statement) + ";")
def visit_conditional_insert(element, compiler, **kwargs):
# magic copied from sqlalchemy.sql.compiler.SQLCompiler.visit_insert
compiler.isinsert = True
colparams = compiler._get_colparams(element)
text = 'INSERT INTO %s' % compiler.process(element.table, asfrom=True)
text += ' (%s)\n' % ', '.join(compiler.process(c[0]) for c in colparams)
text += 'SELECT %s\n' % ', '.join(c[1] for c in colparams)
text += 'FROM DUAL\n'
# We need FOR UPDATE in the inner SELECT for MySQL, to ensure we acquire an
# exclusive lock immediately, instead of acquiring a shared lock and then
# subsequently upgrading it to an exclusive lock, which is subject to
# deadlocks if another transaction is doing the same thing.
text += 'WHERE NOT EXISTS (SELECT 1 FROM %s\nWHERE %s FOR UPDATE)' % (
compiler.process(element.table, asfrom=True),
compiler.process(element.unique_condition))
return text
def pg_explain(element, compiler, **kw):
text = "EXPLAIN "
if element.analyze:
text += "ANALYZE "
if element.verbose:
text += "VERBOSE "
text += compiler.process(element.statement, **kw)
return text
def pg_explain(element, compiler, **kw):
text = "EXPLAIN "
if element.analyze:
text += "ANALYZE "
if element.verbose:
text += "VERBOSE "
text += compiler.process(element.statement, **kw)
return text
def compile_do_nothing(element, compiler, **kw):
conflict_target = ''
if element.index_elements is not None:
index_columns = [
compiler.process(i, **kw) for i in element.index_elements
]
conflict_target = f'({",".join(index_columns)})'
return f'ON CONFLICT {conflict_target} DO NOTHING'
def _group_concat_mysql(element, compiler, **kwargs):
expr = compiler.process(element.clauses.clauses[0])
def process_clause(idx):
return compiler.process(element.clauses.clauses[idx])
separator = process_clause(1) if len(element.clauses) > 1 else None
order_by = process_clause(2) if len(element.clauses) > 2 else None
inner = expr
if order_by is not None:
inner += " ORDER BY %s" % order_by
if separator is not None:
inner += " SEPARATOR %s" % separator
return 'GROUP_CONCAT(%s)' % inner
def compile_do_update(element, compiler, **kw):
conflict_target = ''
if element.index_elements is not None:
index_columns = [
compiler.process(i, **kw) for i in element.index_elements
]
conflict_target = f'({",".join(index_columns)})'
values = []
for k, v in element.upsert_values.items():
values.append(f'{k} = {v}')
set_expr = ','.join(values)
return f'ON CONFLICT {conflict_target} DO UPDATE SET {set_expr}'
def _merge(merge: Any, compiler: Any, **kwargs: Any) -> Any:
"""Generate MERGE query for inserting or updating records.
"""
table = merge.table
preparer = compiler.preparer
allColumns = [col.name for col in table.columns]
pkColumns = [col.name for col in table.primary_key]
nonPkColumns = [col for col in allColumns if col not in pkColumns]
# To properly support type decorators defined in core/ddl.py we need
# to pass column type to `bindparam`.
selectColumns = [
sqlalchemy.sql.bindparam(col.name, type_=col.type).label(col.name)
for col in table.columns
]
selectClause = sqlalchemy.sql.select(selectColumns)
tableAlias = table.alias("t")
tableAliasText = compiler.process(tableAlias, asfrom=True, **kwargs)
selectAlias = selectClause.alias("d")
selectAliasText = compiler.process(selectAlias, asfrom=True, **kwargs)
condition = sqlalchemy.sql.and_(*[
tableAlias.columns[col] == selectAlias.columns[col]
for col in pkColumns
])
conditionText = compiler.process(condition, **kwargs)
query = f"MERGE INTO {tableAliasText}" \
f"\nUSING {selectAliasText}" \
f"\nON ({conditionText})"
updates = []
for col in nonPkColumns:
src = compiler.process(selectAlias.columns[col], **kwargs)
dst = compiler.process(tableAlias.columns[col], **kwargs)
updates.append(f"{dst} = {src}")
text = ", ".join(updates)
query += f"\nWHEN MATCHED THEN UPDATE SET {text}"
insertColumns = ", ".join(
[preparer.format_column(col) for col in table.columns])
insertValues = ", ".join([
compiler.process(selectAlias.columns[col], **kwargs)
for col in allColumns
])
query += f"\nWHEN NOT MATCHED THEN INSERT ({insertColumns}) VALUES ({insertValues})"
return query
def _visit_insert_from_select(element, compiler, **kw):
return "INSERT INTO %s %s" % (
compiler.process(element.table, asfrom=True),
compiler.process(element.select)
)
def visit_insert_from_select(element, compiler, **kw):
return "INSERT INTO %s (%s)" % (compiler.process(
element.table, asfrom=True), compiler.process(element.select))
def compile(element, compiler, **kwargs):
return (
"%s INTO OUTFILE '%s' "
"FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' "
"LINES TERMINATED BY '\\n'"
) % (compiler.process(element.select), element.path)
def process_clause(idx):
return compiler.process(element.clauses.clauses[idx])
def compile(element, compiler, **kwargs):
return ("%s INTO OUTFILE '%s' "
"FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' "
"LINES TERMINATED BY '\\n'") % (compiler.process(
element.select), element.path)
def compile_group_fasta(element, compiler, **kw):
return 'group_concat(">" || {} || "\n" || {}, "\n")'.format(
compiler.process(element.header),
compiler.process(element.sequence),
)