how to insert a dict in sqlite3

Question

Is there any way I can insert dict data to a sqlite3 column? Let's say I have a table named item and I would like to insert co-ordinates like x and y which is a dict {'x': 2, 'y': 4} to the column name called position

I have given the datatype of position as varchar or blob. In both cases it shows me sqlite3.InterfaceError: Error binding parameter :position - probably unsupported type. Is there any way I can insert value {'x': 2, 'y': 4} to position column? what is the best way to insert a dict?

EDIT

for i in range(0, count):
        item = {
            'player': random.choice(players),
            'floor': random.choice(floors),
            'position': {
                'x': random.randint(0, 17),
                'y': random.randint(0, 10)
            },
            'ts': random.randint(ts_2015_01_01, ts_now)
        }
columns = ', '.join(item.keys())
            placeholders = ':'+', :'.join(item.keys())
            query = 'INSERT INTO items (%s) VALUES (%s)' % (columns, placeholders)
            print(query)
            c.execute(query, item)
            connection.commit()

here c is the cursor based on connection on my sqlite db.

Have you tried serializing it to JSON? Then it would just be a string. That assumes you want to store it as an opaque blob of text; if you want to stick your `x` value into one column and your `y` value into another you'll need a different approach. — ChrisGPT was on strike, Dec 11 '20 at 17:48
Can you show what code you use. In theory, what you want to do is definitely possible. — Setop, Dec 11 '20 at 17:48
**Warning**: Stop using the modulo, `%`, operator for string formatting. This has been [de-emphasized](https://stackoverflow.com/a/13452357/1422451) but not officially deprecated *yet* in Python. Instead use `str.format` (Python 2.6+) or f-string (Python 3.6+). — Parfait, Dec 11 '20 at 19:01

Parfait · Accepted Answer · 2020-12-11T22:50:33.653

0

Unlike other RDBMS's, SQLite does not have json types (the most analogous industry format to Python dict). Therefore, you would need to convert the dictionary to a string to store the object in a database table. Consider json.dumps for this:

import json

...
columns = ', '.join(item.keys())
placeholders = ':'+', :'.join(item.keys())

item['position'] = json.dumps(item['position'])
query = 'INSERT INTO items ({0}) VALUES ({1})'.format(columns, placeholders)  # PYTHON 2.6+
query = f'INSERT INTO items ({columns}) VALUES ({placeholders})'              # PYTHON 3.6+
print(query)
# INSERT INTO items (player, floor, position, ts) VALUES (:player, :floor, :position, :ts)

c.execute(query, items)
connection.commit()

edited Dec 11 '20 at 22:50

answered Dec 11 '20 at 19:05

Parfait

104,375
17
94
125

This is not working, I would like to insert this `{'player': 'Mark', 'floor': 'Fist', 'position': {'x': 13, 'y': 10}, 'ts': 1429077674}` to my table `items` – change198 Dec 11 '20 at 21:59
Unlike other RDBMS's, SQLite does not have `json` or `dict` types. You will have to convert the `dict` to string for append to database table. See edit. – Parfait Dec 11 '20 at 22:53

how to insert a dict in sqlite3

1 Answers1