I can't find examples using plpg-sql in raw SQL to be executed by sqlAlchemy these were the closest but no plpg-sql:
How to execute raw SQL in Flask-SQLAlchemy app
how to set autocommit = 1 in a sqlalchemy.engine.Connection
I've done research and I'm not sure if this is possible. I'm trying to either INSERT or UPDATE a record and there is no error. It must fail silently because there's no record created/updated in the database and I've explicitly set AutoCommit=True.
Python:
engine = db.create_engine(connstr, pool_size=20, max_overflow=0)
Session = scoped_session(sessionmaker(bind=engine, autocommit=True))
s = Session()
query = """DO $$
declare
ppllastActivity date;
percComplete numeric;
begin
select lastactivity into ppllastActivity FROM feeds WHERE email = :e and courseName=:c and provider = :prov;
IF COALESCE (ppllastActivity, '1900-01-01') = '1900-01-01' THEN
INSERT INTO feeds (email, courseName, completedratio, lastActivity, provider) VALUES (:e, :c, :p, :l, :prov);
ELSEIF ppllastActivity < :l THEN
UPDATE feeds set completedratio = :p,lastActivity = :l WHERE email = :e and courseName = :c and provider = :prov;
END if;
end; $$"""
params = {'e' : item.get('email').replace("'", "''").lower(), 'c' : item.get('courseName').replace("'", "''"), 'p' : item.get('progress'), 'l' : item.get('lastActivity'),'prov' : "ACG" }
result = s.execute(text(query),params)
I'm unable to troubleshoot since it doesn't give me any errors. Am I going down the wrong path? Should I just use psql.exe or can you do plpg-sql in raw SQL with sqlAlchemy?
