Column and Data Types

SQLAlchemy provides abstractions for most common database data types,and a mechanism for specifying your own custom data types.

The methods and attributes of type objects are rarely used directly.Type objects are supplied to Table definitionsand can be supplied as type hints to functions for occasions wherethe database driver returns an incorrect type.

  1. >>> users = Table('users', metadata,
  2. ...               Column('id', Integer, primary_key=True)
  3. ...               Column('login', String(32))
  4. ...              )

SQLAlchemy will use the Integer and String(32) typeinformation when issuing a CREATE TABLE statement and will use itagain when reading back rows SELECTed from the database.Functions that accept a type (such as Column()) willtypically accept a type class or instance; Integer is equivalentto Integer() with no construction arguments in this case.

Generic Types

Generic types specify a column that can read, write and store aparticular type of Python data. SQLAlchemy will choose the bestdatabase column type available on the target database when issuing aCREATE TABLE statement. For complete control over which columntype is emitted in CREATE TABLE, such as VARCHAR seeSQL Standard and Multiple Vendor Types and the other sections of this chapter.

A type for bigger int integers.

Typically generates a BIGINT in DDL, and otherwise acts likea normal Integer on the Python side.

A bool datatype.

Boolean typically uses BOOLEAN or SMALLINT on the DDL side,and on the Python side deals in True or False.

The Boolean datatype currently has two levels of assertionthat the values persisted are simple true/false values. For allbackends, only the Python values None, True, False, 1or 0 are accepted as parameter values. For those backends thatdon’t support a “native boolean” datatype, a CHECK constraint is alsocreated on the target column. Production of the CHECK constraintcan be disabled by passing the Boolean.create_constraintflag set to False.

Changed in version 1.2: the Boolean datatype now asserts thatincoming Python values are already in pure boolean form.

  • init(create_constraint=True, name=None, createevents=True)[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.types.Boolean.init__)

  • Construct a Boolean.

    • Parameters

      • create_constraint – defaults to True. If the booleanis generated as an int/smallint, also create a CHECK constrainton the table that ensures 1 or 0 as a value.

      • name – if a CHECK constraint is generated, specifythe name of the constraint.

  • bindprocessor(_dialect)

  • Return a conversion function for processing bind values.

Returns a callable which will receive a bind parameter valueas the sole positional argument and will return a value tosend to the DB-API.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. -

dialect – Dialect instance in use.

  • literalprocessor(_dialect)
  • Return a conversion function for processing literal values that areto be rendered directly without using binds.

This function is used when the compiler makes use of the“literal_binds” flag, typically used in DDL generation as wellas in certain scenarios where backends don’t accept bound parameters.

New in version 0.9.0.

  • property python_type
  • Return the Python type object expected to be returnedby instances of this type, if known.

Basically, for those types which enforce a return type,or are known across the board to do such for all commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

Note that any type also accommodates NULL in SQL whichmeans you can also get back None from any typein practice.

  • resultprocessor(_dialect, coltype)
  • Return a conversion function for processing result row values.

Returns a callable which will receive a result row columnvalue as the sole positional argument and will return a valueto return to the user.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. - 
  3.   -

dialect – Dialect instance in use.

  1.   -

coltype – DBAPI coltype argument received in cursor.description.

A type for datetime.date() objects.

  • getdbapi_type(_dbapi)
  • Return the corresponding type object from the underlying DB-API, ifany.

This can be useful for calling setinputsizes(), for example.

  • property python_type
  • Return the Python type object expected to be returnedby instances of this type, if known.

Basically, for those types which enforce a return type,or are known across the board to do such for all commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

Note that any type also accommodates NULL in SQL whichmeans you can also get back None from any typein practice.

A type for datetime.datetime() objects.

Date and time types return objects from the Python datetimemodule. Most DBAPIs have built in support for the datetimemodule, with the noted exception of SQLite. In the case ofSQLite, date and time types are stored as strings which are thenconverted back to datetime objects when rows are returned.

For the time representation within the datetime type, somebackends include additional options, such as timezone support andfractional seconds support. For fractional seconds, use thedialect-specific datatype, such as mysql.TIME. Fortimezone support, use at least the TIMESTAMP datatype,if not the dialect-specific datatype object.

  • init(timezone=False)

  • Construct a new DateTime.

    • Parameters
    • timezone – boolean. Indicates that the datetime type shouldenable timezone support, if available on thebase date/time-holding type only. It is recommendedto make use of the TIMESTAMP datatype directly whenusing this flag, as some databases include separate genericdate/time-holding types distinct from the timezone-capableTIMESTAMP datatype, such as Oracle.

  • getdbapi_type(_dbapi)

  • Return the corresponding type object from the underlying DB-API, ifany.

This can be useful for calling setinputsizes(), for example.

  • property python_type
  • Return the Python type object expected to be returnedby instances of this type, if known.

Basically, for those types which enforce a return type,or are known across the board to do such for all commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

Note that any type also accommodates NULL in SQL whichmeans you can also get back None from any typein practice.

Generic Enum Type.

The Enum type provides a set of possible string valueswhich the column is constrained towards.

The Enum type will make use of the backend’s native “ENUM”type if one is available; otherwise, it uses a VARCHAR datatype andproduces a CHECK constraint. Use of the backend-native enum typecan be disabled using the Enum.native_enum flag, andthe production of the CHECK constraint is configurable using theEnum.create_constraint flag.

The Enum type also provides in-Python validation of stringvalues during both read and write operations. When reading a valuefrom the database in a result set, the string value is always checkedagainst the list of possible values and a LookupError is raisedif no match is found. When passing a value to the database as aplain string within a SQL statement, if theEnum.validate_strings parameter isset to True, a LookupError is raised for any string value that’snot located in the given list of possible values; note that thisimpacts usage of LIKE expressions with enumerated values (an unusualuse case).

Changed in version 1.1: the Enum type now provides in-Pythonvalidation of input values as well as on data being returned bythe database.

The source of enumerated values may be a list of string values, oralternatively a PEP-435-compliant enumerated class. For the purposesof the Enum datatype, this class need only provide amembers method.

When using an enumerated class, the enumerated objects are usedboth for input and output, rather than strings as is the case witha plain-string enumerated type:

  1. import enum
  2. class MyEnum(enum.Enum):
  3.     one = 1
  4.     two = 2
  5.     three = 3
  6.  
  7. t = Table(
  8.     'data', MetaData(),
  9.     Column('value', Enum(MyEnum))
  10. )
  11.  
  12. connection.execute(t.insert(), {"value": MyEnum.two})
  13. assert connection.scalar(t.select()) is MyEnum.two

Above, the string names of each element, e.g. “one”, “two”, “three”,are persisted to the database; the values of the Python Enum, hereindicated as integers, are not used; the value of each enum cantherefore be any kind of Python object whether or not it is persistable.

