MySQL Dynamic Query Statement in Python with Dictionary

Question

Very similar to this question MySQL Dynamic Query Statement in Python

However what I am looking to do instead of two lists is to use a dictionary

Let's say i have this dictionary

instance_insert = {
#    sql column      variable value
    'instance_id'   : 'instnace.id',
    'customer_id'   : 'customer.id',
    'os'            : 'instance.platform',
}

And I want to populate a mysql database with an insert statement using sql column as the sql column name and the variable name as the variable that will hold the value that is to be inserted into the mysql table.

Kind of lost because I don't understand exactly what this statement does, but was pulled from the question that I posted where he was using two lists to do what he wanted.

sql = "INSERT INTO instance_info_test VALUES (%s);" % ', '.join('?' for _ in instance_insert)
cur.execute (sql, instance_insert)

Also I would like it to be dynamic in the sense that I can add/remove columns to the dictionary

pswaminathan · Accepted Answer · 2013-04-27 17:40:22Z

0

Before you post, you might want to try searching for something more specific to your question. For instance, when I Googled "python mysqldb insert dictionary", I found a good answer on the first page, at http://mail.python.org/pipermail/tutor/2010-December/080701.html. Relevant part:

Here's what I came up with when I tried to make a generalized version of the above:

def add_row(cursor, tablename, rowdict):
    # XXX tablename not sanitized
    # XXX test for allowed keys is case-sensitive

    # filter out keys that are not column names
    cursor.execute("describe %s" % tablename)
    allowed_keys = set(row[0] for row in cursor.fetchall())
    keys = allowed_keys.intersection(rowdict)

    if len(rowdict) > len(keys):
        unknown_keys = set(rowdict) - allowed_keys
        print >> sys.stderr, "skipping keys:", ", ".join(unknown_keys)

    columns = ", ".join(keys)
    values_template = ", ".join(["%s"] * len(keys))

    sql = "insert into %s (%s) values (%s)" % (
        tablename, columns, values_template)
    values = tuple(rowdict[key] for key in keys)
    cursor.execute(sql, values)

filename = ...
tablename = ...
db = MySQLdb.connect(...)
cursor = db.cursor()
with open(filename) as instream:
    row = json.load(instream)
add_row(cursor, tablename, row)

Peter

If you know your inputs will always be valid (table name is valid, columns are present in the table), and you're not importing from a JSON file as the example is, you can simplify this function. But it'll accomplish what you want to accomplish. While it may initially seem like DictCursor would be helpful, it looks like DictCursor is useful for returning a dictionary of values, but it can't execute from a dict.

answered Apr 27, 2013 at 17:40

pswaminathan

9,1441 gold badge24 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lawless Over a year ago

Thanks pswaminathan, I will try to work with that and return what I come up with for code. Sorry about not doing enough extensive research, I did try searching but am so new to python it is hard for me to wade through it all and determine what exactly I am looking for.

pswaminathan Over a year ago

No problem. Just wanted to point out what you could've searched. Not a big deal. Good luck with Python!

Collectives™ on Stack Overflow

MySQL Dynamic Query Statement in Python with Dictionary

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related