SQLAlchemy - Error A TVP's rows must be Sequence objects

Question

C# devloper playing with python and I am having issues with inserting rows into an SQL database using SQLAlchemy.

The update code is this.

def updateDatabase(self, testSummary):
        params = quote_plus(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER=GBBED01DB01\SQLHOTEL2;DATABASE=TesterDb;trusted_connection=yes")
        engine = create_engine(
            "mssql+pyodbc:///?odbc_connect={}".format(params))

        Session = sessionmaker(bind=engine)

        session = Session()
        session.add(testSummary)

        for testStepResult in testSummary.testStepResults:   <------- Exception
            session.add(testStepResult)
            for ictResult in testStepResult.ictResults:
                session.add(ictResult)

        try:
            session.commit()
        except Exception as e:
            print("SQL failed to save\n" + e)

I have marked where the exception occours and this is the exception.

(pyodbc.ProgrammingError) ("A TVP's rows must be Sequence objects.", 'HY000') [SQL: 'INSERT INTO [IctResults] ([didTestPass], [testName], component, [lowerLimit], [upperLimit], measured, [rawMeasured], [isOverRange], [isUnderRange], [isPosativeInfinity], [isNegativeInfinity], [testStepResultId]) OUTPUT inserted.id VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: (1, 'Motor Resistance Check', 'Motor', (2224.0,), (2409.0,), (2292.9794921875,), (2292.9794921875,), 0, 0, 0, 0, 129649)]

Table structure looks like this

class TestSummary(Base):
__tablename__ = 'TestSummaries'
id = Column(Integer, primary_key=True)
didTestPass = Column(Boolean)
barcode = Column(String)
testDateTime = Column(DateTime)
testerId = Column(Integer)
fixtureId = Column(Integer)
boardId = Column(Integer)
testMode = Column(Integer)
userMode = Column(Integer)
productVariantId = Column(Integer, ForeignKey('ProductVariants.id'))
cycleTimeSeconds = Column(Integer)
testStepResults = relationship("TestStepResult", backref="TestSummary", lazy='dynamic')


class TestStepResult(Base):
__tablename__ = 'TestStepResults'
id = Column(Integer, primary_key=True)
testSummaryId = Column(Integer, ForeignKey('TestSummaries.id'))
testSummary = relationship(TestSummary)
testName = Column(String)
stepTime = Column(Float, default=0)
stepResult = Column(Boolean)
ictResults = relationship("IctResult", backref="TestStepResult", lazy='dynamic')


class IctResult(Base):
__tablename__ = 'IctResults'
id = Column(Integer, primary_key=True)
didTestPass = Column(Boolean)
testName = Column(String)
component = Column(String)
lowerLimit = Column(Float)
upperLimit = Column(Float)
measured = Column(Float)
rawMeasured = Column(Float)
isOverRange = Column(Boolean, default=False)
isUnderRange = Column(Boolean, default=False)
isPosativeInfinity = Column(Boolean, default=False)
isNegativeInfinity = Column(Boolean, default=False)
testStepResultId = Column(Integer, ForeignKey('TestStepResults.id'))
testStepResult = relationship(TestStepResult)

Maybe I have the relationship setups wrong but it should be this. One 'Test Summary' with Many 'TestStepResult' and One'TestStepResult' with Many 'IctResult'.

Answer 1

As @ilja points out, some of the parameters you have passed to the query are tuples, and not scalars. SQLAlchemy does not support array database types out of the box as far as I know.

I had the same problem and mine was because I had left commas at the end of assignment lines as a result of some copy-paste. Like this:

testSummary.lowerLimit = 2224.0,
testSummary.upperLimit = 2409.0,

This effectively turned the scalar value into a single-valued tuple, which SQLAlchemy does not know what to do with. Hence the ProgrammingError exception.

You must have solved your problem by now, but I post because this is among first results when searching the web for this exception.

Answer 2

Although this question has been answered in your specific case, it's the #1 google result for this error message so I figured I'd add my experience with it.

I ran into this error while attempting to parse a JSON api response and insert it into a database table.

The field in question was originally set up to return a list of values, but was later changed to only return 1. It was, however, still returning a list of length 1.

Attempting to insert the list item threw the error; converting it to a string solved it.