CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Jason Drain
Jason Drain

Reputation: 85

Adding related data to a MySQL database with sqlalchemy

class A(Base):
     __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    name = Column(String(1000))

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    name = Column(String(1000))

class C(Base):
    __tablename__ = 'c'

    id = Column(Integer, primary_key=True)
    b_id = Column(Integer, ForeignKey('b.id'))
    name = Column(String(1000))
    a_id = Column(Integer, ForeignKey('a.id'))

    b = relationship('B', backref=backref('cb'), foreign_keys = b_id)
    a = relationship('A', backref=backref('ca'), foreign_keys = a_id)

connect = engine.connect()
Base.metadata.create_all(bind=engine)

I have created classes to play with creating tables and adding data to a MySQL database with sqlalchemy. The problem I am facing right now is as follows.

If I want to add an entry to table c all I need is code like this.

c_ent = C(name = 'c1')
a1 = A(name = 'a1')
b1 = B(name = 'b1')

a1.ca = [c_ent]
b1.cb = [c_ent]

list1 = [a1, b1]

sess = create_session(name)
for i in list1:
    sess.add(i)

sess.commit()
sess.close()

This all works the way I need it to and the foreign keys for b_id and a_id in the c entry match those of the corresponding ids in a and b.

However, suppose after committing to the database and closing the session I now decide to add another entry to c, 'c2'. I want this entry to have the same relationship to tables a and b as my first one (so that a_id and b_id are the same as for the entry named 'c1'). However, if I use code similar to the first entry, sqlalchemy will just create duplicate entries in table a and b with the names 'a1' and 'b1' having id numbers of 2 instead of 1. Then, my entry in table c will have the correct id numbers, but they will be linked to the duplicate entries.

I want to be able to add an entry to c however many times I choose and relate it to tables a and b without creating new entries in a and b each time. How can I avoid this redundancy?

Upvotes: 2

Views: 106

Answers (1)

Aleksandr Kovalev
Aleksandr Kovalev

Reputation: 3734

Try to do it this way:

c2 = C(name='c2')
c2.a = a1
c2.b = b1

sess = create_session(name)
sess.add(c2)
sess.commit()
sess.close()

Full code with using sqlite:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import relationship, backref, sessionmaker


Base = declarative_base()


class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    name = Column(String(1000))


class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    name = Column(String(1000))


class C(Base):
    __tablename__ = 'c'

    id = Column(Integer, primary_key=True)
    b_id = Column(Integer, ForeignKey('b.id'))
    name = Column(String(1000))
    a_id = Column(Integer, ForeignKey('a.id'))

    b = relationship('B', backref=backref('cb'), foreign_keys=b_id)
    a = relationship('A', backref=backref('ca'), foreign_keys=a_id)

engine = create_engine('sqlite:////tmp/temp.db')

connect = engine.connect()
Base.metadata.create_all(bind=engine)

Session = sessionmaker(bind=engine)
sess = Session()

c_ent = C(name='c1')
a1 = A(name='a1')
b1 = B(name='b1')

a1.ca = [c_ent]
b1.cb = [c_ent]

list1 = [a1, b1]

for i in list1:
    sess.add(i)
sess.commit()

c2 = C(name='c2')
c2.a = a1
c2.b = b1
sess.add(c2)
sess.commit()

sess.close()

After execution:

$ sqlite3 /tmp/temp.db 
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM c;
1|1|c1|1
2|1|c2|1
sqlite> SELECT * FROM a;
1|a1
sqlite> SELECT * FROM b;
1|b1

As you can see, there is no duplication of data. You have only one entry in table a and only one entry in table b.

Upvotes: 1

Related Questions