YouTip

Flask Database Operations In Flask, database operations are an important aspect of building Web applications. Flask provides multiple ways to interact with databases, including directly using SQL and using ORM (Object-Relational Mapping) tools like SQLAlchemy. The following is a detailed explanation of Flask database operations, including basic operations using SQLAlchemy and direct execution of SQL statements. 1. **Using SQLAlchemy**: Define models, configure databases, and perform basic CRUD operations. 2. **Creating and Managing Databases**: Use `db.create_all()` to create tables. 3. **CRUD Operations**: Add, read, update, and delete records. 4. **Query Operations**: Perform basic and complex queries, including sorting and pagination. 5. **Flask-Migrate**: Use Flask-Migrate to manage database migrations. 6. **Executing Raw SQL**: Use raw SQL statements for database operations. ## 1. Using SQLAlchemy SQLAlchemy is a powerful ORM library that simplifies database operations by interacting with database tables through Python objects. Flask-SQLAlchemy is an extension for Flask that integrates SQLAlchemy. ### Install Flask-SQLAlchemy pip install flask-sqlalchemy ### Configure SQLAlchemy app.py file code: ## Example from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask( __name__ ) app.config['SQLALCHEMY_DATABASE_URI']='sqlite:///example.db'# Use SQLite database app.config['SQLALCHEMY_TRACK_MODIFICATIONS']=False db = SQLAlchemy(app) ## 2. Defining Models Models are Python classes that represent database tables, with each model class representing a table in the database. ## Example 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'' db.Model: All model classes need to inherit from db.Model. db.Column: Defines the fields of the model, specifying the field type, whether it is a primary key, whether it is unique, whether it can be null, and other properties. ## 3. Creating and Managing Databases ### Creating Database and Tables After defining models, you can use the methods provided by SQLAlchemy to create databases and tables. with app.app_context(): db.create_all() db.create_all(): Creates tables corresponding to all models defined in the current context. ## 4. Basic CRUD Operations ### Creating Records ## Example @app.route('/add_user') def add_user(): new_user = User(username='john_doe',email='john@example.com') db.session.add(new_user) db.session.commit() return'User added!' db.session.add(new_user): Adds the new user object to the session. db.session.commit(): Commits the transaction and saves the changes to the database. ### Reading Records ## Example @app.route('/get_users') def get_users(): users = User.query.all()# Get all users return'
'.join([f'{user.username} ({user.email})'for user in users]) User.query.all(): Queries all user records. ### Updating Records ## Example @app.route('/update_user/') def update_user(user_id): user= User.query.get(user_id) if user: user.username='new_username' db.session.commit() return'User updated!' return'User not found!' User.query.get(user_id): Queries a single user record by primary key. Update field values and commit the transaction. ### Deleting Records ## Example @app.route('/delete_user/') def delete_user(user_id): user= User.query.get(user_id) if user: db.session.delete(user) db.session.commit() return'User deleted!' return'User not found!' db.session.delete(user): Deletes the user record and commits the transaction. ## 5. Query Operations SQLAlchemy provides rich query functionality, allowing you to perform various query operations through query objects. ### Basic Query users = User.query.filter_by(username='john_doe').all() filter_by(): Filters records based on field values. ### Complex Query from sqlalchemy import or_ users = User.query.filter(or_(User.username == 'john_doe', User.email == 'john@example.com')).all() or_(): Used to execute complex query conditions. ### Sorting and Pagination users = User.query.order_by(User.username).paginate(page=1, per_page=10) order_by(): Sorts by the specified field. paginate(): Paginates the query. ## 6. Using Flask-Migrate for Migrations Flask-Migrate is an extension for database migrations based on Alembic, which helps you manage database version control. ### Install Flask-Migrate pip install flask-migrate ### Configure Flask-Migrate app.py file code: from flask_migrate import Migrate migrate = Migrate(app, db) ### Initialize Migration flask db init ### Create Migration Script flask db migrate -m "Initial migration." ### Apply Migration flask db upgrade * `flask db init`: Initialize the migration environment. * `flask db migrate -m "message"`: Create a migration script. * `flask db upgrade`: Apply migrations to the database. ## 7. Executing Raw SQL Although SQLAlchemy provides ORM functionality, you can also execute raw SQL statements. ## Example @app.route('/raw_sql') def raw_sql(): result = db.session.execute('SELECT * FROM user') return'
'.join([str(row)for row in result]) db.session.execute(): Executes raw SQL queries. ## Connecting and Operating MySQL Database in Flask Connecting to and operating MySQL databases in Flask typically involves using SQLAlchemy or directly using MySQL's Python drivers. The following are detailed steps, including operations using Flask-SQLAlchemy and directly using MySQL's Python drivers. ### 1. Using Flask-SQLAlchemy to Connect to MySQL Flask-SQLAlchemy is an extension for Flask that simplifies the configuration and operation of SQLAlchemy. To connect to MySQL, you need to install Flask-SQLAlchemy and the MySQL driver. Install necessary libraries: pip install flask-sqlalchemy mysqlclient * `flask-sqlalchemy`: Flask's SQLAlchemy extension. * `mysqlclient`: Python driver for MySQL database. ### Configure Flask-SQLAlchemy app.py file code: ## Example from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask( __name__ ) app.config['SQLALCHEMY_DATABASE_URI']='mysql://username:password@localhost/dbname' app.config['SQLALCHEMY_TRACK_MODIFICATIONS']=False db = SQLAlchemy(app) SQLALCHEMY_DATABASE_URI: Sets the database connection URI, with the format `mysql://username:password@localhost/dbname`. * `username`: MySQL username. * `password`: MySQL password. * `localhost`: MySQL host address (usually `localhost` for local). * `dbname`: Database name. ### Define Models and Perform Basic Operations app.py file code: ## Example 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'' @app.route('/') def index(): users = User.query.all() return'
'.join([f'{user.username} ({user.email})'for user in users]) if __name__ =='__main__': with app.app_context(): db.create_all()# Create database tables app.run(debug=True) ### Directly Using MySQL's Python Driver If you choose not to use SQLAlchemy and instead directly use MySQL's Python driver, you can use the mysql-connector-python or PyMySQL library. Install mysql-connector-python: pip install mysql-connector-python Install PyMySQL (if you choose to use PyMySQL): pip install PyMySQL **Using mysql-connector-python to Connect to MySQL** app.py file code: ## Example from flask import Flask, request, jsonify import mysql.connector app = Flask( __name__ ) def get_db_connection(): connection = mysql.connector.connect( host='localhost', user='username', password='password', database='