Python, mysql og mysqldump

#! /usr/bin/python
import cgi,sys
import subprocess
from lxml import etree

"""
Run a subprocess, used to perform:
extract from database with mysqldump   or    mysql
return ok or error....
"""
def doProcess(params,targetfile):
    try:
        f=open(targetfile,'wb')
        p=subprocess.check_call(params,stdout=f,stderr=f,shell=False)
        f.flush()
        f.close()
        if p==0:            
            return 'ok'
        else:
            return 'error in subprocess: '+str(p)
    except:
        res=sys.exc_info()
        return 'error '+ str(res[1])
 
"""
 extract from database and
 make an xslt transform to legal JSON
 return the JSON string or error....
"""
def makeJson(params,xmlfile,xslfile):
    res=doProcess(params,xmlfile)
    if res.startswith('error'):
        return res
    try:
        tree=etree.parse(xmlfile)
        xsltree=etree.parse(xslfile)
        transform=etree.XSLT(xsltree)    
        resulttree=transform(tree)
        return str(resulttree)
    except:
        res=sys.exc_info()
        return 'error '+ str(res[1])
 
"""
 extract from database,
 make an xslt transform to legal JSON
 and save the string as a compact Javascript string variable
  var obj='....'
"""
def saveJSVariable(params,xslfile,targetfile):
    S=makeJson(params,"data/tmp",xslfile)
    if S.startswith('error'):
        S='{"message":"S"}'
    else:
        S=S.replace('\r','').replace('\n','')
    T="var obj='"+S+"'"
    res=storeTextFile(targetfile,T)
    return res[0]
 
"""
 Set up parameters for a use of mysqldump
 user|password|host|database|table
"""
def prepareDumpParameters(connect):
    p=[]
    parts=connect.split('|')
    if len(parts) == 5:                           
        p.append('mysqldump')
        p.append('--xml')
        p.append('--lock-tables=false')#when limited user rights        
        p.append('--user='+parts[0])
        p.append('--password='+parts[1])
        p.append('--default-character-set=utf8')
        p.append('--host='+parts[2])
        p.append(parts[3]) #database
        p.append(parts[4]) #table
    return p
"""
 Set up parameters for a use of mysql
 user|password|host|database
"""
def prepareSelectParameters(connect,sql):
    p=[]
    parts=connect.split('|')
    if len(parts) == 4:                           
        p.append('mysql')
        p.append('-X')
        p.append('--execute='+sql)
        p.append('--user='+parts[0])
        p.append('--password='+parts[1])
        p.append('--default-character-set=utf8')
        p.append('--host='+parts[2])
        p.append(parts[3]) #database
    return p
 
"""
Read / write text files
"""
def getTextFile(filename):
    try:
        file=open(filename,'r')
        res=file.read()
        file.close()
        return ('ok',res)
    except:
        res=sys.exc_info()
        return('error',res[1])
    
    
def storeTextFile(filename,txt):
    try:
        file=open(filename,'w')
        file.write(txt)
        file.close()
        return('ok','')
    except:
        res=sys.exc_info()
        return('error',res[1])

#! /usr/bin/python
import cgi,sys
import dbutils
#import cgitb; cgitb.enable()
"""
Dump to xml
using mysqldump in a subprocess as implemented in utils
"""

form=cgi.FieldStorage()
"""
 Need connect. | separated string
 Sequence is important:
 user|password|host|database|table
"""
try:
    # always connect
    connect=form['connect'].value
    # set up parameters
    p=dbutils.prepareDumpParameters(connect)
    if len(p) >0:
        res=dbutils.doProcess(p,'data/dump.xml')
        if res.startswith('ok'):
            # return the produced file
            print "Location: data/dump.xml\n"
        else:   
            print 'Content-type: text/plain; charset=utf-8 \n'
            print res
    else:
        print 'Content-type: text/plain; charset=utf-8 \n'
        print 'error bad parameter list in connect'
except:
        print 'Content-type: text/plain; charset=utf-8 \n'
        res=sys.exc_info()
        print 'error '+str(res[1])

