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)