def update_dat_crimes():
# Step Five: Update Main Crime table
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
try:
src_crime_table = Table('src_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
src_crime_table = src_crime()
try:
new_crime_table = Table('new_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
new_crime_table = new_crime()
excluded_cols = ['end_date', 'current_flag', 'chicago_crimes_all_row_id']
dat_cols = [c for c in dat_crime_table.columns.keys() if c not in excluded_cols]
excluded_cols.append('start_date')
src_cols = [c for c in src_crime_table.columns if c.name not in excluded_cols]
src_cols.append(text("'%s' AS start_date" % datetime.now().strftime('%Y-%m-%d')))
ins = dat_crime_table.insert()\
.from_select(
dat_cols,
select(src_cols)\
.select_from(src_crime_table.join(new_crime_table,
src_crime_table.c.id == new_crime_table.c.id))
)
conn = engine.connect()
conn.execute(ins)
return 'Crime Table updated'
def new_crime():
# Step Four: Find New Crimes
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
try:
src_crime_table = Table('src_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
src_crime_table = src_crime()
new_crime_table = Table('new_chicago_crimes_all', Base.metadata,
Column('id', Integer, primary_key=True),
extend_existing=True)
new_crime_table.drop(bind=engine, checkfirst=True)
new_crime_table.create(bind=engine)
ins = new_crime_table.insert()\
.from_select(
['id'],
select([src_crime_table.c.id])\
.select_from(src_crime_table.join(dat_crime_table,
src_crime_table.c.id == dat_crime_table.c.id, isouter=True))\
.where(dat_crime_table.c.chicago_crimes_all_row_id == None)
)
conn = engine.connect()
try:
conn.execute(ins)
return new_crime_table
except TypeError:
# No new records
return None
def new_crime():
# Step Four: Find New Crimes
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
try:
src_crime_table = Table('src_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
src_crime_table = src_crime()
new_crime_table = Table('new_chicago_crimes_all', Base.metadata,
Column('id', Integer, primary_key=True),
extend_existing=True)
new_crime_table.drop(bind=engine, checkfirst=True)
new_crime_table.create(bind=engine)
ins = new_crime_table.insert()\
.from_select(
['id'],
select([src_crime_table.c.id])\
.select_from(src_crime_table.join(dat_crime_table,
src_crime_table.c.id == dat_crime_table.c.id, isouter=True))\
.where(dat_crime_table.c.chicago_crimes_all_row_id == None)
)
conn = engine.connect()
try:
conn.execute(ins)
return new_crime_table
except TypeError:
# No new records
return None
def update_dat_crimes():
# Step Five: Update Main Crime table
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
try:
src_crime_table = Table('src_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
src_crime_table = src_crime()
try:
new_crime_table = Table('new_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
new_crime_table = new_crime()
excluded_cols = ['end_date', 'current_flag', 'chicago_crimes_all_row_id']
dat_cols = [c for c in dat_crime_table.columns.keys() if c not in excluded_cols]
excluded_cols.append('start_date')
src_cols = [c for c in src_crime_table.columns if c.name not in excluded_cols]
src_cols.append(text("'%s' AS start_date" % datetime.now().strftime('%Y-%m-%d')))
ins = dat_crime_table.insert()\
.from_select(
dat_cols,
select(src_cols)\
.select_from(src_crime_table.join(new_crime_table,
src_crime_table.c.id == new_crime_table.c.id))
)
conn = engine.connect()
conn.execute(ins)
return 'Crime Table updated'
def update_master_current_flag():
# Step Eight: Update end_date and current_flag in master table
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
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.connect()
conn.execute(update)
return 'Master table current flag updated'
def update_master_current_flag():
# Step Eight: Update end_date and current_flag in master table
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
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.connect()
conn.execute(update)
return 'Master table current flag updated'
def update_crime_current_flag():
# Step Seven: Update end_date and current_flag in crime table
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
try:
chg_crime_table = Table('chg_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
chg_crime_table = chg_crime()
update = dat_crime_table.update()\
.values(current_flag=False, end_date=datetime.now().strftime('%Y-%m-%d'))\
.where(dat_crime_table.c.id==chg_crime_table.c.id)\
.where(dat_crime_table.c.current_flag == True)
print update
conn = engine.connect()
conn.execute(update)
return 'Crime table current flag updated'
def update_crime_current_flag():
# Step Seven: Update end_date and current_flag in crime table
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
try:
chg_crime_table = Table('chg_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
chg_crime_table = chg_crime()
update = dat_crime_table.update()\
.values(current_flag=False, end_date=datetime.now().strftime('%Y-%m-%d'))\
.where(dat_crime_table.c.id==chg_crime_table.c.id)\
.where(dat_crime_table.c.current_flag == True)
print update
conn = engine.connect()
conn.execute(update)
return 'Crime table current flag updated'
def dedupe_crime():
# Step Two: Find duplicate records by case_number
try:
raw_crime_table = Table('raw_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
raw_crime_table = raw_crime(tablename='raw_chicago_crimes_all')
dedupe_crime_table = Table('dedup_chicago_crimes_all', Base.metadata,
Column('dup_row_id', Integer, primary_key=True),
extend_existing=True)
dedupe_crime_table.drop(bind=engine, checkfirst=True)
dedupe_crime_table.create(bind=engine)
ins = dedupe_crime_table.insert()\
.from_select(
['dup_row_id'],
select([func.max(raw_crime_table.c.dup_row_id)])\
.group_by(raw_crime_table.c.id)
)
conn = engine.connect()
conn.execute(ins)
return dedupe_crime_table
def dedupe_crime():
# Step Two: Find duplicate records by case_number
try:
raw_crime_table = Table('raw_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
raw_crime_table = raw_crime(tablename='raw_chicago_crimes_all')
dedupe_crime_table = Table('dedup_chicago_crimes_all', Base.metadata,
Column('dup_row_id', Integer, primary_key=True),
extend_existing=True)
dedupe_crime_table.drop(bind=engine, checkfirst=True)
dedupe_crime_table.create(bind=engine)
ins = dedupe_crime_table.insert()\
.from_select(
['dup_row_id'],
select([func.max(raw_crime_table.c.dup_row_id)])\
.group_by(raw_crime_table.c.id)
)
conn = engine.connect()
conn.execute(ins)
return dedupe_crime_table
def chg_crime():
# Step Seven: Find updates
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
try:
src_crime_table = Table('src_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
src_crime_table = new_crime()
chg_crime_table = Table('chg_chicago_crimes_all', Base.metadata,
Column('id', Integer, primary_key=True),
extend_existing=True)
chg_crime_table.drop(bind=engine, checkfirst=True)
chg_crime_table.create(bind=engine)
src_cols = [c for c in src_crime_table.columns if c.name != 'id']
dat_cols = [c for c in dat_crime_table.columns if c.name != 'id']
and_args = []
for s, d in zip(src_cols, dat_cols):
ors = or_(s != None, d != None)
ands = and_(ors, s != d)
and_args.append(ands)
ins = chg_crime_table.insert()\
.from_select(
['id'],
select([src_crime_table.c.id])\
.select_from(src_crime_table.join(dat_crime_table,
src_crime_table.c.id == dat_crime_table.c.id))\
.where(or_(
and_(dat_crime_table.c.current_flag == True,
and_(or_(src_crime_table.c.id != None, dat_crime_table.c.id != None),
src_crime_table.c.id != dat_crime_table.c.id)),
*and_args))
)
conn = engine.connect()
conn.execute(ins)
return chg_crime_table
def update_master():
# Step Six: Update Master table
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
try:
new_crime_table = Table('new_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
new_crime_table = new_crime()
col_names = ['start_date', 'end_date', 'current_flag', 'location', 'latitude', 'longitude']
cols = [
dat_crime_table.c.start_date,
dat_crime_table.c.end_date,
dat_crime_table.c.current_flag,
dat_crime_table.c.location,
dat_crime_table.c.latitude,
dat_crime_table.c.longitude,
]
cols.append(dat_crime_table.c.orig_date.label('obs_date'))
cols.append(text("NULL AS obs_ts"))
cols.append(text("NULL AS geotag1"))
cols.append(text("NULL AS geotag2"))
cols.append(text("NULL AS geotag3"))
cols.append(text("'chicago_crimes_all' AS dataset_name"))
cols.append(dat_crime_table.c.chicago_crimes_all_row_id.label('dataset_row_id'))
cols.append(text("ST_PointFromText('POINT(' || dat_chicago_crimes_all.longitude || ' ' || dat_chicago_crimes_all.latitude || ')', 4326) as location_geom"))
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.connect()
conn.execute(ins)
return 'Master updated'
def chg_crime():
# Step Seven: Find updates
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
try:
src_crime_table = Table('src_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
src_crime_table = new_crime()
chg_crime_table = Table('chg_chicago_crimes_all', Base.metadata,
Column('id', Integer, primary_key=True),
extend_existing=True)
chg_crime_table.drop(bind=engine, checkfirst=True)
chg_crime_table.create(bind=engine)
src_cols = [c for c in src_crime_table.columns if c.name != 'id']
dat_cols = [c for c in dat_crime_table.columns if c.name != 'id']
and_args = []
for s, d in zip(src_cols, dat_cols):
ors = or_(s != None, d != None)
ands = and_(ors, s != d)
and_args.append(ands)
ins = chg_crime_table.insert()\
.from_select(
['id'],
select([src_crime_table.c.id])\
.select_from(src_crime_table.join(dat_crime_table,
src_crime_table.c.id == dat_crime_table.c.id))\
.where(or_(
and_(dat_crime_table.c.current_flag == True,
and_(or_(src_crime_table.c.id != None, dat_crime_table.c.id != None),
src_crime_table.c.id != dat_crime_table.c.id)),
*and_args))
)
conn = engine.connect()
conn.execute(ins)
return chg_crime_table
def update_master():
# Step Six: Update Master table
try:
dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dat_crime_table = dat_crime()
try:
new_crime_table = Table('new_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
new_crime_table = new_crime()
col_names = ['start_date', 'end_date', 'current_flag', 'location', 'latitude', 'longitude']
cols = [
dat_crime_table.c.start_date,
dat_crime_table.c.end_date,
dat_crime_table.c.current_flag,
dat_crime_table.c.location,
dat_crime_table.c.latitude,
dat_crime_table.c.longitude,
]
cols.append(dat_crime_table.c.orig_date.label('obs_date'))
cols.append(text("NULL AS obs_ts"))
cols.append(text("NULL AS geotag1"))
cols.append(text("NULL AS geotag2"))
cols.append(text("NULL AS geotag3"))
cols.append(text("'chicago_crimes_all' AS dataset_name"))
cols.append(dat_crime_table.c.chicago_crimes_all_row_id.label('dataset_row_id'))
cols.append(text("ST_PointFromText('POINT(' || dat_chicago_crimes_all.longitude || ' ' || dat_chicago_crimes_all.latitude || ')', 4326) as location_geom"))
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.connect()
conn.execute(ins)
return 'Master updated'
def dat_crime():
# Step Zero: Create dat_crime table
try:
src_crime_table = Table('src_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
src_crime_table = src_crime()
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, default=datetime.now))
dat_crime_table.append_column(Column('end_date', TIMESTAMP, default=None))
dat_crime_table.append_column(Column('current_flag', Boolean, default=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.connect()
conn.execute(dat_ins)
return dat_crime_table
def dat_crime():
# Step Zero: Create dat_crime table
try:
src_crime_table = Table('src_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
src_crime_table = src_crime()
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, default=datetime.now))
dat_crime_table.append_column(Column('end_date', TIMESTAMP, default=None))
dat_crime_table.append_column(Column('current_flag', Boolean, default=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.connect()
conn.execute(dat_ins)
return dat_crime_table
def src_crime():
# Step Three: Create New table with unique ids
try:
raw_crime_table = Table('raw_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
raw_crime_table = raw_crime()
try:
dedupe_crime_table = Table('dedup_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dedupe_crime_table = dedupe_crime()
src_crime_table = crime_table('src_chicago_crimes_all', Base.metadata)
src_crime_table.drop(bind=engine, checkfirst=True)
src_crime_table.create(bind=engine)
ins = src_crime_table.insert()\
.from_select(
src_crime_table.columns.keys(),
select([c for c in raw_crime_table.columns if c.name != 'dup_row_id'])\
.where(raw_crime_table.c.dup_row_id == dedupe_crime_table.c.dup_row_id)
)
conn = engine.connect()
conn.execute(ins)
return src_crime_table
def src_crime():
# Step Three: Create New table with unique ids
try:
raw_crime_table = Table('raw_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
raw_crime_table = raw_crime()
try:
dedupe_crime_table = Table('dedup_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
except NoSuchTableError:
dedupe_crime_table = dedupe_crime()
src_crime_table = crime_table('src_chicago_crimes_all', Base.metadata)
src_crime_table.drop(bind=engine, checkfirst=True)
src_crime_table.create(bind=engine)
ins = src_crime_table.insert()\
.from_select(
src_crime_table.columns.keys(),
select([c for c in raw_crime_table.columns if c.name != 'dup_row_id'])\
.where(raw_crime_table.c.dup_row_id == dedupe_crime_table.c.dup_row_id)
)
conn = engine.connect()
conn.execute(ins)
return src_crime_table