Postgres SqlAlchemy model with server defaults and different schema cannot be used

[filipmarkoski]

I am using a PostgreSQL 14.2-1 Windows x64 database using pgAdmin to which I am connecting to like so:

engine = create_async_engine(DATABASE_URL,
                             future=True, echo=True,
                             pool_use_lifo=True, pool_pre_ping=True, pool_recycle=7200
                             )
async_session_maker = sessionmaker(engine, class_=AsyncSession,
                                   expire_on_commit=False)

Base: DeclarativeMeta = declarative_base()

Using the following SQL script I have made the table dbo.Quote, like so:

CREATE TABLE dbo.Quote
(
    "ID" bigint GENERATED ALWAYS AS IDENTITY,
    "UUID" uuid NOT NULL DEFAULT gen_random_uuid(),
    "DateCreated" timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "DateModified" timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "IsActive" boolean NOT NULL DEFAULT TRUE,
    "Text" character varying(512) NOT NULL,
    "Author" character varying(126) DEFAULT 'Unknown',
    "Origin" character varying(126) DEFAULT 'Unknown',
    "UserID" bigint,
      CONSTRAINT "FK_Quote_Users" FOREIGN KEY ("UserID")
        REFERENCES auth.users ("ID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    PRIMARY KEY ("ID")
);

ALTER TABLE IF EXISTS dbo.Quote
    OWNER to postgres;

I have also developed an SQLAlchemy model for the dbo.Quote postgres table:

class Quote(Base):
    __tablename__ = 'quote'
    __table_args__ = {'schema': 'dbo'}

    ID = Column(BigInteger, Identity(always=True, start=1, increment=1,
                                     minvalue=1, maxvalue=9223372036854775807, cycle=False, cache=1), primary_key=True)
    UUID = Column(UUID, nullable=False, server_default=text("gen_random_uuid()"))
    DateCreated = Column(DateTime(True), nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    DateModified = Column(DateTime(True), nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    IsActive = Column(Boolean, nullable=False, server_default=text("true"))
    Text = Column(String(512), nullable=False)
    Author = Column(String(126), server_default=text("'Unknown'::character varying"))
    Origin = Column(String(126), server_default=text("'Unknown'::character varying"))
    UserID = Column(BigInteger, ForeignKey('auth.users.ID'))
`

I am using an AsyncSession generator, such as you have exemplified in your simple example, namely:
`
async def get_transaction_session() -> AsyncSession:
    async with async_session_maker() as session:
        async with session.begin():
            yield session

and I am adding the crud router builder like so

QuoteCRUDRouter = crud_router_builder(db_model=Quote,
                                      prefix='/quote', tags=['Quote'],

                                      exclude_columns=['UUID','DateCreated','DateModified','IsActive'],
                                      crud_methods=[CrudMethods.CREATE_ONE],

                                      async_mode=True,
                                      autocommit=False,
                                      db_session=get_transaction_session,
                                      )

Finally, when I run

import uvicorn

if __name__ == "__main__":
    uvicorn.run("app.app:app",
                # host="0.0.0.0", # defaults to localhost
                log_level="info", reload=True)

I get the following error

INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [14456] using watchgod
Process SpawnProcess-1:
Traceback (most recent call last):
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python37\lib\multiprocessing\process.py", line 297, in _bootstrap
    self.run()
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python37\lib\multiprocessing\process.py", line 99, in run
    self._target(*self._args, **self._kwargs)
  File "C:\Users\DELL\Documents\code\fastapi-learning\.env\lib\site-packages\uvicorn\subprocess.py", line 76, in subprocess_started
    target(sockets=sockets)
  File "C:\Users\DELL\Documents\code\fastapi-learning\.env\lib\site-packages\uvicorn\server.py", line 67, in run
    return asyncio.run(self.serve(sockets=sockets))
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python37\lib\asyncio\runners.py", line 43, in run
    return loop.run_until_complete(main)
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python37\lib\asyncio\base_events.py", line 579, in run_until_complete
    return future.result()
  File "C:\Users\DELL\Documents\code\fastapi-learning\.env\lib\site-packages\uvicorn\server.py", line 74, in serve
    config.load()
  File "C:\Users\DELL\Documents\code\fastapi-learning\.env\lib\site-packages\uvicorn\config.py", line 458, in load
    self.loaded_app = import_from_string(self.app)
  File "C:\Users\DELL\Documents\code\fastapi-learning\.env\lib\site-packages\uvicorn\importer.py", line 21, in import_from_string
    module = importlib.import_module(module_str)
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python37\lib\importlib\__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1006, in _gcd_import
  File "<frozen importlib._bootstrap>", line 983, in _find_and_load
  File "<frozen importlib._bootstrap>", line 967, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 677, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 728, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "C:\Users\DELL\Documents\code\fastapi-learning\app\app.py", line 36, in <module>
    db_session=get_transaction_session,
  File "C:\Users\DELL\Documents\code\fastapi-learning\.env\lib\site-packages\fastapi_quickcrud\crud_router.py", line 114, in crud_router_builder
    sql_type, = asyncio.get_event_loop().run_until_complete(async_runner(db_session))
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python37\lib\asyncio\base_events.py", line 566, in run_until_complete
    self.run_forever()
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python37\lib\asyncio\base_events.py", line 521, in run_forever
    raise RuntimeError('This event loop is already running')
RuntimeError: This event loop is already running

Why am I getting this error?

Also, because SQLite does not have schemas, the __table_args__ in the SQLAlchemy model cause a problem, so what is the issue with that as well?

[LuisLuii]

I have tested in my environment with your same code with pg version 14 but different AsyncSession generator, and it can run normally and call the api successfully. Can I have your DATABASE_URL and AsyncSession generator code please?

[LuisLuii]

please try to update the library version to 0.1.9. I have provided the sql_type argument in crud_router_builder in this version. can you please try rerun the code with the code as follows?

from fastapi_quickcrud.misc.type import SqlType

QuoteCRUDRouter = crud_router_builder(db_model=Quote,
                                      prefix='/quote', tags=['Quote'],

                                      exclude_columns=['UUID','DateCreated','DateModified','IsActive'],
                                      crud_methods=[CrudMethods.CREATE_ONE],
                                      async_mode=True,
                                      autocommit=False,
                                      db_session=get_transaction_session,
                                      sql_type=SqlType.postgresql
                                      )

[LuisLuii]

and I hope you can provide more detail of your environment to let me reproduce this issue, such as python version, pip list. Thanks so much

[filipmarkoski]

DATABASE_URL = 'postgresql+asyncpg://postgres:metamars@localhost:5432/postgres'

engine = create_async_engine(DATABASE_URL,
                             future=True, echo=True,
                             pool_use_lifo=True, pool_pre_ping=True, pool_recycle=7200
                             )
async_session_maker = sessionmaker(engine, class_=AsyncSession,
                                   expire_on_commit=False)

Base: DeclarativeMeta = declarative_base()

async def get_transaction_session() -> AsyncSession:
    async with async_session_maker() as session:
        async with session.begin():
            yield session

I will make a GitHub repository to share all of my code.

[filipmarkoski]

Here's a GitHub repository with all of the code. https://github.com/filipmarkoski/fastapi-learning

[filipmarkoski]

Also, is there a way to override a certain function, for example, let's say I want to create an instance of a certain model such as Quote, but I also want to perform some machine learning before I insert the record into the database. Is there any way to get the generated code verbatim, which I can edit how I choose afterwards? My question is inspired by the following package: https://pypi.org/project/sqlacodegen/

[filipmarkoski]

Your update seemed to work, I am able to post a quote from Swagger now. Thank you! I love the package by the way.

[LuisLuii]

Originally I didn't plan to open sql_type because I wanted to automate it. But it seems like some errors will occur. I'll look into it. Thanks for the issue that made me aware of the bug <3

[LuisLuii]

Also, is there a way to override a certain function, for example, let's say I want to create an instance of a certain model such as Quote, but I also want to perform some machine learning before I insert the record into the database. Is there any way to get the generated code verbatim, which I can edit how I choose afterwards? My question is inspired by the following package: https://pypi.org/project/sqlacodegen/

Sorry, it's not supported yet. But this is what I've always wanted to achieve, I think this feature is a high priority, I will implement it after I complete the #9 .

[LuisLuii]

Root cause: Fastapi project in async mode with auto-reload and crud_router_builder with async mode, but I created an event loop to get the type of the sql connection. Which creates multiple event loops when reload.

SOLVED: Provide sql_type to be declared by the user