Python Flask framework and database connection tutorial, pythonflask framework

Python Flask framework and database connection tutorial, pythonflask framework

Run the Python script using the command line

In this section, we will write some simple database management scripts. Before that, let's review how to execute the Python script through the command line.

If the operating system of Linux or OS X requires the permission to execute the script. For example:

chmod a+x script.py

This script has a command line pointing to the interpreter. After the script grants the execution permission, it can be executed through the command line, like this:

./script.py <arguments>

However, this does not work on Windows systems. You must provide the Python interpreter as a required parameter, for example:

Copy codeThe Code is as follows: flask/Scripts/python script. py <arguments>

To avoid errors in the Python interpreter path input, you can add your folder microoblog/flask/Scripts to the system path to ensure that the Python interpreter is displayed normally.

From now on, the statements on Linux/OS X are concise. If you are using a Windows system, remember to convert the statement.
 
Use the database in Flask

We will use the Flask-SQLAlchemy extension to manage databases. Provided by the SQLAlchemy project, it has encapsulated a plug-in for relational object ORM ing (ORM.

ORMs allows database programs to represent and use SQL statements as objects. Object-oriented operations are converted into database commands by ORM. This means that you do not need to use SQL statements, so that Flask-SQLAlchemy can execute SQL statements for us.
 
Migration

Most database tutorials cover how to create and use a database, but they do not fully solve the database update problem when the application expands. In general, you will delete the old database, and then create a new database to achieve the update effect, thus losing all the data. If the data is difficult to create, we have to write the script for import and export.

Fortunately, we have a better solution.

We can now use SQLAlchemy-migrate for database migration updates. Although it increases the burden on Database startup, it is worth a small cost, after all, we don't have to worry about manual database migration.

After learning the theory, let's get started!

Configuration

Our applet uses the sqlite database. Sqlite is the best choice for small program databases. It can be stored in a single file.

Add a new configuration item (fileconfig. py) to our configuration file ):
 

import osbasedir = os.path.abspath(os.path.dirname(__file__)) SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'app.db')SQLALCHEMY_MIGRATE_REPO = os.path.join(basedir, 'db_repository')

SQLALCHEMY_DATABASE_URI is a required extension of the Flask-SQLAlchemy. This is the path of our database file.

SQLALCHEMY_MIGRATE_REPO is a folder used to store SQLAlchemy-migrate database files.

Finally, you also need to initialize the database when initializing the application. Here is the upgraded init file (fileapp/_ init ):
 

from flask import Flaskfrom flask.ext.sqlalchemy import SQLAlchemy app = Flask(__name__)app.config.from_object('config')db = SQLAlchemy(app) from app import views, models

Note that the generated script has been changed to two places. Now we start to create the adb object of the database and reference the new module. Write this module immediately.

Database Model

The data stored in the database is mapped to objects in some classes through the database model layer. The ORM layer maps the class objects to the corresponding fields in the database.

Let's create a model mapped to users. Using the www SQL Designer tool, we have created an icon representing the users table:

The id field is usually used as the primary key in all models. Each user in the database has a specified Unique id value. Fortunately, these are all automatic and we only need to provide an id field.

The nickname and email fields are defined as the string type, and their length has been specified, which can save the database storage space.

The role field is defined as the integer type, which is used to identify whether users is admins or other types.

Now we have clarified the structure of the users table. The next step is to convert it into encoding (fileapp/models. py ):
 

from app import db ROLE_USER = 0ROLE_ADMIN = 1 class User(db.Model):  id = db.Column(db.Integer, primary_key = True)  nickname = db.Column(db.String(64), index = True, unique = True)  email = db.Column(db.String(120), index = True, unique = True)  role = db.Column(db.SmallInteger, default = ROLE_USER)   def __repr__(self):    return '<User %r>' % (self.nickname)

The User class defines the fields we just created as class variables. The field uses the db. Column class to create an instance. The field type is used as a parameter. Other optional parameters are also provided. For example, the parameter that identifies the uniqueness of a field and the index.

The _ repr _ method tells Python how to print class objects for debugging.

Create a database

Put the configuration and model in the correct directory. Now we create a database file. The SQLAlchemy-migrate package comes with the command line tool and APIs to create a database. This method can be easily updated later. However, I think this command line tool is awkward, so I wrote a python script to call the migrated APIs.

Here is a script for creating a database (filedb_create.py ):
 

#!flask/bin/pythonfrom migrate.versioning import apifrom config import SQLALCHEMY_DATABASE_URIfrom config import SQLALCHEMY_MIGRATE_REPOfrom app import dbimport os.pathdb.create_all()if not os.path.exists(SQLALCHEMY_MIGRATE_REPO):  api.create(SQLALCHEMY_MIGRATE_REPO, 'database repository')  api.version_control(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)else:  api.version_control(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO, api.version(SQLALCHEMY_MIGRATE_REPO))

Note that this script is completely generic and all application path names are read from the configuration file. When you use it in your own project, you can copy the script to your app's directory for normal use.

To create a database, you only need to run the following command (note that windows is slightly different ):

