I need some help with designing a database. My aim is to persistently store a number of pandas DataFrames in a searchable way, and from what I've read SQLite is perfect for this task. Using sqlalchemy I can already store each DataFrame as a table in a new DataBase:

from sqlalchemy import create_engine
import pandas as pd
  

engine = create_engine("sqlite:////mnt/storage/test.db")


exp1 = pd.read_csv("/mnt/storage/exp1.csv")
exp2 = pd.read_csv("/mnt/storage/exp2.csv")
exp3 = pd.read_csv("/mnt/storage/exp3.csv")
 
exp1.to_sql("exp1", engine, if_exists="replace")
exp2.to_sql("exp2", engine, if_exists="replace")
exp3.to_sql("exp2", engine, if_exists="replace")

But this is too basic. How can I store each DataFrame/experiment with a couple of metadata fields like Name, Date in such a way that later on it's possible to return all experiments conducted by a certain person, or on a specific date?

Assuming each DataFrame/experiment has a column temperature, how could I retrieve all experiments where the mean temperature value is below or above an arbitrary threshold?

I need some help with designing a database. My aim is to persistently store a number of pandas DataFrames in a searchable way, and from what I've read SQLite is perfect for this task.

Each DataFrame contains about a million rows of particle movement data like this:

              z            y            x  frame  particle
0     49.724138    45.642857   813.035714      0         0
3789  14.345679  2820.537500  4245.162500      0         1
3788  10.692308  2819.210526  1646.842105      0         2
3787  34.100000  2817.700000  1375.300000      0         3
3786   8.244898  2819.729167  1047.375000      0         4

Using sqlalchemy I can already store each DataFrame as a table in a new DataBase:

from sqlalchemy import create_engine
import pandas as pd
  

engine = create_engine("sqlite:////mnt/storage/test.db")
 
exp1.to_sql("exp1", engine, if_exists="replace")
exp2.to_sql("exp2", engine, if_exists="replace")
exp3.to_sql("exp3", engine, if_exists="replace")

But this is too basic. How can I store each DataFrame/experiment with a couple of metadata fields like Name, Date in such a way that later on it's possible to return all experiments conducted by a certain person, or on a specific date?

I will add more columns over time. Assuming each DataFrame/experiment has a column velocity, how could I retrieve all experiments where the mean temperature value is below or above an arbitrary threshold?