SQLAlchemy - Hello World

The all time indisputable GOAT in programming is the famous "Hello World". If you haven't written an "Hello World" program as a developer, well here's your chance to meet the GOAT.

This is a series where I document discoveries in my Journey to SQLAlchemy. If you haven't seen my previous posts, check them out - they are somewhere at the top and bottom of this page.

In this discovery, we'll write an "Hello World" program. This may sound trivial but there're lots of important basis to be covered, it'll be worth your while - PROMISE.

Haven't setup your codebase structure yet ? do it already, as the discoveries are becoming more practical and this will build on the previous.

The Engine

Everything begins and ends with the engine in SQLAlchemy. Remember how previously, we setup a postgres database engine ? We did, so SQLAlchemy can linkup with that. The engine is the link between the database and everything else SQLAlchemy has to offer. This is a very important part, lets proceed to creating it.

Creating an engine

Before creating an engine, create a folder within db folder with name, core and add a file within db/core folder called initializer.py. Your file structure should now look like this

In this file, we'll initialize an engine using create_engine. See code below.

from sqlalchemy import create_engine
from conf.settings.base import DATABASE

# create database engine
engine = create_engine(
    "postgresql+psycopg2://{db_username}:{db_password}@{db_host}:{db_port}/{db_name}".format(
        db_username=DATABASE['USERNAME'], 
        db_password=DATABASE['PASSWORD'],
        db_host=DATABASE['HOST'],
        db_port=DATABASE['PORT'],
        db_name=DATABASE['NAME']
    ),
    echo=True
)

# create a connection with the database
def create_connection():
    return engine.connect()

There're lots of moving parts here. We imported create_engine provided by SQLAlchemy. We also imported DATABASE from core.settings.base. This module is currently unavailable, we'll visit it soon.

When creating an engine, the only requirement needed is connection string. "postgresql+psycopg2://{db_username}:{db_password}@{db_host}:{db_port}/{db_name}". This is a structured string containing the relational database management system (RDBMS), the adapter, user's credentials and database port and name. Broken into parts, we've:

• rdbms+adapter: SQLAlchemy supports a range of rdbms, from PostgreSQL to MySQL, Oracle, MSSQL. To tell which we're using, we specified postgresql. The postgres adapter we choose to use is psycopg2 which helps SQLAlchemy understand how to interact with postgresql. If we had used MySQL we would've provided a different adapter that could bridge the link for SQLAlchemy and MySQL. For us and this configuration, rdbms+adapter = postgresql+psycopg2.

• ://{db_username}:{db_password}: Identifies the user which has access to the database we'll be connecting to. db_username and db_password acts as the credentials to which this said user will be identified.

• @{db_host}:{db_port}/{db_name}: These are the database details. db_host identifies the machine where the RDBMS resides. It could either be in your local machine or a remote machine. db_port represents a connection point in the machine (local or remote) where the RDBMS is expecting a connection to be made to it. db_name is the database name to which we're trying to connect to.

The connection string in summary, specifies the database we want to connect to, where the database resides and the user that has access to the database by providing the user's credentials. Some of these details are sensitive and it isn't a good practice to include such in codebase hence why we used placeholder. The placeholder values are filled using python string formatter .format(db_username=DATABASE['xxx'], .... ). Identifying each placeholder one at a time, values are collected from DATABASE configuration constant we imported earlier.

We specified echo=True when creating the engine, just so we could see the query for each operation we perform to the database. This will be outputted in the command line where the program is run.

Having created an engine, we added a function that returns a connection to the database.

Setting up database secrets

To configure our database secrets, we've to do two things. First we'll create a config.ini file within which we'll keep all application secrets. In this case - our database secrets. Secondly, we'll read the secrets from the config.ini file into our application.

NOTE
config.ini file should not be added to git due to the sensitive details in it. To exclude it, add it to .gitignore

create a new folder within conf folder called settings. Within conf/settings, create a file named base.py. You should now have a structure that looks like the below image conf/settings/base.py

Add the following code to base.py

import configparser
import pathlib

# Setup base directory
BASE_PATH = pathlib.Path(".").absolute()

# Load environment variables
config = configparser.ConfigParser()
config.read(BASE_PATH.joinpath("config.ini"))

# get database configurations
DATABASE = {
    'USERNAME': config['database']['username'],
    'PASSWORD': config['database']['password'],
    'HOST': config['database']['host'],
    'PORT': config['database']['port'],
    'NAME': config['database']['name'],
}

