

In [7]:

    
import os
import sys
from sqlalchemy import Column, ForeignKey, Integer, String, Float, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy import create_engine



In [5]:

    
Base = declarative_base()

class Agency(Base):
    __tablename__ = 'agency'
    
    # Simpler to not use composite PK, because composite FKs are a pain
    uid = Column(String(50), primary_key=True)
    ag_id = Column(String(50), nullable=False)
    ag_type = Column(String(20), nullable=False)
    ag_brand = Column(String(50), nullable=False)
    ag_name = Column(String(100), nullable=False)
    ag_bcode = Column(String(50), nullable=False)
    ag_addr = Column(String(200), nullable=False)
    ag_city = Column(String(50), nullable=False)
    ag_state = Column(String(3), nullable=False)
    ag_zip = Column(Integer, nullable=False) 
    ag_dist = Column(Float, nullable=False)
    ag_lat = Column(Float, nullable=False)
    ag_lon = Column(Float, nullable=False)

    
class Price(Base):
    __tablename__ = 'prices'
    
    uid = Column(Integer, primary_key=True, autoincrement=True)
       
    agency_id = Column(String(50), ForeignKey('agency.uid'))
    # All hail our SQLAlchemy overlords
    agency = relationship("Agency",back_populates="prices")    
    
    pickup = Column(String(20), nullable=False)
    dropoff = Column(String(20), nullable=False)
    time = Column(String(20), nullable=False)
    querytime = Column(DateTime, nullable=False)
    
    car_econ = Column(Float)
    car_comp = Column(Float)
    car_int = Column(Float)
    car_std = Column(Float)
    car_full = Column(Float)
    car_prem = Column(Float)
    car_lux = Column(Float)
    car_intsuv = Column(Float)
    car_stdsuv = Column(Float)
    car_fullsuv = Column(Float)
    car_premsuv = Column(Float)
    car_mv = Column(Float)  
    car_fv = Column(Float)
    
# reciprocal link to make things nicer
Agency.prices = relationship("Price", order_by=Price.querytime,
                          back_populates="agency")
    
# in-memory database
engine = create_engine('sqlite://')
 
# Create all tables in the engine. 
Base.metadata.create_all(engine)