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)# execute the query and print the resultswith 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()# execute the query and print the resultswith 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)# execute the query and print the resultswith 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))# execute the query and print the resultswith 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))# execute the query and print the resultswith 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))# execute the query and print the resultswith 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))# execute the query and print the resultswith 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))# execute the query and print the resultswith 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']))# execute the query and print the resultswith 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))# execute the query and print the resultswith 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))# execute the query and print the resultswith 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"))# execute the query and print the resultswith engine.connect() as conn: result = conn.execute(query).fetchall() for row in result: print(row) |
Output :
SQLAlchemy NOT
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
Contact Us