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


            本文实例讲述了Python实现简单的多任务mysql转xml的方法。分享给大家供大家参考，具体如下：

为了需求导出的格式尽量和navicat导出的xml一致。

用的gevent，文件i/o操作会阻塞，所以并不会完全异步。

1. mysql2xml.py：





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

'''

Created on 2014/12/27

@author: Yoki

'''

import gevent

import pymysql

from pymysql.cursors import DictCursor

import re

import codecs

db_conn = None

def init_mysql_connect(*args, **kwargs):

  global db_conn

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

def list_to_xml(result_cur, key_list):

  '''

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

  :param result_cur:

  :param key_list:

  :return:

  '''

  content = ''

  content += '<?xml version="1.0" encoding="UTF-8" ?>\r\n'

  content += '<RECORDS>\r\n' # root节点

  for item in result_cur:

    content += '\t<RECORD>\r\n'

    for k in key_list:

      v = item.get(k, '')

      real_value = v

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

    content += '\t</RECORD>\r\n'

  content += '</RECORDS>\r\n'

  return content

def get_table_rows(tb_name):

  '''

  获取mysql表rows

  :param tb_name:

  :return:

  '''

  global db_conn

  rows = []

  cursor = db_conn.cursor(cursor=DictCursor)

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

  for row in cursor:

    rows.append(row)

  return rows

def get_table_keys(tb_name):

  '''

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

  :param tb_name:

  :return:

  '''

  global db_conn

  cursor = db_conn.cursor(cursor=DictCursor)

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

  if cur != 1:

    raise Exception

  for r in cursor:

    create_sql = r['Create Table']

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

    result = []

    # 处理字段

    for i in xrange(1, len(fields)):

      field = fields[i]

      if field in result:

        continue

      result.append(field)

    return result

  return []

def mysql_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 = []

  for tb_name in tb_list:

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

  gevent.joinall(jobs)







2. list_to_xml函数修改，速度提升上百倍





def list_to_xml(result_cur, key_list):

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

  fp.write('<?xml version="1.0" encoding="UTF-8" ?>\r\n')

  fp.write('<RECORDS>\r\n')

  for item in result_cur:

    fp.write('\t<RECORD>\r\n')

    for k in key_list:

      v = item.get(k, '')

      if v is None:

        real_value = ''

      else:

        if type(v) == unicode:

          real_value = cgi.escape(v)

        else:

          real_value = v

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

    fp.write('\t</RECORD>\r\n')

  fp.write('</RECORDS>\r\n')

  fp.close()







更多关于Python相关内容感兴趣的读者可查看本站专题：《Python+MySQL数据库程序设计入门教程》、《Python常见数据库操作技巧汇总》、《Python数据结构与算法教程》、《Python Socket编程技巧总结》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》

希望本文所述对大家Python程序设计有所帮助。