import csv, MySQLdb, sys

处理数据库的函数

def mysql_database(sql_str, values):
conn = MySQLdb.connect(host='192.168.2.20', user='root',passwd='123456')
cursor = conn.cursor()
conn.select_db('eldorajob')

cursor.executemany(sql_str, values)
conn.commit()

cursor.close()
conn.close()

def hardSkillDomain(reader):
values=[]
for obj1 in reader:
values.append((obj1['HardSkillDomainID'], obj1['DomainName']))
return values

def domain(reader):
values=[]
for obj1 in reader:
values.append((obj1['DomainID'], mdcode(obj1['DomainName'])))
return values

处理转码的函数

def mdcode(str1):
for c in ('utf-8', 'gbk', 'gb2312'):
try:
return str1.decode(c).encode('utf-8')
except:
pass
return 'unknown'

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

if name == "main":
FILE_PATH = 'd:/csv/Domain_Table_Data.csv'

INSERT_SQL = {'hardskilldomain' : 'insert into modules_hardskilldomain values(%s, %s)',
              'domain'          : 'insert into modules_domain values(%s, %s)'}

INSERT_VALUES = {'hardskilldomain' : hardSkillDomain,
                 'domain'          : domain}

try:
    # get connection
    fd = open(FILE_PATH)
    dialect = csv.Sniffer().sniff(fd.readline())
    fd.seek(0)
    reader = csv.DictReader(fd, dialect = dialect)

    # insert into mysql database
    mysql_database(INSERT_SQL['domain'], INSERT_VALUES['domain'](reader))
except:
    print sys.exc_info()[0],sys.exc_info()[1]
finally:
    fd.close()

要处理的csv文件内容(第一行对应数据表中的字段名):

DomainID,DomainName
1,Agroalimentaire
2,Automobile
3,Banque/Finance
4,Biotechnologie
5,Chimie
6,Pharmaceutique
7,Enseignement
8,Edition/Publication
9,Informatique
10,Ingénierie
11,Métaux & Minéraux
12,Gestion des déchets
13,Services aux entreprises
14,Services aux particuliers
15,Institutionnel