def _get_column_info(self, *args, **kwargs):
kw = kwargs.copy()
encode = kw.pop('encode', None)
if sa_version >= Version('1.3.16'):
# SQLAlchemy 1.3.16 introduced generated columns,
# not supported in redshift
kw['generated'] = ''
if sa_version < Version('1.4.0') and 'identity' in kw:
del kw['identity']
elif sa_version >= Version('1.4.0') and 'identity' not in kw:
kw['identity'] = None
column_info = super(RedshiftDialectMixin, self)._get_column_info(
*args,
**kw
)
if isinstance(column_info['type'], VARCHAR):
if column_info['type'].length is None:
column_info['type'] = NullType()
if 'info' not in column_info:
column_info['info'] = {}
if encode and encode != 'none':
column_info['info']['encode'] = encode
return column_info
def _get_column_info(self, name, format_type, default, notnull, domains,
enums, schema):
column_info = super(RedshiftDialect,
self)._get_column_info(name, format_type, default,
notnull, domains, enums,
schema)
if isinstance(column_info['type'],
VARCHAR) and column_info['type'].length is None:
column_info['type'] = NullType()
return column_info
def test_typing_construction(self):
t = text("select * from table :foo :bar :bat")
self._assert_type_map(t, {
"foo": NullType(),
"bar": NullType(),
"bat": NullType()
})
t = t.bindparams(bindparam("foo", type_=String))
self._assert_type_map(t, {
"foo": String(),
"bar": NullType(),
"bat": NullType()
})
t = t.bindparams(bindparam("bar", type_=Integer))
self._assert_type_map(t, {
"foo": String(),
"bar": Integer(),
"bat": NullType()
})
t = t.bindparams(bat=45.564)
self._assert_type_map(t, {
"foo": String(),
"bar": Integer(),
"bat": Float()
})
def _get_column_info(self, *args, **kwargs):
kw = kwargs.copy()
encode = kw.pop('encode', None)
column_info = super(RedshiftDialect,
self)._get_column_info(*args, **kw)
if isinstance(column_info['type'], VARCHAR):
if column_info['type'].length is None:
column_info['type'] = NullType()
if 'info' not in column_info:
column_info['info'] = {}
if encode and encode != 'none':
column_info['info']['encode'] = encode
return column_info
def _get_column_info(self, *args, **kwargs):
kw = kwargs.copy()
encode = kw.pop('encode', None)
if sa.__version__ < '1.2.0':
# SQLAlchemy 1.2.0 introduced the 'comment' param
del kw['comment']
column_info = super(RedshiftDialect,
self)._get_column_info(*args, **kw)
if isinstance(column_info['type'], VARCHAR):
if column_info['type'].length is None:
column_info['type'] = NullType()
if 'info' not in column_info:
column_info['info'] = {}
if encode and encode != 'none':
column_info['info']['encode'] = encode
return column_info
def ordered_values(self, *args):
"""Specify the VALUES clause of this UPDATE statement with an explicit
parameter ordering that will be maintained in the SET clause of the
resulting UPDATE statement.
E.g.::
stmt = table.update().ordered_values(
("name", "ed"), ("ident": "foo")
)
.. seealso::
:ref:`updates_order_parameters` - full example of the
:meth:`_expression.Update.ordered_values` method.
.. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values`
method
supersedes the
:paramref:`_expression.update.preserve_parameter_order`
parameter, which will be removed in SQLAlchemy 2.0.
"""
if self._values:
raise exc.ArgumentError(
"This statement already has values present"
)
elif self._ordered_values:
raise exc.ArgumentError(
"This statement already has ordered values present"
)
arg = [
(
coercions.expect(roles.DMLColumnRole, k),
coercions.expect(
roles.ExpressionElementRole,
v,
type_=NullType(),
is_crud=True,
),
)
for k, v in args
]
self._ordered_values = arg
def col2_dict(key):
return {
'name': 'col2',
'type': NullType()
}.get(key, Mock())
def values(self, *args, **kwargs):
r"""Specify a fixed VALUES clause for an INSERT statement, or the SET
clause for an UPDATE.
Note that the :class:`_expression.Insert` and
:class:`_expression.Update`
constructs support
per-execution time formatting of the VALUES and/or SET clauses,
based on the arguments passed to :meth:`_engine.Connection.execute`.
However, the :meth:`.ValuesBase.values` method can be used to "fix" a
particular set of parameters into the statement.
Multiple calls to :meth:`.ValuesBase.values` will produce a new
construct, each one with the parameter list modified to include
the new parameters sent. In the typical case of a single
dictionary of parameters, the newly passed keys will replace
the same keys in the previous construct. In the case of a list-based
"multiple values" construct, each new list of values is extended
onto the existing list of values.
:param \**kwargs: key value pairs representing the string key
of a :class:`_schema.Column`
mapped to the value to be rendered into the
VALUES or SET clause::
users.insert().values(name="some name")
users.update().where(users.c.id==5).values(name="some name")
:param \*args: As an alternative to passing key/value parameters,
a dictionary, tuple, or list of dictionaries or tuples can be passed
as a single positional argument in order to form the VALUES or
SET clause of the statement. The forms that are accepted vary
based on whether this is an :class:`_expression.Insert` or an
:class:`_expression.Update` construct.
For either an :class:`_expression.Insert` or
:class:`_expression.Update`
construct, a single dictionary can be passed, which works the same as
that of the kwargs form::
users.insert().values({"name": "some name"})
users.update().values({"name": "some new name"})
Also for either form but more typically for the
:class:`_expression.Insert` construct, a tuple that contains an
entry for every column in the table is also accepted::
users.insert().values((5, "some name"))
The :class:`_expression.Insert` construct also supports being
passed a list of dictionaries or full-table-tuples, which on the
server will render the less common SQL syntax of "multiple values" -
this syntax is supported on backends such as SQLite, PostgreSQL,
MySQL, but not necessarily others::
users.insert().values([
{"name": "some name"},
{"name": "some other name"},
{"name": "yet another name"},
])
The above form would render a multiple VALUES statement similar to::
INSERT INTO users (name) VALUES
(:name_1),
(:name_2),
(:name_3)
It is essential to note that **passing multiple values is
NOT the same as using traditional executemany() form**. The above
syntax is a **special** syntax not typically used. To emit an
INSERT statement against multiple rows, the normal method is
to pass a multiple values list to the
:meth:`_engine.Connection.execute`
method, which is supported by all database backends and is generally
more efficient for a very large number of parameters.
.. seealso::
:ref:`execute_multiple` - an introduction to
the traditional Core method of multiple parameter set
invocation for INSERTs and other statements.
.. versionchanged:: 1.0.0 an INSERT that uses a multiple-VALUES
clause, even a list of length one,
implies that the :paramref:`_expression.Insert.inline`
flag is set to
True, indicating that the statement will not attempt to fetch
the "last inserted primary key" or other defaults. The
statement deals with an arbitrary number of rows, so the
:attr:`_engine.CursorResult.inserted_primary_key`
accessor does not
apply.
.. versionchanged:: 1.0.0 A multiple-VALUES INSERT now supports
columns with Python side default values and callables in the
same way as that of an "executemany" style of invocation; the
callable is invoked for each row. See :ref:`bug_3288`
for other details.
The UPDATE construct also supports rendering the SET parameters
in a specific order. For this feature refer to the
:meth:`_expression.Update.ordered_values` method.
.. seealso::
:meth:`_expression.Update.ordered_values`
.. seealso::
:ref:`inserts_and_updates` - SQL Expression
Language Tutorial
:func:`_expression.insert` - produce an ``INSERT`` statement
:func:`_expression.update` - produce an ``UPDATE`` statement
"""
if self._select_names:
raise exc.InvalidRequestError(
"This construct already inserts from a SELECT")
elif self._ordered_values:
raise exc.ArgumentError(
"This statement already has ordered values present")
if args:
# positional case. this is currently expensive. we don't
# yet have positional-only args so we have to check the length.
# then we need to check multiparams vs. single dictionary.
# since the parameter format is needed in order to determine
# a cache key, we need to determine this up front.
arg = args[0]
if kwargs:
raise exc.ArgumentError(
"Can't pass positional and kwargs to values() "
"simultaneously")
elif len(args) > 1:
raise exc.ArgumentError(
"Only a single dictionary/tuple or list of "
"dictionaries/tuples is accepted positionally.")
elif not self._preserve_parameter_order and isinstance(
arg, collections_abc.Sequence):
if arg and isinstance(arg[0], (list, dict, tuple)):
self._multi_values += (arg, )
return
# tuple values
arg = {c.key: value for c, value in zip(self.table.c, arg)}
elif self._preserve_parameter_order and not isinstance(
arg, collections_abc.Sequence):
raise ValueError("When preserve_parameter_order is True, "
"values() only accepts a list of 2-tuples")
else:
# kwarg path. this is the most common path for non-multi-params
# so this is fairly quick.
arg = kwargs
if args:
raise exc.ArgumentError(
"Only a single dictionary/tuple or list of "
"dictionaries/tuples is accepted positionally.")
# for top level values(), convert literals to anonymous bound
# parameters at statement construction time, so that these values can
# participate in the cache key process like any other ClauseElement.
# crud.py now intercepts bound parameters with unique=True from here
# and ensures they get the "crud"-style name when rendered.
if self._preserve_parameter_order:
arg = [(
coercions.expect(roles.DMLColumnRole, k),
coercions.expect(
roles.ExpressionElementRole,
v,
type_=NullType(),
is_crud=True,
),
) for k, v in arg]
self._ordered_values = arg
else:
arg = {
coercions.expect(roles.DMLColumnRole, k): coercions.expect(
roles.ExpressionElementRole,
v,
type_=NullType(),
is_crud=True,
)
for k, v in arg.items()
}
if self._values:
self._values = self._values.union(arg)
else:
self._values = util.immutabledict(arg)