DEV Community: Sapan Ravidas

PostgreSQL With Python3

Sapan Ravidas — Sun, 27 Jun 2021 12:15:47 +0000

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+

-- 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

import psycopg2

connection = psycopg2.connect(
    database="store",
    user="sapan",
    password="lgwmfpsc",
    host="localhost",
    port="5432",
    )

# We need cursor for execution of SQL queries
cursor = connection.cursor()
cursor.execute('''CREATE TABLE store
               (Id INT PRIMARY KEY,
               City TEXT,
               Category TEXT,
               SubCategory TEXT,
               Sales REAL,
               Quantity INT, 
               Discount REAL,
               Profit REAL);'''
            )

connection.commit()
connection.close()

import psycopg2

# I'm passing cinnection object for instant commit
def insert_data(connection, id, city, category, subcategory,
                sales, quantity, discount, profit):
    data_tuple = (id, city, category, subcategory, sales, quantity, discount, profit)

    cursor = connection.cursor()
    cursor.execute('''INSERT INTO store 
                   (Id, City, Category, SubCategory, Sales, Quantity, Discount, Profit)
                   VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                   ''', data_tuple)

    connection.commit()

    # to confirm that data is inserted
    cursor.execute("SELECT * FROM store;")
    results = cursor.fetchall()
    for row in results:
        print(row)


if __name__ == '__main__':
    connection = psycopg2.connect(
        user = "sapan",
        password = "lgwmfpsc",
        database = "store",
        port = 5432,
        host= "localhost"
        )

    insert_data(connection, 1, 'Miami', 'Furniture', 'Chairs', 267.67, 3, 0.2, 35)
    connection.close()

PostgreSQL with SqlAlchemy Core

We will work with a new database flowers.

from sqlalchemy import (create_engine, Table, MetaData,
                        Column, Integer, String, Float)

# the engine allows your apllication to have multiple database connections
# and it manages those connections. From engine with can create a databse 
# connection which can be used to execute database queries on

engine = create_engine('postgres://sapan:lgwmfpsc@localhost/flowers')

# the benifit of connection with 'with' block is- as the block ends
# connection gets closed
with engine.connect() as connection:
    # grap the metadata
    meta = MetaData(engine)

    iris = Table('iris', meta, 
                 Column('id', Integer, primary_key=True), 
                 Column('sepallength', Float), 
                 Column('sepalwidth', Float),
                 Column('petallength', Float),
                 Column('petalwidth', Float)
                 )
    meta.create_all(engine)

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

from sqlalchemy import create_engine
from sqlalchemy import Table, MetaData

engine = create_engine('postgres://postgres:lgwmfpsc@localhost/flowers')

with engine.connect() as connection:
    # grap some metadata
    meta = MetaData(engine)

    # load the data from the database
    iris_table = Table('iris', meta, autoload = True, autoload_with = engine)

    insert_statement = iris_table.insert().values(
        sepallength = 4.2,
        sepalwidth = 3.2, 
        petallength = 1.1,
        petalwidth = 0.1,
        species = 'Iris-verginia'
        )

    connection.execute(insert_statement)

    # reading first 10 entries
    print("reading first 10 entries\n----------------")
    select_statement = iris_table.select().limit(10)
    result_set = connection.execute(select_statement)
    for row in result_set:
        print(row)


    # select using where clause
    print("\nselect using where clause\n-------------------")
    select_statement = iris_table.select().where(iris_table.c.id == 151)
    result_set = connection.execute(select_statement)
    for row in result_set:
        print(row)

    # update
    print("\n update \n-----------------")
    update_statement = iris_table.update().where(iris_table.c.id == 151).values(sepallength = 4.9, petalwidth = 0.3)
    connection.execute(update_statement)

    print("\nselect using where clause\n-------------------")
    select_statement = iris_table.select().where(iris_table.c.id == 151)
    result_set = connection.execute(select_statement)
    for row in result_set:
        print(row)

    # delet statement
    print("\n delete \n--------------")
    delete_statement = iris_table.delete().where(iris_table.c.id == 151)
    result_set = connection.execute(delete_statement)

    # confirm delete
    print("confim delete")
    not_found_set = connection.execute(select_statement)
    print(not_found_set.rowcount)

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.

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('postgres://sapan:lgwmfpsc@localhost:5432/books')

# Classes mapped using the Declarative system are defined in terms of a base class which maintains
# a catalog of classes and tables relative to that base - this is known as the declarative base class.
Base = declarative_base()

class Author(Base):
    __tablename__ = 'authors'

    id = Column(Integer, primary_key=True, autoincrement=True)
    first_name = Column(String(length=50))
    last_name = Column(String(length = 50))

    def __repr__(self):
        return f"<Author(id='{self.id}', first_name='{self.first_name}', last_name='{self.last_name}')>"


