Flask SQlAlchemy

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'weather_detail.basic_id' could not find table 'weatherbasic' with which to generate a foreign key to target column 'id'

the table name is automatically set for you unless overridden. It’s derived from the class name converted to lowercase and with “CamelCase” converted to “camel_case”.

Define table

Define a table
Define columns: Integer, String, Boolean, Datetime
Constriction: unique, nullable
Add default value

class CurrentBasic(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    location = db.Column(db.String(50), nullable=False)
    description = db.Column(db.String(50), nullable=False)
    icon = db.Column(db.String(10), nullable=False)
    temperature = db.Column(db.Integer, nullable=False)
    temp_unit = db.Column(db.String(10), nullable=False)
    dt = db.Column(db.DateTime, nullable=False)
    amendment = db.Column(db.Boolean, nullable=False, default=False)
    last_update = db.Column(db.DateTime, nullable=False)

def __init__(self, session_id, location, description, icon, temperature, temp_unit, dt):
     self.location = location
     self.description = description
     self.icon = icon
     self.temperature = temperature
     self.temp_unit = temp_unit
     self.dt = dt
     self.last_update = datetime.now()

def __repr__(self):
     return '<CurrentBasic %r>' % self.location

PickleType

PickleType holds Python objects, which are serialized using pickle. For example, here I use PickleType to store a list.

class Daily(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    location = db.Column(db.String(50), nullable=False)
    temp_unit = db.Column(db.String(10), nullable=False)
    daily_list = db.Column(db.PickleType, nullable=False)
    last_update = db.Column(db.DateTime, nullable=False)

Foreign Key & One to One Relationship

uselist=False means this is a one to one relationship
one to one relationship can not set lazy='dynamic'

class CurrentBasic(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    location = db.Column(db.String(50), nullable=False)
    detail = db.relationship('CurrentDetail', backref=db.backref('basic'), uselist=False)


class CurrentDetail(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    basic_id = db.Column(db.Integer, db.ForeignKey('current_basic.id'))

Exception:

* sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'weather_detail.basic_id' could not find table 'weatherbasic' with which to generate a foreign key to target column 'id'

Root cause:

The table name is automatically set for you unless overridden. It’s derived from the class name converted to lowercase and with “CamelCase” converted to “camel_case”.

So, change db.ForeignKey('currentbasic.id') to db.ForeignKey('current_basic.id')

Query, filter

filter_by

detail = CurrentDetail.query.filter_by(basic_id=basic.id).first()

fileter

from sqlalchemy import and_
condition = and_(CurrentBasic.session_id == self.session_id, CurrentBasic.location == self.location)
basic = CurrentBasic.query.filter(condition).first()

filter all

history_basic = HistoryBasic.query.filter_by(session_id=self.session_id).all()

Insert

basic = CurrentBasic(current['location'],
                     current['description'],
                     current['icon'],
                     current['temperature'],
                     current['temp_unit'],
                     current['datetime'])
db.session.add(basic)
db.session.commit()

Insert to a table with foreign key

detail = CurrentDetail(current['pressure'],
                       current['humidity'],
                       current['wind_direction'],
                       current['wind_speed'],
                       current['wind_unit'],
                       current['clouds'],
                       current['sunrise'],
                       current['sunset'],
                       current['rain'],
                       current['snow'],
                       basic)
db.session.add(detail )
db.session.commit()

Update

basic.description = current['description']
basic.icon = current['icon']
basic.temperature = current['temperature']
basic.temp_unit = current['temp_unit']
basic.dt = current['datetime']
basic.last_update = datetime.now()
db.session.commit()

Import json file to database

# load weather description
def load_description():
    with open('description.json', encoding='utf-8') as file:
        text = file.readlines()
        for line in text:
            data = json.loads(line)
            record = Description(data['desc_id'], data['group'],
                                 data['description'])
            db.session.add(record)
            db.session.commit()

Configuration

config.py

import os

BASE_DIR = os.path.abspath(os.path.dirname(__file__))
SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(BASE_DIR, 'weather.db')
SQLALCHEMY_TRACK_MODIFICATIONS = True

5.2 Flask SQLAlchemy