Databases-Python-Demo

Adapted from Databases-Java-Demo and Databases-NodeJS-Demo, originally by Christos Hadjichristofi.

Implementation in PHP here.

Dependencies

• MySQL for Windows
• Python, with the additional libraries:
  • Flask
  • Flask-MySQLdb
  • faker (for data generation)
  • Flask-WTForms and email-validator (a more involved method of input validation)

Use pip3 install <package_name> to install each individual Python package (library) directly for the entire system, or create a virtual environment with the venv module. The necessary packages for this app are listed in requirements.txt and can be installed all together via pip install -r requirements.txt.

What does Flask do

Flask is a micro web framework used to create web applications. It uses Jinja as its templating engine, to generate static template files at runtime, and Werkzeug as its WSGI toolkit, to facilitate the communication between web server and application. When writing an app locally, Flask will launch a simple "development" server on which to run it.

How to Execute SQL Queries with Python and Flask

In order to send queries to a database from a Python program, a connection between it and the databases' server must be established first. That is accomplished by a cursor object from the Flask-MySQLdb library, and using the appropriate methods (execute, commit).

Flask-WTForms

This package integrates the WTForms library with Flask. WTForms is used for secure input (form) validation and form rendering inside the templates. It provides security features such as CSRF protection. Each field of a FlaskForm class is essentially rendered as the corresponding input tag in HTML.

Project Structure

Generally, Flask allows some freedom of choice regarding the layout of the application's components. This demo follows the structure recommended by the official documentation, whereby a package, arbitrarily named "dbdemo", contains the application's code and files, separated into folders for each category (models, controllers, HTML templates - views, static files such as css or images).

Additionally, it utilizes Blueprints, a Flask structure that divides the app into sub-modules. Each of those is supposed to represent an entity of the database, and contains its own init file, and corresponding form and route declarations.

• __init__.py configures the application, including the necessary information and credentials for the database
• Each module folder contains:
  • an __init__ file that initializes the Blueprint
  • a routes.py file with the relevant endpoints and corresponding controllers
• run.py launches the simple, built-in server and runs the app on it
• all HTML templates are stored together in the templates folder, but could also be separated per Blueprint

Run via the flask run command (set the environment variable FLASK_APP to run.py) or directly with run.py.

The demo's toy database is created and populated by db-project-demo.sql.

Good Practices

1. Never upload passwords or API keys to github. One simple way to secure your passwords is to store them in a separate file, that will be included in .gitignore:

   dbdemo/config.json

   {
       "MYSQL_USER": "dbuser",
       "MYSQL_PASSWORD": "dbpass",
       "MYSQL_DB": "dbname",
       "MYSQL_HOST": "localhost",
       "SECRET_KEY": "key",
       "WTF_CSRF_SECRET_KEY": "key"
   }

   Import the credentials in __init__.py by replacing the app.config commands with:

   import json
   ## ...
   app.config.from_file("config.json", load = json.load)

Note for Linux users

Applications that run without sudo privileges often are not allowed to connect to MySQL with the root user. In order to overcome this problem, you should create a new MySQL user an grant him privileges for this demo application. Follow these steps:

1. Open a terminal and precede the mysql command with sudo to invoke it with the privileges of the root Ubuntu user in order to gain access to the root MySQL user. This can be done using
   sudo mysql -u root -p.
2. Create a new MySQL user using:
   mysql> CREATE USER 'type_username'@'localhost' IDENTIFIED BY 'type_your_password_here_123';
3. Grant the user root privileges on the application's database using:
   mysql> GRANT ALL PRIVILEGES ON demo.* TO 'type_username'@'localhost' WITH GRANT OPTION;
4. Reload the grant tables to ensure that the new privileges are put into effect using: FLUSH PRIVILEGES;.
5. Exit MySQL with mysql> exit;.
6. Go to dbdemo/__init__.py and change the app.config["MYSQL_USER"] and app.config["MYSQL_PASSWORD"] lines according to the username and the password you chose before.

For more details read this.

Screenshots