Peewee: NOT NULL constraint failed

Created on 13 Jun 2019 · 1Comment · Source: coleifer/peewee

models/prcl.py:

from peewee import *

DATABASE = SqliteDatabase('databases/prcl.db')


class Prcl(Model):
    CityBlock = FloatField()
    Parcel = IntegerField()
    OwnerCode = IntegerField()
    ParcelId = BigIntegerField()
    PrimAddrRecNum = IntegerField()
    AddrType = TextField(default="")
    LowAddrNum = IntegerField()
    LowAddrSuf = TextField(default="")
    HighAddrNum = IntegerField()
    HighAddrSuf = TextField(default="")
    NLC = IntegerField()
    Parity = TextField(default="")
    StPreDir = TextField(default="")
    StName = TextField(default="")
    StType = TextField(default="")
    StSufDir = TextField(default="")
    StdUnitNum = TextField(default="")
    OwnerName = TextField(default="")
    OwnerName2 = TextField(default="")
    OwnerAddr = TextField(default="")
    OwnerCity = TextField(default="")
    OwnerState = TextField(default="")
    OwnerCountry = TextField(default="")
    OwnerZIP = TextField(default="")
    OwnerRank = TextField(default="")
    LegalDesc1 = TextField(default="")
    LegalDesc2 = TextField(default="")
    LegalDesc3 = TextField(default="")
    LegalDesc4 = TextField(default="")
    LegalDesc5 = TextField(default="")
    AsrClassCode = IntegerField()
    AsrLandUse1 = IntegerField()
    AsrLanduse2 = IntegerField()
    RedevPhase = IntegerField()
    RedevYearEnd = IntegerField()
    RedevPhase2 = IntegerField()
    RedevYearEnd2 = IntegerField()
    VacantLot = BooleanField()
    SpecBusDist = IntegerField()
    SpecBusDist2 = IntegerField()
    TIFDist = IntegerField()
    LendingAgcy = IntegerField()
    Condominium = BooleanField()
    NbrOfUnits = IntegerField()
    NbrOfApts = IntegerField()
    Frontage = FloatField()
    LandArea = IntegerField()
    RecDailyDate = DateField()
    RecDailyNum = IntegerField()
    RecBookNum = TextField(default="")
    RecPageNum = IntegerField()
    AsdLand = DoubleField()
    AsdImprove = DoubleField()
    AsdTotal = DoubleField()
    BillLand = DoubleField()
    BillImprove = DoubleField()
    BillTotal = DoubleField()
    AprLand = DoubleField()
    CostAprImprove = DoubleField()
    AsmtAppealYear = IntegerField()
    AsmtAppealNum = IntegerField()
    AsmtAppealType = TextField(default="")
    PriorAsdDate = DateField()
    PriorAsdLand = DoubleField()
    PriorAsdImprove = DoubleField()
    PriorAsdTotal = DoubleField()
    PriorTaxAmt = DoubleField()
    CDALandUse1 = IntegerField()
    CDALandUse2 = IntegerField()
    LRMSUnitNum = TextField(default="")
    Zoning = TextField(default="")
    NbrOfBldgsRes = IntegerField()
    NbrOfBldgsCom = IntegerField()
    FirstYearBuilt = IntegerField()
    LastYearBuilt = IntegerField()
    ResSalePrice = DoubleField()
    ResSaleDate = DateField()
    VacBldgYear = IntegerField()
    GeoCityBlockPart = DoubleField()
    Ward10 = IntegerField()
    Precinct10 = IntegerField()
    InspArea10 = IntegerField()
    Ward00 = IntegerField()
    Precinct02 = IntegerField()
    Precinct04 = IntegerField()
    Nbrhd = IntegerField()
    CDADist = IntegerField()
    CDASubDist = IntegerField()
    PoliceDist = IntegerField()
    CensTract10 = IntegerField()
    CensBlock10 = IntegerField()
    CensBlock00 = DoubleField()
    Ward90 = IntegerField()
    Precinct90 = IntegerField()
    CensBlock90 = DoubleField()
    HouseConsDist = IntegerField()
    AsrNbrhd = IntegerField()
    EntZone = IntegerField()
    ImpactArea = IntegerField()
    CTDArea = IntegerField()
    LeafArea = IntegerField()
    ZIP = DoubleField()
    OnFloodBlock = IntegerField()
    SpecParcelType = TextField(default="")
    SubParcelType = TextField(default="")
    NbrOfSubAccts = IntegerField()
    NbrOfCondos = IntegerField()
    LRMSParcel = BooleanField()
    AcctPrimary = BooleanField()
    GisPrimary = BooleanField()
    GisCityBLock = FloatField()
    GisParcel = IntegerField()
    GisOwnerCode = IntegerField()
    Handle = TextField(default="")
    Parcel9 = DoubleField()
    OwnerOcc = TextField(default="")
    FirstDate = DateField()
    LastDate = DateField()
    OwnerUpdate = DateField()

    class Meta:
        database = DATABASE
        db_table = 'Prcl'


