A tutorial on Python's Flask Framework and database connection _python

Command line run Python script

In this section, we'll write a few simple database management scripts. Before that, let's review how to execute a Python script from the command line.

If Linux or OS X's operating system requires permission to execute scripts. For example:

chmod a+x script.py

The script has a command line that points to using an interpreter. Once the script is given execute permissions, it can be executed from the command line, like this:

./script.py <arguments>

However, doing so on a Windows system does not work, you must provide the Python interpreter as a required parameter, such as:

Copy Code code as follows:

Flask/scripts/python script.py <arguments>

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

From now on, the statement on Linux/os X is concise. If you use Windows system please remember to convert the statement.

using databases in Flask

We will use Flask-sqlalchemy extensions to manage the database. A plug-in for relational object Mapping (ORM), provided by the SQLAlchemy project.

Orms allows database programs to represent and SQL statements in the same way as objects. Object-oriented operations are transformed into database commands by ORM. This means that, without SQL statements, let Flask-sqlalchemy execute SQL statements for us.

Migrate

Most database tutorials cover the creation and use of a database, but do not adequately address the issue of database updates when an application expands. Typically, you will delete the old database and then create a new database to achieve the updated results, so that all the data is lost. If this data is difficult to create, then we have to write a script to import the export.

Luckily, we have a better plan.

We can now use Sqlalchemy-migrate to do the update of the database migration, although it increased the burden of database startup, but this small price is worth it, after all, we do not have to worry about the problem of manually migrating the database.

The theory study completes, we begin!

Configuration

Our applet uses the SQLite database. SQLite is the best choice for a small program database, a database that can be stored as a single file.

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

Import os
basedir = 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 the necessary extension of the flask-sqlalchemy. This is the path to our database file.

Sqlalchemy_migrate_repo is the folder used to store sqlalchemy-migrate database files.

Finally, initializing the application also requires initialization of the database. Here is the upgraded init file (fileapp/__init):

From flask import flask from
flask.ext.sqlalchemy import sqlalchemy
 
app = Flask (__name__)
app.config.from_ Object (' config ')
db = SQLAlchemy (APP)
 
from the app import views, models

Note that the generated script has changed 2 places. We are now starting to create the ADB object for the database, referencing the new module. Write this module right away.

Database model

The data we store in the database is mapped to objects in some classes through the database model layer, and the ORM layer maps to the corresponding fields of the database based on the class object.

Let's create a model that maps to users. Using the WWW SQL Designer tool, we created an icon representing the users table:

The ID field is usually used as a primary key in all models, and each user in the database has a specified unique ID value. Luckily, these are all automatic and we just need to provide an ID field.

The nickname and email fields are defined as string types and their lengths are also specified, which saves database storage space.

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

Now that we have identified the structure of the Users table, the next move to coding will be fairly straightforward (fileapp/models.py):

From app Import db
 
role_user = 0
role_admin = 1
 
class USER (db. Model):
  ID = db. Column (db. Integer, Primary_key = True)
  nickname = db. Column (db. String (+), index = True, unique = true)
  email = db. Column (db. String (in), 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 few fields we have just created as class variables. The field uses DB. The column class creates an instance, the type of the field as a parameter, and some other optional parameters. For example, a parameter that identifies the field uniqueness and index.

The __repr__ method tells Python how to print the class object, which is convenient for us to debug and use.

Creating a Database

Put the configuration and model into the correct directory location, and now we create the database file. The Sqlalchemy-migrate package comes with command-line tools and APIs to create a database that can be easily updated later. But I thought it was a bit awkward to use this command-line tool, so I wrote a Python script to invoke the migrated APIs.

Here's a script to create a database (filedb_create.py):

#!flask/bin/python from
migrate.versioning import APIs from
Config import Sqlalchemy_database_uri
from Config import Sqlalchemy_migrate_repo from
App. Import db
import Os.path
db.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 that all the application path names are read from the configuration file. When you use your own project, you can copy the script to your app's directory and use it normally.

To create a database you only need to run one of the following commands (note that Windows is slightly different):

./db_create.py

After you run this command, you create a new app.db file. This is an empty SQLite database that supports migration, and it also generates a db_repository directory with several files, which is where Sqlalchemy-migrate stores the database files, noting that if the database already exists it will not be regenerated. This will help us to automatically create the existing database once it is lost.

First migration

Now that we've defined model and associated it with the database, let's try a migration that changes the structure of the application database for the first time, which will help us from an empty database into a database that can store users ' information.

Do a migration I use another Python small helper script (filedb_migrate.py):

#!flask/bin/python
import imp
from migrate.versioning import api
from app import db
from config import SQLALCHEMY_DATABASE_URI
from config import SQLALCHEMY_MIGRATE_REPO
migration = 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 ' + migration
print 'Current database version: ' + str(api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO))

The script looks very complicated, but there's really not much to do. Sqlalchemy-migrate by comparing the structure of the database (read from the app.db file) and the models structure (from app/ models.py file read) to create a migration task, the difference will be recorded as a migration script in the Migration Library, migration scripts know how to apply or undo a migration, so it is easy to upgrade or demote a database format.

While I used the above script to automatically generate migrations without any problems, it's sometimes really hard to decide what the old format of the database and what the new format is going to change. To make it easier for sqlalchemy-migrate to determine database changes, I never rename an existing field, restrict the addition of a delete models, a field, or modify the type of an existing field. I always check that the generated migration script is correct.

Needless to say, you must make a backup before you try to migrate the database in case there is a problem. Do not run the script that was first used on a production database, run it first on a database that is being developed.

