PostgreSQL Query To View with SQLAlchemy
Last Updated :
24 Apr, 2025
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
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
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
engine = create_engine('postgresql:'+
'//demouser:12345678@localhost:5432/demo')
metadata = MetaData()
demo_view = Table('demo_view', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('created_at', DateTime)
)
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
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'}
])
conn.commit()
query = select().select_from(demo_view).with_only_columns(
demo_view.c.name, demo_view.c.created_at)
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
Query a Postgres View with SQLAlchemy
Querying a database view refers to finding the rows of the view that satisfy a specified condition. Following are the different ways to query a database view:
Querying a database view using the Group By Clause
The Group By clause groups the rows in the view according to the selected columns. The following query shows the number of rows for each name in demo_view.
Python3
query = select().select_from(demo_view).with_only_columns(
demo_view.c.name,
func.count(demo_view.c.name)
).group_by(demo_view.c.name)
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
The above query will return the number of rows in demo_view by name.
SQLAlchemy Group BY
Querying a database view using the distinct keyword
The distinct keyword returns distinct rows in the view. The following query returns unique names within the demo_view.
Python3
query = select().select_from(
demo_view).with_only_columns(
demo_view.c.name).distinct()
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
The above query returns unique names within the demo_view.
SQLAlchemy Distinct
Querying a database view using the Order By clause
The Order By clause orders the rows in the view based on the specified columns. The following query returns rows from demo_view sorted by the created_at column.
Python3
query = select().select_from(
demo_view).order_by(
demo_view.c.created_at)
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
The above query returns rows from demo_view sorted by the created_at column.
SQLAlchemy ORDER BY
Querying a database view using the Sum function
The Sum function returns the sum of the values ​​in the specified column. The following query returns the sum of values ​​in the id column of demo_view.
Python3
query = select().select_from(
demo_view).with_only_columns(
func.sum(
demo_view.c.id))
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
The above query will return the sum of the values ​​in the id column of the demo_view.
SQLAlchemy SUM
Querying a database view using the Avg function
The Avg function returns the average of the values ​​in the specified column. The following query returns the average of the id column values ​​in demo_view.
Python3
query = select().select_from(
demo_view).with_only_columns(
func.avg(demo_view.c.id))
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
The above query will return the average of the id column values ​​in demo_view.
SQLAlchemy AVG
Querying a database view using the Count function
The Count function returns the count or the number of rows present for that particular column or attribute from the view.
Python3
query = select().select_from(
demo_view).with_only_columns(
func.count(demo_view.c.id))
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
The above query returns the number of rows in demo_view.
SQLAlchemy COUNT
Querying a database view using the Min function
The Min function provided by sqlalchemy returns the least or the minimum value for a specific attribute from all the rows for the column specified. The following is a demonstration of a query implemented using the Min function on the id column,
Python3
query = select().select_from(
demo_view).with_only_columns(
func.min(demo_view.c.id))
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
The above query will return the minimum value of the id column in demo_view.
SQLAlchemy MIN
Querying a database view using the Max function
The Max function same as Min but exactly opposite returns the maximum value of the attribute for the column specified in the query. The following query returns the maximum value of the id column in demo_view.
Python3
query = select().select_from(
demo_view).with_only_columns(
func.max(demo_view.c.id))
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
The above query will return the maximum value of the id column in demo_view.
SQLAlchemy MAX
Querying a database view using the `in` keyword
The “in” keyword returns rows in the view whose values ​​in the specified column match any of the values ​​in the specified list. The following query returns rows in demo_view whose name column values ​​match the values ​​in the specified list.
Python3
query = select('*').select_from(
demo_view).where(
cast(
demo_view.c.name, String
).in_(['John', 'Jane']))
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
The above query returns rows in demo_view whose name column value matches any value in the given list.
SQLAlchemy CAST
Querying a database view using the ‘and’ keyword
The keyword ‘and’ returns rows in the view that match all the specified conditions. The following query returns rows in demo_view that have name column values ​​that match the specified value and id column values ​​that match the specified value.
Python3
query = select().select_from(
demo_view).where(
and_(demo_view.c.name == "John", demo_view.c.id == 1))
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
The above query returns rows in demo_view that have name column values ​​that match the specified value and id column values ​​that match the specified value.
SQLAlchemy AND
Querying a database view using the ‘or’ keyword
The ‘or’ function returns rows in the view that match any of the specified conditions the same as the logical or operator. The following query will return rows in demo_view that have either the name column value equals “John” or the id column value equals 2,
Python3
query = select('*').select_from(demo_view).where(
(demo_view.c.name == "John") | (demo_view.c.id == 2)
)
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output:
SQLAlchemy OR
Querying a database view using the ‘not’ keyword
The ‘not’ keyword returns rows in the view that do not match the specified criteria. The following query returns rows in demo_view that do not have a name column value that matches the specified value.
Python3
query = select('*').select_from(demo_view).where(
not_(demo_view.c.name == "John")
)
with engine.connect() as conn:
result = conn.execute(query).fetchall()
for row in result:
print(row)
|
Output :
SQLAlchemy NOT
Similar Reads
PostgreSQL ILIKE query with SQLAlchemy
The ILIKE is a pattern-matching approach provided by PostgreSQL. It is similar to the LIKE operator but it simply ignores the case. It is case-sensitive. For more understanding, you can visit this article on ILIKE operators. In this article, we will cover about PostgreSQL ILIKE query with SQLAlchemy
4 min read
Connecting PostgreSQL with SQLAlchemy in Python
In this article, we will discuss how to connect PostgreSQL with SQLAlchemy in Python. In order to connect with any Database management system, it is essential to create an engine object, that serves as a central source of connection by providing a connection pool that manages the database connection
3 min read
SQLAlchemy ORM - Query
In this article, we will see how to query using SQLAlchemy ORM in Python. To follow along with this article, we need to have sqlalchemy and anyone database installed in our system. We have used the MySQL database for this article's understanding. Created a Profile table and a Students table: Here we
10 min read
Sqlalchemy Core With Text SQL For Date Range
SQLAlchemy Core is a low-level SQL abstraction layer of SQLAlchemy, a popular Python Object Oriented Mapping(ORM) library. It provides a way to interact with relational databases wing python code, allowing developers to write SQL Queries in a more object-oriented manner. SQLAlchemy is a python libra
2 min read
Python SQLAlchemy - Performing union with three queries
In this article, we will see how to perform a union of three queries using SQLAlchemy in Python. Since we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none of the code implementations changes with change in the database except for the SQL co
4 min read
How to count rows with SELECT COUNT(*) with SQLAlchemy?
In this article, we will see how to select the count of rows using SQLAlchemy in Python. Since we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none of the code implementations changes with change in the database except for the SQL connectors
3 min read
How to use sum and order by in SQLAlchemy query?
In this article, we are going to see how to perform the sum and count function in SQLAlchemy against a PostgreSQL database in python. SUM and count operations are performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In PostgreSQL, Gr
3 min read
SQLalchemy Bulk insert with one to one relation
When working with databases in Python, SQLAlchemy is a popular and powerful library that provides a high-level interface for interacting with relational databases. It offers an Object-Relational Mapping (ORM) layer that allows developers to work with database tables as Python objects. In this articl
5 min read
How to get specific columns in SQLAlchemy with filter?
In this article, we will see how to query and select specific columns using SQLAlchemy in Python. For our examples, we have already created a Students table which we will be using: Selecting specific column in SQLAlchemy based on filter:To select specific column in SQLAlchemySyntax: sqlalchemy.selec
2 min read
SQLAlchemy Core - Multiple Tables
SQLAlchemy is an open-source library that provides a set of tools for working with relational databases. It offers a high-level Object-Relational Mapping (ORM) interface as well as a lower-level SQL Expression Language (Core) interface. SQLAlchemy Core is a lightweight and flexible SQL toolkit that
4 min read