Postgres With Python3

In this post we’ll see how we can interact with our postgres database using python3 application in three different ways ie. psycopg2, SqlAlchemy core and SqlAlchemy ORM. We’ll also look, how we can use Stored Procedure for our transactions.

To access the databases in python, we often use the given database’s python modules. These modules can have completely different syntax how we interact with them. But since there are so many database systems, then Python created a common specification to follow so the programmers can manage their own databases. These specifications is called the python database API.

Each module must implement a connect function that returns a connection object. These returned connection object is a connection to the database

- connection = connect(parameters…)

- connection.commit()

- connection.rollback()

- connection.rollback()

Postgres

In postgres, data is organized into tables and also known for highly extensible and standard compliant. It is also object relational database system, including advanced features like table inheritances and function overloading.

Adheres more closely to SQL standards than MySQL.

Like MySQL, Postgres follows a client server model, so we need a driver to interact with the database — use Postgres shell or a Postgres GUI.

First thing first install PostgreSQL in your system. https://www.postgresql.org/

Then start PostgreSQL in your system. I’m using using ubuntu-20.0. In my case:

$ sudo systemctl start postgresql@13-main
$ sudo -u postgres psql

Now our server starts and running, before we create database we need to create a role that we can use to access the database in our python program. PostgreSQL lets you grant permissions directly to the database users. However, as a good practice, it is recommended that you create multiple roles with specific sets of permissions based on application and access requirements. Then assign the appropriate role to each user. The roles should be used to enforce a least privilege model for accessing database objects.

Users, groups, and roles are the same thing in PostgreSQL. The only difference is that being that users have permission to log in by default. The roles are used only to group grants and other roles. This role can then be assigned to one or more users to grant them all the permissions.

CREATE ROLE <user> WITH LOGIN PASSWORD <password>;
ALTER ROLE <user> WITH CREATEDB;
expample:
CREATE ROLE postgres WITH LOGIN PASSWORD 'lgwmpsc';
ALTER ROLE postgres WITH CREATEDB;
-- In this case we have provided a privilege to create databases to the specific user.
-- Note: If you're creating role with name other than 'postgres' you have to create database with the same name.
-- We can see the list of users or roles with syntax
\du 
or 
\du+ 

Login to PostgreSQL with user and password.

-- To view the list of databases use
\list
-- To connect to the database use 
\c <database-name>
-- to view the list of the table in that particular database
\dt

PostgreSQL with psycopg2 module

pip install psycopg2

Now we’ll see how we can connect with our database using python

PostgreSQL with SqlAlchemy Core

We will work with a new database flowers.

In above we see, how we can create a blank relation in our database.

Now we’ll learn see how we can perform SQL queries in the relation which is already created.

I’ll populate the data in the relation from csv file. For different methods of populating data click here.

PostgreSQL with SqlAlchemy ORM

The SQLAlchemy Object Relational Mapper (ORM) presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables.

The Foreign Key in AuthorBooks model is for the relations in the database and ‘relationship’ is for the Models in our Python application. Both are need for the connection in our relations.

Stored Procedure

A drawback of user-defined functions is that they cannot perform transactions.

PostgreSQL 11 introduced stored procedures that support transactions.

When working with databases in python you may want to call a stored procedure associate wit your database to extract database operations.

A stored procedure is prepared SQL code that usually consists of several SQL statements that are saved and reused.

Benefits of stored procedure

• improve database performance
• Separate database and application functionality
• Provide a common interface
• Before postgres 11, the only way to create a stored procedure was with a Postgres functions
• function don not support transactions
• With postgres 11 and up we create procedure that do support transactions

Lets create a stored procedure in Postgres

• We’ll use postgres shell, not python
• stored procedure are associated with database, not application.
• our python application just call the stored procedure.

make sure you have stored procedure functionality in your dbms using

CREATE OR REPLACE PROCEDURE <name-of-procedure> (<data-type>, <data-type>) LANGUAGE <language> SET ......
example:
CREATE OR REPLACE PROCEDURE retured_reduce_sepallength(INT, REAL) LANGUAGE plpgsql 
AS $$ 
BEGIN 
UPDATE iris 
SET sepallengthcm = sepallengthcm - $2, petalwidthcm = petalwidthcm + $2  
WHERE id = $1; 
COMMIT;
END;
$$;

AS separates the heading and the body of the stored procedure.

$$ indicates the start of the body procedure.

Before, we write the implementation of the procedure, it’s good practice to surround the implementation with begin and end, so its treated as transaction.

Call the stored procedure

CALL retured_reduce_sepallength(154, 1.2);

You can run this scripts and simultaneously check the data in postgres shell.

END