def add_federal_dist_id_field(args): try: db_session = DBSession() transaction.manager.begin() eng = db_session.get_bind() meta_data = Base.metadata real_table = Table(Region.__table__.name, meta_data, schema=Region.__table_args__['schema'], autoload=True, autoload_with=eng) if not Region.federal_dist_id.key in real_table.columns: StructUpdater.create_column(real_table, Region.federal_dist_id.key, Region.federal_dist_id.type) # it's super cool... SQL Migration! eng.execute('''ALTER TABLE compulink.region ADD CONSTRAINT region_federal_dist_id_fkey FOREIGN KEY (federal_dist_id) REFERENCES compulink.federal_district (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION; ''') transaction.manager.commit() db_session.close() print ('Federal district id column added for ' + real_table.name) except Exception as ex: print('Error on adding field to Region table: %s' % (ex.message))
def create_column(cls, table_object, field_name, field_type): db_session = DBSession() engine = db_session.get_bind() column = Column(field_name, field_type) column_name = column.compile(dialect=engine.dialect) column_type = column.type.compile(engine.dialect) engine.execute('ALTER TABLE "%s"."%s" ADD COLUMN %s %s' % (table_object.schema or 'public', table_object.name, column_name, column_type))
def __change_column_datatype(table_uid, field_uid, new_column_type): db_session = DBSession() engine = db_session.get_bind() column = Column('fld_%s' % field_uid, new_column_type) column_name = column.compile(dialect=engine.dialect) column_type = column.type.compile(engine.dialect) engine.execute('ALTER TABLE "vector_layer"."layer_%s" ALTER COLUMN %s TYPE %s' % (table_uid, column_name, column_type))
def __drop_column(table_uid, field_uid): # еще не юзал! db_session = DBSession() engine = db_session.get_bind() column = Column('fld_%s' % field_uid) column_name = column.compile(dialect=engine.dialect) engine.execute('ALTER TABLE "vector_layer"."layer_%s" DROP COLUMN %s' % (table_uid, column_name))
def drop_vector_layer_table(table_uid, make_transaction=False): db_session = DBSession() #start transaction if make_transaction: transaction.manager.begin() engine = db_session.get_bind() engine.execute('DROP TABLE "vector_layer"."layer_%s"' % table_uid) #close transaction if make_transaction: transaction.manager.commit() else: db_session.flush()
def append_status_dt(cls): db_session = DBSession() transaction.manager.begin() eng = db_session.get_bind() meta_data = Base.metadata real_table = Table(FoclStruct.__table__.name, meta_data, autoload=True, autoload_with=eng) if not FoclStruct.status_upd_dt.key in real_table.columns: StructUpdater.create_column(real_table, FoclStruct.status_upd_dt.key, FoclStruct.status_upd_dt.type) print 'Status DT column added for ' + real_table.name transaction.manager.commit() db_session.close()