Yanfeng's life and thought

Use Python to automatically get files and perform zipping transfer

Use Python to automatically get files and perform zipping transfer
The requirement:
The customers require to get the feed out files from the ftp server and send to them every day. The files on Solaris server is *.tar.gz with time stamp, however, the customer can not unzip the file on windows host. So the files must be unzipped and re-zipped to .zip file before sending to the customers.
Traditional solution:
1. Use Putty to login the file server to get the file path and name.
2. Use Winscp to connect to the server based on sftp protocol, change the directory to the file path which step 1 gets.
3. Get the files from the server to the local host via Winscp
4. Unzip the *.tar.gz and re zipped using PKZip (Need the PKZip is installed on the local host)
5. Send the files to the customers.
Improved solution:
1. Write a python program to get the files and unzip, re zip the files automatically.
2. Send the files to the customers.
The benifits:
1. The efficiency is improved significantly and the steps is very easy.
2. The quality is improved since the program is not prone to any error.
Disadvantage:
Need python runtime environment. jython can not be support: ImportError: no module named glob
Python implementation:
1. The server can only accept sftp, so the ftplib can not be accepted.
2. Use Winscp command line to get the files
3. Use 7zip command line to unzip and zip
Actually, the python program just borrow some free modules and glue them together.

sftp.py
import os,time

year = time.strftime("%Y",time.localtime())
mon = time.strftime("%m",time.localtime())
day = int(time.strftime("%d",time.localtime()))-1
fileserver = '/opt/appl/feedsout/nda_nfi/nda_nfi_vap/archive/AVG_VOL*'+str(year)+str(mon)+str(day)+'*.tar.gz '
command = 'winscp405.exe /console /log=winscp_log.txt /command "option batch on" "open sftp://abinitio:3m3m@gic3@172.26.66.226" "get '
#print command+fileserver+'.\\" "exit"'
os.system(command+fileserver+'.\\" "exit"')
#winscp405.exe /console /log=winscp_log.txt /command "option batch on" "open sftp://abinitio:3m3m@gic3@172.26.66.226" "get /opt/appl/feedsout/nda_nfi/nda_nfi_vap/archive/AVG_VOL*20091020*.tar.gz .\" "exit"

trans.py
import os,glob,time

##convert all *.tar.gz to *.zip under the current directory
filelist = glob.iglob('*.tar.gz')
for file in filelist:
os.system('"7za.exe" e '+file)
tmppath = file+str(time.time())
os.system('"7za.exe" x '+file[:-3]+' -O'+tmppath)
os.system('"7za.exe" a '+file[:-7]+'.zip '+'.\\'+tmppath+'\\*')
os.system('rd /S /Q '+tmppath)
os.system('del '+file[:-3])

JavaDB Usage

JavaDB Usage
1. set java_home and derby_home and path environment.
2. run ij or java -jar %DERBY_HOME%\lib\derbyrun.jar ij
3. Create the database and open a connection to the database using the embedded
driver.
CONNECT 'jdbc:derby:firstdb;create=true';
;create=true
The Derby URL attribute that is used to create a database. Derby does not
have an SQL create database command.
4.
Create a table with two columns using standard SQL.
CREATE TABLE FIRSTTABLE
(ID INT PRIMARY KEY,
NAME VARCHAR(12));
0 rows inserted/updated/deleted

INSERT INTO FIRSTTABLE VALUES
(10,'TEN'),(20,'TWENTY'),(30,'THIRTY');
3 rows inserted/updated/deleted

SELECT * FROM FIRSTTABLE
WHERE ID=20;
ID |NAME
------------------------
20 |TWENTY

5.
Load the SQL script ToursDB_schema.sql.
run 'ToursDB_schema.sql';

ij> show connections;
ij> connect 'jdbc:derby:sample';
ij> connect 'jdbc:derby://localhost:1527/sample';

ij> get cursor menuCursor as 'SELECT * FROM menu';
ij> next menuCursor;
COURSE |ITEM |PRICE
-----------------------------------------------
entree |lamb chop |14
ij> next menuCursor;
COURSE |ITEM |PRICE
-----------------------------------------------
dessert |creme brulee |6
ij> close menuCursor;
ij>
ij> commit;
ij> elapsedtime on;
ij> VALUES current_date;
1
----------
1998-07-15
ELAPSED TIME = 2134 milliseconds
ij> disconnect;
ij>help;

ij> protocol 'jdbc:derby:';
ij> connect 'sample' as sample1;
ij> connect 'newDB;create=true' as newDB;
ij (NEWDB)> show connections;
SAMPLE1 - jdbc:derby:sample
NEWDB* - jdbc:derby:newDB;create=true
* = current connection
ij(NEWDB)> set connection sample1;
ij(SAMPLE1)> disconnect all;
ij>

6.
Exit the ij tool.
exit;

