Database basics with python and sqlalchemy

There are a number of tools to manage a database with python. I have found that using sqlalchemy is a great tool to use to do just that.

To get started we need to create a python virtual environment so that we don't install sqlalchemy in the system package environment. You should always install python packages in a virtual environment. Once you have done that we can install sqlalchemy and it's dependencies for a postgresql database.

Install it now with the following:

pip install sqlalchemy psycopg2

Now that we have them installed we can start building out or models to represent our tables and columns in the database.

app/models.py

from sqlalchemy import CHAR, Column, Integer, PrimaryKeyConstraint,
from sqlalchemy.orm import declarative_base

Base = declarative_base()
metadata = Base.metadata

class Cars(Base):
    __tablename__ = 'car'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='car_pkey'),
    )

    id = Column(Integer)
    model = Column(CHAR(255))
    make = Column(CHAR(255))
    engine = Column(CHAR(255))

    def __repr__(self):
        return f"Cars(id={self.id!r}, model={self.model}, "\
               f"make={self.make}, engine={self.engine})"

We have created our model and we need to setup or file to manage our db session and connection. Substitute the db credentials below with your settings as desired. Again this is for a postgresql database that needs to be setup before setting up the db.py file.

app/db.py

from sqlalchemy import create_engine
import app.models

from sqlalchemy.orm import sessionmaker, scoped_session

DATABASE_URI = f"postgresql+psycopg2://{db_username}:{db_password}@{db_host}:{db_port}/{db_name}"

engine = create_engine(DATABASE_URI)
Session = scoped_session(sessionmaker())
session = Session(bind=engine)

Now we have a database session object that we can work with throughout our app that we can call upon when necessary.

Next we need to create all the tables in the database that is already setup. Run the following commands from the python repl.

from app import models
from app import db
engine = db.engine
models.Base.metadata.create_all(bind=engine)

The last command is the one that creates all the tables and columns based on the model objects specified in the app/models.py file.

Now we can query for all the cars and their individual data that they contain.

from app import db
from app import models

def all_cars():
    data = []
    cars = db.session.query(models.Cars).all()
    if len(cars) >= 1:
        for car in cars:
            data.append({'id': car.id,
                         'make': car.make,
                         'model': car.model,
                         'engine': car.engine,})
    return data

As you now can see we have a new method called all_cars that returns a list of dictionaries which represents the individual car's data. We can access the individual car's data via methods assigned by the model itself such as "model".

There will be more to come that will include more advanced queries.