def create_tables(cur: DictCursor, conn: DictConnection): """ Function that creates all tables from `CREATE_TABLE_QUERIES` Parameters ---------- cur conn """ for query in CREATE_TABLE_QUERIES: cur.execute(query) conn.commit()
def load_staging_tables(cur: DictCursor, conn: DictConnection): """ Function that loads all data from the source S3 bucket into the staging Redshift tables by using the `COPY_TABLE_QUERIES` variable Parameters ---------- cur conn """ for query in COPY_TABLE_QUERIES: cur.execute(query) conn.commit()
def insert_tables(cur: DictCursor, conn: DictConnection): """ Function that inserts data from the staging Redshift tables into the Redshift analytical tables by using the `INSERT_TABLE_QUERIES` variable Parameters ---------- cur conn """ for query in INSERT_TABLE_QUERIES: cur.execute(query) conn.commit()
def drop_tables(cur: DictCursor, conn: DictConnection): """ Function that drops all tables from `DROP_TABLES` by using the `DROP_TABLE_FORMAT` string format Parameters ---------- cur conn """ for table in DROP_TABLES: cur.execute(DROP_TABLE_FORMAT.format(table=table)) conn.commit()
class DatabaseClass: ################ def __init__(self): self.initialized = False self.connectString = None ################## def __del__(self): self.close() ######################################## def connect(self, connectString=None): if connectString is None: from pgcredentials import connectString self.connectString = connectString self.setup() ################ def setup(self): if self.initialized: return if not self.connectString: raise ValueError( 'No connection string set, call ' '"connection(connectString)" first.') from psycopg2.extras import DictConnection self.connection = DictConnection(self.connectString) self.initialized = True ################ def close(self): if not self.initialized: return self.connection.close() self.connection = None self.initialized = False ############################## def query(self, query, *args): self.setup() cursor = self.connection.cursor() cursor.execute(query, args) return(CursorHelper(cursor)) ########################## def queryone(self, *args): '''Like query(), but if you are expecting only one result. Either returns None if there were no results returned, or the row. ''' try: ret = self.query(*args)[0] except IndexError: return(None) return(ret) ################# def commit(self): self.setup() self.connection.commit() ################### def rollback(self): self.setup() self.connection.rollback()
class DatabaseClass: ################ def __init__(self): self.initialized = False self.connectString = None ################## def __del__(self): self.close() ######################################## def connect(self, connectString = None): if connectString == None: from pgcredentials import connectString self.connectString = connectString self.setup() ################ def setup(self): if self.initialized: return if not self.connectString: raise ValueError('No connection string set, call ' '"connection(connectString)" first.') from psycopg2.extras import DictConnection self.connection = DictConnection(self.connectString) self.initialized = True ################ def close(self): if not self.initialized: return self.connection.close() self.connection = None self.initialized = False ############################## def query(self, query, *args): self.setup() cursor = self.connection.cursor() cursor.execute(query, args) return(CursorHelper(cursor)) ########################## def queryone(self, *args): '''Like query(), but if you are expecting only one result. Either returns None if there were no results returned, or the row. ''' try: ret = self.query(*args)[0] except IndexError: return(None) return(ret) ############################################### def insert(self, table, dict = None, **kwargs): '''Insert a row into the specified table, using the keyword arguments or dictionary elements as the fields. If a dictionary is specified with keys matching kwargs, then the dictionary takes precedence. For example: insert('users', name = 'Sean', uid = 10, password = '******') will run the SQL: INSERT INTO users ( name, uid, password ) VALUES ( 'Sean', 10, 'xyzzy') ''' if dict is not None: kwargs.update(dict) values = kwargs.values() cmd = ('INSERT INTO %s ( %s ) VALUES ( %s )' % ( table, ','.join(kwargs.keys()), ','.join(['%s'] * len(values)), )) self.query(cmd, *values) ################# def commit(self): self.setup() self.connection.commit() ################### def rollback(self): self.setup() self.connection.rollback()