DEV Community: Osazuwa J. Agbonze

From Novice to Ninja: The Django Advantage

Osazuwa J. Agbonze — Fri, 05 Jan 2024 10:44:00 +0000

Entering the realm of web development can be daunting for those unfamiliar with the intricacies of how an application works or how the web operates. While some may suggest learning how the web works before delving into application development, it's essential to recognize that not everyone learns that way. Some prefer an immersive approach, learning through practice by building web-related systems.

So, if you're like my younger self, who knew nothing about web development but was interested in learning how to build web applications, Django web framework provides a hospitable journey for that.

Having extensively used Django for over 5 years now, I find it comfortable exploring other frameworks and recognizing familiar patterns.

There are several other alternatives that I now find comfortable to work with (e.g., Express, NestJS, PHP, FastAPI, Flask), but this wasn't the case when I was starting out solely due to my experience level at that time.

Django is not only easier to get started with but also makes it very easy to build web apps more quickly with lesser code.

Django's Advantage

1) Compelling Documentation :

This was how I got hooked in. Django's documentation doesn't assume you have knowledge about the building blocks of an application. It explains every concept in detail with examples, starting from scratch.

From the content structure and web layout to font sizing, and most importantly, the content, everything is so well-placed that I need not click another link to understand the subject matter on a page.

Django's documentation contains tutorials, guides, references with lots of examples on every subject matter.

Every component that makes an application are also well sectioned out with guides, references, lots of examples, advanced topics and more.

Django's documentation is not only accessible online, the complete documentation can be downloaded as a pdf, html or epub for offline use.

2) Achieved Early Contentment:

As a beginner, my grasp of web applications was confined to their operation in a browser. I felt exhilaration witnessing an application—something I hadn't actively searched for on Google—run seamlessly in my browser. This magic happened with just a simple input of 'localhost:8000,' the default host and port that Django uses to launch applications in development. This came about after executing two straightforward and self-explanatory commands.

The catch was that I didn't need to write any code to see the created application working in my browser. Only later did I find myself tweaking things, after my interest had been captivated. At that point, I hungered for more, and my mind eagerly embraced the opportunity to understand the entire process, leading to continuous learning.

3) Everything Included Formed a Habit in Me:

As i continued building, i observed that aside everything being well documented i didn't have to explore external systems, libraries or services.

Whatever piece or puzzle I needed for my application, Django already provided out of the box. I didn't have to go outside the documentation to learn additional tools or libraries to progress in my learning.

That formed a habit in me. I consistently utilized the documentation and delved deeper into the framework and all it provides. I would say, django successfully killed two birds with one stone.

While Django provides the majority of components an application would require, it also allows you to either extend a component or replace it completely

4) Community:

It's not always smooth and rosy. There were times in my learning process when I faced difficulties or encountered total roadblocks. Without the help of other experienced developers willing to support and assist me, I probably wouldn't have come this far.

Django has a diverse and vibrant community willing to guide you. Besides getting help, I also learned, through the community, how to fine-tune my questions for better reach.

As a self-taught developer, knowing there are experts you can reach out to, who wholeheartedly support and guide you, adds to your peace and pace. Consider joining a community.

5) Consistent Resource:

When I'm not studying Django's documentation, I'm either watching a YouTube tutorial or reading books. Most people follow the same approach, ensuring consistency with external online resources with the documentation.

I also found that most external libraries/packages' documentation uses well-known examples from Django's documentation, such as the poll application.

This consistency was immensely helpful because it ensured I wasn't building half-baked applications. For instance, Django's documentation teaches the creation of a web application using a poll application, and the same poll application is used by Django Rest Framework to demonstrate the development of RESTful APIs. Lastly, Django Graphene also employs the same application but transforms it into a GraphQL implementation.

The consistency everywhere points to the solid foundation django team consciously built into the culture of using the framework.

6) Django is built on Python

Python is widely considered one of the easiest programming languages for beginners to learn. This ease extends to Django since it is built on Python.

Resources I Found Helpful

I'm not a marketer, and I don't receive any benefits from mentioning these resources. I'm sharing them solely because I found them exceptionally helpful on my journey.

1) Django's Official Documentation

https://www.djangoproject.com/

2) Automate the Boring Stuff by Al Sweigart ( Book )

I only needed this book starting out and it did justice to my knowledge of python and django web framework. After completing this book, django's official documentation easily served as point of reference for more advanced exploration.

With a quick google search, you should find a way to purchase this book or read it online.

3) Try Django Youtube Series by CodingEntrepreneurs

https://www.youtube.com/@CodingEntrepreneurs

Justin teaches with so much details leaving no points untouched and he does it so well.

Would I Recommend Developers to Begin with Django ?

Considering various factors in web development, Django emerges as a viable choice for beginners.

If one is facing challenges in selecting a framework, Django could be considered as a potential first choice, especially for those initiating their journey into web development. This path has proven effective for many learners in developing web applications.

If you've already started with another web framework, continuing with that could also lead to a comfortable proficiency.

Conclusion

In conclusion, my journey from a novice to embracing the Django framework has been marked by discovery, challenges, and growth.

As I reflect on the consistency, versatility, and solid foundation that Django provides, I can confidently say that the 'Django Advantage' has been a guiding force in shaping my web development journey. Whether through documentation, community engagement, or exploring external resources, the path from novice to ninja has been enriched by the principles embedded in the Django culture.

As I continue forward, I'm not just a user of Django; I'm a beneficiary of the invaluable lessons it has imparted and the community it has connected me with.

API File Upload Done Right - FastAPI

Osazuwa J. Agbonze — Mon, 01 May 2023 20:44:52 +0000

In this article, I document my research on HTTP limitation for file upload and show an implementation to build an API for file upload using fastapi.

Lets assume you want to update your social profile to hold your work email and a new profile picture. The request therefore is required to contain two data entries: email and profile picture. To ensure this update request succeeds, the request header content type would have to reflect the kind of data being sent

What is Content Type and Why does it matter ?

Content Type is how we communicate the kind of data that's being sent. This allows for an appropriate interpretation by the receiver which can either be the client or server.

content type multipart/form-data: expresses values submitted through an HTML form. A sample HTML form with multipart/form-data

<form action="/" method="post" enctype="multipart/form-data">
  <input type="email" name="email" value="enter your  email address" />
  <input type="file" name="picture" />
  <button type="submit">Submit</button>
</form>

content type application/json: expresses values submitted through JSON (JavaScript Object Notation) data. This is basically a mapper of key and values pair.

Because content type allows receiver to understand the kind of data that's being sent, for there to be a successful communication, the rules has to be respected. If the receiver is expecting data to be sent in via multipart/form-data but ends up receiving data in form of application/json then there would be a problem in communication and vice versa.

Below is an example of data which would respect the application/json content type:

{
     "email": "johndoe@mail.com",
     "picture": "myfile.png"
}

While the above data is a valid JSON, there's a misinterpretation going on there. The value given to picture payload would be interpreted as string although our intention was to submit a file.

So how do you solve this ?

ACCEPTABLE JSON PAYLOAD WITH FILE FIELD

While you cannot feed in a raw file data to a JSON object, you can convert the raw file into data that could be understood and allowed by JSON. Did i say conversion ? Yes, I did. This would warrant you to reconvert the data to the initial state at the receiving side too. JSON can understand Base64 and here's some excerpts about it from wikipedia

Common to all binary-to-text encoding schemes, Base64 is designed to carry data stored in binary formats across channels that only reliably support text content. Base64 is particularly prevalent on the World Wide Web where one of its uses is the ability to embed image files or other binary assets inside textual assets such as HTML and CSS files.

The more typical use is to encode binary data (such as an image); the resulting Base64 data will only contain 64 different ASCII characters, all of which can reliably be transferred across systems that may corrupt the raw source bytes.

Here's a summary of the above exerpt:

Base64 is typically used to encode binary data (images, files, e.t.c) to text strings (actually bytes) which JSON understands and can work with.

How do we do this ? you'd ask. I would give you a practical answer in a bit. To clarify all that has been said so far, here are some straight forward questions and quick answers

When should I use content type format multipart/form-data ? Use multipart/form-data when you're handling a request from an HTML form.

When should I use content type format application/json ? When building an API (Application Programming Interface).

Let's answer the big question How do i upload a file data with extra payloads using a practical example

HOW TO HANDLE FILE UPLOAD WITH PYDANTIC MODEL

In fastapi documentation, a section is dedicated to explaining how to request file, for which you must have python-multipart package installed as it is a requirement. The given example expects request from HTML form i.e multipart/form-data content type.

But that's not what we want, we want to send our data as with application/json content type. Following are steps showing the right way to go about this

convert the file to a base64 data url string: we'll use an online tool to achieve this. I experimented with base64guru during my research. After converting your file, the generated string should have this kind of format

data:@file/jpeg;base64,/9j/4AAQSkxxxxxxxxxxxxxxxx

copy the response from the generator as is and use it for step 2

assign the value to picture payload
on the receiver side, reconvert the base64 data url string back to it's initial state

I'll assume you've a fastapi application setup already, so create a new pydantic model with email and picture field

from pydantic import BaseModel, EmailStr

class UpdateProfileSchema(BaseModel):
    """Models updatable field of a profile instance"""
    email:EmailStr
    picture:bytes

Create an api endpoint to handle a real profile update expecting both email and picture data payload. This endpoint reconverts the received base64 data url string back to a file and save the file in the file system. For other cases, you might want to upload the file to a private s3 bucket.

import base64

@app.post("/upload", response_model=dict)
def update_profile(payload:UpdateProfileSchema):
    data_split = payload.image.split('base64,')
    encoded_data = data_split[1]
    data = base64.b64decode(encoded_data)
    with open("uploaded_image.png", "wb") as writer:
        writer.write(data)

    return {"detail": "Profile update successful"}

Below is a snipped screen of my request payload and the content type used in the request header.

CONCLUSION

We've successfully uploaded a file using application/json content type. The resolution approach taken is not a limitation on FastAPI but of HTTP. Therefore the followed approach is how images/files should be uploaded to an API regardless of the framework used.

Encounter any error ? leave a question on the comment section. If you enjoyed this article, follow me for more.

Email me spaceofmiah@gmail.com

Implementing Authorization in FastAPI: A Step-by-Step Guide to Securing Your Web Applications

Osazuwa J. Agbonze — Fri, 03 Feb 2023 07:09:34 +0000

In today's world, building secure and reliable web applications is more important than ever. One crucial aspect of this is implementing proper authorization protocols to protect sensitive user data and ensure that only authorized users can access certain resources.

FastAPI is a modern, fast (high-performance), web framework for building APIs with Python 3.6+ based on standard Python type hints. One of the key advantages of FastAPI is its built-in support for handling user authentication and authorization.

FastAPI has built-in support for handling authentication through the use of JSON Web Tokens (JWT). Once a user is authenticated, their JWT can be used to authorize them to access certain resources or perform certain actions. In this guide, we'll see how to do just that. Below is an outline on what we'll touch on in this guide.

FastAPI authentication scheme setup
Data access protection configuration
Access authorization

To follow along, do note:

