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