./db_create.py

After running this command, you create a new app. db file. This is an empty sqlite database that supports migration, and a db_repository directory with several files will be generated. This is where SQLAlchemy-migrate stores database files, note that if the database already exists, it will not be regenerated. This will help us to automatically create a database after it is lost ..

First migration

Since we have already defined the model and associated it with the database, next we will try to perform a migration to change the structure of the application database for the first time, this will help us change from an empty database to a database that can store users information.

For a migration task, I use another Python helper script (filedb_migrate.py ):
 

#!flask/bin/pythonimport impfrom migrate.versioning import apifrom app import dbfrom config import SQLALCHEMY_DATABASE_URIfrom config import SQLALCHEMY_MIGRATE_REPOmigration = SQLALCHEMY_MIGRATE_REPO + '/versions/%03d_migration.py' % (api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO) + 1)tmp_module = imp.new_module('old_model')old_model = api.create_model(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)exec old_model in tmp_module.__dict__script = api.make_update_script_for_model(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO, tmp_module.meta, db.metadata)open(migration, "wt").write(script)a = api.upgrade(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)print 'New migration saved as ' + migrationprint 'Current database version: ' + str(api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO))

This script looks very complicated. Actually, there are not many things to do. SQLAlchemy-migrate compares the database structure (from app. db File Read) and models structure (from app/models. to create a migration task, the difference between the two will be recorded as a migration script in the migration library, the Migration script knows how to apply or cancel a migration, therefore, it can easily upgrade or downgrade a database format.

Although I did not encounter any problems when using the above script to automatically generate the migration, sometimes it is really difficult to decide what the old format and new format of the database have changed. To make SQLAlchemy-migrate easier to determine database changes, I never rename existing fields, restrict the addition and deletion of models and fields, or modify the types of existing fields. I always check whether the generated migration script is correct.

Don't talk about it. You must back up the database before trying to migrate it. Do not run the script used for the first time on the database used for production. Run the script on the database used for development first.

Proceed and record our migration:

./db_migrate.py

The script prints the following information:

New migration saved as db_repository/versions/001_migration.py Current database version: 1

The script information shows the location where the migration script is stored and the version number of the current database. The version number of the empty database is 0. When the users information is imported, the version number is changed to 1.

Database Upgrade and rollback

Now you may want to know why we need to do additional work for database migration record.

Imagine that you have an application on the development machine, and a duplicate application is running on the server.

For example, in the next version of your product, your models layer is modified, for example, a new table is added. If the file is not migrated, you need to solve the problem of modifying the database format on both the development machine and the server. This will be a huge workload.

If you already have a database that supports migration, when you release a new application version to the production server, you only need to record the new migration records, copy the migration script to your production server, and then run a simple application to change the script. You can use the following Python script (filedb_upgrade.py) to upgrade the database ):
 

#!flask/bin/pythonfrom migrate.versioning import apifrom config import SQLALCHEMY_DATABASE_URIfrom config import SQLALCHEMY_MIGRATE_REPOapi.upgrade(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)print 'Current database version: ' + str(api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO))

When you run the above script, the database will be upgraded to the latest version, and the changed information will be stored in the database through the script.

This is not a common method to roll back the database to the old format, but SQLAlchemy-migrate is also well supported (filedb_downgrade.py) Just in case ):
 

#!flask/bin/pythonfrom migrate.versioning import apifrom config import SQLALCHEMY_DATABASE_URIfrom config import SQLALCHEMY_MIGRATE_REPOv = api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)api.downgrade(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO, v - 1)print 'Current database version: ' + str(api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO))

This script rolls back one version of the database. You can roll back multiple versions by running multiple times.

Database Association

Relational databases are best at storing relationships between data. If a user writes a microblog, the user's information is stored in the users table, and the microblog is stored in the post table. The most effective way to record who writes a microblog is to establish an association between two pieces of data.

Once the relationship table between the user and Weibo is created, two query methods are available .. The most trivial one is that when you see a microblog, you want to know which user wrote it. The more complicated one is reverse query. If you know a user, you want to know all the Weibo posts he has written. Flask-SQLAlchemy will help us with the two methods of query.

Let's expand the data to store Weibo information so that we can see the corresponding relationship. Let's go back to the database design tool we used to create a posts Table:

The posts table contains a required id, body of the Weibo content, and a timestamp. There is nothing new, but the user_id field is worth explaining.

We want to establish associations between users and their weibo posts. This method is to add a field containing the user id to identify the Weibo posts written by users. This id is called a foreign key. Our database design tool also shows the connection to the table as a foreign key and id field. This association is called One-to-Multiple Association, that is, one user can write multiple articles.

 

Let's modify models to respond to these changes (app/models. py ):
 

