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
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.
Top comments (1)
Thanks!