Connecting to Databases Programmatically

I made a queries.json for the queries we ran last time and included the query string, and a brief description of the query in it

{
  "queries": [
    {
      "index": 1,
      "query_string": "SELECT DISTINCT c.course_name FROM course c NATURAL JOIN courses_programs NATURAL JOIN program p NATURAL JOIN tags NATURAL JOIN topic t WHERE p.program_type = 'certificate' AND t.topic_name = 'AI' AND p.duration <= interval '6 months';",
      "description": "All certificate courses on AI with duration less than or equal to 6 months",
      "column_names": ["course_name"]
    },
    {
      "index": 2,
      "query_string": "SELECT DISTINCT c.course_name FROM course c NATURAL JOIN courses_programs NATURAL JOIN program p NATURAL JOIN tags NATURAL JOIN topic t NATURAL JOIN university u WHERE p.program_type = 'certificate' AND t.topic_name = 'AI' AND p.duration <= interval '6 months' AND u.university_name = 'IITKGP';",
      "description": "All certificate courses on AI for less than 6 months by IITKGP",
      "column_names": ["course_name"]
    },
    {
      "index": 3,
      "query_string": "SELECT DISTINCT s.student_name FROM student s NATURAL JOIN courses_students NATURAL JOIN course c WHERE c.course_name = 'GenAI' AND ( EXTRACT(year FROM age(current_date,s.student_dob))::int < 18 OR EXTRACT(year FROM age(current_date,s.student_dob))::int > 60);",
      "description": "Students less than 18 or above 60 who GenAI",
      "column_names": ["student_name"]
    },
    {
      "index": 4,
      "query_string": "SELECT DISTINCT s.student_name FROM student s NATURAL JOIN courses_students NATURAL JOIN course c NATURAL JOIN tags NATURAL JOIN topic t INNER JOIN university u USING(university_id) WHERE s.country <> 'IN' AND t.topic_name = 'AI' AND u.university_name = 'IITKGP';",
      "description": "Non-Indian students in AI from IITKGP",
      "column_names": ["student_name"]
    },
    {
      "index": 5,
      "query_string": "SELECT DISTINCT cn.name FROM country cn INNER JOIN student s ON cn.iso = s.country NATURAL JOIN courses_students NATURAL JOIN courses_teachers NATURAL JOIN instructor i WHERE i.instructor_name = 'Andrew Ng';",
      "description": "Countries of students taught by Andrew Ng",
      "column_names": ["country_name"]
    },
    {
      "index": 6,
      "query_string": "SELECT DISTINCT i.instructor_name FROM instructor i NATURAL JOIN courses_teachers NATURAL JOIN courses_students NATURAL JOIN student s WHERE s.country = 'IN';",
      "description": "Instructors with at least one Indian Student",
      "column_names": ["instructor_name"]
    },
    {
      "index": 7,
      "query_string": "SELECT DISTINCT c.course_name FROM course c NATURAL JOIN courses_students NATURAL JOIN student s WHERE s.student_id IN ( SELECT DISTINCT s.student_id FROM student s NATURAL JOIN courses_students NATURAL JOIN course c WHERE c.course_name = 'GenAI');",
      "description": "Courses taken by students who also took GenAI",
      "column_names": ["course_name"]
    },
    {
      "index": 8,
      "query_string": "SELECT DISTINCT c.course_name FROM course c WHERE NOT EXISTS ( SELECT cs.course_id FROM courses_students cs WHERE cs.course_id = c.course_id AND cs.student_id NOT IN ( SELECT s.student_id FROM student s NATURAL JOIN courses_students NATURAL JOIN course WHERE course.course_name = 'GenAI'));",
      "description": "Courses where all students took GenAI",
      "column_names": ["course_name"]
    },
    {
      "index": 9,
      "query_string": "SELECT c.course_name FROM course c NATURAL JOIN courses_students NATURAL JOIN student s INNER JOIN university u USING(university_id) WHERE u.university_name = 'IITKGP' GROUP BY c.course_name ORDER BY count(s.student_id) DESC LIMIT 1;",
      "description": "Most popular IITKGP course",
      "column_names": ["course_name"]
    },
    {
      "index": 10,
      "query_string": "SELECT s.student_name, avg(cs.evaluation) FROM student s NATURAL JOIN courses_students cs NATURAL JOIN tags NATURAL JOIN topic t WHERE t.topic_name = 'AI' AND s.country = 'IN' GROUP BY s.student_id ORDER BY avg(cs.evaluation) DESC LIMIT 1;",
      "description": "Indian Student with highest average marks in AI",
      "column_names": ["student_name", "avg(evaluation)"]
    }
  ]
}

