flask-SQLAlchemy connects to the database and performs some basic operations

introduce

Introduction to flask

Flask is a lightweight Python Web framework built on top of the Werkzeug and Jinja2 libraries. It is popular for its simplicity, flexibility, and ease of extension.

The core design concept of Flask is "micro", which means "micro". Compared with other large Web frameworks, Flask only provides the most basic set of functions, such as routing, request and response processing, template rendering, etc., but it also allows developers to add more functions through plug-ins or extensions. This design method makes Flask very suitable for building small Web applications, API services, and prototype verification scenarios.

In addition, Flask also has the following advantages:

Easy to learn: Flask's API design is very simple, easy to grasp and use.
Highly flexible: Flask allows developers to freely choose the plug-ins or libraries that need to be introduced according to actual needs, thus achieving highly flexible customization.
Lightweight and efficient: Flask's core code is small, runs very fast, and can handle high concurrent access.
Rich extension library: Flask has a huge extension library, covering various functions such as database, form verification, user authentication, and caching.

In summary, Flask is a simple, flexible, and extensible Python Web framework for Web application development of all sizes. Its design ideas and philosophy enable developers to get started quickly, develop efficiently, and expand freely according to actual needs.

Introduction to SQLAlchemy

SQLAlchemy is a Python ORM (Object Relational Mapping) tool that provides a way to map relational databases to Python objects. ORM is a programming model that allows us to abstract the underlying SQL queries by using object-oriented syntax to operate relational databases.

SQLAlchemy provides a high-level, Pythonic interface that enables programmers to use Python code for database operations without writing SQL directly. At the same time, it also provides support for multiple relational databases, including MySQL, PostgreSQL, Oracle, Microsoft SQL Server, etc.

SQLAlchemy provides two different APIs: Core API and ORM API. Core API provides low-level SQL expression and query construction, while ORM API provides higher-level object-oriented data access and persistence.

Using SQLAlchemy, developers can easily create and manage database tables, execute complex queries, implement transaction processing, maintain data integrity, and more. It also supports connection pooling, thread safety, automatic rollback and other features to improve application performance and reliability.

In short, SQLAlchemy is a powerful, flexible, and easy-to-use Python ORM framework, and it is one of the indispensable tools for Python developers when dealing with relational databases.

Introduction to Flask-SQLAlchemy

Flask-SQLAlchemy is an extension that provides SQLAlchemy support for Flask applications. By integrating SQLAlchemy into Flask, developers can more easily use the ORM (Object Relational Mapping) model to operate the database.

Flask-SQLAlchemy provides an object-oriented way to deal with databases, users can map database tables by defining Python classes. These classes are usually called "models", and each model corresponds to a database table, where the class attributes correspond to the columns of the table.

Key features of Flask-SQLAlchemy include:

Ease of use: Flask-SQLAlchemy provides a concise, easy-to-understand and learn API, and users can quickly get started and start building database models.
Database migration: Flask-SQLAlchemy supports the integration of Flask-Migrate extensions to realize automatic migration of database structures, so as to facilitate the management of changes in database structures.
Database session management: Flask-SQLAlchemy provides the session management function of SQLAlchemy in the Flask application, and developers can easily perform database operations.
Query optimization: Flask-SQLAlchemy provides a flexible query API that supports multiple query methods and filters, and developers can choose freely according to actual needs.

In summary, Flask-SQLAlchemy is a very powerful, flexible and easy-to-use ORM framework, and it is an indispensable tool for Web application developers using the Flask framework. Using Flask-SQLAlchemy, developers can achieve data persistence more conveniently and efficiently, and at the same time avoid manually writing inefficient and error-prone SQL statements.

connection steps

To use Flask-SQLAlchemy, you need to install Flask and SQLAlchemy and the Flask-SQLAlchemy extension first. They can be installed using the pip command:

pip install flask
pip install sqlalchemy
pip install flask_sqlalchemy

Once installed, you can use Flask-SQLAlchemy as follows:

1. Import the flask_sqlalchemy module in your Flask application and create a SQLAlchemy object:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# database configuration
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'flask_study'
USERNAME = 'root'
PASSWORD = 'root'

DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)

2. Create a database model (aka "table"), define a Python class that inherits from db.Model, and define class attributes as columns. For example, the following code defines a model named User:

class User(db.Model):
    __tablename__ = 'user'
    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)

3. To create a database table, you can use the Flask-Migrate extension for database migration management. For example, you can run the following command to create a migration script:

flask db init #initialization, only needed once
flask db migrate #As long as the above model is changed, this and the next command need to be executed
flask db upgrade

4. Using the database, you can use db.session to perform database operations. For example, the following code will create a new user:

new_user = User(username='xiaogao', email='1443004194@qq.com')
db.session.add(new_user)
db.session.commit()

