2

Edit 2 :

class MethodCall(Base):
    __tablename__ = 'methodCall'
    id = Column(Integer, primary_key=True)
    name = Column(String( 255 ), unique = True)
    actions = relationship("Action")

def test_bulk_insert():
    ses = DBSession()
    mc = MethodCall()
    a1 = Action()
    a2 = Action()
    a1.lfn = getFile(ses, 'toto')
    a2.lfn = getFile(ses, 'toto')
    mc.actions.append(a1)
    mc.actions.append(a2)

Edit : Now i have two tables with a relationship :

class Action(Base):
    __tablename__ = 'action'
    id = Column(Integer, primary_key=True)
    file_id = Column(Integer, ForeignKey('file.id'))
    lfn = relationship("File")
    methodCall_id = Column(Integer, ForeignKey('methodCall.id'))

class File(Base):
    __tablename__ = 'file'
    id = Column(Integer, primary_key=True)
    name = Column(String( 255 ), unique = True)

When I insert two actions like this :

a1 = Action()
a2 = Action()
a1.lfn = getFile(ses, 'toto')
a2.lfn = getFile(ses, 'toto')
ses.bulk_save_objects([a1,a2])

def getFile(session,n):
    instance = session.query( File ).filter_by( name = n ).first()
    if not instance:
        instance = File( name=n )
        session.add( instance )
        session.commit()
    return instance

The file_id in table action is not set, it is null. How can I do ?

I have a little problem with SQLALchemy when inserting row.

I have a table file :

class File(Base): __tablename__ = 'file' id = Column(Integer, primary_key=True,autoincrement=True)

when I insert two files like this :

f1 = File( id = 1 )
f2 = File( id =2 )
session.add(f1)
session.add(f2)
session.commit()

It's a bulk insert :

2015-06-16 11:27:57,057 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-06-16 11:27:57,058 INFO sqlalchemy.engine.base.Engine INSERT INTO file (id) VALUES (%s)
2015-06-16 11:27:57,058 INFO sqlalchemy.engine.base.Engine ((1,), (2,))
2015-06-16 11:27:57,060 INFO sqlalchemy.engine.base.Engine COMMIT

but when I don't precise id, the insertion is one by one:

f1 = File(  )
f2 = File( )
session.add(f1)
session.add(f2)
session.commit()

2015-06-16 11:28:46,648 INFO sqlalchemy.engine.base.Engine INSERT INTO file () VALUES ()
2015-06-16 11:28:46,649 INFO sqlalchemy.engine.base.Engine ()
2015-06-16 11:28:46,654 INFO sqlalchemy.engine.base.Engine INSERT INTO file () VALUES ()
2015-06-16 11:28:46,654 INFO sqlalchemy.engine.base.Engine ()
2015-06-16 11:28:46,655 INFO sqlalchemy.engine.base.Engine COMMIT

Is there any way when I don't precise id to insert in bulk with auto-increment?

Thank you

coberger
  • 21
  • 3
  • What's wrong? The id field is assigned by DB in this case, not by SQLAlchemy – Aleksandr Kovalev Jun 16 '15 at 12:42
  • In this case it's not a bulk insertion, and i want a bulk insertion – coberger Jun 16 '15 at 13:23
  • http://docs.sqlalchemy.org/en/latest/orm/session_api.html?highlight=bulk_save_objects#sqlalchemy.orm.session.Session.bulk_save_objects might help – pavel_form Jun 16 '15 at 13:25
  • thank you but if i have an other table with a foreign key, it doesn't work, the foreign key is null – coberger Jun 16 '15 at 15:08
  • Please include your full example in question. – pavel_form Jun 16 '15 at 15:37
  • Thank you for answering, i have edit my post – coberger Jun 17 '15 at 07:58
  • Hmm, looks like bulk operations bypass relationship logic. However, for your current example solution is present. Instead of `a1.lfn = getFile(ses, 'toto')` use `a1.file_id = getFile(ses, 'toto').id`. So instead of using relationships you will directly use foreign key value. – pavel_form Jun 17 '15 at 10:24
  • Thank you, but if now i have the methodCall table, is it possible to keep relationship ? – coberger Jun 17 '15 at 10:58
  • No, all bulk operations bypass relationship logic, you'll have to manually work with foreign keys. In your example you'll first need to insert `MethodCall` record, then assign its `id` to `methodCall_id` field of `Action` objects. – pavel_form Jun 17 '15 at 11:06
  • Thank you very much for you help – coberger Jun 17 '15 at 16:14

0 Answers0