Flask-SQLAlchemy Many-to-many relationship : How to insert data

Question

I have created a many-to-many relationship in my Flask-Sqlalchemy models. However it seems that I don't well understand how to fill my database. Could you please tell me how I suppose to write my code to fill my database correctly?

Here are my models:

company_contact = db.Table('company_contact', db.Model.metadata,
    db.Column('company_id', Integer, ForeignKey('company.id')),
    db.Column('contact_id', Integer, ForeignKey('contact.id'))
)

class Company(db.Model):
    __tablename__ = 'company'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    contacts = db.relationship('Contact', secondary=company_contact, back_populates="companies")
    company_name = db.Column(db.String(100))

    def __repr__(self):
        return '<Company {}{}>'.format(self.contacts, self.company_name)


class Contact(db.Model):
    __tablename__ = 'contact'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    last_name = db.Column(db.String(255))
    first_name = db.Column(db.String(255))
    job_position = db.Column(db.String(255))
    companies = db.relationship('Company', secondary=company_contact, back_populates="contacts")
    contact_status = db.Column(db.Enum('Client', 'Prospect', 'Churner', 'Prestataire', 'Reperage',
                                       name='contact_status'))
    start_date = db.Column(db.DateTime, server_default=db.func.now())
    stop_date = db.Column(db.DateTime, default=None)

    def __repr__(self):
        return '<Contact {}>'.format(self.last_name, self.first_name, self.job_position, self.companys,
                                     self.contact_status, self.start_date, self.stop_date)

What I'm trying to do, is to add two Contact with the same company_name. Adding the 1st one is not a problem, everything works fine. However for the 2nd Contact an issue appear when I add this code.

com = Company(company_name='bla')
con = Contact(last_name='Blabla', first_name='Jeff', job_position='Resp Marketing',contact_status='Client')
con.companies.append(com)
db.session.add(con)
db.session.commit()

sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "company_company_name_key" DETAIL: Key (company_name)=(bla) already exists.

I don't know how to write it to allow, two Contact with the same company and also to allow two Company with same Contact.

Thank you so much for your help.

score 5 · Accepted Answer · answered Nov 27 '19 at 16:48

So: your database is empty. You init db, create table with sqlalchemy... (can't remember the command)

then you do this:

com = Company(company_name='bla')
con = Contact(last_name='Blabla', first_name='Jeff', job_position='Resp Marketing',contact_status='Client')
con.companies.append(com)
db.session.add(con)
db.session.commit()

Then you DO this:

#can't remember how to get just one row, is it first instaid of all ? sorry
com_already_created = Company().query.all()[0] 

con = Contact(last_name='Blabla2', first_name='Jeff2', job_position='Resp Marketing',contact_status='Client')

con.companies.append(com_already_created)
#or
com_already_created.contacts.append(con)

db.session.add(con)
db.session.commit()

Your problem was your were creating an element to add to your table Company which already exist (I think you removed PK or unique from "company_name" within "Company" models implementation)

Also: Check for your __repr__, use python3.7 f"string" example :

    def __repr__(self):
        return '<Contact {}>'.format(self.last_name, self.first_name, 
    self.job_position, self.companys, self.contact_status, self.start_date, 
    self.stop_date)

it should be:

def __repr__(self):
    return f'<Contact PK:{self.id}, lst_nm:{self.last_name}, 
    frst_nm:{self.first_name}, jb_pos:{self.job_position>}, 
    cnt_comp:{len(self.companys)}'

#(add more if you want but its getting messy)

Doobeh · Answer 2 · 2019-11-27T16:50:06.123

It sounds like you already have a Company with a company_name of bla in your database. If it already exists, you just need to grab that existing company and then add that object to your Contact. So lets to do that:

# Query the database to find out if we have a company named 'bla'
company = Company.query.filter_by(company_name="bla").first()

# Did we fail to get a company by the name 'bla'?
# If so-- lets create one.
if not company:
    company = Company(company_name="bla")

# Now we're sure to have a company, but not to have a duplicate,
# Let's create a new contact, and add the company.

contact = Contact(
    last_name="Blabla",
    first_name="Jeff",
    job_position="Resp Marketing",
    contact_status="Client",
)

# Finally lets commit the contact and associated company to the
# database.

contact.companies.append(company)
db.session.add(contact)
db.session.commit()

Thank you very much @Doobeh this is works fine. I just changed contact.companies.add(company) --> contact.companies.append(company). :) — Coline Gilles, Nov 27 '19 at 16:42

score 1 · Answer 3 · answered Dec 08 '20 at 17:50

1

Here is the method I used to add data. This one is simple.

    com = Company(company_name='bla')
    db.session.add(com)
    con = Contact(last_name='Blabla', first_name='Jeff', job_position='Resp Marketing', 
    companies= [com] , contact_status='Client')
    db.session.add(con)
    db.session.commit()

answered Dec 08 '20 at 17:50

Nisha

369
4
13

first you need to You init db, create table with sqlalchemy. – Nisha Dec 08 '20 at 17:52

Flask-SQLAlchemy Many-to-many relationship : How to insert data

3 Answers3