How to commit when inserting records by calling Postgres function

I wrote a function to insert record to table person. Row ID is serial (auto-increment)

CREATE OR REPLACE FUNCTION public.add_person(
    name character varying,
    email character varying,
    level integer,
    company_id integer,
    comp_list integer[])
    RETURNS integer as

$BODY$
declare 
    p_id integer;
begin 
    insert into person (name, email, level, company_id, comp_list, status, insert_ts) values ($1, $2, $3, $4, $5, 'Y', now())
    returning person.person_id into p_id;
    return p_id;
end
$BODY$ LANGUAGE 'plpgsql'

If I run this function with sql select * from add_person('xxx', '[email protected]', 1, 3, '{1,2}'), it inserts a record successfully. However when I call this function in Python using SQLAlchemy, record can't get inserted.

engine = create_engine(URL(**settings.DATABASE))
session = scoped_session(sessionmaker(bind=engine))
email = '[email protected]'
company = 1
level = 3
comp_list = '1,2'
args = "'', '" + email + "', " + str(company) + ", " + str(level) + ", '{" + comp_list + "}'"
statement = "select * from add_person({})".format(args)
session.execute(statement)

The statement constructed in Python is exactly the same as the command I run in postgres. But it did not insert record into the table like it is supposed to. No error message at all. The session, engine are configured correctly because all other select queries work.

I also noticed that even though the records can't be inserted using python code. The sequence of primary key did increase. Because when I run the function again in postgres, the row ID skipped.

The behavior is the same if I do the insert with SQLAlchemy session without commit.

def add_person(name, email, company, level, comp_list):
    current_ts = datetime.datetime.now()
    p = Person(name = name,
               email = email,
               company_id = company,
               level = level,
               comp_list = comp_list,
               status = 'Y',
               insert_ts = current_ts)
    session.add(p)
    session.flush()
    return p.person_id

If I run the Python method above, person_id increments but no record is inserted. Only when I change session.flush to the following, the record gets inserted correctly.

    session.add(p)
    session.commit()
    session.refresh(p)
    print(p.person_id)
    return p.person_id

What is the correct way to commit inserts when calling that plsql function?

Solution

Unless you have autocommit on the connection set to True you will need to call the commit() function of the session.

Documentation: http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it

The reason your person_id incremented even without the commit is because it is using a sequence - either your created that explicitly or it was created for you when you defined the column type as SERIAL (or similar). That sequence will increment with or without a commit and does not depend on the successful insertion of a record in the table.

Note that calling a commit is not required for statements only involving SELECT.