Insert a list of dictionaries into an SQL table using python

Question

I'm making my first steps using python and sql databases and still am not sure which package to use and how. I have a list with approx 300k dictionaries each with roughly about 20 keys. These dicts shall be inserted into an SQL table.

In my opinion the advantahe of the list of dict approach is, that I explicitly name the columns in which I want to enter specific values. (It might be, that this is not a good approach)

Let me present a more specific example catching the essentials of my problem. The table consists of three columns: ID (Integer), Price (Decimal), Type (string). Type supports null values.

The keys of my dict have the same name and the list of dicts 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 questions that arise are the following:

1. Is the approach using dicts the right? (Note that I have 20 columns)
2. If yes/or no: How should one perform such a query efficiently?
3. Is it necessary to convert the prices to Decimal and before the SQL statement, or can this be achieved 'on-the-fly'
4. Is the None value automatically converted to null, or is there extra work to be done?

Answer 1

Assuming you are using a Python Database API specification compliant database driver.

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

As for the 2), there is executemany():

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

Answer 2

mydb = MySQLdb.connect(host='',    # your host_name
                       user='',    # your username
                       passwd='',  # your password
                       db=''       # your database
                       )
cur= mydb.cursor()
insert_query = "INSERT INTO table_name(feild_1,feild2,feild3) VALUES ( %(id)s, %(price)s, %(type)s);"
cur.executemany(insert_query, lst)
mydb.commit