This guide will build on JWT authentication in FastAPI. Yet to see the guide? then check it out, i'll wait here. Github repository is also available, so you can clone the project and begin from where we left off

JWT Authentication in FastAPI ( Comprehensive Guide )

Osazuwa J. Agbonze ・ Jan 14 '23

#devops

Generating JWT token in FastAPI for authentication

FastAPI authentication scheme setup

To access core features in most applications, logging in is usually required. While some application would only provide an option to log in with email and password (this is what we'll use in this guide), others might require that and also include log in with google, facebook you get the gist. These options have a distinct interface which collects user credentials and a process (view, controller, service e.t.c) that validates the credentials. All of these together is an authentication scheme: Authentication scheme defines what's needed for an authentication process, these include:

the kind of authentication to be supported e.g auth with email & password, social auth e.t.c
an interface to collect auth credentials e.g SPA app / postman e.t.c
a process to validate the credentials and return a token

Authentication Scheme with different login options

When it comes to setting up authentication scheme, FastAPI shines with it's setup simplicity.Open main.py and include the following content

# other imports statement at the top
from fastapi.security import OAuth2PasswordBearer

# setup authentication scheme
oauth2_scheme = OAuth2PasswordBearer(tokenUrl="login")

The above code is an authentication scheme which would request that users provide a username and password to login. We imported and instantiated 0Auth2PasswordBearer with a route to handle login process fed to tokenUrl param. The login route would generate a JWT token on successful authentication.

Data access protection configuration

Data privacy is an important topic which should be taken very seriously. You only want to grant data access to authorized users. We'll see how to facilitate data privacy in FastAPI by allowing only authorized access to user's profile. Open up main.py and include the following code

@app.get("/profile/{id}", response_model=UserSchema)
def profile(id:int, session:Session=Depends(get_db)):
    """Processes request to retrieve user
    profile by id
    """
    return user_db_services.get_user_by_id(session=session, id=id)

We've defined a profile view which is accessible on route /profile/{id}. It takes as a path parameter the user's id whose profile is to be viewed. Response type is a UserSchema. The body of the view feeds the id and a database session to get_user_by_id service. This service is currently unavailable, open up services/db/users.py to include it:

# other functions and import statements above

def get_user_by_id(session:Session, id:int):
    return session.query(User).filter(User.id == id).one()

get_user_by_id function queries the User database table and filter user having an id value similar to that retrieved from path parameter from our profile view.

At this point we've not set any data restrictions (we'll get to this shortly and you'd see how easy FastAPI has made the setup) meaning all user's profile data is accessible to the general public. Lets test this out. Go to project root and run docker-compose up --build -d --no-deps

Note
You might want to run migrations to avoid database interaction errors. Alembic is smart enough and wouldn't make any migration that has already been made, so it's fine if you re-run the migration.

In your browser, open localhost:8000/docs and you should be presented with this view

Build with FastAPI - docs route snipshot of profile view

Create a new user by interacting with the signup route by using the try it out button. When a user is created, the response would return the new user's id which will be needed to interact with the profile view. Below is an image of what the response should look like

Build with FastAPI - create new user response

In my case, the new user id is 2. we'll proceed to interacting with the profile endpoint with the new user's id. Click on try it out on the profile endpoint and you should have a form field where an id is requested for.

Build with FastAPI - user profile route

On clicking execute, you should have a response similar to the one gotten on creation of the user.

To set data privacy, we'll utilize the authentication scheme setup earlier by supplying it as a parameter to profile view signature.

main.py -- updating profile view

@app.get("/profile/{id}", response_model=UserSchema)
def profile(
    id:int, 
    token: str = Depends(oauth2_scheme),
    session:Session=Depends(get_db)
):
        """Processes request to retrieve the requesting user
    profile 
    """
    return user_db_services.get_user_by_id(session=session, id=id)

With that simple addition, we should now have an Authorize button on the docs page as shown below

Can't find expected update on the Docs ?

If you can't find the changes on the docs page or your changes not working as expected, restarting your app container service might fix the issue

docker-compose restart app

Didn't work ? leave a comment below

Build with FastAPI - authorize button

Attempt to authorize a user by clicking on any of the authorize buttons would result to 422 Unprocessable Entity Error. Clicking on try it out on the profile endpoint that previously returned the user detail whose id was passed should now return Unauthorized as seen in the image below

Build with FastAPI - unauthorized access

Fixing Unprocessable Entity Error

Our login view is currently using UserLoginSchema to capture authentication details. While this is not entirely wrong, the token provided as response from the endpoint cannot be used to interact with endpoints that requires authorization on the Docs. So lets fix this

main.py

from fastapi.security import OAuth2PasswordRequestForm

@app.post('/login', response_model=Dict)
def login(
        payload: OAuth2PasswordRequestForm = Depends(),
        session: Session = Depends(get_db)
    ):
    """Processes user's authentication and returns a token
    on successful authentication.

    request body:

    - username: Unique identifier for a user e.g email, 
                phone number, name

    - password:
    """
    try:
        user:user_model.User = user_db_services.get_user(
            session=session, email=payload.username
        )
    except:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Invalid user credentials"
        )

    is_validated:bool = user.validate_password(payload.password)
    if not is_validated:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Invalid user credentials"
        )

    return user.generate_token()

We imported OAuth2PasswordRequestForm and utilized it in login function signature as a type to payload parameter which is automatically injected by Depends. Changes was also made on user retrieval from database by using payload.username (initially was payload.email ). Those are all the needed changes.

Jan 27 '20 Comments: 1 Answers: 13

124

I'm building a simple API to test a database. When I use GET request everything works fine, but if I change to POST, I get 422 Unprocessable Entity error.

Here is the FastAPI code:

from fastapi import FastAPI
app = FastAPI()

@app.post("/")
def main(user):
    return user

Here is my…

Open Full Question

Access authorization

To gain authorized access first click on the authorize button which should present a form with username and password field. Provide the email of the user created earlier as username and the password to authenticate. On successful authentication, clicking on the authorize button again should show that authorization access has been granted

Build with FastAPI - successful authorization

With authorized access now granted, interacting with the endpoint to retrieve a user's profile would work fine.

Summary

In this guide we looked at authorization scheme and what it's comprised of. We also touched on data privacy and how easy FastAPI has made it's implementation.

Thanks for taking the time to read through. With the ability to setup a JWT authentication and authorization scheme in FastAPI, you can further extend what you've learnt and include it in your project. I'm currently using this concept to restrict unauthorized interaction with an object storage.

Code is on Github

JWT Authentication in FastAPI ( Comprehensive Guide )

Osazuwa J. Agbonze — Sat, 14 Jan 2023 06:00:00 +0000

Hi and welcome. In this guide we'll build a JWT authentication system with FastAPI. Followed technique is production grade and by the end of this walkthrough, you should've a system ready to authenticate users. We'll use SQLAlchemy as ORM for Postgres DB and alembic as migration tool. Application and database will be containerized with docker.

Pre-requisite

It is expected to have installed docker and to be familiar with it's usage. Foundational knowledge to SQLAlchemy would also be of an advantage.

You can find the complete codebase on github

Case Study

💫 Zap
feel free to skip this section if you're familiar with how authentication works with JWT

A simple use case to keep in mind is that of a student that needs to access her unique profile in an academic portal to submit a project. It is required student creates an account (only for new students) by providing an email and password which is saved on the platform to allow for account recognition on future access. At login, student provides an email and password to gain access to the academic portal. Valid credentials would allow student access and invalid credentials would deny access. On successful login a token is given which when used before the expiration time would allow the student access to the platform.

Virtual Environment & Application Dependencies

To get started, open your terminal & navigate to a folder dedicated solely for this guide. As a personal choice, I've named mine jwt-fast-api. Use below script to create & activate virtual environment which will scope dependencies needed for this guide from those installed globally.

# create environment [ windows, linux, mac ]
python -m venv env

# activate environment [ windows ]
env/Scripts/activate

# activate environment [ linux & mac ]
source env/bin/activate

We'll proceed to installing the necessary dependencies needed in this guide. Copy the below content to requirements.txt.

fastapi==0.88.0
bcrypt==4.0.1
pyjwt==2.6.0
alembic>=1.9.1
uvicorn==0.20.0
SQLAlchemy>=1.4,<=2.0
psycopg2-binary==2.9.5
email-validator>=1.0.3

Install dependencies with command

pip install -r requirements.txt

Hello Login

Create a new file at the root of your project folder named main.py which will serve as the application entrypoint. Below is the current folder structure

jwt-fast-api/
├─ main.py
├─ requirements.txt

Open up main.py and include the following content

import fastapi


app = fastapi.FastAPI()


@app.post('/login')
def login():
    """Processes user's authentication and returns a token
    on successful authentication.

    request body:

    - username: Unique identifier for a user e.g email, 
                phone number, name

    - password:
    """
    return "ThisTokenIsFake"

Above code simply creates a fastapi application to which /login/ route is attached to accept a post request. The endpoint currently returns a fake token, this we'll revisit and refactor.

Serve the application using below command

uvicorn --reload main:app

If the application is served successfully, the command line output should be similar to the below output

Exploring the Docs

We'll be using the interactive docs auto-generated by fastapi to test the application as we build. Open your browser and visit 127.0.0.1:8000/docs. You should be greeted with a page similar to the one below

Every endpoint on the docs has a Try It Out button, when clicked on shows an Execute button that sends a request to the endpoint. Clicking Execute button on the login endpoint would return a response ThisTokenIsFake.

Application Docker Image

Having gotten our application to run successfully, let's create a docker image for it. With this we are sure to have consistent platform agnostic application behavior.

In the project root, create a new file named Dockerfile and include the following code

FROM        python:3.8-alpine

ENV         PYTHONUNBUFFERED=1

WORKDIR     /home

COPY        ./requirements.txt .

COPY        * .

RUN         pip install -r requirements.txt \
            && adduser --disabled-password --no-create-home doe

USER        doe

EXPOSE      8000

CMD         ["uvicorn", "main:app", "--port", "8000", "--host", "0.0.0.0"]

We had to be explicit with uvicorn command used in the Dockerfile to specify the port and the host IP address we want the app to run on.

Before using any docker command, ensure to have docker installed and it's service running.

To build the docker image, your current working directory should be in same location as the Dockerfile. Run the following script

docker build . -t fastapiapp

this would name the application docker image as fastapiapp.

Test that the application runs successfully when launched using the docker image.

docker run -it -p 8000:8000 fastapiapp

Open your browser and you should still be able to access the interactive documentation autogenerated for the application by fastapi.

Database Service Setup

The database and application are separate entities and as such would need a way to interact. Docker compose would be used to define and connect our services, that is, application and database service. The application is already configured and can take more features, the following section shows how to configure the database

Create a docker-compose.yml file in the project root. Your project structure should resemble

jwt-fast-api/
├─ Dockerfile
├─ requirements.txt
├─ docker-compose.yml
├─ main.py

Paste the following content into docker-compose.yml

version: "3.9"

