SelfSync

Explore your database with Python

DuckDB has a first-class Python client. Query your database with SQL and get the results as pandas or Polars DataFrames, ready for plotting, statistics or machine learning.

In a Jupyter notebook, it is the most flexible way to explore your history: mix SQL, charts and notes in one place.

Runs on
macOS, Windows, Linux
Price
Free, open source
Website
duckdb.org

Get started

  1. Install the packages

    Ideally in a virtual environment. JupyterLab is optional but makes exploring much nicer.

    pip install duckdb pandas matplotlib jupyterlab

  2. Open the database

    read_only=True guarantees nothing gets modified.

    import duckdb

con = duckdb.connect("selfsync-db.duckdb", read_only=True)
con.sql("FROM z_fit_session_mesgs LIMIT 5").show()

  3. Get a DataFrame and plot it

    .df() returns a pandas DataFrame (.pl() a Polars one). Here, the kilometres you ran each year, as a bar chart.

    df = con.sql("""
    SELECT year(z_start_time) AS year,
           round(sum(z_total_distance) / 1000) AS km
    FROM z_fit_session_mesgs
    WHERE z_sport = 'running'
    GROUP BY ALL
    ORDER BY year
""").df()

df.plot.bar(x="year", y="km")

Good to know

  • Positions are stored in semicircles: multiply by 180 / 2**31 to get degrees before drawing a map.
  • DuckDB can also query your DataFrames directly: con.sql("FROM df") works on any pandas DataFrame in scope.

Other tools

Wondering what a table or a column holds? Browse the FIT file reference.