SQLAlchemy session management in a long process

Question

Scenario:

• The .NET-based application server ( Wonderware IAS / System Platform ) contains automation objects that interact with various equipment on the factory floor.
• CPython is hosted inside this application server (using Python for .NET ).
• Automation objects have built-in scripting functions (using a custom language based on .NET). These scripts call Python functions.

Python functions are part of the incomplete execution tracking system on the factory floor. The purpose of the system is to track the created widgets in the process, to ensure that the widgets go through the process in the correct order and check that certain conditions are met in the process. The widget’s history and widget status are stored in a relational database, this is where SQLAlchemy plays a role.

For example, when the widget passes the scanner, the automation software runs the following script (written in the scripting language of the user script):

' wiget_id and scanner_id provided by automation object
' ExecFunction() takes care of calling a CPython function
retval = ExecFunction("WidgetScanned", widget_id, scanner_id);
' if the python function raises an Exception, ErrorOccured will be true
' in this case, any errors should cause the production line to stop.
if (retval.ErrorOccured) then
    ProductionLine.Running = False;
    InformationBoard.DisplayText = "ERROR: " + retval.Exception.Message;
    InformationBoard.SoundAlarm = True
end if;

The script calls the WidgetScannedpython function :

# pywip/functions.py
from pywip.database import session
from pywip.model import Widget, WidgetHistoryItem
from pywip import validation, StatusMessage
from datetime import datetime

def WidgetScanned(widget_id, scanner_id):
    widget = session.query(Widget).get(widget_id)
    validation.validate_widget_passed_scanner(widget, scanner) # raises exception on error

    widget.history.append(WidgetHistoryItem(timestamp=datetime.now(), action=u"SCANNED", scanner_id=scanner_id))
    widget.last_scanner = scanner_id
    widget.last_update = datetime.now()

    return StatusMessage("OK")

# ... there are a dozen similar functions

: SQLAlchemy ? - , . .

:

, :

# pywip/iasfunctions.py
from pywip import functions

def ias_session_handling(func):
    def _ias_session_handling(*args, **kwargs):
        try:
            retval = func(*args, **kwargs)
            session.commit()
            return retval
        except:
            session.rollback()
            raise
    return _ias_session_handling

# ... actually I populate this module with decorated versions of all the functions in pywip.functions dynamically
WidgetScanned = ias_session_handling(functions.WidgetScanned)

: ? session.remove()?

SQLAlchemy - :

# pywip/database.py
from sqlalchemy.orm import scoped_session, sessionmaker

session = scoped_session(sessionmaker())

. :

• , . . .
• . a) - TurboGears. TurboGears. ) IPython. commit/rollback .

( , , . , ?)

Answer 1

, , . -, session.remove().

try:
    try:
        retval = func(*args, **kwargs)
        session.commit()
        return retval
    except:
        session.rollback()
        raise
finally:
    session.remove()

, with:

try:
    with session.registry().transaction:
        return func(*args, **kwargs)
finally:
    session.remove()

, .with_lockmode('update') , .

Answer 2

WonderWare Wonderware Historian, MSSQL sqlalchemy, .

, .