def get_data_from_db(query: str, conn: psycopg2.extensions.connection,
cur: psycopg2.extensions.cursor, df: pd.DataFrame,
col_names: List[str]) -> pd.DataFrame:
try:
cur.execute(query)
while True:
# Fetch the next 100 rows
query_results = cur.fetchmany(100)
# If an empty list is returned, then we've reached the end of the results
if query_results == list():
break
# Create a list of dictionaries where each dictionary represents a single row
results_mapped = [{
col_names[i]: row[i]
for i in range(len(col_names))
} for row in query_results]
# Append the fetched rows to the DataFrame
df = df.append(results_mapped, ignore_index=True)
return df
except Exception as error:
print(f"{type(error).__name__}: {error}")
print("Query:", cur.query)
conn.rollback()
def lookup_venue_id(cur: psycopg2.extensions.cursor, name):
cur.execute("SELECT id FROM venues WHERE name = %s", (name, ))
rows = cur.fetchmany(2)
if len(rows) > 1:
raise ValueError("Found more than 1 venue with name {}".format(name))
elif len(rows) == 0:
raise ValueError("Didn't find venue with name {}".format(name))
else:
return rows[0][0]
