Python实现简单的多任务mysql转xml的方法

# -*- coding: utf-8 -*-

'''

Created on 2014/12/27

@author: Yoki

'''

importgevent

importpymysql

frompymysql.cursorsimportDictCursor

importre

importcodecs

db_conn=None

definit_mysql_connect(*args,**kwargs):

  globaldb_conn

  db_conn=pymysql.connect(*args,**kwargs)

deflist_to_xml(result_cur, key_list):

  '''

  mysql 结果集转xml，非xml标准导出方式; xml dom 不支持相同名字的node

  :param result_cur:

  :param key_list:

  :return:

  '''

  content=''

  content+='\r\n'

  content+='\r\n'# root节点

  foriteminresult_cur:

    content+='\t\r\n'

    forkinkey_list:

      v=item.get(k, '')

      real_value=v

      content+='\t\t<%s>%s\r\n'%(k, real_value, k)

    content+='\t\r\n'

  content+='\r\n'

  returncontent

defget_table_rows(tb_name):

  '''

  获取mysql表rows

  :param tb_name:

  :return:

  '''

  globaldb_conn

  rows=[]

  cursor=db_conn.cursor(cursor=DictCursor)

  cursor.execute('select * from %s'%tb_name)

  forrowincursor:

    rows.append(row)

  returnrows

defget_table_keys(tb_name):

  '''

  获取表中字段，顺序 为创建表时的顺序

  :param tb_name:

  :return:

  '''

  globaldb_conn

  cursor=db_conn.cursor(cursor=DictCursor)

  cur=cursor.execute('show create table %s'%tb_name)

  ifcur !=1:

    raiseException

  forrincursor:

    create_sql=r['Create Table']

    fields=re.findall('`(.*?)`', create_sql)

    result=[]

    # 处理字段

    foriinxrange(1,len(fields)):

      field=fields[i]

      iffieldinresult:

        continue

      result.append(field)

    returnresult

  return[]

defmysql_to_xml(tb_name, output_dir='xml', postfix='xml'):

  '''

  mysql数据导出xml，

  :param tb_name: 数据库表名

  :param output_dir:

  :param postfix:

  :return:

  '''

  rows=get_table_rows(tb_name)

  keys=get_table_keys(tb_name)

  content=list_to_xml(rows, keys)

  fp=codecs.open('%s/%s.%s'%(output_dir, tb_name, postfix),'w','utf-8')

  fp.write(content)

  fp.close()

tb_list=[

  'tb_item',

  'tb_state'

]

if__name__=='__main__':

  init_mysql_connect(host="localhost", user='user', password="password", database='test', port=3306,

            charset='utf8')

  jobs=[]

  fortb_nameintb_list:

    jobs.append(gevent.spawn(mysql_to_xml, tb_name))

  gevent.joinall(jobs)

deflist_to_xml(result_cur, key_list):

  fp=codecs.open('test.xml'),'w','utf-8')

  fp.write('\r\n')

  fp.write('\r\n')

  foriteminresult_cur:

    fp.write('\t\r\n')

    forkinkey_list:

      v=item.get(k, '')

      ifvisNone:

        real_value=''

      else:

        iftype(v)==unicode:

          real_value=cgi.escape(v)

        else:

          real_value=v

      fp.write('\t\t<%s>%s\r\n'%(k, real_value, k))

    fp.write('\t\r\n')

  fp.write('\r\n')

  fp.close()