class Book(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(length =50))
    n_pages = Column(Integer, nullable=False)

    def __repr__(self):
        return f"<Author(id='{self.id}', title='{self.title}', n_pages='{self.n_pages}')>"


class AuthorBook(Base):
    __tablename__ = 'authorbooks'

    id = Column(Integer, primary_key=True, autoincrement=True)
    author_id = Column(Integer, ForeignKey('authors.id'))  
    book_id = Column(Integer, ForeignKey('books.id'))

    author = relationship("Author")
    book = relationship("Book")

    def __repr__(self):
        return f"<AuthorBook(id='{self.id}', author_id='{self.author_id}', book_id='{self.book_id}')>"


Base.metadata.create_all(engine)

def create_session():
    session = sessionmaker(bind=engine)
    return session()


def add_book(title, n_pages, first_name, last_name):
    book = Book(title = title, n_pages = n_pages)

    session = create_session()
    try:
        existing_author = session.query(Author).filter(Author.first_name == first_name, Author.last_name == last_name).first()
        session.add(book)

        if existing_author:
            session.flush() # we need to book id to be populated to the pairing
            pairing = AuthorBook(author_id = existing_author.id, book_id = book.id)
        else:
            author = Author(first_name = first_name, last_name = last_name)
            session.add(author)
            session.flush()
            pairing = AuthorBook(author_id = author.id, book_id = book.id)

        session.add(pairing)
        session.commit()
        print(pairing)
    except:
        session.rollback()
        raise 
    finally:
        session.close()


if __name__ == '__main__':
    print("Input new book detail after '?':\n------------")
    title = input("titile of the book? ")
    n_pagess = int(input("number of pages in book? "))
    first_name = input("author's first name? ")
    last_name = input("autho's last_name? ")

    add_book(title, n_pagess, first_name, last_name)
    print("done!")

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.

Benfits of stored procedure

imporve database performace
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 pocedure that do supoort 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

\n CREATE PROCEDURE

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);

import psycopg2

if __name__ == '__main__':
    conn = psycopg2.connect(
        database = "flowers",
        user = "postgres",
        password = "lgwmfpsc",
        host = "localhost",
        port = "5432"
        )

    # Since we're moving our commit to the transaction layer (stored procedure)'
    # rather having in application layer
    conn.autocommit = True
    cursor = conn.cursor()

    cursor.execute('''CALL retured_reduce_sepallength(%s, %s)''', (152, 1))
    # callproc is used for postgress functions, not for postgres procedures. But at 
    # the time API has not been updated, so we will use this
    # How to call a function : cursor.callproc('retured_reduce_sepallength', (152, 1))


    conn.close()

from sqlalchemy import create_engine
from sqlalchemy import Table, MetaData

engine = create_engine(
    'postgres://postgres:lgwmfpsc@localhost/flowers',
    isolation_level="AUTOCOMMIT"
    )

# autocommit sometimes a bit tricky. If you want to perform a trasaction in python app 
# layer, you have set this AUTOCOMMIT to false

with engine.connect() as connection:
    meta = MetaData(engine)
    iris_table = Table('iris', meta, autoload=True, autoload_with=engine)

    # connection.execute('COMMIT')
    connection.execute("CALL retured_reduce_sepallength(%s, %s)", (151, 1))

from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine  = create_engine(
    'postgres://postgres:lgwmfpsc@localhost/flowers',
    # isolation_level = "AUTOCOMMIT"
    )
Base = declarative_base(engine)
Base.metadata.reflect(engine)

class Iris(Base):
    __table__ = Base.metadata.tables['iris']

    def __repr__(self):
        return f"""<Iris(id='{self.id}', sepalLength='{self.sepallengthcm}', sepalWidth='{self.sepalwidthcm}', petalLength='{self.petallengthcm}', petalWidth='{self.petalwidthcm}')>"""


if __name__ == '__main__':
    with engine.connect() as connection:
        connection.execute("COMMIT")
        connection.execute("CALL retured_reduce_sepallength(%s, %s)", (150, 1))

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

END

Different Ways to Load Data From CSV Files in PostgreSQL

Sapan Ravidas — Sun, 27 Jun 2021 12:04:15 +0000

Populating data

First of all make sure that your user has SUPERUSER privileges.
You can set this using.

ALTER ROLE <user> WITH SUPERUSER;

Here we'll see three ways to populate our data

using inbuilt csv module
using SQL CALL
using pandas

1. Using CSV module

import psycopg2 
import csv

