Coercion in SQLAlchemy from Column annotations

Question

Good day everyone,

I have a file of strings corresponding to the fields of my SQLAlchemy object. Some fields are floats, some are ints, and some are strings.

I'd like to be able to coerce my string into the proper type by interrogating the column definition. Is this possible?

For instance:

class MyClass(Base):
    ...
    my_field = Column(Float)

It feels like one should be able to say something like MyClass.my_field.column.type and either ask the type to coerce the string directly or write some conditions and int(x), float(x) as needed.

I wondered whether this would happen automatically if all the values were strings, but I received Oracle errors because the type was incorrect.

Currently I naively coerce -- if it's float()able, that's my value, else it's a string, and I trust that integral floats will become integers upon inserting because they are represented exactly. But the runtime value is wrong (e.g. 1.0 vs 1) and it just seems sloppy.

Thanks for your input!

SQLAlchemy 0.7.4

score 1 · Answer 1 · edited May 23 '17 at 12:00

Type coercion for sqlalchemy prior to committing to some database.

How can I verify Column data types in the SQLAlchemy ORM?

from sqlalchemy import (
    Column,
    Integer,
    String,
    DateTime,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
import datetime

Base = declarative_base()

type_coercion = {
    Integer: int,
    String: str,
    DateTime: datetime.datetime,
}
# this event is called whenever an attribute
# on a class is instrumented
@event.listens_for(Base, 'attribute_instrument')
def configure_listener(class_, key, inst):
    if not hasattr(inst.property, 'columns'):
        return
    # this event is called whenever a "set"
    # occurs on that instrumented attribute
    @event.listens_for(inst, "set", retval=True)
    def set_(instance, value, oldvalue, initiator):
        desired_type = type_coercion.get(inst.property.columns[0].type.__class__)
        coerced_value = desired_type(value)
        return coerced_value

class MyObject(Base):
    __tablename__ = 'mytable'
    id = Column(Integer, primary_key=True)
    svalue = Column(String)
    ivalue = Column(Integer)
    dvalue = Column(DateTime)

x = MyObject(svalue=50)
assert isinstance(x.svalue, str)

score 1 · Accepted Answer · answered Dec 25 '11 at 09:54

You can iterate over columns of the mapped Table:

for col in MyClass.__table__.columns:
    print col, repr(col.type)

... so you can check the type of each field by its name like this:

def get_col_type(cls_, fld_):
    for col in cls_.__table__.columns:
        if col.name == fld_:
            return col.type # this contains the instance of SA type

assert Float == type(get_col_type(MyClass, 'my_field'))

I would cache the results though if your file is large in order to save the for-loop on every row imported from the file.

score 0 · Answer 3 · answered Dec 25 '11 at 07:00

I'm not sure if I'm reading this question correctly, but I would do something like:

class MyClass(Base):
    some_float = Column(Float)
    some_string = Column(String)
    some_int = Column(Int)
    ...

    def __init__(self, some_float, some_string, some_int, ...):
        if isinstance(some_float, float):
            self.some_float = somefloat
        else:
            try:
                self.some_float = float(somefloat)
            except:
                # do something intelligent
        if isinstance(some_string, string):
        ...

And I would repeat the checking process for each column. I would trust anything to do it "automatically". I also expect your file of strings to be well structured, otherwise something more complicated would have to be done.

Assuming your file is a CSV (I'm not good with file reads in python, so read this as pseudocode):

while not EOF:
    thisline = readline('thisfile.csv', separator=',') # this line is an ordered list of strings
    thisthing = MyClass(some_float=thisline[0], some_string=thisline[1]...)
    DBSession.add(thisthing)

Hi Jonathan, thanks for your message. I'm afraid your initializer is what I called naive coercion; int doesn't play into it, although I guess another case could be if float(x) == int(x). This all depends though on the strings (~50 of them) having the right type, and I'd be able to check that if I could retrieve the column type versus making inferences from the values. Any further ideas? You've been a big help to me. — j128, Dec 25 '11 at 08:20

Coercion in SQLAlchemy from Column annotations

3 Answers3