Introduction to SQLAlchemy in Python

SQLAlchemy is a framework that helps you to create connection between your object oriented classes written in Python language and tables represented by your databases.

In this article I want to show how to realize standard operations with ORM like SQLAlchemy using Python and postgresql database:

• creating URI to postgresql database
• creating classes in Python representing your models
• running commands that build tables in database
• inserting / updating / deleting / filtering records in database using SQLAlchemy
• creating relations between tables and quering them
• updating schema of existing tables

However updating schemas for tables in postgresql is possible with sqlalchemy I will present you in next article how to do it better.

Preparing postgresql database

In this section I need to be sure that you are fully prepared before we go deeper into using sqlalchemy with postgresql.

What you need to is to have it installed postgresql database with default database, username and password. For example in this article I will use:

• database name: testDB
• username: postgres
• password: admin

Now I believe you are ready with postgresql database. Let’s go to the next step with python packages.

Installing sqlalchemy and other python libraries

We should go very fastly through this part of prepation. In order to install sqlalchemy package just hit the command in your command line console:

pip install sqlalchemy

As a result you should have something similar to mine.

As you see for the moment current version of sql alchemy is 1.3.22.

But wait there is one more thing to run codes that I prepared for you. At this point you may have an error because of not installed package named psycopg2.

Let’s solve it by install psycopg2 package by hitting the command:

pip install psycopg2

And that’s it. Now I’m sure that you are very well prepared to start using sqlalchemy with postgresql database. In the next paragraph we will go deeper into topic.

Designing postgresql table schema

In this step, we need to prepare the PostgreSQL table schema. It took me some time to find good and simple example for you.

You probably want to learn python sqlalchemy to use with postgresql database but you want to eliminate too complicated table schemas.

I found that simple relation between company and people is not a complex example but still interesting to show how to play with sqlalchemy.

In the presented example we have relation many to many represented by the table employees. The responsibility of the table is to join people with companies.

Defining database uri postgres in sqlalchemy

Now it’s time to define connection to our postgresql database. In sqlalchemy creating database uri is very easy task to do.

Let’s create config.py file with the content like below:

# config.py - database configuration
 
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
engine = create_engine('postgresql://postgres:admin@localhost:5433/testDB')
 
Session = sessionmaker(bind=engine, autoflush=False, expire_on_commit=False)

In the presented code we have standard import declarations for sqlalchemy to get access to:

• database engine – create_engine
• database session – sessionmake
• creating tables using python classes and their fields – declarative_base

But going back to our database uri as you can see that the connection string consists of:

• postgresql prefix – postgresql://
• username and password separeted by color – postgres:admin
• next we have database address separeted by @ – @localhost:5433
• and finally we have database name – testDB

Mapping postgresql tables into object oriented classes

I believe that up to the moment you have working database and you went successfully through previous steps.

We need to go from the simple design of database tables into programming objects that represent columns and their types. Generally we will create three files that I describe below:

• Person.py – with class Person that maps table People
• Company.py – with class Company that maps table Companies
• Employee.py – with class Employee that maps table Employees

Firstly we start from table People. You might be a little bit surprised why we have a table named People and python class has the name Person.

# Person.py file
 
from sqlalchemy import Column, Integer, String, DateTime
from dbconfig import Base
 
class Person(Base):
  __tablename__ = 'people'
 
  id = Column(Integer, primary_key=True)
  full_name = Column(String)
  address = Column(String)
  country = Column(String)
  created_at = Column(DateTime)

We can use any name for the class name but by convention, we use the singular for class name and plural for the table name.

You should ask why we need to import Base into all of the files ? Generally speaking sqlalchemy needs it to properly map classes into tables.

In other words sqlalchemy do all the stuff for us and we only need to focus on describing the columns and their types.

# Company.py file
 
from sqlalchemy import Column, Integer, String, DateTime
from dbconfig import Base
 
class Company(Base):
  __tablename__ = 'companies'
 
  id = Column(Integer, primary_key=True)
  name = Column(String)
  address = Column(String)
  country = Column(String)
  created_at = Column(DateTime)

Full list of columns you can reach from official sqlalchemy documentation about column and datatypes.

The most interesting table is employees. In the python class the table is represented by class name Employee.

The main role of employees table is to join records from companies and people tables.

