Pg AutoJoin

tl;dr

Make a generic sql query with auto-join for PostgreSQL towards foreign keys.

pg-autojoin is a Python library that simplifies complex SQL queries on a PostgreSQL database by automatically handling joins. Its primary function is to dynamically generate the necessary JOIN clauses based on foreign keys. This allows you to retrieve data from a source table and all its related tables without manually writing intricate join statements.

Features

1. Automated Join Generation: The library scans your database schema to identify foreign keys and then constructs the appropriate SQL JOIN statements. This is particularly useful for complex schemas where manually tracking all relationships would be tedious.

2. Dynamic Query Building: It lets you build generic queries to explore related data. This is great for data analysis, reporting, or building applications that need to dynamically display data from multiple interconnected tables.

3. Simple Interface: The process is straightforward:

   • Initialize the SqlJoin class with your PostgreSQL credentials.

   • Call get_joins() to see all related tables, or get_joined_query() to get the full SQL query with all the necessary joins.

4. Customization:

   • You can set your own aliases for tables, which helps in creating more readable and manageable queries.

   • You can specify which fields to retrieve from foreign tables, allowing you to tailor the output to your needs.

Usage

from pg_autojoin import SqlJoin

conn = SqlJoin(db="mydb", user="me", password="1234")
# host and port are optional, default to localhost

conn.get_joins(table="res_users")

# list all related tables with a dataframe output
shape: (4, 4)
┌───────────┬─────────────┬─────────────┬────────┐
│ table     ┆ foreign_key ┆ to_table    ┆ column │
╞═══════════╪═════════════╪═════════════╪════════╡
│ res_users ┆ company_id  ┆ res_company ┆ id     │
│ res_users ┆ partner_id  ┆ res_partner ┆ id     │
│ res_users ┆ create_uid  ┆ res_users   ┆ id     │
│ res_users ┆ write_uid   ┆ res_users   ┆ id     │
└───────────┴─────────────┴─────────────┴────────┘

Tips: you may want get a list of dict instead of dataframe:

conn.get_joins(table="res_users", dataframe=False)

Dataset comes from Odoo business app, but you can use it with any PostgreSQL database.

# define your own aliases (it's not a mandatory step)
conn.set_aliases(
    {"res_company": "company", "res_partner": "partner", 'res_users': 'user_'}
)

sql, _ = conn.get_joined_query(table="res_users")
print(sql)

give an auto-joined query output

SELECT user_.* FROM res_users users_
  LEFT JOIN res_company company ON company.id = user_.company_id
  LEFT JOIN res_partner partner ON partner.id = user_.partner_id
  LEFT JOIN res_users user_2 ON user_2.id = user_.create_uid
  LEFT JOIN res_users user_3 ON user_3.id = user_.write_uid

# Optionally, you may want retrieve particulars fields
# from foreign table you may specify before previous command

conn.set_columns_to_retrieve(["name", "ref", "code"])
sql, _ = conn.get_joined_query(table="res_users")
print(sql)

with this result

SELECT company.name AS "company", partner.name AS "partner"
  , partner.ref AS "partner_ref", user_.* 
FROM res_users user_
  LEFT JOIN res_company company ON company.id = user_.company_id
  LEFT JOIN res_partner partner ON partner.id = user_.partner_id
  LEFT JOIN res_users user_2 ON user_2.id = user_.create_uid
  LEFT JOIN res_users user_3 ON user_3.id = user_.write_uid

Use case

You dynamically want to produce dataset with relevant data only from a source table.

Installation

pip install git+https://github.com/bealdav/pg-autojoin.git@main

Roadmap

• Github Actions for CI/CD
• Add unit tests

Final Notes

pg-autojoin is a powerful tool for developers who need to quickly query interconnected data in a PostgreSQL database without getting bogged down in writing complex join logic.

This description has been completed by AI which is a better than me for this kind of task definitively.