Question 89285 in PostgreSQL Question posted in
The official documentation can be found here.

Unable to create Tables using SQL alchemy and fastAPI – Postgresql

I am learning how to use fastapi and sql alchemy to build an api, but when trying to get my script to create the tables in postgreSQL it just doesn’t work, nothing happens.

This is what my code looks like:

class Settings(BaseSettings):
    DB_URL: str =  f'postgresql+asyncpg://{db_user}:{db_password}@{host}:{db_port}/{db_name}'
    DBbaseModel = declarative_base()

    class Config:
        case_sensitive = True


settings = Settings()

this is my engine and session:

engine : AsyncEngine = create_async_engine(settings.DB_URL)

Session: AsyncSession =  sessionmaker(
    autocommit=False, autoflush=False, expire_on_commit=False, class_=AsyncSession, bind=engine
)

and this is what my script to create the tables looks like:

async def create_tables() -> None:
    async with engine.begin() as connection:
        await connection.run_sync(settings.DBbaseModel.metadata.drop_all)
        await connection.run_sync(settings.DBbaseModel.metadata.create_all)


if __name__ == '__main__':
    import asyncio
    asyncio.run(create_tables())

I did that following a step by step tutorial even, and yet it doesn’t work. can anyone please help me with this?

Edit:
as requested this is one of my models:

from core.configs import Settings
from sqlalchemy import Column, Integer, String

class UserModel(Settings.DBbaseModel):
    __tablename__ = 'users'
    id: Column(Integer, primary_key=True, autoincrement=True)
    fullname: Column(String)
    email: Column(String)
    password: Column(String)
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    SOLVED!

    The issue was that I had to add a line with __allow_unmapped__ = True in my models for it to work


  2. 0

    It looks like you are using asyncpg to connect to your PostgreSQL database, but you are using run_sync to execute your database commands.

    run_sync is intended for use with synchronous functions, but you are using it with async functions. Instead, you should use connection.run to execute your async functions.

    Edit your code like this:

    async def create_tables() -> None:
async with engine.begin() as connection:
    await connection.run(settings.DBbaseModel.metadata.drop_all)
    await connection.run(settings.DBbaseModel.metadata.create_all)

if __name__ == '__main__':
    import asyncio
    asyncio.run(create_tables())
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search