Keep moving forward 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

This script information shows where the migration script resides and the version number of the current database. The version number of the empty database is 0, and the version number becomes 1 after we import the users information.

Upgrade and rollback of databases

Now you may want to know why we have to do extra work to make a database migration record.

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

For example, in the next version of your product, your models layer has been modified, such as adding a new table. Without migrating files, you need to solve the problem of database format modification on both the developer and server, which will be a huge workload.

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

#!flask/bin/python from
migrate.versioning import APIs from
Config import Sqlalchemy_database_uri
from Config import Sqlalchemy_migrate_repo
api.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 is upgraded to the latest version, and the change information is stored in the database through a script.

Rolling the database back into the old format, which is uncommon in one way, but just in case, Sqlalchemy-migrate also very well supported (filedb_downgrade.py):

#!flask/bin/python from
migrate.versioning import APIs from
Config import Sqlalchemy_database_uri
from Config import sqlalchemy_migrate_repo
v = 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 a version of the database, and you can roll back multiple versions as many times as you run them.

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 keep track of who wrote the tweets is to create a correlation between two data.

Once the user and the microblogging relational table are established, we have two ways to use the query. The most trivial one is when you see a microblog, you want to know which user wrote it. The more complicated one is the reverse query, if you know a user, you want to know all the tweets he wrote. Flask-sqlalchemy will provide us with help for both ways of querying.

Let's do an extension of the data to store the microblogging information so we can see the corresponding relationship. We went back to the database design tool we used to create a posts table:

The posts table contains a required ID, the contents of the microblogging body, and a timestamp. Nothing new, but the user_id field is worth explaining.

We want to establish a link between the user and the tweets they write by adding a field containing the user ID to identify who wrote the tweet, which is called a foreign key. Our database design tool also shows the foreign key as a foreign key and ID field to point to the connection to the table. This association is called a One-to-many association, which means that a user can write more than one article.

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

From app Import db
 
role_user = 0
role_admin = 1
 
class USER (db. Model):
  ID = db. Column (db. Integer, Primary_key = True)
  nickname = db. Column (db. String (+), unique = True)
  email = db. Column (db. String (), unique = True) role
  = db. Column (db. Smallinteger, default = Role_user)
  posts = db.relationship (' Post ', backref = ' author ', lazy = ' dynamic ')
 
  def __rep R__ (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 represents a user-written microblog, and the user_id field is initialized in the post class as a foreign key, so Flask-sqlalchemy will know that the field will be associated with the user.

Note that we also added a new field to the user class named posts, which is defined as a db.relationship field that is not actually a field in the database, so it's not in our database diagram. For a one-to-many associative db.relationship field, it is usually only necessary to define one side. Based on this association we can get to the user's microblog list. The first parameter of the db.relationship represents the class name of the "many" side. The Backref parameter defines a field that refers to the object of the "many" class to the "one" object, and for us, we can use Psot.author to get the user instance to create a microblog. If you can't understand it, don't worry, we'll explain it by an example later in the article.

Let's record this change with a different migration file. Simply run the following script:

./db_migrate.py

When you run the script, you get the following output:

New migration saved as db_repository/versions/002_migration.py current database Version:2

We do not need to use a separate migration file every time to record the small changes in the database model layer, a migration file is usually just record a release version of the change. The next more important thing is that we need to understand how the migration system works.

Application Practice

We've spent a lot of time on the definition of the database, but we still don't see how he works because we don't have any data-related coding in our applications, and we'll use our new database in the Python interpreter.

Go ahead and start Python. On Linux or OS X:

Copy Code code as follows:

Flask/bin/python

Under Windows:

Copy Code code as follows:

Flask/scripts/python

When you enter the following information at the python command line prompt:

>>> from app Import db, Models >>>

So our database modules and models are loaded into 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 accumulate into a session finally by invoking a db.session.commit () command to commit, the commit also guarantees atomicity. If an error occurs in the session, Db.session.rollback () is called to roll the database back to the state it was in before it was addressed. If the call is neither committed nor rolled back, the system rolls back the session by default. Sessions (session) guarantees the data consistency of the database.

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 John
2 Susan
>>>

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

There is another way to query, if we know the user ID, we can use the following way to find user information:

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

Now let's add a Twitter message:

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

This place we set the time to UTC, 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 a future tutorial we will learn how to use these times in the user's local time zone.

You may have noticed that we did not set the USER_ID field in the Post class, and instead stored the user object in the Author field. The Auhtor field is a virtual field added through Flask-sqlalchemy to establish an association, and we've already defined that name, referring to the backref parameters in the db.relationship in model. With this information, the ORM layer will know how to get to user_id.

To complete this session, let's look at more database queries to do:

# 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 post! ' ]
 
# Obtain author of each post >>> for
p in posts:
...   Print P.id,p.author.nickname,p.body ...
1 John My 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 ']
>>>

The best way to learn more about database query options is to look at the Flask-sqlalchemy documentation.

Before we end the session, we delete the test users and articles that were created before, and we can start with a clean database in the following sections:

>>> 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 ()
>>>

Concluding remarks

This novice beginner, we understand the basic operation of the database, but we have not yet associated the database into the program. In the next chapter we practice the database operations we have learned through the user login system.

In the meantime, if you haven't started writing the program, you need to download the current file microblog-0.4.zip. Note that the zip file does not include a database, but there are already stored scripts. Create a new database with db_create.py and upgrade your database to the latest version with db_upgrade.py.