0

I'm trying the below code, but I get an error when fetching data to a dataframe. I think this is because I declared the table as class:

def get_task_products(task_id):
    before = time.time()
    session = get_new_session()

    persisted_task_products =session.query(TaskProducts). \
        filter(TaskProducts.task_id == task_id). \
        options(load_only(TaskProducts.product_id)).all()
    df = pd.DataFrame(persisted_task_products)
    df.columns = persisted_task_products[0].keys()
    print_debug(
        'db_manager.persisted_motionlesstime: query result (after {0} seconds) : {1})'. \
            format(time.time() - before, df))

    if not df or len(df) != 0:
        return df
    session.close()

The table declaration:

class TaskProducts(Base):
    __tablename__ = 'task_products'

    id = Column(BigInteger, autoincrement=True, primary_key=True)
    task_id = Column(Integer, nullable=False)
    product_id = Column(Integer, nullable=False)

    def __repr__(self):
        return """
            <TaskProducts
            (
            id=%s, task_id=%s, product_id=%s
            )>""".replace(" ", "").replace("\n", " ") % (
            self.id, self.task_id, self.product_id
        )

but I got an error 

 df.columns = persisted_task_products[0].keys()
AttributeError: 'TaskProducts' object has no attribute 'keys'

How could I solve this issue?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
walid barakeh
  • 3
  • 3
  • I think this might be related to your troubles: https://stackoverflow.com/questions/29525808/sqlalchemy-orm-conversion-to-pandas-dataframe – Ilja Everilä Jan 07 '20 at 13:10
  • The crux of the matter is that you're trying to mix the ORM with pandas. Dropping down to "Core level" using for example `Query.statement` is the way to go instead, because pandas works with "tabular" data. The error you receive is one result of this mixing, because the ORM query results in a list of object entities, not rows / tuples. Just "it didn't work for me" does not quite describe what you tried and why it did not work. – Ilja Everilä Jan 07 '20 at 14:02
  • thanks for your comment! its works now, but I got other error . query =session.query(SiteProducts). \ join(TaskProducts, SiteProducts.id == TaskProducts.product_id). \ filter(TaskProducts.task_id == task_id) df = pd.read_sql(query.statement, query.session.bind) ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). – walid barakeh Jan 08 '20 at 13:40
  • Is `task_id` in `TaskProducts.task_id == task_id` a dataframe? – Ilja Everilä Jan 08 '20 at 14:24
  • no its part of the query the query= session.query(SiteProducts). \ join(TaskProducts, SiteProducts.id == TaskProducts.product_id). \ filter(TaskProducts.task_id == task_id) and the dataframe df df = pd.read_sql(query.statement, query.session.bind) – walid barakeh Jan 08 '20 at 15:44

0 Answers0