Add magic arguments to pass variables into templated queries

[psychemedia]

Generalise how line magic elements are handled and then add support for passing in py variables that can be used in the sql queries as template parameters.

[psychemedia]

Currently, the pglite_df_insert can accept a pandas.Dataframe which it will try insert into a specified table. The datarame value is picked up from the local_ns via local_ns[args.dataframe].

    @line_magic
    @needs_local_scope
    @magic_arguments()
    @argument("-t", "--table", type=str, help="Table name")
    @argument("-d", "--dataframe", type=str, help="Datatframe")
    @argument("-w", "--widget-name", type=str, help="widget variable name")
    def pglite_df_insert(self, line, local_ns=None):
        """Call as: %pglite -d df -t tableName"""
        from pandas import DataFrame
        args = parse_argstring(self.pglite_df_insert, line)
        if args.dataframe not in local_ns:
            raise ValueError(f"DataFrame '{args.dataframe}' not found in the local namespace")

        # Get the DataFrame
        df = local_ns[args.dataframe]

        # Validate the DataFrame
        if not isinstance(df, DataFrame):
            raise TypeError(f"'{args.dataframe}' is not a pandas DataFrame")

        # Generate the SQL statement
        columns = ', '.join(df.columns)
        values = ',\n    '.join(
            [f"({', '.join(repr(value) for value in row)})" for row in df.itertuples(index=False, name=None)]
        )
        sql = f"INSERT INTO {args.table} ({columns})\nVALUES\n{values};"
        return self._run_query(args, sql)