From above code, we imported configparser and pathlib. With the help of pathlib, we are able to get an absolute path to the root directory of our program. This absolute path is saved in a constant variable BASE_PATH. configparser helps to read config.ini file ( which will be created shortly ).To get the full path to the file, we had to join the root directory absolute path with the file name when reading it into the configparser --> config.read( BASE_PATH.joinpath("config.ini") ).

DATABASE was the constant imported into the initializer.py file which was used to populate the placeholders in the connection string. It holds the actual database configuration.

In the root directory, create a file named config.ini with the following content

[database]
port=5432
host=localhost
username=learner
name=learnsqlalchemy
password=StrongPassword123

If you followed along with the previous post, you should be familiar with the above configurations. The only bit that looks odd is [database]( the first line ). config.ini file can hold multiple configurations/secrets, for the different configuration we can create a section using [whatever_the_section_is] e.g [aws] or [firebase] for aws or firebase base configurations respectively. Having included the above, codebase structure should be similar with the image below.

Welcoming Hello World

Well done, you'll be meeting the Greatest of All Time soon. Before you do, there're some preparations that needs to be done. First we'll add __init__.py file to all of the folders we've created so far. __init__.py is an empty file. python wouldn't recognize those folders when we try importing from them without it.

We'll add one directly within db folder and another within db/core folder. See the image below and create the files accordingly

We'll create an __init__.py file within conf folder and another within conf/settings folder, just like we did for db and db/core.

Lastly we'll add one within src folder. Complete folder structure should now look like the image below.

If you've done the above, you're amazing. Looking at the above image, you must've observed we've a main.py file all along, right ? Yes, that's the entrypoint into the application which we'll be working on shortly. Before we work on main.py let's create a new file within src folder, file name should be basic.py and should contain the following content.

from sqlalchemy import text
from db.core.initializer import create_connection


def run_db_select_statement():
    """Creates a self closing connection to the database after outputting 'Hello World'"""
    with create_connection() as conn:
        result = conn.execute(text("select 'Hello World'"))
        print(result.all())

We imported text from sqlalchemy and also create_connection function from our database initializer file in db/core/initializer.py.

The function run_db_select_statement uses a context manager to create a connection aliased as conn. With the connection alias, we run sql query to render 'Hello World' using the text function. The result from the executed query is then printed out using with print statement. The reason the connection object is self closing is because, it is used within a context manager.

In main.py add the following code

from src.basic import run_db_select_statement

if __name__ == "__main__":
    run_db_select_statement()

We simply imported run_db_select_statement function that was just created and call the function when main.py file is run using python command.

Running the program

To run this program, open up your terminal and navigate to the root directory for this project and run the command

python main.py

Your output should be similar to mine

python main.py 
2022-10-09 15:04:22,452 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2022-10-09 15:04:22,452 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-10-09 15:04:22,469 INFO sqlalchemy.engine.Engine select current_schema()
2022-10-09 15:04:22,469 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-10-09 15:04:22,485 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2022-10-09 15:04:22,485 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-10-09 15:04:22,491 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-10-09 15:04:22,491 INFO sqlalchemy.engine.Engine select 'Hello World'
2022-10-09 15:04:22,492 INFO sqlalchemy.engine.Engine [generated in 0.00094s] {}
[('Hello World',)]
2022-10-09 15:04:22,499 INFO sqlalchemy.engine.Engine ROLLBACK

As can be seen from the output, every processes it took to run the query select 'Hello World' are outputted, this is because, when creating our engine, we added echo=True.

Potential Errors

Some errors that you might encounter could be as a result of:

• not having your docker postgres container running: To confirm this, run docker ps -a. Check through your output and confirm you can find a postgres:12-alpine container image having a status as Up. See mine below:

If you've your image status saying Exited, copy CONTAINER ID value ( the first column, mine is afbf6e6bd5f8) and run the below command :

docker start afbf6e6bd5f8

Check SQLAlchemy with Docker - Setup if you have nothing in your output after running docker ps -a.

• not having an activated virtual environment: Ensure to run python main.py from your virtual environment. See how to create & activate a virtual environment

If you've encountered some other errors, kindly drop it in the comment section.

Thanks for staying with me, I hope it was worth your while ? If you like this, don't forget to hit the LIKE button and FOLLOW ME NOW, so you're notified on future discoveries.

Project Github Link --> https://github.com/spaceofmiah/practical-route-to-alchemy

☕Coffee