In order to persist the values and not the names, theEnum.values_callable parameter may be used. The value ofthis parameter is a user-supplied callable, which is intended to be usedwith a PEP-435-compliant enumerated class and returns a list of stringvalues to be persisted. For a simple enumeration that uses string values,a callable such as lambda x: [e.value for e in x] is sufficient.

New in version 1.1: - support for PEP-435-style enumeratedclasses.

See also

postgresql.ENUM - PostgreSQL-specific type,which has additional functionality.

mysql.ENUM - MySQL-specific type

  • init(*enums, **kw)
  • Construct an enum.

Keyword arguments which don’t apply to a specific backend are ignoredby that backend.

  1. - Parameters
  2. - 
  3.   -

*enums –

either exactly one PEP-435 compliant enumerated typeor one or more string or unicode enumeration labels. If unicodelabels are present, the convert_unicode flag is auto-enabled.

New in version 1.1: a PEP-435 style enumerated class may bepassed.

  1.   -

convert_unicode –

Enable unicode-aware bind parameter andresult-set processing for this Enum’s data. This is setautomatically based on the presence of unicode label strings.

Deprecated since version 1.3: The Enum.convert_unicode parameter is deprecated and will be removed in a future release. All modern DBAPIs now support Python Unicode directly and this parameter is unnecessary.

  1.   -

create_constraint –

defaults to True. When creating a non-nativeenumerated type, also build a CHECK constraint on the databaseagainst the valid values.

New in version 1.1: - added Enum.create_constraintwhich provides the option to disable the production of theCHECK constraint for a non-native enumerated type.

  1.   -

metadata – Associate this type directly with a MetaDataobject. For types that exist on the target database as anindependent schema construct (PostgreSQL), this type will becreated and dropped within create_all() and drop_all()operations. If the type is not associated with any MetaDataobject, it will associate itself with each Table in which it isused, and will be created when any of those individual tables arecreated, after a check is performed for its existence. The type isonly dropped when drop_all() is called for that Tableobject’s metadata, however.

  1.   -

name – The name of this type. This is required for PostgreSQLand any future supported database which requires an explicitlynamed type, or an explicitly named constraint in order to generatethe type and/or a table that uses it. If a PEP-435 enumeratedclass was used, its name (converted to lower case) is used bydefault.

  1.   -

native_enum – Use the database’s native ENUM type whenavailable. Defaults to True. When False, uses VARCHAR + checkconstraint for all backends.

  1.   -

schema –

Schema name of this type. For types that exist on thetarget database as an independent schema construct (PostgreSQL),this parameter specifies the named schema in which the type ispresent.

Note

The schema of the Enum type does notby default make use of the schema established on theowning Table. If this behavior is desired,set the inherit_schema flag to True.

  1.   -

quote – Set explicit quoting preferences for the type’s name.

  1.   -

inherit_schema – When True, the “schema” from the owningTable will be copied to the “schema” attribute of thisEnum, replacing whatever value was passed for theschema attribute. This also takes effect when using theTable.tometadata() operation.

  1.   -

validate_strings –

when True, string values that are beingpassed to the database in a SQL statement will be checkedfor validity against the list of enumerated values. Unrecognizedvalues will result in a LookupError being raised.

New in version 1.1.0b2.

  1.   -

values_callable –

A callable which will be passed the PEP-435compliant enumerated type, which should then return a list of stringvalues to be persisted. This allows for alternate usages such asusing the string value of an enum to be persisted to the databaseinstead of its name.

New in version 1.2.3.

  1.   -

sort_key_function –

a Python callable which may be used as the“key” argument in the Python sorted() built-in. The SQLAlchemyORM requires that primary key columns which are mapped mustbe sortable in some way. When using an unsortable enumerationobject such as a Python 3 Enum object, this parameter may beused to set a default sort key function for the objects. Bydefault, the database value of the enumeration is used as thesorting function.

New in version 1.3.8.

  • create(bind=None, checkfirst=False)

inherited from thecreate()method ofSchemaType

Issue CREATE ddl for this type, if applicable.

  • drop(bind=None, checkfirst=False)

inherited from thedrop()method ofSchemaType

Issue DROP ddl for this type, if applicable.

  • class sqlalchemy.types.Float(precision=None, asdecimal=False, decimal_return_scale=None)
  • Bases: sqlalchemy.types.Numeric

Type representing floating point types, such as FLOAT or REAL.

This type returns Python float objects by default, unless theFloat.asdecimal flag is set to True, in which case theyare coerced to decimal.Decimal objects.

Note

The Float type is designed to receive data from a databasetype that is explicitly known to be a floating point type(e.g. FLOAT, REAL, others)and not a decimal type (e.g. DECIMAL, NUMERIC, others).If the database column on the server is in fact a Numerictype, such as DECIMAL or NUMERIC, use the Numerictype or a subclass, otherwise numeric coercion betweenfloat/Decimal may or may not function as expected.

  • init(precision=None, asdecimal=False, decimal_return_scale=None)

  • Construct a Float.

    • Parameters

      • precision – the numeric precision for use in DDL CREATETABLE.

      • asdecimal – the same flag as that of Numeric, butdefaults to False. Note that setting this flag to Trueresults in floating point conversion.

      • decimal_return_scale –

Default scale to use when convertingfrom floats to Python decimals. Floating point values will typicallybe much longer due to decimal inaccuracy, and most floating pointdatabase types don’t have a notion of “scale”, so by default thefloat type looks for the first ten decimal places when converting.Specifying this value will override that length. Note that theMySQL float types, which do include “scale”, will use “scale”as the default for decimal_return_scale, if not otherwise specified.

New in version 0.9.0.

  • resultprocessor(_dialect, coltype)
  • Return a conversion function for processing result row values.

Returns a callable which will receive a result row columnvalue as the sole positional argument and will return a valueto return to the user.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. - 
  3.   -

dialect – Dialect instance in use.

  1.   -

coltype – DBAPI coltype argument received in cursor.description.

A type for int integers.

  • getdbapi_type(_dbapi)
  • Return the corresponding type object from the underlying DB-API, ifany.

This can be useful for calling setinputsizes(), for example.

  • literalprocessor(_dialect)
  • Return a conversion function for processing literal values that areto be rendered directly without using binds.

This function is used when the compiler makes use of the“literal_binds” flag, typically used in DDL generation as wellas in certain scenarios where backends don’t accept bound parameters.

New in version 0.9.0.

  • property python_type
  • Return the Python type object expected to be returnedby instances of this type, if known.

Basically, for those types which enforce a return type,or are known across the board to do such for all commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

