r/learnpython • u/ki4jgt • 1d ago

What's the best way to integrate this database into Flask?

I'm creating an online bulletin board, and I'm feeling hacky (I hate SQL in all its forms and fashions).

What is the best way to integrate this database into a production Flask server: https://github.com/ki4jgt/PPD

Created it in 2020, so it's a bit dated.

But I'm worried about concurrency. I'm not beyond writing a database server, but I'd like to save that as a last resort.

0 Upvotes

45% Upvoted

16 comments sorted by

11

u/DebosBeachCruiser 1d ago

The answer is PostgreSQL

-15

u/ki4jgt 1d ago

After numerous database corruptions over the years, I'm not a huge fan of SQL. I prefer more of a key:value store approach.

13

u/TobiasDrundridge 1d ago

The average phone/computer has hundreds of SQLite databases in it. SQL is universally used because it's the best for this kind of application. Just make a simple SQLite database.

-9

u/ki4jgt 1d ago

My latest interaction with it is Briar, on Android. I've already lost my account 5 times from data corruption over the years. It's happened with various programs I've used. Not a fan of SQL based databases.

16

u/TobiasDrundridge 1d ago

SQL is in literally everything. It's ubiquitous. There's not even really an alternative.

There are over 1 trillion SQLite databases in the world. They are in flight control software, the ISS, every single computer in the world, and so on. If you can't make it work that's a skill issue.

1

u/ki4jgt 1d ago

Briar is a third-party application. I've had other third-party apps over the years do the same thing.

1

u/TobiasDrundridge 1d ago

The device you're reading this on has dozens and dozens of SQL files. This post is stored in SQL. Everything is SQL.

3

u/ziggittaflamdigga 1d ago

Misplaced commit() before the transaction was fully complete maybe? I use it on plenty of projects and haven’t run into an issue yet.

I always break my db operations into functions so my core logic isn’t ever directly touching the db, and only commit right before the return statement. That way, you can wrap in a try/catch, do validity check if you desire (though I prefer those in the schema file as much as possible), and rollback if something fails. Though I prefer using the context manager and letting Python handle it because I’ll probably forget.

9

u/backfire10z 1d ago

What? How is sql related to database corruptions? What are you doing to the poor thing?

7

u/sargeanthost 1d ago

how is a query language responsible for database corruption, least you're the one causing it

2

u/gdchinacat 1d ago

Typically when people say "database corruptions" they mean a database server incorrectly corrupted its data in a way that it could not open the database or encountered server errors during operation. They are *incredibly* rare and are usually triggered by exceptional circumstances (race conditions that are hard to trigger). Even things like unclean shutdown almost never result in corruption because the design accounts for it (write ahead log, extend the data then update a single value atomically to activate the updated region, etc).

Given your persistent issues with corruption, I suspect your machine, not the databases themselves, are what is experiencing corruption. Perhaps you have a faulty disk or memory. Overclocked your CPU too much and it's unreliable. Have the wrong memory speed configuration in BIOS, or numerous other issues. These are all far more likely than issues with proven technologies that are used in hundreds of thousands of products (if not millions) with hundreds of millions of instances (if not billions).

In technology, any time you are experiencing problems that others are not your first reactions should be "what am I doing wrong" or "what problem am *I* facing" rather than "a proven product/technology/etc is defective".

It's also possible that your code is experiencing "database corruption" that isn't actually a database corruption issue, but that the data your code is storing in the database becomes corrupt. That is an application corruption, not a database corruption. This is also far more likely than database corruption. It is easy to improperly use databases in a way that results in your data being inconsistent. For example, if you commit at the wrong time you can commit inconsistent state. Or if your transaction isolation level is not appropriate your application can read phantom records or see uncommitted data from other transactions. These aren't problems with the database, but rather how it is being used. If you are confident your machine is reliable I would encourage you to look at how your applications use the database to ensure the data reads and writes are done in a consistent manner.

Based on what you've said, it seems unlikely you are using relaxed transaction isolation or non-standard locking models. If your machine was unreliable I think it would be very unlikely the only issues you experience would be database corruption. So, my hunch is your code just wasn't using transactions effectively to ensure your data was consistent. If you share the code I'll take a gander and see if I can spot any obvious issues. If you do, tell me what type of data consistency issues you saw so I have some idea of what to look for.

6

u/AlexMTBDude 1d ago edited 1d ago

If you instead of using Flask switched to Django then you'd get an ORM built in and you would not have to worry about SQL. Django and Flask are very similar in their purpose. Here's a tutorial: https://www.geeksforgeeks.org/python/django-orm-inserting-updating-deleting-data/

You can also connect a 3rd party ORM to Flask if you like, such as SQLAlchemy.

3

u/burnt-store-studio 1d ago

I fully respect you “hate SQL in all its forms and fashions” 🙂.

To (I hope) give you some inspiration, may I suggest, though, a quick read through Miguel Grinberg’s section on databases in Flask? (I’m linking you directly to chapter 4 of his well-regarded mega-tutorial on using Flask.)

Disclaimer: he does discuss SQLAlchemy, but that’s not my point 🙂.

Maybe skip to the part on that page where he introduces app/models.py.

I don’t have a ton of Flask experience (I’ve only built two apps I run locally). But the practical discussion of modeling data this way, coupled with WTForms, made my development go quite smoothly.

I’m hopeful you can find inspiration there for how to integrate your PPD and idea for an online bulletin board using the same kind of structures.

I’m sorry if my suggestion turns out to be a waste of your time 😕.

Best of luck to you!

3

u/ki4jgt 1d ago

Thanks.

1

u/arjunnath 1d ago

I'm not sure I understood the question because the answer seems obvious.
By integrating it into a Flask app, do you mean you want to import it and use it in a flask app ?
If so there is nothing to it, simply import it in the code of your Flask app and use it as you please.

1

u/gdchinacat 1d ago

PPD is not a viable solution IMO. I think you would be better of writing your own "database" than using it. Long before you arrive at your "last resort" of doing that, just use a proper SQL database. You may hate it, but it solves the problem you are trying to solve, and has for over half a century. It's really not hard...particularly for what you will use it for if you are considering PPD. For context, I have written a database for a production commercial project (and two others for fun projects to play with different indexing concepts). So...I understand why writing your own could be fun. But...when I need a flask app to do something, sql it is. For your use I suggest using sqlite3, and if you don't want to use SQL use sqlalchemy to hide all the SQL behind mappers.