Python to retrieve csv attachment from saved outlook msg file

1. import win32com.client
if you got this error:   ModuleNotFoundError: No module named 'win32com'
then install the module first:
    $ python3 -m pip install pywin32
  $ python3 -m pip install pypiwin32
2. Use absolute path
3. Outlook installed
4. Close(or Open and then Close) Outlook if you see error like:

for idx, file in enumerate(msg_files):
    # get file date 02-Apr-2020
    print(f'{idx+1}/{number_of_files}: {file}')
    date_str = re.split(' |\.', file)[-2]
    print(date_str)

    # Read attachments
    outlook = win32com.client.Dispatch('Outlook.Application').GetNamespace('MAPI')
    msg = outlook.OpenSharedItem(file)
    att = msg.Attachments

    for i in att:

        csv_file_name = os.path.join(csv_file_dir, f'{csv_file_prefix}-{date_str}.csv')
        i.SaveAsFile(csv_file_name)

To get error message from an error code:

import win32api
s = win32api.FormatMessage(-2147352565)
print(s)

s = win32api.FormatMessage(-2147287008)
print(s)

s = win32api.FormatMessage(-2147352567)
print(s)

Pandas dataframe to get column names as a list

Compare two SQL queries with pandas dataframe comparison

Compare the the two queries return the same data:

1. query 1 from QA
2. query 2 from PROD

Both queries use the same query statement:

SELECT *
FROM MY_HANA_VIEW
WHERE MY_CONDITION

1. Get data from DB

df_qa = pd.read_sql_query(QUERY_DIM_LEI_RATING, engine_qa)

df_prod = pd.read_sql_query(QUERY_DIM_LEI_RATING, engine_prod)

2. Sort by columns in place

columns_list = df_qa.columns.values.tolist()

df_qa.sort_values(by=columns,inplace=True)

df_prod.sort_values(by=columns, inplace = True)

3. Reset index in place

Drop the existing index and replace with the reset one

df_qa.reset_index(drop=True, inplace=True)

df_prod.reset_index(drop=True, inplace=True)

4. Assert frame equal

assert_frame_equal(df_qa, df_prod)

Note that you do not need step 2&3 if the columns have been ordered in SQL query statement, e.g.:

SELECT c1, c2, c3
FROM MY_HANA_VIEW
WHERE MY_CONDITION
order by c1, c2, c3

Load Thomson Reuters LEI to HANA with SAP DataService + Python

This blog is to consume TR REST API using SAP DS and Python to load TR LEI information to HANA database:

1. Create a DataFlow with 3 objects
    SQL: query HANA view to get the LEI identifiers which will be put into the payload of REST API
    User Defined Base Transform: this is where the Python code accessing REST API and processing coming response
    Table: the database table to save the data


2. Qquery HANA view to get the LEI identifiers


3. Set the input for Python processing


4. Bring up the "User Defined Editor", here it's Python


5. Set up the output of Python processing


We'll use 'Per Collection' mode


Save the final data to Collection(the data records collection, this is the output data)


With a solution using Python in DS, it is flexible and powerful for data loading and processing.
The only thing I dislike the is the integrated Python editor in SAP DS.

Note that SAP DS 4.2 support Python 2.7 only. Also the default library accessing REST API is urllib/urllib2. I'd like to install 'pip' and then install 'requests' for REST API consumption.

Share directory over HTTP with Python

cd the directory you want to share, and

# For Python >=2.4
python -m SimpleHTTPServer 8888
# For Python 3.x
python3 -m http.server 8888

Then you can access this http server with: http://your_host_ip:8888

Base64 encoded string to Decimal

We are using kafka-connect-jdbc to streaming data out of HANA Views, and the decimal columns are now saved as base64 encoded strings in Kafka.

To decode it with Python:

    """Convert a base64 encoded string to decimal
       b64str: the base64 encoded string
       Example: 'ATFvqA==' -> 20017064,  'JA==' -> 36
    """
    def b64_string_to_decimal(b64str):
        decoded_bytes = base64.b64decode(b64str)
        decimal_value = decimal.Decimal(int.from_bytes(decoded_bytes, byteorder='big'))
        return decimal_value

To decode it with Java:

    /*
     * Convert a base64 encoded string to decimal
     * b64str: the base64 encoded string
     * Example: 'ATFvqA==' -> 20017064,  'JA==' -> 36
     */
    public BigDecimal base64StringToDecimal(String b64String) {
        BigDecimal bigDecimal = new BigDecimal(new
BigInteger(Base64.getDecoder().decode(b64String)));
        return bigDecimal;
    }

2d array in python3

m = 5
n = 3

a = [[0 for x in range(n)] for y in range(m)]

Or a shorter version:
a = [[0]*n for y in range(m)]

Note: shortening this to something like the following does not really work since you end up with 5 copies of the same list, so when you modify one of them, they all change.
a = [[0]*n]*m
print(a)
a[1][2] = 3
print(a)

[[0, 0, 0], [0, 0, 0], [0, 0, 0], [0, 0, 0], [0, 0, 0]]
[[0, 0, 3], [0, 0, 3], [0, 0, 3], [0, 0, 3], [0, 0, 3]]

