def visit_merge(element, compiler, **kw):
msql = "MERGE INTO %s " % compiler.process(element._target_table,
asfrom=True)
msql += "USING %s " % compiler.process(element._source_expr,
asfrom=True)
msql += "ON ( %s ) " % compiler.process(element._on)
if element._merge_update_values is not None:
cols = crud._get_crud_params(compiler, element._merge_update_values)
msql += "\nWHEN MATCHED THEN UPDATE SET "
msql += ', '.join(compiler.visit_column(c[0]) + '=' + c[1] for c in cols)
if element._merge_delete:
msql += "\nDELETE "
if element._delete_where is not None:
msql += " WHERE %s" % compiler.process(element._delete_where)
else:
if element._update_where is not None:
msql += " WHERE %s" % compiler.process(element._update_where)
else:
if element._merge_delete:
msql += "\nWHEN MATCHED THEN DELETE "
if element._delete_where is not None:
msql += "WHERE %s" % compiler.process(element._delete_where)
if element._merge_insert_values is not None:
cols = crud._get_crud_params(compiler, element._merge_insert_values)
msql += "\nWHEN NOT MATCHED THEN INSERT "
msql += "(%s) " % ', '.join(compiler.visit_column(c[0]) for c in cols)
msql += "VALUES (%s) " % ', '.join(c[1] for c in cols)
if element._insert_where is not None:
msql += "WHERE %s" % compiler.process(element._insert_where)
return msql
def visit_merge(element, compiler, **kw):
msql = "MERGE INTO %s " % compiler.process(element._target_table,
asfrom=True)
msql += "USING %s " % compiler.process(element._source_expr,
asfrom=True)
msql += "ON ( %s ) " % compiler.process(element._on)
if element._merge_update_values is not None:
cols = crud._get_crud_params(compiler, element._merge_update_values)
msql += "\nWHEN MATCHED THEN UPDATE SET "
msql += ', '.join(compiler.visit_column(c[0]) + '=' + c[1] for c in cols)
if element._merge_delete:
msql += "\nDELETE "
if element._delete_where is not None:
msql += " WHERE %s" % compiler.process(element._delete_where)
else:
if element._update_where is not None:
msql += " WHERE %s" % compiler.process(element._update_where)
else:
if element._merge_delete:
msql += "\nWHEN MATCHED THEN DELETE "
if element._delete_where is not None:
msql += "WHERE %s" % compiler.process(element._delete_where)
if element._merge_insert_values is not None:
cols = crud._get_crud_params(compiler, element._merge_insert_values)
msql += "\nWHEN NOT MATCHED THEN INSERT "
msql += "(%s) " % ', '.join(compiler.visit_column(c[0]) for c in cols)
msql += "VALUES (%s) " % ', '.join(c[1] for c in cols)
if element._insert_where is not None:
msql += "WHERE %s" % compiler.process(element._insert_where)
return msql
def _setup_crud_params(compiler, stmt, local_stmt_type, **kw): restore_isinsert = compiler.isinsert restore_isupdate = compiler.isupdate restore_isdelete = compiler.isdelete should_restore = ( restore_isinsert or restore_isupdate or restore_isdelete ) or len(compiler.stack) > 1 if local_stmt_type is ISINSERT: compiler.isupdate = False compiler.isinsert = True elif local_stmt_type is ISUPDATE: compiler.isupdate = True compiler.isinsert = False elif local_stmt_type is ISDELETE: if not should_restore: compiler.isdelete = True else: assert False, "ISINSERT, ISUPDATE, or ISDELETE expected" try: if local_stmt_type in (ISINSERT, ISUPDATE): return crud._get_crud_params(compiler, stmt, **kw) finally: if should_restore: compiler.isinsert = restore_isinsert compiler.isupdate = restore_isupdate compiler.isdelete = restore_isdelete
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 __compileInsert(insert_stmt, compiler, **kw):
'''
Compiles INSERT statements.
'''
compiler.stack.append({
'correlate_froms': set(),
'asfrom_froms': set(),
'selectable': insert_stmt,
})
compiler.isinsert = True
crud_params = crud._get_crud_params(compiler, insert_stmt, **kw)
if not crud_params:
return
if insert_stmt._has_multi_parameters \
and not compiler.dialect.supports_multivalues_insert:
return
dml = 'INSERT INTO ' + compiler.preparer.format_table(insert_stmt.table)
separator = ',' if SchemaGenerator.minified else ', '
if insert_stmt.select is not None:
dml += ' {}'.format(
compiler.process(compiler._insert_from_select, **kw))
elif insert_stmt._has_multi_parameters:
dml += ' VALUES'
if not SchemaGenerator.minified:
dml += ' '
dml += separator.join(
'({})'.format(separator.join(
param[1] for param in crud_param_set
))
for crud_param_set in crud_params
)
else:
dml += ' VALUES'
if not SchemaGenerator.minified:
dml += ' '
dml += '({})'.format(separator.join([
param[1] for param in crud_params
]))
compiler.stack.pop(-1)
return dml + ';'
def __compileInsert(insert_stmt, compiler, **kw):
'''
Compiles INSERT statements.
'''
compiler.stack.append({
'correlate_froms': set(),
'asfrom_froms': set(),
'selectable': insert_stmt,
})
compiler.isinsert = True
crud_params = crud._get_crud_params(compiler, insert_stmt, **kw)
if not crud_params:
return
if insert_stmt._has_multi_parameters \
and not compiler.dialect.supports_multivalues_insert:
return
dml = 'INSERT INTO ' + compiler.preparer.format_table(
insert_stmt.table)
separator = ',' if SchemaGenerator.minified else ', '
if insert_stmt.select is not None:
dml += ' {}'.format(
compiler.process(compiler._insert_from_select, **kw))
elif insert_stmt._has_multi_parameters:
dml += ' VALUES'
if not SchemaGenerator.minified:
dml += ' '
dml += separator.join('({})'.format(
separator.join(param[1] for param in crud_param_set))
for crud_param_set in crud_params)
else:
dml += ' VALUES'
if not SchemaGenerator.minified:
dml += ' '
dml += '({})'.format(
separator.join([param[1] for param in crud_params]))
compiler.stack.pop(-1)
return dml + ';'
def compile_insertOnDuplicateKeyUpdate(insert_stmt, compiler, **kw):
compiler.isinsert = True
try:
# pylint: disable=E0611
from sqlalchemy.sql import crud
colparams = crud._get_crud_params(compiler, insert_stmt)
except ImportError: # SQLAlchemy <= 1.0
colparams = compiler._get_colparams(insert_stmt)
if not colparams and \
not compiler.dialect.supports_default_values and \
not compiler.dialect.supports_empty_insert:
raise exc.CompileError("The version of %s you are using does "
"not support empty inserts." %
compiler.dialect.name)
preparer = compiler.preparer
supports_default_values = compiler.dialect.supports_default_values
text = "INSERT"
prefixes = [compiler.process(x) for x in insert_stmt._prefixes]
if prefixes:
text += " " + " ".join(prefixes)
text += " INTO " + preparer.format_table(insert_stmt.table)
if colparams or not supports_default_values:
text += " (%s)" % ', '.join([preparer.format_column(c[0])
for c in colparams])
if compiler.returning or insert_stmt._returning:
compiler.returning = compiler.returning or insert_stmt._returning
returning_clause = compiler.returning_clause(insert_stmt, compiler.returning)
if compiler.returning_precedes_values:
text += " " + returning_clause
if not colparams and supports_default_values:
text += " DEFAULT VALUES"
else:
text += " VALUES (%s)" % \
', '.join([c[1] for c in colparams])
if compiler.returning and not compiler.returning_precedes_values:
text += " " + returning_clause
text += ' ON DUPLICATE KEY UPDATE ' + \
', '.join(
compiler.preparer.quote(c[0].name, c[0].quote) + '=' + c[1]
for c in colparams
)
return text
def visit_update(self, update_stmt, **kw):
if not self.dialect.supports_update:
raise exc.CompileError(
'ALTER UPDATE is not supported by this server version')
compile_state = update_stmt._compile_state_factory(
update_stmt, self, **kw)
update_stmt = compile_state.statement
render_extra_froms = []
correlate_froms = {update_stmt.table}
self.stack.append({
"correlate_froms": correlate_froms,
"asfrom_froms": correlate_froms,
"selectable": update_stmt,
})
text = "ALTER TABLE "
table_text = self.update_tables_clause(update_stmt, update_stmt.table,
render_extra_froms, **kw)
crud_params = crud._get_crud_params(self, update_stmt, compile_state,
**kw)
text += table_text
text += " UPDATE "
text += ", ".join(expr + "=" + value for c, expr, value in crud_params)
if update_stmt._where_criteria:
t = self._generate_delimited_and_list(update_stmt._where_criteria,
include_table=False,
**kw)
if t:
text += " WHERE " + t
else:
raise exc.CompileError('WHERE clause is required')
self.stack.pop(-1)
return text
def visit_insert(self, insert_stmt, asfrom=False, **kw):
"""
used to compile <sql.expression.Insert> expressions.
this function wraps insert_from_select statements inside
parentheses to be conform with earlier versions of CreateDB.
"""
self.stack.append({
'correlate_froms': set(),
"asfrom_froms": set(),
"selectable": insert_stmt
})
self.isinsert = True
crud_params = crud._get_crud_params(self, insert_stmt, **kw)
if not crud_params and \
not self.dialect.supports_default_values and \
not self.dialect.supports_empty_insert:
raise NotImplementedError(
"The '%s' dialect with current database version settings does "
"not support empty inserts." % self.dialect.name)
if insert_stmt._has_multi_parameters:
if not self.dialect.supports_multivalues_insert:
raise NotImplementedError(
"The '%s' dialect with current database "
"version settings does not support "
"in-place multirow inserts." % self.dialect.name)
crud_params_single = crud_params[0]
else:
crud_params_single = crud_params
preparer = self.preparer
supports_default_values = self.dialect.supports_default_values
text = "INSERT "
if insert_stmt._prefixes:
text += self._generate_prefixes(insert_stmt, insert_stmt._prefixes,
**kw)
text += "INTO "
table_text = preparer.format_table(insert_stmt.table)
if insert_stmt._hints:
dialect_hints = dict([
(table, hint_text)
for (table, dialect), hint_text in insert_stmt._hints.items()
if dialect in ('*', self.dialect.name)
])
if insert_stmt.table in dialect_hints:
table_text = self.format_from_hint_text(
table_text, insert_stmt.table,
dialect_hints[insert_stmt.table], True)
text += table_text
if crud_params_single or not supports_default_values:
text += " (%s)" % ', '.join(
[preparer.format_column(c[0]) for c in crud_params_single])
if self.returning or insert_stmt._returning:
self.returning = self.returning or insert_stmt._returning
returning_clause = self.returning_clause(insert_stmt,
self.returning)
if self.returning_precedes_values:
text += " " + returning_clause
if insert_stmt.select is not None:
text += " (%s)" % self.process(self._insert_from_select, **kw)
elif not crud_params and supports_default_values:
text += " DEFAULT VALUES"
elif insert_stmt._has_multi_parameters:
text += " VALUES %s" % (", ".join(
"(%s)" % (', '.join(c[1] for c in crud_param_set))
for crud_param_set in crud_params))
else:
text += " VALUES (%s)" % \
', '.join([c[1] for c in crud_params])
if self.returning and not self.returning_precedes_values:
text += " " + returning_clause
self.stack.pop(-1)
return text
def visit_insert(self, insert_stmt, asfrom=False, **kw):
"""
used to compile <sql.expression.Insert> expressions.
this function wraps insert_from_select statements inside
parentheses to be conform with earlier versions of CreateDB.
"""
self.stack.append(
{'correlate_froms': set(),
"asfrom_froms": set(),
"selectable": insert_stmt})
self.isinsert = True
crud_params = crud._get_crud_params(self, insert_stmt, **kw)
if not crud_params and \
not self.dialect.supports_default_values and \
not self.dialect.supports_empty_insert:
raise NotImplementedError(
"The '%s' dialect with current database version settings does "
"not support empty inserts." % self.dialect.name)
if insert_stmt._has_multi_parameters:
if not self.dialect.supports_multivalues_insert:
raise NotImplementedError(
"The '%s' dialect with current database "
"version settings does not support "
"in-place multirow inserts." % self.dialect.name)
crud_params_single = crud_params[0]
else:
crud_params_single = crud_params
preparer = self.preparer
supports_default_values = self.dialect.supports_default_values
text = "INSERT "
if insert_stmt._prefixes:
text += self._generate_prefixes(insert_stmt,
insert_stmt._prefixes, **kw)
text += "INTO "
table_text = preparer.format_table(insert_stmt.table)
if insert_stmt._hints:
dialect_hints = dict([
(table, hint_text)
for (table, dialect), hint_text in
insert_stmt._hints.items()
if dialect in ('*', self.dialect.name)
])
if insert_stmt.table in dialect_hints:
table_text = self.format_from_hint_text(
table_text,
insert_stmt.table,
dialect_hints[insert_stmt.table],
True
)
text += table_text
if crud_params_single or not supports_default_values:
text += " (%s)" % ', '.join([preparer.format_column(c[0])
for c in crud_params_single])
if self.returning or insert_stmt._returning:
self.returning = self.returning or insert_stmt._returning
returning_clause = self.returning_clause(
insert_stmt, self.returning)
if self.returning_precedes_values:
text += " " + returning_clause
if insert_stmt.select is not None:
text += " (%s)" % self.process(self._insert_from_select, **kw)
elif not crud_params and supports_default_values:
text += " DEFAULT VALUES"
elif insert_stmt._has_multi_parameters:
text += " VALUES %s" % (
", ".join(
"(%s)" % (
', '.join(c[1] for c in crud_param_set)
)
for crud_param_set in crud_params
)
)
else:
text += " VALUES (%s)" % \
', '.join([c[1] for c in crud_params])
if self.returning and not self.returning_precedes_values:
text += " " + returning_clause
self.stack.pop(-1)
return text
def visit_insert(self, insert_stmt, **kw):
self.stack.append({
'correlate_froms': set(),
'asfrom_froms': set(),
'selectable': insert_stmt
})
self.isinsert = True
crud_params = crud._get_crud_params(self, insert_stmt, **kw)
if not crud_params and \
not self.dialect.supports_default_values and \
not self.dialect.supports_empty_insert:
raise exc.CompileError("The '%s' dialect with current database "
"version settings does not support empty "
"inserts." % self.dialect.name)
if insert_stmt._has_multi_parameters:
if not self.dialect.supports_multivalues_insert:
raise exc.CompileError(
"The '%s' dialect with current database "
"version settings does not support "
"in-place multirow inserts." % self.dialect.name)
crud_params_single = crud_params[0]
else:
crud_params_single = crud_params
preparer = self.preparer
supports_default_values = self.dialect.supports_default_values
jsn = {"command": "advanced/insert"}
if insert_stmt._prefixes:
text += self._generate_prefixes(insert_stmt, insert_stmt._prefixes,
**kw)
# table_text = preparer.format_table(insert_stmt.table)
table_text = insert_stmt.table._compiler_dispatch(self,
asfrom=True,
iscrud=True)
if insert_stmt._hints:
dialect_hints = dict([
(table, hint_text)
for (table, dialect), hint_text in insert_stmt._hints.items()
if dialect in ('*', self.dialect.name)
])
if insert_stmt.table in dialect_hints:
table_text = self.format_from_hint_text(
table_text, insert_stmt.table,
dialect_hints[insert_stmt.table], True)
jsn['table'] = table_text['table']
jsn['schema'] = table_text.get('schema', DEFAULT_SCHEMA)
if crud_params_single or not supports_default_values:
jsn["fields"] = [
preparer.format_column(c[0]) for c in crud_params_single
]
if self.returning or insert_stmt._returning:
self.returning = self.returning or insert_stmt._returning
returning_clause = self.returning_clause(insert_stmt,
self.returning)
if self.returning_precedes_values:
jsn["returning_insert"] = returning_clause
if insert_stmt.select is not None:
jsn['values'] = self.process(self._insert_from_select, **kw)
jsn['method'] = 'select'
elif not crud_params and supports_default_values:
jsn['values'] = " DEFAULT VALUES"
elif insert_stmt._has_multi_parameters:
jsn['values'] = [[c[1] for c in crud_param_set]
for crud_param_set in crud_params]
else:
jsn['values'] = [[c[1] for c in crud_params]]
if self.returning and not self.returning_precedes_values:
jsn["returning"] = returning_clause
return jsn
def visit_insert(self, insert_stmt, **kw):
self.stack.append(
{'correlate_froms': set(),
'asfrom_froms': set(),
'selectable': insert_stmt})
self.isinsert = True
crud_params = crud._get_crud_params(self, insert_stmt, **kw)
if not crud_params and \
not self.dialect.supports_default_values and \
not self.dialect.supports_empty_insert:
raise exc.CompileError("The '%s' dialect with current database "
"version settings does not support empty "
"inserts." %
self.dialect.name)
if insert_stmt._has_multi_parameters:
if not self.dialect.supports_multivalues_insert:
raise exc.CompileError(
"The '%s' dialect with current database "
"version settings does not support "
"in-place multirow inserts." %
self.dialect.name)
crud_params_single = crud_params[0]
else:
crud_params_single = crud_params
preparer = self.preparer
supports_default_values = self.dialect.supports_default_values
jsn = {"command": "advanced/insert"}
if insert_stmt._prefixes:
text += self._generate_prefixes(insert_stmt,
insert_stmt._prefixes, **kw)
# table_text = preparer.format_table(insert_stmt.table)
table_text = insert_stmt.table._compiler_dispatch(
self, asfrom=True, iscrud=True)
if insert_stmt._hints:
dialect_hints = dict([
(table, hint_text)
for (table, dialect), hint_text in
insert_stmt._hints.items()
if dialect in ('*', self.dialect.name)
])
if insert_stmt.table in dialect_hints:
table_text = self.format_from_hint_text(
table_text,
insert_stmt.table,
dialect_hints[insert_stmt.table],
True
)
jsn['table'] = table_text['table']
jsn['schema'] = table_text.get('schema', DEFAULT_SCHEMA)
if crud_params_single or not supports_default_values:
jsn["fields"] = [preparer.format_column(c[0])
for c in crud_params_single]
if self.returning or insert_stmt._returning:
self.returning = self.returning or insert_stmt._returning
returning_clause = self.returning_clause(
insert_stmt, self.returning)
if self.returning_precedes_values:
jsn["returning_insert"] = returning_clause
if insert_stmt.select is not None:
jsn['values'] = self.process(self._insert_from_select, **kw)
jsn['method'] = 'select'
elif not crud_params and supports_default_values:
jsn['values'] = " DEFAULT VALUES"
elif insert_stmt._has_multi_parameters:
jsn['values'] = [[c[1] for c in crud_param_set]
for crud_param_set in crud_params]
else:
jsn['values'] = [[c[1] for c in crud_params]]
if self.returning and not self.returning_precedes_values:
jsn["returning"] = returning_clause
return jsn
