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()

• Ilja Everilä: Please produce a minimal reproducible example, including a small sample of the data (in the database).
• Ilja Everilä: The query is a bit odd, since the proper syntax for IN would be in (...), not in [...], but I suppose that's a typo. Given the example in the question, you should be getting rows back. Have you checked what Rset looks like? Are you sure you're connecting to the correct DB?
• Pradheer Kk: yes, I have connected to correct db. I am not getting anything back when I use list as parameter.How can i pass a list to db.select(). The data which i am fetching is more than 50GB. If not db.select() can you tell me which libraries i can use to fetch data quickly.
• Ilja Everilä: Your query1 should work as is, the list is bound as parameters by SQLAlchemy Core.
• Pradheer Kk: Even if I directly try to print the some values in Rset by saying print(Rset[0:100]), It gives an empty list.

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.