Validating SQLAlchemy Fields

Question

I have a dictionary that gets created from a programatic process that looks like

{'field1: 3, 'field2: 'TEST'}

I feed this dictionary into the model as its fields (for example: Model(**dict))

I want to run a series of unit tests that determine whether the fields are of valid data type.

How do I validate that these data types are valid for my database without having to do an insertion and rollback as this would introduce flakiness into my tests as I would interacting with an actual database correct? (MySQL).

Possible duplicate of [How can I verify Column data types in the SQLAlchemy ORM?](https://stackoverflow.com/questions/8980735/how-can-i-verify-column-data-types-in-the-sqlalchemy-orm) — Michael, Nov 22 '18 at 12:27

score 1 · Answer 1 · answered Jul 24 '15 at 18:48

1

I do not have much experience with sqlalchemy but if you use data-types in Columns of your models, won't that work?

This link might help you : http://docs.sqlalchemy.org/en/rel_0_9/core/type_basics.html

answered Jul 24 '15 at 18:48

Akash B

99
8

Manuel J. Diaz · Answer 2 · 2015-07-24T19:19:51.737

Here's a rudimentary way to do what you asked

class Sample_Table(Base):
    __tablename__ = 'Sample_Table'
    __table_args__ = {'sqlite_autoincrement': True}
    id = Column(Integer, primary_key=True, nullable=False)
    col1 = Column(Integer)
    col2 = Column(Integer)

    def __init__(self, **kwargs):
        for k,v in kwargs.items():
            col_type = str(self.__table__.c[k].type)
            try:            
                if str(type(v).__name__) in col_type.lower():
                    setattr(self, k, v)
                else:
                    raise Exception("BAD COLUMN TYPE FOR COL " + k)
            except ValueError as e:
                print e.message

If you try to use the above to insert a record with a column type that is different than what you specified, it will throw an error, i.e. it will not perform an insertion and rollback.

To prove that this works, try the following full-working code:

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Sample_Table(Base):
    __tablename__ = 'Sample_Table'
    __table_args__ = {'sqlite_autoincrement': True}
    id = Column(Integer, primary_key=True, nullable=False)
    col1 = Column(Integer)
    col2 = Column(Integer)

    def __init__(self, **kwargs):
        for k,v in kwargs.items():
            col_type = str(self.__table__.c[k].type)
            try:            
                if str(type(v).__name__) in col_type.lower():
                    setattr(self, k, v)
                else:
                    raise Exception("BAD COLUMN TYPE FOR COL " + k)
            except ValueError as e:
                print e.message


engine = create_engine('sqlite:///')
session = sessionmaker()
session.configure(bind=engine)
s = session()
Base.metadata.create_all(engine)

data = {"col1" : 1, "col2" : 2}
record = Sample_Table(**data)
s.add(record) #works
s.commit()

data = {"col1" : 1, "col2" : "2"}
record = Sample_Table(**data)
s.add(record) #doesn't work!
s.commit()

s.close()

(Even though I used SQLite, it will work for a MySQL database alike.)

@Andy, is this what you were looking for? If so, please mark the answer as accepted; if not, please clarify. — Manuel J. Diaz, Jul 26 '15 at 22:38

Validating SQLAlchemy Fields

2 Answers2