Connecting to PostgreSQL with Python

Psycopg is the most popular PostgreSQL database adapter for Python.

I installed it with

❯ sudo pacman -S python-psycopg2

I can now execute all my queries from queries.json by first loading them in my program then executing them one by one. The actual connectivity is very simple

conn = psycopg2.connect(<db details>) # establish connection
cursor = conn.cursor()                # to interact with the db
cursor.execute(<query>)               # execute a sql instruction
cursor.fetchall()                     # get the results

import psycopg2
import json
 
def query(index: int, column_names, desc_string: str, query_string: str, cursor) -> None:
    print("====================================================================================")
    print(f"QUERY {index}: {desc_string}")
    print("------------------------------------------------------------------------------------")
    print(f"{query_string}")
    print("====================================================================================")
    print()
    cursor.execute(query_string)
    results = cursor.fetchall()
    for name in column_names:
        print(name, end='\t')
    print("\n----------------------")
    for row in results:
        for col in row:
            print(col, end='\t')
        print()
    print()
 
if __name__ == "__main__":
    conn = psycopg2.connect(
        database='',
        host='',
        user='',
        password='',
        port=''
    )
 
    cursor = conn.cursor()
 
    with open("./queries.json") as INFILE:
        data = json.load(INFILE)
        queries = data['queries']
        for q in queries:
            query(q['index'], q['column_names'], q['description'], q['query_string'], cursor)

Here’s what the output looks like

❯ python connect.py
====================================================================================
QUERY 1: All certificate courses on AI with duration less than or equal to 6 months
------------------------------------------------------------------------------------
SELECT DISTINCT c.course_name FROM course c NATURAL JOIN courses_programs NATURAL JOIN program p NATURAL JOIN tags NATURAL JOIN topic t WHERE p.program_type = 'certificate' AND t.topic_name = 'AI' AND p.duration <= interval '6 months';
====================================================================================
 
course_name	
----------------------
NLP	
 
====================================================================================
QUERY 2: All certificate courses on AI for less than 6 months by IITKGP
------------------------------------------------------------------------------------
SELECT DISTINCT c.course_name FROM course c NATURAL JOIN courses_programs NATURAL JOIN program p NATURAL JOIN tags NATURAL JOIN topic t NATURAL JOIN university u WHERE p.program_type = 'certificate' AND t.topic_name = 'AI' AND p.duration <= interval '6 months' AND u.university_name = 'IITKGP';
====================================================================================
 
course_name	
----------------------
NLP	
 
====================================================================================
QUERY 3: Students less than 18 or above 60 who GenAI
------------------------------------------------------------------------------------
SELECT DISTINCT s.student_name FROM student s NATURAL JOIN courses_students NATURAL JOIN course c WHERE c.course_name = 'GenAI' AND ( EXTRACT(year FROM age(current_date,s.student_dob))::int < 18 OR EXTRACT(year FROM age(current_date,s.student_dob))::int > 60);
====================================================================================
 
student_name	
----------------------
Aragorn	
Gandalf	
Hatsune Miku	
Kaustav Mishra	
Mehul Mathur	
Shikhar Soni	
Teto

Connecting to PostgreSQL with Rust

I tried to learn rust, sorry

I used sqlx for connecting to PostgreSQL. I also used serde for parsing the json

I am new to rust so I could be wrong about a lot of things, but this was a lot more strict than python, I needed to give exact return types for each query (which thankfully were just strings most of the time). I also needed to provide error checking everywhere and ensure the compiler didn’t have anything to cry about

