Python - Inserting dictionary into SQLite3

Question

I got a dictionary with 14 keys.

First Ive created createTableOfRecordsenter function:

    def createTableOfRecords(self):
        create_table = '''CREATE TABLE IF NOT EXISTS records(Budget TEXT , Commitment TEXT, Contract_Type TEXT , Customer_Type TEXT, Duration TEXT , Goals TEXT, Pace TEXT , 
                        Procedures_and_Regulations TEXT, Resources TEXT , Scope TEXT, Team_Availability TEXT , Team_Distribution TEXT, Team_Size TEXT , Uncertainty TEXT);'''
        self.cursor.execute(create_table)
        self.connection.commit()

and the table with columns created successfully. After that, I tried to insert the data using the insertRecords function:

    global var_dict
    var_dict = dict(Budget="Fixed",
                    Commitment="Low",
                    Contract_Type="Hybrid",
                    Customer_Type="Market",
                    Duration="Long",
                    Goals="Unclear",
                    Pace="Fast",
                    Procedures_and_Regulations="None",
                    Resources="Standart",
                    Scope="Rigid",
                    Team_Availability="Fully",
                    Team_Distribution="Global",
                    Team_Size="Small",
                    Uncertainty="Predictable")

    def insertRecords(self):
        self.cursor.execute('INSERT INTO records (Budget,Commitment,Contract_Type,Customer_Type,Duration,Goals,Pace,'
                                                 'Procedures_and_Regulations,Resources,Scope,Team_Availability,'
                                                 'Team_Distribution,Team_Size,Uncertainty) '
                            'VALUES (:Budget, :Commitment, :Contract_Type, :Customer_Type, :Duration, '
                                    ':Goals, :Pace, :Procedures_and_Regulations, :Resources, :Scope, :Team_Availability, '
                                    ':Team_Distribution, :Team_Size, :Uncertainty);'), var_dict
        self.connection.commit()

but I didn't get any value inserted into the database table. I got this error message:

self.cursor.execute('INSERT INTO records (Budget,Commitment,Contract_Type,Customer_Type,Duration,Goals,Pace,' sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 14, and there are 0 supplied.

Does anyone know what I've done wrong? Thanks!

Does this answer your question? [Python : How to insert a dictionary to a sqlite database?](https://stackoverflow.com/questions/10913080/python-how-to-insert-a-dictionary-to-a-sqlite-database) — tbhaxor, May 27 '21 at 22:30
Does this answer your question? [Python and SQLite: insert into table](https://stackoverflow.com/questions/2092757/python-and-sqlite-insert-into-table) — sahasrara62, May 27 '21 at 22:30
You're not passing your dict as an argument to `execute`... Gotta pay attention to your parenthesis. — Shawn, May 27 '21 at 22:32
Both answers above are good and I've seen them but still not manage to execute the code. @Shawn What do you mean? — User1, May 27 '21 at 22:34
I've pasted the Error message, tried to google it but didn't found anything helpful. — User1, May 27 '21 at 22:35
`var_dict` should be second parameter for `self.cursor.execute` ie `self.cursor.execute(command, var_dict)` — sahasrara62, May 27 '21 at 22:45

Schwern · Accepted Answer · 2021-05-28T00:00:20.607

You've written:

self.cursor.execute( 'insert ...' ), var_dict

var_dict is not being passed as an argument to execute. It is outside the parenthesis. Instead you are making a two element tuple of the result of execute and var_dict and then throwing it out.

You want to pass var_dict into execute like so.

self.cursor.execute( 'insert ...', var_dict )

Here's a quick illustration of the difference.

>>> var_dict = dict(foo="bar")
>>> def test(sql, *optional):
...     print(f"Got sql '{sql}' and args {optional}\n")
... 
>>> test('insert...'), var_dict
Got sql 'insert...' and args ()

(None, {'foo': 'bar'})
>>> test('insert...', var_dict)
Got sql 'insert...' and args ({'foo': 'bar'},)

Python - Inserting dictionary into SQLite3

1 Answers1