Skip to case study

Dataset query system

Individual academic project, 2025

My role
Designed and built the whole application
Stack
Python, TCP sockets, pickle, PySide6, PostgreSQL, pandas, matplotlib, folium
Data
Los Angeles arrests from 2020 on (Kaggle)

Logged-in users query a dataset of Los Angeles arrests that stays on the server, from a desktop app that talks to it over TCP sockets. A separate server window lets the operator watch clients and send them messages.

Problem

Querying data without handing out the dataset

The dataset and the analysis code live on the server, and clients ask it for specific statistics and charts. That meant the app needed a clear network protocol, a server that handles several clients at once, authentication, a moderator view, and desktop interfaces on both sides that stay responsive.

Architecture

Separate interface, protocol, and processing

ClientPySide6 desktop applicationLogin, parameterized queries, results, messages, and history
↕ TCP
ServerThreaded client handlersOne thread per connection. Messages are pickled Python objects with a length prefix, gzipped above 1 MB
↓
Data layerPostgreSQL and pandasPostgreSQL holds users and query history. A pool of four workers runs the pandas queries with a timeout

Delivered

A client app and a server app

  • Built registration and login, and a separate PySide6 server window for the moderator, with tabs for server control, messages, clients, and statistics.
  • Built four parameterized queries: arrests by area and date with an age filter, how a charge type changes over time, arrests by descent and sex, and arrest hotspots within a radius drawn on a folium map.
  • Let the moderator see who is connected, each client's query history, which queries are popular, and the live log, and send a message to everyone or to one client.
  • Used worker threads and Qt signals so networking and large query results don't block the interface.
  • Moved storage from SQLite to PostgreSQL in Docker, with a connection pool, query timeouts, and memory limits.

Lesson

Concurrency is part of interface design

The query logic itself wasn't the hard constraint. Keeping network traffic, analysis work, and GUI updates apart was. A correct result doesn't help much if the window freezes while the data comes in or a plot is being generated.