Python 3 convert dictionary to SQL insert

In this article you can see how to convert any python dictionary easily into SQL insert statements. This is useful when you need to provide SQL inserts to DBA guy who needs to apply them to DB. In this example we are testing the resulted query in MySQL 5.7 and output the queries to a external file. We will see two examples:

• first one will create SQL like syntax into text file which can be used for DB import
• second will do imports from the MySQL to DB directly(if you have corect access)

You can find video tutorial here: Easy way to convert dictionary to SQL insert with Python

Python create SQL insert statements from dict

Let us do a single dict to a single insert statement. Below you can see the first example and the result:

mydict = {'user': 'Bot', 'version': 0.15, 'items': 43, 'methods': 'standard', 'time': 1536304833437, 'logs': 'no', 'status': 'completed'}

columns = ', '.join("`" + str(x).replace('/', '_') + "`" for x in mydict.keys())
values = ', '.join("'" + str(x).replace('/', '_') + "'" for x in mydict.values())
sql = "INSERT INTO %s ( %s ) VALUES ( %s );" % ('mytable', columns, values)
print(sql)

result:

INSERT INTO mytable ( user, version, items, methods, time, logs, status ) VALUES ( 'Bot', '0.15', '43', 'standard', '1536304833437', 'no', 'completed' );

Some words about this example:

• we have a single dictionary and read items one by one
• columns - we generate from all keys column names
  • remove extra characters which can cause problems related to MySQL naming like - ''
  • append special symbols to column name to deal with complex names
• for values we iterate over the dict items values
  • remove extra characters which can cause problems related to MySQL naming like - ''
  • append special characters to values like '
• the final line build the insert statement where we provide:
  • table name (optional database)
  • column names
  • insert values
• the final result is printed to the console

The next example is pretty similar to the first one with a few differences:

• we will work with list of dictionaries - the typical situation where you need to automate the conversion
• output the results to a file

mylist = [
            {'user': 'Bot1', 'version': 0.11, 'items': 23, 'methods': 'standard', 'time': 1536304833437, 'logs': 'no', 'status': 'completed'},
            {'user': 'Bot2', 'version': 0.15, 'items': 43, 'methods': 'standard', 'time': 2555645896453, 'logs': 'yes', 'status': 'cancelled'},
            {'user': 'Bot3', 'version': 0.17, 'items': 63, 'methods': 'standard', 'time': 3265114687998, 'logs': 'yes', 'status': 'completed'}
          ]

for mydict in mylist:
    placeholders = ', '.join(['%s'] * len(mydict))
    columns = ', '.join("`" + str(x).replace('/', '_') + "`" for x in mydict.keys())
    values = ', '.join("'" + str(x).replace('/', '_') + "'" for x in mydict.values())
    sql = "INSERT INTO %s ( %s ) VALUES ( %s );" % ('mytable', columns, values)
    # print(sql)


    f = open("/home/user/files/bots.sql", "a")
    f.write(sql + '\n')

result:

INSERT INTO mytable ( user, version, items, methods, time, logs, status ) VALUES ( 'Bot1', '0.11', '23', 'standard', '1536304833437', 'no', 'completed' );
INSERT INTO mytable ( user, version, items, methods, time, logs, status ) VALUES ( 'Bot2', '0.15', '43', 'standard', '2555645896453', 'yes', 'cancelled' );
INSERT INTO mytable ( user, version, items, methods, time, logs, status ) VALUES ( 'Bot3', '0.17', '63', 'standard', '3265114687998', 'yes', 'completed' );

Python create SQL insert for direct import

In this section we will see how to import from python to MySQL directly. We will build insert statements and provide them to a cursor which is sending them to MySQL.

mydict =  {'user': 'Bot1', 'version': 0.11, 'items': 23, 'methods': 'standard', 'time': 1536304833437, 'logs': 'no', 'status': 'completed'}

placeholders = ', '.join(['%s'] * len(mydict))
columns = ', '.join(mydict.keys())
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % ('mytable', columns, placeholders)
cursor.execute(sql, mydict.values())

Note: you need to import cursor and setup the MySQL connection depending on your needs.

The result of this query can be seen in the DB. This is a sample output if you print it:

INSERT INTO mytable ( user, version, items, methods, time, logs, status ) VALUES ( %s, %s, %s, %s, %s, %s, %s ) dict_values(['Bot1', 0.11, 23, 'standard', 1536304833437, 'no', 'completed'])

Latest statement will be parsed by the cursor:

cursor.execute(sql, mydict.values())