Creating a primary key in sqlalchemy when creating a table instance

Question

I have created a tmp table from a sqllite table which is a subset of the original table based on various selection criteria. A sample is in the screenshot.

I'm trying to loop through the table records one at a time in order to update a field in each. I have run into a problem detailed in Mapper could not assemble any primary key columns . Based on the recommendations at http://docs.sqlalchemy.org/en/latest/faq/ormconfiguration.html#how-do-i-map-a-table-that-has-no-primary-key . Based on this discussion I do have a candidate key which is a unique id : the column 'id'. Therefore, I have changed my code to:

source_table= self.source
engine = create_engine(db_path)
Base = declarative_base()
# metadata = Base.metadata
# Look up the existing tables from database
Base.metadata.reflect(engine)

# Create class that maps via ORM to the database table
table = type(source_table, (Base,), {'__tablename__': source_table}, __mapper_args__ = {
    'primary_key':'id'
})

Session = sessionmaker(bind=engine)
session = Session()
i = 0
for row in session.query(table).limit(500):

    i += 1
    print object_as_dict(row)

But this gives:

TypeError: type() takes 1 or 3 arguments

How can I use the mapper_args argument to identify id as the primary key

edit:

I tried :

    table = type(source_table, (Base,), {'__tablename__': source_table}, {"__mapper_args__": {"primary_key": [Base.metadata.tables[source_table].c.id]}})

giving:

TypeError: type() takes 1 or 3 arguments

univerio · Accepted Answer · 2017-06-21 19:07:13Z

2

__mapper_args__ needs to be an attribute defined on the class. Whereas you need to write

class Foo(Base):
    ...
    __mapper_args__ = {...}

when defining a class with the class syntax, you need to write

type("Foo", (Base,), {..., "__mapper_args__": {...}})

when defining a class with the type function.

Note that __mapper_args__ may need to be

{"primary_key": [Base.metadata.tables[source_table].c.id]}

instead of

{"primary_key": "id"}

for it to work properly.

answered Jun 21, 2017 at 19:07

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1592380 Over a year ago

Thank you, I'm almost there but would you mind checking the edit?

univerio Over a year ago

@user61629 type takes three arguments. You're giving it four. It needs to be type(source_table, (Base,), {"__tablename__": source_table, "__mapper_args__": ...}).

user1592380 Over a year ago

Final answer: -> table = type(source_table, (Base,), {"tablename": source_table, "mapper_args": {"primary_key":[Base.metadata.tables[source_table].c.id]}})

Collectives™ on Stack Overflow

Creating a primary key in sqlalchemy when creating a table instance

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related