def insert_dataframe(cursor: psycopg2.extensions.cursor,
df: pd.DataFrame,
query: str,
fields: List[str] = None,
batch_mode: bool = True) -> bool:
"""Inserts the contents of a pandas DataFrame into a PostgreSQL database
Args:
cursor (psycopg2.extensions.cursor): The active cursor used to insert the data
df (pd.DataFrame): The pandas DataFrame data container
query (str): The Psycopg2 compliant query to be executed
fields (list[str], optional): The column subset to be considered by df. Defaults to None.
batch_mode (bool, optional): True to use batch mode (optimized for multi-inserts). Defaults to True.
Returns:
bool: True if the process completed properly
"""
current_file_name: str = os.path.basename(__file__)
if cursor is None:
print(current_file_name, '::insert_dataframe no cursor provided.')
return False
if df is None:
print(current_file_name,
'::insert_dataframe no pandas.DataFrame provided.')
return False
if query is None:
print(current_file_name, '::insert_dataframe no query provided.')
return False
try:
# Use the provided fields, if any
data = df if fields is None else df[fields]
if batch_mode:
# Fetch the data and load it into a List of Tuples
data = df.values.tolist()
data = [tuple(row) for row in data]
cursor.executemany(query, data)
else:
for i, row in df.iterrows():
cursor.execute(query, list(row))
except Exception as err:
print(current_file_name,
'::insert_dataframe {error}'.format(error=err))
return False
return True
def exec_hook(cur: psycopg2.extensions.cursor,
batch: Sequence[Dict[str, Any]]) -> None:
cur.executemany(q, batch)
