1

I'm trying to create an Order model using SQLAlchemy with a Column having an Array of ProductItem Class defined, but it is throwing an exception Both the classes are defined in the same file

models.py

# Product Item Class
class ProductItem(Base):
    __tablename__ = 'product_items'
    id = Column(Integer, primary_key=True, index=True)
    productName = Column(String,index=True,nullable=False)
    productBrand = Column(String, nullable=False)
    
    def __repr__(self):
        return "<ProductList(productName = '%s', productCategoryName = '%s')>" % (
            self.productName,
            self.productCategoryName
        )

#Order Class
class Order(Base):
    __tablename__ = 'orders'
    id= Column(Integer, index=True, primary_key=True)
    dateCreated = Column(DateTime,nullable=False)
    dateDelivered = Column(DateTime, nullable=True)
    orderItem = Column(ARRAY(ProductItem), nullable=False)  # This Column
    orderStatus = Column(String, nullable=False)

Exception:

>alembic revision --autogenerate -m "Initial db with order"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'orders'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_orders_businessId' on '['businessId']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_orders_customerId' on '['customerId']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_orders_id' on '['id']'
INFO  [alembic.ddl.postgresql] Detected sequence named 'product_categories_id_seq' as owned by integer column 'product_categories(id)', assuming SERIAL and omitting
INFO  [alembic.ddl.postgresql] Detected sequence named 'business_billing_id_seq' as owned by integer column 'business_billing(id)', assuming SERIAL and omitting
INFO  [alembic.ddl.postgresql] Detected sequence named 'business_timings_id_seq' as owned by integer column 'business_timings(id)', assuming SERIAL and omitting
INFO  [alembic.ddl.postgresql] Detected sequence named 'product_items_id_seq' as owned by integer column 'product_items(id)', assuming SERIAL and omitting
INFO  [alembic.ddl.postgresql] Detected sequence named 'inventory_id_seq' as owned by integer column 'inventory(id)', assuming SERIAL and omitting
Generating D:\app Backend\db-migration\versions\ceff7117b4cb_initial_db_without_order.py ...  done
Traceback (most recent call last):
  File "c:\users\mahesh\appdata\local\programs\python\python39\lib\runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "c:\users\mahesh\appdata\local\programs\python\python39\lib\runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "D:\app Backend\env\Scripts\alembic.exe\__main__.py", line 7, in <module>
  File "d:\app backend\env\lib\site-packages\alembic\config.py", line 559, in main
    CommandLine(prog=prog).main(argv=argv)
  File "d:\app backend\env\lib\site-packages\alembic\config.py", line 553, in main
    self.run_cmd(cfg, options)
  File "d:\app backend\env\lib\site-packages\alembic\config.py", line 530, in run_cmd
    fn(
  File "d:\app backend\env\lib\site-packages\alembic\command.py", line 219, in revision
    scripts = [script for script in revision_context.generate_scripts()]
  File "d:\app backend\env\lib\site-packages\alembic\command.py", line 219, in <listcomp>
    scripts = [script for script in revision_context.generate_scripts()]
  File "d:\app backend\env\lib\site-packages\alembic\autogenerate\api.py", line 533, in generate_scripts
    yield self._to_script(generated_revision)
  File "d:\app backend\env\lib\site-packages\alembic\autogenerate\api.py", line 449, in _to_script
    return self.script_directory.generate_revision(
  File "d:\app backend\env\lib\site-packages\alembic\script\base.py", line 658, in generate_revision
    script = Script._from_path(self, path)
  File "d:\app backend\env\lib\site-packages\alembic\script\base.py", line 851, in _from_path
    return cls._from_filename(scriptdir, dir_, filename)
  File "d:\app backend\env\lib\site-packages\alembic\script\base.py", line 904, in _from_filename
    module = util.load_python_file(dir_, filename)
  File "d:\app backend\env\lib\site-packages\alembic\util\pyfiles.py", line 97, in load_python_file
    module = load_module_py(module_id, path)
  File "d:\app backend\env\lib\site-packages\alembic\util\compat.py", line 182, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 786, in exec_module
  File "<frozen importlib._bootstrap_external>", line 923, in get_code
  File "<frozen importlib._bootstrap_external>", line 853, in source_to_code
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "D:\App Backend\db-migration\versions\ceff7117b4cb_initial_db_without_order.py", line 25
    sa.Column('orderItem', sa.ARRAY(app.models.<app.models.ProductItem object at 0x00000256DF9ED100>), nullable=False),
                                                  ^
SyntaxError: invalid syntax

Searched a lot but couldn't find any docs describing custom types for the array. I need a way to store an array of ProductItem class in Order Class. Any help would be appreciated. Thank you!

0

1 Answer 1

1

From the PostgreSQL: documentation:

PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.

Hence, you will not be able to create an ARRAY column of just any Python class, unless you create a corresponding user-defined type in the database, which is most likely against the reason of how one should use SQLAlchemy.

What you need is a way to create a Many To Many relationship which requires an association table.


See also the answer to this question, a similar question although it focused on enforcing ForeignKey constraint from the ARRAY datatype.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.