clean postgre single table

clean postgre single table

DBCleanByTableName.py

from Query import Queryimport timeclass DBCleanByTableName:    vhm_id_range = 4294967295l    _query = Query()    _delete_tables = []    def clean_single_table(self, table_name, owner_id):        """        Clean up single table by table_name and owner_id        """        try:            self._delete_tables = []            info('[{0}]Start delete data from table[{1}]...', self.__get_time(), table_name)            self.__purge_singe_table_data(table_name, owner_id)            info('[{0}]Delete table[{1}] sucessful! Constraint_tables : {2}', self.__get_time(), table_name, str(self._delete_tables))        except Exception,e:            error('Delete data from table[{0}] fail!', table_name)            raise e        finally:            self._query.close_pgdb_conn()    def __purge_singe_table_data(self, table_name, owner_id):        min_id = owner_id << 32        max_id = min_id + self.vhm_id_range        constraint_tables = self._query.get_constraint_table(table_name)        if not constraint_tables:            self.__do_purge_data(table_name, min_id, max_id, owner_id)        else:            for item in constraint_tables:                self._delete_tables.append(item[0])                self.__purge_singe_table_data(item[0], owner_id)            self.__do_purge_data(table_name, min_id, max_id, owner_id)    def __do_purge_data(self, table_name, min_id, max_id, owner_id):        table_columns = self._query.get_column_by_table(table_name)        if table_columns:            if ['id'] in table_columns and ['owner_id'] in table_columns:                column = 'id'            else:                column = table_columns[0].get(0)        else:            error("Don't get columns of {0}", table_name)            raise        delete_sql = 'DELETE FROM {0} where {1} between {2} and {3}'.format(table_name, column, min_id, max_id)        self._query.delete_table_by_sql(delete_sql)    def __get_time(self):        return time.strftime('%Y-%m-%d %X', time.localtime())if __name__ == '__main__':    result = DBCleanByTableName().clean_single_table('hm_nwk_plcy',102)

Query.py

import psycopg2import psycopg2.extrasclass Query:    """    Connect pgdb and execute sql    """    _env = Env()    _db_host = _env.get('db.host')    _db_port = _env.get('db.port')    _db_user = _env.get('db.user.name')    _db_password = _env.get('db.user.password')    _db_name = _env.get('db.name')    try:        _pgdb_conn = psycopg2.connect(host=_db_host, dbname=_db_name, port=_db_port, user=_db_user, password=_db_password)        _cursor = _pgdb_conn.cursor(cursor_factory=psycopg2.extras.DictCursor)        info('[Get pgdb connect sucess!] Host : {0}, DB name : {1}', _db_host, _db_name)    except Exception, e:        error("conntect postgre database failed, ret = {0}", e)        _cursor.close()        _pgdb_conn.close()        raise e    get_constraint_table_sql = 'SELECT tc.table_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ' \                               'ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ' \                               'ON ccu.constraint_name= tc.constraint_name WHERE constraint_type = \'FOREIGN KEY\' AND ccu.table_name=\'{0}\''    get_column_by_table_sql = 'SELECT a.attname FROM pg_class as c,pg_attribute as a  where c.relname =\'{0}\' and a.attrelid = c.oid and a.attnum>0 '    def get_constraint_table(self, table_name):        """        Get the constraint tables of table_name        """        sql = self.get_constraint_table_sql.format(table_name)        self._cursor.execute(sql)        constraint_tables = self._cursor.fetchall()        self._pgdb_conn.commit()        return constraint_tables    def get_column_by_table(self, table_name):        """        Get all columns of table_name        """        sql = self.get_column_by_table_sql.format(table_name)        self._cursor.execute(sql)        table_columns = self._cursor.fetchall()        self._pgdb_conn.commit()        return table_columns    def delete_table_by_sql(self, delete_sql):        self._cursor.execute(delete_sql)        self._pgdb_conn.commit()    def close_pgdb_conn(self):        self._cursor.close()        self._pgdb_conn.close()        info('[Close pgdb connect sucess!] Host : {0}, DB name : {1}', self._db_host, self._db_name)