2

I am using SQLALCHEMY to import some some data from spreadsheets into a sqlite database. I define the tables using a declaritive base with the 

import sqlalchemy as sdb
import sqlalchemy.ext.declarative as sed
Base = sed.declarative_base()

class Volume(Base):
    __tablename__ = 'volumes'
    current_node = sdb.Column(sdb.Integer, primary_key=True)
    relation_to_last = sdb.Column(sdb.Integer, nullable=False)
    last_node = sdb.Column(sdb.Integer, nullable=False)
    rotation = sdb.Column(sdb.Integer)
    cross_st_name = sdb.Column(sdb.String)
    through_st_name = sdb.Column(sdb.String)
    from_west = sdb.Column(ArrayType())
    from_north = sdb.Column(ArrayType())
    from_east = sdb.Column(ArrayType())
    from_south = sdb.Column(ArrayType())
    am_peak_factor = sdb.Column(sdb.Integer)
    pm_peak_factor = sdb.Column(sdb.Integer)
    am_peak_hour = sdb.Column(ArrayType())
    pm_peak_hour = sdb.Column(ArrayType())
    __table_args__ = tuple(sdb.UniqueConstraint(relation_to_last, last_node))

Yet when I test this and put a string where an integer should be the 'string' is read into the database (see below). I thought this would have thrown an error. Is there something wrong with the way I defined the table or is there not enough information here to answer my question?

database with a string in an int col

cole
  • 125
  • 9
  • It is sqlite. It has a rather unique (dynamic) type system among SQL DBMS. See comments here: https://stackoverflow.com/questions/50776139/sqlalchemy-accepts-everything-like-different-data-types-empty-strings-strings, and the answer here: https://stackoverflow.com/questions/48462859/sqlalchemy-selects-give-different-results-on-sqlite-table-raw-sql-versus-select. Also related: https://stackoverflow.com/questions/8980735/how-can-i-verify-column-data-types-in-the-sqlalchemy-orm?noredirect=1&lq=1 – Ilja Everilä Feb 22 '19 at 15:44
  • Are you sure your query is looking for this row? can you session.query(Volume).filter(Volume.current_node == 6).all() using .all() will force the query to execute and retrieve the row, it should produce an error converting string type to integer. But this error will only be thrown when the offending row is loaded/processed. – crooksey Feb 22 '19 at 15:45
  • @crooksey this is a screenshot of the values in the database after inserting them from CSVs. This is before any queries are performed on the table. I thought defining the column as an integer would have prevented this insertion. – cole Feb 22 '19 at 15:50
  • @Ilja Everilä, do you have a quick tip on how to prevent this insertion when using sqlite? – cole Feb 22 '19 at 15:50
  • 1
    I'm afraid that the usual answer is don't use sqlite, if you need a stricter type system. – Ilja Everilä Feb 22 '19 at 15:52
  • Here's an example of hunting down an offending row, when wrong data sneaked in: https://stackoverflow.com/questions/49183577/typeerror-string-argument-without-an-encoding-flask-and-sqlalchemy – Ilja Everilä Feb 22 '19 at 15:58

0 Answers0