Python 3 psycopg2 COPY from stdin failed: error in .read()

user_13074835 0
Viewed 1061

I am trying to apply the code found on this page, in particular part 'Copy Data from String Iterator' of the Table of Contents, but run into an issue with my code.

Since not all lines coming from the generator (here log_lines) can be imported into the PostgreSQL database, I try to filter the correct lines (here row) using itertools.filterfalse like in the codeblock below: 

def copy_string_iterator(connection, log_lines) -> None:
with connection.cursor() as cursor:
    create_staging_table(cursor)
    log_string_iterator = StringIteratorIO((
        '|'.join(map(clean_csv_value, (                    
                row['date'],
                row['time'],
                row['cs_uri_query'],
                row['s_contentpath'],
                row['sc_status'],
                row['s_computername'],
                ...
                row['sc_substates'],
                row['s_port'],
                row['cs_version'],
                row['c_protocol'],
                row.update({'cs_cookie':'x'}),
                row['timetakenms'],
                row['cs_uri_stem'],
            ))) + '\n')
        for row in filterfalse(lambda line: "#" in line.get('date'), log_lines)
    )
    cursor.copy_from(log_string_iterator, 'log_table', sep = '|')

When I run this, cursor.copy_from() gives me the following error:

QueryCanceled: COPY from stdin failed: error in .read() call
CONTEXT:  COPY log_table, line 112910

I understand why this error happens, it is because in the test file I use there are only 112909 lines that meet the filterfalse condition. But why does it try to copy line 112910 and throw the error and not just stop?

python-3.7 itertools psycopg2 generator
1 Answers

Since Python doesn't have a coalescing operator, add something like:

(map(clean_csv_value, (  
    row['date'] if 'date' in row else None,
    :
    row['cs_uri_stem'] if 'cs_uri_stem' in row else None, 
 ))) + '\n')

for each of your fields so you can handle any missing fields in the JSON file. Of course the fields should be nullable in the db if you use None otherwise replace with None with some default value for that field.

user_158119740
Related