How to Store Python Dictionary in to MySQL Db Through Python

How to store python dictionary in to mysql DB through python

First of all, don't ever construct raw SQL queries like that. Never ever. This is what parametrized queries are for. You've asking for an SQL injection attack.

If you want to store arbitrary data, as for example Python dictionaries, you should serialize that data. JSON would be good choice for the format.

Overall your code should look like this:

import MySQLdb
import json

db = MySQLdb.connect(...)    
cursor = db.cursor() 

dic = {'office': {'component_office': ['Word2010SP0', 'PowerPoint2010SP0']}}
sql = "INSERT INTO ep_soft(ip_address, soft_data) VALUES (%s, %s)"

cursor.execute(sql, ("192.xxx.xx.xx", json.dumps(dic)))
cursor.commit()

How do I insert data from a Python dictionary to MySQL?

Here is some basic code to create a MySQL database, and insert some data.

import MySQLdb
import datetime

THEHOST="localhost"
THEUSER="user"
THEPASSWD="passwd"
THEDB="database"

connection=MySQLdb.connect(
    host=THEHOST,user=THEUSER,passwd=THEPASSWD,db=THEDB)
cursor=connection.cursor()

abc,efg,ijk=1,2,3

data={'1': ['1', 'K', abc, 'xyz', None, None, None, datetime.date(2009, 6, 18)],
      '2': ['2', 'K', efg, 'xyz', None, None, None, None],
      '3': ['3', 'K', ijk, 'xyz', None, None, None,
            datetime.datetime(2010, 2, 5, 16, 31, 2)]}

sql='''\
CREATE TABLE IF NOT EXISTS temp (id int auto_increment primary key,
    field1 varchar(8),
    field2 int,
    field3 varchar(8),
    field4 bool,
    field5 varchar(8),
    field6 varchar(8),
    field7 datetime )'''

cursor.execute(sql)

sql='''\
INSERT INTO temp (id, field1, field2, field3, field4, field5, field6, field7)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
'''
cursor.executemany(sql, data.values())

How do I store a list of Python dictionaries in MySQL the Pythonic way?

Just flat out the dictionaries and insert them:

def encoding(val):
    if isinstance(val, unicode):
        return val.encode('utf-8')
    else:
        return str(val)


for id, val in mydict.items():
    data = dict(reduce(lambda x, y: x+y, [v.items() for v in val]) + [('id', id)])
    sorted_keys = sorted(map(str, data.keys()))
    sorted_vals = map(encoding, [v[k] for k in sorted_keys])  # sorted by keys
    format = ', '.join(["'%s'"] * len(sorted_vals))
    c.execute("insert into deldictmysql
               (%s) values (%s)" % (', '.join(sorted_keys), format), sorted_vals)

UPD: for any number and values of keys

Writing Python Dictionary to MySQL with multiple values per key

Rather than creating another dict, create a list of tuples containing the values:

values = []
for key, value in sorted(servers.items()):
    values.append((key, fc_grab(value), fs_grab(value), curtime))

Now you can create your SQL

placeholders = ', '.join(['%s'] * len(values[0]))
columns = ', '.join(['server_name', 'file_count', 'file_size', 'curtime'])
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, columns, placeholders)
cursor.executemany(sql, values)

cursor.executemany inserts all the rows in a single python method call, rather than doing cursor.execute in a loop.

---

Related Topics