Python dictionary to sqlite database

Question

I'm trying to write a dictionary into an existing sql database, but without success giving me:

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Based on my minimal example, has anzbody some useful hints? (python3)

Command to create the empty db3 anywhere on your machine:

CREATE TABLE "testTable" (
        sID INTEGER NOT NULL UNIQUE PRIMARY KEY,
        colA REAL,
        colB TEXT,
        colC INTEGER);

And the code for putting my dictionary into the database looks like:

import sqlite3

def main():
    path = '***anywhere***/test.db3'
    data = {'sID': [1, 2, 3],
            'colA': [0.3, 0.4, 0.5],
            'colB': ['A', 'B', 'C'],
            'colC': [4, 5, 6]}
    db = sqlite3.connect(path)
    c = db.cursor()
    writeDict2Table(c, 'testTable', data)
    db.commit()
    db.close()
    return

def writeDict2Table(cursor, tablename, dictionary):
    qmarks = ', '.join('?' * len(dictionary))
    cols = ', '.join(dictionary.keys())
    values = tuple(dictionary.values())
    query = "INSERT INTO %s (%s) VALUES (%s)" % (tablename, cols, qmarks)
    cursor.execute(query, values)
    return

if __name__ == "__main__":
    main()

I had already a look at

Python : How to insert a dictionary to a sqlite database?

but unfortunately I did not succeed.

score 2 · Answer 1 · answered Aug 14 '18 at 08:34

2

You must not use a dictionary with question marks as parameter markers, because there is no guarantee about the order of the values.

To handle multiple rows, you must use executemany().

And executemany() expects each item to contain the values for one row, so you have to rearrange the data:

>>> print(*zip(data['sID'], data['colA'], data['colB'], data['colC']), sep='\n')
(1, 0.3, 'A', 4)
(2, 0.4, 'B', 5)
(3, 0.5, 'C', 6)

cursor.executemany(query, zip(data['sID'], data['colA'], data['colB'], data['colC']))

answered Aug 14 '18 at 08:34

CL.

173,858
17
217
259

Thanks for this hint. How to proceed when I don't know the names/ammount of dict fields? Can I overcome this "manual" reshaping of data in order to write them using executemany into the database? – David K. Aug 14 '18 at 09:11
Is it really necessary for the dictionary to have this form (or to be a dictionary at all)? – CL. Aug 14 '18 at 09:14
Maybe I've to think about the way my data are loaded from the external .csv. Each column contains the 'timehistory' data of several parameter. I loaded each column into one field of the dictionary. thats the reason why the structure is like it is. Do you have any suggestions to restrucutre it during loading? – David K. Aug 14 '18 at 09:17
Read the column names into an array. Then make an array of rows, with each row being an array of values. This matches both the original .CSV structure and the table structure. – CL. Aug 14 '18 at 09:20

Python dictionary to sqlite database

1 Answers1