How do I make this plpythonu stored procedure insert to database?

Question

I'm trying to read lines from stdin, and insert data from those lines into a PostgreSQL db, using a plpythonu stored procedure.

When I call the procedure under Python 3, it runs (consuming a serial value for each line read), but stores no data in the db. When I call the same procedure from psql, it works fine, inserting a single line in the db.

For example:

Action: Run SELECT sl_insert_day('2017-01-02', '05:15'); from within psql as user jazcap53
Result: day inserted with day_id 1.

Action: Run python3 src/load/load_mcv.py < input.txt at the command line
Result: nothing inserted, but 2 serial day_id's are consumed.

Action: Run SELECT sl_insert_day('2017-01-03', '06:15'); from within psql as user jazcap53
Result: day inserted with day_id 4.

file: input.txt:

DAY, 2017-01-05, 06:00
DAY, 2017-01-06, 07:00

Output:

('sl_insert_day() succeeded',)  
('sl_insert_day() succeeded',)  

I'm running Fedora 25, Python 3.6.0, and PostgreSQL 9.5.6.

Thank you very much to anyone who can help me with this!

---

Below is an MCV example that reproduces this behavior. I expect my problem is in Step 8 or Step 6 -- the other Steps are included for completeness.

The Steps used to create the MCV:

Step 1) Create database:

In psql as user postgres,
CREATE DATABASE sl_test_mcv;

Step 2) Database init:

file: db/database_mcv.ini

[postgresql]
host=localhost
database=sl_test_mcv
user=jazcap53
password=*****

Step 3) Run database config:

file: db/config_mcv.py

from configparser import ConfigParser

def config(filename='db/database_mcv.ini', section='postgresql'):
    parser = ConfigParser()
    parser.read(filename)
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {} not found in the {} file'.format(section, filename))
    return db

Step 4) Create table:

file: db/create_tables_mcv.sql

DROP TABLE IF EXISTS sl_day CASCADE;

CREATE TABLE sl_day (
    day_id SERIAL UNIQUE,
    start_date date NOT NULL,
    start_time time NOT NULL,
    PRIMARY KEY (day_id)
);

Step 5) Create language:

CREATE LANGUAGE plpythonu;

Step 6) Create procedure:

file: db/create_procedures_mcv.sql

DROP FUNCTION sl_insert_day(date, time without time zone);

CREATE FUNCTION sl_insert_day(new_start_date date, 
    new_start_time time without time zone) RETURNS text AS $$
from plpy import spiexceptions
try:
    plan = plpy.prepare("INSERT INTO sl_day (start_date, start_time) \
            VALUES($1, $2)", ["date", "time without time zone"])
    plpy.execute(plan, [new_start_date, new_start_time])
except plpy.SPIError, e:
    return "error: SQLSTATE %s" % (e.sqlstate,)
else:
    return "sl_insert_day() succeeded"
$$ LANGUAGE plpythonu;

Step 7) Grant privileges:

file: db/grant_privileges_mcv.sql

GRANT SELECT, UPDATE, INSERT, DELETE ON sl_day TO jazcap53;  
GRANT USAGE ON sl_day_day_id_seq TO jazcap53;

Step 8) Run procedure as python3 src/load/load_mcv.py < input.txt:

file: src/load/load_mcv.py

import sys 
import psycopg2
from spreadsheet_etl.db.config_mcv import config

def conn_exec():
    conn = None
    try:
        params = config()
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        last_serial_val = 0 
        while True:
            my_line = sys.stdin.readline()
            if not my_line:
                break
            line_list = my_line.rstrip().split(', ')
            if line_list[0] == 'DAY':
                cur.execute('SELECT sl_insert_day(\'{}\', \'{}\')'.
                            format(line_list[1], line_list[2]))
                print(cur.fetchone())
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

if __name__ == '__main__':
    conn_exec()

Answer 1

Do conn.commit() after cur.close()