Flask and MySQL configuration operations, flaskmysql Configuration

Flask and MySQL configuration operations, flaskmysql Configuration

Running environment:
Centos6.7; python 2.7.11;

Fix the bug by writing it above:
SQLALCHEMY_DATABASE_URI is written as SQLALCHEMY_DATABASE_URL, which is extremely concealed;
Write session ['Known '] As session ['knowm'] because it is a string name error and the idle cannot be checked, it takes a long time to fix this bug. Be careful!

---------- Preparations for Splitting:

1. In the roles table, add the primary key attribute for the id column, and in the other table, users adds the foreign key attribute for the role_id column, so that the primary key corresponds to the foreign key, once the role name (the row value of the name column in the roles table) is modified in the roles table, all users who reference this role through role_id can immediately see the updated relationship, therefore, it is named relational database.
2. NoSQL databases generally use collections instead of tables and documents instead of records. The advantage of using NoSQL databases is that data duplication can increase the query speed.
3. There are several methods to operate the database:
A. You can write SQL statements in database command lines, that is, underlying database operations.
B. Some database drivers such as the MySQL-python driver can be used in python to operate the database. The driver encapsulates the complex underlying commands.
C. Use the SQLAlchemy database framework in flask to further encapsulate the database driver and further simplify the command. Therefore, SQLAlchemy is not a database, but a framework for database operations.

---------- I love splitting Database Management

After the preparation is complete, you can perform the following steps to manage the database:

