Fetching specific date from MySQL in FastAPI-CodePudding

I want to fetch a date from mysql workbench according to a certain id (msisdn). But I don't know how to do it with SQLAlchemy. msisdn is the primary key.

Should emulate this query:

SELECT 
    last_port 
FROM 
    `realtime-backend`.portabilidad 
WHERE 
    last_port='2020-04-23 6:00:00' AND 
     msisdn='56977095634'

Models:

class PortabilidadInfo(Base):
      __tablename__ = "portabilidad"
      msisdn = Column(Integer, primary_key=True)
      operator_id = Column(Integer, nullable=True)
      last_port = Column(Date, default=datetime.now(), nullable=True)

Code:

portabilidad_msisdn = '56977095634'
fecha_csv = '2020-04-23 6:00:00'
                
date_info = session.query(PortabilidadInfo).filter(PortabilidadInfo.last_port == fecha_csv).get(portabilidad_msisdn) 
print("FECHA BDD OBJ: ", date_info, type(date_info))

But I get an error saying this: Query.get() being called on a Query with existing criterion.

CodePudding user response：

You can combine terms to the filter statement:

date_info = session.query(PortabilidadInfo).filter(
    PortabilidadInfo.last_port == fecha_csv,
    PortabilidadInfo.msisdn == portabilidad_msisdn,
).first()

This will retrieve the first row that matches both last_port and portabilidad_msisdn.

You can then access the value of last_port through date_info.last_port. However, since you're also including this value in your query, you can just check if a row was returned at all (so check if date_info is None, otherwise use fecha_csv).

get is a special construct to retrieve something from the primary_key field, and can't be applied together with other fields.

However, since msisdn is a primary key, there can't be any duplicates for that field, so this will only be able to retrieve a single row in either case (it will just return None if the date doesn't match).