SQLAlchemy - Hello World
Tags: sqlalchemy, python, postgres, docker
Published: 2022-10-09 16:28:36

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](https://dev.to/spaceofmiah/sqlalchemy-with-docker-the-structure-8hl), 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](https://dev.to/spaceofmiah/sqlalchemy-with-docker-setup-4ljd?#postgres-engine-setup) ? 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 

![Added initializer file to db/core folder](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h2u1whf922vj93nc43sq.png)

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

```python
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`

![Application configurations](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4lavrc2rly158lfvjk4j.png)

Add the following code to `base.py`

```python
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

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

If you followed along with the [previous post](https://dev.to/spaceofmiah/sqlalchemy-with-docker-setup-4ljd), 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.

![folder structure with config.ini](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8ixy91pmjdjhfqzpu5qk.png)

## 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

![Added init file](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/60hdadvpy88ewlzl55dp.png)

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.

![Full structure with all __init__.py file added](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8gls4110qsjwvbdj5j6b.png)

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.

```python
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

```python
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

```shell
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:

![docker container check](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k11qdjcf9aayos1le7t1.png)

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

```shell
docker start afbf6e6bd5f8
```

Check [SQLAlchemy with Docker - Setup](https://dev.to/spaceofmiah/sqlalchemy-with-docker-setup-4ljd) 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](https://dev.to/spaceofmiah/sqlalchemy-with-docker-setup-4ljd?#getting-started-dependencies-installation)

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](buymeacoffee.com/spaceofmiah)