Fall back to NULL if dict key is missing in Python cursor Insert statement

Question

I'm trying to use a Python dictionary to create a SQLite record as follows, but sometimes one of the keys might not exist, which causes an error, so I've tried to make sure all keys always exist and will be populated as NULL using None, but this also doesn't work ('NoneType' object is not iterable). What am I missing?

    def insert_device(self, device):
    try:
        db = sqlite3.connect(self.db_path)
        cursor = db.cursor()
        for column in cursor.description:
            print(column)
            if column not in device:
                device[column] = None
        cursor.execute('''INSERT INTO devices(created, name, location_id, model, port) VALUES(CURRENT_TIMESTAMP,?,?,?,?)''', [device['name'], device['location_id'], device['model'], device['port']])
        cursor.close()
        db.commit()
        db.close()
    except Exception as e:
        self._logger.error("Error inserting device into database: %s" % e)

GProst · Accepted Answer · 2020-10-02 20:58:06Z

1

I believe the issue you're having is because cursor.description is None and you try to iterate over it. And it's None cause you didn't query anything.

You can simply use device.get(column) instead of device[column], get method will either return a key value on a dict if exists or None otherwise.

edited Oct 2, 2020 at 20:58

answered Oct 2, 2020 at 19:35

GProst

10.3k3 gold badges28 silver badges51 bronze badges

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

1 Comment

StringsOnFire Over a year ago

Ah, I should have read about how that works. Any way to use cursor.description in this way so I don't need to explicitly specify all possible columns?

Collectives™ on Stack Overflow

Fall back to NULL if dict key is missing in Python cursor Insert statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related