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(