SQLAlchemy inserting data into two tables

Question

I am just working on my first App based on SQLAlchemy and after couple hours of work with the documentation and some videos, I still can't fix the issue.

My app is a simple CRUD grocery list. I want to keep the category of the product in separate table so here comes relationship module of the SQLAlchemy. Error msg gives me no hint at all tbh.

engine = create_engine(my_database, echo = True)

connection = engine.connect()

Base = declarative_base()
session = sessionmaker(bind=engine)

class MyEnum(enum.Enum):
    one = "pieces"
    two = "kg"

class ProductTable(Base):
    __tablename__ = 'product'
    product_id = Column(Integer, primary_key=True)
    product_name = Column(String(30), nullable=False)
    product_quantity = Column(Integer, nullable=False)
    product_type = Column(Enum(MyEnum), nullable=False)
    category_id = Column(Integer, ForeignKey('category.id'), nullable=False)
    category = relationship("category", back_populates="product")
    product_description = Column(String(255))


class CategoryTable(Base):
    __tablename__ = 'category'
    id = Column(Integer, primary_key=True)
    category_name = Column(String(25), nullable=False)

Base.metadata.create_all(engine)

session = session()

cc_product = ProductTable(product_id=1,
                          product_name="cucumber",
                          product_quantity="1",
                          product_type="kg",
                          product_description="For the salad")

cc_category= CategoryTable(category_name="vegetables")


session.add(cc_product, cc_category)

session.commit()

I. Creation of the tables finished smoothly with no errors, however, is the creation itself designed properly? Each product has single category but one category should be assigned to one or more product. I made that based on one to one relationship.

II. Inserting data to both tables. I want to insert data like:

Product_id = 1
Product_name = Cucumber
Product_quantity = 1
Product_type = "kg" or "pieces"
Category = Vegetables ( from the category table)
Description = "blah blah blah"

I think there is something wrong not only with the data inserting process but also with the tables creation.

Here is the error, which tbh, doesn't tell me anything:

sqlalchemy.exc.ArgumentError: relationship 'category' expects a class or a mapper argument (received: <class 'sqlalchemy.sql.schema.Table'>)

Possible duplicate of [ArgumentError: relationship expects a class or mapper argument](https://stackoverflow.com/questions/25002620/argumenterror-relationship-expects-a-class-or-mapper-argument) — Ilja Everilä, Dec 03 '17 at 15:57
You have some small errors, like passing [`Session.add()`](http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.add) multiple instances as arguments (the second will not be added actually), using the wrong target in the relationship, which trips mapper configuration, and using `back_populates` without defining the other end (instead of `backref`). The product and category are not linked either. I'd recommend (re)reading the [ORM tutorial](http://docs.sqlalchemy.org/en/latest/orm/tutorial.html). — Ilja Everilä, Dec 03 '17 at 16:04
I found some minor errors, about Session.add() and the fact, that these tables aren't actually linked but I just lack with the idea how to set them correctly. The documentation is great but since it's referring to so many different concepts at the same time, I am so confused. If there is anyone, who could describe how this relationship for this specific case should look like, i would highly appreciate that — michael93pl, Dec 03 '17 at 17:00
Instead of trying to form a relationship to the `Table` (the name you've given in the model with `__tablename__`), create a relationship to the model: `relationship("CategoryTable", ...)`. And with the linking I meant that you should for example do `cc_product.category = cc_category` before commit. — Ilja Everilä, Dec 03 '17 at 17:07
@IljaEverilä like that?: `category_id = Column(Integer, ForeignKey('product.product_id'))` and `categories = relationship("CategoryTable",backref='product')` — michael93pl, Dec 03 '17 at 17:53
Yes on the latter, but why `ForeignKey('product.product_id')`? Leave `category_id` as it was. — Ilja Everilä, Dec 03 '17 at 19:58

score 0 · Answer 1 · edited Dec 04 '17 at 07:29

You have two mistakes:

you wrote "category" as the Mapper class instead of "CategoryTable"
forgot to create products on 'CategoryTable'

class ProductTable(Base):
    __tablename__ = 'product'
    product_id = Column(Integer, primary_key=True)
    product_name = Column(String(30), nullable=False)
    product_quantity = Column(Integer, nullable=False)
    product_type = Column(Enum(MyEnum), nullable=False)
    category_id = Column(Integer, ForeignKey('category.id'), nullable=False)
    categories = relationship("CategoryTable", back_populates="products")
    product_description = Column(String(255))


class CategoryTable(Base):
    __tablename__ = 'category'
    id = Column(Integer, primary_key=True)
    category_name = Column(String(25), nullable=False)
    products = relationship('ProductTable', back_populates='categories')

Some more changes are still needed:

change CategoryTable to Category (also for ProductTable, better names)
you'll have constraints failing after you'll get things running...

I have manged to make it up and running by doing it this way: https://pastebin.com/k1j0KRAM Even though I don't exactly understand why but it works.. — michael93pl, Dec 03 '17 at 22:07

SQLAlchemy inserting data into two tables

1 Answers1