Skip to content

Querying Postgres Tables

In this guide, you will learn how to query using postgres tables while using PyStark

Tip

No need to add sqlalchemy or psycopg2 to requirement.txt as they are dependencies of pystark


Using the in-built functions

PyStark provides some default functions to query postgres tables. These functions allow you to query tables using table name (__tablename__ attribute), that is, a string instead of a class. Therefore, you do not need to import classes.

Note

All the in-built functions are asynchronous and use raw sql.

First thing you need to do is creating an instance of Database class

Tip

It's a good idea to create this instance in a separate module so that it's easily importable.

from pystark.database.sql import Database

db = Database()

Now you can call other functions.

Name Function
all Get All Rows
get Get a Particular Row
count Get Number of Rows
set Set/Update value of a key in a Row
delete Delete a Row

  • Get All Rows
# Get all rows from "users" table as dicts.
async def get_users():
    all_data = await db.get_all_data("users")  # or await db.all("users")
    print(all_data)

  • Get a Particular Row
# Get row using primary key from "users" table.
async def get_user():
    user_id = 500123456  # primary key
    get_data = await db.get("users", user_id)
    print(get_data)

  • Get Number of Rows
# Get number of rows in "users" table.
async def user_count():
    count = await db.count("users")
    print(count)

  • Set/Update value of a key in a Row
# set/update key, value pairs in "users" table.
async def set_data():
    user_id = 500123456  # primary key
    key_to_change = "aim"
    new_value = "programmer"
    # If row doesn't exist, it'll be auto added and committed.
    await db.set("users", user_id, {"key_to_change": "new_value"})
    print("Set")

  • Delete a Row
# Delete a row using primary key from "users" table.
async def delete_user():
    user_id = 500123456
    delete_data = await db.delete("users", user_id)
    print("Deleted")

Using the Regular Way (Session object)

You can query tables using the session object which is the regular way in sqlalchemy. Session objects is available as an attribute to Database class of pystark.database.sql

# import 'Session' object
from pystark.database.sql import Database
# import Python class for respective table
# let's say it is in 'users_sql.py' inside 'database' folder.
from database.users_sql import Users


db = Database()


# This function gives total 'rows', that is total user ids in 'users' table.
def num_users():
    users = db.session.query(Users).count()
    # close session after all queries are made.
    db.session.close()
    return users


# This function returns 'name' and 'aim' for users by using 'user_id'
def get_name_and_aim(user_id):
    query = db.session.query(Users).get(user_id)
    name = query.name  # get name
    aim = query.aim  # get aim
    db.session.close()
    return (name, aim)


# This function sets name and aim for users by using 'user_id'
def set_name_and_aim(user_id, name, aim):
    query = db.session.query(Users).get(user_id)
    query.name = name  # set name
    query.aim = aim  # set aim
    db.session.commit()  # use this after setting anything.
    # Now you don't need to 'db.session.close()' as you used 'db.session.commit()' already.

# Etc
Back to top