Flask SQL Alchemy is a library that is intended to make it easy to do something against a database, such as creating a database, creating tables, inserting data, and so on. Instead of having to write complex SQL commands, simple SQL Alchemy directives are used.

When running my Flask app in VSCode on my Windows laptop, all I had to do was to use the pip install command to install the Flask SQL Alchemy and MariaDB packages.

However, when attempting to deploy my Flask app to a uwsgi nginx flask container on Docker, I was getting "MariaDB Connector/Python requires MariaDB Connector/C". Check out my article on resolving "MariaDB Connector/Python requires MariaDB Connector/C".

pip install flask-sqlalchemy
pip install mariadb

Or, you can specify the version to install

pip install flask-sqlalchemy==3.0.2
pip install mariadb==1.1.7

Or, better yet, use a requirements.txt file.

Flask-SQLAlchemy==3.0.5
mariadb==1.1.7

And then install the packages using the requirements.txt file.

pip install --requirement requirements.txt

You will need to know the username and password of your MariaDB user that is allowed to make a remote connection. If you are able to log into your MariaDB server, the select * from mysql.user can be used to list your users. Something like this should be returned for each user. In this example, user john.doe is allowed to make a connection to Host % (remote).

You will also want to ensure MariaDB is configured to allow remote connections. Check out my article Configure mySQL or MariaDB to allow remote connections on Docker.

                  Host: %
                  User: john.doe
              Password: *B87A204DF7CFB2D1ABC123456JFH0DC081CD35EF2
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: Y
       References_priv: N
            Index_priv: N
            Alter_priv: Y
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin:
 authentication_string:

create_all() can be used to make the initial connection to MariaDB. In this example, create_all() is in __init__.py.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func, exc
import mariadb

db = SQLAlchemy()

def app_obj():
    app = Flask(__name__)
    uri = "mariadb+mariadbconnector://john.doe:itsasecret@mariadb.example.com/mydatabase"
    app.config['SQLALCHEMY_DATABASE_URI'] = uri
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db.init_app(app)

    with app.app_context():
        try:
            db.create_all()
        except exc.SQLAlchemyError as sqlalchemyerror:
            print(sqlalchemyerror)
        except Exception as exception:
            print(exception)
        finally:
            print("no exceptions were raised")

    return app

If the connection to MariaDB is successful, something like this should be returned.

Successfully connected to MariaDB
conn = <sqlalchemy.engine.base.Connection object at 0x0000028AB3C67490>

​