Flask, a popular Python web framework, is often used in conjunction with SQLAlchemy, a powerful Object-Relational Mapping (ORM) library, to interact with databases in a more intuitive and efficient way. In this article, we'll delve into the world of Flask SQLAlchemy queries, exploring how to perform common database operations using Flask-SQLAlchemy, an extension that binds SQLAlchemy with Flask.
Setting Up Flask-SQLAlchemy
Before we start querying databases, we need to set up Flask-SQLAlchemy in our Flask application. Here's a simple example:
```python from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db' db = SQLAlchemy(app) ```
Defining Models
SQLAlchemy models are classes that represent the tables in your database. Here's how you might define a simple User model:
```python
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
def __repr__(self):
return f' To create a new record, you can use the `db.session.add()` and `db.session.commit()` methods:Basic CRUD Operations
Create
```python new_user = User(username='john', email='john@example.com') db.session.add(new_user) db.session.commit() ```
Read
To retrieve records, you can use the query methods provided by SQLAlchemy:
- db.session.query(): Returns a query object that can be filtered, ordered, etc.
- db.session.query.all(): Returns a list of all matching records.
- db.session.query.first(): Returns the first matching record.
- db.session.query.get(): Returns the record with the given ID.
For example, to get all users:
```python users = db.session.query(User).all() ```
Update
To update a record, you can simply modify its attributes and then call `db.session.commit()`:
```python user = User.query.get(1) user.email = 'new_email@example.com' db.session.commit() ```
Delete
To delete a record, you can use the `db.session.delete()` method:
```python user = User.query.get(1) db.session.delete(user) db.session.commit() ```
Filtering and Sorting Queries
SQLAlchemy allows you to filter and sort your queries using the query object's methods. Here are a few examples:
- .filter(): Filters records based on a condition.
- .order_by(): Sorts records based on one or more columns.
- .limit(): Limits the number of records returned.
- .offset(): Skips a certain number of records before returning the rest.
For example, to get all users ordered by their usernames:
```python users = db.session.query(User).order_by(User.username).all() ```
To get the first 5 users who have the letter 'a' in their usernames:
```python users = db.session.query(User).filter(User.username.like('%a%')).limit(5).all() ```
Joining Tables
SQLAlchemy also supports joining tables, allowing you to retrieve data from multiple tables in a single query. Here's an example:
```python from flask_sqlalchemy import SQLAlchemy from sqlalchemy.orm import relationship class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(80), unique=True, nullable=False) email = db.Column(db.String(120), unique=True, nullable=False) posts = relationship('Post', backref='author', lazy='dynamic') class Post(db.Model): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(100), nullable=False) content = db.Column(db.Text, nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) # To get all posts with their authors: posts = db.session.query(Post).join(User).all() ```
Conclusion
Flask-SQLAlchemy provides a powerful and intuitive way to interact with databases in Flask applications. Whether you're creating, reading, updating, or deleting records, or performing more complex queries, SQLAlchemy has you covered. With its extensive documentation and active community, you'll find that SQLAlchemy is a valuable tool in your Flask development toolbox.
