SQLAlchemy ORM Connecting to PostgreSQL, from scratch, Create, Fetch, Update and Delete.

This tutorial is indicated to those that have no idea how to do this, it’s really from beginning teaching all the steps in order to do this basic operations, that we call CRUD. Can be use as reference in the future of course, it’s basically a small course, so if you want to understand just one item just search for it.

Let’s assume that you already know about virtual environment, and you have your setup, of course you can chose do not use, beside do not recommend.

We will first of course need to install our “dependencies” in order to be able to import and instantiate.

You will need to install two, following:

pip install sqlalchemy

pip install psycopg2

*And here go a tip, at least for Mac user’s, I faced an issue when try to instantiate my sqlalchemy as he need a “driver” to connect to my postgresql database, missing psycopg2, it’s ok if I could’t install using this pip, I had to use a different one on my virtual environment:

pip install psycopg2-binary

With this on I was able to successful install and run after, as he install the complete “binary” for beginners, but why not it’s just local environment, so, if you face in your Mac virtual environment some problem related to install psycopg2 try this one.

Let’s move to the fun now.

Firs’t of everything i’m imagining that you have some database right? I will not teach how to create one here, but you can find from my other tutorial’s how to do this, try here:

1 — Configuring PostgreSQL in your Mac;

2 — Most important SQL / PostgreSQL that you need to know;

Ok, let’s assume that you have your database, let’s add one table with the name “customer” with columns:

CREATE TABLE customer (
name TEXT,
age INTEGER,
email CHARACTER(255),
address CHARACTER(400),
zip_code CHARACTER(20)
);

Now fulfil with this data for beginning:

INSERT INTO customer(name,age,email,address,zip_code) 
VALUES
('Paul',23,'paul@gmail.com','address from paul','2321LL'),
('Felipe',32,'felipegarcia@gmail.com','address from felipe','3413MS'),
('Teddy',90,'teddy@gmail.com','address from teddy','3423PO'),
('Mark',17,'mark@gmail.com','address from mark','9423MA'),
('David',35,'david@gmail.com','address from david','2341DA'),
('Allen',56,'allen@gmail.com','address from allen','3423PO'),
('James',56,'james@gmail.com','address from james','3423PO');

Doing this we can move to our first class, as the idea here is to show how to use this ORM we will simple have one class that will be the one that will have the most basic command / queries or how people call CRUD.

First of all create a file called a file called database.py and inside create your class called Database and import sqlalchemy, should have this:

Connecting to your database class

Let’s talk a little about what we are doing here, first is that we want to connect to our database, and for this, but only for clarify we basically have two ways of doing this, one is the way that we are doing, by create an “engine” and create a connection.

The second way is by creating a Session, that behind will create a connection too, but it’s not relevant, and we will be back to this topic in the next sample that is about fetch our first data.

With this is already can create your first query so let’s do this.

Let’s create a method that we can pass an argument and request the data that we want:

Here we created a generic way of fetch, for now we are just using the SELECT * FROM that will return “everything” from a specific “filter“, this is just for you understand how it works.

Go to your terminal inside the folder where your python file are, and enter in the “python” mode interpreter, type python and you should have a “terminal” like this:

Entering on python interpreter mode in your terminal

Let’s import our file and instantiate our class / object and set to a variable.

Instantiate our class Database and create the database instance

You will see the message that we have inside our initialiser that our instance was created, now let’s call our method passing as argument which table we want like this:

>>> db.fetchByQyery("customer")

And this should be the result:

Fetching user information from database using our method to fetch by query

Ok, that’s nice, we see how easy is in order to fetch data just using the normal SQL queries, before continue I would like to just mention about a “concept” that we have in the database, that is that we open a connection and should be closed one day.

The importance in understand what is happen is because one simple thing, everything that start should finish right?

We have two ways of doing our connection one is the way that we are doing, that is using connection, it’s really fit’s as we know that is simple, small, and we will handle.

And for this we do not need to close basically, as soon finish our query, this “request” is automatically close, so we do not left a “lock” in the database that we are using.

We could for instance close by our self, just need to do this self.connection.close().

The other way of doing is creating our connection using a session, and most probably when you have many place’s that need to connect to the database you should use this one, I will not go into, but just to let you know, in that scenario work’s like being able to create a “poll” that can create multiple connections, and with this you can handle by use try catch finally the situation when fail, get stuck and close by your self, and it’s indicate to handle this way.

This is just a note / tip because when you plan to use this in a bigger context its important to know this, if you want to know more about the connection it self or session take a look at this links from the documentation:

