Flask SQLAlchemy
Placement-ready Online Courses: Your Passport to Excellence - Start Now
In this tutorial, we will learn about flask SQLAlchemy. Let’s start!!
Flask SQLalchemy
Flask is a compact Python web platform that provides helpful tools and functionalities for developing web applications. An SQL toolset called SQLAlchemy offers relational databases fast, effective database access. It offers means of communicating with several database engines, including Sql, MySQL, and PostgreSQL. You can use the SQL features of the database thanks to it. You also get a Data Structure Mapper. It enables you to use basic Py entities and methods to perform queries and manage data. A Flask plugin called Flask-SQLAlchemy makes it simpler to use SQLAlchemy with Flask by giving you tools and techniques for interacting with your databases in your Flask apps through SQLAlchemy.
It can be difficult to carry out CRUD transactions on the database with pure SQL in the Flask Web app. Alternatively, SQLAlchemy, the Python Toolkit, is a potent OR Mapper that gives application developers access to all of SQL’s features and adaptability.
The Flask application now supports SQLAlchemy thanks to the Flask-SQLAlchemy extension.
An ORM tool called Flask SQLAlchemy creates a connection between relational databases’ objects and tables. The mapping between both the two is crucial. The database stores data as Relational tables, which are collections of rows and columns. Python can store data as objects. Without performing any raw SQL queries, the object-relational mapping technique stores Python objects in database tables. In this tutorial session, we’ll use flask-sqlalchemy ORM approaches to build a simple web application.
It can be laborious to use pure SQL in Flask development tools to carry out CRUD tasks on databases. Alternatively, SQLAlchemy, a Python toolkit, is a potent OR Mapper that allows programmers complete access to SQL’s flexibility and capability. The Flask extension known as Flask-SQLAlchemy enables your Flask application to support SQLAlchemy.
Prerequisites for Flask SQLAlchemy
1. Create a local Python 3 programming environment.
2. Working knowledge of fundamental Flask ideas like routes, view functions, and templates.
3. Working knowledge of HTML fundamentals.
4. Fundamental knowledge of MySQL usage.
Let’s use ORM
Object Relation Mapping, or ORM, is an acronym (Object Relationship Mapping). The majority of programming language platforms use objects. Tables are used by the RDBMS server to store data. A technique called object-relational mapping links object parameters to a layer RDBMS table’s structure. Instead of using raw SQL statements, you can carry out CRUD tasks using the ORM API.
Installing Flask SQLAlchemy
In order to build a web application that makes use of flask ORM approaches, we must first install flask sqlalchemy using the pip installer.
$ pip install flask-sqlalchemy
Try importing the module in the Python shell to verify the installation; if it is effective there, it has been successful.
$ from flask_sqlalchemy import SQLAlchemy
Setting the URI for the databases to use and building a Flask application instance.
application = Flask(__name__) application.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students.sqlite3'
Illustration
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
application = Flask(__name__)
application.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///example.sqlite"
database = SQLAlchemy(application)
class User(database.Model):
ID = database.Column(database.Integer, primary_key=True)
UserName = database.Column(database.String, unique=True, nullable=False)
with application.app_context():
database.create_all()
database.session.add(User(UserName="example"))
database.session.commit()
users = database.session.execute(database.select(User)).scalars()
Utilizing Flask SQLAlchemy to build a simple web application
In this tutorial portion, we’ll make a CRUD Python application utilising ORM SQLAlchemy.
app.html
<!DOCTYPE html>
<html>
<body>
<h3>Adding Employees</h3>
<hr/>
{%- for category, message in get_flashed_messages(with_categories = true) %}
<div class = "alert alert-danger">
{{ message }}
</div>
{%- endfor %}
<form action = "{{ request.path }}" method = "post">
<label for = "name">Name of the employee</label><br>
<input type = "text" name = "name" placeholder = "Name of the Employee" /><br>
<label for = "salary">Salary of the employee</label><br>
<input type = "text" name = "salary" placeholder = "Salary of the employee" /><br>
<label for = "age">Age of the employee</label><br>
<textarea name = "age" placeholder = "Age of the employee"></textarea><br>
<label for = "Passcode">Pin</label><br>
<input type = "text" name = "pin" placeholder = "Passcode" /><br>
<input type = "submit" value = "Submit" />
</form>
</body>
</html>
emp_list.html
<!DOCTYPE html>
<html lang = "en">
<head><title>HOME</title></head>
<body>
<h3>
<a href = "{{ url_for('list_employees') }}">System for Managing Employees</a>
</h3>
<hr/>
{%- for message in get_flashed_messages() %}
{{ message }}
{%- endfor %}
<h3>List of Employees</h3>
<table border="2" padding = "5">
<thead>
<tr>
<th>Name</th>
<th>Salary</th>
<th>Age</th>
<th>Passcode</th>
</tr>
</thead>
<tbody>
{% for employee in Employees %}
<tr>
<td>{{ employee.name }}</td>
<td>{{ employee.salary }}</td>
<td>{{ employee.age }}</td>
<td>{{ employee.pin }}</td>
</tr>
{% endfor %}
</tbody>
</table>
<br><br>
<a href="{{ url_for('addEmployee') }}">Append New Employee</a>
</body>
</html>
app.py
from flask import Flask, request, flash, url_for, redirect, render_template
from flask_sqlalchemy import SQLAlchemy
application = Flask(__name__)
application.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///employeess.sqlite3'
application.config['SECRET_KEY'] = "secret key"
database = SQLAlchemy(app)
class Employee_Data(database.Model):
ID = database.Column('employee_id', database.Integer, primary_key = True)
Name = database.Column(database.String(100))
Salary = database.Column(database.Float(50))
Age = database.Column(database.String(200))
Passcode = database.Column(database.String(10))
def __init__(self, Name, Salary, Age,Passcode):
self.Name = Name
self.Salary = Salary
self.Age = Age
self.Passcode = Passcode
@application.route('/')
def emp_lists():
return render_template('emp_list.html', Employees = Employees.query.all() )
@application.route('/add', methods = ['GET', 'POST'])
def addEmployee():
if request.method == 'POST':
if not request.form['name'] or not request.form['salary'] or not request.form['age']:
flash('Please enter data in all the fields', 'error')
else:
employee = Employees(request.form['name'], request.form['salary'],
request.form['age'], request.form['pin'])
database.session.add(employee)
database.session.commit()
flash('Successfully added record')
return redirect(url_for('list_employees'))
return render_template('app.html')
if __name__ == '__main__':
database.create_all()
application.run(debug = True)
Models and Tables
Flask-SQLAlchemy is a Flask module that offers a straightforward interface to SQLAlchemy, a well-liked Python ORM and SQL toolkit. Models and tables that correspond to your database schema can be quickly created using Flask-SQLAlchemy.
Defining Models: Subclassing the db.Model class allows you to create a model. This SQLAlchemy declarative base class builds a database based on the defined attributes.
import sqlalchemy as sa
class User(db.Model):
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String)
email = sa.Column(sa.String, unique=True)
In the aforementioned illustration, we create a User model with a name, email, and id column. The main key is the id column. For ease, we import the Column class as sa even though it originates from SQLAlchemy.
Keep in mind that the __tablename__ property doesn’t require definition. Based on the class name, Flask-SQLAlchemy will automatically create a table name. The database name in this scenario is user.
We employ the create all() method to create the table in the database. A Flask application and a SQLAlchemy entity must first be created:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydb.sqlite'
db = SQLAlchemy(app)
with app.app_context():
db.create_all()
In this example, we build a Flask app and set it up to use the mydb.sqlite SQLite database. Using create all, we create a db object from SQLAlchemy and use it to generate the table ().
Defining Tables: On occasion, we might want to build a database without specifying a model. We can accomplish this by making a db.Table instance. For many-to-many connections, this is helpful.
import sqlalchemy as sa
user_book_m2m = db.Table(
'user_book',
sa.Column('user_id', sa.Integer, sa.ForeignKey('user.id'), primary_key=True),
sa.Column('book_id', sa.Integer, sa.ForeignKey('book.id'), primary_key=True),
)
We establish a many-to-many connection between the User and Book models in this example. User id and book id, which are foreign keys to the id sections of the User and Book models, respectively, are the two columns in the user book m2m table. Both columns are included in the main key as well.
Reflecting Tables: If you already have a database, you can use SQLAlchemy to display the tables. Reading the database structure and producing table objects from it is called reflection.
with app.app_context():
db.reflect()
class User:
__table__ = db.metadata.tables['user']
Using db.reflect, we reflect the tables in this case (). By doing this, table instances that are kept in db.metadata.tables are created. The __table__ attribute can then be used to link the User model to the user table.
Conclusion
An organized collection of data called a database is typically required for online applications. To preserve data files that can be efficiently retrieved and changed, you utilise a database. These conditions might be met by a user creating a new post, erasing an existing post, or deactivating their membership. Your application’s individual features will determine the steps you take to alter data. You might, for instance, not even want users to submit posts without titles.
Your application will now support SQLAlchemy thanks to the Flask SQLAlchemy extension. Creating standard objects and usage patterns for those entities makes it easier to use SQLAlchemy with Flask.
Did you like this article? If Yes, please give DataFlair 5 Stars on Google
