[python]Create database with SQLAlchemy

The objective of learning SQLAlchemy is to use its Object Relational Mapper (ORM), this allows programmer who does not do SQL syntax to also do CRUD on supported database, the ORM does the “translation or mapping” for us in the background, in our code we only need to do CRUD with python syntax.
The SQLAlchemy tutorials I saw before kickstart users by first creating an engine object with create_engine method, then use the execute method of the engine object to push the SQL queries over to the database, however if I need to do SQL syntax then with or without using SQLAlchemy does not matter to me anymore as there are well known DB connection python modules that can help me push the SQL syntax over to the database.

So here is a post to record how I create a database without any SQL syntax by using SQLAlchemy ORM feature.

There are three parts of py files, first is the base.py which creates the objects for a declarative base, this base is used to associate the tables within the database, second is the asaobjects.py this defines the schema of the table, last is the create_table.py this creates the table based on base.py and asaobjects.py. The base.py and the asaobjects.py are stored in models directory.

base.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

###############################################################################################
# Initialize the database connection and prepare the session                                  #
# create_engine creates the engine that interacts with database                               #
# Class will be used for tables, declarative_base is used to associate which                  #
# class (table) is which in the database.                                                     #
# Session is used to perform CRUD to the database.                                            #
# See tutorial from https://leportella.com/english/2019/01/10/sqlalchemy-basics-tutorial.html #
###############################################################################################
Base = declarative_base()
engine = create_engine('sqlite:///objects.db', echo=True)
Session = sessionmaker(bind=engine)

asaobjects.py

from models.base import Base
from sqlalchemy import Integer, Text, Column

#####################################################################################
# If __tablename__ is not declared the class name will be the assumed table name.   #
# This declares the structure of the tables, AsaObjects is the class to create      #
# the structure of the table where it has id as primary key, obj_name, host_address #
# and description as columns.                                                       #
#####################################################################################
class AsaObjects(Base):
    __tablename__ = 'ASA_OBJECTS'
    id = Column(Integer, primary_key=True)
    obj_name = Column(Text)
    host_address = Column(Text)
    description = Column(Text)

create_table.py
The check if table exists can be found in here, look for the comments by Ron Kalian.

from models.asaobjects import AsaObjects
from models.base import Base, engine

# Create the table ASA_OBJECTS, do not create again if table exists.
if not engine.dialect.has_table(engine, AsaObjects.__tablename__):
    Base.metadata.create_all(engine)

Result
Screenshot 2019-10-02 at 1.18.08 PM

Advertisements

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