connection = psycopg2.connect(
    database="store", 
    user="postgres",
    password="lgwmfpsc", 
    host="localhost",
    port="5432", # default post
    )

cursor = connection.cursor()

cursor.execute("DROP TABLE IF EXISTS superstore;")
connection.commit()

create_query = '''CREATE TABLE superstore (
    id INT PRIMARY KEY,
    ship_model VARCHAR(255) NOT NULL,
    segment VARCHAR(255) NOT NULL,
    country VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    state VARCHAR(255) NOT NULL,
    postal_code INT NOT NULL,
    region VARCHAR(255) NOT NULL,
    category VARCHAR(255) NOT NULL,
    subcategory VARCHAR(255) NOT NULL,
    sales FLOAT NOT NULL,
    quantity INT NOT NULL,
    discount FLOAT NOT NULL,
    profit FLOAT NOT NULL
    )
'''

cursor.execute(create_query)
connection.commit()

with open('./SampleSuperstore.csv') as file:
    id = 1
    csv_data = csv.reader(file)
    next(csv_data)
    for row in csv_data:
        row = [id] + row
        row_tuple = tuple(row)
        cursor.execute('''INSERT INTO superstore (id, ship_model, segment, country, city, state, postal_code,
                       region, category, subcategory, sales, quantity, discount, profit)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''', row_tuple)
        id += 1

connection.commit()

cursor.execute("SELECT * FROM superstore LIMIT 10")
print(cursor.fetchall())

connection.close()

Before iterating throught the list next is used to skip the first line of the file as they are headers.

2. Using SQL CALL

import psycopg2
import csv

connection = psycopg2.connect(
    user = 'postgres',
    password = 'lgwmfpsc', 
    host = 'localhost',
    database = 'store', 
    port="5432"
    )

cursor = connection.cursor()

create_query = '''CREATE TABLE superstore (
    id INT(255) NOT NULL AUTO_INCREMENT,
    ship_model VARCHAR(255) NOT NULL,
    segment VARCHAR(255) NOT NULL,
    country VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    state VARCHAR(255) NOT NULL,
    postal_code INT NOT NULL,
    region VARCHAR(255) NOT NULL,
    category VARCHAR(255) NOT NULL,
    subcategory VARCHAR(255) NOT NULL,
    sales FLOAT NOT NULL,
    quantity INT NOT NULL,
    discount FLOAT NOT NULL,
    profit FLOAT NOT NULL,
    PRIMARY KEY(id))
'''



cursor.execute("DROP TABLE IF EXISTS superstore")
cursor.execute(create_query)

q = '''LOAD DATA LOCAL INFILE '/home/sapan/codes/Linkedin-Python/database-python/csv-database/SampleSuperstore.csv'
    INTO TABLE superstore
    FIELDS TERMINATED BY ',' ENCLOSED BY "" (ship_model, segment, country, city, state, postal_code,
    region, category, subcategory, sales, quantity, discount, profit);'''

cursor.execute(q)

connection.commit()

cursor.execute("SELECT * FROM superstore LIMIT 10")
print(cursor.fetchall())

connection.close()

3. Usinng pandas

# using pandas with sqlalchemy

import pandas as pd
from sqlalchemy import Column, Integer, String, DateTime, Float, Boolean
from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('postgres://postgres:lgwmfpsc@localhost/store')

Base = declarative_base()

class SalesReport(Base):
    __tablename__ = 'salesreport'

    id = Column(Integer, primary_key=True)
    country = Column("Country", String(length=50))
    state = Column("State", String(length=50))
    city = Column("City", String(length=50))
    postalcode = Column("Postal Code", String(length=50))

    def __repr__(self):
        return f'''<Salesreport(id='{self.country}', country='{self.country}', state='{self.state}', postal_code='{self.postalcode}')>
    '''    

Base.metadata.create_all(engine)

file_name = './sample_data.csv'
data_frame = pd.read_csv(file_name)

# # the below function call automatically import the data into our database, so we do not
# # to work with the session here
data_frame.to_sql(
    con = engine,
    name = SalesReport.__tablename__, 
    if_exists='append', 
    index = False
    )

# # however if we want to prove the data was imported, we need session here
session = sessionmaker()
session.configure(bind=engine)
s = session()

results = s.query(SalesReport).limit(10).all()
for i, r in enumerate(results):
    print(i, r)
    print()


overall_max = s.query(func.max(SalesReport.postalcode)).scalar
print(overall_max) 

results = s.query(SalesReport).order_by(SalesReport.postalcode.desc()).limit(10)
for row in results:
    print(results)

In this post we only see how to populate our data. If you want to view completed details how to use postgreSQL with python refers to this article.
https://sapanravidas.medium.com/postgres-with-python3-ec3cafc5c98d