in-memory sqlite in production with python

Question

I am creating a python system that needs to handle many files. Each of the file has more than 10 thousand lines of text data.

Because DB (like mysql) can not be used in that environment, when file is uploaded by a user, I think I will save all the data of the uploaded file in in-memory-SQLite so that I can use SQL to fetch specific data from there.

Then, when all operations by program are finished, save the processed data in a file. This is the file users will receive from the system.

But some websites say SQLite shouldn't be used in production. But in my case, I just save them temporarily in memory to use SQL for the data. Is there any problem for using SQLite in production even in this scenario?

Edit: The data in in-memory-DB doesn't need to be shared between processes. It just creates tables, process data, then discard all data and tables after saving the processed data in file. I just think saving everything in list makes search difficult and slow. So using SQLite is still a problem?

Firefox is a good example of using sqlite pretty much ubiquitously — Mad Physicist
– Mad Physicist, Commented May 14, 2020 at 7:21
You should also check if this can be done with python dictionaries in memory. Then time your solution and it might be good enough. Since you don't descibe your planned process in detail, it's impossible to ive you good advice here. Also, stack overflow is not for open questions, but to solve specific problems for which you give a reproducable example. This kind of question is more suited for reddit.com/r/learnpython — 576i
– 576i, Commented May 14, 2020 at 8:01
The data of each file has hierarchy (like parent-children), so I want to use SQL to fetch specific data. It might be possible with dict, but it would be difficult. — lechat
– lechat, Commented May 14, 2020 at 8:08

synweap15 · Accepted Answer · 2020-05-14 07:21:43Z

2

SQLite shouldn't be used in production is not a one-for-all rule, it's more of a rule of thumb. Of course there are appliances where one could think of reasonable use of SQLite even in production environments.

However your case doesn't seem to be one of them. While SQLite supports multi-threaded and multi-process environments, it will lock all tables when it opens a write transaction. You need to ask yourself whether this is a problem for your particular case, but if you're uncertain go for "yes, it's a problem for me".

You'd be probably okay with in-memory structures alone, unless there are some details you haven't uncovered.

answered May 14, 2020 at 7:21

synweap15

4696 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roy2012 · Accepted Answer · 2020-05-14 08:02:49Z

0

I'm not familiar with the specific context of your system, but if what you're looking for is a SQL database that is

light
Access is from a single process and a single thread.
If the system crashes in the middle, you have a good way to recover from it (either backing up the last stable version of the database or just create it from scratch).

If you meet all these criteria, using SQLite is production is fine. OSX, for example, uses sqlite for a few purposes (e.g. ./var/db/auth.db).

answered May 14, 2020 at 8:02

Roy2012

12.7k3 gold badges28 silver badges38 bronze badges

Collectives™ on Stack Overflow

in-memory sqlite in production with python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related