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 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 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), )