CODEWITHSUNDEEP
python-3.xsqlalchemypyramid
Thornhale
Thornhale

Reputation: 2376

I cannot save rows to the DB with SQLAchemy + Pyramid

After hours of debugging, and because my organization does not have a lot of Python expertise, I am turning to this community for help.

I am trying to follow this tutorial with the goal of committing some data to the database. Although no errors get reported, I am also not saving any rows. What am I doing wrong?

When trying to commit using the db2Session, I get:

Transaction must be committed using the transaction manager.

But nowhere in the tutorial, do I see the transaction manager being used. I thought that this manager is bound using zope.sqlalchemy? Yet, nothing is happening otherwise. Help again would be really appreciated!

I have the following setup in my main function in a Pyramid App:

from sqlalchemy import engine_from_config
from .models import db1Session, db2Session

def main(global_config, **settings):
""" This function returns a Pyramid WSGI application.
"""
    db1_engine = engine_from_config(settings, 'db1.')
    db2_engine = engine_from_config(settings, 'db2.')

    db1Session.configure(bind=db1_engine)
    db2Session.configure(bind=db2_engine)

In .models/__init__py, I have:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (scoped_session, sessionmaker)
from zope.sqlalchemy import ZopeTransactionExtension

db1Session = scoped_session(sessionmaker(
    extension=ZopeTransactionExtension()))
db2Session =      
    scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

Base = declarative_base()

In ./model/db2.py I have:

class PlateWellResult(Base):
    __tablename__ = 'SomeTable'
    __table_args__ = {"schema": 'some_schema'}

    id = Column("ID", Integer, primary_key=True)
    plate_id = Column("PlateID", Integer)
    hit_group_id = Column("HitID", Integer, ForeignKey(
        'some_schema.HitGroupID.ID'))
    well_loc = Column("WellLocation", String)

The relevant bits of my saving function look like this. ./lib/db2_api.py:

def save_selected_rows(input_data, selected_rows, hit_group_id):
    """ Wrapper method for saving selected rows """
    # Assume I have all the right data below.
    new_hit_row = PlateWellResult(
        plate_id=master_plate_id,
        hit_group_id=hit_group_id,
        well_loc=selected_df_row.masterWellLocation)

    db1Session.add(new_hit_row)
    # When I try the row below:
    # db2Session.commit()
    # I get: Transaction must be committed using the transaction manager
    # If I cancel the line above, nothing gets committed.
    return 'Save successful.'

That function is called from my viewer:

@view_config(route_name='some_routename', renderer='json',
             permission='create_hit_group')
def save_to_hitgroup(self):
    """ Backend to AJAX call to save selected rows to a hit_group """
    try:
        # Assume that all values were checked and all the right 
        # parameters are passed
        status = save_selected_rows(some_result, 
                                    selected_rows_list,
                                    hitgroup_id)
        json_resp = json.dumps({'errors': [],
                                'status': status})
        return json_resp
    except Exception as e:
        json_resp = json.dumps({'errors': ['Error during saving. {'
                                           '0}'.format(e)],
                                'status': []})
        return json_resp

Upvotes: 0

Views: 281

Answers (1)

Michael Merickel
Michael Merickel

Reputation: 23331

The comments above are good. I just wanted to summarize here.

The transaction manager is begun/committed/aborted by pyramid_tm. If you aren't using that then it's likely the issue.

You are also squashing possible database exceptions which need to be conveyed to the transaction manager. You can do this via transaction.abort() in the exception handler.

Upvotes: 1

Related Questions