Managing Databases with Flask-sqlalchemy

SQLAlchemy is a powerful relational database framework that supports a variety of database backgrounds. Provides a high-level ORM, as well as lower functionality that uses database native SQL.

Installing Flask-sqlalchemy

&emsp&emsp first, activate the VIRTUALENV virtual environment and use the command in the same sibling directory as the virtual Environment installation directory

venv\Scripts\activate

Occurs before the command line virtualenv indicates that the activation was successful

&emsp&emsp Then, install Flask-sqlalchemy

pip install flask-sqlalchemy

Configuring the SQLite Database

from flask.ext.sqlalchemy import SQLAlchemybasedir = os.path.abspath(os.path.dirname(__file__))app = Flask(__name__)app.config[‘SQLALCHEMY_DATABASE_URI‘] =‘sqlite:///‘ + os.path.join(basedir, ‘data.sqlite‘)app.config[‘SQLALCHEMY_COMMIT_ON_TEARDOWN‘] = Truedb = SQLAlchemy(app)

dbObject: Is an instance of the SQLAlchemy class that represents the database used by the program, and also obtains the Flask-sqlalchemy
All the features provided.
SQLALCHEMY_DATABASE_URI: The database URL used by the program
SQLALCHEMY_COMMIT_ON_TEARDOWN: When set to True, changes in the database are automatically committed at the end of each request

Defining the Model

In ORM, the model is typically a Python class, and the properties in the class correspond to the columns in the database table.

class User(db.Model):    __tablename__ = ‘users‘    id = db.Column(db.Integer, primary_key=True)    username = db.Column(db.String(64), unique=True, index=True)    def __repr__(self):    return ‘<User %r>‘ % self.username

__tablename__: Defines the name of the table used in the database. If tablenameis not defined, Flask-sqlalchemy will use a default name.
The rest of the class variables are properties of the model and are defined as db. An instance of the column class. Db. The first parameter of the column class constructor is the type of database column and model property.

Manipulating Database Creation Tables

Let Flask-sqlalchemy create the database based on the model class. The method is to use the db.create_all() function:

(venv) $ python hello.py shell>>> from hello import db>>> db.create_all()

Looking at the program directory, you will find a new file named Data.sqlite. If a database table already exists in the database, Db.create_all () does not recreate or update the table. This feature can be inconvenient if you want to apply changes to an existing database after modifying the model . The rough way to do this is to delete and re-use the data before it is >>> db.drop_all() >>> db.create_all() deleted. So using Flask-Migrate plug-ins, this plugin makes a lightweight wrapper for the database migration Framework Alembic and integrates it into flask-script, all of which are done through flask-script commands. The next step is to install and use the flask-migrate.

Inquire

模型名.query.all()Take all records in the corresponding table:

>>> User.query.al[<User u‘john‘>]

You can view the fields of the table by looking at the native statement

>>> str(User.query.filter_by(username=‘john‘))‘SELECT users.id AS users_id, users.email AS users_email, users.username AS users_username, users.role_id AS users_role_id, users.password_hash AS users_password_hash, users.confirmed AS users_confirmed \nFROM users \nWHERE users.username = ?‘

You can see that the model user corresponds to the table in the Users field, ID, email, username, role_id, etc.

Modify

First fetch the data and then modify and then save the changes back to the table

>>> User.query.get(1)<User u‘john‘>>>> u=User.query.get(1)  #取数据>>> print u.email[email protected]>>> u.email=‘[email protected]‘  #修改数据>>> db.session.add(u)     #添加>>> db.session.commit()  #提交修改>>> u.email                     #查看修改后数据u‘[email protected]‘

Delete

>>> db.session.delete(u)>>> db.session.commit()

Insert Row

>>> u = User(email=‘[email protected]‘, username=‘john‘, password=‘cat‘)>>> db.session.add(u)>>> db.session.commit()

Managing Databases with Flask-sqlalchemy