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'.

Why are some of the parameters passed as single value tuples, such as (2224.0,). My guess is that your driver interprets tuples in some particular way. — Ilja Everilä
– Ilja Everilä, Commented Feb 25, 2019 at 20:40
@IljaEverilä Looks like that has pointed me in the right direction. I did a print of the assignment of those values that look like Tuples in table 'IctResult'. Turns out I actually was doing this so I fixed the issue to ensure I was assigning the first item of the tuple, which is a float value. Issue now resolved. — Gaz83
– Gaz83, Commented Feb 26, 2019 at 11:15

zaadeh · Accepted Answer · 2021-08-23 13:31:16Z

7

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.

edited Aug 23, 2021 at 13:31

answered Jun 25, 2020 at 8:40

zaadeh

1,9113 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jinto Lonappan Over a year ago

This "comma at the end" exactly was the mistake I was doing. A copy/paste mistake from the POST/create method.

simonpa71 Over a year ago

Thanks for taking the effort to post a reply. In my case it was a parameter = cursor.fetchone() that returned a value followed by a comma. Replacing it with parameter = cursor.fetchone()[0] solved the issue.

Bilbottom · Accepted Answer · 2022-05-25 10:06:56Z

zaadeh's answer and Christopher Prosser's answer are spot on -- the DataFrame has some values that SQLAlchemy doesn't know how to put into a SQL table

The 'correct' approach is to fix this for the offending columns depending on what type of values they hold -- for example, if your values are tuples or lists then you may want to flatten your data (think about using some data normalisation -- specifically 1NF)

However, if you're in a rush, the following function worked for me:

def convert_objects_to_strings(data_frame: pd.DataFrame) -> pd.DataFrame:
    """Convert the `object` columns into `string`s so that SQLAlchemy can handle them"""
    for col, col_type in data_frame.dtypes.items():
        if col_type == 'object':
            data_frame.loc[:, col] = data_frame.loc[:, col].astype('string')
    return data_frame

This will convert the values on the offending columns (the columns whose data type is usually object) to strings which can be handled by SQLAlchemy and your database

Since this returns the adjusted DataFrame, the way I used it is something like:

convert_objects_to_strings(df).to_sql(
    name='table_name',
    con=engine,
    if_exists='replace',
    schema='schema_name'
)

Note that engine is the SQLAlchemy Engine object that you create with sqlalchemy.create_engine()

Please note that this can have unexpected behaviour depending on the values in your DataFrame, so I still recommend implementing a solution appropriate for your specific data/use-case

Christopher Prosser · Accepted Answer · 2021-01-16 14:01:31Z

1

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.

answered Jan 16, 2021 at 14:01

Christopher Prosser

211 bronze badge

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related