You can use [0] * n since  Python cannot create a reference to the value 0(it's not an object) and this produces [0,0,0]. Then if you pretend you had a variable x = [0,0,0] then

c1 = x * 5
[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]


c2 = [x] * 5
[[0, 0, 0], [0, 0, 3], [0, 0, 0], [0, 0, 0], [0, 0, 0]]
[[0, 22, 0], [0, 22, 0], [0, 22, 0], [0, 22, 0], [0, 22, 0]]

Break training data into slices for stochastic gradient decent:

import numpy as np
n = 100
training_data = list(range(n))
mini_batch_size = 10
np.random.shuffle(training_data)
mini_batches = [training_data[k:k+mini_batch_size]
    for k in range(0, n, mini_batch_size)]
mini_batches

[[90, 5, 70, 82, 58, 2, 16, 85, 12, 35],
 [14, 54, 62, 39, 96, 73, 60, 80, 33, 89],
 [20, 38, 76, 47, 65, 42, 71, 46, 93, 34],
 [52, 64, 13, 92, 17, 49, 88, 63, 74, 23],
 [43, 25, 10, 97, 48, 68, 95, 81, 24, 31],
 [9, 32, 84, 83, 22, 87, 61, 26, 28, 99],
 [0, 67, 30, 69, 72, 45, 79, 51, 40, 55],
 [6, 15, 75, 66, 29, 3, 18, 77, 98, 21],
 [53, 44, 50, 19, 91, 8, 11, 59, 27, 56],
 [36, 94, 7, 57, 1, 37, 86, 78, 41, 4]]

Indices in Python list

You may feel uncomfortable with Python indices at the beginning. But it is really convenient if you understand it. You'd love its simplicity actually:

>>> a = list(range(10))
>>> a
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
>>> a[::2]
[0, 2, 4, 6, 8]
>>> a[1::2]
[1, 3, 5, 7, 9]
>>> a[::-2]
[9, 7, 5, 3, 1]
>>> a[1::-2]
[1]
>>> a[1:8]
[1, 2, 3, 4, 5, 6, 7]
>>> a[1:-2]
[1, 2, 3, 4, 5, 6, 7]
>>> a[::-1]
[9, 8, 7, 6, 5, 4, 3, 2, 1, 0]
>>> a[100]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
IndexError: list index out of range
>>> a[:100]
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
>>> a[7:100]
[7, 8, 9]

Reference:

https://docs.python.org/3/tutorial/introduction.html#strings

pandas read_csv from https with Python 3.6.4

On Mac OSX, if you are using Python 3.6 and pandas to try to read a csv file via https:

california_housing_dataframe = pd.read_csv("https://storage.googleapis.com/mledu-datasets/california_housing_train.csv", sep=",")
california_housing_dataframe.describe()

 you may get an error like:
urllib.error.URLError:

To fix this issue:

Open a terminal and take a look at:

/Applications/Python 3.6/Install Certificates.command

Python 3.6 on MacOS uses an embedded version of OpenSSL, which does not use the system certificate store. More details here.

(To be explicit: MacOS users can probably resolve by opening Finder and double clicking Install Certificates.command)

Or read https csv with a workaround:

from io import StringIO

import pandas as pd
import requests


url = "https://storage.googleapis.com/mledu-datasets/california_housing_train.csv"
s = requests.get(url).text
c = pd.read_csv(StringIO(s))
print(c.head())

numpy.array vs numpy.asarray

Looking at the definition, you'll see the difference between them:

def asarray(a, dtype=None, order=None):
    return array(a, dtype, copy=False, order=order)

The main difference is that array (by default) will make a copy of the object, while asarray will not unless necessary.

The difference can be demonstrated by this example:

1. generate a matrix

   >>> A = numpy.matrix(np.ones((3,3)))
   >>> A
   matrix([[ 1.,  1.,  1.],
           [ 1.,  1.,  1.],
           [ 1.,  1.,  1.]])

2. use numpy.array to modify A. Doesn't work because you are modifying a copy

   >>> numpy.array(A)[2]=2
   >>> A
   matrix([[ 1.,  1.,  1.],
           [ 1.,  1.,  1.],
           [ 1.,  1.,  1.]])

3. use numpy.asarray to modify A. It worked because you are modifying A itself

   >>> numpy.asarray(A)[2]=2
   >>> A
   matrix([[ 1.,  1.,  1.],
           [ 1.,  1.,  1.],
           [ 2.,  2.,  2.]])

Python None vs Empty list

>>> a=[]
>>> b=None
>>> type(a),type(b)
(<class 'list'>, <class 'NoneType'>)
>>> not a, not b
(True, True)
>>> a is None, b is None
(False, True)
>>> a is not None, b is not None
(True, False)
>>> a,b
([], None)

best way to check if a list is empty in Python3

Do it with:

if not a:
    print("a is an empty list.")

instead of:

if len(a):
    print("a is an empty list.")

Reference: Official Python programming recommendations
See the discussions on Stack Overflow

/ vs // in python3

Code speaks:

>>> a=b=5
>>> a,b
(5, 5)
>>> type(a), type(b)
(<class 'int'>, <class 'int'>)
>>> a/=2
>>> b//=2
>>> a,b
(2.5, 2)
>>> type(a), type(b)
(<class 'float'>, <class 'int'>)
>>> alist = list(range(10))
>>> alist
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
>>> alist[:a]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: slice indices must be integers or None or have an __index__ method
>>> alist[:b]
[0, 1]

Note if you are trying calculating the indices with '/' you would get trouble as showed above.