services:
  db:
    image: postgres:12-alpine
    container_name: fastapiapp_demodb
    restart: always
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    networks:
      - fastapiappnetwork

  app:
    image: fastapiapp
    container_name: fastapiapp_demoapp
    ports:
      - 8000:8000
    volumes:
      - .:/home
    depends_on:
      - db
    networks:
      - fastapiappnetwork

networks:
  fastapiappnetwork:

Above docker-compose.yml file defines two services namely: app and db. app service composition defines a connection to the db using the depends_on statement which would allow the app to have access to the database.

To bring the application and database to live, run

docker-compose up --build

which would run both services on the foreground of your terminal. You should've a similar output as seen below

The application should be accessible from the browser like previously seen.

Hide Sensitive Variables

As a best practice, sensitive variables shouldn't be committed to public repository. For this, we'll prune docker-compose.yml. First off, create a new file .env in project root

jwt-fast-api/
├─ .env
├─ Dockerfile
├─ requirements.txt
├─ docker-compose.yml
├─ main.py

Add the following to .env file

POSTGRES_DB=enteryourdbname
POSTGRES_USER=enterdbusername
POSTGRES_PASSWORD=enterdbuserpassword

Update db service environment block on docker-compose.yml with the following code

.....

   db:
    ......
    ......
    environment:
      - POSTGRES_DB=$POSTGRES_DB
      - POSTGRES_USER=$POSTGRES_USER
      - POSTGRES_PASSWORD=$POSTGRES_PASSWORD
    ......

......

We've successfully prune docker-compose.yml. Just one more step left, create a .gitignore file and add .env as it's only content. Your folder structure should now resemble

jwt-fast-api/
├─ .env
├─ Dockerfile
├─ requirements.txt
├─ docker-compose.yml
├─ main.py
├─ .gitignore

Setup Application Database Usage With SQLAlchemy

SQLAlchemy is the Object Relational Mapper (ORM) with which we'll interact with our database.

Create settings.py file in project root. This file would house all application configurations. With this new file added, folder structure should now resemble

jwt-fast-api/
├─ .env
├─ Dockerfile
├─ requirements.txt
├─ docker-compose.yml
├─ main.py
├─ settings.py
├─ .gitignore

Add the following content to settings.py

import os

# Database url configuration
DATABASE_URL = "postgresql+psycopg2://{username}:{password}@{host}:{port}/{db_name}".format(
    host=os.getenv("POSTGRES_HOST"),
    port=os.getenv("POSTGRES_PORT"),
    db_name=os.getenv("POSTGRES_DB"),
    username=os.getenv("POSTGRES_USER"),
    password=os.getenv("POSTGRES_PASSWORD"),
)

The database URL is composed using the sensitive database variables defined in .env file. From above database URL declaration, there're two variables accessed which are not in .env i.e POSTGRES_HOST and POSTGRES_PORT. Update .env file to include the following variables

POSTGRES_HOST=db
POSTGRES_PORT=5432

Although we've setup the application to read sensitive variables from it's environment, these variables are yet to be served to the app service in our docker-compose.yml file. Update app service composition in docker-compose.yml to include environment block (just like we had for db service). Below is the complete code for docker-compose.yml file

version: "3.9"

services:
  db:
    image: postgres:12-alpine
    container_name: fastapiapp_demodb
    restart: always
    environment:
      - POSTGRES_DB=$POSTGRES_DB
      - POSTGRES_USER=$POSTGRES_USER
      - POSTGRES_PASSWORD=$POSTGRES_PASSWORD
    networks:
      - fastapiappnetwork

  app:
    image: fastapiapp
    container_name: fastapiapp_demoapp
    ports:
      - 8000:8000
    volumes:
      - .:/home
    depends_on:
      - db
    networks:
      - fastapiappnetwork
    environment:
      - POSTGRES_DB=$POSTGRES_DB
      - POSTGRES_USER=$POSTGRES_USER
      - POSTGRES_HOST=$POSTGRES_HOST
      - POSTGRES_PORT=$POSTGRES_PORT
      - POSTGRES_PASSWORD=$POSTGRES_PASSWORD

networks:
  fastapiappnetwork:

At this point, our application now have access to the environment variable and the DATABASE_URL configuration setup in settings.py is ready to be used.

All SQLAlchemy processes passes through a base called engine. An engine powers communication and specifies access to the database where sql interactions is directed. Create a db_initializer.py file within project root and include the following content

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

import settings


# Create database engine
engine = create_engine(settings.DATABASE_URL, echo=True, future=True)

# Create database declarative base
Base = declarative_base()

# Create session
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