#! /usr/bin/python
import cgi,sys
from lxml import etree
import dbutils
#import cgitb; cgitb.enable()
"""
Dump to json
using mysqldump in a subprocess as implemented in dbutils
"""
#transformation xml -> json
XSLT_FILE='tojson.xsl'       
form=cgi.FieldStorage()
"""
 Need connect. | separated string
 Sequence is important:
 user|password|host|database|table
"""
print 'Content-type: text/plain; charset=utf-8 \n'
try:
    # always connect
    connect=form['connect'].value
    # set up parameters
    p=dbutils.prepareDumpParameters(connect)
    parts=connect.split('|')
    if len(p) > 0:                           
        print dbutils.makeJson(p,'data/dump.xml',XSLT_FILE)
    else:
        print 'error bad parameter list in connect'
except:
        res=sys.exc_info()
        print 'error '+str(res[1])

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="utf-8"/>
<!--
Producing strait json
either from a mysqldump of one table to xml
or from mysql extract to xmlfile  with sql
Limitation:
Replace " with /" in values,
as from:
http://stackoverflow.com/questions/9370633/xslt-replacing-double-quotes-with-escape-sequence
no other cleaning or escaping
alternativ stylesheet at:
http://code.google.com/p/xml2json-xslt/

-->
<xsl:template match="/">
{"list":[<xsl:apply-templates select="//row"/>]}
</xsl:template>
<xsl:template match="row">
{<xsl:apply-templates select="field"/>
}
<xsl:if test="position() &lt; last()">,</xsl:if>
</xsl:template>
<xsl:template match="//row/field">
"<xsl:value-of select="@name"/>":"<xsl:call-template name="escapeQuote"/>"<xsl:if test="position() &lt; last()">,</xsl:if>
</xsl:template>
<!-- as from 
http://stackoverflow.com/questions/9370633/xslt-replacing-double-quotes-with-escape-sequence
-->
<xsl:template name="escapeQuote">
  <xsl:param name="pText" select="."/>
  <xsl:if test="string-length($pText) >0">
   <xsl:value-of select="substring-before(concat($pText, '&quot;'), '&quot;')"/>
   <xsl:if test="contains($pText, '&quot;')">
    <xsl:text>\"</xsl:text>
    <xsl:call-template name="escapeQuote">
      <xsl:with-param name="pText" select="substring-after($pText, '&quot;')"/>
    </xsl:call-template>
   </xsl:if>
  </xsl:if>
</xsl:template>
</xsl:stylesheet>

#! /usr/bin/python
import cgi,sys
import dbutils
#import cgitb; cgitb.enable()
"""
Select to XML
using mysql in a subprocess as implemented in dbutils
"""
form=cgi.FieldStorage()
"""
 Need connect. | separated string
 Sequence is important
 user|password|host|database
 and sql
"""
try:
    # always connect
    connect=form['connect'].value
    sql=form['sql'].value
    # set up parameters
    p=dbutils.prepareSelectParameters(connect,sql)
    if len(p) > 0:                           
        res=dbutils.doProcess(p,'data/sqlselect.xml')
        if res.startswith('ok'):
            # return the produced file
            print "Location: data/sqlselect.xml\n"
        else:   
            print 'Content-type: text/plain; charset=utf-8 \n'
            print res
    else:
        print 'Content-type: text/plain; charset=utf-8 \n'
        print 'error bad parameter list in connect'
except:
        print 'Content-type: text/plain; charset=utf-8 \n'
        res=sys.exc_info()
        print 'error '+str(res[1])

#! /usr/bin/python
import cgi,sys
from lxml import etree
import dbutils
#import cgitb; cgitb.enable()
"""
select from database to json
using mysql in a subprocess as implemented in utils
"""

# transform xml -> json
XSLT_FILE='tojson.xsl'       
form=cgi.FieldStorage()
"""
 Need connect. | separated string
 Sequence is important:
 user|password|host|database|table
 sql
"""
print 'Content-type: text/plain; charset=utf-8 \n'
try:
    # always connect
    connect=form['connect'].value
    sql=form['sql'].value
    # set up parameters
    p=dbutils.prepareSelectParameters(connect,sql)
    if len(p) > 0:                           
        print dbutils.makeJson(p,'data/select.xml',XSLT_FILE)
    else:
        print 'error bad parameter list in connect'
except:
        res=sys.exc_info()
        print 'error '+str(res[1])