On 11/08/2016 01:03 PM, Vlad Frolov wrote:
I was trying to solve an issue of an incorrect Alembic migration
autogeneration for a custom field (SQLAlchemy-Uitls.PasswordType), which
fallbacks to VARBINARY type for SQLite and it turned out that SQLAlchemy
confuses me:
*from* sqlalchemy *import* VARBINARY
*from* sqlalchemy.dialects.sqlite *import* dialect as sqlite_dialect
sqlite_dialect._resolve_type_affinity(*str*(VARBINARY(128)))
NUMERIC(precision=128)
sqlite_dialect.type_descriptor(sqlalchemy.VARBINARY(128))
VARBINARY(length=128)
Is this an expected behaviour? I expected to see the same output in both
cases. Alembic gets confused as the DB reports the column type as
NUMERIC(precision=128) (which type affinity is Numeric), while
PasswordType inspection ends up with VARBINARY(length=128) (which type
affinity is _Binary).
I went to see what "resolve_type_affinity" does as this is an odd method
in the first place. SQLAlchemy has its own concept of "type affinity"
but this particular method is doing something totally specific to SQLite
itself.
The docstring for resolve_type_affinity is in-depth and should explain
the behavior, if you also read the datatype3.html document linked
(though note it appears to be section 3.1 now, no longer section 2.1).
The short answer is, specific types don't matter on SQLite very much so
you wouldn't want to use a very specific type like VARBINARY in the
first place, and in this case VARBINARY on SQLite is NUMERIC due to
SQLite's own naming rules (there is no actual VARBINARY type).
If sqlalchemy-utils is using VARBINARY in a database-agnostic way, they
should change that. VARBINARY is a very specific datatype that means
different things on different platforms (which is what UPPERCASE_NAMES
mean in the typing system). There is no "short binary" datatype that
is database agnostic, unfortunately, only LargeBinary which generally
tries to act like BLOB (and which SQlite's affinity rules support).
def _resolve_type_affinity()
Return a data type from a reflected column, using affinity tules.
SQLite's goal for universal compatibility introduces some complexity
during reflection, as a column's defined type might not actually be a
type that SQLite understands - or indeed, my not be defined *at all*.
Internally, SQLite handles this with a 'data type affinity' for each
column definition, mapping to one of 'TEXT', 'NUMERIC', 'INTEGER',
'REAL', or 'NONE' (raw bits). The algorithm that determines this is
listed in http://www.sqlite.org/datatype3.html section 2.1.
This method allows SQLAlchemy to support that algorithm, while still
providing access to smarter reflection utilities by regcognizing column
definitions that SQLite only supports through affinity (like DATE and
DOUBLE).
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
