CODEWITHSUNDEEP
python-3.xdictionarysqlite
David K.
David K.

Reputation: 123

Python dictionary to sqlite database

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.

Upvotes: 0

Views: 2080

Answers (1)

CL.
CL.

Reputation: 180010

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']))

Upvotes: 2

Related Questions