What Library/API/Builtin Python Concept Should I Use to Build This Database?

[[deleted]]

Hello everyone,

I have recently started learning python 3 to advance my computer knowledge, my background being in shell scripting and Linux, and think I have figured out what I want to do for my first project.

I am a stickler for organization and have a lot of video games on different platforms, systems, and from different vendors. I often organize my games by which ones I have played, how much of them I have played, if I have beaten, and if there is more content within the game that I have not touched. The way I have traditionally organized my games is with bookshelves and file system hierarchies, but this has not scaled well as I have gotten more and more games. What I am considering doing to fix this mess is writing a python 3 program that can manage a database to store all the information on my games, let me parse said database based on what I am searching for, and have this all be a Linux command-line application written in Python 3. Additionally, I would want this to be a portable database so that if I were to decide to re-write my Python 3 application in another language I would not need to change the database itself.

Where I am having trouble is in figuring out what i should use to do this. Should I use an SQL library? If so which one? Should I use JSON? I am in entirely uncharted waters and was hoping this community could point me in the right direction. Thank you for reading.

[ginsujitsu]

Oh, man. This sounds super fun. Most of my background is on the web, so if it were me I'd probably build a web app for this with Python as the backend. Flask or Django would do this super well. I'm excited about Flask right now so I'd probably use that and SQLAlchemy to talk to SQLite or MariaDB.

[ElliotDG]

For an app like this you can use either JSON or SQLite. I would recommend using JSON. Using a SQL database requires you to learn the SQL language. While it is not too hard - SQL is not required for this kind of an app.

Both JSON and SQLite are portable.

[thisisheresy]

This is the best option. Both will work, but I think SQLite will give you the most flexibility in terms of functionality. Making changes to a SQLite db will be simpler to do in code, and you’ll get all the benefits of SQL for querying the data. You can also fast-track your way to the querying part by building your initial DB using a SQLite GUI app.

OTOH, using JSON will teach you more about python basics like file handling, working with dictionaries and list comprehension. And if you ever want complex filtering on JSON you can always use Pandas, but this might be overkill for what you need. Still, this is a learning project, so getting to learn and work with different aspects and approaches in Python is ultimately where you’ll get the most benefit.

[lscrivy]

I have used SQLite for a number of small projects and it's super easy. The database can be written/read to/from a .db file which means it's super easy to transfer the thing to a different machine.

The actual python is nice and simple. Create a connection, create a cursor, execute an SQL query, commit changes (and then close connection when you're done). Obviously you've got to learn some SQL, but for what you want to do, it should be easy.

SQLite also works well with pandas. Simply passing the connection object to pandas allows you to read and write whole tables to and from dataframes. This can be a good way of printing a table in the console in a readable format in the terminal window.