2/ Allow scientists to query data with graphical widgets
Once you have established a connection, you can allow scientists to query, manipulate and visualize their own tables of data from PostgreSQL using graphical query builders.See an example below with a table of synthetic barcode counts. Scientists can retrieve counts and barcodes based on date ranges and sample IDs:
Copy
Ask AI
from lplots.widgets.text import w_text_inputfrom lplots.widgets.multiselect import w_multi_selectfrom datetime import datetimestart_date = w_text_input( label="Start Date", default="2023-01-01", appearance={"help_text": "Enter start date in YYYY-MM-DD format"})end_date = w_text_input( label="End Date", default="2023-01-31", appearance={"help_text": "Enter end date in YYYY-MM-DD format"})sample_ids = w_multi_select( label="Sample IDs", options=['SAMPLE_001_A1', 'SAMPLE_001_A2', 'SAMPLE_001_A3'], appearance={"help_text": "Select one or more sample IDs"})barcode_ids = w_multi_select( label="Barcode IDs", options=['BC000001', 'BC000002', 'BC000003'], appearance={"help_text": "Select one or more barcode IDs"})limit = w_text_input( label="Query Limit", default="1000", appearance={"help_text": "Enter the maximum number of rows to return"})# PostgreSQL uses different parameter style (%s instead of %(name)s)base_query = """SELECT sample_id, barcode_id, count, run_id, experiment_dateFROM barcode_countsWHERE experiment_date BETWEEN %s AND %s"""params = [start_date.value, end_date.value]if sample_ids.value: base_query += " AND sample_id = ANY(%s)" params.append(sample_ids.value)if barcode_ids.value: base_query += " AND barcode_id = ANY(%s)" params.append(barcode_ids.value)base_query += """ORDER BY experiment_date, sample_idLIMIT %s"""params.append(int(limit.value))with postgres_connection(PostgresConfig.build()) as conn: df = pd.read_sql(base_query, conn, params=params)df