How do I transfer records from one table to another?

user_11277108 0
Viewed 52

I have the following example table:

class DestDB(Base):
    __tablename__ = "dest_db"
    __table_args__ = (Index('nameplace', "name", "place"))

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    place = Column(String(100))

I'm quite used to using a dictionary to add records. For example:

dict = {"name": "foo", "place": "bar"}
session.add(DestDB(**dict))

However, how do I transfer records from one table to another? I presume I could query the source table, iterate through each record converting it into a dictionary and then add the result as above. However, I'm sure there must be a neater solution?

There will be duplicate combinations of name and place records in the source database so I don't want to use any kind of bulk operation unless it won't insert any duplicate records.

Let's assume the source database looks like this:

class SourceDB(Base):
    __tablename__ = "source_db"

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    place = Column(String(100))

I only want to transfer the name and place fields.

sqlalchemy python
1 Answers

I suggest sticking to The Zen of Python.

Explicit is better than implicit.

I'd implement a method on ‍SourceDB to provide the dictionary format of the object as I want it.

class SourceDB(Base):
    __tablename__ = "source_db"

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    place = Column(String(100))

    def as_dict(self):
        return dict(name=self.name, place=self.place)

and use it like this

session.add(DestDB(**src_obj.as_dict()))

Although you could do it more dynamically, I'd still recommend the more explicit way.

class SourceDB(Base):
    __tablename__ = "source_db"

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    place = Column(String(100))

    def as_dict(self, fields):
        return dict(field: getattr(self, field) for field in fields if hasattr(self, field))

and doing this

session.add(DestDB(src_obj.as_dict(("name", "place"))))
user_36543520
Related