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

Improve this question
6
  • It is sqlite. It has a rather unique (dynamic) type system among SQL DBMS. See comments here: stackoverflow.com/questions/50776139/…, and the answer here: stackoverflow.com/questions/48462859/…. Also related: stackoverflow.com/questions/8980735/… Commented Feb 22, 2019 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. Commented Feb 22, 2019 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. Commented Feb 22, 2019 at 15:50
  • @Ilja Everilä, do you have a quick tip on how to prevent this insertion when using sqlite? Commented Feb 22, 2019 at 15:50
  • 1
    I'm afraid that the usual answer is don't use sqlite, if you need a stricter type system. Commented Feb 22, 2019 at 15:52
Related questions
3413
Improve INSERT-per-second performance of SQLite
3002
Convert string "Jun 1 2005 1:33PM" into datetime
3882
Convert bytes to a string in Python 3
Load 6 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.