Connection: https://docs.sqlalchemy.org/en/13/core/connections.html

Session: https://docs.sqlalchemy.org/en/13/orm/session_basics.html

Now let’s move one level, we said that we do intend to do a CRUD environment right, if you don’t know what is, it’s Create Read Update Delete.

Create

First of all, let’s make easier in order to save the data that we want, and we will upgrade latter, for now let’s create an object that have the data that we want to save, our customer.

Our class Customer that is the information that we will handle

I created this in the same file as our database, as for now it’s important the functionality and not the architecture.

Now back to our database class create a method called def saveData(self, customer):

What we want is use the INSERT query, so let’s add this implementation:

Save method using just SQL query

For this first scenario I will show different formats that we can add new data into our database using SQLAlchemy.

First the simple one, that is just our query as you can make the same in your database, important to know here is about when will be comited.

Not digging into too much, for this scenario, using connection SQLAlchemy by default use auto-commit for this transaction, but you can set this to false and commit by yourself, if you would like to know more about, it’s a very small read and with good explanation when is automatic and not and how you use, go to this link:

Autocommit / Transaction: https://docs.sqlalchemy.org/en/13/core/connections.html#using-transactions

Running this method passing your customer you will have a new user for, like this:

Using our created method to save customer information using our object

Now if we look into our database this user will be there, for this we can use the previous method that retrieve all our customers:

db.fetchByQyery("customer")

And you can see as the last one will be your just added customer.

Using our method to fetch we can see that was added

Well done, we just inserted our first data using our method, but if you notice we are not using what is supposed from a ORM library, because if it’s just for write pure SQL queries why do you need a hole library, just to manage the connection?

This was just to show you the basic and what you can do, I will show how to do the same using the “power” of the library, and from that moment on just use what this library can offer.

In order to do this we will need to have more modules imported, add to your file this import statement: from sqlalchemy import MetaData, Table, Column.

Simple introduction, what we will do is describe a database using metadata, this create a container that will represent what we are looking for, for more read here: SQLAlchemy Metadata.

Fetch

Create a method that fetch our user by name:

Method that we can fetch users name that are in our database

Let’s dig into and understand what we are doing

1 — We want a table, and for this we need Metadata, remember that we said that this create a representation from our database.

2 — What we want is a table, so for this we need to have a object Table this object basically we will say which table, that in our case is ‘customer’ and we need to pass our metadata object in order to create our representation of this data, and for simplify we say that we want for now only our Column that has the ‘name’ name.

Running this we have:

Result printing user fetch using our method to fetch by user name

Following the same principle this is how we can ask for all columns that we want:

Fetch all user data information mapping using our Table object and printing

We just set all other columns names that we want.

The same can be applied in order to create your data on database, I will not dig into, but reading the documentation after understand this will be pretty simple, we have possibilities to set some ‘parameters’ should be something like this:

Column('new_item'), Integer, primary_key=True

One next step would be work more close representing our database with an model, and not with object’s with string, as it’s not “strong typed” beside python don’t be, but it’s easier to make mistake, have a variable named would fit better, let’s see how would be a object representation of the same data that we requested but now as a object model, just for curiosity they name this declarative base class.

First of all, in order to do this SqlAlchemy has an requirement, that is this table that you will retrieve data need to have Primary Key, our table does not have one, so create using this command:

ALTER TABLE customer ADD COLUMN id SERIAL PRIMARY KEY;

Now that we have let’s create / adjust our customer model:

Customer object now using SQLAlchemy object Base to map the informations from our database

Here we will need to import one new module called Base, this is the base class that all models should inherit in order to be able to map this object.

We have __tablename__ = 'customer' maybe you have notice that this is the way that I tell which table I want to be this mapping.

For each column I need to declare the type of that variable, this is why we have one for each item that we want, remember, I can chose do not get some item or not map.

And for last we have a method def __repr__(self): this is just a way to have a nice representation from this object when I print.

Let’s move, In order to have this “Base” object we need to “create”, them for this before the declaration of this class do this:

Base = declarative_base()

Now let’s get back to our Database class, and add a new method called fetchAllUsers method.

Fetch all users but now mapping using our custom object

First thing that you notice is that we have less code, and some other differences, let’s dig into each one.

1 — Here we have to use a session, the reason for this is that connection kind create a direct connection to the database, but in order to have this “wrapper” that will “translate” our table into an object we need session for this, so we “bind” our already created connection into a session.

2 — We easily query using our session, the query object has several methods, here we will use all() to get all our customers.

  • If you would like to know what else query can provide you can see in this link: https://docs.sqlalchemy.org/en/13/orm/query.html

