excel_to_mysql python script

excel_to_mysql

#!/usr/bin/env python
# -*- coding:utf-8 -*-
#
#   Author  :   Rhapsodyzs
#   E-mail  :   zs1213yh@gmail.com
#   Date    :   14/08/09 00:22:28
#   Desc    :   excel to mysql operation
#

import sys 
import hashlib
reload(sys) 
sys.setdefaultencoding('utf8')
import MySQLdb, xlrd
import time, datetime
f = "%Y-%m-%d %H:%M:%S"


conn = MySQLdb.connect(host='localhost', user='**', passwd='***', db='***', port=3306, charset="utf8")
cur = conn.cursor()

def openExcelFile():
    bk = xlrd.open_workbook('test.xls')  #打开文件
    sh = bk.sheets()[0]  #得到文件的sheet 1, 如果sheet 2就 bk.sheets()[1] 依次类推
    nrows = sh.nrows #得到 sheet 1的列数
    for i in range(1, nrows):
        id = int(sh.row(i)[0].value) #这里i是行, 中括号里的是 列数
        nick_name = sh.row(i)[1].value
        sex = sh.row(i)[2].value
        age = sh.row(i)[3].value
        phone = sh.row(i)[4].value
        address = sh.row(i)[5].value
        province = address[0:3] 
        city = address[3:6]
        addre = address[6:]
        email = sh.row(i)[6].value
        description = sh.row(i)[7].value
        timenow = datetime.datetime.now().strftime(f)
        
        print (id, nick_name, sex, age, phone, address, email, description, timenow)
        try:
            sql_user = "insert into `dd_user`(id, nick_name, gmt_create, gmt_modified) values(%s, %s, %s, %s)"
            param_user = (id, nick_name, timenow, timenow)
            sql_user_info = "insert into `dd_user_info`(true_name, user_id) values(%s, %s)"
            param_user_info = (nick_name, id+100)
            sql_enroll = "insert into `enroll`(enroll_id, name, sex, age, phone, address, city, province, email, description, user_id) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            param_enroll = (id, nick_name, sex, age, phone, addre, city, province, email, description, id)
            n_user = cur.execute(sql_user, param_user) 
            n_user_info = cur.execute(sql_user_info, param_user_info)
            n_enroll = cur.execute(sql_enroll, param_enroll)
            print n_user
            print n_user_info
            print n_enroll
            conn.commit()
        except MySQLdb.Error, e:
           print "Mysql Error %d: %s" %(e.args[0], e.args[1]);
    cur.close()
    conn.close()




openExcelFile()