xnuinside/omymodels

O!My Models (omymodels) is a library to generate Pydantic, Dataclasses, GinoORM Models, SqlAlchemy ORM, SqlAlchemy Core Table, Models from SQL DDL. And convert one models to another.

Hi 👋

I'm Python enthusiast and I like do 🔭 experiments in code generation & metaprogramming.

🌱 If you interesting to participate in any project: feel free to open PR or 💬 issue, I'm very glad to any participation.

I also participate as a Program committee Member of cute & friendly conference PiterPy https://piterpy.com/en/ (We alwasy welcome for the new speakers, if you have a theme to talk about - don't afraid to send CFP, if you have some doubts - you can ask any questions to me in telegram t.me/xnuinside, feel free to do this)

Right now, 🧑‍🌾 I'm focusing on supporting & adding new features to (but honestly - I don't have a time :(():

• simple-ddl-parser - Parser that focudes on differen DDL dialects and only DDL

• omymodels - Code generator that allows you to create different Python Modules from DDL and convert one model type to another

If you need any help or want to collobarate anyhow: contact me via mail [email protected] or t.me/xnuinside

omymodels's Issues

Enhancement - Support UUID

Hi again, seem like it doesn't support UUID

CREATE TABLE "prefix--schema-name"."table" (
  _id uuid PRIMARY KEY,
);

will be convert to

from gino import Gino
db = Gino(schema="prefix--schema-name")

class Table(db.Model):
    __tablename__ = 'table'

    _id = db.Column(OMM_UNMAPPED_TYPE(), nullable=False, primary_key=True)

Expected:

from sqlalchemy.dialects.postgresql import UUID
from gino import Gino

db = Gino()

class Table(db.Model):
    __tablename__ = 'table'
    __table_args__ = dict(schema="prefix--schema-name")

    _id = db.Column(UUID, primary_key=True)

Notes: the schema may be different between tables, should place in model definition instead of gino instance

`NOW()` not recognized as `now()`

Describe the bug

NOW() is not recognized as equivalent to now() when used as a default value, even though SQL is case-insensitive.

To Reproduce

Given the following SQL:

ddl = """
CREATE TABLE asdf (
  t TIMESTAMP DEFAULT NOW()
);
"""
print(omymodels.create_models(ddl=ddl, models_type="dataclass")["code"])

The output is

import datetime
from dataclasses import dataclass


@dataclass
class Asdf:

    t: datetime.datetime = 'NOW()'  # Default value is wrong

Note that, while the datatype of the column is correct, the default value is a string instead of a call to datetime.datetime.now(). If we make NOW()` lowercase, we get the expected output.

import datetime
from dataclasses import dataclass


@dataclass
class Asdf:

    t: datetime.datetime = datetime.datetime.now()

n.b. the expected output has a bug in it which I'll file a separate ticket for

Expected behavior

SQL is case-insensitive, therefore now() and NOW() should behave identically and produce the same output.

Additional context

Python: 3.8.2
Version: 0.8.1

Schema not included in foreign keys in SQLAlchemy model

In a case where there are multiple schemas, schema name should be included in the generated foreign key references.

As an an example, a really simple dbdiagrams.io "model":

The exported PostgreSQL DDL looks like this:

CREATE SCHEMA "schema1";

CREATE SCHEMA "schema2";

CREATE TABLE "schema1"."table1" (
  "id" int PRIMARY KEY,
  "reference_to_table_in_another_schema" int NOT NULL
);

CREATE TABLE "schema2"."table2" (
  "id" int PRIMARY KEY
);

ALTER TABLE "schema1"."table1" ADD FOREIGN KEY ("reference_to_table_in_another_schema") REFERENCES "schema2"."table2" ("id");

Generating sqlalchemy model from this with command

omm /tmp/sample_ddl.sql -m sqlalchemy --no-global-schema

creates model like this:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Table1(Base):

    __tablename__ = "table1"

    id = sa.Column(sa.Integer(), primary_key=True)
    reference_to_table_in_another_schema = sa.Column(
        sa.Integer(), sa.ForeignKey("table2.id"), nullable=False
    )

    __table_args__ = dict(schema="schema1")


class Table2(Base):

    __tablename__ = "table2"

    id = sa.Column(sa.Integer(), primary_key=True)

    __table_args__ = dict(schema="schema2")

Problem is that the schema name is missing from sa.ForeignKey("table2.id"). This will fail because this statement would mean that the table should be found under "public" schema in postgresql, whereas the table is in fact in schema2.

File "/root/.cache/pypoetry/virtualenvs/test-enkIQtTI-py3.9/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 2530, in _resolve_column
    raise exc.NoReferencedTableError(
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'table1.reference_to_table_in_another_schema' could not find table 'table2' with which to generate a foreign key to target column 'id'

Adding the schema reference like below fixes the issue:

    reference_to_table_in_another_schema = sa.Column(
        sa.Integer(), sa.ForeignKey("schema2.table2.id"), nullable=False

MSSQL DDLs with '[]'

Describe the bug
Right now this code:

"""CREATE TABLE [dbo].[users_WorkSchedule](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [RequestDropDate] [smalldatetime] NULL,
        [ShiftClass] [varchar](5) NULL,
        [StartHistory] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
        [EndHistory] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
        CONSTRAINT [PK_users_WorkSchedule_id] PRIMARY KEY CLUSTERED
        (
            [id] ASC
        )
        WITH (
            PAD_INDEX = OFF,
            STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON
        )  ON [PRIMARY],
        PERIOD FOR SYSTEM_TIME ([StartHistory], [EndHistory])
    )
  """

produces models like:

from gino import Gino
from sqlalchemy.dialects.postgresql import ARRAY

db = Gino(schema="[dbo]")


class [users_WorkSchedule](db.Model):

    __tablename__ = '[users_WorkSchedule]'

    [id] = db.Column(ARRAY((1,1)), nullable=False)
    [RequestDropDate] = db.Column(ARRAY(()))
    [ShiftClass] = db.Column(ARRAY((5)))
    [StartHistory] = db.Column(ARRAY((7)), nullable=False)
    [EndHistory] = db.Column(ARRAY((7)), nullable=False)

need to remove [] from types, tables, columns names & etc before generate code based on metadata from DDL

Add Real type

Is your feature request related to a problem? Please describe.
When converting sql type real to sqlalchemy, it is changed to real() but it should be sa.Real

Describe the solution you'd like
it should be automatically changed to 'Real'

I'm happy to give this go, and give a PR

datetime DEFAULT NULL leads to TypeError: 'Column' object is not subscriptable

To Reproduce
testcase.sql:

CREATE TABLE "option" (
  "opt_date" datetime DEFAULT NULL,
);

omm testcase.sql results in:

Traceback (most recent call last):
  File "/home/hrehfeld/projects/ext/omymodels/.venv/bin/omm", line 8, in <module>
    sys.exit(main())
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/omymodels/cli.py", line 76, in main
    result = create_models(
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/omymodels/from_ddl.py", line 54, in create_models
    output = generate_models_file(
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/omymodels/from_ddl.py", line 115, in generate_models_file
    models_str += generator.generate_model(
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/omymodels/models/pydantic/core.py", line 103, in generate_model
    model += self.generate_attr(column, defaults_off) + "\n"
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/omymodels/models/pydantic/core.py", line 67, in generate_attr
    column_str = self.add_default_values(column_str, column)
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/omymodels/models/pydantic/core.py", line 78, in add_default_values
    column.default = f"'{column['default']}'"
TypeError: 'Column' object is not subscriptable

Transformation target to Pydal

Would you be so kind an consider to to add support for transform e.g. Pydal Tables definitions to SQLAlchemy table defintions?

If that would be something easy for you?

Or could you give me a pointer where I could implement that logic in your code?

You already have support for pydal in Py-Models-Parser.

It would help the py4web community https://github.com/web2py/py4web a lot.

The use case ist the pydal db migration tool is good, but not as mature as alembic.

If you could translate our models to Sqlalchemy models easily, we could quickly adopt alembic for migrations.

Thank you for your consideration.

Btw: Py4Web is a really cool and easy to use Python web framework, made by the creator of web2py.

If you interested why its cool and some people prefer it to flask or django (mainly producitivy and speed),
happy to give you a tour.

Syntax Error arises when NOT NULL and FOREIGN KEY exist in the same column

Describe the bug
When a column is set as NOT NULL and a foreign key is added, Positional argument cannot appear after keyword arguments error arises in models.py.

To Reproduce
Steps to reproduce the behavior:

Create database.sql.

CREATE TABLE "merchants" (
  "id" int PRIMARY KEY,
  "merchant_name" varchar
);

CREATE TABLE "products" (
  "id" int PRIMARY KEY,
  "merchant_id" int NOT NULL
);

ALTER TABLE "products" ADD FOREIGN KEY ("merchant_id") REFERENCES "merchants" ("id");

Following models.py is produced after create_models.

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Merchants(Base):

    __tablename__ = 'merchants'

    id = sa.Column(sa.Integer(), primary_key=True)
    merchant_name = sa.Column(sa.String())


class Products(Base):

    __tablename__ = 'products'

    id = sa.Column(sa.Integer(), primary_key=True)
    merchant_id = sa.Column(sa.Integer(), nullable=False, sa.ForeignKey('merchants.id'))

Expected behavior
In models.py, the last sentence should be like following.

merchant_id = sa.Column(sa.Integer(), sa.ForeignKey('merchants.id'), nullable=False)

Typo in types.py

ddl = """
CREATE TABLE qwe (
    id integer NOT NULL,
    name character varying(255),
);
"""
result = create_models(ddl, models_type='sqlmodel')['code']

This code fails due to the typo in

omymodels/omymodels/types.py

Line 11 in 4893e38

"character_vying",

When character_vying changed to character varying everything seems to work.

Default value of `now()` always returns same time

Describe the bug

Using now() as the default value of a timestamp column in the DDL results in a hard-coded default timestamp.

To Reproduce

Run the following:

ddl = """
CREATE TABLE asdf (
  t TIMESTAMP DEFAULT now()
);
"""
print(omymodels.create_models(ddl=ddl, models_type="dataclass")["code"])

The output is

import datetime
from dataclasses import dataclass


@dataclass
class Asdf:

    t: datetime.datetime = datetime.datetime.now()

Using the returned code, run the following:

>>> a = Asdf()
>>> a.t
datetime.datetime(2021, 7, 12, 9, 26, 27, 251799)

# Wait several seconds, then

>>> b = Asdf()
>>> b.t
datetime.datetime(2021, 7, 12, 9, 26, 27, 251799)

Note that the timestamps are identical, even though I waited several seconds between the two. This is because the default value is evaluated upon class creation, and doesn't change after that. The proper solution (for dataclasses, at least), would use field() to define the column, like so:

import datetime
from dataclasses import dataclass
from dataclasses import field


@dataclass
class Asdf:

    t: datetime.datetime = field(default_factory=datetime.datetime.now)

This will result in the correct behavior, where the timestamp changes for every instantiation.

Expected behavior

The default value should be the time when the instance of the class was created, not when the module defining its class was first imported.

Additional context

Python: 3.8.2
Version: 0.8.1

create_models() exits entire program if no tables are found

Describe the bug

If create_models() doesn't find tables in the DDL string it's given, it exits the program.

To Reproduce

In an interpreter (IDLE, IPython, etc.) run create_models() with a string containing only whitespace. The interpreter exits.

Expected behavior

If this function is to be used in a library, ideally an exception would be thrown with information about the error. The issue is here; I'd greatly appreciate it if you could throw, say, a ValueError or something instead of exiting.

Personally, when I'm making a library, I like creating my own error hierarchy (e.g. here) so the source of the exception is very clear to the caller, and they can selectively catch certain errors. For example, here I would create two exceptions:

# omymodels/errors.py

class Error(Exception):
    """Base class for most if not all errors thrown by this library""

class NoTablesFoundError(Error):
    """No tables were found in the DDL string."""

Then instead of sys.exit() I would throw NoTablesFoundError. This design allows me to add new exceptions as I see fit, and standardize error messages (example here).

These are my personal design habits, don't let me dictate yours!

Additional context

Python: 3.8.2
Version: 0.8.3

map varchar(n) to Field(..., max_length=n) when targeting a pydantic model type

Is your feature request related to a problem? Please describe.
The pydantic BaseModel (as well as dataclasses) validate the data on instantiation

Describe the solution you'd like
starting from FIELD1 VARCHAR(256)
instead of generating field1: str generate field1: str = Field(..., max_length=256)

Additional context
I like this project and think this is a step forward to make it more useful

Conflict dependency version with omymodels and simple-ddl-generator

Describe the bug

I cannot use simple-ddl-generator with omymodels together because omymodels depends on the old version of table-meta.
I use poetry to manage my dependent and it does not permit the use both together.
As you are the creator of both packages, I believe you could update the dependencies of omymodels

To Reproduce

Because no versions of simple-ddl-generator match >0.4.1,<0.5.0
and simple-ddl-generator (0.4.1) depends on table-meta (0.3.2), simple-ddl-generator (>=0.4.1,<0.5.0) requires table-meta (0.3.2).
And because omymodels (0.13.0) depends on table-meta (>=0.1.5,<0.2.0)
and no versions of omymodels match >0.13.0,<0.14.0, simple-ddl-generator (>=0.4.1,<0.5.0) is incompatible with omymodels (>=0.13.0,<0.14.0).
So, because open-insurance depends on both omymodels (^0.13.0) and simple-ddl-generator (^0.4.1), version solving failed.

ps: Again, congratulations on the excellent work you have been done

Cannot parse schema prefix

Hello, this is a very useful project. It would be nice if you can parse the schema in DDL file.

CREATE TABLE "my-custom-schema"."users" (
  "id" SERIAL PRIMARY KEY,
  "name" varchar,
  "created_at" timestamp,
  "updated_at" timestamp,
  "country_code" int,
  "default_language" int
);

Thanks for a nice project!

Updated:

"schema-name"."table-name" works fine, but "schema--name"."table--name" ( with double - symbol) doesn't work

Enhancement - Support Enum Type

Currently, compiler cannot parse table with custom enum type
Eg.

CREATE TYPE "schema--notification"."ContentType" AS
 ENUM ('TEXT','MARKDOWN','HTML');
CREATE TABLE "schema--notification"."notification" (
    content_type "schema--notification"."ContentType"
);

Expected result

from enum import Enum
from gino import Gino

db = Gino()


class ContentType(Enum):
    HTML = "HTML"
    MARKDOWN = "MARKDOWN"
    TEXT = "TEXT"

class Notification(db.Model):
    __tablename__ = 'notification'
    __table_args__ = dict(schema="schema--notification")

    content_type =db.Column(db.Enum(ContentType))
)

Support Python 3.11

Describe the bug
DOES NOT work in Python 3.11 because of using py-models-parser according to xnuinside/py-models-parser#10

To Reproduce
Run in Python 3.11

Expected behavior
Support Python 3.11

Screenshots

ddl with DEFAULT CHARSET leads to `ValueError: Found ALTER statement to not existed TABLE`

Describe the bug
ddl with DEFAULT CHARSET leads to

Traceback (most recent call last):
  File "/home/hrehfeld/projects/ext/omymodels/.venv/bin/omm", line 8, in <module>
    sys.exit(main())
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/omymodels/cli.py", line 76, in main
    result = create_models(
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/omymodels/from_ddl.py", line 45, in create_models
    data = get_tables_information(ddl, ddl_path)
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/omymodels/from_ddl.py", line 26, in get_tables_information
    tables = parse_from_file(ddl_file, group_by_type=True)
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/simple_ddl_parser/ddl_parser.py", line 231, in parse_from_file
    return DDLParser(df.read(), **(parser_settings or {})).run(file_path=file_path, **kwargs)
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/simple_ddl_parser/parser.py", line 346, in run
    self.tables = result_format(self.tables, output_mode, group_by_type)
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/simple_ddl_parser/output/common.py", line 181, in result_format
    tables_dict = process_alter_and_index_result(
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/simple_ddl_parser/output/common.py", line 145, in process_alter_and_index_result
    tables_dict = add_alter_to_table(tables_dict, table)
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/simple_ddl_parser/output/common.py", line 80, in add_alter_to_table
    target_table = get_table_from_tables_data(tables_dict, table_id)
  File "/home/hrehfeld/projects/ext/omymodels/.venv/lib/python3.10/site-packages/simple_ddl_parser/output/common.py", line 29, in get_table_from_tables_data
    raise ValueError(
ValueError: Found ALTER statement to not existed TABLE `option` with SCHEMA None

To Reproduce
use this ddl:

CREATE TABLE `option` (
  `opt_id` int(10) UNSIGNED NOT NULL,
) DEFAULT CHARSET=utf8;

ALTER TABLE `option`
  ADD PRIMARY KEY (`opt_id`);

then just omm option.sql.

This runs without error:

CREATE TABLE `option` (
  `opt_id` int(10) UNSIGNED NOT NULL,
) CHARSET=utf8;

ALTER TABLE `option`
  ADD PRIMARY KEY (`opt_id`);

pretty sure this is valid mysql syntax: https://dev.mysql.com/doc/refman/8.0/en/create-table.html

** version **
omymodels-0.13.0

back_populates in tables One-To-Many (SQLAlchemy)

Is your feature request related to a problem? Please describe.
The packages is beautiful and makes greate work with make a SQL to a model.

I repair unfortunately that Omymodels do not make back_populates in SQLAlchemy ORM when a create a model have a One To Many. Reference (https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html)

Describe the solution you'd like

I do not understand now how to package works (yet), but I belive that it is necessary check that table have a relationship (ForeignKey) and put in refereced table a line like:

children = relationship("Child")

I belive that necessary create a parameter to use or not this option to put back_populates.

SolverProblemError

Describe the bug
Can;t install dependencies

To Reproduce
Use Python 3.9?

Expected behavior
Be able to install dependencies

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS] win10

Additional context

Installing dependencies from lock file

[SolverProblemError]
The current project's Python requirement (3.9.6) is not compatible with some of the required packages Python requirement:

• dataclasses requires Python >=3.6, <3.7

Because no versions of table-meta match >0.1.5,<0.2.0
and table-meta (0.1.5) depends on dataclasses (>=0.8,<0.9), table-meta (>=0.1.5,<0.2.0) requires dataclasses (>=0.8,<0.9).
Because dataclasses (0.8) requires Python >=3.6, <3.7
and no versions of dataclasses match >0.8,<0.9, dataclasses is forbidden.
Thus, table-meta is forbidden.
So, because omymodels depends on table-meta (^0.1.5), version solving failed.

The lock file might not be compatible with the current version of Poetry.
Upgrade Poetry to ensure the lock file is read properly or, alternatively, regenerate the lock file with the poetry lock command.

Simple Online App

Simple Online App 😀

https://archon-omymodels-online.hf.space

Option to have different schemas in separate files, with separate Base

It would be really nice and helpful if it were possible to have SQLAlchemy model "split" in to separate, schema specific, files with a schema specific Base.

As an example, if a DDL had something like

CREATE SCHEMA schema1;
CREATE SCHEMA schema2;
CREATE TABLE schema1.table1 (
...
);
CREATE TABLE schema2.table2 (
...
);

the -m sqlalchemy in combination with a new option would produce two model files,

schema1.py
schema2.py

with

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Schema1Base = declarative_base()
class Table1(Schema1Base):

    __tablename__ = "table1"
    __table_args__ = {"schema": "schema1"}

and

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Schema2Base = declarative_base()
class Table2(Schema2Base):

    __tablename__ = "table2"
    __table_args__ = {"schema": "schema2"}

respectively. This would be amazingly helpful & useful feature as it would make it possible to really separate the schemas.

Any update if there will be SQL Alchemy 2 support?

Any update if there will be SQL Alchemy 2 support?

Add support for SQLModel output

Is your feature request related to a problem? Please describe.
I would like to use SQLModel https://sqlmodel.tiangolo.com/ files with FastAPI https://fastapi.tiangolo.com/. I could use the sqlalchemy adaptor, but then I also would need to make pydantic models.

Additional context
I have started the work here: https://github.com/cfhowes/omymodels/tree/sqlmodel_support Comments welcome, will submit a PR once I have some more verifications in place.

Any way of keeping the exsisting column names as they are?

When using dll with capitals in the name for dataclasses anything with capitals gets renamed such as NHS become n_h_s in the dataclass. is there any way of creating the fieldname as they are in the dll?

GBMSM smallint becomes g_b_m_s_m: int = None