7.
C:\JavaDB\DERBYTUTOR>dblook -d jdbc:derby:firstdb -o myDB_DDL.sql
ij> connect 'jdbc:derby:seconddb';
ij> run 'myDB_DDL.sql';
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (null,'FIRSTTABLE','myfile.del',null,null,null);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(null,'FIRSTTABLE','myfile.del',null,null,null,1);

A non-zero value for the replace parameter will import in REPLACE mode, while
a zero value will import in INSERT mode. REPLACE mode deletes all existing
data from the table by truncating the table and inserts the imported data.

Import and Export
Example importing all data from a file
The following example shows how to import data into the STAFF table in a sample
database from the myfile.del file. The data will be appended to the existing data in the
table.
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE
(null,'STAFF','myfile.del',null,null,null,0);

Example exporting all data from a table to a single export file
The following example shows how to export data from the STAFF table in a sample
database to the file myfile.del.
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE
(null,'STAFF','myfile.del',null,null,null);

XSLT Note and Example

1. Match and template
2. VS2005 will automatically format xml, auto complete the input and even can debug xslt
3. <xsl:text disable-output-escaping="yes"> </xsl:text>
4. <xsl:if test="middle"> checks for the existence of a node set rather than for
a boolean value. If any <middle> elements are found, the content of <xsl:if> is instantiated.
Checking for the existence of an attribute is very similar to checking for the existence of an
element. For example:
<xsl:if test="@someAttribute">
...execute this code if "someAttribute" is present
</xsl:if>
Inside XSLT
1. XSL = XSLT+XPath+XSL-FO
2. Root node and root element
The root node of an XSLT tree represents the entire document. It is not the same as the root element. For example, take a look at the following document; in XSLT terms, the root node represents the whole document, and the root element is <library>:
<?xml version="1.0"?>
<library>
   <book>
        <title>
            Earthquakes for Lunch
        </title>
        <title>
            Volcanoes for Dinner
        </title>
   </book>
</library>
3.  "/" Matches the root node

 "*" Matches element nodes (not all nodes, which is a common mistake to make)

 "PLANET" Matches <PLANET> elements

 "PLANET/MASS" Matches all <MASS> elements that are children of a <PLANET> element

 "//PLANET" Matches all <PLANET> elements descending from the root node

 "." Matches the current node

4. Using <xsl:apply-templates/> alone simply makes the XSLT processor search for all templates that match the child nodes of the context node, and that's the default usage. However, sometimes that's not good enough, because you may want to apply templates in a specific order, or otherwise choose what templates to apply, and you do that with the select attribute of the <xsl:apply-templates/>.
5. To refer to an attribute value using XPath, you preface the attribute name with @ like this: "@src", "@height", "@width", and so on. To match any attribute, you can use the expression "@*".

6. By default, <xsl:text> elements escape characters that could be part of markup. For example, <xsl:text>Here is a greater-than sign:></xsl:text> gets written as "Here is a greater-than sign: >," not "Here is a greater-than sign: >." You can use <xsl:text> when you want characters such as < and & to appear in your output document, rather than < and &. To do that, set the <xsl:text> element's disable-output-escaping attribute to "yes" (the default is "no").
such as:
                        <td class="valueJobHistoryDevice" width="20%">
                          <xsl:value-of select="Barcode"></xsl:value-of>
                          <xsl:if test="string-length(Barcode/text())=0">
                            <xsl:text disable-output-escaping="yes"> </xsl:text>
                          </xsl:if>
                        </td>

7. The <xsl:copy> element enables you to copy a node from the source tree to the output tree. Note that this is a shallow copy, however, which does not copy any of the node's descendents or attributes. This element has one attribute:
 use-attribute-sets . Specifies the names of attribute sets to be applied to a created element. Set to a whitespace-separated list of QNames. You can use the attribute only when the context node is an element;
However, there's an easier way of making sure that you copy all the children, attributes, and other descendents of nodes: You can use <xsl:copy-of> rather than <xsl:copy>.
The <xsl:copy-of> element enables you to make a deep copy of nodes, which means that not just the node, but all attributes and descendents are copied as well. This element has one attribute:
 select (mandatory). The node or node set you want copied.
8. You also can use the * character as a wildcard, standing for any element. (* can match only elements, although note that the pattern @* can match any attribute.) For example, the following rule applies to all <NAME> elements that are grandchildren of <PLANET> elements:
<xsl:template match="PLANET/*/NAME">
<H3>
<xsl:value-of select="."/>
</H3>
</xsl:template>

In a pattern, the node node test matches any node except the root node

XSLT Example
1. Local variable overrides global variable
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="Test.xslt"?>
<source>
<chapter>Chapter A</chapter>
<chapter>Chapter B</chapter>
<chapter>Chapter C</chapter>
<chapter>Chapter D</chapter>
</source>
<xsl:stylesheet version = '1.0'
     xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>