def get_db():
    """Database session generator"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

With above configuration, we now have SQLAlchemy setup within our application and ready for use.

Create User Model

User model would represent an entity capable of being authenticated. The most crucial details needed for authentication (in this case) are email and password. As a best practice, user's password are not meant to be saved in it's raw context, therefore, it's advised that the saved value should be the hashed representation of the raw text.

Create models folder in project root directory and within it add __init__.py and users.py. Project structure should be similar with the one below

jwt-fast-api/
├─ models/
   ├─ __init__.py
   ├─ users.py
├─ .env
├─ main.py
├─ .gitignore
├─ Dockerfile
├─ settings.py
├─ requirements.txt
├─ docker-compose.yml
├─ db_initializer.py

Open users.py and add the following content

from sqlalchemy import (
    LargeBinary, 
    Column, 
    String, 
    Integer,
    Boolean, 
    UniqueConstraint, 
    PrimaryKeyConstraint
)

from db_initializer import Base


class User(Base):
    """Models a user table"""
    __tablename__ = "users"
    email = Column(String(225), nullable=False, unique=True)
    id = Column(Integer, nullable=False, primary_key=True)
    hashed_password = Column(LargeBinary, nullable=False)
    full_name = Column(String(225), nullable=False)
    is_active = Column(Boolean, default=False)

    UniqueConstraint("email", name="uq_user_email")
    PrimaryKeyConstraint("id", name="pk_user_id")

    def __repr__(self):
        """Returns string representation of model instance"""
        return "<User {full_name!r}>".format(full_name=self.full_name)

Alembic Setup

Now that we've our user model declared, we'll use alembic to run it's migration. To use alembic, it needs to be initialized in the project directory. Run

alembic init alembic

Alembic's configurations and versioning will now be contained in a folder alembic.

🎆 Note
initialization of alembic should be run from the virtual environment created and activated earlier in this guide

On successful initialization of alembic, project folder structure should resemble

jwt-fast-api/
├─ alembic/              <-- alembic folder & sub files
   ├─ versions/ 
   ├─ env.py
   ├─ README
   ├─ script.py.mako
├─ models/
   ├─ __init__.py
   ├─ users.py
├─ .env
├─ alembic.ini            <-- just added
├─ main.py
├─ .gitignore
├─ Dockerfile
├─ settings.py
├─ requirements.txt
├─ docker-compose.yml
├─ db_initializer.py

A couple of setup needs to integrated before we can run our first migrations. Update alembic/env.py so it has the following content

from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context

from db_initializer import Base
from settings import DATABASE_URL

from models.users import User

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = Base.metadata

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
config.set_section_option(config.config_ini_section, "sqlalchemy.url", DATABASE_URL)


def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        compare_type=True,
        literal_binds=True,
        target_metadata=target_metadata,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            compare_type=True,
            connection=connection, 
            target_metadata=target_metadata,
        )

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Running Migrations

Alembic autogenerate migrations by watching changes in a model's Base class. With alembic we've backward compatibility with our migrations and can go back to previous migration with a few command.

Alembic is a tool utilized within our application service to interacts with our database. To use it, we'll need access to our application service/container. Each container has got a unique identifier made up of alphanumeric character. Below command would list the running container

docker ps -a

You should have a similar output like the one below. The application container identifier is underlined.

To gain access into the application container use below script

docker exec -it 2a6 sh

🎆 Note
your container's unique identifier output should be different from mine and we only need the first 3 alphanumeric to interact with it. Kindly replace 2a6* with the first 3 alphanumeric of your application container unique identifier*

Run first migration using

alembic revision --autogenerate -m "Create user model"

A successful output should resemble.

🎆 Note
take note of the sha value autogenerated at the last line of the above output. You can find the sha value within alembic/versions/some_sha_value_create_user_table.py

To reflect migrations on our database, we'll run

# kindly use the appropriate sha value as yours will be different from mine
alembic upgrade 66b63a

Password hashing on signup

We'll only be requiring user to provide on sign up, email, password and full name. For this we'll create a pydantic model which would capture these payload. Create a new folder schemas and add within it __init__.py and users.py. Folder structure should be similar to:

jwt-fast-api/
├─ alembic/              
   ├─ versions/ 
   ├─ env.py
   ├─ README
   ├─ script.py.mako
├─ models/
   ├─ __init__.py
   ├─ users.py
├─ schemas/            <-- schemas folder & sub files
   ├─ __init__.py
   ├─ users.py
├─ .env
├─ alembic.ini            
├─ main.py
├─ .gitignore
├─ Dockerfile
├─ settings.py
├─ requirements.txt
├─ docker-compose.yml
├─ db_initializer.py

Open schemas/users.py file and include this content

from pydantic import BaseModel, Field, EmailStr


class UserBaseSchema(BaseModel):
    email: EmailStr
    full_name: str


class CreateUserSchema(UserBaseSchema):
    hashed_password: str = Field(alias="password")


class UserSchema(UserBaseSchema):
    id: int
    is_active: bool = Field(default=False)

    class Config:
        orm_mode = True

There're 3 schema classes above of which two inherits from UserBaseSchema. This inheritance structure is simply to avoid duplication of model fields. So what we've done is to say, the most basic user data that can be public facing are the email and full_name and this is composed in the UserBaseSchema. As we only need full_name, email and password on sign up, there's no need to redefine all those fields in CreateUserSchema as we've already composed the same in UserBaseSchema. Hence why CreateUserSchema inherits UserBaseSchema and added the only required field, that is hashed_password. We aliased hashed_password so it is public facing as password, that is, instead of the api to request that user should provide hashed_password in request body, password will be requested for instead and fastapi will remap the captured password field to hashed_password automatically. UserSchema declares the fields returnable the API. Given hashed_password is a sensitive information we don't want users to have access to, it's deliberately excluded from the schema property. UserSchema has a config subclass which solely defines that the schema would as as an ORM ( would capture data coming from database as if it the real model class ). This is done using orm_mode = True.

We'll include helper functions in User model class to help with password hashing and for password confirmation. Open models/users.py and update the class to include the below methods

# other import statement above

import bcrypt

class User(Base):
        # previous class attributes and methods are here

    @staticmethod
    def hash_password(password) -> str:
        """Transforms password from it's raw textual form to 
        cryptographic hashes
        """
        return bcrypt.hashpw(password.encode(), bcrypt.gensalt())

    def validate_password(self, password) -> bool:
        """Confirms password validity"""
        return {
            "access_token": jwt.encode(
                {"full_name": self.full_name, "email": self.email},
                "ApplicationSecretKey"
            )
        }

    def generate_token(self) -> dict:
        """Generate access token for user"""
        return {
            "access_token": jwt.encode(
                {"full_name": self.full_name, "email": self.email},
                settings.SECRET_KEY
            )
        }

In above code, we used an amazing library bcrypt to handle both password hashing and confirmation.

🏁 Checkpoint
Setup an application secret key as environment variable and replace "ApplicationSecretKey" on models/users.py with the value consumed from the environment variable. NOTE:: if you leave code as is without taking on this task, your code should still run properly

Final step before creating our signup endpoint is to create a database service which would handle all database interactions (DDL, DML, DQL e.t.c) with user model. Create a new folder services, add __init__.py as the folder's only file and db folder as it's only folder. Create __init__.py and users.py file within services/db folder. Your folder structure should now resemble

jwt-fast-api/
├─ alembic/              
   ├─ versions/ 
   ├─ env.py
   ├─ README
   ├─ script.py.mako
├─ models/
   ├─ __init__.py
   ├─ users.py
├─ schemas/            
   ├─ __init__.py
   ├─ users.py
├─ services/            <-- services folder & sub files
   ├─ __init__.py
   ├─ db/                <-- db folder & sub files
       ├─ __init__.py
       ├─ users.py
├─ .env
├─ alembic.ini            
├─ main.py
├─ .gitignore
├─ Dockerfile
├─ settings.py
├─ requirements.txt
├─ docker-compose.yml
├─ db_initializer.py

Update services/db/users.py with the following code

from sqlalchemy.orm import Session
from sqlalchemy import select

from models.users import User
from schemas.users import CreateUserSchema

def create_user(session:Session, user:CreateUserSchema):
    db_user = User(**user.dict())
    session.add(db_user)
    session.commit()
    session.refresh(db_user)
    return db_user

The file only contains one function create_user which does the actual interaction with the database using session object to create a user instance passed down from CreateUserSchema.

🎆 Quick Recap
To get going with password hashing, we added password hashing and validation helper methods to User model just to ensure related behaviors are kept close. We proceeded to creating schema to collect sign up information i.e CreateUserSchema and schema defining user data consumable from the API i.e UserSchema. Lastly we added a database service to interact with the database ( this is just a clean approach for seperation of concerns )

🎆 Quick Recap

To get going with password hashing, we added password hashing and validation helper methods to User model just to ensure related behaviors are kept close. We proceeded to creating schema to collect sign up information i.e CreateUserSchema and schema defining user data consumable from the API i.e UserSchema. Lastly we added a database service to interact with the database ( this is just a clean approach for seperation of concerns )

Update main.py to include a signup endpoint which will utilize all we've done.

# other import statement are above
from fastapi import Body, Depends
from sqlalchemy.orm import Session

from db_initializer import get_db
from models import users as user_model
from schemas.users import CreateUserSchema, UserSchema
from services.db import users as user_db_services

@app.post('/signup', response_model=UserSchema)
def signup(
    payload: CreateUserSchema = Body(), 
    session:Session=Depends(get_db)
):
    """Processes request to register user account."""
    payload.hashed_password = user_model.User.hash_password(payload.hashed_password)
    return user_db_services.create_user(session, user=payload)


# uncompleted login endpoint handler is below

Signup handler uses some foreign bodies:

app.post() which is the decorator indicating request verb takes a new parameter response_model pointing to UserSchema. This is how we define what user should have access to. In this case on successful signup, the fields defined in UserSchema would be returned as response.
signup function signature explicitly define that payload parameter would serve as the expected request body by using Body. Payload is an instance of CreateUserSchema which means all fields defined in it would be expected on signup.
signup function uses dependency injection to create an instance of database session scoped to the lifecycle of the request for which it is created. This is done using Depends(get_db)

As a best practice, before creating the user in the signup request function body, we first have to hash the password using below code.

payload.hashed_password = user_model.User.hash_password(payload.hashed_password)

Rebuild the application docker image and restart the composed services with below script

# rebuilding the docker image
docker build . -t fastapiapp

# restart docker services
docker-compose restart

Signup Exploration

Visit the docs page on http://localhost:8000/docs and you should have a new endpoint for user signup. The below image contains values supplied to create a new user brain.

Once the execute button is clicked on you should get a response containing the details of the new user created. It should be similar to the below image

🏁 Checkpoint
Try creating a new user by using the try it out & execute buttons on the docs. Create users John Doe and Jane Doe. Leave in the comment session if you encounter any challenge

Refactoring Login

A successful user login should return a recognized access token with which restricted endpoints can be accessed with. To standardize the login endpoint, we'll need to capture payload (email and password) supplied in the request body on the login endpoint, confirm if any user of such exists using the given email and verify the password given in the payload is valid for the user. On successful authentication, a JSON token will be returned as a response.

Update schemas/users.py and include below code defining login schema

# previously defined schemas are above

class UserLoginSchema(BaseModel):
    email: EmailStr = Field(alias="username")
    password: str

Update services/users.py and include below code which is a service to retrieve a single user from the database

# previously defined services are above 

def get_user(session:Session, email:str):
    return session.query(User).filter(User.email == email).one()

Below code is the refactored login verifying the existence of the acclaimed user and validating the credentials of the same user when found.

from typing import Dict
from schemas.users import CreateUserSchema, UserSchema, UserLoginSchema


@app.post('/login', response_model=Dict)
def login(
        payload: UserLoginSchema = Body(),
        session: Session = Depends(get_db)
    ):
    """Processes user's authentication and returns a token
    on successful authentication.

    request body:

    - username: Unique identifier for a user e.g email, 
                phone number, name

    - password:
    """
    try:
        user:user_model.User = user_db_services.get_user(
            session=session, email=payload.email
        )
    except:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Invalid user credentials"
        )

    is_validated:bool = user.validate_password(payload.password)
    if not is_validated:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Invalid user credentials"
        )

    return user.generate_token()

Above code utilizes UserLoginSchema from schemas/users.py and Dict class from typings. Exception is raised on failed authentication attempt and an access token is returned on a successful one.

To confirm the refactored login endpoint, visit the auto-generated docs page at http://localhost:8000/docs, you should find out that the login interactive docs now requires a username and password. Provide a valid credential of a user previously created and you should have a successful response with an access token

An invalid credentials when provided should return an access denied response with a message that credentials are invalid

Conclusion

There are more that can be done to strengthen the security of the system such as

token blacklist system
refresh token for renewing expired access tokens
rolling tokens for automatic renewal based on access timeframe
token expiration

e.t.c but we've successfully been able to setup a workable and production grade solution for JSON Web Token with FastAPI.

If you've come this far, I appreciate your time and I hope it was well worth it.

If yncountered any error, kindly drop in the comment section.

Don't forget to SHARE, LIKE & FOLLOW for more

📌 The Extra Mile
Study my Journey to SQLAlchemy ( Practical Guide) . Support with a cup of Coffee

Perfection Undefined

Osazuwa J. Agbonze — Sun, 13 Nov 2022 08:29:40 +0000

Hey my friend from the internet 👋 the journey you're in, there's no location called perfection. In your job there's no position, perfection. Your profession has no certification called perfection. Perfection is undefined.

Freely, I'll tell you - no one is perfect either and that shouldn't be the goal at all. I died the idea/notion of being perfect when I realized perfection is undefined, instead I've embraced Understanding. Understanding that new concept, understanding the base principles of a theory, understanding my field and its market, understanding why I'm in the Journey I'm in.

The beauty of Understanding is that it comes with a realization which in itself is a product of having dealt multiple times with that which you're trying to understand and questioning your process every single time. Trying to be perfect, places pressure on you and makes for vulnerability to imposter syndrome but trying to understand doesn't. Every failed attempt builds on your understanding as you get to see what doesn't work and for every successful attempt, you not only understand the right way to get it done but you also begin to see patterns to simplify the process and still achieve same or an even better result.

Understanding varies for different people, but when what you understand is right, every angle you take it from is right too. This is why two person could both understand a concept and when told to explain, they would use their own words/examples to convey it. The choice of words and example most likely is from the realization gotten when understanding the concept.

When you understand a process, you will follow it a million times easily, you might make some mistakes (imperfect nature of man) but you'll easily identify where that's from and easily fix it. At this point is where mastery comes in. This simply says you've mastered the process and know the behavior gotten/expected from each steps. Understanding a concept to point of mastery takes time, it requires you to get hands on and work it out. With understanding you know when to move on to the next thing, perfection keeps you stuck.

Every industry is changing rapidly and as a software developer, I wake up to meet new update in libraries, frameworks, methodologies and architectures, trying to perfect any of these will take more than eternity.

Pursue understanding instead by getting hands on, give yourself time and not allow failure get the best of you. Perfection is Undefined, breakout !!!

Thank you for being AWESOME

⏩Share with a colleague, friend and your social circle. Help someone breakout from Perfection Jail

❤️Hit the LIKE button and FOLLOW me so you're notified on future posts.

☕ Thanks to my cup of coffee

Image Credit Gerd Altmann

SQLAlchemy Expression Language - Just Enough

Osazuwa J. Agbonze — Mon, 07 Nov 2022 21:45:33 +0000

Image Credit LOBS Arts

Expression language is a succinct way in SQLAlchemy to interacting with database entities using pythonic construct. Lots can be done through expression language, however, in this post, we'll focus on the Just Enough.

We'll see how to SELECT, INSERT, UPDATE and DELETE from database table using SQLAlchemy expression language. SQLAlchemy's expression language sits within Core layer. Item table defined with CORE API construct from previous guide will be used in this journey.

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

If you're following the codebase, a little restructuring was done -> Moved DDL operations in db/models/items.py to db/scripts/items_el.py

We'll be working with db/scripts/items_el.py and main.py file in this guide.

SELECT with SQLAlchemy expression language

SELECT-ing from database falls in Database Query Language (DQL) for which we can either select all available columns or selected few from a table.

For this purpose, SQLAlchemy provides a select function which can be imported from sqlalchemy.sql module. If interest is only in a subset of the table column, select function requires that those columns be explicitly specified as parameter on the function call.

To access table's column, sqlalchemy.Table instances has a .c attribute which is a collection of all columns in a table. e.g Item.c returns a collection of all columns in Item table and to access specific column Item.c.column_name can be used.

select_statement = select(Item.c.name, Item.c.id, Item.c.category, ...)

To retrieve all columns, each column can be explicitly specified (like above snippet) or the table instance can be given as is.

select_statement = select(Item)

db/scripts/items_el.py

class DQL:
    """Encapsulates database query language (DML)"""

    @staticmethod
    def retrieve_all_items():
        """Retrieves all data entries and 
        corresponding columns from Item table.
        """
        with create_connection() as conn:
            return conn.execute(select(Item))

Hype
Ignore the class & function construct from above code, the magic is a simple statement select(Item). Others are for structure while conn.execute() is familiar as we've used it previously.

DQL class is created to encapsulate all select query expressions & database calls. retrieve_all_items function return all Item entries and also all columns.

To retrieve a unique Item entry, an upgrade of chaining .where() construct to the existing select construct is all required.

db/scripts/items_el.py

class DQL:
    """Encapsulates database query language (DML)"""

    # codes from existing methods         

    @staticmethod
    def retrieve_item_by_id(id:int):
        """Retrieves a single item by it's id.

        - id <int> A unique identifier of an item.
        """
        statement = (
            select(Item)
            .where(Item.c.id==bindparam('id', type_=Integer))
        )
        with create_connection() as conn:
            result = conn.execute(statement, {'id': id})
            return result

SELECT statement from above snippet utilizes .where() construct which is passed the id (primary key) column of Item table for specificity.

The little catch is bindparam which simply, is used to specify the value we're expecting from user and the type of the value. In this case we're expecting an id of type Integer. This is passed as a dictionary to the second argument of conn.execute.

bindparam is imported from sqlalchemy.sql

INSERT with SQLAlchemy expression language

Database INSERT statement is used to populate a database table. INSERT is a data manipulation language (DML) clause in SQL. Instances of sqlalchemy.Table class has .insert() method to facilitate data insertion to the respective table. On data insertion, we either specify the column name for which we have data & feed the statement those data as values or serve data for all available columns while factoring available constraints.

Statement Construct Point

Specify table column for which we've data or address all columns
Send data as values to the statement
factor constraints

db/scripts/items_el.py

class DML:
    """Encapsulates database manipulation language (DML)"""

    @staticmethod
    def add_item(name:str, category:str):
        """Adds a single item to Item table"""
        statement = Item.insert().values(name=name, category=category)
        with create_connection() as conn:
            conn.execute(statement)
            conn.commit()

Item.insert().values(name=name, category=category) from above code meets all points outlined for the insert statement construct. Item table has 3 columns ( id, name & category ). id column wasn't specified on above code fragment as it's a primary key which autoincrements and by default self populates. We've only supplied name and category column. We could as well omit category column as it is a nullable column and would default to None if nothing is supplied. It's also possible to utilize bindparam and supply the values as dictionary to the second argument to the conn.execute function.

# every other code above

statement = Item.insert().values(
    name=bindparam('name', type_=String),
    category=bindparam('category', type_=String)
)
with create_connection() as conn:
    conn.execute(statement, {'name': name, 'category': category)

# every other code below

With add_item(...) function, only one Item record can be inserted at a time. SQLAlchemy allows for insertion of multiple items which would translates into execute many statement.

db/scripts/items_el.py

class DML:
    """Encapsulates database manipulation language (DML)"""

    # codes from existing methods 

    @staticmethod
    def add_items(payload:List[Dict[str, str]]):
        """Inserts multiple items to Item table

        - payload <list> new data to be added to
                         Item table. Each dict has
                         key mapped to the Item table
                         and it's corresponding value.
        """
        with create_connection() as conn:
            conn.execute(Item.insert(), payload)
            conn.commit()

add_items function takes a sequence of key-value pair as parameter. This parameter is served as second argument to conn.execute() function call. The insert statement didn't have to specify any columns with .values() and as such, it's expected that the key-value pairs within the sequence payload has all the non-nullable field. In this case just name key value pair would be sufficient as id column can self populate and category column is nullable.

DELETE with SQLAlchemy expression language

With DELETE statement, a single, multiple or all records can be removed from the database table. Instances of sqlalchemy.Table class has a .delete() method which can be used to remove existing records from the table. For specificity, .where() construct is called to identify a record.

db/scripts/items_el.py

class DML:
    """Encapsulates database manipulation language (DML)"""

    # code from existing methods

    @staticmethod
    def delete_item(item_id:int):
        """Deletes an item whose id is passed as a 
        parameter

        - item_id <int> Uniquely identifies an item
                        instance
        """
        with create_connection() as conn:
            statement = Item.delete().where(
                Item.c.id==bindparam('id', type_=Integer)
            )
            conn.execute(statement, {'id': item_id})
            conn.commit()

delete_item function takes item_id parameter to identify the record to be deleted from Item table. Item.delete() converts to the statement: delete all records from Item table. Chaining .where() construct helps to specify the given record to be deleted. In this case, the item id column is used for record lookup.

To remove multiple records, although there're couple of ways this could be achieved, we'll utilize .in_() construct.
.in_( ) construct takes a sequence as an argument. This construct is called on column for which records are to be identified by (e.g id column). If any database record matches with given value in the sequence, such records will be selected for deletion (in this case).

db/scripts/items_el.py

class DML:
    """Encapsulates database manipulation language (DML)"""

    # code from existing methods

    @staticmethod
    def delete_many_items_by_id(id:list):
        """Deletes multiple items with the corresponding
        id
        """
        with create_connection() as conn:
            statement = Item.delete().where(Item.c.id.in_(ids))
            conn.execute(statement)
            conn.commit()

delete_many_items_by_id allows for deletion of records whose id are contained in the ids sequence. Below is a breakdown of the statement.

Item.delete()     # delete records in Item table
.where(           # identified by 
   Item.c.id.in_(ids)   # item id found in ids sequence
)

To delete all records in a database table, pass Item.delete() without .where() construct to conn.execute().

conn.execute(Item.delete())
conn.commit()

Knowing how to perform a complete removal of all entries in a table is crucial but its usecase would be rare going forward in this journey, hence why no function is created for it.

UPDATE with SQLAlchemy expression language

To update existing record, there's need for specificity to avoid the mistake of changing all records with an update meant for just a single record. .where() construct plays a crucial role to avoid such pitfall. UPDATE statement is a data manipulation language (DML).

Instances of sqlalchemy.Table class has .update() method which tells the kind of operation to be performed. To update any value, there's need to be explicit on the column name and the new value by chaining .values() construct.

db/scripts/items_el.py

class DML:
    """Encapsulates database manipulation language (DML)"""

    # code from existing methods

    @staticmethod
    def update_item(item_id:int, data:Dict[str, str]):
        """Updates an existing item

        - item_id <int> Uniquely identifies an item
                        instance

        - data <dict>   Key-value pair with column name
                        as key and the new entry for 
                        column as value.
        """
        with create_connection() as conn:
            statement=Item.update().where(
                Item.c.id==bindparam('item_id', type_=Integer)
            ).values(**data)
            conn.execute(statement, {'item_id': item_id})
            conn.commit()

update_item function takes two parameter, first for identifying record by its id and second parameter which is a key-value pair which would serve as the columns and the new value to make update by. The second parameter is destructured in .values(**data) construct.

Putting It Altogether

Having constructed just enough DML and DQL operations with SQLAlchemy expression language, lets validate the functions and use them within main.py.

Insert Single Record

_main.py_

from db.scripts import queries as q, items_el


if __name__ == "__main__":
    # Add 3 items individually
    # ------------------------

    items_el.DML.add_item(name="Potatoes", category="Meal")
    items_el.DML.add_item(name="Tuna", category="Grocery")
    items_el.DML.add_item(name="PS 5", category="Game")

    # Output available records in the database
    # ----------------------------------------

    results = items_el.DQL.retrieve_all_items()
    for row in results:
        print("{id}) {name:<30s} {category}".format(
                id=row['id'],
                name=row['name'], 
                category=row['category']
            )
        )

Insert Multiple Records Together

_main.py_

from db.scripts import queries as q, items_el


if __name__ == "__main__":
    # Add 4 items together
    # --------------------

    items_el.DML.add_items(
        payload=[
            {'name': 'Addidas ZX 22 Boost', 'category': 'Shoe'},
            {'name': 'Nike Revolution', 'category': 'Shoe'},
            {'name': 'NK Force Dunk', 'category': 'Shoe'},
            {'name': 'Nike Air', 'category': 'Shoe'},
        ]
    )

    # Output available records in the database
    # ----------------------------------------

    results = items_el.DQL.retrieve_all_items()
    for row in results:
        print("{id}) {name:<30s} {category}".format(
                id=row['id'],
                name=row['name'], 
                category=row['category']
            )
        )

Kindly note that the id of every record is outputted, this will be used for DELETE & UPDATE statement.

Delete Single Record

_main.py_

from db.scripts import queries as q, items_el


if __name__ == "__main__":
    # Delete a single item
    # --------------------

    items_el.DML.delete_item(item_id=12) 
    # kindly note that 12 is an id & mightn't correspond to what
    # you have in your output for existing record.


    # Output available records in the database
    # ----------------------------------------

    results = items_el.DQL.retrieve_all_items()
    for row in results:
        print("{id}) {name:<30s} {category}".format(
                id=row['id'],
                name=row['name'], 
                category=row['category']
            )
        )

Delete Multiple Record

_main.py_

from db.scripts import queries as q, items_el


if __name__ == "__main__":
    # Delete a multiple items
    # -----------------------

    items_el.DML.delete_many_items_by_id([19, 20, 21, 22, 23])
    # kindly note that given ids mightn't correspond to what
    # you have in your output for existing record.


    # Output available records in the database
    # ----------------------------------------

    results = items_el.DQL.retrieve_all_items()
    for row in results:
        print("{id}) {name:<30s} {category}".format(
                id=row['id'],
                name=row['name'], 
                category=row['category']
            )
        )

From above code we've specified to delete 5 records with id 19, 20, 21, 22, 23 respectively.

Update a Record

_main.py_

from db.scripts import queries as q, items_el


if __name__ == "__main__":
    # Update single record
    # ---------------------

    items_el.DML.update_item(item_id=12, data={'category': 'Shoe'})

    # Output available records in the database
    # ----------------------------------------

    results = items_el.DQL.retrieve_item_by_id(12)
    for row in results:
        print("{id}) {name:<30s} {category}".format(
                id=row['id'],
                name=row['name'], 
                category=row['category']
            )
        )

Above should output a single record as we've used retrieve_item_by_id() function and specified the id of the record which we're interested in.

Conclusion

SQLALchemy expression language has other interesting constructs such as join, join_from, common table expressions (cte), aggregate functions e.t.c. This guide has uncover the just enough for further explorations.

Thank you for being AWESOME

✍️

Encountered any bugs, typos or concept misinterpretation from this guide ? Let me know in the comment section below.

❤️

Hit the LIKE button and FOLLOW me so you're notified on future discoveries.

☕

A lot is accomplished with a cup of coffee
Codebase on github

Thank you for your time and for staying with me on this one.

DDL on SQLAlchemy Core API

Osazuwa J. Agbonze — Sun, 30 Oct 2022 05:39:41 +0000

Image credit Tobias Hämmer

Previously we saw how to work with database definition language using raw SQL queries on text function provided by SQLAlchemy. In this guide however, we'll see how to simplify the process using SQLAlchemy Core API.

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

Codebase is on github

Codebase Structure Update

We'll update the codebase structure by creating a new folder within db folder called models. Create __init__.py and items.py file within models folder. We'll be working with items.py and main.py file in this guide.

Rewrite Tables with Core API

We've two tables to rewrite and Core API provides classes which would help in their construct. Core API provides MetaData, Table, Column classes for this cause.

db/models/items.py


# ---- Import External Resources
# -------------------------------

import datetime
from sqlalchemy import (
    Table,      Column,     String, 
    Integer,    MetaData,   DateTime,
    ForeignKey, Boolean,    Sequence,
)
from db.core.initializer import engine

# ---- End of Imports
# -------------------


# ---- Construct Tables
# ---------------------

table_meta = MetaData()


Item = Table(
    'item',
    table_meta,
    Column(
        'id', 
        Integer, 
        Sequence('item_id'), 
        primary_key=True
    ),
    Column('category', String(200)),
    Column('name', String(250), nullable=False),
    Column(
        'date_tracked', 
        DateTime, 
        default=datetime.datetime.now
    ),
)


CravedItem = Table(
    'craveditem',
    table_meta,
    Column(
        'id', 
        Integer, 
        Sequence('craveditem_id'), 
        primary_key=True
    ),
    Column('item_id', ForeignKey('item.id')),
    Column('is_satisfied', Boolean(), default=False),
    Column(
        'date_tracked', 
        DateTime, 
        default=datetime.datetime.now
    ),
)

Construct Explanation

An instance of MetaData class was instantiated and stored on variable table_meta. MetaData acts as a container for related database tables and their associated schema construct. An application can have multiple MetaData instances ( in the case of a multi-tenant application where tables belongs to different schema ) but most application will do fine with just one.

When using Core API, table construct must specify a MetaData, explicitly declare Columns and table name. Item and CravedItem table both share similar MetaData instance.

SQLAlchemy provide Table class used to define the structure of a table. In the construct of Item and CravedItem from above snippet, the first argument passed to Table class is the name of the tables respectively.

Table column is defined using the Column class. Parameters passed to Column class are much and we've only used a few. The first parameter passed to all Column call is the column name, followed by the data types e.g Integer, String, Boolean, DateTime. String data type takes a numeric value specifying total allowable characters for the given column. Some named parameters used are:

default: set default value for column
nullable: defines either or not a column would accommodate no (None/Null) value.
primary_key: Set the column which would be used as the unique reference for entries in the table.

Create & Drop Operations With SQLAlchemy Core

Creating Tables

We've successfully define the construct of Item & CravedItem tables in our python code. To send this table definition to the database for creation, we can either have them created individually or run mass creation.

db/models/items.py

# ...          other codebase above

def create_all_tables():
    """Creates all tables that share same metadata"""
    table_meta.create_all(engine)

def create_item_table_with_core():
    """Creates item table"""
    Item.create(engine, checkfirst=True)

def create_craveditem_table_with_core():
    """Creates craved item table"""
    CravedItem.create(engine, checkfirst=True)

create_all_tables function uses table_meta. This is same MetaData instance shared by Item & CravedItem table. It uses .create_all() method to create all attached tables. .create_all() method takes the database engine which the creation will be executed against. By default .create_all() will check if the tables to be created exists and ignore if it exists. So it's safe to run the create_all_tables multiple times.

create_item_table_with_core and create_craveditem_table_with_core function uses the individual creation format. .create() method is called on Item & CravedItem which are Table class instances. .create() method also takes a database engine to identify the database to execute the create statement against.

Additionally we included an optional checkfirst argument and set it to True just to ensure that we don't attempt to create an already existing table.

main.py

from db.scripts.queries import show_all_tables
from db.models.items import (
    create_item_table_with_core,
    create_craveditem_table_with_core,
    create_all_tables,
)

if __name__ == "__main__":
    create_item_table_with_core()
    create_craveditem_table_with_core()
    show_all_tables()

When the above is executed using python main.py, we'll should've our tables created. create_item_table_with_core() and create_craveditem_table_with_core() can both be replaced with create_all_tables() to achieve same result.

WATCHOUT : When using individual table creation, ensure to create all independent tables ( tables without ForeignKey ) first before attempting to create dependent ones. In our case we'll create Item table first before creating CravedItem.

Dropping Tables

When dropping tables, we can also have it done individually or run mass table drop. Same concept with creation applies here too.

db/models/items.py

def drop_all_tables():
    """Drop all tables"""
    table_meta.drop_all(engine)

def drop_item():
    """Drops Item table"""
    Item.drop(engine)

def drop_craveditem():
    """Drops CravedItem table"""
    CravedItem.drop(engine)

Exercise

Import above functions and utilize them to drop existing Item & CravedItem tables in the database.

Conclusion

Unlike the raw approach to DDL in SQLAlchemy from previous guide, DDL Operation with SQLAlchemy Core API doesn't warrant that one be familiar with how to write SQL queries. The only requirement here is, understanding your database tables definitions. With the helper classes from Core API, the construct is pythonic and less error prone.

Thank you for being AWESOME

✍️

Encountered any bugs, typos or concept misinterpretation from this guide ? Let me know in the comment section below.

❤️

Hit the LIKE button and FOLLOW me so you're notified on future discoveries.

☕

A lot is accomplished with a cup of coffee

Thank you for your time and for staying with me on this one.

Database Interaction with SQLAlchemy - Raw DML & DQL

Osazuwa J. Agbonze — Wed, 19 Oct 2022 05:02:25 +0000

Image credit to Игорь Левченко

In this discovery, we'll look at how to perform SQL DML ( CREATE, UPDATE, DELETE ) and DQL ( SELECT ) operations with SQLAlchemy taking a raw ( writing all SQL query ) approach.

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

This journey would solely utilize

db/scripts/queries.py: contains queries to interact with the database
main.py: application entrypoint.

To follow along, clone codebase from github

To understand codebase structure, visit SQLAlchemy with Docker - The Structure.

NOTE
This journey is quite technical and requires basic understanding of SQL. This guide focuses on SQLAlchemy processes to interact with databases.

Data Manipulation Language (DML) : with this language, we're able to interact with data in the database through clauses which allows us to add (CREATE), modify (UPDATE) and trash (DELETE) data records.

Data Query Language (DQL) is used to retrieve data from the database. This uses the SELECT clause.

Using SQL SELECT clause in SQLAlchemy

With SELECT clause, we're able to retrieve data from the database. We previously used this clause to retrieve tables available in our database.

Below code fragment contains a function that runs the SELECT clause to retrieve all records from Item table.

db/scripts/queries.py

# ....         other code are above this function

def retrieve_all_item():
    """Retrieves all records from Item table"""
    with create_connection() as conn:
        results = conn.execute(
            text("SELECT * FROM Item")
        )

        for result in results:
            print(result)

When database is queried/interacted with, there're responses containing the effect of the interaction which are returned as an instance of Result object. In this case, we queried the database to return all records in Item table. The returned records are stored on Result instance variable results, automatically created on execution of SELECT statement. From above code, we iterate over results and output each record with print statement.

Using SQL INSERT clause in SQLAlchemy

We currently do not have any record in the database tables, with the help of SQLAlchemy, we'll execute CREATE clause to add some.

db/scripts/queries.py

# ....         other code are above this function

def insert_item(name:str, category:str):
    """Inserts new record into Item table

    param: name [str] Represents an item name to be added
    param: category [str] Represents an item category
    """
    with create_connection() as conn:
        conn.execute(
            text("INSERT INTO Item (name, category) VALUES (:name, :category)"),
            {'name':name, 'category':category}
        )
        conn.commit()

Above code contains a function that adds a single record to Item table. To add a new record, the data would be collected from a source e.g a human user, automation e.t.c. which will be fed through named parameter

Explaining named parameter

conn.execute(
  text("INSERT INTO Item (name, category) VALUES (:name, :category)"),

  # ... collapsed code
)

SQLAlchemy recommends executing an external source (e.g human user, automation process) data using named parameter syntax denoted with :any_given_name when using textual construct. This is important for many reasons, most importantly, it mitigates against SQL injection. The named parameter from above code snippet are :name and :category

How is named parameter value passed ?

conn.execute( 
  text(...),  # statement within text function are collapsed
  {'name':name, 'category':category} )

To populate named parameters, a dictionary is passed as the second argument for conn.execute() function, where the key maps to the name assigned to the named parameters

main.py

from db.scripts.queries import (
    # ........ other imported functions above
    insert_item,
    retrieve_all_item,
)

if __name__ == "__main__":
    insert_item(name="Tesla Model S", category="Auto")
    retrieve_all_item()

We imported insert_item and retrieve_all_item function and utilize them within if block. Running python main.py from terminal to execute the above code would insert some data to Item table and retrieve the inserted data.

SQLAlchemy can also handle multiple insertion with the INSERT clause. To do this, instead of passing a dictionary when feeding data to named parameter, a list of dictionary would be expected, where each dictionary will represent distinct rows and have keys that map appropriately to the expected named parameters. Add the following code to allow for multiple data insertion.

db/scripts/queries.py

from typing import List, Any, Dict

# ....         other code are above this function


def insert_multiple_items(data:List[Dict[str, Any]]):
    """Allows insertion of multiple records into Item table

    param: data [List] sequence of dictionary where each 
           dictionary represents a record to be added to 
           the db
    """
    with create_connection() as conn:
        conn.execute(
            text("INSERT INTO Item (name, category) VALUES (:name, :category)"),
            data
        )
        conn.commit()

To see multiple insertion in practice, we'll utilize insert_multiple_items function within main.py as shown below.

main.py

from db.scripts.queries import (
    # ........ other imported functions above
    insert_multiple_items,
)


if __name__ == "__main__":
    insert_multiple_items([
        {
            'name': 'Iphone 14 Pro Max',   
            'category': 'mobile'
        },
        {
            'name': 'Pizza',
            'category': 'meal'
        }
    ])
    retrieve_all_item()

Running python main.py, would insert two new records into Item table (making total records to be 3 in Item table), thereafter all records would be outputted. Your result should be similar to that of the image below.

Using SQL UPDATE clause in SQLAlchemy

With an UPDATE clause, existing record can be modified. To update an existing record, the unique identifier for said record is required for explicit identification of the record.

We currently have three (3) records in Item table, we'll update the name value of the first record (with a unique identifier 1) from Tesla Model S to Tesla Starlink.

Below code shows function that processes an update on Item record existing in the database.

db/scripts/queries.py

# ....         other code are above this function

def update_existing_item_name(item_id:int, new_value:Any):
    """Handles the update of an existing record name
    in `Item` table

    param: item_id [int] A unique identifier of the record to be 
           updated.
    param: new_value [Any] Replacement value for previous `name` column value.
    """
    with create_connection() as conn:
        conn.execute(
            text("UPDATE Item SET name=:update_value WHERE id=:id"),
            {'id': item_id,  'update_value': new_value}
        )
        conn.commit()

Utilize this function within main.py by first importing it and then call it in the codebase as show below.

main.py

from db.scripts.queries import (
    # ... there're other import statement above
    update_existing_item_name,
)

if __name__ == "__main__":
    update_existing_item_name(item_id=1, new_value="Tesla Starlink")
    retrieve_all_item()

Running python main.py would update the previous value from "Tesla Model S" -> to Tesla Starlink for record with of
a unique identifier of 1. Note that an error would be raised for an unexistent identifier.

Using SQL DELETE clause in SQLAlchemy

DELETE clause handles removal of existing record from the database. Just like UPDATE clause, to delete a unique record, DELETE clause would require the unique identifier of record to be deleted. Below code shows an example function utilizing DELETE clause.

db/scripts/queries.py

def delete_item(id:int):
    """Removes an existing item record from `Item` table.

    param: id [int] A unique identifier for item to be deleted
    """
    with create_connection() as conn:
        conn.execute(text("DELETE FROM Item WHERE id=:id"), {'id':id})
        conn.commit()

When delete_item function is called within main.py, it deletes record whose id is given as a unique identifier on the function call.

main.py

from db.scripts.queries import (
    # ... there're other import statement above
    delete_item,
)

if __name__ == "__main__":
    delete_item(1)
    retrieve_all_item()

The above should delete an item whose unique identifier is 1 ( 1 is passed to delete_item() function call). If an unexistent identifier is given, an error would be raised.

Conclusion

SQLAlchemy at it's raw state (using text function and writing all queries) provides a very easy to follow process/structure to interacting with the database. There're little to no changes with construct when using DML, DQL, DDL clauses while interacting with the database.

Thank you for being AWESOME

✍️

Encountered any bugs, typos or concept misinterpretation from this guide ? Let me know in the comment section below.

❤️

Hit the LIKE button and FOLLOW me so you're notified on future discoveries.

☕

A lot is accomplished with a cup of coffee

Thank you for your time and for staying with me on this one.

Database Interaction with SQLAlchemy - Raw DDL Tour

Osazuwa J. Agbonze — Thu, 13 Oct 2022 06:06:14 +0000

Image Credit to winterseitler

Code available on Github

In this discovery, we'll use SQLAlchemy to perform fundamental database operations. The database operations performed in this guide will utilize raw SQL query. We'll manually write and run all database operations using SQLAlchemy.

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.

NOTE
This journey is quite technical. Basics understanding of SQL is a requirement. Only shallow touches will be given to SQL query syntax on this guide as our focus is on SQLAlchemy.

Database as a data store supports different languages to construct and interact with the stored data. Below is a brief intro to the languages:

Data Query Language (DQL): This language helps to retrieve stored data e.g. this blog post is stored on a database. If you're reading this post, you've triggered a DQL to the database. SQL SELECT statement is used to query data from the database.
Data Definition Language (DDL): This language constructs the structure to manage data. Without Data Definition Language, there wouldn't be a library to save or retrieve data from. SQL statements for data definition in a database are CREATE, DROP, RENAME, TRUNCATE, ALTER.
Data Manipulation Language (DML): While DDL interacts with structure, DML interacts with the actual data. SQL statement for this language allows for data INSERT, UPDATE, or DELETE query.

Codebase Structure

To setup codebase structure, visit SQLAlchemy with Docker - The Structure

Maintaining codebase structure, we'll create a new folder within db folder and we'll name it scripts. Add two files within db/scripts/, __init__.py and queries.py respectively.

We'll be working with two files in this journey

db/scripts/queries.py: This file will contain all of our database queries with which we'll be interacting with the database.
main.py: We'll use this file to run the program containing database queries.

Case Study

Lets assume, each time we have a craving, we get to track it and update any of the tracked record after the crave has been satisfied.

Table blueprint

ITEM

id	name	timestamp	category
1	Tesla Model S	07-12-2022 00:00:00	auto
2	Iphone 14 Pro Max	03-29-2022 00:00:00	mobile
3	Pizza	01-12-2022 00:00:00	meal

CRAVEDITEM

id	item_id	date_tracked	is_satisfied
1	1	10-12-2022 00:00:00	False
2	2	09-29-2022 00:00:00	True
3	2	10-12-2022 00:00:00	False

There are two tables in the blueprint - Item and CravedItem, each having 4 columns.

Lets use Data Definition Language (DDL) to define these structure where we'll track our records. This structure is called table in database world.

CREATE statement in action

In db/scripts/queries.py add the following code

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

def create_item_table():
    """Creates Item table.

    col: id              Unique identifier - primary key.
    col: timestamp       datetime defaults to current 
                         date and time the item is tracked.
    col: name            Name of item - field is required.
    col: category        Category of the tracked item
    """
    # Creates a connection to the database
    with create_connection() as conn:
        # Utilizes connection instance to execute SQL query 
        conn.execute(
            text(
                '''
                CREATE TABLE Item (
                    id              SERIAL PRIMARY KEY,
                    date_tracked    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    name            VARCHAR(250) NOT NULL,
                    category        VARCHAR(200),
                )
                '''
            )
        )

        # Sends the query to the database
        conn.commit()

The first two lines imports, text from sqlalchemy and create_connection from database initializer that we created at journey's setup.

We define a create_item_table function. This function would create Item table with the exact columns as outlined on the blueprint with the help of connection instance returned by create_connection.

conn.execute( .... )

conn.execute( ... ) is non-autocommitting which means the SQL query taken by which is whatever is in text(...) is not automatically processed. We've to manually process each query execution using conn.commit().

text is a function which takes raw sql query. text function call returns an executable object which will be executed against the database by conn.execute() only when conn.commit is called

In main.py import create_item_table from db/scripts/queries.py with below code

from db.scripts.queries import create_item_table

Replace run_db_select_statement() (from previous guide) with create_item_table(). main.py file should now look like this

# ....               import statements

if __name__ == "__main__":
    create_item_table()

Run python main.py from your terminal, you should have an output similar to what the image below holds

As an experiment, re-run python main.py, this should give you an error as there cannot be two Tables with similar name. Outputted error should be similar to the below.

DROP statement in action

DROP is a critical database clause/statement and should be used with caution. When used, it'll teardown/destroy the entity and whatever is in such entity. We created an Item table structure earlier, executing a DROP statement on that table will not only destroy the structure and make it unavailable, it'll also destroy the records in it.

Open up db/scripts/queries.py file and include the code below which is a new function that executes DROP statement on Item table.

# ....         other code are above this function

def drop_item_table():
    """Delete Item table"""
    with create_connection() as conn:
        conn.execute(text("DROP TABLE Item"))
        conn.commit()

Open up main.py file and import drop_item_table function. Your import statement should now look like

from db.scripts.queries import create_item_table, drop_item_table

Replace create_item_table function call to drop_item_table function like below

# ....         other code are above this function

if __name__ == "__main__":
    # create_item_table()
    drop_item_table()

Open your terminal and run python main.py, this should delete the existing Item table. If you should uncomment create_item_table and comment out drop_item_table, making your code like so

# ....         other code are above this function

if __name__ == "__main__":
    create_item_table()
    # drop_item_table()

Running python main.py now wouldn't give you the duplicate table error we had earlier as the existing table was dropped, instead you should now have the Item table recreated.

ALTER X RENAME statement in action

There will be less fun using RENAME statement if we can't see existing table. Open db/scripts/queries.py and include below code to retrieve (DQL) all available tables that are not autogenerated on database creation.

# ....         other code are above this function

def show_all_tables():
    """Show all available tables in the database

    Returned tables excludes those having their
    schema as `pg_catalog` and `information_schema`.
    """
    with create_connection() as conn:
        results = conn.execute(
            text(
                '''
                SELECT * FROM pg_catalog.pg_tables
                WHERE schemaname != 'pg_catalog' 
                AND schemaname != 'information_schema'
                '''
            )
        )

        for data in results:
            # Only print the table name contained on Index 1
            print(f"{data[1]} Table".title())

Import this function into main.py, and call it within the if block like below

from db.scripts.queries import (
    create_item_table, 
    drop_item_table, 
    show_all_tables
)

if __name__ == "__main__":
    # create_item_table()
    # drop_item_table()
    show_all_tables()

Running python main.py should emit the existing tables in the database

RENAME statement is used to change the name of an existing Table e.g if we want to change Item table name to Commodity, we'll use the RENAME statement.

Open up db/scripts/queries.py and include the following function which will rename existing Item table to Commodity


# ....         other code are above this function

def rename_item_table():
    """Rename Item table if it exists in the database"""
    with create_connection() as conn:
        conn.execute(
            text(
                '''
                ALTER TABLE Item
                RENAME TO Commodity
                '''
            )
        )

        conn.commit()

Now that we have rename_item_table function, import it into main.py and update if block

from db.scripts.queries import (
    create_item_table, 
    drop_item_table, 
    show_all_tables,
    rename_item_table
)

if __name__ == "__main__":
    show_all_tables()
    rename_item_table()
    show_all_tables()

The if block code statement will display the available tables before and after execution of renaming Item table. After running above code using python main.py, we would no longer have a table named Item as it would've been renamed to Commodity.

Run above command using python main.py and you should have similar result

Exercise
Rename the table from Commodity to Item. Do note that we'll be working with Item table for future discoveries, ensure to complete this exercise

NOTE
SQLALchemy doesn't have a general support for ALTER & some other DDL statement

Don't forget me !!!

From our table blueprint, we had two tables, but so far we have only created one.

Open up db/scripts/queries.py and include this function. First function holds query to create CravedItem table and the second function holds query to drop CravedItem table

# ....         other code are above this function

def create_craved_item_table():
    """Create CravedItem table.

    This table keeps tracks of items that are craved for

    col: id              Unique identifier - primary key.
    col: item_id         Item unique identifier - field is
                         required.
    col: date_tracked    datetime defualts to current 
                         date and time the item is tracked.
    col: is_satisfied    Denotes either or not craved item
                         has been satisfied.
    """
    # Creates a connection to the database
    with create_connection() as conn:
        # Utilizes connection instance to execute SQL query 
        conn.execute(
            text(
                '''
                CREATE TABLE CravedItem (
                    id              SERIAL      PRIMARY KEY,
                    item_id         INT         NOT NULL,
                    date_tracked    TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
                    is_satisfied    BOOLEAN     DEFAULT False,
                    CONSTRAINT      fk_item     FOREIGN KEY(item_id) REFERENCES Item(id)
                )
                '''
            )
        )

        # Sends the query to the database
        conn.commit()


def drop_craved_item_table():
    """Delete CravedItem table"""
    with create_connection() as conn:
        conn.execute(text("DROP TABLE CravedItem"))
        conn.commit()

CravedItem table relates to Item table through FOREIGN KEY constraint, because of this, drop_item_table DDL query will fail at execution. This would warrant an update in the DROP statement used in destroying Item table

conn.execute(text("DROP TABLE Item CASCADE"))

I only just showed the part that has been updated. The only difference is the CASCADE that's added

In summary, we've seen how to use CREATE, DROP, ALTER X RENAME DDL Clause to perform database interaction through SQLAlchemy. In addition we've maintained structured codebase by following the principle of Separation of Concern.

SQLAlchemy has more to offer

Although this raw query approach we've just taken has it's place of creating that awareness that this is possible in SQLAlchemy, by no means is this how SQLAlchemy is used in the real world on a day to day basis.

Thanks for sticking with me on this discovery. If you like this, don't forget to Bookmark it for later. Hit the Heart ❤️ button and do well to FOLLOW me so you're notified on the next discovery in this journey.

☕ My Coffee Link

Code available on Github

SQLAlchemy - Hello World

Osazuwa J. Agbonze — Sun, 09 Oct 2022 16:28:36 +0000

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

SQLAlchemy with Docker - Setup

Osazuwa J. Agbonze — Fri, 07 Oct 2022 14:05:14 +0000

Image credit to Sies Kranen

Hi, I found some new discoveries to share with you on docker & SQLAlchemy, should be done before our second round of beer 🍺.

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.

Needed requirements to follow along are, basic knowledge of python, database and docker. HOLD ON, I'll be explaining each step so you should still be able to follow along.

Outline

Docker Installation
Postgres Engine Setup
Database Setup
Getting Started Dependencies Installation

Please note, this is a bit, but it'll be worth it. Do follow along, you might as well discover something new. Yoo, Barkeep !! a bottle of beer for my friend here please.

Docker Installation

Do we actually need docker to journey to SQLAlchemy ? No. The only reason I choose to use Docker, is because, it is fast to setup, lightweight + docker is also famous.

Docker provides platform which helps us deliver software packages in containers. The software package we need from docker is a database, postgres to be precise.

Installing docker is simple, see how to if you don't have it installed, I'll wait for you.

Alright, I'll assume you've already installed docker. Congratulations, you now have access to all software packages available on docker hub and with a simple command, you can make whichever you want available for you just like we'll do now.

Postgres Engine Setup

docker pull postgres:12-alpine

Run above command on your terminal and you should automatically have available for use, a postgres database image. An image is what an executable (those icons you click on your PC) is to desktop but for docker. Now that we have an image, lets launch our postgres program by running below command

docker run --env POSTGRES_USER=admin --env POSTGRES_PASSWORD=password -d -p 5432:5432 postgres:12-alpine

I know, I know !!! an executable file is way more easier to click on, I agree with you too. Lets try to understand what is happening though. Here are the bits and explanation

docker run : is the main command saying we want to run a docker package available on our machine or docker hub.
.
--env POSTGRES_USER=admin --env POSTGRES_PASSWORD=password: Every database needs a user to access it and password to identify that user, so we're making one here
.
-d: d for Disappear - wow but in kindergarten, I was taught d is for Dog. Well Well, this is version 2.0
.
-p 5432:5432: Provisions port for which our database engine will be accessed and we're also making the port accessible to your PC. There are two statement here, hence why we have two 5432 one for each statement respectively. You're free to liken a port to that single section where you fit your phone charger, if it makes the concept clear. Remember only a single charger can fit in there at a time, so it is for systems (another program cannot use 5432 port having provision it to postgres). We just did that by fitting our system to the database engine port 5432:5432
.
postgres:12-alpine: this is the name identifying the docker image we are trying to run

Kindly Note:

For simplicity, I used some concepts for explanation e.g -d is actually for detached mode and not Disappear. When -d is used, what ought to give an output is detached from the output renderer. Disappear simplifies all of these statement I just made to explain detached.

That was a lot. Freely give it another glance, no rush my friend.

To confirm we've an instance of postgres:12-alpine image running, use below command

docker ps -a

You should see a container whose IMAGE column holds value postgres:12-alpine and PORTS column hold values 0.0.0.0:5432->5432/tcp. Give attention to either column, NAME or CONTAINER ID and take note of the values they've got as the below command will be needing either of them.

docker exec -it (NAME value | CONTAINER_ID value ) bash

Running above command will give bash access to the database engine. Kindly replace NAME or CONTAINER_ID with whatever value your postgres container had when your ran docker ps -a, as this is different for everyone. Your command should look like this docker exec -it replace_me_with_the_value bash.

Database Setup

To setup database we'll be working with, we need to access the default database environment using the user details we provided when creating our postgres instance.

psql --username=admin --password

Running above command would activate an interactive interface for which password given to the admin user on creation of postgres instance (which should be password) should be entered.

To create a database, assign a user to it and grant all database privileges to the assigned user ( this is similar to what we did using --env=POSTGRES_USER & --env=POSTGRES_PASSWORD ), run next command

CREATE DATABASE learnsqlalchemy;
CREATE USER learner WITH PASSWORD 'StrongPassword123';
GRANT ALL PRIVILEGES ON DATABASE learnsqlalchemy TO learner;

The above command when run, would give us the following configuration

DATABASE_NAME = learnsqlalchemy

DATABASE_USER = learner

DATABASE_PASSWORD = StrongPassword123

Disclaimer:

This is just a learning process therefore, these values are not to be used in a real project. Kindly ensure to use a stronger value for each setup. Something only you knows

Getting Started Dependencies Installation

To get started, we need some dependencies installed, but before we install them, kindly note we'll be working in the the directory structure setup for this journey. Navigate to the directory (set it up if you haven't) before running any of the below commands, just to keep things organize.

NOTE:

The below commands should be run on a different terminal separate from that used in creating and setting up database. You can as well close the terminal used in setting up database as we're done with that

As a best practice, we'll need an environment to isolate any tools/dependencies we'll install in this journey from those already in our PC or in other future journey to avoid conflict. To do this, python comes to the rescue. Run below command

python3 -m venv env

venv is the program that creates a virtual environment. In this case, we call it env (you could name it whatever pleases you, but we'll keep it as env for this journey).

You've only created the virtual environment, there's need to activate it before proceeding to dependencies installation. Run the first command if you're on windows and second command if you're on Mac/Linux

first command

source env\Script\activate

second command

source env\bin\activate

Now that virtual environment is created and activated, run the below command to install dependencies.

pip3 install psycopg2-binary==2.9.3 SQLAlchemy==1.4.41

The dependencies installed are SQLAlchemy which is our focus point and postgres database adapter, psycopg2-binary, through which, SQLAlchemy can interact with postgres database. SQLAlchemy can interact with MySQL, MS Server e.t.c. For these different databases a different adapter is needed to tell SQLAlchemy which it'll be interacting with.

If you've made it this far, you definitely need another beer, may I place another request ?

We've covered a lot and here's a summary:

We started by installing docker. Docker gives us access to multiple software packages. We installed the postgres:12-alpine package image ( docker pull postgres:12-alpine ) and launched an instance of it with an open port connecting to our PC and provided default user credential ( docker run --env POSTGRES_USER=admin --env POSTGRES_PASSWORD=password -d -p 5432:5432 postgres:12-alpine ). Database for our journey was created by accessing the running postgres instance using the default user credentials given it. We proceeded to creating and activating a virtual environment within which we installed our getting started dependencies.

Thanks for sticking with me on this one. If you observed any errors or have any question, do leave it in the comment.

Like, Follow My Journey and Share it if you find it useful - cheers 🍻 . Ohh !! I will answer you now, yes I take coffee too

SQLAlchemy with Docker - The Structure

Osazuwa J. Agbonze — Thu, 06 Oct 2022 05:37:48 +0000

Image credit to Phoenix Han

I really do not have to say this to get started and do forgive me if you find it appalling, you could also meet me in the second paragraph. But, Ohhh my beer 🍺 !!! I LOVE STRUCTURE. I'm starting with my love for structure because I've ignored this journey for sometime now as I couldn't find a structured way to get started. This really was a pain for me due to my background with ORM ( django ORM to be precise ). The struggle for me was, after I had consumed a getting started resource on sqlalchemy, I couldn't really find a structure to fit it into existing or new project as none of the resource I used could teach me that ( maybe I just couldn't find it, it's a big ocean). I do understand why most resources are like that and every single one deserves their credit honestly but, I do also hope that, with this post, I can show a different approach to getting started by first dealing with my own pain - The Structure ( If you've a different structure, leave a github link in the comment section. I/someone else could learn something - thanks )

If you've not read The Genesis of my Journey to SQLAlchemy, please do.

I could easily just proceed to sqlalchemy installation and how it can be imported into our terminal showing different examples which would portray how simple and flexible it is to use, but no! To begin with, lets define a structure for our codebase in anticipation for the future.

Worthy Of Note:

This journey is solely focused on SQLAlchemy - not flask or any other web framework, we'll keep the focus of our structure to that.

Disclaimer

This structure is by no mean an Opinionated approach on how you should structure your sqlalchemy project or codebase and as such not imposed on anyone as the golden structure. This is my boilerplate for my journey.

There will be three folders in the root directory, so open up VSCode or your favorite IDE/editor and create accordingly.

conf/: This folder will house all application configurations/settings for different environment. There are at least 3 environments for real projects i.e development, staging and production environments. These three environment files/folder can be housed within conf/. We'll leave it as is for now.

db/: This folder will be used to manage database engine initializer and metadata. It will also contain models/ folder when the time comes. We'll see what all of these are as we proceed in this journey.

src/: Application source code will reside here.

main.py: Will be the entry-point into the application.

If you've created the above file and folders, your codebase should look like mine.

The importance of structure cannot be overemphasized especially in programming. Just by defining this structure, we already have a head start which you'll see very shortly in this Journey. Let me know your thought on this structure in the comment section.

Thanks for sticking with me on this one. I'm still holding my beer 🍻

DEV Community: Osazuwa J. Agbonze

From Novice to Ninja: The Django Advantage

Django's Advantage

1) Compelling Documentation :

2) Achieved Early Contentment:

3) Everything Included Formed a Habit in Me:

4) Community:

5) Consistent Resource:

6) Django is built on Python

Resources I Found Helpful

1) Django's Official Documentation

2) Automate the Boring Stuff by Al Sweigart ( Book )

3) Try Django Youtube Series by CodingEntrepreneurs

4) London Developer Youtube

5) Corey Schafer Youtube

6) Unofficial Django Community on Discord

Would I Recommend Developers to Begin with Django ?

Conclusion

API File Upload Done Right - FastAPI

What is Content Type and Why does it matter ?

ACCEPTABLE JSON PAYLOAD WITH FILE FIELD

HOW TO HANDLE FILE UPLOAD WITH PYDANTIC MODEL

CONCLUSION

Implementing Authorization in FastAPI: A Step-by-Step Guide to Securing Your Web Applications

JWT Authentication in FastAPI ( Comprehensive Guide )

Osazuwa J. Agbonze ・ Jan 14 '23

FastAPI authentication scheme setup

Data access protection configuration

Can't find expected update on the Docs ?

Fixing Unprocessable Entity Error

Access authorization

Summary

JWT Authentication in FastAPI ( Comprehensive Guide )

Pre-requisite

Case Study

Virtual Environment & Application Dependencies

Hello Login

Exploring the Docs

Application Docker Image

Database Service Setup

Hide Sensitive Variables

Setup Application Database Usage With SQLAlchemy

Create User Model

Alembic Setup

Running Migrations

Password hashing on signup

Signup Exploration

Refactoring Login

Conclusion

Perfection Undefined

SQLAlchemy Expression Language - Just Enough

SELECT with SQLAlchemy expression language

INSERT with SQLAlchemy expression language

DELETE with SQLAlchemy expression language

UPDATE with SQLAlchemy expression language

Putting It Altogether

Conclusion

DDL on SQLAlchemy Core API

Codebase Structure Update

Rewrite Tables with Core API

Construct Explanation

Create & Drop Operations With SQLAlchemy Core

Creating Tables

Dropping Tables

Exercise

Conclusion

Database Interaction with SQLAlchemy - Raw DML & DQL

Using SQL SELECT clause in SQLAlchemy

Using SQL INSERT clause in SQLAlchemy

Using SQL UPDATE clause in SQLAlchemy

Using SQL DELETE clause in SQLAlchemy

Conclusion

Database Interaction with SQLAlchemy - Raw DDL Tour

Codebase Structure

Case Study

CREATE statement in action

DROP statement in action

ALTER X RENAME statement in action

Don't forget me !!!

SQLAlchemy - Hello World