Approach 1
one way to store and retrieve data in a database is to use an Object-Relational Mapping (ORM) library, such as SQLAlchemy, in conjunction with a database library like Flask-SQLAlchemy.
Here is an example of how to use Flask-SQLAlchemy to store and retrieve data in a database:
In this example, we use the SQLAlchemy ORM to define a User model, which has three fields: id, username, and email. We then create a database table for the User model using the create_all() method, and add a new user to the database using the add() and commit() methods. Finally, we retrieve a user from the database using the filter_by() and first() methods.
This is just an example, in a real world scenario you should use environment variables for database URI and use a more robust database such as PostgreSQL or MySQL.
You can also use the Flask-Migrate library to handle database migrations and also use Flask-Admin to administrate the database via UI.
Approach 2
Another approach to store and retrieve data in a MySQL database in a Flask application is to use the PyMySQL library, which is a pure Python MySQL client library. Here is an example of how to use PyMySQL to store and retrieve data in a MySQL database:
In this example, we use the pymysql.connect() function to connect to a MySQL database using the host, user, password, and db parameters. We then create a new cursor object using the connection.cursor() method, which allows us to execute SQL statements. We use the cursor.execute() method to insert a new user into the users table, and the connection.commit() method to save the changes to the database. Finally, we use the cursor.execute() method to retrieve a user from the database, and the cursor.fetchone() method to retrieve the result.
You should always close the connection after use and also use environment variables to store the database credentials.
Please make sure that the SQL statements are properly sanitized to prevent SQL injection attacks.
One Comment