Write Panda Dataframes to SQL. Each data frame must be identifiable by a trial number
You should have a separate trials table in your database where you cspture the details of each trial. The trials table will have an auto incremented id field.
Before writing your dataframes to your values table, each process inserts a record into the trials table and get the generated auto increment value.
Then use this value to set the trial number column when you dump the frame to your table.
I am trying to write several Panda Dataframes into a SQL database. The dataframes are generated in different processes using the multiprocessing library.
Each dataframe should get its own trial number when it is written into the database. Can I solve this using SQL autoincrement or do I have to create a counter variable in the Python code.
If I use the function pandas.DataFrame.to_sql and set an index as autoincrement, I get a consecutive index for each row.
Here is an example how it should look like




trial number
timestamp
value




1
time1
value1


1
time2
value2


1
time_n
value_n


2
time1
value1


2
time2
value2


2
time3
value3


2
time_n
value_n




I use Python 3.9 and MariaDb as Database. I hope for help. Thanks