Send query to postgres RDB via python using psycopg2

Question

(Answered below)

I am trying to send a query to a postgres relational db using psycopg2. Below is the code. I keep getting the 'undefined table' error:

E psycopg2.errors.UndefinedTable: relation "person" does not exist

E LINE 4: JOIN person p ON u.person_id=p.id

How can I get around this?

from itertools import starmap
import psycopg2
from psycopg2 import sql

db_conn = psycopg2.connect(db parameters)
db_cursor = conn.cursor()

query_fields = (
            'u.username',
            'p.first_name',
            'p.last_name',
            'ec.employee_code',
            'ec.status'
        )

flds = sql.SQL(', ').join(starmap(sql.Identifier, map(lambda f: f.split('.'), query_fields)))

query_str = sql.SQL(
     """ 
     SELECT {fields} 
     FROM user u 
     JOIN person p ON u.person_id=p.id 
     JOIN employee_contract ec ON ec.person_id=p.id
     WHERE u.username LIKE '%word%'
     """).format(fields=flds)
db_cursor.execute(query_str)
results = db_cursor.fetchall()
for i in results:
    print(i)
db_conn.commit()
db_conn.close()

Thanks in advance. Archie

Answer 1

Resolved it by editing the query string as below:

query_str = sql.SQL(
   """
   SELECT {fields}
   FROM "user" u
   JOIN person p ON {id1} = {id2}
   JOIN employee_contract ec ON {id3} = {id4}
   WHERE u.username LIKE '%word%'
   """).format(fields=flds01, id1=sql.Identifier('u','person_id'), 
               id2=sql.Identifier('p','id'), id3=sql.Identifier('ec','person_id'), 
               id4=sql.Identifier('p','id'))