Easy Python CGI web database?
October 12, 2022 5:54 PM
I know I've seen projects like this for Perl, and they probably exist for PHP (PHPMyAdmin is only so-so), but: is there an open source Python project somewhere that will let me set it up on my shared hosting and quickly configure a front end to a MySQL database? I want to be able to add/modify/delete/query/sort, make the input form compact and helpful, and show listings in a multi-line/custom/pretty format.
I got a baby "Hello World" Python cgi script working, and I've been vaguely looking at using Flask, although I don't know how well that will work for this purpose. Django won't work on the shared server (and seems bigger than what I want).
I also found the SQLAlchemy module, but I haven't looked at it in depth.
Advice?
What I want to make is kind of similar to a recipe database -- it will multiple tables.
I got a baby "Hello World" Python cgi script working, and I've been vaguely looking at using Flask, although I don't know how well that will work for this purpose. Django won't work on the shared server (and seems bigger than what I want).
I also found the SQLAlchemy module, but I haven't looked at it in depth.
Advice?
What I want to make is kind of similar to a recipe database -- it will multiple tables.
Note, though, that Flask won't let you quickly create a front-end for your database like PHPMyAdmin does, depending on how you define "quickly." You'll have to do a fair amount of coding to make a useful interface to your database with Flask.
For something a bit more like PHPMyAdmin using Flask, the Flask-Admin library can work in conjunction with Flask to reduce the amount of coding you have to do to build an admin interface to a database.
Also, you could look for existing open-source recipe-database applications built with Flask, whether to adapt or just learn from. Some examples from a quick search: 1, 2, 3.
posted by whatnotever at 9:19 PM on October 12, 2022
For something a bit more like PHPMyAdmin using Flask, the Flask-Admin library can work in conjunction with Flask to reduce the amount of coding you have to do to build an admin interface to a database.
Also, you could look for existing open-source recipe-database applications built with Flask, whether to adapt or just learn from. Some examples from a quick search: 1, 2, 3.
posted by whatnotever at 9:19 PM on October 12, 2022
Not sure if this is helpful to your project, but Dash/Plotly is a pretty straightforward way to make web-facing python apps. They have a page on connecting a dash app to an SQL database. Dash apps can quite easily be run on a heroku server (thus avoiding having to set up your own server and web server).
posted by pjenks at 3:58 AM on October 13, 2022
posted by pjenks at 3:58 AM on October 13, 2022
Flask would definitely work for this purpose, but IMHO Flask is a pretty complex framework with a significant learning curve. A similar but simpler framework is Bottle. I've used that in combination with Peewee to implement a system that has been in use at our institution for > 1 year. (Please feel free to MeMail me for specifics.)
That said, the vast majority of tutorials and solutions you will find for this situation are Flask-based. For example just Googling around, I found this tutorial and example – I don't know anything about it but just offer it as an example of complete tutorials that are easy to find. There are fewer for alternatives like Bottle, and it might be easier to just bite the bullet and use Flask. (And doing that may have benefits down the line for other projects.)
A challenge with any system like this is going to be hosting it and (possibly) securing it. Is this going to be exposed to the world, or on a private server behind a firewall? If the former, it's worth considering the security implications, and it's best to think about them before you start implementing something, because affects what you implement and how you do it.
posted by StrawberryPie at 7:17 AM on October 13, 2022
That said, the vast majority of tutorials and solutions you will find for this situation are Flask-based. For example just Googling around, I found this tutorial and example – I don't know anything about it but just offer it as an example of complete tutorials that are easy to find. There are fewer for alternatives like Bottle, and it might be easier to just bite the bullet and use Flask. (And doing that may have benefits down the line for other projects.)
A challenge with any system like this is going to be hosting it and (possibly) securing it. Is this going to be exposed to the world, or on a private server behind a firewall? If the former, it's worth considering the security implications, and it's best to think about them before you start implementing something, because affects what you implement and how you do it.
posted by StrawberryPie at 7:17 AM on October 13, 2022
Hey StrawberryPie, OK if I ask a couple of follow-up questions?
- Can you tell me a couple of things one can do with Flask and not Bottle? I see generalizations but I don't know if the difference in complexity affects, for example, my ability to control form layout; multi-page forms; ability to shoehorn in relational aspects of database queries; create PDFs; manage user accounts with different functions available; etc.
- Security: public, exposed to the world, mainly for searching. Some accounts (I hope) that will be able to add/edit/delete records.
posted by amtho at 9:52 AM on October 13, 2022
- Can you tell me a couple of things one can do with Flask and not Bottle? I see generalizations but I don't know if the difference in complexity affects, for example, my ability to control form layout; multi-page forms; ability to shoehorn in relational aspects of database queries; create PDFs; manage user accounts with different functions available; etc.
- Security: public, exposed to the world, mainly for searching. Some accounts (I hope) that will be able to add/edit/delete records.
posted by amtho at 9:52 AM on October 13, 2022
Bottle is a smaller and simpler framework, so IMHO easier to figure out, but it is also used by fewer people. Compared to Flask, there are fewer off-the-shelf modules, fewer tutorials and examples, etc. The list of available Bottle plugins is shorter than, for example, the Awesome Flask list, which, depending on what you end up needing, may or may not be a consideration.
With respect to security, adding logins and exposing this to the world really raises the complexity and risk. A ton of things come into consideration. Basic things like how is it hosted, how secure is the server, is the server proof against a myriad of hacking scenarios, etc. – before you even get to the Flask & database parts. This probably tips the balance towards Flask, because there are modules for login and security already available and there will be more tutorials about how to secure the system.
I like Miguel Grinberg's Flask tutorials and book and would start there for a project like this.
posted by StrawberryPie at 12:02 PM on October 14, 2022
With respect to security, adding logins and exposing this to the world really raises the complexity and risk. A ton of things come into consideration. Basic things like how is it hosted, how secure is the server, is the server proof against a myriad of hacking scenarios, etc. – before you even get to the Flask & database parts. This probably tips the balance towards Flask, because there are modules for login and security already available and there will be more tutorials about how to secure the system.
I like Miguel Grinberg's Flask tutorials and book and would start there for a project like this.
posted by StrawberryPie at 12:02 PM on October 14, 2022
It sounds like you want quite a bit more than a Python equivalent to PHPMyAdmin, which is what I assumed from your initial question.
Flask will be able to do all this I think with various add-ons but, acknowledging that I'm biased because I usually work with Django, by the time you've finished it might have been better to use Django from the start. My experience with more-than-very-basic Flask projects might have been unfortunate, but they can end up quite disorganised - because they're made of lots of small pieces added together however the developer wants. And you have to hope all the add-ons you use work well together.
Whereas Django has a lot more built in - database stuff; ORM (object relational mapping - models that abstract away the underlying database details); users, groups, authentication, permissions; admin screens (a step up in niceness from phpmyadmin); etc. But it is a lot more complicated to start with so, despite that, you might be happier beginning with Flask for your first such project.
posted by fabius at 5:11 AM on October 15, 2022
Flask will be able to do all this I think with various add-ons but, acknowledging that I'm biased because I usually work with Django, by the time you've finished it might have been better to use Django from the start. My experience with more-than-very-basic Flask projects might have been unfortunate, but they can end up quite disorganised - because they're made of lots of small pieces added together however the developer wants. And you have to hope all the add-ons you use work well together.
Whereas Django has a lot more built in - database stuff; ORM (object relational mapping - models that abstract away the underlying database details); users, groups, authentication, permissions; admin screens (a step up in niceness from phpmyadmin); etc. But it is a lot more complicated to start with so, despite that, you might be happier beginning with Flask for your first such project.
posted by fabius at 5:11 AM on October 15, 2022
This thread is closed to new comments.
Why not? Flask is designed for exactly this sort of thing. If you need concurrency, the standard thing to do is to drop nginx in front of it. Python did support CGI but it's deprecated.
Be sure that you're taking steps to prevent SQL Injection like using parameterized queries.
posted by Candleman at 9:04 PM on October 12, 2022