5. Using the database in the Flask application, you can use the database in the Flask view function to perform data query, update and other operations. For example, the following code queries all users and renders them to an HTML page:

@app.route('/users')
def users():
    all_users = User.query.all()
    return render_template('users.html', users=all_users)

CRUD operations

1. Increase data

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# database configuration
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'flask_study'
USERNAME = 'root'
PASSWORD = 'root'

DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)

# Define the data table class
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    age = db.Column(db.Integer)

# create new user
new_user = User(name='John', age=30)

# Add to session and submit
db.session.add(new_user)
db.session.commit()

2. Query data

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# database configuration
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'flask_study'
USERNAME = 'root'
PASSWORD = 'root'

DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)

# Define the data table class
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    age = db.Column(db.Integer)
#query by id
user=User.query.get(1)#Query the user whose id is 1
    
# query all users
all_users = User.query.all()
#traverse
for user in all_users:
	print(f'{user.id}:{user.name}:{user.age}')
    
# Query a specified user
user = User.query.filter_by(name='John').first()

# Query users whose age is between 20 and 30
users = User.query.filter(User.age >= 20, User.age <= 30).all()

3. Modify data (update data)

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# database configuration
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'flask_study'
USERNAME = 'root'
PASSWORD = 'root'

DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

# Define the data table class
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    age = db.Column(db.Integer)

# Query the user who needs to be modified
user = User.query.filter_by(name='John').first()

# modify user information
user.age = 35

# Submit changes
db.session.commit()

4. Delete data

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# database configuration
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'flask_study'
USERNAME = 'root'
PASSWORD = 'root'

DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

# Define the data table class
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    age = db.Column(db.Integer)

# Query the user to be deleted
user = User.query.filter_by(name='John').first()

# delete users
db.session.delete(user)

# submit delete
db.session.commit()

Foreign key binding and ORM relationship mapping

1. One-to-one relationship

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# database configuration
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'flask_study'
USERNAME = 'root'
PASSWORD = 'root'

DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

# Define the data table class
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    age = db.Column(db.Integer)

class Post(db.Model):
    __tablename__ = 'post'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(50))
    content = db.Column(db.Text)
# The user_id foreign key of the Post table is bound to the id of the User table
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
	
    
# Create posts based on users
user = User(name='John', age=30)
db.session.add(user)
db.session.commit()

post = Post(title='Hello World', content='This is my first post.', user_id=user.id)
db.session.add(post)
db.session.commit()

# Query posts and their corresponding user information
post = Post.query.first()
print(post.title)
print(post.user.name)

The User class contains a posts property that points to all posts related to this user. The Post class contains a user attribute that points to the user who created the post. In this way, relationships can be established between Python objects for easy query and manipulation.

2. One-to-many relationship

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# database configuration
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'flask_study'
USERNAME = 'root'
PASSWORD = 'root'

DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    profile = db.relationship('Profile', backref='user', uselist=False)
    # db.backref
    # 1. When backreferring, if you need to pass some other parameters, then you need to use this function, otherwise you don’t need to use it, just set the name of the backref on the backref parameter of the relationship.
    # 2. uselist=False: When it represents a back reference, it is not a list, but an object.
    

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    content = db.Column(db.Text)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    
user = User(name='John')
post = Post(title='Hello World', content='This is my first post.')
user.posts.append(post)
db.session.add(user)
db.session.commit()

The Post model references the User model via the user_id foreign key. In the User model, we defined a relationship to the Post model using the posts attribute, and created a backreference called user using the backref parameter. This way, we can access all of its post objects from any user object via user.posts.

Tags: Python Flask pip Pycharm

Posted by RockRunner on Wed, 08 Mar 2023 04:11:18 +1030

Programs Wiki

flask-SQLAlchemy connects to the database and performs some basic operations

flask-SQLAlchemy connects to the database and performs some basic operations

introduce

Introduction to flask

Introduction to SQLAlchemy

Introduction to Flask-SQLAlchemy

connection steps

1. Import the flask_sqlalchemy module in your Flask application and create a SQLAlchemy object:

2. Create a database model (aka "table"), define a Python class that inherits from db.Model, and define class attributes as columns. For example, the following code defines a model named User:

3. To create a database table, you can use the Flask-Migrate extension for database migration management. For example, you can run the following command to create a migration script:

4. Using the database, you can use db.session to perform database operations. For example, the following code will create a new user:

5. Using the database in the Flask application, you can use the database in the Flask view function to perform data query, update and other operations. For example, the following code queries all users and renders them to an HTML page:

CRUD operations

1. Increase data

2. Query data

3. Modify data (update data)

4. Delete data

Foreign key binding and ORM relationship mapping

1. One-to-one relationship

2. One-to-many relationship

Popular Tags