Linking SQL/polars/pandas database with aggrid allowing dynamic editing and replacement of values

[vickorian]

import polars as pl
from nicegui import ui
from bunch  import Bunch
import sqlparse
import psycopg2 as pg


db = 'dbs'
tn = 'clients'

ui.dark_mode(True)
connection_uri = f"postgresql://name:PW@IP:5432/{db}"
query = f"select * from {tn}"
clients = pl.read_database_uri(uri=connection_uri,query=query, engine='adbc')
df = clients
# print(clients)


def handleCellValueChanged(msg):
    zn = msg.args
    print(zn)
    args_ = Bunch(zn)
    qry = SQL_replace_query_creator(tn, args_)
    print(qry)
    conn = pg.connect(connection_uri)
    cur = conn.cursor()
    cur.execute(qry)
    conn.commit()
    cur.close()
    conn.close()


def SQL_replace_query_creator(tn, val):
    table_name = tn

    str_val = []
    for v in val.data:
        if v == val.colId:
            m_val = val.oldValue
            m = v
            str_val.append(f"{m} = '{m_val}'")
        else:
            str_val.append(f"{v} = '{val.data[v]}'")
        
    logic_str = "WHERE " + " AND ".join(str_val)+";"
    set_str = f"SET {val.colId} = '{val.newValue}' "
    update_str = f"UPDATE {table_name} "
    que = update_str + set_str + logic_str
    query = sqlparse.format(que, reindent=True)
    return query


rep_dict = {'lastname':'Last Name','firstname':'First Name','dop':'Date of Purchase','purchases':'Purchases'}
# Get column names from the DataFrame
column_names = df.columns

# Create column definitions dynamically
def create_nice_names(cnames, rd:dict)->list[dict]:
    cdefs = []
    for col in cnames:
        try:
            cdefs.append({'headerName':rd[col], 'flex':1, 'field': col, 'editable':True, 'sortable':True,'filter':True,'wrapText':True,'autoHeight': True})
        except:
            cdefs.append({'headerName':col, 'field': col})
    return cdefs
c_defs = create_nice_names(column_names, rep_dict)
# column_defs = [{'headerName': col, 'field': col} for col in c_defs]

# Convert Polars DataFrame to a list of dictionaries (row data)
row_data = df.to_dicts()
grid = ui.aggrid({
    'columnDefs': c_defs,
    'rowData': row_data,
    'rowSelection': 'multiple', # Optional: Set row selection mode
    'pagination': True,
    'paginationPageSize':25,
    'enableFilter':True,
    # "domLayout": "autoHeight",
    "stopEditingWhenCellsLoseFocus": True,
    'defaultColDef':{
        'cellEditor': "agLargeTextCellEditor",
        'cellEditorPopup': True,
        'cellEditorParams': {
            'useFormatter':True,
            'rows': 15,
            'cols': 50,
        },
        'resizable':True,
        'cellStyle':{
            'color': 'black',
            # 'background-color': '#53c98e'
        },
        # 'headerClass': 'bg-indigo-400'
    }
    
},auto_size_columns=True, theme='balham-dark').classes('w-full').style('height: 500px').on('cellValueChanged', handleCellValueChanged)

ui.run()

So this is a basic example for how to interact with an SQL database and link it with AgGrid. Any edits to AgGrid will automatically be updated to the SQL database by pulling the row data for the edit, and dynamically writing the SQL query. Polars supports writing dataframe queries in the style of SQL, so it should be possible to adapt this pretty easily.

If you are working with a small database it shouldn't take that long, but wrapping this in an async await might help if your database is taking too long and blocking your UI.

This is something people have been bringing up, so i thought it might spark interest.

[falkoschindler]

Thanks for sharing, @vickorian! 😀

[maxrausch]

If you pivot in aggrid UI does it use the pandas back end?