How to commit when inserting records by calling Postgres function

Question

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?

Please do not use string formatting for passing values to queries. That's what placeholders/bindparams are for. Read docs.sqlalchemy.org/en/latest/core/… for "how" and xkcd.com/327 for "why". — Ilja Everilä
– Ilja Everilä, Commented Dec 3, 2017 at 11:56
You don't even need textual SQL here. Function generics would allow you to do execute(func.add_person('', email, company, level, comp_list.split(',')).select()). Note that you could and should pass comp_list as just that, a list. — Ilja Everilä
– Ilja Everilä, Commented Dec 3, 2017 at 12:03

Nick · Accepted Answer · 2017-12-03 08:15:37Z

2

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.

answered Dec 3, 2017 at 8:15

Nick

7,5012 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to commit when inserting records by calling Postgres function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related