def initialize():
    DATABASE.connect()
    DATABASE.create_tables([Prcl], safe=True)
    DATABASE.close()

main.py:

import os
from datetime import datetime
import xml.etree.ElementTree as ET

import peewee

from models import prclsale
from models import prcl

field_types = {
    peewee.TextField: (lambda string: string),
    peewee.IntegerField: (lambda string: int(string)),
    peewee.FloatField: (lambda string: float(string)),
    peewee.DoubleField: (lambda string: float(string)),
    # this is how Access formats their dates
    peewee.DateField: (lambda string: datetime.strptime(string, '%Y-%m-%dT%H:%M:%S')),
    peewee.BooleanField: (lambda string: bool(int(string))),
    peewee.BigIntegerField: (lambda string: float(string))
}


# TODO add a way for the method to resume where it stopped from an error
def populate_database(database, file, model_class, update=False):
    model_class.initialize()
    # This method requires the xml file to be formatted a certain way so that it can use reflection
    if os.path.isfile(database) and not update:
        # abort if the database already exists
        pass
    root = ET.parse(file).getroot()
    for table_index, table in enumerate(list(root)):
        # get the table class
        table_class = getattr(model_class, table.tag)
        entries = list(table)
        for index, entry in enumerate(entries):
            # just printing the progress
            if index % 500 == 0:
                print(str(round(1000 * (index / len(entries))) / 10) + "%")
            fields = list(entry)
            table_class.create(
                # create the model with a dictionary of the row's fields and values
                # use the model class and field type lambdas to transform the xml text to the proper type
                **{field.tag: field_types.get(getattr(table_class, field.tag).__class__)(field.text)
                   for field in fields}
            )


populate_database('databases/prcl.db', 'static/prcl_TEST.xml', prcl)

static/prcl_TEST.xml:

