Insert list of dictionaries into SQL table using python

Question

I am taking the first steps using python and sql databases and am still not sure which package to use and how. I have a list with approximately 300k dictionaries, each of which contains approximately 20 keys. These dicts must be inserted into the SQL table.

In my opinion, the advantage of the dictation approach list is that I explicitly name the columns in which I want to enter specific values. (Perhaps this is not a good approach)

Let me introduce a more concrete example, covering the main issues. The table consists of three columns: identifier (integer), price (decimal), type (string). Type supports null values.

My dict keys have the same name, and the dicts list might look like this:

lst = [{'ID':1, 'Price': '9.95', 'Type': None}, {'ID':2, 'Price': '7.95', 'Type': 'Sports'}, {'ID':3, 'Price': '4.95', 'Type': 'Tools'}, ...] 

So, the following questions arise:

• Is the approach using dicts correct? (Note that I have 20 columns)
• If yes / or no: how should such a request be effectively executed?
• Do I need to convert prices to Decimal before the SQL statement, or can this be achieved on the fly
• Is None automatically converted to null or is additional work being done?

Answer 1

Assuming you are using the Python database API specification compatible with the database driver.

Type conversions (questions 3 and 4) should be handled by the database driver out of the box.

As for 2), then executemany() :

 cursor.executemany(""" INSERT INTO mytable (id, price, type) VALUES (%(id)s, %(price)s, %(type)s) """, lst)