def insert_execute_batch(connection, beers: Iterator[Dict[str, Any]]) -> None: with connection.cursor() as cursor: create_staging_table(cursor) all_beers = [{ **beer, 'first_brewed': parse_first_brewed(beer['first_brewed']), 'volume': beer['volume']['value'], } for beer in beers] psycopg2.extras.execute_batch( cursor, """ INSERT INTO staging_beers VALUES ( %(id)s, %(name)s, %(tagline)s, %(first_brewed)s, %(description)s, %(image_url)s, %(abv)s, %(ibu)s, %(target_fg)s, %(target_og)s, %(ebc)s, %(srm)s, %(ph)s, %(attenuation_level)s, %(brewers_tips)s, %(contributed_by)s, %(volume)s ); """, all_beers)
def copy_stringio(connection, beers: Iterator[Dict[str, Any]]) -> None: with connection.cursor() as cursor: create_staging_table(cursor) csv_file_like_object = io.StringIO() for beer in beers: csv_file_like_object.write('|'.join( map(clean_csv_value, ( beer['id'], beer['name'], beer['tagline'], parse_first_brewed(beer['first_brewed']), beer['description'], beer['image_url'], beer['abv'], beer['ibu'], beer['target_fg'], beer['target_og'], beer['ebc'], beer['srm'], beer['ph'], beer['attenuation_level'], beer['contributed_by'], beer['brewers_tips'], beer['volume']['value'], ))) + '\n') csv_file_like_object.seek(0) cursor.copy_from(csv_file_like_object, 'staging_beers', sep='|')
def insert_execute_values_iterator( connection, beers: Iterator[Dict[str, Any]], page_size: int = 100, ) -> None: with connection.cursor() as cursor: create_staging_table(cursor) psycopg2.extras.execute_values( cursor, """ INSERT INTO staging_beers VALUES %s; """, (( beer['id'], beer['name'], beer['tagline'], parse_first_brewed(beer['first_brewed']), beer['description'], beer['image_url'], beer['abv'], beer['ibu'], beer['target_fg'], beer['target_og'], beer['ebc'], beer['srm'], beer['ph'], beer['attenuation_level'], beer['brewers_tips'], beer['contributed_by'], beer['volume']['value'], ) for beer in beers), page_size=page_size)
def copy_string_iterator(connection, beers: Iterator[Dict[str, Any]], size: int = 8192) -> None: with connection.cursor() as cursor: create_staging_table(cursor) beers_string_iterator = StringIteratorIO(('|'.join( map(clean_csv_value, ( beer['id'], beer['name'], beer['tagline'], parse_first_brewed(beer['first_brewed']).isoformat(), beer['description'], beer['image_url'], beer['abv'], beer['ibu'], beer['target_fg'], beer['target_og'], beer['ebc'], beer['srm'], beer['ph'], beer['attenuation_level'], beer['brewers_tips'], beer['contributed_by'], beer['volume']['value'], ))) + '\n' for beer in beers)) cursor.copy_from(beers_string_iterator, 'staging_beers', sep='|', size=size)
def retrieve_and_dump_data(conn, url): """ Retrieve json from the given url and dump it into the database. Return the data for further processing. """ cursor = conn.cursor() data = json.loads(urllib.urlopen(url).read()) # dump every piece of data into the database for i in range(0, len(data["resultSets"])): table_schema = "staging_" + data["resource"].lower() + "_2agg" table_name = data["resultSets"][i]["name"].lower() column_names = data["resultSets"][i]["headers"] records = data["resultSets"][i]["rowSet"] # Create schema, table, and insert records db.create_staging_schema(cursor,table_schema) db.create_staging_table(cursor,table_schema,table_name,column_names) db.insert_records(cursor,table_schema,table_name,column_names,records) conn.commit() cursor.close() return data
import executeBatchFromIteratorWithPageSize import executeValues import executeValuesFromIterator import executeValuesFromIteratorWithPageSize import pgcopy import pgcopy_csvLike import pgcopyIterWithBuffer print( "Starting to fetch the data in bulk to remove the network latency in our \ testing") beers = list(iter_beers_from_api()) * 100 print("\nFinished fetching data from the API to be created into an inter\n") with connection.cursor() as cursor: create_staging_table(cursor) oneInsertRowsOneByOne.insert_one_by_one(connection, beers) executeMany.insert_executemany(connection, beers) executeManyFromIterator.insert_executemany_iterator(connection, beers) executeBatch.insert_execute_batch(connection, beers) executeBatchFromIterator.insert_execute_batch_iterator(connection, beers) executeBatchFromIteratorWithPageSize.insert_execute_batch_iterator(connection, iter(beers), page_size=1) executeBatchFromIteratorWithPageSize.insert_execute_batch_iterator(