👀 Views: 0 💬 Answers: 1 📅 Created: 2025-06-11

python fastapi sqlalchemy asyncio postgresql Python

I'm attempting to set up I've hit a wall trying to This might be a silly question, but I'm trying to perform a bulk insert operation using SQLAlchemy's `AsyncSession` in my FastAPI application, but I'm running into a performance bottleneck and unexpected behavior..... My model looks like this: ```python from sqlalchemy import Column, Integer, String, create_engine from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine from sqlalchemy.orm import sessionmaker, declarative_base Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) age = Column(Integer) engine = create_async_engine('postgresql+asyncpg://user:password@localhost/dbname', echo=True) AsyncSessionLocal = sessionmaker(bind=engine, class_=AsyncSession, expire_on_commit=False) ``` I have a list of user dictionaries that I want to insert: ```python user_data = [ {'name': 'Alice', 'age': 30}, {'name': 'Bob', 'age': 25}, {'name': 'Charlie', 'age': 35}, ] # Let's say we have 10,000 records ``` When I try to perform the insertion in an async route like this: ```python from fastapi import FastAPI, Depends app = FastAPI() async def get_db() -> AsyncSession: async with AsyncSessionLocal() as session: yield session @app.post('/users/bulk-insert') async def bulk_insert(users: list[dict], db: AsyncSession = Depends(get_db)): db_users = [User(**user) for user in users] db.add_all(db_users) await db.commit() return {'message': 'Users added'} ``` I experience a timeout after a few seconds when inserting a large amount of data. The error I'm getting is `sqlalchemy.exc.OperationalError: (asyncpg.exceptions.QueryCanceledError) canceling statement due to statement timeout`. I've tried increasing the timeout in PostgreSQL settings, but it still doesn't seem to work. Additionally, I noticed that the application consumes a lot of memory during this operation. I want to know if there is a more efficient way to handle bulk inserts in this scenario. Should I split the data into smaller chunks, or is there a specific method in SQLAlchemy that caters to bulk inserts? Any suggestions on how to improve this process would be greatly appreciated! My development environment is Windows. What am I doing wrong? I'm working on a API that needs to handle this. Is there a better approach? This is part of a larger mobile app I'm building. Any ideas how to fix this? Cheers for any assistance!