Steps to Query a (PostgreSQL) View with SQLAlchemy

Creating a Database and User

Now let’s start by setting up PostgreSQL by creating a database with a user, and it is granted all required privileges on the created database.

# create a database named demo
CREATE DATABASE demo;
# created a user demouser with password 12345678
CREATE USER demouser WITH PASSWORD '12345678';
# configured client encoding to utf8
ALTER ROLE demouser SET client_encoding TO 'utf8';
ALTER ROLE demouser SET default_transaction_isolation TO 'read committed';
ALTER ROLE demouser SET timezone TO 'UTC';
# grant all required privileges to demouser over demo db
GRANT ALL PRIVILEGES ON DATABASE demo TO demouser;

Creating DB

Set Up a Python Development Environment

Up till here we have got a database user created and configure now let’s configure the virtual environment for development, this step can be skipped but it is always recommended to use a dedicated development environment for each project to avoid dependency clash, this can be achieved using a Python virtual environment.

mkdir gfg
# Move to gfg folder
cd gfg

The name of the folder doesn’t matter, you can name it anything you want and cd (change directory) to go into your newly created directory then run the following command that will create a virtual environment for your project.

to create a virtual environment
python -m venv venv
to activate the virtual environment
.\venv\Scripts\activate

Python File Creation

Connecting to Postgres using Python

In this step, we are going to connect to the `demo` database that we created earlier and create a view name demo_view and then will query it.

Connecting to Postgres using Python can be achieved using the psycopg2 adapter we just installed as shown,

Python3

from sqlalchemy import create_engine
 
# create a database engine with SQLAlchemy and connect to the database server
# url format: dialect+driver://username:password@host:port/database
engine = create_engine('postgresql://demouser:12345678@localhost:5432/demo')
 
# connect to the database and printing connection successful if connected
with engine.connect() as conn:
    print("Connection successful")

Output:

PostgreSQL connection

Creating Required Tables

To query a PostgreSQL view using Python we first need some data to be present in the database as currently it doesn’t

PostgreSQL User change

Before we insert some data into the database we need to create the view by defining it as an SQLAlchemy table, this is demonstrated below,

Python3

from sqlalchemy import create_engine, MetaData, Table, 
Column, Integer, String, DateTime
 
# create a database engine with SQLAlchemy 
# and connect to the database server
engine = create_engine('postgresql:'+
                       '//demouser:12345678@localhost:5432/demo')
 
# create a metadata object
metadata = MetaData()
 
# create a table object for the demo view
demo_view = Table('demo_view', metadata,
                  Column('id', Integer, primary_key=True),
                  Column('name', String),
                  Column('created_at', DateTime)
                  )
 
# create the demo view in the database
metadata.create_all(engine)

Output:

Table Properties

Inserting Data into Postgres

Now that we have got all thing configured and with a demo_view table created let’s insert some data and query the demo_view table which will return a view with the following Python script,

Python3

# insert some data into the demo view
with engine.connect() as conn:
    conn.execute(demo_view.insert(), [
        {'name': 'John', 'created_at': '2021-07-01'},
        {'name': 'Jane', 'created_at': '2021-07-02'},
        {'name': 'Joe', 'created_at': '2021-07-03'}
    ])
 
    # commit the changes
    conn.commit()
 
 
# query the demo view to get name and created_at columns
query = select().select_from(demo_view).with_only_columns(
          demo_view.c.name, demo_view.c.created_at)
# execute the query and print the results
with engine.connect() as conn:
    result = conn.execute(query).fetchall()
    for row in result:
        print(row)

Output:

The Python script above contains 2 sections first in which we are inserting a few data into the demo_view table and then query the same table to return a view with name and created_at columns only. Lastly, it will print all the rows in the view returned from the database,

SQLAlchemy Terminal

If we check the database the data is been inserted into the demo_view table,

SQLAlchemy Terminal

PostgreSQL Query To View with SQLAlchemy

As a software developer, it is a common task to query a PostgreSQL view. Using views which is a virtual table representing the output of a SQL query, is considered to be an efficient way when dealing with a relational database. This article covers how to query a PostgreSQL view using SQLAlchemy in Python. Before directly moving to the demonstration following is an overview of all the tools we will be using.

Prerequisites

A decent understanding of Python and a machine with Python.
Understanding of basic concepts of SQL, PostgreSQL, and SQLAlchemy.
Postgres is installed on your local machine.

Required Modules

pip install psycopg2
pip install sqlalchemy

Tags:

#postgreSQL #Python-SQLAlchemy #Python #python

Query a Postgres View with SQLAlchemy

Steps to Query a (PostgreSQL) View with SQLAlchemy

Creating a Database and User

Set Up a Python Development Environment

Connecting to Postgres using Python

Python3

Creating Required Tables

Python3

Inserting Data into Postgres

Python3

PostgreSQL Query To View with SQLAlchemy

Similar Reads

Contact Us