Skip to content

Database

A Flask application of any significant complexity requires a database for persistent storage. Although Flask can work with NoSQL databases such as MongoDB, a relational database is a more common choice. There are different opinions about exactly which database to use, and some sources strongly recommend PostgreSQL. In this tutorial though, we will be using the ever-popular MySQL because more people are familiar with it. On Windows, the recommendation is to use MariaDB instead. MariaDB is a fork of MySQL and so it is virtually identical, but it has a much simpler and more reliable installation process. Once it is installed, just follow all of the other instructions for MySQL.

Check whether your system already has MySQL installed. You can do this on Mac or Linux by opening a command prompt and typing

1
mysql --version

On Windows, check whether you have the MariaDB or MySQL executables on your Start menu.

If MySQL is installed, you will see the version number. Otherwise, you will see an error message saying that the command is not found. In that case, install MySQL by following the instructions for your operating system.

During the installation process, you will be asked to set the root password. Make sure that you keep this password safe. You will need it for all database maintenance including creating new databases and users.

You can access MySQL in several different ways, the simplest of which is with the command line. Some people prefer a graphical environment such as MySQL Workbench; however, the database integration features in PyCharm provide similar functionality.

Create a database

Follow the steps below to verify the installation of MySQL, and to create the database and user account needed for the Flask application

  1. Open a command line - on Windows, use the special MySQL command line tool that was created during the installation

  2. Connect to the database as the root user using the command below. When prompted, enter the root password that you set during the installation.

    1
    mysql -u root -p

  3. At the mysql> prompt, list the available database with the command

    1
    show databases;

  4. Create a new database called students with the command

    1
    create database students_db;

  5. Make students_db the active database with the command

    1
    use students_db

  6. Create a test table:

    1
    CREATE TABLE test (col1 VARCHAR(10));

  7. Show the table details:

    1
    DESC test;

  8. Create a new record in the table

    1
    INSERT INTO test (col1) VALUES ('abcde');

  9. Show the contents of the table:

    1
    SELECT * FROM test;

  10. List all of the tables currently defined:

    1
    show tables;

  11. Create a new database user account with the command below. Note that student_admin is the username, and a^%RvbnjuY is the password. You will need these credentials again later when configuring the database connection to PyCharm.

    1
    CREATE USER 'student_admin'@'localhost' IDENTIFIED BY 'a27Rv35bZnju0Yw1';

  12. Allow the new user all privileges on the students database:

    1
    GRANT ALL PRIVILEGES ON students_db.* TO 'student_admin'@'localhost';

Both the application and PyCharm will connect to the database using this user account.

Further reading

MySQL reference manual

MySQL language reference