sqlalchemy2ormar - failed to create ormar class

[avico78]

Describe the bug
I create tables where tables actually define as ormar class in program ,.
once tables created - verified them in DB.
Also manage to fetch/insert data with the models,

Tried pull the ormar class using the sqlalchemy2ormar:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
import ormar

Base = automap_base()

# engine, suppose it has tables setup
engine = create_engine("postgresql://admin:[email protected]:5432/customer_trg")

# reflect the tables
Base.prepare(engine, reflect=True)

# translate whole db
ormar_models = [sqlalchemy_to_ormar(sqlalchemymodel, database=database, metadata=metadata) for sqlalchemymodel in Base.classes]

getting:

   raise sa_exc.ArgumentError(
sqlalchemy.exc.ArgumentError: WARNING: when configuring property 'customer' on mapped class subscriber->subscriber, column 'customer' conflicts with property '<RelationshipProperty at 0x48b32e8; customer>'. To resolve this, map the column to the class under a different name in the 'properties' dictionary.  Or, to remove all awareness of the column entirely (including its availability as a foreign key), use the 'include_properties' or 'exclude_properties' mapper arguments to control specifically which table columns get mapped

To Reproduce
Create tables in DB:

-- Table: public.customer

-- DROP TABLE public.customer;

CREATE TABLE public.customer
(
    creation_date timestamp without time zone,
    modification_date timestamp without time zone,
    id integer NOT NULL DEFAULT nextval('customer_id_seq'::regclass),
    customer_no integer,
    customer_status character varying(100) COLLATE pg_catalog."default",
    first_name character varying(100) COLLATE pg_catalog."default",
    last_name character varying(100) COLLATE pg_catalog."default",
    email character varying(60) COLLATE pg_catalog."default",
    pin_code text COLLATE pg_catalog."default",
    CONSTRAINT customer_pkey PRIMARY KEY (id),
    CONSTRAINT uc_customer_creation_date_modification_date_customer_no UNIQUE (creation_date, modification_date, customer_no)
)

TABLESPACE pg_default;

ALTER TABLE public.customer
    OWNER to admin;

-- Table: public.subscriber

-- DROP TABLE public.subscriber;

CREATE TABLE public.subscriber
(
    creation_date timestamp without time zone,
    modification_date timestamp without time zone,
    id integer NOT NULL DEFAULT nextval('subscriber_id_seq'::regclass),
    subscriber_no integer,
    is_active boolean,
    customer integer,
    CONSTRAINT subscriber_pkey PRIMARY KEY (id),
    CONSTRAINT fk_subscriber_customer_id_customer FOREIGN KEY (customer)
        REFERENCES public.customer (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE public.subscriber
    OWNER to admin;

Expected behavior
Expect to get ormar Class

Versions (please complete the following information):

• Database backend used : postgress
  Python version 3.8
  ormar 0.10.5
  sqlalchemy-to-ormar 0.0.1

Additional context

I double check with other tool there's no DB issue ,
the tool provide the sqlalchemy our of DB :

sqlacodegen postgresql://admin:admin@192.168.1.112:5432/customer_trg


class Customer(Base):
    __tablename__ = 'customer'
    __table_args__ = (
        UniqueConstraint('creation_date', 'modification_date', 'customer_no'),
    )

    creation_date = Column(DateTime)
    modification_date = Column(DateTime)
    id = Column(Integer, primary_key=True, server_default=text("nextval('customer_id_seq'::regclass)"))
    customer_no = Column(Integer)
    customer_status = Column(String(100))
    first_name = Column(String(100))
    last_name = Column(String(100))
    email = Column(String(60))
    pin_code = Column(Text)




class Subscriber(Base):
    __tablename__ = 'subscriber'

    creation_date = Column(DateTime)
    modification_date = Column(DateTime)
    id = Column(Integer, primary_key=True, server_default=text("nextval('subscriber_id_seq'::regclass)"))
    subscriber_no = Column(Integer)
    is_active = Column(Boolean)
    customer = Column(ForeignKey('customer.id'))

    customer1 = relationship('Customer')

Would appreciate an instruction how run the tool on specific list ,

[collerek]

Yeah I had the same problem seems that automap is far from being perfect as it tries to create column and relation with the same name, which fails. You can use also models generated with sqlacodegen and this is what I did in one of my projects. Let me know how that goes.

[avico78]

Yeah I had the same problem seems that automap is far from being perfect as it tries to create column and relation with the same name, which fails. You can use also models generated with sqlacodegen and this is what I did in one of my projects. Let me know how that goes.

@collerek

How did u convert the sqlalchemy to ormar Class ?
I have tons* of table to maintain/deploy so i'd like to advise how it can be done
in order to simplify the process ,
thanks!

[collerek]

I used sqlacodegen to save the file with reflected sqlalchemy models and imported Base from this file and got Base.classes like in automap (at least I think so 😅 - either imported Base or all as namespace). After import process it with a loop.

[avico78]

I used sqlacodegen to save the file with reflected sqlalchemy models and imported Base from this file and got Base.classes like in automap (at least I think so 😅 - either imported Base or all as namespace). After import process it with a loop.

would u mine to share the code please

[collerek]

I saved the db with sqlacodegen into models.py file and in the same package I created a new file

# parser.py
import sqlalchemy
from databases import Database
from sqlalchemy_to_ormar import sqlalchemy_to_ormar

import models  # this is the file saved by sqlacodegen
import sys, inspect

ormar_models = []
metadata = sqlalchemy.MetaData()
database = Database("sqlite:///")


def parse_classes():
    for name, obj in inspect.getmembers(sys.modules["models"]):
        if inspect.isclass(obj):
            if (isinstance(obj, sqlalchemy.ext.declarative.api.DeclarativeMeta)
                    and obj.__name__ != "Base"):
                print(obj)
                ormar_models.append(
                    sqlalchemy_to_ormar(obj, metadata=metadata, database=database))


parse_classes()

Note that it will explode (recurrsion error) if you have circles in code or self-referencing modules as it's not supported yet.

[avico78]

I saved the db with sqlacodegen into models.py file and in the same package I created a new file

# parser.py
import sqlalchemy
from databases import Database
from sqlalchemy_to_ormar import sqlalchemy_to_ormar

import models  # this is the file saved by sqlacodegen
import sys, inspect

ormar_models = []
metadata = sqlalchemy.MetaData()
database = Database("sqlite:///")


def parse_classes():
    for name, obj in inspect.getmembers(sys.modules["models"]):
        if inspect.isclass(obj):
            if (isinstance(obj, sqlalchemy.ext.declarative.api.DeclarativeMeta)
                    and obj.__name__ != "Base"):
                print(obj)
                ormar_models.append(
                    sqlalchemy_to_ormar(obj, metadata=metadata, database=database))


parse_classes()

Note that it will explode (recurrsion error) if you have circles in code or self-referencing modules as it's not supported yet.

sqlacodegen + conversion to ormar - great feature !
Maybe worth thinking for adopting to sqlachemy2ormar as the suggested solution ,
like an auto - discovery mode which allow the user the pull the class automatically from DB,
with an option for predefine list (sqlacodegen --tables support that).

It could be also interesting to have assign default assignment automatically (be set of rules of course).
not sure how yet but definitely worth thinking about it ,

[collerek]

Yes I want to add automap/autodiscovery with sqlacodegen (as automap from sqlalchemy is not really helpful in most of my cases).

As for auto assignment if you mean register a class in a module so you can access it by name you can modify the example as:

import sqlalchemy
from databases import Database
from sqlalchemy_to_ormar import sqlalchemy_to_ormar

import models
import sys, inspect

metadata = sqlalchemy.MetaData()
database = Database("sqlite:///")


def parse_classes():
    for name, obj in inspect.getmembers(sys.modules["models"]):
        if inspect.isclass(obj):
            if (isinstance(obj, sqlalchemy.ext.declarative.api.DeclarativeMeta)
                    and obj.__name__ != "Base"):
                print(obj)
                ormar_class = sqlalchemy_to_ormar(obj, metadata=metadata, database=database)
                # register in current module
                setattr(sys.modules[__name__], ormar_class.__name__, ormar_class)


parse_classes()

# should be available
print(<YOUR_MODEL_NAME>)

If __name__ gives you __main__ (depend on how you run the script) use setattr(sys.modules[ormar_class.__module__], ormar_class.__name__, ormar_class)

You can also save to file, there is a ormar_model_str_repr function so you can do something like:

#when you have list of ormar models
with open("ormar.model.py", "w") as file:
    for model in ormar_models:
        file.write(ormar_model_str_repr(model))

I don't remember the new lines so you might have to add new lines ("\n") writes to the loop between models.

[avico78]

@collerek

Trying to add the auto discovery via fastapi,
meaning , by given db details + list of tables(optional),
we get the ormar models.

I have doubt if this should be the recommended approach ,
one of the drawback with approach - each time I'm creating either the sqlalachmy or ormar modules file
fastapi see the change and reload but it also shutting down ?
see:

WARNING: WatchGodReload detected file change in '['/app/auto_discovery/db_schemas.py']'. Reloading...
INFO: Shutting down
INFO: Waiting for application shutdown.
INFO: ASGI 'lifespan' protocol appears unsupported.
INFO: Application shutdown complete

Q:
let say fastapi handle some other request in parallel by other user,
is that mean such "Shutting down" can cause the other request failed?

is there more elegant way to do it?

code:

@audo_discovery_router.post("/auto_discovery/", response_model=DbConfigAndSetup,tags=["auto_discovery"])
async def auto_discovery(dbConfigandsetup: DbConfigAndSetup):
    module = importlib.import_module('databases_config.db')

# enum class - translate the friendly name for db_name --> BaseMeta 
# So dbConfigandsetup.db_name.name holds the chosen related  BaseMeta 

    choosen_basemeta_class = getattr(module, dbConfigandsetup.db_name.name)
## User prvoide table_list (or None)

    tables_file = sqlacodegen_get_tables(choosen_basemeta_class.database,dbConfigandsetup.tables)

    return {"Message": "done"}

     
def sqlacodegen_get_tables(BaseMeta ,table_list ):
#extracting the url for sqlacodegen
    url = str(BaseMeta.database.url)
    metadata = BaseMeta.metadata
    database = BaseMeta.database
    if not table_list is None:
        table_list = ",".join(table_list)
        sqlacodegen_cmd ="sqlacodegen --outfile auto_discovery/db_schemas.py --tables (" + table_list  + ") " +  url
    else:
        sqlacodegen_cmd ="sqlacodegen --outfile auto_discovery/db_schemas.py " + url
#exeucting sqlacodegen with cmd
    os.system(sqlacodegen_cmd)

# getting an error as file hasn't finish written so import failed
#don't like the idea anyway 

    from . import db_schemas
#converting to ormar 
     for name, obj in inspect.getmembers(sys.modules["db_schemas"]):
         if inspect.isclass(obj):
             if (isinstance(obj, sqlalchemy.ext.declarative.api.DeclarativeMeta)
                     and obj.__name__ != "Base"):
    
                 ormar_models.append(
                     sqlalchemy_to_ormar(obj, metadata=metadata, database=database))
    
     with open("auto_discovery/models.py", "w") as file:
         for model in ormar_models:
             file.write(ormar_model_str_repr(model))  

[collerek]

Fastapi reloads cause you probably run it with --reload flag, that means it shutdown and startup again. But this setting is not suitable for production. And yes it will terminate all other requests.

And if you run this behind a server like gunicorn etc. without reload there might be problems with picking up the new python files. So if you want to do this dynamically on fastapi request you shouldn't write it to files.

Why do you want to write to files through fastapi? Reflecting a database and creating models from this might be quite expensive operation depending on number of tables, so you can easily hit timouts along the way if you go through web request.