Python script update mysql database

Reading time ~1 minute

I use this script to update Racktables databases value

import MySQLdb
import xlrd
import time
import sys
reload(sys)
sys.setdefaultencoding("utf-8")

def get_table():
    FILE_NAME = 'owner.xls'
    data = xlrd.open_workbook(FILE_NAME)
    table = data.sheets()[0]
    return table

def insert_by_many(table):
    nrows = table.nrows
    param=[]
    for i in xrange(1,nrows):
        #param.append([table.cell(i, 0).value, table.cell(i, 1).value])
        #change to 3 values for the new sql script
        param.append([table.cell(i, 0).value, table.cell(i, 1).value])
        print param
    try:
        #sql = 'UPDATE AttributeValue JOIN Object ON AttributeValue.object_id = Object.id SET AttributeValue.string_value = %s WHERE AttributeValue.attr_id=14 and Object.name = %s'
        #update sql script, if not exist insert , if exist update, like "UPSERT", so param list should have three value
        sql = "insert into AttributeValue (object_id, object_tid, attr_id, string_value) select id, objtype_id, '14', %s from Object where name=%s on duplicate key update AttributeValue.string_value=%s"
        cur.executemany(sql, param)
        conn.commit()
    except Exception as e:
        print e
        conn.rollback()
    print '[insert_by_many executemany] total:',nrows-1


conn = MySQLdb.connect(host="127.0.0.1", port=3306, user="root", passwd="password", db="racktables")
cur = conn.cursor()

table = get_table()


start = time.clock()
insert_by_many(table)
end = time.clock()
print '[insert_by_many executemany] Time Usage:',end-start

if cur:
    cur.close()
if conn:
    conn.close()

Kubernetes Tutorials (1)

Published on June 16, 2017

Kubernetes How It Works

Published on January 22, 2017