[python]Create database if not exists with sqlalchemy

This is an example of using SQLAlchemy module to create database if it does not exist otherwise connect to the requested database.

from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database
from getpass import getpass

"""
database url is dialect+driver://username:password@db_address/db_name
To connect to mysql/mariadb, pymysql module is required to install.
The purpose of using SqlAlchemy is to abstract sql syntax from the programmer/scripter,
hence there should be no sql syntax used, to use sql syntax use the execute method of the create_engine object.
"""

# Get database address.
db_addr = input("DB ip address: ")
# Get username of the database.
db_user = input(f"Username of {db_addr}: ")
# Get password.
db_pass = getpass(f"Password of {db_user}@{db_addr}: ")
# Get the database name.
db_name = input("Database name to connect: ")

# join the inputs into a complete database url.
url = f"mysql+pymysql://{db_user}:{db_pass}@{db_addr}/{db_name}"

# Create an engine object.
engine = create_engine(url, echo=True)

# Create database if it does not exist.
if not database_exists(engine.url):
    create_database(engine.url)
else:
    # Connect the database if exists.
    engine.connect()

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