Documentation for taking an existing FastAPI project and adding a Piccolo admin to it

[arunabhdas]

I have an existing blog API written in FastAPI here

https://github.com/arunabhdas/unicorn-fullstack/tree/main/unicorn-fullstack-fastapi/blog

Was trying to find instructions in the docs for steps on converting it into a piccolo project.

I got as far as getting the admin route working

Ran the code in https://github.com/arunabhdas/unicorn-fullstack/tree/main/unicorn-fullstack-fastapi/blog
using the command

python app.py 

and am getting the admin login UI -

However, I'm getting the error

asyncpg.exceptions.UndefinedTableError: relation "piccolo_user" does not exist

Any help would be appreciated @dantownsend

[arunabhdas]

Just a quick update on this one. I realized that I hadn't run the migrations for user and session_auth. So I went ahead and ran those

piccolo migrations forwards user

piccolo migrations forwards session_auth

Then I ran

piccolo user create

to create a user for admin

Was then able to login with the above user I created

[arunabhdas]

Running into a different issue now related to the same topic. Hence, re-opening this thread.

Have my pydantic schemas in schemas.py as below

from pydantic import BaseModel

class Post(BaseModel):
    title: str
    body: str

Also, created the corresponding model in models.py

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from database import Base

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    body = Column(String, index=True)

Tried to generate the admin for the above model as below in app.py

from starlette.applications import Starlette
from starlette.routing import Mount
from piccolo_admin.endpoints import create_admin
import uvicorn

# from tables import Task
from schemas import Post

# admin = create_admin([Task])
admin = create_admin([Post])

app = Starlette(routes=[
    Mount('/admin/', admin)
])

if __name__ == "__main__":
    uvicorn.run(app)

However, when I run

python app.py

Am getting the below error

==> python app.py 
Traceback (most recent call last):
...
    admin = create_admin([Post])
...packages/piccolo_admin/endpoints.py", line 1134, in create_admin
    all_table_classes = get_all_tables(tuple(table_config_map.keys()))
...packages/piccolo_admin/endpoints.py", line 995, in get_all_tables
    get_references(table)
...packages/piccolo_admin/endpoints.py", line 979, in get_references
    for i in table._meta.foreign_key_columns
AttributeError: type object 'Post' has no attribute '_meta'

How would I go about adding my existing pydantic model to the admin if I started off with a default FastAPI project?

[dantownsend]

It looks like the problem here is the tables are SQLAlchemy tables instead of Piccolo tables (inheriting from Table).

[arunabhdas]

So, what would be the Piccolo syntax for associating a Post for the below model class, with User

class Todo(Table):
    """
    Todo
    """

    title = Varchar()
    description = Varchar()
    completed = Boolean(default=False)

Am essentially, trying to do the Piccolo equivalent of the below User model class for what I would do in pydantic / SQLalchemy

from pydantic import BaseModel

class User(BaseModel):
    name: str
    email: str
    password: str

And additionally, establishing a one-to-many relationship between User and Post

The Piccolo version of my source code is here -

https://github.com/arunabhdas/unicorn-fullstack/tree/main/unicorn-fullstack-fastapi-piccolo/piccolo

The FastAPI / SQLAlchemy is here -

https://github.com/arunabhdas/unicorn-fullstack/tree/main/unicorn-fullstack-fastapi/blog

Also, is there a way to have users be able to be managed in the Piccolo admin?

[dantownsend]

You need a foreign key from Post to User.

If you then pass the tables into create_admin you'll be able to manage them in Piccolo Admin,

[arunabhdas]

Thanks @dantownsend

I added the foreign key to Post as below

class Author(Table):
    """
    Author
    """

    name = Varchar()
    username = Varchar()
    email = Varchar()
    password = Varchar()
    active = Boolean(default=False)


class Post(Table):
    """
    Post
    """

    title = Varchar()
    description = Varchar()
    author = ForeignKey(references=Author)
    completed = Boolean(default=False)

And that works in as far as getting the Authors and Posts manageable via admin.

However, is there a way to have the Post be linked to the user that one creates with the piccolo user create command?

Also, is there a way to have the users created with piccolo user create command managed via admin?

[dantownsend]

The users which are created via piccolo user create are added to this table:

from piccolo.apps.user.tables import BaseUser

If you register that table with create_admin then you can manage users via Piccolo Admin.

You could also add a foreign key to BaseUser if you wanted from Post.

If you wanted to automatically set which BaseUser created a Post, it's possible using Piccolo Admin hooks. This is quite an advanced thing though.

[arunabhdas]

Thanks @dantownsend

When I changed exclude_imported=False in piccolo_app.py, then the BaseUser class shows up in admin as you mentioned

APP_CONFIG = AppConfig(
    app_name="home",
    migrations_folder_path=os.path.join(
        CURRENT_DIRECTORY, "piccolo_migrations"
    ),
    table_classes=table_finder(modules=["home.tables"], exclude_imported=False),
    migration_dependencies=[],
    commands=[],
)

So that works great 🚀

However, I now wanted to have a Project model class that has a many-to-one relationship with BaseUser

So I tried what you said and I created a model class called Project which has a foreign key referencing BaseUser

from piccolo.table import Table
from piccolo.columns import (
    Varchar,
    Integer,
    ForeignKey,
    Boolean,
    Text,
    Timestamp,
    Numeric,
    Real
)
from piccolo.apps.user.tables import BaseUser
# OTHER MODEL CLASSES
class Project(Table):
    """
    An example table.
    """

    title = Varchar()
    description = Varchar()
    owner = ForeignKey(references=BaseUser)
    completed = Boolean(default=False)

Then I ran piccolo migrations new home --auto and that creates a migration file under piccolo_migrations

==> piccolo migrations new home --auto
Creating new migration ...
Created tables                           3
Dropped tables                           0
Renamed tables                           0
Created table columns                    18
Dropped columns                          0
Columns added to existing tables         3
Renamed columns                          0
Altered columns                          0

However, when I run piccolo migrations forwards home, I get the below error

==> piccolo migrations forwards home
👍 3 migrations already complete
⏩ 1 migration not yet run
🚀 Running 1 migration:
  - 2023-03-21T09:30:13:353986 [forwards]... The command failed.
relation "piccolo_user" already exists

Also, without that migration running, I get the following error when I run python main.py

  File "asyncpg/protocol/protocol.pyx", line 168, in prepare
asyncpg.exceptions.UndefinedTableError: relation "project" does not exist

Thanks again for your help. Really appreciate it!!

[dantownsend]

Your problem is by using exclude_imported=False it made the migration generator pick up BaseUser, so that will be in your migration file. So I'd recommend setting exclude_imported=True, deleting the last migration file, and recreating it. To add BaseUser to create_admin just add it manually:

from piccolo.apps.user.tables import BaseUser

# you'll end up with something similar to this:
create_admin(tables=APP_CONFIG.table_classes + [BaseUser])

The reason the base user already exists, is when you ran piccolo migrations forwards user that has already run a migration to create the base user table.

[arunabhdas]

Thanks @dantownsend !! That worked 🚀

[dantownsend]

Cool!