Query SQLAlchemy Time to Pandas Time (Python)

Question

I am trying to query a SQLalchemy table to a Pandas dataframe and match the times up. There is no date, just time. Basically I just need to find the record in the database that matches the time from my Pandas dataframe.

Release Table is basically just this:

class Release_Table(Base):
    __tablename__ = 'Release_Table'

    id = Column('Id', Integer, primary_key=True)
    release_time = Column('release_time', Time)

And the datatype of df['release'] is dtype('O')

So I am doing this:

for i in df.index:
     release = session.query(Release_Table).filter(Release_Table.release_time == df.loc[i,'release']).first()
     df.loc[i, 'release'] = release.id

When I do this, I get this error:

ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The data types time and datetime2 are incompatible in the equal to operator. (402) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)')

If I try to convert df.loc[i, 'release'] to datetime, it says:

TypeError: is not convertible to datetime

So I don't know how to compare these two times. Both data types are datetime.time from what I can tell, unless I'm missing something.

You need to provide a lot more information. What is Release_Table? Also, please show the dtypes of the df: df.dtypes — elPastor

Emac Emac · Accepted Answer · 2020-03-06T15:34:10

Okay, I figured it out. I just needed to do this:

for i in df.index:
     release = session.query(Release_Table).filter(Release_Table.release_time == str(df.loc[i,'release'])).first()
     df.loc[i, 'release'] = release.id

Turning it into a string solved it for me. I'll leave this here in case anyone else stumbles upon this problem.

Query SQLAlchemy Time to Pandas Time (Python)

1 Answers