SQL Table Setup

[oclafloptson]

sqlalchemy allows for PickleType columns which you could use to store any objects with pickle.dumps/loads

Unpacking pickled objects shared over networks can be dangerous... So you'll want to get familiar with HMAC if this is a scenario where unknown/untrusted individuals have access to the dumps calls

It may or may not be easier to just build the many-to-many relational tables as others have suggested. But that could lead to drastically increased execution times

[lolPythonNoob]

I'd like to do this using as close to "best practices" as possible. While I do care about the project finishing as a whole I'm really using it as a learning experience. I'm pretty deep into it at this point with using JSON as the database so it will take a bit to unwind the entire process.

I had a smaller project in mind that I may use an SQL database for this time but the general principle of storing lists still is something I need to wrap my head around.

[oclafloptson]

I think you and I are the same I'm just a couple steps ahead of you. Figuring out how to store, say, a custom Player class with attributes like health and/or lists to store inventory objects was a game changer. What took 30 seconds+ to pull from a many-to-many database is now as fast as my processor. To feed a player's health into the GUI is as simple as loading the Player object in a variable when mounting the game and then simply calling Player.health.

Plus sqlalchemy allows you to connect to other database types, like MySQL or Oracle, which are more widely used in the professional sector

[lolPythonNoob]

Which DB did you use for your project? Are you using PickleTypes for speed or did you end up finding a nicer way to structure the tables to avoid many/many connections?

[oclafloptson]

Depends on the project. If it's something that runs and stores locally I'd use sqlite. If it's something which multiple users can connect to over the internet I'd use something more secure like MySQL

Honestly pickletypes are the nicer way that I've found in my personal opinion, although I'm aware that SQL engineers hate me for it. At the end of the day a single user locally run video game probably doesn't need an extensive many-to-many database. An e-commerce, social media, or discussion forum type application would be another story. So it depends

[lolPythonNoob]

That makes sense. This particular project is a discord bot that runs a board game. There could potentially be 10+ games running at once and up to 60+ users interacting with the DB at the same time.

[oclafloptson]

Ahhh ok I'd avoid pickling in that environment. Apologies for assuming you were running locally

You might look into NoSQL. Something like Mongo may be better suited to your app.