[Question] How conexion Flet with PostgresSQL and reports

[avilca]

Question

Hello, I have a database in postgres and I would like to connect.
1.- Does anyone know how to connect postgres with Flet?
2.- I want to make graphical reports (Dashboard) can it be done in desktop mode or is it necessary to switch to web mode with flet?

Hola, tengo una base de datos en postgres y me gustaría conectarme.
1.- ¿Alguien sabe cómo conectar postgres con Flet?
2.- Quiero hacer informes gráficos (Dashboard) ¿se puede hacer en modo escritorio o es necesario cambiar al modo web con flet?

Code sample

No response

Error message

No response

------------------------------------------------------

• I have searched for answers to my question both in the issues and in previous discussions.

[vindevoy]

Flet is frontend, a database is a backend.

Use a function or a class to retrieve your data. I would suggest that you use SQLAlchemy or Pandas to get your data from Postgres.
If you convert your results to a list, you can easily put this in a dataframe.

For instance, below is code to fetch data from vega_datasets. You should get your data from your db here. I'm using pandas here ... You can see how I go from a dataframe which is pandas, to a tuple of lists for example.

from vega_datasets import data

_data_countries = None


def _read_data():
    global _data_countries

    if _data_countries is None:
        _data_countries = data.countries()
        _data_countries = _data_countries.astype({"year": "int32", "life_expect": "int32"})

    return _data_countries


def life_expectancy(country: str):
    df = _read_data()
    df = df.loc[df.country == country]

    return df.year.tolist(), df.life_expect.tolist()


def all_country_names():
    return _read_data().country.unique().tolist()


def selectable_countries(selected_countries: list[str]):
    return [c for c in all_country_names() if c not in selected_countries]

Here you have a sample on how I get the data above into a plotly graph

def life_expectancy_chart(selected_countries: list[str]):
    _logger.debug(f"Creating graph with {len(selected_countries)} countries: {', '.join(selected_countries)}")

    data = []

    for c in sorted(selected_countries):
        years, expectancy = life_expectancy(country=c)

        data.append(
            go.Scatter(x=years, y=expectancy, name=c, mode="lines+markers")
        )

    fig = ce.fig_create_with_defaults(data, title_x="Year", title_y="Life Expectancy (age)")

    return fig

There are some of my functions to make the graph:

def fig_create_with_defaults(data: list, title_x: str, title_y: str):
    fig = go.Figure(data)

    fig = fig_set_margin(fig)
    fig = fig_set_x_axis(fig, title=title_x)
    fig = fig_set_y_axis(fig, title=title_y)

    fig.update_layout(dict(plot_bgcolor="rgba(0,0,0,0)"))
    fig.update_layout(dict(paper_bgcolor="rgba(0,0,0,0)"))

    return fig


def fig_set_margin(figure: Figure, top: int = 0, right: int = 0, bottom: int = 0, left: int = 0):
    margin = dict(l=left, r=right, t=top, b=bottom)

    figure.update_layout(margin=margin)

    return figure


def fig_set_x_axis(figure: Figure, title: str):
    settings = dict(gridcolor="rgba(192,192,192,255)", title=title)

    figure.update_layout(xaxis=settings)

    return figure


def fig_set_y_axis(figure: Figure, title: str):
    settings = dict(gridcolor="rgba(192,192,192,255)", title=title)

    figure.update_layout(yaxis=settings)

    return figure

You would include the above in a page with:

PlotlyChart(figure=figure, expand=True)

The following code uses pandas to read postgres:

import pandas as pd
import psycopg2 as pg

engine = pg.connect("dbname='my_db_name' user='pguser' host='127.0.0.1' port='15432' password='pgpassword'")
df = pd.read_sql('select * from Stat_Table', con=engine)

My function to create a datatable from the column names and rows is this:

def datatable(column_names: list[str], rows: list[dict] = None):
    if rows is None:
        rows = []

    columns = [ft.DataColumn(ft.Text(c)) for c in column_names]

    return ft.DataTable(columns=columns, rows=rows)

Works in both desktop as web version. I can't show you that because I'm only using it in a web version with FastAPI, which I run via uvicorn that I'm running in.