I am creating an inventory system in Python for my employer, should I store inventory information in an SQLite database?

Question

I have begun creating an inventory system in Python for my employer. I have the frontend GUI mostly complete and have questions about the backend.

How should I store the information?

The program will be located in a dropbox folder that can be accessed by multiple people at once. Should I use an SQLite database, and upon executing functions such as "add stock", open a connection, execute the change, and close the connection? Will this allow multiple users to have the inventory open at once? Are there better ways to handle this?

There won't be a lot of inventory items. Would it be better to use python objects and object methods to store and manipulate the information?

Answer 1

You can manipulate an sqlite database pretty easily with Python.

https://docs.python.org/3/library/sqlite3.html

import sqlite3 as sql

class SQLManiuplator:
  def __init__(self, fp=None):
    path = fp
    if not fp:
      path = "example.db"
    self.path = path

  def add_item_to_inventorytable(self, item):
    with sql.connect(self.path) as conn: # close automatically
        c = conn.cursor()
        # Use '?' substitution to sanitize inputs per sqlite3 docs
        c.execute('''
        INSERT INTO inventory VALUES (?)''', item)
        c.commit()

  # More CRUD functions

You can built a pretty CRUD api with this pattern and map the operations to your GUI. From a design standpoint, it then falls to you to decide what operations you are willing to expose to your users. For example I don't suppose you will want to make CREATE TABLE available to users so I didn't include that. (But this means you would have to manually create tables and define what columns exist in them.)

You already said that this would be a pretty lightweight inventory system, but it should go without saying that at enterprise scale this solution is a loser.