use dotenv::dotenv;
use rust_decimal::Decimal;
use serde::{Deserialize, Serialize};
use sqlx::{Error, PgPool};
use std::env;
use std::fs::File;
use std::io::BufReader;
use std::path::Path;
 
#[warn(dead_code)]
#[derive(Deserialize, Serialize)]
struct Query {
    index: i32,
    query_string: String,
    description: String,
    column_names: Vec<String>,
}
 
fn load_queries_from_json(path: &Path) -> Vec<Query> {
    let file = File::open(path).expect("Path error");
    let reader = BufReader::new(file);
    let queries = serde_json::from_reader(reader).expect("json parse error");
 
    queries
}
 
async fn generic_query(index: i32, q: Query, pool: &PgPool) -> Result<(), Error> {
    let rows: Vec<(String,)> = sqlx::query_as(&q.query_string).fetch_all(pool).await?;
 
    println!("=====================================================");
    println!("Query {}: {}", index, q.description);
    println!("=====================================================\n");
 
    println!("{}", q.column_names[0]);
    println!("---------------------");
    for row in rows {
        println!("{}", row.0,);
    }
    println!("");
 
    Ok(())
}
 
async fn last_query(index: i32, q: Query, pool: &PgPool) -> Result<(), Error> {
    let rows: Vec<(String, Decimal)> = sqlx::query_as(&q.query_string).fetch_all(pool).await?;
 
    println!("=====================================================");
    println!("Query {}: {}", index, q.description);
    println!("=====================================================\n");
 
    for col in q.column_names {
        print!("{}\t", col);
    }
    println!("\n-------------------------------------");
    for row in rows {
        println!("{}\t{}", row.0, row.1);
    }
    println!("");
 
    Ok(())
}
 
#[tokio::main]
async fn main() -> Result<(), Error> {
    dotenv().ok();
 
    tracing_subscriber::fmt::init();
 
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL env var is not set");
    let pool = PgPool::connect(&database_url).await?;
    println!("Successfully connect to database\n");
 
    let path = Path::new("./src/queries.json");
    let queries: Vec<Query> = load_queries_from_json(path);
    let mut i: i32 = 0;
 
    for q in queries {
        i = i + 1;
        if i == 10 {
            last_query(i, q, &pool).await?;
        } else {
            generic_query(i, q, &pool).await?;
        }
    }
 
    Ok(())
}

sqlx always expects the row type to be a tuple so I had to make sure the type was something like

let rows: Vec<(String,)>; // notice the ',' after "String"

I also didn’t have to put my credentials in the plaintext and instead loaded them from a .env file which I could have done in python too, but it was quite easy to do here and I appreciate that.

Here’s the output

2026-01-28T09:35:56.348927Z  INFO sqlx::query: SELECT DISTINCT s.student_name FROM …; rows affected: 9, rows returned: 9, elapsed: 130.923ms
 
SELECT
  DISTINCT s.student_name
FROM
  student s NATURAL
  JOIN courses_students NATURAL
  JOIN course c NATURAL
  JOIN tags NATURAL
  JOIN topic t
  INNER JOIN university u USING(university_id)
WHERE
  s.country <> 'IN'
  AND t.topic_name = 'AI'
  AND u.university_name = 'IITKGP';
 
=====================================================
Query 4: Non-Indian students in AI from IITKGP
=====================================================
 
student_name
---------------------
Aragorn
Gandalf
Hatsune Miku
Imad Farooque
Kaustav Mishra
Kyoko Hori
Shikhar Soni
Tanishq Sura
Teto
 
2026-01-28T09:35:56.553771Z  INFO sqlx::query: SELECT DISTINCT cn.name FROM …; rows affected: 5, rows returned: 5, elapsed: 134.211ms
 
SELECT
  DISTINCT cn.name
FROM
  country cn
  INNER JOIN student s ON cn.iso = s.country NATURAL
  JOIN courses_students NATURAL
  JOIN courses_teachers NATURAL
  JOIN instructor i
WHERE
  i.instructor_name = 'Andrew Ng';
 
