cardinal_pythonlib.sqlalchemy.list_types
Original code copyright (C) 2009-2022 Rudolf Cardinal (rudolf@pobox.com).
This file is part of cardinal_pythonlib.
Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
SQLAlchemy type classes to store different kinds of lists in a database.
- class cardinal_pythonlib.sqlalchemy.list_types.IntListType(*args, **kwargs)[source]
Store a list of integers as CSV.
Note: see
StringListType
for a general discussion about SQLAlchemy types where the Python representation is a list; they can seem slightly unusual.Construct a
TypeDecorator
.Arguments sent here are passed to the constructor of the class assigned to the
impl
class level attribute, assuming theimpl
is a callable, and the resulting object is assigned to theself.impl
instance attribute (thus overriding the class attribute of the same name).If the class level
impl
is not a callable (the unusual case), it will be assigned to the same instance attribute ‘as-is’, ignoring those arguments passed to the constructor.Subclasses can override this to customize the generation of
self.impl
entirely.- process_bind_param(value: List[int] | None, dialect: Dialect) str [source]
Convert things on the way from Python to the database.
- process_literal_param(value: List[int] | None, dialect: Dialect) str [source]
Convert things on the way from Python to the database.
- process_result_value(value: str | None, dialect: Dialect) List[int] [source]
Convert things on the way from the database to Python.
- property python_type
Return the Python type object expected to be returned by 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 common DBAPIs (like
int
for example), will return that type.If a return type is not defined, raises
NotImplementedError
.Note that any type also accommodates NULL in SQL which means you can also get back
None
from any type in practice.
- class cardinal_pythonlib.sqlalchemy.list_types.StringListType(*args, **kwargs)[source]
Store a list of strings as CSV. (Rather less arbitrary in its encoding requirements than e.g. https://sqlalchemy-utils.readthedocs.io/en/latest/_modules/sqlalchemy_utils/types/scalar_list.html#ScalarListType.)
2019-01-01: removed trailing
\r\n
(vialineterminator=""
).Some related test code:
import csv from io import StringIO pythonlist = [None, 1, "string", "commas, within string", "line 1\nline2"] output_1 = StringIO() wr_1 = csv.writer(output_1, quoting=csv.QUOTE_ALL) # appends '\r\n' wr_1.writerow(pythonlist) csvstring_1 = output_1.getvalue() print(repr(csvstring_1)) backtopython_1 = list(csv.reader([csvstring_1]))[0] print(repr(backtopython_1)) output_2 = StringIO() wr_2 = csv.writer(output_2, quoting=csv.QUOTE_ALL, lineterminator="") wr_2.writerow(pythonlist) csvstring_2 = output_2.getvalue() print(repr(csvstring_2)) backtopython_2 = list(csv.reader([csvstring_2]))[0] print(repr(backtopython_2)) assert len(csvstring_1) > len(csvstring_2) assert backtopython_1 == backtopython_2
So:
The newline terminator is obviously unnecessary for something that will always be a single CSV line.
Eliminating it saves two bytes and adds clarity in the database representation.
Eliminating it keeps the system back-compatible, since the reader happily reads things without the line terminator.
NOTE in particular that this does not behave completely like a plain Python list on the Python side, as follows.
When an ORM object is created, the default value on the Python side is
None
.The SQLAlchemy
default
option is invoked atINSERT
, not at ORM object creation; see https://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Column.params.default.The SQLAlchemy
server_default
is the DDLDEFAULT
value, not a Python default.On database load, everything is fine (as
process_result_value
will be called, which can translate a databaseNULL
to a Python[]
).So that means that if you want the field to be a list rather than None from the outset, you must set it to
[]
from__init__()
.
Secondly, SQLAlchemy makes its columns behave in a special way upon assignment. So, in particular,
mylist.append(value)
will not itself mark the field as “dirty” and in need of writing to the database.Internally, support we define (on the class)
mycol = Column(Integer)
, and then create an instance viainstance = cls()
.Then
cls.mycol
will actually be of typesqlalchemy.orm.attributes.InstrumentedAttribute
, andinstance.mycol
will be of typeint
(orNoneType
if it’sNone
).from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.sql.schema import Column from sqlalchemy.sql.sqltypes import Integer Base = declarative_base() class MyClass(Base): __tablename__ = "mytable" pk = Column(Integer, primary_key=True) mycol = Column(Integer) instance = MyClass() type(MyClass.pk) # <class 'sqlalchemy.orm.attributes.InstrumentedAttribute'> type(instance.pk) # <class 'NoneType'>
The class
sqlalchemy.orm.attributes.InstrumentedAttribute
implements__set__()
,__delete__()
, and__get__()
. This means that when you writeinstance.mycol = 5
, it calls the__set__()
function; see https://docs.python.org/3.7/howto/descriptor.html.So, for a list (e.g.
mylist = Column(StringListType)
, if you writemylist = [value1, value2]
, it will call the appropriate__set__()
function and mark the field as “dirty” (see e.g.sqlalchemy.orm.attributes.ScalarAttributeImpl.set()
). But ifmylist
is already a list and you writemylist.append(value)
, the__set__()
function won’t be called.If you haven’t yet written the instance to the database, this doesn’t matter; “new” values are considered dirty and are written to the database fine. But if you (a) create, (b) save, and then (c) append to a list, the change won’t be noticed. Since SQLAlchemy can save objects for you as soon as another object needs to know it’s PK, the fact that (b) has happened may not be obvious.
Therefore, in short, beware append() and use assignment for these sorts of lists, if this might apply; e.g.
mylist = mylist + [value]
.Don’t use
+=
, either; that callslist.__iadd__()
and modifies the existing list, rather than callingInstrumentedAttribute.__set__()
.
So one method is to ignore
__init__()
(meaning new instances will have the list-type field set toNone
) and then using this sort of access function:def add_to_mylist(self, text: str) -> None: if self.mylist is None: self.mylist = [text] else: # noinspection PyAugmentAssignment self.mylist = self.mylist + [text] # not "append()", not "+="
Construct a
TypeDecorator
.Arguments sent here are passed to the constructor of the class assigned to the
impl
class level attribute, assuming theimpl
is a callable, and the resulting object is assigned to theself.impl
instance attribute (thus overriding the class attribute of the same name).If the class level
impl
is not a callable (the unusual case), it will be assigned to the same instance attribute ‘as-is’, ignoring those arguments passed to the constructor.Subclasses can override this to customize the generation of
self.impl
entirely.- process_bind_param(value: List[str] | None, dialect: Dialect) str [source]
Convert things on the way from Python to the database.
- process_literal_param(value: List[str] | None, dialect: Dialect) str [source]
Convert things on the way from Python to the database.
- process_result_value(value: str | None, dialect: Dialect) List[str] [source]
Convert things on the way from the database to Python.
- property python_type
Return the Python type object expected to be returned by 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 common DBAPIs (like
int
for example), will return that type.If a return type is not defined, raises
NotImplementedError
.Note that any type also accommodates NULL in SQL which means you can also get back
None
from any type in practice.