<dataroot>
    <Prcl>
        <Prcl>
            <CityBlock>1</CityBlock>
            <Parcel>5</Parcel>
            <OwnerCode>0</OwnerCode>
            <ParcelId>00010000050</ParcelId>
            <PrimAddrRecNum>1</PrimAddrRecNum>
            <AddrType>3</AddrType>
            <LowAddrNum>626</LowAddrNum>
            <LowAddrSuf></LowAddrSuf>
            <HighAddrNum>626</HighAddrNum>
            <HighAddrSuf></HighAddrSuf>
            <NLC>785</NLC>
            <Parity>E</Parity>
            <StPreDir>S</StPreDir>
            <StName>1ST</StName>
            <StType>ST</StType>
            <StSufDir></StSufDir>
            <StdUnitNum></StdUnitNum>
            <OwnerName>CITY OF ST LOUIS</OwnerName>
            <OwnerAddr>1200 MARKET ST</OwnerAddr>
            <OwnerCity>ST LOUIS MO</OwnerCity>
            <OwnerZIP>63103</OwnerZIP>
            <LegalDesc1>CB 1 FIRST ST</LegalDesc1>
            <LegalDesc2>0.140 ACS</LegalDesc2>
            <LegalDesc3>BROWNS SURVEY ADDN LOT PTS 9 THRU 12</LegalDesc3>
            <LegalDesc4>BD N-POPCAR S- PLUM</LegalDesc4>
            <AsrClassCode>4</AsrClassCode>
            <AsrLandUse1>3000</AsrLandUse1>
            <AsrLanduse2>0</AsrLanduse2>
            <RedevPhase>0</RedevPhase>
            <RedevYearEnd>0</RedevYearEnd>
            <RedevPhase2>0</RedevPhase2>
            <RedevYearEnd2>0</RedevYearEnd2>
            <VacantLot>1</VacantLot>
            <SpecBusDist>0</SpecBusDist>
            <SpecBusDist2>0</SpecBusDist2>
            <TIFDist>0</TIFDist>
            <LendingAgcy>0</LendingAgcy>
            <Condominium>0</Condominium>
            <NbrOfUnits>0</NbrOfUnits>
            <NbrOfApts>0</NbrOfApts>
            <Frontage>0</Frontage>
            <LandArea>6098</LandArea>
            <RecDailyNum>0</RecDailyNum>
            <RecBookNum> CVN</RecBookNum>
            <RecPageNum>0</RecPageNum>
            <AsdLand>6800</AsdLand>
            <AsdImprove>-2300</AsdImprove>
            <AsdTotal>4400</AsdTotal>
            <BillLand>6800</BillLand>
            <BillImprove>-2300</BillImprove>
            <BillTotal>4400</BillTotal>
            <AprLand>21300</AprLand>
            <CostAprImprove>0</CostAprImprove>
            <AsmtAppealYear>0</AsmtAppealYear>
            <AsmtAppealNum>0</AsmtAppealNum>
            <PriorAsdDate>2015-10-04T00:00:00</PriorAsdDate>
            <PriorAsdLand>6800</PriorAsdLand>
            <PriorAsdImprove>-2300</PriorAsdImprove>
            <PriorAsdTotal>4400</PriorAsdTotal>
            <PriorTaxAmt>0</PriorTaxAmt>
            <CDALandUse1>6700</CDALandUse1>
            <CDALandUse2>0</CDALandUse2>
            <LRMSUnitNum></LRMSUnitNum>
            <NbrOfBldgsRes>0</NbrOfBldgsRes>
            <NbrOfBldgsCom>0</NbrOfBldgsCom>
            <FirstYearBuilt>0</FirstYearBuilt>
            <LastYearBuilt>0</LastYearBuilt>
            <ResSalePrice>0</ResSalePrice>
            <VacBldgYear>0</VacBldgYear>
            <GeoCityBlockPart>1.0085</GeoCityBlockPart>
            <Ward10>7</Ward10>
            <Precinct10>6</Precinct10>
            <InspArea10>3</InspArea10>
            <Ward00>7</Ward00>
            <Precinct02>12</Precinct02>
            <Precinct04>6</Precinct04>
            <Nbrhd>35</Nbrhd>
            <CDADist>9</CDADist>
            <CDASubDist>2</CDASubDist>
            <PoliceDist>4</PoliceDist>
            <CensTract10>1274</CensTract10>
            <CensBlock10>1054</CensBlock10>
            <CensBlock00>1222.1001</CensBlock00>
            <Ward90>7</Ward90>
            <Precinct90>6</Precinct90>
            <CensBlock90>1222.104</CensBlock90>
            <HouseConsDist>55</HouseConsDist>
            <AsrNbrhd>266</AsrNbrhd>
            <EntZone>1</EntZone>
            <ImpactArea>0</ImpactArea>
            <CTDArea>0</CTDArea>
            <LeafArea>0</LeafArea>
            <ZIP>63102</ZIP>
            <OnFloodBlock>1</OnFloodBlock>
            <NbrOfSubAccts>1</NbrOfSubAccts>
            <NbrOfCondos>0</NbrOfCondos>
            <LRMSParcel>1</LRMSParcel>
            <AcctPrimary>1</AcctPrimary>
            <GisPrimary>1</GisPrimary>
            <GisCityBLock>1</GisCityBLock>
            <GisParcel>5</GisParcel>
            <GisOwnerCode>0</GisOwnerCode>
            <Handle>10001000005</Handle>
            <Parcel9>100005</Parcel9>
            <FirstDate>2009-01-25T00:00:00</FirstDate>
            <LastDate>2019-05-17T00:00:00</LastDate>
            <OwnerUpdate>1983-05-10T00:00:00</OwnerUpdate>
        </Prcl>
    </Prcl>
</dataroot>

main.py just loads in the database from the xml file.
When I run main.py, I get 'peewee.IntegrityError: NOT NULL constraint failed: Prcl.LowAddrSuf.'
I have a default value for LowAddrSuf so I'm not sure how else to fix it.
Please ask if my problem isn't clear enough.

Source