<xsl:variable name="text">Chapter</xsl:variable>
<xsl:template match="/">
    <TABLE>
      <xsl:for-each select="//chapter">
        <TR>
          <TD>
            <xsl:variable name="text">
              <xsl:choose>
                <xsl:when test="position() = 1">First chapter</xsl:when>
                <xsl:when test="position()=last()">Last chapter</xsl:when>
                <xsl:otherwise>
                  <xsl:value-of select="$text"/>
                </xsl:otherwise>
              </xsl:choose>
            </xsl:variable>
            <xsl:value-of select="$text"/>
            <xsl:text> : </xsl:text>
            <xsl:value-of select="."/>
          </TD>
        </TR>
      </xsl:for-each>
    </TABLE>
</xsl:template>
</xsl:stylesheet>
2. The use of for-each and test
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="Test.xslt"?>
<source>
<number>1</number>
<number>3</number>
<number>4</number>
<number>17</number>
<number>8</number>
</source>
<xsl:stylesheet version = '1.0'
     xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>
<xsl:template match="/">
    <TABLE>
      <xsl:for-each select="//number">
        <TR>
          <TD>
            <xsl:if test=".>=3">
              <xsl:value-of select="name()"/>
              <xsl:text> </xsl:text>
              <xsl:value-of select="."/>
            </xsl:if>
            <xsl:if test=".>=1">
              <xsl:value-of select="name()"/>
              <xsl:text> </xsl:text>
              <xsl:value-of select="."/>
            </xsl:if>
          </TD>
        </TR>
      </xsl:for-each>
    </TABLE>
</xsl:template>
</xsl:stylesheet>

3. Test constains muliti-conditions
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" />
<xsl:template match="/">
<html>
<body>
<h1>XPath Examples</h1>
Presidents elected between 1800 and 1850 were:
<ul>
<xsl:if test="presidents/president[(term/@from > 1800 ) and (term/@from < 1850)]">
<xsl:value-of select="count(presidents/president[(term/@from > 1800 ) and (term/@from < 1850)])"></xsl:value-of>
<xsl:apply-templates select="presidents/president[(term/@from > 1800 ) and (term/@from < 1850)]/name"/>
</xsl:if>
</ul>

</body>
</html>
</xsl:template>

<xsl:template match="name">
<li>
<xsl:value-of select="first"/>
<xsl:text> </xsl:text>
<xsl:value-of select="middle"/>
<xsl:text> </xsl:text>
<xsl:value-of select="last"/>
</li>
</xsl:template>
</xsl:stylesheet>

4. xsl:choose example:
<xsl:template match="presidents">
<h3>Color Coded by Political Party</h3>
<ul>
<xsl:for-each select="president">
<xsl:variable name="color">

<xsl:choose>
<xsl:when test="party = 'Democratic'">
<xsl:text>blue</xsl:text>
</xsl:when>
<xsl:when test="party = 'Republican'">
<xsl:text>green</xsl:text>
</xsl:when>
<xsl:when test="party = 'Democratic Republican'">
<xsl:text>purple</xsl:text>
</xsl:when>
<xsl:when test="party = 'Federalist'">
<xsl:text>brown</xsl:text>
</xsl:when>
<xsl:when test="party = 'Whig'">
<xsl:text>black</xsl:text>
</xsl:when>

<xsl:otherwise>
<xsl:text>red</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<li>
<font color="{$color}">

<xsl:apply-templates select="name"/>
<xsl:text> - </xsl:text>
<xsl:value-of select="party"/>
</font>
</li>
</xsl:for-each>
</ul>
5. call-emplate and param example:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/">
<html>
<body>
<h3>Team Members</h3>
<ul>
<xsl:for-each select="team/manager|team/programmer">
<xsl:sort select="name"/>
<li>
<xsl:value-of select="name"/>
<xsl:text>, ssn = </xsl:text>
<xsl:call-template name="formatSSN">
<xsl:with-param name="ssn" select="@ssn"/>
</xsl:call-template>
</li>
</xsl:for-each>
</ul>
</body>
</html>
</xsl:template>

<xsl:template name="formatSSN">
<xsl:param name="ssn"/>
<xsl:value-of select="substring($ssn, 1, 3)"/>
<xsl:text>-</xsl:text>
<xsl:value-of select="substring($ssn, 4, 2)"/>
<xsl:text>-</xsl:text>
<xsl:value-of select="substring($ssn, 6)"/>
</xsl:template>
</xsl:stylesheet>
6. The identity Transformation:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="@*|node( )">
<xsl:copy>
<xsl:apply-templates select="@*|node( )"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>

And maybe

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>

<xsl:template match="/">
<xsl:copy-of select="."></xsl:copy-of>
</xsl:template>
</xsl:stylesheet>

Yanfeng's life and thought

Monday, December 31, 2012

Use Python to automatically get files and perform zipping transfer

JavaDB Usage

XSLT Note and Example

About Me