[python]Create table with SQLAlchemy

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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s