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', 'xxx@gmail.com', 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 = 'xxx@gmail.com'
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?
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
.