Note that any type also accommodates NULL in SQL whichmeans you can also get back None from any typein practice.

  • class sqlalchemy.types.Interval(native=True, second_precision=None, day_precision=None)
  • Bases: sqlalchemy.types.Emulated, sqlalchemy.types._AbstractInterval, sqlalchemy.types.TypeDecorator

A type for datetime.timedelta() objects.

The Interval type deals with datetime.timedelta objects. InPostgreSQL, the native INTERVAL type is used; for others, thevalue is stored as a date which is relative to the “epoch”(Jan. 1, 1970).

Note that the Interval type does not currently provide date arithmeticoperations on platforms which do not support interval types natively. Suchoperations usually require transformation of both sides of the expression(such as, conversion of both sides into integer epoch values first) whichcurrently is a manual procedure (such as viafunc).

  • init(native=True, second_precision=None, day_precision=None)

  • Construct an Interval object.

    • Parameters

      • native – when True, use the actualINTERVAL type provided by the database, ifsupported (currently PostgreSQL, Oracle).Otherwise, represent the interval data asan epoch value regardless.

      • second_precision – For native interval typeswhich support a “fractional seconds precision” parameter,i.e. Oracle and PostgreSQL

      • day_precision – for native interval types whichsupport a “day precision” parameter, i.e. Oracle.

  • adaptto_emulated(_impltype, **kw)

  • Given an impl class, adapt this type to the impl assuming “emulated”.

The impl should also be an “emulated” version of this type,most likely the same class as this type itself.

e.g.: sqltypes.Enum adapts to the Enum class.

  • bindprocessor(_dialect)
  • Return a conversion function for processing bind values.

Returns a callable which will receive a bind parameter valueas the sole positional argument and will return a value tosend to the DB-API.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. -

dialect – Dialect instance in use.

  • impl

  • alias of DateTime

  • property python_type

  • Return the Python type object expected to be returnedby instances of this type, if known.

Basically, for those types which enforce a return type,or are known across the board to do such for all commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

Note that any type also accommodates NULL in SQL whichmeans you can also get back None from any typein practice.

  • resultprocessor(_dialect, coltype)
  • Return a conversion function for processing result row values.

Returns a callable which will receive a result row columnvalue as the sole positional argument and will return a valueto return to the user.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. - 
  3.   -

dialect – Dialect instance in use.

  1.   -

coltype – DBAPI coltype argument received in cursor.description.

  • class sqlalchemy.types.LargeBinary(length=None)
  • Bases: sqlalchemy.types._Binary

A type for large binary byte data.

The LargeBinary type corresponds to a large and/or unlengthedbinary type for the target platform, such as BLOB on MySQL and BYTEA forPostgreSQL. It also handles the necessary conversions for the DBAPI.

  • init(length=None)

  • Construct a LargeBinary type.

    • Parameters
    • length – optional, a length for the column for use inDDL statements, for those binary types that accept a length,such as the MySQL BLOB type.
  • class sqlalchemy.types.MatchType(create_constraint=True, name=None, _create_events=True)
  • Bases: sqlalchemy.types.Boolean

Refers to the return type of the MATCH operator.

As the ColumnOperators.match() is probably the most open-endedoperator in generic SQLAlchemy Core, we can’t assume the return typeat SQL evaluation time, as MySQL returns a floating point, not a boolean,and other backends might do something different. So this typeacts as a placeholder, currently subclassing Boolean.The type allows dialects to inject result-processing functionalityif needed, and on MySQL will return floating-point values.

New in version 1.0.0.

  • class sqlalchemy.types.Numeric(precision=None, scale=None, decimal_return_scale=None, asdecimal=True)
  • Bases: sqlalchemy.types._LookupExpressionAdapter, sqlalchemy.types.TypeEngine

A type for fixed precision numbers, such as NUMERIC or DECIMAL.

This type returns Python decimal.Decimal objects by default, unlessthe Numeric.asdecimal flag is set to False, in which casethey are coerced to Python float objects.

Note

The Numeric type is designed to receive data from a databasetype that is explicitly known to be a decimal type(e.g. DECIMAL, NUMERIC, others) and not a floating pointtype (e.g. FLOAT, REAL, others).If the database column on the server is in fact a floating-point typetype, such as FLOAT or REAL, use the Floattype or a subclass, otherwise numeric coercion betweenfloat/Decimal may or may not function as expected.

Note

The Python decimal.Decimal class is generally slowperforming; cPython 3.3 has now switched to use the cdecimal library natively. Forolder Python versions, the cdecimal library can be patchedinto any application where it will replace the decimallibrary fully, however this needs to be applied globally andbefore any other modules have been imported, as follows:

  1. import sys
  2. import cdecimal
  3. sys.modules["decimal"] = cdecimal

Note that the cdecimal and decimal libraries are notcompatible with each other, so patching cdecimal at theglobal level is the only way it can be used effectively withvarious DBAPIs that hardcode to import the decimal library.

  • init(precision=None, scale=None, decimal_return_scale=None, asdecimal=True)

  • Construct a Numeric.

    • Parameters

      • precision – the numeric precision for use in DDL CREATETABLE.

      • scale – the numeric scale for use in DDL CREATE TABLE.

      • asdecimal – default True. Return whether or notvalues should be sent as Python Decimal objects, oras floats. Different DBAPIs send one or the other based ondatatypes - the Numeric type will ensure that return valuesare one or the other across DBAPIs consistently.

      • decimal_return_scale –

Default scale to use when convertingfrom floats to Python decimals. Floating point values will typicallybe much longer due to decimal inaccuracy, and most floating pointdatabase types don’t have a notion of “scale”, so by default thefloat type looks for the first ten decimal places when converting.Specifying this value will override that length. Types whichdo include an explicit “.scale” value, such as the baseNumeric as well as the MySQL float types, will use thevalue of “.scale” as the default for decimal_return_scale, if nototherwise specified.

New in version 0.9.0.

When using the Numeric type, care should be taken to ensurethat the asdecimal setting is appropriate for the DBAPI in use -when Numeric applies a conversion from Decimal->float or float->Decimal, this conversion incurs an additional performance overheadfor all result columns received.

DBAPIs that return Decimal natively (e.g. psycopg2) will havebetter accuracy and higher performance with a setting of True,as the native translation to Decimal reduces the amount of floating-point issues at play, and the Numeric type itself doesn’t needto apply any further conversions. However, another DBAPI whichreturns floats natively will incur an additional conversionoverhead, and is still subject to floating point data loss - inwhich case asdecimal=False will at least remove the extraconversion overhead.

  • bindprocessor(_dialect)
  • Return a conversion function for processing bind values.

Returns a callable which will receive a bind parameter valueas the sole positional argument and will return a value tosend to the DB-API.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. -

dialect – Dialect instance in use.

  • getdbapi_type(_dbapi)
  • Return the corresponding type object from the underlying DB-API, ifany.

