Using MySQL in flask

In the book "Flaskweb Development: Python-based Web application development", the author uses the SQLite database in order to be simple and more focused on flask rather than the description of the database.

But the actual application of MySQL should be more extensive. So try using MySQL as a replacement for the functionality of the SQLite database in the Zhongyuan code.

Platform Description:

Ubuntu 16.04 4.8.0-36-generic (installed on VMware)

1. The first thing to note is that, unlike SQLite, Flask-sqlalchemy does not actively build a database for MySQL. So you need to manually set up a corresponding database in MySQL, then you can use Flask-sqlalchemy to find the corresponding data, verification and other operations. If you do not create it beforehand, you cannot use MySQL.
   So the first step is to create the database on MySQL, which is created using the SQL script, and uses the native MySQL.
   Use command: Mysql-u root-p
   Then enter MySQL with the password you set when installing MySQL.
   Then use the command: Source Data_test.sql execute the pre-written SQL script to create the appropriate database. Here I am really the script directory that executes if you do not need to specify the full path.
   The contents of the Data_test.sql file are as follows. Change the script to correspond to the models.py content in the book. Table, the table header one by one corresponds, but the function does not necessarily correspond to each other, not very familiar with MySQL.
   

   1 Drop Database if existsdata_test;2 Create Databasedata_test;3  Usedata_test;4 CREATE TABLERoles (5IdINT( One) not NULLAuto_increment,6NameVARCHAR(255) COLLATE Utf8_bin not NULL,7     UNIQUE(name),8     Permissions INT( One),9`default' BOOLEANDEFAULTFALSE,Ten     INDEX(`default`), One     PRIMARY KEY(ID) A) ENGINE=INNODBDEFAULTCHARSET=UTF8 COLLATE=Utf8_bin -Auto_increment=1; -  the CREATE TABLEUsers ( -IdINT( One) not NULLAuto_increment, -UsernameVARCHAR(255) COLLATE Utf8_bin not NULL, -     UNIQUE(username), +EmailVARCHAR(255) COLLATE Utf8_bin not NULL, -     UNIQUE(email), +     INDEX(username,email), APassword_hashVARCHAR(255) COLLATE Utf8_bin not NULL, atrole_idINT( One), -Confirmed BOOLEANDEFAULTFALSE, -NameVARCHAR(255), -LocationVARCHAR(255), -About_meTEXT, -Member_sincedatetime DEFAULT Current_timestamp, inLast_seendatetime DEFAULT Current_timestamp, -Avatar_hashVARCHAR(255), toFollowedINT( One), +FollowersINT( One), -     FOREIGN KEY(role_id)REFERENCESRoles (ID) on DELETE CASCADE, the     PRIMARY KEY(ID) *) ENGINE=INNODBDEFAULTCHARSET=UTF8 COLLATE=Utf8_bin $Auto_increment=1;Panax Notoginseng  - CREATE TABLEPosts ( theIdINT( One) not NULLAuto_increment, +BodyTEXT, ABody_htmlTEXT, the     timestamp datetime DEFAULT Current_timestamp, +     INDEX(timestamp), -author_idINT( One), $     FOREIGN KEY(author_id)REFERENCESUsers (ID) on DELETE CASCADE, $     PRIMARY KEY(ID) -) ENGINE=INNODBDEFAULTCHARSET=UTF8 COLLATE=Utf8_bin -Auto_increment=1; the  - CREATE TABLEfollows (Wuyifollower_idINT( One), the     constraintCon_followerFOREIGN KEY(follower_id)REFERENCESusers (ID), -followed_idINT( One), Wu     constraintCon_followedFOREIGN KEY(followed_id)REFERENCESusers (ID), -     PRIMARY KEY(follower_id,followed_id), About     timestamp datetime DEFAULT Current_timestamp $) ENGINE=INNODBDEFAULTCHARSET=UTF8 COLLATE=Utf8_bin; -  - CREATE TABLEComments ( -IdINT( One) not NULLAuto_increment, ABodyTEXT, +Body_htmlTEXT, the     timestamp datetime DEFAULT Current_timestamp, -     INDEX(timestamp), $ disabled BOOLEAN, theauthor_idINT( One), thepost_idINT( One), the     FOREIGN KEY(author_id)REFERENCESUsers (ID) on DELETE CASCADE, the     FOREIGN KEY(post_id)REFERENCESPosts (ID) on DELETE CASCADE, -     PRIMARY KEY(ID) in) ENGINE=INNODBDEFAULTCHARSET=UTF8 COLLATE=Utf8_bin theAuto_increment=1;

   After execution, MySQL should be able to query to the newly created table.
   

3. Config file to set the database in the
   

   Sqlalchemy_database_uri uses the following configuration:
   Class Testingconfig (Config):
   testing = True
   Sqlalchemy_database_uri = "Mysql+pymysql://root:[email protected]:3306/data_test"
   You can then use Flask-sqlalchemy to manipulate the database without having to go to MySQL.

The whole process for me, the more difficult should be considered to map the relationship between Flask-sqlalchemy in the MySQL database, there is a place worth noting:

• SQLAlchemy in the relationship.
  For example: The roles table has users = db.relationship (' User ', backref= ' role ', lazy= ' dynamic '). The corresponding to MySQL is actually a foreign key, and is defined in the users table, that is, the role_id in users must be referenced from the ID field of table roles. If you add a role to the users and the ID does not exist in the role, an error will be added.
  The parameter backref indicates that the relationship is bidirectional, so that you do not need to define a relationship again for users. When lazy is set to dynamic, it indicates that the query does not load the result directly, but instead returns a query object so that the query results can be screened by filtering.

The following links are referenced in the practice process:

Understanding of external construction

FOREIGN KEY constraint keyword

Using MySQL in flask