How can i insert row into table sqlalchemy object?

Question

i have table object that its name is tags and i don't know how to insert new row into it

this is my table object :

tags = db.Table('tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True),
    db.Column('Post_id', db.Integer, db.ForeignKey('post.id'), primary_key=True)
)

i tried to use this query for test :

db.session.add(tags(
        post_id = 1,
        tag_id = 2
))

But I encountered this error :

TypeError: 'Table' object is not callable

maslak · Answer 1 · 2022-01-27T09:21:19.903

18

I encourage you to use SQLAlchemy ORM approach. In your case it would be something like this:

from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Tags(Base):
    __tablename__ = 'tags'

    tag_id = Column(Integer, ForeignKey('tags.id'), primary_key=True)
    post_id = Column(Integer, ForeignKey('posts.id'), primary_key=True)


session.add(Tags(tag_id=1, post_id=2))
session.commit()

Check SQLAlchemy site, they have great docs, recipes and examples!

edited Jan 27 '22 at 09:21

answered May 30 '19 at 17:36

maslak

1,115
3
8
22

2

[Context of question](https://flask-sqlalchemy.palletsprojects.com/en/2.x/models/#many-to-many-relationships) – Paweł Apr 23 '20 at 08:53
This should be the selected answer – RaphWork May 14 '20 at 14:27

score 16 · Answer 2 · answered Sep 09 '20 at 17:44

16

An actual answer for the stated question, you need to get an Insert object from the table:

ins = tags.insert().values(post_id=1, tag_id=2)
db.engine.execute(ins)

answered Sep 09 '20 at 17:44

Maximilian Burszley

18,243
4
34
63

5

`Engine.execute()` is getting removed in 2.0. The SQLAlchemy docs suggest doing `Connection.execute()` https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Engine.execute – ian-campbell Dec 14 '20 at 13:51

Gabe · Accepted Answer · 2023-01-28T19:59:29.010

A many-to-many relationship adds an association table between the two models.

tags = db.Table('tags',
        db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True),
        db.Column('page_id', db.Integer, db.ForeignKey('page.id'), primary_key=True)
    )
    
class Page(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tags = db.relationship('Tag', secondary=tags, lazy='subquery',
        backref=db.backref('pages', lazy=True))

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)

Now you can insert data into the table:

>>> page = Page()
>>> tag = Tag()
>>> page.tags.append(tag)
>>> db.session.add(page)
>>> db.session.commit()

Read this for more information.

How can i insert row into table sqlalchemy object?

3 Answers3