This can be useful for calling setinputsizes(), for example.

  • literalprocessor(_dialect)
  • Return a conversion function for processing literal values that areto be rendered directly without using binds.

This function is used when the compiler makes use of the“literal_binds” flag, typically used in DDL generation as wellas in certain scenarios where backends don’t accept bound parameters.

New in version 0.9.0.

  • property python_type
  • Return the Python type object expected to be returnedby instances of this type, if known.

Basically, for those types which enforce a return type,or are known across the board to do such for all commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

Note that any type also accommodates NULL in SQL whichmeans you can also get back None from any typein practice.

  • resultprocessor(_dialect, coltype)
  • Return a conversion function for processing result row values.

Returns a callable which will receive a result row columnvalue as the sole positional argument and will return a valueto return to the user.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. - 
  3.   -

dialect – Dialect instance in use.

  1.   -

coltype – DBAPI coltype argument received in cursor.description.

Holds Python objects, which are serialized using pickle.

PickleType builds upon the Binary type to apply Python’spickle.dumps() to incoming objects, and pickle.loads() onthe way out, allowing any pickleable Python object to be stored asa serialized binary field.

To allow ORM change events to propagate for elements associatedwith PickleType, see Mutation Tracking.

  • init(protocol=4, pickler=None, comparator=None)

  • Construct a PickleType.

    • Parameters

      • protocol – defaults to pickle.HIGHEST_PROTOCOL.

      • pickler – defaults to cPickle.pickle or pickle.pickle ifcPickle is not available. May be any object withpickle-compatible dumps` and ``loads methods.

      • comparator – a 2-arg callable predicate usedto compare values of this type. If left as None,the Python “equals” operator is used to compare values.

  • bindprocessor(_dialect)

  • Provide a bound value processing function for thegiven Dialect.

This is the method that fulfills the TypeEnginecontract for bound value conversion. TypeDecoratorwill wrap a user-defined implementation ofprocess_bind_param() here.

User-defined code can override this method directly,though its likely best to use process_bind_param() so thatthe processing provided by self.impl is maintained.

  1. - Parameters
  2. -

dialect – Dialect instance in use.

This method is the reverse counterpart to theresult_processor() method of this class.

  • comparevalues(_x, y)
  • Given two values, compare them for equality.

By default this calls upon TypeEngine.compare_values()of the underlying “impl”, which in turn usuallyuses the Python equals operator ==.

This function is used by the ORM to comparean original-loaded value with an intercepted“changed” value, to determine if a net changehas occurred.

  • impl

  • alias of LargeBinary

  • resultprocessor(_dialect, coltype)

  • Provide a result value processing function for the givenDialect.

This is the method that fulfills the TypeEnginecontract for result value conversion. TypeDecoratorwill wrap a user-defined implementation ofprocess_result_value() here.

User-defined code can override this method directly,though its likely best to use process_result_value() so thatthe processing provided by self.impl is maintained.

  1. - Parameters
  2. - 
  3.   -

dialect – Dialect instance in use.

  1.   -

coltype – A SQLAlchemy data type

This method is the reverse counterpart to thebind_processor() method of this class.

  • class sqlalchemy.types.SchemaType(name=None, schema=None, metadata=None, inherit_schema=False, quote=None, _create_events=True)
  • Bases: sqlalchemy.sql.expression.SchemaEventTarget

Mark a type as possibly requiring schema-level DDL for usage.

Supports types that must be explicitly created/dropped (i.e. PG ENUM type)as well as types that are complimented by table or schema levelconstraints, triggers, and other rules.

SchemaType classes can also be targets for theDDLEvents.before_parent_attach() andDDLEvents.after_parent_attach() events, where the events fire offsurrounding the association of the type object with a parentColumn.

See also

Enum

Boolean

  • adapt(impltype, **kw)
  • property bind
  • copy(**kw)
  • create(bind=None, checkfirst=False)

  • Issue CREATE ddl for this type, if applicable.

  • drop(bind=None, checkfirst=False)

  • Issue DROP ddl for this type, if applicable.

A type for smaller int integers.

Typically generates a SMALLINT in DDL, and otherwise acts likea normal Integer on the Python side.

The base for all string and character types.

In SQL, corresponds to VARCHAR. Can also take Python unicode objectsand encode to the database’s encoding in bind params (and the reverse forresult sets.)

The length field is usually required when the String type isused within a CREATE TABLE statement, as VARCHAR requires a lengthon most databases.

  • init(length=None, collation=None, convert_unicode=False, unicode_error=None, warnon_bytestring=False, expectunicode=False)

  • Create a string-holding type.

    • Parameters

      • length – optional, a length for the column for use inDDL and CAST expressions. May be safely omitted if no CREATETABLE will be issued. Certain databases may require alength for use in DDL, and will raise an exception whenthe CREATE TABLE DDL is issued if a VARCHARwith no length is included. Whether the value isinterpreted as bytes or characters is database specific.

      • collation –

Optional, a column-level collation foruse in DDL and CAST expressions. Renders using theCOLLATE keyword supported by SQLite, MySQL, and PostgreSQL.E.g.:

  1. >>> from sqlalchemy import cast, select, String
  2. >>> print select([cast('some string', String(collation='utf8'))])
  3. SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1
  1.   -

convert_unicode –

When set to True, theString type will assume thatinput is to be passed as Python Unicode objects under Python 2,and results returned as Python Unicode objects.In the rare circumstance that the DBAPI does not supportPython unicode under Python 2, SQLAlchemy will use its ownencoder/decoder functionality on strings, referring to thevalue of the create_engine.encoding parameterparameter passed to create_engine() as the encoding.

Deprecated since version 1.3: The String.convert_unicode parameter is deprecated and will be removed in a future release. All modern DBAPIs now support Python Unicode directly and this parameter is unnecessary.

For the extremely rare case that Python Unicodeis to be encoded/decoded by SQLAlchemy on a backendthat does natively support Python Unicode,the string value "force" can be passed here which willcause SQLAlchemy’s encode/decode services to beused unconditionally.

Note

SQLAlchemy’s unicode-conversion flags and features only applyto Python 2; in Python 3, all string objects are Unicode objects.For this reason, as well as the fact that virtually all modernDBAPIs now support Unicode natively even under Python 2,the String.convert_unicode flag is inherently alegacy feature.

Note

In the vast majority of cases, the Unicode orUnicodeText datatypes should be used for aColumn that expects to store non-ascii data. Thesedatatypes will ensure that the correct types are used on thedatabase side as well as set up the correct Unicode behaviorsunder Python 2.

See also

create_engine.convert_unicode -Engine-wide parameter

  1.   -

unicode_error –

Optional, a method to use to handle Unicodeconversion errors. Behaves like the errors keyword argument tothe standard library’s string.decode() functions, requiresthat String.convert_unicode is set to"force"

Deprecated since version 1.3: The String.unicode_errors parameter is deprecated and will be removed in a future release. This parameter is unnecessary for modern Python DBAPIs and degrades performance significantly.

  • bindprocessor(_dialect)
  • Return a conversion function for processing bind values.

Returns a callable which will receive a bind parameter valueas the sole positional argument and will return a value tosend to the DB-API.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. -

dialect – Dialect instance in use.

  • getdbapi_type(_dbapi)
  • Return the corresponding type object from the underlying DB-API, ifany.

This can be useful for calling setinputsizes(), for example.

  • literalprocessor(_dialect)
  • Return a conversion function for processing literal values that areto be rendered directly without using binds.

This function is used when the compiler makes use of the“literal_binds” flag, typically used in DDL generation as wellas in certain scenarios where backends don’t accept bound parameters.

New in version 0.9.0.

  • property python_type
  • Return the Python type object expected to be returnedby instances of this type, if known.

Basically, for those types which enforce a return type,or are known across the board to do such for all commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

Note that any type also accommodates NULL in SQL whichmeans you can also get back None from any typein practice.

  • resultprocessor(_dialect, coltype)
  • Return a conversion function for processing result row values.

Returns a callable which will receive a result row columnvalue as the sole positional argument and will return a valueto return to the user.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. - 
  3.   -

dialect – Dialect instance in use.

  1.   -

coltype – DBAPI coltype argument received in cursor.description.

  • class sqlalchemy.types.Text(length=None, collation=None, convert_unicode=False, unicode_error=None, warnon_bytestring=False, expectunicode=False)
  • Bases: sqlalchemy.types.String

A variably sized string type.

In SQL, usually corresponds to CLOB or TEXT. Can also take Pythonunicode objects and encode to the database’s encoding in bindparams (and the reverse for result sets.) In general, TEXT objectsdo not have a length; while some databases will accept a lengthargument here, it will be rejected by others.

A type for datetime.time() objects.

  • getdbapi_type(_dbapi)
  • Return the corresponding type object from the underlying DB-API, ifany.

This can be useful for calling setinputsizes(), for example.

  • property python_type
  • Return the Python type object expected to be returnedby instances of this type, if known.

Basically, for those types which enforce a return type,or are known across the board to do such for all commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

Note that any type also accommodates NULL in SQL whichmeans you can also get back None from any typein practice.

A variable length Unicode string type.

The Unicode type is a String subclassthat assumes input and output as Python unicode data,and in that regard is equivalent to the usage of theconvert_unicode flag with the String type.However, unlike plain String, it also implies anunderlying column type that is explicitly supporting of non-ASCIIdata, such as NVARCHAR on Oracle and SQL Server.This can impact the output of CREATE TABLE statementsand CAST functions at the dialect level, and canalso affect the handling of bound parameters in somespecific DBAPI scenarios.

The encoding used by the Unicode type is usuallydetermined by the DBAPI itself; most modern DBAPIsfeature support for Python unicode objects as boundvalues and result set values, and the encoding shouldbe configured as detailed in the notes for the targetDBAPI in the Dialects section.

For those DBAPIs which do not support, or are not configuredto accommodate Python unicode objectsdirectly, SQLAlchemy does the encoding and decodingoutside of the DBAPI. The encoding in this scenariois determined by the encoding flag passed tocreate_engine().

When using the Unicode type, it is only appropriateto pass Python unicode objects, and not plain str.If a plain str is passed under Python 2, a warningis emitted. If you notice your application emitting these warnings butyou’re not sure of the source of them, the Pythonwarnings filter, documented athttp://docs.python.org/library/warnings.html,can be used to turn these warnings into exceptionswhich will illustrate a stack trace:

  1. import warnings
  2. warnings.simplefilter('error')

For an application that wishes to pass plain bytestringsand Python unicode objects to the Unicode typeequally, the bytestrings must first be decoded intounicode. The recipe at Coercing Encoded Strings to Unicode illustrateshow this is done.

See also

UnicodeText - unlengthed textual counterpartto Unicode.

  • init(length=None, **kwargs)
  • Create a Unicode object.

Parameters are the same as that of String,with the exception that convert_unicodedefaults to True.

An unbounded-length Unicode string type.

See Unicode for details on the unicodebehavior of this object.

Like Unicode, usage the UnicodeText type implies aunicode-capable type being used on the backend, such asNCLOB, NTEXT.

  • init(length=None, **kwargs)
  • Create a Unicode-converting Text type.

Parameters are the same as that of Text,with the exception that convert_unicodedefaults to True.

SQL Standard and Multiple Vendor Types

This category of types refers to types that are either part of theSQL standard, or are potentially found within a subset of database backends.Unlike the “generic” types, the SQL standard/multi-vendor types have noguarantee of working on all backends, and will only work on those backendsthat explicitly support them by name. That is, the type will always emitits exact name in DDL with CREATE TABLE is issued.

Represent a SQL Array type.

Note

This type serves as the basis for all ARRAY operations.However, currently only the PostgreSQL backend has supportfor SQL arrays in SQLAlchemy. It is recommended to use thepostgresql.ARRAY type directly when using ARRAY typeswith PostgreSQL, as it provides additional operators specificto that backend.

types.ARRAY is part of the Core in support of various SQLstandard functions such as array_agg which explicitly involvearrays; however, with the exception of the PostgreSQL backend and possiblysome third-party dialects, no other SQLAlchemy built-in dialect has supportfor this type.

An types.ARRAY type is constructed given the “type”of element:

  1. mytable = Table("mytable", metadata,
  2.         Column("data", ARRAY(Integer))
  3.     )

The above type represents an N-dimensional array,meaning a supporting backend such as PostgreSQL will interpret valueswith any number of dimensions automatically. To produce an INSERTconstruct that passes in a 1-dimensional array of integers:

  1. connection.execute(
  2.         mytable.insert(),
  3.         data=[1,2,3]
  4. )

The types.ARRAY type can be constructed given a fixed numberof dimensions:

  1. mytable = Table("mytable", metadata,
  2.         Column("data", ARRAY(Integer, dimensions=2))
  3.     )

Sending a number of dimensions is optional, but recommended if thedatatype is to represent arrays of more than one dimension. This numberis used:

  • When emitting the type declaration itself to the database, e.g.INTEGER[][]

  • When translating Python values to database values, and vice versa, e.g.an ARRAY of Unicode objects uses this number to efficientlyaccess the string values inside of array structures without resortingto per-row type inspection

  • When used with the Python getitem accessor, the number of dimensionsserves to define the kind of type that the [] operator shouldreturn, e.g. for an ARRAY of INTEGER with two dimensions:

  1. >>> expr = table.c.column[5]  # returns ARRAY(Integer, dimensions=1)
  2. >>> expr = expr[6]  # returns Integer

For 1-dimensional arrays, an types.ARRAY instance with nodimension parameter will generally assume single-dimensional behaviors.

SQL expressions of type types.ARRAY have support for “index” and“slice” behavior. The Python [] operator works normally here, giveninteger indexes or slices. Arrays default to 1-based indexing.The operator produces binary expressionconstructs which will produce the appropriate SQL, both forSELECT statements:

  1. select([mytable.c.data[5], mytable.c.data[2:7]])

as well as UPDATE statements when the Update.values() methodis used:

  1. mytable.update().values({
  2.     mytable.c.data[5]: 7,
  3.     mytable.c.data[2:7]: [1, 2, 3]
  4. })

The types.ARRAY type also provides for the operatorstypes.ARRAY.Comparator.any() andtypes.ARRAY.Comparator.all(). The PostgreSQL-specific version oftypes.ARRAY also provides additional operators.

New in version 1.1.0.

See also

postgresql.ARRAY

  • class Comparator(expr)
  • Bases: sqlalchemy.types.Comparator, sqlalchemy.types.Comparator

Define comparison operations for types.ARRAY.

More operators are available on the dialect-specific formof this type. See postgresql.ARRAY.Comparator.

  1. - <code>all</code>(_elements_, _other_, _operator=None_)[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.types.ARRAY.Comparator.all)
  2. -

Return other operator ALL (array) clause.

Argument places are switched, because ALL requires arrayexpression to be on the right hand-side.

E.g.:

  1. from sqlalchemy.sql import operators
  2.  
  3. conn.execute(
  4.     select([table.c.data]).where(
  5.             table.c.data.all(7, operator=operators.lt)
  6.         )
  7. )
  1.   - Parameters
  2.   - 
  3.     -

other – expression to be compared

  1.     -

operator – an operator object from thesqlalchemy.sql.operatorspackage, defaults to operators.eq().

See also

sql.expression.all_()

types.ARRAY.Comparator.any()

  1. - <code>any</code>(_elements_, _other_, _operator=None_)[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.types.ARRAY.Comparator.any)
  2. -

Return other operator ANY (array) clause.

Argument places are switched, because ANY requires arrayexpression to be on the right hand-side.

E.g.:

  1. from sqlalchemy.sql import operators
  2.  
  3. conn.execute(
  4.     select([table.c.data]).where(
  5.             table.c.data.any(7, operator=operators.lt)
  6.         )
  7. )
  1.   - Parameters
  2.   - 
  3.     -

other – expression to be compared

  1.     -

operator – an operator object from thesqlalchemy.sql.operatorspackage, defaults to operators.eq().

See also

sql.expression.any_()

types.ARRAY.Comparator.all()

  1. - <code>contains</code>(_*arg_, _**kw_)[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.types.ARRAY.Comparator.contains)
  2. -

Implement the ‘contains’ operator.

Produces a LIKE expression that tests against a match for the middleof a string value:

  1. column LIKE '%' || <other> || '%'

E.g.:

  1. stmt = select([sometable]).\
  2.     where(sometable.c.column.contains("foobar"))

Since the operator uses LIKE, wildcard characters"%" and "_" that are present inside the expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.contains.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.contains.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

  1.   - Parameters
  2.   - 
  3.     -

other – expression to be compared. This is usually a plainstring value, but can also be an arbitrary SQL expression. LIKEwildcard characters % and _ are not escaped by default unlessthe ColumnOperators.contains.autoescape flag isset to True.

  1.     -

autoescape –

boolean; when True, establishes an escape characterwithin the LIKE expression, then applies it to all occurrences of"%", "_" and the escape character itself within thecomparison value, which is assumed to be a literal string and not aSQL expression.

An expression such as:

  1. somecolumn.contains("foo%bar", autoescape=True)

Will render as:

  1. somecolumn LIKE '%' || :param || '%' ESCAPE '/'

With the value of :param as "foo/%bar".

New in version 1.2.

Changed in version 1.2.0: TheColumnOperators.contains.autoescape parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the ColumnOperators.contains.escape parameter.

  1.     -

escape –

a character which when given will render with theESCAPE keyword to establish that character as the escapecharacter. This character can then be placed preceding occurrencesof % and _ to allow them to act as themselves and notwildcard characters.

An expression such as:

  1. somecolumn.contains("foo/%bar", escape="^")

Will render as:

  1. somecolumn LIKE '%' || :param || '%' ESCAPE '^'

The parameter may also be combined withColumnOperators.contains.autoescape:

  1. somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)

Where above, the given literal parameter will be converted to"foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

ColumnOperators.endswith()

ColumnOperators.like()

  • init(item_type, as_tuple=False, dimensions=None, zero_indexes=False)
  • Construct an types.ARRAY.

E.g.:

  1. Column('myarray', ARRAY(Integer))

Arguments are:

  1. - Parameters
  2. - 
  3.   -

item_type – The data type of items of this array. Note thatdimensionality is irrelevant here, so multi-dimensional arrays likeINTEGER[][], are constructed as ARRAY(Integer), not asARRAY(ARRAY(Integer)) or such.

  1.   -

as_tuple=False – Specify whether return resultsshould be converted to tuples from lists. This parameter isnot generally needed as a Python list corresponds wellto a SQL array.

  1.   -

dimensions – if non-None, the ARRAY will assume a fixednumber of dimensions. This impacts how the array is declaredon the database, how it goes about interpreting Python andresult values, as well as how expression behavior in conjunctionwith the “getitem” operator works. See the description attypes.ARRAY for additional detail.

  1.   -

zero_indexes=False – when True, index values will be convertedbetween Python zero-based and SQL one-based indexes, e.g.a value of one will be added to all index values before passingto the database.

  • comparator_factory

  • alias of ARRAY.Comparator

  • comparevalues(_x, y)

  • Compare two values for equality.

  • property hashable

  • bool(x) -> bool

Returns True when the argument x is true, False otherwise.The builtins True and False are the only two instances of the class bool.The class bool is a subclass of the class int, and cannot be subclassed.

  • property python_type
  • Return the Python type object expected to be returnedby instances of this type, if known.

Basically, for those types which enforce a return type,or are known across the board to do such for all commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

Note that any type also accommodates NULL in SQL whichmeans you can also get back None from any typein practice.

  • zeroindexes = False_
  • if True, Python zero-based indexes should be interpreted as one-basedon the SQL expression side.

The SQL BIGINT type.

  • class sqlalchemy.types.BINARY(length=None)
  • Bases: sqlalchemy.types._Binary

The SQL BINARY type.

The SQL BLOB type.

The SQL BOOLEAN type.

  • class sqlalchemy.types.CHAR(length=None, collation=None, convert_unicode=False, unicode_error=None, warnon_bytestring=False, expectunicode=False)
  • Bases: sqlalchemy.types.String

The SQL CHAR type.

  • class sqlalchemy.types.CLOB(length=None, collation=None, convert_unicode=False, unicode_error=None, warnon_bytestring=False, expectunicode=False)
  • Bases: sqlalchemy.types.Text

The CLOB type.

This type is found in Oracle and Informix.

The SQL DATE type.

The SQL DATETIME type.

  • class sqlalchemy.types.DECIMAL(precision=None, scale=None, decimal_return_scale=None, asdecimal=True)
  • Bases: sqlalchemy.types.Numeric

The SQL DECIMAL type.

  • class sqlalchemy.types.FLOAT(precision=None, asdecimal=False, decimal_return_scale=None)
  • Bases: sqlalchemy.types.Float

The SQL FLOAT type.

Represent a SQL JSON type.

Note

types.JSON is provided as a facade for vendor-specificJSON types. Since it supports JSON SQL operations, it onlyworks on backends that have an actual JSON type, currently:

  • PostgreSQL

  • MySQL as of version 5.7 (MariaDB as of the 10.2 series does not)

  • SQLite as of version 3.9

types.JSON is part of the Core in support of the growingpopularity of native JSON datatypes.

The types.JSON type stores arbitrary JSON format data, e.g.:

  1. data_table = Table('data_table', metadata,
  2.     Column('id', Integer, primary_key=True),
  3.     Column('data', JSON)
  4. )
  5.  
  6. with engine.connect() as conn:
  7.     conn.execute(
  8.         data_table.insert(),
  9.         data = {"key1": "value1", "key2": "value2"}
  10.     )

The base types.JSON provides these operations:

  • Keyed index operations:
  1. data_table.c.data['some key']
  • Integer index operations:
  1. data_table.c.data[3]
  • Path index operations:
  1. data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')]

Additional operations are available from the dialect-specific versionsof types.JSON, such as postgresql.JSON andpostgresql.JSONB, each of which offer more operators thanjust the basic type.

Index operations return an expression object whose type defaults toJSON by default, so that further JSON-oriented instructions maybe called upon the result type. Note that there are backend-specificidiosyncrasies here, including that the PostgreSQL database does notgenerally compare a “json” to a “json” structure without type casts. Theseidiosyncrasies can be accommodated in a backend-neutral way by makingexplicit use of the cast() and type_coerce() constructs.Comparison of specific index elements of a JSON object to otherobjects works best if the left hand side is CAST to a string and theright hand side is rendered as a JSON string; a future SQLAlchemyfeature such as a generic “astext” modifier may simplify this at somepoint:

  • Compare an element of a JSON structure to a string:
  1. from sqlalchemy import cast, type_coerce
  2. from sqlalchemy import String, JSON
  3.  
  4. cast(
  5.     data_table.c.data['some_key'], String
  6. ) == '"some_value"'
  7.  
  8. cast(
  9.     data_table.c.data['some_key'], String
  10. ) == type_coerce("some_value", JSON)
  • Compare an element of a JSON structure to an integer:
  1. from sqlalchemy import cast, type_coerce
  2. from sqlalchemy import String, JSON
  3.  
  4. cast(data_table.c.data['some_key'], String) == '55'
  5.  
  6. cast(
  7.     data_table.c.data['some_key'], String
  8. ) == type_coerce(55, JSON)
  • Compare an element of a JSON structure to some other JSON structure- note that Python dictionaries are typically not ordered so care shouldbe taken here to assert that the JSON structures are identical:
  1. from sqlalchemy import cast, type_coerce
  2. from sqlalchemy import String, JSON
  3. import json
  4.  
  5. cast(
  6.     data_table.c.data['some_key'], String
  7. ) == json.dumps({"foo": "bar"})
  8.  
  9. cast(
  10.     data_table.c.data['some_key'], String
  11. ) == type_coerce({"foo": "bar"}, JSON)

The JSON type, when used with the SQLAlchemy ORM, does notdetect in-place mutations to the structure. In order to detect these, thesqlalchemy.ext.mutable extension must be used. This extension willallow “in-place” changes to the datastructure to produce events whichwill be detected by the unit of work. See the example at HSTOREfor a simple example involving a dictionary.

When working with NULL values, the JSON type recommends theuse of two specific constants in order to differentiate between a columnthat evaluates to SQL NULL, e.g. no value, vs. the JSON-encoded stringof "null". To insert or select against a value that is SQL NULL,use the constant null():

  1. from sqlalchemy import null
  2. conn.execute(table.insert(), json_value=null())

To insert or select against a value that is JSON "null", use theconstant JSON.NULL:

  1. conn.execute(table.insert(), json_value=JSON.NULL)

The JSON type supports a flagJSON.none_as_null which when set to True will resultin the Python constant None evaluating to the value of SQLNULL, and when set to False results in the Python constantNone evaluating to the value of JSON "null". The Pythonvalue None may be used in conjunction with eitherJSON.NULL and null() in order to indicate NULLvalues, but care must be taken as to the value of theJSON.none_as_null in these cases.

The JSON serializer and deserializer used by JSON defaults toPython’s json.dumps and json.loads functions; in the case of thepsycopg2 dialect, psycopg2 may be using its own custom loader function.

In order to affect the serializer / deserializer, they are currentlyconfigurable at the create_engine() level via thecreate_engine.json_serializer andcreate_engine.json_deserializer parameters. For example,to turn off ensure_ascii:

  1. engine = create_engine(
  2.     "sqlite://",
  3.     json_serializer=lambda obj: json.dumps(obj, ensure_ascii=False))

Changed in version 1.3.7: SQLite dialect’s json_serializer and json_deserializerparameters renamed from _json_serializer and_json_deserializer.

See also

postgresql.JSON

postgresql.JSONB

mysql.JSON

New in version 1.1.

  • class Comparator(expr)
  • Bases: sqlalchemy.types.Comparator, sqlalchemy.types.Comparator

Define comparison operations for types.JSON.

common function for index / path elements in a JSON expression.

  1. - <code>bind_processor</code>(_dialect_)[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.types.JSON.JSONElementType.bind_processor)
  2. -

Return a conversion function for processing bind values.

Returns a callable which will receive a bind parameter valueas the sole positional argument and will return a value tosend to the DB-API.

If processing is not necessary, the method should return None.

  1.   - Parameters
  2.   -

dialect – Dialect instance in use.

  1. - <code>literal_processor</code>(_dialect_)[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.types.JSON.JSONElementType.literal_processor)
  2. -

Return a conversion function for processing literal values that areto be rendered directly without using binds.

This function is used when the compiler makes use of the“literal_binds” flag, typically used in DDL generation as wellas in certain scenarios where backends don’t accept bound parameters.

New in version 0.9.0.

  • class JSONIndexType
  • Bases: sqlalchemy.types.JSONElementType

Placeholder for the datatype of a JSON index value.

This allows execution-time processing of JSON index valuesfor special syntaxes.

  • class JSONPathType
  • Bases: sqlalchemy.types.JSONElementType

Placeholder type for JSON path operations.

This allows execution-time processing of a path-basedindex value into a specific SQL syntax.

  • NULL = symbol('JSON_NULL')
  • Describe the json value of NULL.

This value is used to force the JSON value of "null" to beused as the value. A value of Python None will be recognizedeither as SQL NULL or JSON "null", based on the settingof the JSON.none_as_null flag; the JSON.NULLconstant can be used to always resolve to JSON "null" regardlessof this setting. This is in contrast to the sql.null() construct,which always resolves to SQL NULL. E.g.:

  1. from sqlalchemy import null
  2. from sqlalchemy.dialects.postgresql import JSON
  3.  
  4. # will *always* insert SQL NULL
  5. obj1 = MyObject(json_value=null())
  6.  
  7. # will *always* insert JSON string "null"
  8. obj2 = MyObject(json_value=JSON.NULL)
  9.  
  10. session.add_all([obj1, obj2])
  11. session.commit()

In order to set JSON NULL as a default value for a column, the mosttransparent method is to use text():

  1. Table(
  2.     'my_table', metadata,
  3.     Column('json_data', JSON, default=text("'null'"))
  4. )

While it is possible to use JSON.NULL in this context, theJSON.NULL value will be returned as the value of the column,which in the context of the ORM or other repurposing of the defaultvalue, may not be desirable. Using a SQL expression means the valuewill be re-fetched from the database within the context of retrievinggenerated defaults.

  • init(none_as_null=False)

  • Construct a types.JSON type.

    • Parameters
    • none_as_null=False –

if True, persist the value None as aSQL NULL value, not the JSON encoding of null. Note thatwhen this flag is False, the null() construct can stillbe used to persist a NULL value:

  1. from sqlalchemy import null
  2. conn.execute(table.insert(), data=null())

Note

JSON.none_as_null does not apply to thevalues passed to Column.default andColumn.server_default; a value of Nonepassed for these parameters means “no default present”.

See also

types.JSON.NULL

  • bindprocessor(_dialect)
  • Return a conversion function for processing bind values.

Returns a callable which will receive a bind parameter valueas the sole positional argument and will return a value tosend to the DB-API.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. -

dialect – Dialect instance in use.

  • comparator_factory

  • alias of JSON.Comparator

  • property python_type

  • Return the Python type object expected to be returnedby instances of this type, if known.

Basically, for those types which enforce a return type,or are known across the board to do such for all commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

Note that any type also accommodates NULL in SQL whichmeans you can also get back None from any typein practice.

  • resultprocessor(_dialect, coltype)
  • Return a conversion function for processing result row values.

Returns a callable which will receive a result row columnvalue as the sole positional argument and will return a valueto return to the user.

If processing is not necessary, the method should return None.

  1. - Parameters
  2. - 
  3.   -

dialect – Dialect instance in use.

  1.   -

coltype – DBAPI coltype argument received in cursor.description.

  • property should_evaluate_none
  • Alias of JSON.none_as_null

The SQL INT or INTEGER type.

The SQL NCHAR type.

The SQL NVARCHAR type.

  • class sqlalchemy.types.NUMERIC(precision=None, scale=None, decimal_return_scale=None, asdecimal=True)
  • Bases: sqlalchemy.types.Numeric

The SQL NUMERIC type.

  • class sqlalchemy.types.REAL(precision=None, asdecimal=False, decimal_return_scale=None)
  • Bases: sqlalchemy.types.Float

The SQL REAL type.

The SQL SMALLINT type.

  • class sqlalchemy.types.TEXT(length=None, collation=None, convert_unicode=False, unicode_error=None, warnon_bytestring=False, expectunicode=False)
  • Bases: sqlalchemy.types.Text

The SQL TEXT type.

The SQL TIME type.

The SQL TIMESTAMP type.

TIMESTAMP datatypes have support for timezonestorage on some backends, such as PostgreSQL and Oracle. Use thetimezone argument in order to enable“TIMESTAMP WITH TIMEZONE” for these backends.

  • init(timezone=False)

  • Construct a new TIMESTAMP.

    • Parameters
    • timezone – boolean. Indicates that the TIMESTAMP type shouldenable timezone support, if available on the target database.On a per-dialect basis is similar to “TIMESTAMP WITH TIMEZONE”.If the target database does not support timezones, this flag isignored.

  • getdbapi_type(_dbapi)

  • Return the corresponding type object from the underlying DB-API, ifany.

This can be useful for calling setinputsizes(), for example.

  • class sqlalchemy.types.VARBINARY(length=None)
  • Bases: sqlalchemy.types._Binary

The SQL VARBINARY type.

  • class sqlalchemy.types.VARCHAR(length=None, collation=None, convert_unicode=False, unicode_error=None, warnon_bytestring=False, expectunicode=False)
  • Bases: sqlalchemy.types.String

The SQL VARCHAR type.

Vendor-Specific Types

Database-specific types are also available for import from eachdatabase’s dialect module. See the Dialectsreference for the database you’re interested in.

For example, MySQL has a BIGINT type and PostgreSQL has anINET type. To use these, import them from the module explicitly:

  1. from sqlalchemy.dialects import mysql
  2.  
  3. table = Table('foo', metadata,
  4.     Column('id', mysql.BIGINT),
  5.     Column('enumerates', mysql.ENUM('a', 'b', 'c'))
  6. )

Or some PostgreSQL types:

  1. from sqlalchemy.dialects import postgresql
  2.  
  3. table = Table('foo', metadata,
  4.     Column('ipaddress', postgresql.INET),
  5.     Column('elements', postgresql.ARRAY(String))
  6. )

Each dialect provides the full set of typenames supported bythat backend within its all collection, so that a simpleimport * or similar will import all supported types asimplemented for that backend:

  1. from sqlalchemy.dialects.postgresql import *
  2.  
  3. t = Table('mytable', metadata,
  4.            Column('id', INTEGER, primary_key=True),
  5.            Column('name', VARCHAR(300)),
  6.            Column('inetaddr', INET)
  7. )

Where above, the INTEGER and VARCHAR types are ultimately fromsqlalchemy.types, and INET is specific to the PostgreSQL dialect.

Some dialect level types have the same name as the SQL standard type,but also provide additional arguments. For example, MySQL implementsthe full range of character and string types including additional argumentssuch as collation and charset:

  1. from sqlalchemy.dialects.mysql import VARCHAR, TEXT
  2.  
  3. table = Table('foo', meta,
  4.     Column('col1', VARCHAR(200, collation='binary')),
  5.     Column('col2', TEXT(charset='latin1'))
  6. )