I am using the raw module of SQLAlchemy, and not the Flask’s version of SQLAlchemy, Flask’s version is easier to use than the original as more abstraction has been implemented by Flask.

This post is to record how tables can be created.

Requirements.txt

These python libraries are necessary, for this example I am using Mariadb (MySQL).

passlib==1.7.2
PyMySQL==0.9.3
six==1.13.0
SQLAlchemy==1.3.12
SQLAlchemy-Utils==0.36.1

Import modules

from sqlalchemy import Column, Integer, String, func, create_engine, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utils import PasswordType
from sqlalchemy.exc import OperationalError
from getpass import getpass

Import statements breakdown

from sqlalchemy import Column, Integer, String, func, create_engine, DateTime This statement imports the data type: Integer, String, DateTime, and create column: Column. The func is needed to use the SQL syntax of now and utc_timestamp. The create_engine, connects the database and create an engine object, this object is used by the Base metadata to create the table, and it is also used by sessionmaker to insert, delete and update the table.

from sqlalchemy_utils import PasswordType This statement uses PasswordType to store the hash of the password and not the password itself.

from sqlalchemy.exc import OperationalError This statement uses the exception OperationalError.

from getpass import getpass This prompts user to enter the password.

The entire code

from sqlalchemy import Column, Integer, String, func, create_engine, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utils import PasswordType
from sqlalchemy.exc import OperationalError
from getpass import getpass

# Create base object, to be inherited by class tables.
Base = declarative_base()

# define table.
class User(Base):
    # customized table name.
    __tablename__ = "users"
    # id column
    id = Column(Integer, primary_key=True, autoincrement=True)
    # username column
    username = Column(String(64), unique=True, nullable=False)
    # password column, PasswordType will store the hash of password.
    password = Column(PasswordType(
        schemes = ['pbkdf2_sha512']
    ), nullable=False)
    # created at column, record the date and time when user first created.
    created_at = Column(DateTime, default=func.now())
    # modified at column, record date and time of modification.
    modified_at = Column(DateTime, default=func.utc_timestamp())

# Get password from user.
password = getpass("Enter password of cyruslab on database - 192.168.56.101: ")
# Connect to the database.
engine = create_engine("mysql+pymysql://cyruslab:{}@192.168.56.101/dblab".format(password))

# create tables, ignore table creation if exists.
try:
    Base.metadata.create_all(engine)
except OperationalError as oe:
    print(oe)