The psycopg module (v3) is the postgresql module for Python.

Setup

sudo apt install libpq

And install psycopg via either apt or pip depending on distro.

1

table = "mrflibble"

def example():
  with psycopg.connect("host=reddwarf dbname=flibble user=me password=fluffy") as conn:
    with self.conn.cursor() as cur:
      cur.execute(f"""
        DROP TABLE IF EXISTS {table}
      """)
      cur.execute(f"""
        CREATE TABLE {table} (
          id serial PRIMARY KEY,
          shortname TEXT,
          fullname TEXT)
      """)

Convenience class

import psycopg

class Psql1:
  def __init__(self,host="localhost",user="",password="",dbname=""):
    self.host = host
    self.user = user
    self.password = password
    self.dbname = dbname
  def main(self):
    with psycopg.connect(f"host={self.host} user={self.user} password={self.password} dbname={self.dbname}") as self.conn:
      with self.conn.cursor() as self.cur:
        self.go()
  def go(self):
    print("Need to override go()")

Use via

from jda_psql import Psql1

class App(Psql1):
  def go(self):
    cur = self.cur
    cur.execute("SELECT * FROM mytable LIMIT 10")
    for row in cur.fetchall():
      print(row)

app = App(user="me",password="SuperSecret",dbname="mrflibble")
app.main()