Jonnie Grieve Digital Media: Blog

Blog: Getting to grips with the basics of SQLAlchemy (More Posts)

A Short Introduction

I created this post which is based on a video by Treehouse which is also based on the official SQLAlchemy Documentation. So the chances are you’re not seeing much new to you here. I’m doing this to try and make sure what I’m doing sticks. I started messing around with SQLAlchemy last week and got into a roadblock with trying to do this in a Python Virtual Environment.  I couldn’t and it was too long before I discovered that strictly speaking, you didn’t need to.  So when I saw it working it was for me, an exciting moment.

So I’m now about to explain how to create a Database Schema at its most basic level. The following code can be written in a file for example like models.py.

Creating a SQLAlchemy Schema with Python Code

SQL Alchemy requires a few imports in order to work and files in order to work.

First of all, make sure you have the latest version of Python, PIP, and SQLAlchemy available to use in your console and terminal.

Secondly, you need to you have a requirements.txt file in your project’s root directory. This is particularly useful for version control so GIT knows which dependencies and their versions the project is using. A bit like a package.json file for node projects.

Once that is done, you can begin coding your project. At the top like any OOP projects, you need to define your project imports.

from sqlalchemy import create_engine, Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base

SQLAlchemy uses a method called create_engine() to create a connection and look for SQL commands that create a schema. The rest of the imports define the available SQL data types to the project.

The other funny line can be quite a bit to remember but essentially what is happening is we are mapping the model we are going to create to the database so that  SQLAlchemy recognises it as a database schema.

So now we have the Database imports ready. What happens next is we create the database, which we do by assigning the create_engine() method to a variable.

# create db
engine = create_engine('sqlite:///users.db', echo=True)

# This base, maps our model(s) to the database.
Base = declarative_base()

We pass in 2 arguments. A reference to the database file we’re going to create and a second argument to make sure what is happening is being logged to your console or terminal.

We then assign the code that does the database mapping to a Class variable.

class User(Base):

To create the schema, all we need to do is write a simple python class with the class variable passed in as an argument.

So as the example I’m writing a model called user by making a User class.

Each table obviously requires a name, some columns of data with data types, and needs a primary key. The following example shows how to set all of this.

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

We start by setting a __dunder__ special attribute with a string value. I used the example “users”.

We then add as many attributes as you require for your Column names passing in the Data Types, (as imported earlier in your code, and where necessary your table primary key.

Next, we can add a method called __repr__ which is used as an aid to help us see the columns in the console via a formatted string.  We add it as part of the User class we just made.

e.g.

 def __repr__(self):
        # ED Note: not a multiline string in code
        return f'<User(name={self.name}, fullname={self.fullname}, nickname="{self.nickname})>'

This is the method that finally creates the schema (our database table.) We call Basemeta data and pass in the engine variable we created at the top of our code.

# -----> Create the Table
if __name__ == '__main__':
    Base.metadata.create_all(engine)

And that is how we can create a simple database schema using SQLAlchemy.  There’s a lot more to learn about SQLAlchemy of course. A Database needs data, you often need to link tables with each other with foreign keys, etc and I did gloss over a little bit about making sure your console is set up properly for using it but that is the nuts and bolts needed in code.

You need

• Your imports
• Your mapping methods
• Your class to create your schema
• Your creation method to write and apply your SQL commands with SQLAlchemy