=====================================================
Query 5: Countries of students taught by Andrew Ng
=====================================================
 
country_name
---------------------
UNITED STATES
AUSTRALIA
GERMANY
INDIA
UNITED KINGDOM
 
2026-01-28T09:35:56.758507Z  INFO sqlx::query: SELECT DISTINCT i.instructor_name FROM …; rows affected: 5, rows returned: 5, elapsed: 103.028ms
 
SELECT
  DISTINCT i.instructor_name
FROM
  instructor i NATURAL
  JOIN courses_teachers NATURAL
  JOIN courses_students NATURAL
  JOIN student s
WHERE
  s.country = 'IN';
 
=====================================================
Query 6: Instructors with at least one Indian Student
=====================================================
 
instructor_name
---------------------
Pabitra Mitra
Anirudh Verma
Biwas Mitra
Andrew Ng
Pawan Goyal
 
2026-01-28T09:35:56.950298Z  INFO sqlx::query: SELECT DISTINCT c.course_name FROM …; rows affected: 4, rows returned: 4, elapsed: 119.817ms
 
SELECT
  DISTINCT c.course_name
FROM
  course c NATURAL
  JOIN courses_students NATURAL
  JOIN student s
WHERE
  s.student_id IN (
    SELECT
      DISTINCT s.student_id
    FROM
      student s NATURAL
      JOIN courses_students NATURAL
      JOIN course c
    WHERE
      c.course_name = 'GenAI'
  );
 
=====================================================
Query 7: Courses taken by students who also took GenAI
=====================================================
 
course_name
---------------------
GenAI
Infosec
NLP
OSTEP
 
2026-01-28T09:35:57.162465Z  INFO sqlx::query: SELECT DISTINCT c.course_name FROM …; rows affected: 1, rows returned: 1, elapsed: 126.525ms
 
SELECT
  DISTINCT c.course_name
FROM
  course c
WHERE
  NOT EXISTS (
    SELECT
      cs.course_id
    FROM
      courses_students cs
    WHERE
      cs.course_id = c.course_id
      AND cs.student_id NOT IN (
        SELECT
          s.student_id
        FROM
          student s NATURAL
          JOIN courses_students NATURAL
          JOIN course
        WHERE
          course.course_name = 'GenAI'
      )
  );
 
=====================================================
Query 8: Courses where all students took GenAI
=====================================================
 
course_name
---------------------
GenAI
 
2026-01-28T09:35:57.373973Z  INFO sqlx::query: SELECT c.course_name FROM course …; rows affected: 1, rows returned: 1, elapsed: 125.783ms
 
SELECT
  c.course_name
FROM
  course c NATURAL
  JOIN courses_students NATURAL
  JOIN student s
  INNER JOIN university u USING(university_id)
WHERE
  u.university_name = 'IITKGP'
GROUP BY
  c.course_name
ORDER BY
  count(s.student_id) DESC
LIMIT
  1;
 
=====================================================
Query 9: Most popular IITKGP course
=====================================================
 
course_name
---------------------
NLP
 
2026-01-28T09:35:57.548746Z  INFO sqlx::query: SELECT s.student_name, avg(cs.evaluation) FROM …; rows affected: 1, rows returned: 1, elapsed: 86.625ms
 
SELECT
  s.student_name,
  avg(cs.evaluation)
FROM
  student s NATURAL
  JOIN courses_students cs NATURAL
  JOIN tags NATURAL
  JOIN topic t
WHERE
  t.topic_name = 'AI'
  AND s.country = 'IN'
GROUP BY
  s.student_id
ORDER BY
  avg(cs.evaluation) DESC
LIMIT
  1;
 
=====================================================
Query 10: Indian Student with highest average marks in AI
=====================================================
 
student_name	avg(evaluation)	
-------------------------------------
Aditya Prakash	100

I think the output looks nicer in rust :)

notes

Explorer

Database Connectivity

Connecting to Databases Programmatically

Connecting to PostgreSQL with Python

Connecting to PostgreSQL with Rust

Graph View

Table of Contents