[python]SQLAlchemy usage example

This is an aggregation on how to use SQLAlchemy to create a database, table and insert data into the created table. Refer to this tutorial on how to use SQLAlchemy.

from sqlalchemy_utils import database_exists, create_database
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

url = "mysql+pymysql://username:password@database_address/database_name"

# Create engine object to manipulate the database operation
engine = create_engine(url, echo=True)

# This base object is used to create the table.
Base = declarative_base()

if not database_exists(engine.url):
    create_database(engine.url)


# Table format
class Remediation(Base):
    __tablename__ = "remediation"
    tid = Column(String(10), primary_key=True)
    sid = Column(Integer, nullable=False)
    uuid = Column(String(255), nullable=False)
    action = Column(String(50), nullable=False)


# Create table
Base.metadata.create_all(engine)

# Insert record
record1 = Remediation(
    tid="T0178",
    sid=476,
    uuid="ce6feeb2-8329-11ea-943a-af2632097895",
    action="drop"
)

record2 = Remediation(
    tid="T1277",
    sid=100000,
    uuid="a234fc36-92fd-4cf5-8bab-98a587a9adf1",
    action="drop"
)

# Create database session to update and commit the records.
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([
    record1, record2
])
session.commit()

The sqlalchemy-utils contains two functions that help to create database if not exists.

The create_engine method is used to create a database engine object with the specified database url. The database url is in this format: database_dialect+database_driver://username:password@database_fqdn_or_address/database_name. The database driver is a python package based on the database you are using, for this example I am using MySQL/Mariadb hence the driver is pymysql. The database dialect is the type of database such as Oracle, mysql, postgresql, sqlite3.

The Column method creates a column of a table, the Integer and String methods define the database data type, you need to define the length of String else there is an exception, this is because mysql requires VARCHAR to define the length.

The declarative_base constructor constructs a class object which is inherited by the class that defines the structure of the table.

The sessionmaker constructor creates a session class which creates a session object, this session is required for doing read, update and delete and finally session is used to commit the changes.