def dat_crime(fpath=None):
# Step Zero: Create dat_crime table
raw_crime(fpath=fpath)
dedupe_crime()
src_crime()
src_crime_table = Table('src_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
dat_crime_table = crime_table('dat_chicago_crimes_all', Base.metadata)
dat_crime_table.append_column(Column('chicago_crimes_all_row_id', Integer, primary_key=True))
dat_crime_table.append_column(Column('start_date', TIMESTAMP, server_default=text('CURRENT_TIMESTAMP')))
dat_crime_table.append_column(Column('end_date', TIMESTAMP, server_default=text('NULL')))
dat_crime_table.append_column(Column('current_flag', Boolean, server_default=text('TRUE')))
dat_crime_table.append_constraint(UniqueConstraint('id', 'start_date'))
dat_crime_table.create(bind=engine, checkfirst=True)
new_cols = ['start_date', 'end_date', 'current_flag', 'chicago_crimes_all_row_id']
dat_ins = dat_crime_table.insert()\
.from_select(
[c for c in dat_crime_table.columns.keys() if c not in new_cols],
select([c for c in src_crime_table.columns])
)
conn = engine.contextual_connect()
res = conn.execute(dat_ins)
cols = crime_master_cols(dat_crime_table)
master_ins = MasterTable.insert()\
.from_select(
[c for c in MasterTable.columns.keys() if c != 'master_row_id'],
select(cols)\
.select_from(dat_crime_table)
)
conn = engine.contextual_connect()
res = conn.execute(master_ins)
return 'DAT crime created'
def update_master_current_flag():
# Step Eight: Update end_date and current_flag in master table
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
update = MasterTable.update()\
.values(current_flag=False, end_date=datetime.now().strftime('%Y-%m-%d'))\
.where(MasterTable.c.dataset_row_id == dat_crime_table.c.chicago_crimes_all_row_id)\
.where(dat_crime_table.c.current_flag==False)\
.where(dat_crime_table.c.end_date==date.today())
conn = engine.contextual_connect()
conn.execute(update)
return 'Master table current flag updated'
def update_master():
# Step Six: Update Master table
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
try:
new_crime_table = Table('new_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
return None
cols = crime_master_cols(dat_crime_table)
ins = MasterTable.insert()\
.from_select(
[c for c in MasterTable.columns.keys() if c != 'master_row_id'],
select(cols)\
.select_from(dat_crime_table.join(new_crime_table,
dat_crime_table.c.id == new_crime_table.c.id))
)
conn = engine.contextual_connect()
conn.execute(ins)
return 'Master updated'