Here we see that in order to join the two tables we need to create two foreign keys people.id reflected in python by field person_id (yes we changed the name in singular) and companies_id reflected by field company_id.

# Employee.py file
 
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from dbconfig import Base
 
class Employee(Base):
  __tablename__ = 'employees'
 
  id = Column(Integer, primary_key=True)
  card_id = Column(Integer)
  person_id = Column(Integer, ForeignKey('people.id'))
  company_id = Column(Integer, ForeignKey('companies.id'))
  created_at = Column(DateTime)

When you finish typing the code for all three classes then we are ready to go the step with generating tables.

All the stuff in PostgreSQL database will do for us sqlalchemy engine.

How to create schema in database postgresql

You should be proud of yourself but we have last thing to do before we start query tables in postgresql database.

The thing is to run the code responsible for fetching data from defined by us tables in python classes. Below you have some lines of code that you can save in create-tables.py:

# create-tables.py
 
from dbconfig import Base, engine, Session
#from User import User
from Company import Company
from Employee import Employee
from Person import Person
 
Base.metadata.create_all(engine)

Run the script in console line the script by hitting the command:

python create-tables.py

As a result, the script will generate a set of three tables in postgresql database. You don’t need to know any sql commands.

This is the real power of ORM frameworks in programming. The power is very usefull when in the meantime you want to change database from postgresql into another ones.

In such situation you will only change database uri in config.py script and the job we did with mapping tables into python classes will be exactly the same.

How to use sqlalchemy to insert orm objects into postgresql

I propose to start from something simple but enough complicated to present saving objects into postgresql database.

I saved everything into insert-records.py script.

from dbconfig import Base, engine, Session
import datetime
 
from Company import Company
from Employee import Employee
from Person import Person
 
session = Session()
 
date = datetime.datetime.now()
 
# create some companies
ibm = Company('IBM', '1 Orchard Rd, Armonk', 'USA', date)
apple = Company('Apple', 'Infinite Loop, Cupertino', 'USA', date)
microsoft = Company('Microsoft', '1 Microsoft Way Redmond', 'USA', date)
 
session.add(ibm)
session.add(apple)
session.add(microsoft)
 
# create some people
mathiew = Person("Mathiew Pirx", '10 Wall Street, New York', 'USA', date)
kelly = Person("Kelly Jetson", '23 Pennsylvania Avenue, Washington', 'USA', date)
dennis = Person("Deniss Kirkman", '3 Beale Street, Memphisn', 'USA', date)
 
session.add(mathiew)
session.add(kelly)
session.add(dennis)
 
# technically here are sql queries 
# translated from programming objects
session.commit()
session.close()

In the current example you can see that inserting objects into database consists of three phases:

• importing basic sqlalchemy classes and our own classes that represents database’s tables like: employees, companies or people
• creating some instances of Company and Person class for concrete companies and person
• calling actions on session object to commit SQL queries responsible for inserting data into database

I ran the code and results from database are as expected. We have three records in the companies table and next three in people table.

Inserting independent objects into a database using sqlalchemy framework is a relatively easy task. In the next section, we go deeper with querying tables and inserting dependent objects (concerning other tables).

Scenario 1: create new employee with query by id

This scenario is very often used when you develop websites. In most cases you have id of some entities from related tables. You can imagine such link:

http://some-domain.com/employee/create?company_id=1&person_id=2

What we have here are the two ids related to records from companies and people tables. Let’s try to write the code that will create new employee related with proposed ids:

from dbconfig import Base, engine, Session
import datetime
 
from Company import Company
from Employee import Employee
from Person import Person
 
session = Session()
 
date = datetime.datetime.now()
 
# get person with id=2 and company with id=1
person = session.query(Person).get(2)
company = session.query(Company).get(1)
 
# create new employee when person and company exist
if person and company:
    employee = Employee(1, person.id, company.id, date)
    session.add(employee)
 
session.commit()
session.close()

The code is very intuitive. What we do is to get company and person records by ids and if they exist we can create new employee.

You can ask here why you just don’t use directly ids like this way:

employee = Employee(1, 2, 1, date)

The answer is I could do it but then I cannot be sure that both company and person exist in database. In consequence if they are not existing we will receive and error during transaction commit.

You should avoid such situations in your applications also because of possible hacker attacks.