Connecting to a MySQL database with SQLAlchemy

Setting up my web app with flask, I was trying to connect to a MySQL database and I encountered a few errors. I'll be listing them here and writing about how I solved them.
We'll go through the steps of installing our required tools then we move on to pointing out the errors encountered and how I solved them.

It will be good the state that SQLAlchemy is an ORM for the python programming language

But first, what is an ORM?

ORM or Object Relational Mapper is a software or tool that maps your code to a database without you making use of a database connector directly as it abstracts the whole process of the database connection. This in turn makes it possible for us to connect to any relational database without changing too much code.

(Abstraction is the process of making available the features of a tool or program for direct use without having us bother about how it works).

For this process, we'll be using flask_sqlalchemy, a flask extension to leverage the sqlalchemy features. (abstraction😄)

I am also assuming we already have MySQL installed in our working environment. Read more here on installing MySQL on ubuntu.

We will also be installing flask as this would be a flask app.

$ pip3 install flask
...
$ pip3 install flask_sqlalchemy
...

Next, we move on to creating our database by starting our MySQL session and then create our database

$ mysql -h hostname -u test_user -p
...

mysql > CREATE DATABASE testdb;

After this, we proceed to create our flask app where we will be adding all the configurations and creating a basic database model.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SECRET_KEY'] = "xxxxxxxx"

db = SQLAlchemy(app)

# configuring our database uri
# note an error here
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://{username}:{password}@{server}/testdb".format(username, password, server)

# basic model
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(64), unique=True, index=True)
    password = db.Column(db.String(128))

if __name__ == "__main__":
    app.run(debug=True)

To create our tables in the database, we open a python shell session and do the following

>>>from ourflaskapp import app, db
>>>app.app_context().push()
>>>db.create_all()

db.create_all returns an error

ModuleNotFoundError: No module named 'MySQLdb'

This is because, even if we don't see the database connection process as sqlalchemy abstracts it, it still happens and here, we don't have the right module 'MySQLdb' to make this happen.

I read that python doesn't support mysqldb so we then have to install a mysql python connector

$ pip3 install mysql-connector-python

This solves the issue of the module error, however, we still have an error in connection because we want sqlalchemy to connect using the MySQL connector instead of trying to connect directly. This means we have to change the SQLALCHEMY_DATABASE_URI in our python script

# we change 

app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://{username}:{password}@{server}/testdb".format(username, password, server)

# to 

app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+mysqlconnector://{username}:{password}@{server}/testdb".format(username, password, server)

With this we can go back to the python shell session and create our database with no issues at all

>>>from ourflaskapp import app, db
>>>app.app_context().push()
>>>db.create_all()
>>>

I hope you have learned a thing or two here on the use of sqlalchemy.

Cheers.