How to synchronize two sqlite database files in a python application?

Question

Python

I have a tkinter application that will be installed on several PCs (workstations). The application is a window prepared for a specific specialist (engineer, mechanic, repair zone foreman). All of them work with one file (common) sqlite database - that is, they enter and display information through this application. Moreover, they must work with it simultaneously throughout the working day. Only now, from many requests to the same database from different PCs, errors arise and inevitably there will be errors. My solution is this: leave a copy of the database file on each PC so that a separate specific user can access it; hang a shared file in the organization's local network; it remains only to synchronize the PC nodes with a common node on the LAN.

Actually, the question is: how to synchronize nodes on the PC with a common node on the local area?

Answer 1

Make a normal database server instead of SQLite. Take MariaDB, or Postgre, or whatever.
Because otherwise you still have to resolve record conflicts. For example, in table X, one user added an A record with id=10, and another independently added a B record with id=10. One will have to be renumbered. And if they also have related records in other tables?

Answer 2

sqlite is not a database, it is a library that provides an sql interface when working with a file. And multiple editing access to the same file has not yet been implemented in any operating system.
Therefore, you need to remake the software to work with a real database. Or write a service yourself that will work with sqlite alone, accepting requests from different clients.

Answer 3

Everything is described in the documentation
https://www.sqlite.org/whentouse.html
In theory, it should work fine at low loads, but firstly they write about possible brakes, and about the possible destruction of the database.
The ultimate way is to put the database client server and rewrite the program.
A less ultimatum is to create a lock file and check the lock before changing the data. The file is simply 0 length.

FILE* f = fopen("myfile.txt", "w");
int result = flock(fileno(f)), LOCK_EX);

// . . . .
// Locked file in use 
// . . . .
int release = flock(f, LOCK_UN);  // Unlock the file . . .