Pretty much this, but before run our method we have some new objects that we need to import like Base, Session so let’s import before everything inside, they are this:

from sqlalchemy import MetaData, Table, Column, String, Integer
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

Before run let’s make sure you have the structure looks like this:

This is how you structure should looks like, of course the order of the methods doesn’t matter

Let’s run and see how we are getting this data, using terminal and run python this should be the output for this data:

This is the how should be printed the data from fetch all users

The reason why I moved from create to fetch user as it’s easier to show this differences in a scope that make more sense to not mix between the steps of creation.

But now we have all the bases in order to “get back” and create using the power of the ORM, let’s update our method of creation, and create a new user using this format.

Let’s update our saveData method for this:

Update save data user

This are the steps:

  1. We need to create our session in order to be able to add and commit.
  2. Using our session we use the method .add(customer) passing our customer so this session now what we want to persist, and this model need to inherit from Base.
  3. And at the end we need to “commit” our transaction so it’s persisted in our database.

Let’s try this and see how it works and the result:

Using the updated method in order to save our customer

So what we did was, create our Database instance, create our Customer instance setting the informations that we want to persist and save the data using db.saveData(customer).

As you notice I like to use the python interpreter direct on terminal, I do this as the repetition that helps save / hold the information.

And we can see that was success saved:

Database with our just saved data

Update

Definitely this is one of important steps right, you just have your data and now what you want is update. I would say that now, from this moment all the changes you will see the simplicity using this ORM framework, we create our path to understand how it works and now we can use this in our benefit.

Let’s see the code that we will use to update and dig into:

Method to update customer inside our database

Step by step:

1 — We have in our constructor the customerName that we are expecting, it’s suppose to be string, here we are not doing validations about type, next one is the address that we will update, as soon we match the user we will update using this parameter.

2 — Create the data to be update and set to a variable, to be easier to handle and be more readable, it’s a dictionary that we need to pass the “key” that in this scenario is Customer.address and the value that is the address parameter.

3 — We are using query object to look for our Customer, we will filter by our customerName parameter that we are passing.

4 — It’s the update object using update(dataToUpdate), for me I prefer to separate, first we filter and find the user, and set to a variable, after we call the update method and pass the data that we want to update, but all could had being done in the same query.

5 — At the end it’s just call our commit() method in order to “publish” this change, and this is it.

Using your terminal you will have something like this in order to call this method:

Calling the update method passing the data to update

This method we are not printing anything, but looking into our database you can see that the value was update:

Updated table with our last update information to the ‘Felipe Test’ customer

Delete

Now it’s basically our final part in this context, it’s a long road, but indeed we had to “construct” our knowledge in order to understand why we use some different ways.

This is step it’s more straight for, we have the knowledge about use custom object’s and this will play a important role and make our life even more simple, let’s go to the code, let’s create our delete function, we will say the name and according should be deleted:

Method to delete customer

From everything that you have learned you can see how simple it’s right? Let’s dig a little about:

1 — In the scenario the parameter customer I’m passing the name of the customer, so I’m handle as string.

2 — We first fetch the user data, now that we have the object that we map our data we do multiple thing:

1 — We ask for query(Customer);

2 — We filter using the customer name, using filter(Customer.Name==customer);

3 — We ask for the first() data, this because this query return actually a statement, try print to see what get back without first() option;

3 — After retrieving the right data, we call the session.delete(customerData) and ask for the deletion.

4 — Finally we commit the transaction.

You can run now like this:

Calling the delete method

Done, this simple, for now we are not printing nothing, but you can just make a select from you database and you will see that was deleted!

Final considerations

This is it, we did a long “journey”, but as the purpose was indeed teach from the beginning many things was necessary, this is in order to help you that never had any contact create your mind set, understand the process why is being using what we achieve, the easiest ways that the ORM can provide.

It’s important for you try as much you can, that’s why I use the terminal, it’s slow, but make me type many time the same thing, this helps in your learning process, don’t try to speed up in this stage, you need to save this bases information, after you really feel confident, than you can look for IDE or any other tool that improve your coding.

I hope you liked, and if you have any comment please share with me, want to ask for something, let me know, If from here you did some code, let me know too, share your code, there’s no bad code or “junior” code, share what you did, you may help other people sharing your code!!!

Please share as most as possible as this help me reach more people and continue writing, found any mistake? Get in touch let’s help together!

Twitter: @dr_nerd

Linkedin: https://www.linkedin.com/in/felipeflorenciodev/?locale=en_US