Python implements simple multi-tasking mysql to xml method

This article mainly introduces the method of Python to implement simple multi-tasking mysql to xml, and analyzes the related operation skills of Python query mysql result set to xml format data output in the form of examples. Friends who need it can refer to it

The example of this article describes the method of converting simple multi-tasking mysql to xml in Python. Share it with everyone for your reference, the details are as follows:

In order to meet the requirements, the exported format should be consistent with the xml exported by navicat.

When using gevent, file I/O operations will be blocked, so they will not be completely asynchronous.

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 += &#39;<?xml version="1.0" encoding="UTF-8" ?>\r\n&#39;
  content += &#39;<RECORDS>\r\n&#39; # root节点
  for item in result_cur:
    content += &#39;\t<RECORD>\r\n&#39;
    for k in key_list:
      v = item.get(k, &#39;&#39;)
      real_value = v
      content += &#39;\t\t<%s>%s</%s>\r\n&#39; % (k, real_value, k)
    content += &#39;\t</RECORD>\r\n&#39;
  content += &#39;</RECORDS>\r\n&#39;
  return content
def get_table_rows(tb_name):
  &#39;&#39;&#39;
  获取mysql表rows
  :param tb_name:
  :return:
  &#39;&#39;&#39;
  global db_conn
  rows = []
  cursor = db_conn.cursor(cursor=DictCursor)
  cursor.execute(&#39;select * from %s&#39; % tb_name)
  for row in cursor:
    rows.append(row)
  return rows
def get_table_keys(tb_name):
  &#39;&#39;&#39;
  获取表中字段，顺序 为创建表时的顺序
  :param tb_name:
  :return:
  &#39;&#39;&#39;
  global db_conn
  cursor = db_conn.cursor(cursor=DictCursor)
  cur = cursor.execute(&#39;show create table %s&#39; % tb_name)
  if cur != 1:
    raise Exception
  for r in cursor:
    create_sql = r[&#39;Create Table&#39;]
    fields = re.findall(&#39;`(.*?)`&#39;, 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=&#39;xml&#39;, postfix=&#39;xml&#39;):
  &#39;&#39;&#39;
  mysql数据导出xml，
  :param tb_name: 数据库表名
  :param output_dir:
  :param postfix:
  :return:
  &#39;&#39;&#39;
  rows = get_table_rows(tb_name)
  keys = get_table_keys(tb_name)
  content = list_to_xml(rows, keys)
  fp = codecs.open(&#39;%s/%s.%s&#39; % (output_dir, tb_name, postfix), &#39;w&#39;, &#39;utf-8&#39;)
  fp.write(content)
  fp.close()
tb_list = [
  &#39;tb_item&#39;,
  &#39;tb_state&#39;
]
if __name__ == &#39;__main__&#39;:
  init_mysql_connect(host="localhost", user=&#39;user&#39;, password="password", database=&#39;test&#39;, port=3306,
            charset=&#39;utf8&#39;)
  jobs = []
  for tb_name in tb_list:
    jobs.append(gevent.spawn(mysql_to_xml, tb_name))
  gevent.joinall(jobs)

2. Modify the list_to_xml function, increasing the speed by hundreds of times

def list_to_xml(result_cur, key_list):
  fp = codecs.open('test.xml'), 'w', 'utf-8')
  fp.write(&#39;<?xml version="1.0" encoding="UTF-8" ?>\r\n&#39;)
  fp.write(&#39;<RECORDS>\r\n&#39;)
  for item in result_cur:
    fp.write(&#39;\t<RECORD>\r\n&#39;)
    for k in key_list:
      v = item.get(k, &#39;&#39;)
      if v is None:
        real_value = &#39;&#39;
      else:
        if type(v) == unicode:
          real_value = cgi.escape(v)
        else:
          real_value = v
      fp.write(&#39;\t\t<%s>%s</%s>\r\n&#39; % (k, real_value, k))
    fp.write(&#39;\t</RECORD>\r\n&#39;)
  fp.write(&#39;</RECORDS>\r\n&#39;)
  fp.close()

For more Python methods to implement simple multi-tasking mysql to xml related articles, please pay attention to the PHP Chinese website!