How can catch the error info when psql command embedded in python code?

Question

The data can be imported in bash console:

psql -U postgres -d sample -c "copy data(f1,f2) from '/tmp/data.txt'  with delimiter ',' "
Pager usage is off.
Timing is on.
COPY 1
Time: 9.573 ms

I remove with delimiter clause to create an error:

psql -U postgres -d sample -c "copy data(f1,f2) from '/tmp/data.txt'  "
Pager usage is off.
Timing is on.
ERROR:  missing data for column "f2"
CONTEXT:  COPY data, line 1: ""x1","y1""
Time: 0.318 ms

All the error info shown on the bash console,i want to catch the error info when psql command embedded in python code:

import os 
import logging
logging_file = '/tmp/log.txt' 
logging.basicConfig(filename=logging_file,level=logging.INFO,filemode='a+')
logger = logging.getLogger("import_data")

sql_string ="""
psql -U postgres -d sample -c "copy data(f1,f2) from '/tmp/data.txt'  " 
"""

try:
    os.system(sql_string)
except Exception as e:
    logger.info(e)

Why the error info can't be written into the log file /tmp/log.txt?How can catch the error info when psql command embedded in python code?

Answer 1

It is likely that the error produced by os.system() is not being captured by the try-block. os.system() can raise an OSError if the command fails, but it is possible that the error is not being raised and caught by the try block.

You can use the subprocess module instead of os.system() to run the command and capture the output and error streams

Try this code:

import logging
import subprocess
sql_string = """ psql -U postgres -d sample -c "copy data(f1,f2) from '/tmp/data.txt'  " """
logging_file = './log.txt'
logging.basicConfig(filename=logging_file, level=logging.DEBUG, filemode='a+')
try:
    result = subprocess.run(['psql', '-U', 'postgres', '-d', 'sample', '-c', 'copy data(f1,f2) from \'/tmp/data.txt\''],
                        stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    if result.returncode != 0:
        raise Exception(result.stderr.decode('utf-8'))
except Exception as e:
    logging.info(e)
    # The below line will help to get traceback of exception.
    # logging.exception(e)