Compared to Django, Flask doesn't have the kind of ORM framework that comes with Django, but we can utilize third-party ORM frameworks to do so, such as the one we've introduced here, SQLAlchemy.
This next note will cover how to use SQLAlchemy to connect to a database, build a model, manipulate tables, and query table data.
0. Module installation
For SQLAlchemy, it also needs to use the pymysql library for database operations, so here we install the following modules:
pip3 install sqlalchemy==2.0.19 pip3 install pymysql==1.1.0
1. Database connection and use
The following is an example of a connection to a database and a simple query using SQLAlchemy:
from sqlalchemy import create_engine, text
engine = create_engine("mysql+pymysql://root:[email protected]:3306/db_test?charset=utf8")
with () as conn:
sql = "select id, name from users"
result = (text(sql))
print(())
# [(1, 'admin'), (2, 'user_1')]
Here, we presuppose that indb_test There is a table under this library namedusers table, and then use the nativeSQL statement performs a query and print operation, which is actually the same as directly using thepymysql There is no difference in how the module operates the database.
If it's an insert or update statement, you'll need to follow it with the() Submit the operation.
2、ORM model establishment and the use of ORM Session
Next, we define an ORM model, which is actually similar to Django's model, but with some syntactic differences:
from import DeclarativeBase
from sqlalchemy import Column, Integer, String, Text, DateTime, func
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50), unique=True, nullable=False, comment="Username")
email = Column(String(120), default="", comment="Mailbox.")
remark = Column(Text, default="", comment="Remarks information")
created_time = Column(DateTime, server_default=(), comment="Creation time")
updated_time = Column(DateTime, server_default=(), onupdate=(), comment="Modified time")
Here, we define aBase class that inherits fromDeclarativeBase，DeclarativeBase is used to define the base class of the ORM model, which provides some convenient functions to make database operations using ORM more simple and intuitive, such as providing some query methods, which we will introduce later.
insofar asUser this oneclass, an analog of theDjango ORM'sSQLAlchemy The ORM model is now complete.
Among them.Integer，String，Text，DateTime These are the fields that correspond to the database.
For the definedColumn(), there are many fields with attributes such asprimary_key primary key.auto_increment Self-adding.default Default.nullable Is it allowed to beNull et al. (and other authors)
Here, created_time and updated_time implement the auto_now_add and auto_now attributes of Django's datetime fields, which are created or modified at the current time.
The definition of an ORM Session is described next, and the following is an example:
from sqlalchemy import create_engine
from import scoped_session, sessionmaker
engine = create_engine("mysql+pymysql://root:[email protected]:3306/db_test?charset=utf8")
db_session = scoped_session(
sessionmaker(
autoflush=False,
bind=engine
)
)
In the above operation, sessionmaker() creates a session factory object through the engine, and then creates a scoped_session object through the scoped_session() function
We can use the db_session defined above to operate on the database directly, or we can instantiate it:
db_session.do_something() // manipulate the database directly with db_session session = db_session() session.db_something() // Manipulate the database with the results of the db_session instantiation.
They are used with different objects, but both functionally provide thread-local session objects that can be safely used in multithreaded applications.
3、Table operation
The following describes how SQLAlchemy manipulates tables.
1. Creation of tables
After defining the engine and Base base classes and the class for the User table, we can create all the defined tables in the following way:
.create_all(bind=engine)
After executing the above statement, the table corresponding to the class User will be created in the database.
You can add this statement above to the startup step of the project, because this operation will create tables that are not in the library, and the existing ones will not be repeated.
2. Modification of the table structure
SQLAlchemy does not support direct modification of table structures in the form of functions, but you can execute native SQL for ALTER TABLE operations.
Or you can delete it by the following delete table operation, and then do the create_all() operation, but in that case, the data of the original table does not exist.
3. Deletion of tables
Deleting a table operates in a similar way to creating it, as follows:
.drop_all(bind=engine)
As a result, all tables that inherit from the Base base class will be deleted.
To perform a deletion of a single table, you can use Table's drop() function:
from sqlalchemy import Table, MetaData meta_data = MetaData() table_name = "users" user_table = Table(table_name, meta_data, autoload_with=engine) user_table.drop(bind=engine)
4、Create table data
1. Creation of a single piece of data
u = User(name="admin", email="120@") db_session.add(u) db_session.commit()
Use add() to add, then commit
2. Creation of multiple data
u1 = User(name="user_1", email="user1@") u2 = User(name="user_2", email="user2@") db_session.add_all([u1, u2]) db_session.commit()
Batch creation uses the add_all() function.
5、Query table data
1. Query data according to the primary key id
user = db_session.get(User, 1)
The returned user is the User object we defined earlier.
2. Conditional queries
Conditional query, there can be two operations, one is where(), one is filter(), which are the same in effect, both act on the conditional query.
For example, if we want to query for data where the name field has a value of "admin" and the id field has a value of 1, and the id, name, email fields are returned, we can use query() to perform field restriction as follows:
query = db_session.query(, , ).filter( == "admin").filter( == 1) query = db_session.query(, , ).filter( == "admin").where( == 1) query = db_session.query(, , ).where( == "admin").filter( == 1) query = db_session.query(, , ).where( == "admin").where( == 1)
If we want one piece of data, we can use first(), and if we want all the data that matches the condition, we can use all()
() # (1, 'admin', '120@') () # [(1, 'admin', '120@')]
In addition, we can assign db_session.query_property() to , and then you can query directly through the User
= db_session.query_property() ( == "admin").filter( == 1)
The object returned by first() or all() is the defined User.
6. Update table data
For ORM model instances that we fetch, such as the data we fetched earlier with the primary key id, or the data we fetch by specifying the model directly without specifying the fields in db_session.query(), we can modify its fields directly and then commit the
user_1 = db_session.get(User, 1) user_1.email = "999@" db_session.add(user_1) user_2 = db_session.query(User).filter( == 2).first() user_2.email = "888@" db_session.add(user_2) db_session.commit()
Or the way we did earlier with the query attribute would work:
( == 1).update({"email": "19283@"})
db_session.commit()
7. Delete table data
1. Deletion of individual records
user = db_session.get(User, 2) db_session.delete(user) db_session.commit()
2. Bulk deletion based on conditions
( == 3).delete() db_session.commit()
Above is a detailed explanation of how to use SQLAlchemy to connect to the database in detail, more information about SQLAlchemy database please pay attention to my other related articles!