from app import db ROLE_USER = 0ROLE_ADMIN = 1 class User(db.Model):  id = db.Column(db.Integer, primary_key = True)  nickname = db.Column(db.String(64), unique = True)  email = db.Column(db.String(120), unique = True)  role = db.Column(db.SmallInteger, default = ROLE_USER)  posts = db.relationship('Post', backref = 'author', lazy = 'dynamic')   def __repr__(self):    return '<User %r>' % (self.nickname) class Post(db.Model):  id = db.Column(db.Integer, primary_key = True)  body = db.Column(db.String(140))  timestamp = db.Column(db.DateTime)  user_id = db.Column(db.Integer, db.ForeignKey('user.id'))   def __repr__(self):    return '<Post %r>' % (self.body)

We added a Post class that indicates the microblog written by the user. The user_id field is initialized and specified as a foreign key in the Post class, therefore, Flask-SQLAlchemy will know that this field will be associated with the user.

Note that a new field named posts is added to the User class, which is defined as a db. relationship field, which is not an existing field in the database, so it is not in our database chart. For a one-to-multiple correlated db. relationship field, you must define it on one side. Based on this association, we can get the user's Weibo list. The first parameter of db. relationship indicates the Class Name of the "dependencies" side. The backref parameter defines a field that refers the object of the "category" class back to the "one" object. In our case, we can use psot. author to get the User instance and create a microblog. If you cannot understand it, don't worry. We will explain it through an example later in the article.

Let's use another migration file to record this change. Run the following script:

./db_migrate.py

After running the script, the following output is displayed:

New migration saved as db_repository/versions/002_migration.py Current database version: 2

We do not need to use an independent migration file every time to record small changes in the database model layer. A migration file usually only records changes in a released version. Next, we need to understand how the migration system works.

Application practices

We have spent a lot of time on database definition, but we still don't see how it works, because our application does not have any data-related coding, next we will use our new database in the Python interpreter.

Start Python. In Linux or OS X:

Copy codeThe Code is as follows: flask/bin/python

Windows:

Copy codeThe Code is as follows: flask/Scripts/python

Enter the following information at the Python command line prompt:

>>> from app import db, models >>>

In this way, our database module and models are loaded into the memory.

Let's create a new user:
 

>>> u = models.User(nickname='john', email='john@email.com', role=models.ROLE_USER)>>> db.session.add(u)>>> db.session.commit()>>>

Change the database in the same session environment. Multiple modifications can be accumulated in one session and finally called by a db. session. the commit command also guarantees atomicity. If an error occurs in the session, db. session. rollback () is called to roll back the database to the previous state. If neither commit nor rollback is called, The system rolls back the session by default. Sessions ensures database data consistency.
 

Let's add another user:
 

>>> u = models.User(nickname='susan', email='susan@email.com', role=models.ROLE_USER)>>> db.session.add(u)>>> db.session.commit()>>>

Now we can query the user information:
 

>>> users = models.User.query.all()>>> print users[<User u'john'>, <User u'susan'>]>>> for u in users:...   print u.id,u.nickname...1 john2 susan>>>

Here we use the query function, which can be used in all model classes. Note how IDs are automatically generated.

There is another way to query. If we know the user id, we can use the following method to find the user information:
 

>>> u = models.User.query.get(1)>>> print u<User u'john'>>>>

Now let's add a microblog:
 

>>> import datetime>>> u = models.User.query.get(1)>>> p = models.Post(body='my first post!', timestamp=datetime.datetime.utcnow(), author=u)>>> db.session.add(p)>>> db.session.commit()

In this case, we set the time to UTC time zone. All the time stored in the database will be in UTC format. Users may write Weibo around the world, so we need to use a unified time unit. In future tutorials, we will learn how to use these time points in your local time zone.

You may notice that we didn't set the user_id field in the Post class. Instead, we saved the user object to the author field. The auhtor field is a virtual field added through Flask-SQLAlchemy to establish an association. We have defined this name before. For details, refer to the backref parameter in db. relationship in model. Through this information, the ORM layer can know how to obtain user_id.

To complete this session, let's take a look at more available database queries:
 

# get all posts from a user>>> u = models.User.query.get(1)>>> print u<User u'john'>>>> posts = u.posts.all()>>> print posts[<Post u'my first post!'>] # obtain author of each post>>> for p in posts:...   print p.id,p.author.nickname,p.body...1 john my first post! # a user that has no posts>>> u = models.User.query.get(2)>>> print u<User u'susan'>>>> print u.posts.all()[] # get all users in reverse alphabetical order>>> print models.User.query.order_by('nickname desc').all()[<User u'susan'>, <User u'john'>]>>>

To learn more about database query options, the best way is to read the Flask-SQLAlchemy documentation.

Before closing the session, we can delete the previously created Test Users and articles. In the following section, we can start with a clean database:
 

>>> users = models.User.query.all()>>> for u in users:...   db.session.delete(u)...>>> posts = models.Post.query.all()>>> for p in posts:...   db.session.delete(p)...>>> db.session.commit()>>>

Conclusion

As a beginner, we have learned about the basic operations of the database, but we have not associated the database with the program. In the next chapter, you can log on to the system to Practice Database Operations.

At the same time, if you haven't started writing programs, you need to download the current file microblog-0.4.zip. Note that the zip file does not include the database, but there is already a storage script. Use db_create.py to create a new database and use db_upgrade.py to upgrade your database to the latest version.