Fetching data from Postgresq Database using sqlalchemy.select() in python

Question

I am using python and SQLalchemy to fetch data from a table.

import sqlalchemy as db
import pandas as pd

DATABASE_URI = 'postgres+psycopg2://postgres:postgresql@localhost:5432/postgres'
engine = db.create_engine(DATABASE_URI)
connection = engine.connect()
project_table = db.Table('project', metadata, autoload=True, autoload_with=engine)

here i want to fetch records based on a list of ids which i have.

 l=[557997, 558088, 623106, 558020, 623108, 557836, 557733, 622792, 623511, 623185] 
 query1 = db.select([project_table ]).where(project_table .columns.project_id.in_(l))
 #sql query= "select * from project where project_id in l"
 Result = connection.execute(query1)
 Rset = Result.fetchall()
 df = pd.DataFrame(Rset)
 print(df.head())

Here when i print df.head() I am getting an empty dataframe. I am not able to pass a list to the above query. Is there a way to send a list to in to above query.

The result should contain the rows in the table which are equal to project_id's given. i.e.

project_id  project_name  project_date project_developer
557997       Test1        24-05-2011    Ajay
558088       Test2        24-06-2003    Alex

These rows will be inserted into dataset. The Query is

"select * from project where project_id in (557997, 558088, 623106, 558020, 623108, 557836, 557733, 622792, 623511, 623185)"

here as i cant give static values I will insert the values to a list and pass this list to query as a parameter. This is where i am having a problem. I cant pass a list as a parameter to db.select().How can i pass a list to db.select()

Answer 1

After many trails i have found out that because of large data the query is fetching and also less ram in my workstation, the query returned null(no results). so what I did was

 Result = connection.execute(query1)
            while True:
                rows = Result.fetchmany(10000)
                if not rows:
                    break
                for row in rows:
                    table_data.append(row)
                    pass
            df1 = pd.DataFrame(table_data)
            df1.columns = columns

After this the program was working fine.