CODEWITHSUNDEEP
pythonsqlalchemy
MathiasRa
MathiasRa

Reputation: 835

Insert SQLalchemy from dictionary

I have a dictionary in which the key names are the same as the column names of the sqlalchemy table/class which is called class.

Thus I figured I could simply use the below code in order update the SQLalchemy class and then insert the newly updated data into the SQL table.

for key, value in d.items():
    setattr(Series, key, value)

session.add(Series)
session.commit()

However, this doesn't work because "Series" is a class. So I wondered what the most effective is to convert a dictionary into sqlalchemy class and then insert into the database.

I prefer that the method will go through each individual key in the dictionary and then update the sql-alchemy class as there sometimes are missing key values in which the Null value should be inserted into the database.

Upvotes: 1

Views: 6200

Answers (1)

SuperShoot
SuperShoot

Reputation: 10872

You have got the right idea with wanting to build your objects directly from the dictionary data, but just on the wrong track when it comes to the difference between class, instance, and how the SQLAlchemy ORM works with them. But don't worry, it's something that we've all have had to learn at some point or another.

Think of the class as a recipe, and the instance as the cake. We use the recipe to make the cake, and we can create as many cakes as we need using the recipe.

So with that in mind, this piece of code here:

for key, value in d.items():
    setattr(Series, key, value)

... is not making cake, it's changing the recipe, or to drop the metaphor, you are modifying the class. Actually, that part of the code does work - setattr() will work on class objects just as well as it will work on instance objects, the issue comes later when you try to add the class object to the SQLAlchemy session. This is the error message that is raised on session.add(Series):

sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.ext.declarative.api.DeclarativeMeta' is not mapped; was a class (__main__.Series) supplied where an instance was required?

When you build a SQLAlchemy ORM class, you inherit from a declarative_base instance (usually called Base) and looks like this: class Series(Base):. That allows SQLAlchemy to do some stuff behind the scenes when creating an instance which it uses to translate changes to the instance into changes to the database. When you add an object to the session, SQLAlchemy looks for that 'instrumentation', but it doesn't exist on the class, only on the instances made with that class, hence the error.

Referring back to the error message, SQLAlchemy has actually worked out the error that you've made: was a class (__main__.Series) supplied where an instance was required?.

The simplest part is correcting the mistake. We create an instance of our class using the dictionary data, add the instance to the session and commit:

instance = Series(**d)
# The ** is called dictionary unpacking which you can google if you need.
# If an attribute value doesn't exist in the dictionary, it will be None by
# default.
session.add(instance)
session.commit()

Upvotes: 4

Related Questions