1. Configure the database.
2. Define the database model and establish a relationship with the tables in the database.
3. Basic Database Operations (operations in the command line ):
Create tables, insert rows, modify rows, delete rows, and query rows
4. operate databases in view Functions
5. Integrate python shell
6. flask-migrate implements database migration. (I haven't figured out how database migration works till now. I will explain it later)

---------- Specific steps of the magic split line
0. Install
Installation of SQLAlchemy

pip install flask-sqlalchemy

• 1

Install the MySQL-python DRIVER: initially follow the yum method on the Internet

yum install MySQL-python

• 1

Although it is shown that the module has been installed, it is not found when import is used. Later, it can be installed with pip in the virtual environment. For details, refer to Google's installation method. Here we provide you with an idea that yum installation cannot identify and you can try pip.

1. Configure the database
The flask configuration of MySQL database is different from that of sqlite. First, you must create a database by yourself and have a data name to use. sqlite will automatically create a database for you when you do not have a database to run SQLAlchemy, so before you configure the database, you need to use other methods to first create an empty database. We recommend that you use phpMyAdmin (PS: Later we found a better tool: the installation of the simple operation interface is in Chinese, it is a little troublesome to activate, Navicat for MySQL, which is strongly used by beginners of Amway). The blogger is installed based on the following link, which is reliable for test:
Http://blog.sina.com.cn/s/blog_70545bad0101khx3.html
Here, I created a database named text1 with phpMyAdmin in advance, and then wrote the code in the hello. py file:

from flask.ext.sqlalchemy import SQLAlchemy
  app = Flask (__ name__)
  app.config ['SECRET_KEY'] = 'hard to guess'
  app.config ['SQLALCHEMY_DATABASE_URI'] = 'mysql: // root: Password @ localhost: 3306 / text1' #The user logged in here is the root user. To fill in his password, the default port of MySQL is 3306. Database name text1
  app.config ['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True #Set this item to automatically submit changes in the database after each request
  db = SQLAlchemy (app) #Instantiation

• 1
• 2
• 3
• 4
• 5
• 6

2. Define the model and establish the relationship: hello. py

class Role(db.Model):
__Tablename = roles define table name
Id = dB. Column (DB. Integer, primary? Key = true) ා define column object
name = db.Column(db.String(64),unique=True)
User = dB. Relationship ('user ', backref =' role ', lazy =' dynamic ') establishes the relationship between the two tables, where backref is to define the reverse relationship and lazy is to prohibit automatic query execution
def __repr__(self):
return '<Role {}> '.format(self.name)
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer,primary_key = True)
username = db.Column(db.String(64),unique=True,index=True)
role_id = db.Column(db.Integer,db.ForeignKey('roles.id'))
def __repr__(self):
return '<User {}>'.format(self.username)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17

3. After the preceding steps, you can perform database operations (command line)
Create a table

if __name__ == '__main__':
DB. Create? All()? Add it directly to the hello.py file. It's too cumbersome to enter it on the python command line 

• 1
• 2

Insert row:

admin_role = Role (name = 'Admin') #Instantiate
  mod_role = Role (name = 'Moderator')
  user_role = Role (name = 'User')
  user_john = User (username = 'john', role = admin_role) #role attribute is also available. Although it is not a real database column, it is an advanced representation of a one-to-many relationship
  user_susan = User (username = 'susan', role = user_role)
  user_david = User (username = 'david', role = user_role)
  db.session.add_all ([admin_role, mod_role, user_role, user_john, user_susan, user_david]) # Before you write the object to the database, add it to the session. The database session db.session and the Flask session object are not related Database sessions are also called things
  db.session.commit () # Commit session to the database

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

Run the hello. py program. Open phpMyAdmin and you can see that the roles table and users table already exist in the Database text1. In this case, you can try to run the following command in python:
Modify

admin_role.name = 'Adminstrator'
db.session.add (admin_role) #Add to database
db.session.commit () # Commit to the database

• 1
• 2
• 3

Delete:

db.session.delete(mod_role)db.session.commit()

• 1
• 2

Query:

User. query. filter_by (role = user_role). all () # note the use of the filter

• 1

4. Operations in view functions:
In the hello. py file:

@ app.route ('/', methods = ['GET', 'POST'])
  def index ():
      myform = NameForm ()
      if myform.validate_on_submit ():
          user = User.query.filter_by (username = myform.name.data) .first ()

          if user is None:
              user = User (username = myform.name.data)
              db.session.add (user) #Note that the commit () function is not used here. It is because app.config ['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True is set earlier, so SQLAlchemy will automatically help you commit.
              session ['known'] = False #There was a typo here, it took a long time to find it
          else:
              session ['known'] = True
          session ['name'] = myform.name.data
          myform.name.data = ''
          return redirect (url_for ('index'))
      return render_template ('formindex.html', form = myform, name = session.get ('name'), known = session.get ('known', False))

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16

Add the following content to the formindex.html file:

 
  {% if not known %}
    <p>please to meet you !</p>
    {% else %}
    <p>happy to see you again!</p>
    {% endif %}

• 1
• 2
• 3
• 4
• 5

5. Integrate Python shell. If the third step is to operate on the command line, you will know that you need to import the database instance and model in the shell every time. It is quite troublesome. add in py:

from flask.ext.script import Shell
def make_shell_context ():
     return dict (app = app, db = db, User = User, Role = Role) #The app on the right refers to the instance object in hello.py, and the app on the left is named by you. You can enter it on the command line and call the app. Examples
manager.add_command ('shell', Shell (make_context = make_shell_context)) #Add a shell command. The make_context parameter in the Shell class specifies the context to be passed on.

• 1
• 2
• 3
• 4

In this way, the app instance can be used without the need to import apps in the command line.

6. Finally, it was about database migration. I thought about the version library in git, but I didn't understand the specific role:
No way. Copy the code first.
Installation:pip install flask-migrate 
Configuration (in hello. py ):

from flask.ext.migrate import Migrate, MigrateCommand
migrate = Migrate (app, db) #create instance
manager.add_command ('db', MigrateCommand) #Pass the MigrateCommand class to db 

• 1
• 2
• 3

Create a migration warehouse:

(Venv) $ python hello. py db init # youmu has a lot like git

• 1

Automatic Creation of migration scripts:

(Venv) $ python hello. py db migrate-m "initial migration" # manual revision command

• 1

Update:

(venv ) $ pyhton hello.py db upgrade

• 1

So far, the basic operations on the flask and MySQL Databases have come to an end. see you ~

Http://blog.csdn.net/happy_bigqiang/article/details/50935935

View comments