I have developed a web application with a backend in python for enterprise clients to manage their products.

For this I have about 10 text files which are extracted daily from their ERP to our servers and have over 1 Million rows and ca 10 columns each.

Goal is for the user to select a material and get quickly (<1s) all the info related to the specific material.

I have no previous knowledge of SQL or any database for that matter. I've never had a use case where things could not be solved in PC memory. But the feeling is that this amount of data has to be better organized, therefore the need for a database.

Now the use case:

  • The info for a specific material has to be searched in all 10 tables

  • There will be a maximum of ca. 10 users active concurrently

  • Requests from front-end will come as JSON, response will be JSON as well
  • The database should be opensource and python libraries should exist
  • The database should be lightweight on resources, such that it doesn't need a very costly server (ideally below 1 GB of RAM)
  • The database will NOT have to scale to 1000nds of concurrent users
  • The database should be very easy to learn for a total noob in databases of any kind (no idea of schemas, queries etc.)
  • The database should be among the 15 most used worldwide so in the future we can find developers which are familiar with it

Done research so far: I have done the usual 3 hours of web search (otherwise I would not be posting this). My conclusions so far:

  • Traditionally, most people working in databases use SQL databases. Popular ones are MySQL and PostgreSQL with PostgerSQL established as a standard in the Python community

  • There has been a trend towards NoSQL such as MongoDB or Redis. Redis is quick as in memory, MongoDB is easier to learn

  • In python there is already SQLite, but it is advised against running in production Mentioned here

So to summarize there are different types and instances for different applications. It largely also depends on use case you have and what you have done previously. I got extremely different advice on the web and would appreciate your recommendation for my specific use case. I fear going down a rabbit-hole of learning one complicated database only having to switch later. Also, I don't want something that is overkill for my case. I usually prefer light and simple (i.e. Flask) to heavy but complete (i.e. Django).

  • FWIW, the basic level of SQL that it sounds like you would need for what you are trying to do here is not too difficult to learn (you can think of it as learning another programming language even, because that's really what SQL is). II can't comment on whether or not it really is easier to learn to use any alternatives though. Irrespective of that, you might want to look at some kind of middleware library for Python that will let you use arbitrary backends of your choice, as that will let you switch out the backend easier if the need arises. – Austin Hemmelgarn Oct 25 '18 at 19:00
  • If these ten files have the same columns, you do no need ten tables, only one table is needed. – Basil Bourque Oct 26 '18 at 6:46
  • Thanks for your advice. @BasilBourque I need the ten tables as they don't have the same columns. Often they have to be joined by a different unique identifier each time. – Si Mon Oct 31 '18 at 19:14
  • @AustinHemmelgarn Good idea with the middleware library. Do you have an example/preferred one? I am a bit sceptical if you can just switch the database then, especially as document stores/RMDBs are very different apparently – Si Mon Oct 31 '18 at 19:14
  • I don't have any suggestions on what library to use. I've heard a lot of good things about Pandas, but it doesn't really seem to fit what you want (it's very much oriented towards numerical analysis). As far as switching though, it depends more on how you use the features the middleware library provides, and how well the library abstracts those from the underlying implementation. – Austin Hemmelgarn Oct 31 '18 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.