Connecting Flask to a database using Flask-SQLAlchemy is a straightforward process. Here's a step-by-step guide on how to do it:
First, you'll need to install Flask and Flask-SQLAlchemy:
pip install Flask Flask-SQLAlchemy
Now, let's set up a basic Flask application and configure Flask-SQLAlchemy to connect to a SQLite database:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# Configuration for SQLite database
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
db = SQLAlchemy(app)
@app.route('/')
def index():
return 'Hello, World!'
if __name__ == '__main__':
app.run(debug=True)
In the above code:
SQLALCHEMY_DATABASE_URI configuration key. sqlite:///site.db means that a SQLite database named site.db will be created in the current directory.SQLAlchemy with our Flask app.Before we can interact with the database, we need to define a model. A model represents a table in the database:
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"User('{self.username}', '{self.email}')"
This model defines a User table with an id, username, and email column.
Before running the app for the first time, we need to create the actual database and tables. You can do this with the following commands:
from your_flask_file import db db.create_all()
Run this script once to initialize your SQLite database. This will generate the site.db file with the User table in it.
With Flask-SQLAlchemy set up, you can easily perform CRUD (Create, Read, Update, Delete) operations. Here's a quick overview:
Create:
new_user = User(username='John', email='[email protected]') db.session.add(new_user) db.session.commit()
Read:
users = User.query.all() # Get all users user = User.query.filter_by(username='John').first() # Get the first user named 'John'
Update:
user = User.query.filter_by(username='John').first() user.email = '[email protected]' db.session.commit()
Delete:
user = User.query.filter_by(username='John').first() db.session.delete(user) db.session.commit()
Remember to always commit your changes to the database with db.session.commit() after making any alterations.
And that's it! You now have a basic Flask application connected to a SQLite database using Flask-SQLAlchemy. If you wish to connect to other databases like PostgreSQL or MySQL, you just need to modify the SQLALCHEMY_DATABASE_URI configuration and possibly install the required database drivers.
To query a column with "not equals" in Flask-SQLAlchemy, you can use the .filter() method along with the != operator. Here's an example of how to perform a "not equals" query on a column:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///your_database.db' # Replace with your database URI
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
# Query users where the username is not equal to 'admin'
users = User.query.filter(User.username != 'admin').all()
for user in users:
print(user.username)
In this example:
We define a Flask application and configure it to use SQLAlchemy with a SQLite database.
We define a User model with an id and username column.
We use the filter() method on the User model to create a query. Inside the filter() method, we specify the condition User.username != 'admin' to filter out users whose username is not equal to 'admin'.
We execute the query with .all() to retrieve all matching records.
We iterate over the result set and print the usernames of users who do not have 'admin' as their username.
You can adapt this example to your specific Flask-SQLAlchemy project by replacing the database URI and column names as needed.
To update a row's information in a database using Flask-SQLAlchemy, you can follow these steps:
Import the necessary modules:
Make sure you have Flask, Flask-SQLAlchemy, and SQLAlchemy installed. If not, you can install them using pip:
pip install Flask Flask-SQLAlchemy
Then, import the required modules in your Flask application:
from flask import Flask, request, jsonify from flask_sqlalchemy import SQLAlchemy
Initialize your Flask application and configure the SQLAlchemy database:
app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///your_database.db' # Change to your database URL db = SQLAlchemy(app)
Define your database model:
Create a SQLAlchemy model that corresponds to the table you want to update. Define the fields you want to modify.
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 __init__(self, username, email):
self.username = username
self.email = email
Create a route to handle the update:
Create a route in your Flask application that handles the update. This route should receive the updated data and the identifier for the row you want to update (e.g., the user's ID).
@app.route('/update_user/<int:user_id>', methods=['PUT'])
def update_user(user_id):
user = User.query.get(user_id)
if not user:
return jsonify({'message': 'User not found'}), 404
data = request.get_json()
user.username = data['username']
user.email = data['email']
db.session.commit()
return jsonify({'message': 'User updated successfully'}), 200
Run the Flask application:
In your main script, make sure to run the Flask application:
if __name__ == '__main__':
app.run(debug=True)
Now, when you send a PUT request to the /update_user/<user_id> endpoint with the updated user data in JSON format, the corresponding user's information will be updated in the database. Don't forget to handle errors and edge cases according to your specific requirements.