Including a local python module

As we saw in reusing your python code, you can create a python file, a module, that contains our core business logic and then re-use that file. This post is going to talk about to make that happen.

What happens when you import antigravity? The python interpreter is going to check all the places it knows to find modules. It's going to check the install location for a module, it's going to check if you defined pythonhome/pythonpath environment variables, and you can hint to your hearts desire where to find files. If I import a real module, import pprint, I can access the __file__ property which will tell you where it found the module. In my case it was C:\Python38\lib\pprint.py

Python is happy to tell you what the current search path is import sys print(sys.path)

On my machine, that displays an array of ['', 'C:\\Python38\\python38.zip', 'C:\\Python38\\DLLs', 'C:\\Python38\\lib', 'C:\\Python38', 'C:\\Python38\\lib\\site-packages'] If I want reusable_code.py to be callable by another module, then it needs to exist in one of those locations. That first entry of a blank path is the current directory so as long as the module I need is in the same folder, we're golden! I have added i_use_resuable.py to the same folder as the above

# This module lives in the same folder as our reusable_code.py file
import reusable_code


def main():
    c = reusable_code.Configuration()
    print(c.get_modify_date())


if __name__ == '__main__':
    main()

Executing that, I get the expected timestamp - the exact same experience as running our corporate file but now I can focus on using the business logic instead of writing it. We're going to need something more though if we're going to get this reuable code into our pyspark cluster.

In the next post, we'll learn how to package our business module up into something we can install instead of just assuming where the file is.

As always, the code is on my github repro

Reusing your python code

Reusing your ptyhon code

I learned python in 2003 and used it for all the ETL work I was doing. It was beautiful and I would happilly wax to any programmer friends about the language and how they should be learning it. It turns out, my advocacy was just 15+ years too early. I recently had a client reach out to engage me to work on their Databricks project. No gentle reader, I don't much of anything about Databricks. But I do know about working with data, python programming (which I was already updating my mental model to 3.0) and pandas. Yes, pandas is not what we do in databricks but the concepts are similar.

One of the early observations is that they had dozens of notebooks with copy and paste code across them. Copy and paste code in a metadata driven solution isn't an evil but when you're hand crafting boiler plate code artifacts by hand, you're going to sneak a code mutation in there. So, let's look at how we can avoid this with code re-use.

Let's assume we use an important business process that needs to be consistent across our infrastructure. In this case, it's a modification date which is used as part of our partition strategy. This code nugget is spread across all those notebooks datetime.now().strftime("%Y-%m-%dT%H:%M:%SZ") When processing starts up, we set a timestamp so that all activities accrue under that same timestamp. It's a common pattern across data processing. How could we do this better?

In classic python programming, we would abstract that logic away in a reusable library. In this example, I have created a module (file) named reusable_code.py In it, I created a class named Configuration and it exposes a method get_modify_date

# reusable_code.py is a python module that simulates our desire to 
#consolidate our corporate business logic into a re-usable entity

from datetime import datetime

class Configuration():
    """A very important class that provides a standardized approach for our company"""
    def __init__(self):
        # The modify date drives very important business functionality
        # so let's be consitent in how it is defined (ISO 8601)
        # 2021-02-07T17:58:20Z
        self.__modify_date__ = datetime.now().strftime("%Y-%m-%dT%H:%M:%SZ")

    def get_modify_date(self):
        """Provide a standard interface for accessing the modify date"""
        return self.__modify_date__


def main():
    c = Configuration()
    print(c.get_modify_date())

if __name__ == "__main__":
    main()

Usage is simple, I create an instance of my class c, which causes the constructor/initalizer to fire and set the modify date for the life of that object. Calling the get_modify_date method results in an ISO 8601 date to be emitted

2021-02-07T17:58:20Z

At this point, I hope you have an understanding of how we can make a reusable widget. Think about your business processes that you need to encapsulate in to reusable components and tomorrow we'll review using existing python modules in new files. After that, we'll cover converting this module into a wheel. And then we'll walk through installing it to a DataBricks cluster and using it from a notebook. Sound good?

All of this code is available on my github repository -> 2021-02-08_PythonReusableCode

Python pandas repeating character tester

At one of our clients, we are data profiling. They have a mainframe, it's been running for so long, they no longer have SMEs for their data. We've been able to leverage Service Broker to provide a real-time, under 3 seconds, remote file store for their data. It's pretty cool but now they are trying to do something with the data so we need to understand what the data looks like. We're using a mix of TSQL and python to understand nullability, value variances, etc. One of the "interesting" things we've discovered is that they loved placeholder values. Everyone knows a date of 88888888 is a placeholder for the actual date which they'll get two steps later in the workflow. Except sometimes we use 99999999 because the eights are the placeholder for the time.

Initially, we were just searching for one sentinel value, then two values until we saw the bigger pattern of "repeated values probably mean something." For us, this matters because we then need to discard those rows for data type suitability. 88888888 isn't a valid date so our logic might determine that column is best served by a numeric data type. Unless we exclude the eights value in which we get a 100% match rate on the column's ability to be converted to a date.

How can we determine if a string is nothing but repeated values in python? There's a very clever test from StackOverflow

source == source[0] * len(source) I would read that as "is the source variable exactly equal to the the first character of source repeated for the length of source?"

And that was good, until we hit a NULL (None in python-speak). We then took advantage of the ternary equivalent in python to make it

(source == source[0] * len(source)) if source else False

Enter Pandas (series)

Truth is a funny thing in an Pandas Series. Really, it is. The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().. We were trying to apply the above function as we were doing everything else

df.MyColumn.str.len()
# this will fail magnificantly
(df.MyColumn == df.MyColumn[0] * len(df.MyColumn)) if df.MyColumn else False

It took me a while since I hadn't really used the pandas library beyond running what my coworker had done. What I needed to do, was get a row context to apply the calculations for true/false. As it stands, the Series stuff wants to try and aggregate the booleans or something like that. And it makes sense from a SQL perspective, you can't really apply aggregates to bit fields (beyond COUNT).

So, what's the solution? As always, you're likely to say the exact thing you're looking for. In this case, apply was the keyword.

df.MyColumn.apply(lambda source: (source == source[0] * len(source)) if source else False)

Full code you can play with would be

import pandas
import pprint

def isRepeated(src):
    return (src == src[0] * len(src)) if src else False
    
df = pandas.DataFrame({"MyCol":pandas.Series(['AB', 'BC', 'BB', None])})

pprint.pprint(df)

print()
# What rows have the same character in all of them?

pprint.pprint(df.MyCol.apply(lambda source:(source == source[0] * len(source)) if source else False))
#If you'd like to avoid the anonymous function...
pprint.pprint(df.MyCol.apply(isRepeated))

In short, python is glorious and I'm happy to writing in it again ;)

---

Python Azure Function requestor's IP address

I'm working on an anonymous level Azure Function in python and couldn't find where they stored the IP address of the caller, if applicable. It's in the request headers, which makes sense but not until I spent far too much time looking in all the wrong places. A minimal reproduction would look something like

import os
iptag = "REQ_HEADERS_X-FORWARDED-FOR"
ip = "Tag name:{} Tag value:{}".format(iptag, os.environ[iptag])
print(ip)

Now, something to note is that it will return not only the IP address but the port the call came in through. Thus, I see a value of 192.168.1.200:33496 instead of just the ipv4 value.

Knowing where to look, I can see that the heavy lifting had already been done by the most excellent HTTPHelper but as a wise man once said: knowing is half the battle.

import os
from AzureHTTPHelper import HTTPHelper
http = HTTPHelper()
#Notice the lower casing of properties here and the trimming of the type (REQ_HEADERS)
iptag = "x-forwarded-for"
ip = "Tag name:{} Tag value:{}".format(iptag, http.headers[iptag])
print(ip)

Yo Joe!

Python pyinstaller erroring with takes 4 positional arguments but 5 were given

Pyinstaller is a program for turning python files into executable programs. This is helpful as it removes the requirement for having the python interpreter installed on a target computer. What was really weird was I could generate a multi-file package (pyinstaller .\MyFile.py) but not a onefile version.

C:\tmp>pyinstaller -onefile .\MyFile.py

Traceback (most recent call last):
  File "C:\Program Files (x86)\Python35-32\Scripts\pyinstaller-script.py", line 9, in 
    load_entry_point('PyInstaller==3.2.1', 'console_scripts', 'pyinstaller')()
  File "c:\program files (x86)\python35-32\lib\site-packages\PyInstaller\__main__.py", line 73, in run
    args = parser.parse_args(pyi_args)
  File "c:\program files (x86)\python35-32\lib\argparse.py", line 1727, in parse_args
    args, argv = self.parse_known_args(args, namespace)
  File "c:\program files (x86)\python35-32\lib\argparse.py", line 1759, in parse_known_args
    namespace, args = self._parse_known_args(args, namespace)
  File "c:\program files (x86)\python35-32\lib\argparse.py", line 1967, in _parse_known_args
    start_index = consume_optional(start_index)
  File "c:\program files (x86)\python35-32\lib\argparse.py", line 1907, in consume_optional
    take_action(action, args, option_string)
  File "c:\program files (x86)\python35-32\lib\argparse.py", line 1835, in take_action
    action(self, namespace, argument_values, option_string)
TypeError: __call__() takes 4 positional arguments but 5 were given

What's the root cause? The argument is --onefile not -onefile.

Getting Windows share via python

Windows network shares with python

Backstory

On a daily basis, we receive data extracts from a mainframe. They provide a header and data file for whatever the business users want to explore. This client has lots of old data ferreted away and they need to figure out if there's value in it. Our job is to consume the header files to drop and create tables in SQL Server and then populate with actual data. The SQL is trivial -

CREATE TABLE Foo (Col1 varchar(255), ColN varchar(255)); 
BULK INSERT Foo FROM 'C:\sourceFile.csv' WITH (FIRSTROW=1,ROWTERMINATOR='\n',FIELDTERMINATOR='|');

Let's make this harder than it should be

Due to ... curious permissions and corporate politics, the SQL Server service account could only read files via a network share (\\Server\Share\Input\File.csv), never you no mind the fact that path was really just D:\Share\Input. A local drive but permissions were such that we couldn't allow the service account to read from the drive. Opening a network share up and letting the account read from that - no problem.

What are the shares?

That's an easy question to answer, because I knew the answer. net share. I coded up a simple parser and all was well and good until I ran it on the server which had some really, long share names and/or the Resource was long. Like this

Share name   Resource                        Remark

-------------------------------------------------------------------------------
C$           C:\                             Default share
IPC$                                         Remote IPC
ADMIN$       C:\WINDOWS                      Remote Admin
DEV2016      \\?\GLOBALROOT\Device\RsFx0410\\DEV2016
                                             SQL Server FILESTREAM share
RidiculouslyLongShareName
             C:\users\bfellows\Downloads
The command completed successfully.

Super. The output of net share is quasi fixed width and it just wraps whatever it needs to onto the next line/column.

What are the shares^v2

Windows Management Instrumentation to the rescue! WMIC.exe /output:stdout /namespace:\\root\cimv2 path Win32_Share GET Name, Path That's way better, sort of

Name                       Path
ADMIN$                     C:\WINDOWS
C$                         C:\
DEV2016                    \\?\GLOBALROOT\Device\RsFx0410\\DEV2016
IPC$
RidiculouslyLongShareName  C:\users\bfellows\Downloads

Originally, that command ended with GET * which resulted in a lot more information being returned than I needed. The devil though, is that the output width is dependent upon the source data. If I remove the network share for my RidiculouslyLongShareName and rerun the command, I get this output

Name     Path
ADMIN$   C:\WINDOWS
C$       C:\
DEV2016  \\?\GLOBALROOT\Device\RsFx0410\\DEV2016
IPC$
Users    C:\Users

It appears to be longest element +2 spaces for this data but who knows what the real encoding rule is. The good thing is, that while variable, the header rows gives me enough information to slice up the data as needed.

This needs to run anywhere

The next problem is that this process in Dev runs on D:\Share but in QA is is on the I:\datafiles\instance1 and oh by the way, there are two shares for the I drive \\qa\Instance1 (I:\datafiles\instance1) and \\qa\datafiles. (I:\datafiles) In the case where there are multiple shares, if there's one for the folder where the script is running, that's the one we want. Otherwise, it's probably the "nearest" path which I interpreted as having the longest path.

Code good

Here's my beautiful, hacky python. Wherever this script runs, it will then attempt to render the best share path to the same location.

import os
import subprocess

def _generate_share_dictionary(headerRow):
    """Accepts a variable width, white space delimited string that we attempt
        to divine column delimiters from. Returns a dictionary of field names
        and a tuple with start/stop slice positions"""

    # This used to be a more complex problem before I realized I didn't have
    # to do GET * in my source. GET Name, Path greatly simplifies
    # but this code is generic so I keep it as is

    header = headerRow
    fields = header.split()
    tempOrds = {}
    ords = {}
    # Populate the temporary ordinals dictionary with field name and the
    # starting, zero based, ordinal for it.
    # i.e. given
    #Name     Path
    #01234567890123456789
    # we would expect Name:0, Path:9
    for field in fields:
        tempOrds[field] = headerRow.index(field)

    # Knowing our starting ordinal positions, we will build a dictionary of tuples
    # that contain starting and ending positions of our fields
    for iter in range(0, len(fields) -1):
        ords[fields[iter]] = (tempOrds[fields[iter]], tempOrds[fields[iter+1]])
        
    # handle the last element
    ords[fields[-1]] = (tempOrds[fields[-1]], len(headerRow))

    return ords

def get_network_shares():
    """Use WMIC to get the full share list. Needed because "net share" isn't parseable"""
    _command = r"C:\Windows\System32\wbem\WMIC.exe /output:stdout /namespace:\\root\cimv2 path Win32_Share GET Name, Path"
    #_command = r"C:\Windows\System32\wbem\WMIC.exe /output:stdout /namespace:\\root\cimv2 path Win32_Share GET *"
    _results = subprocess.check_output(_command, shell=True).decode('UTF-8')

    _headerRow = _results.splitlines()[0]
    headerOrdinals = _generate_share_dictionary(_headerRow)

    _shares = parse_network_shares_name_path(headerOrdinals, _results)
    return _shares

def parse_network_shares_name_path(header, results):
    """Rip apart the results using our header dictionary"""
    _shares = {}
    #use the above to slice into our results
    #skipping first line since it is header
    for _line in results.splitlines():
        if _line:
            _shares[_line[header["Name"][0]: header["Name"][1]].rstrip()] = _line[header["Path"][0]: header["Path"][1]].rstrip()
    return _shares
    

def translate_local_path_to_share(currentPath):
    """Convert the supplied path to the best match in the shares list"""
    shareName = ""
    defaultShare = ""
    shares = get_network_shares()

    # find the first share match
    if currentPath in shares.values():
        shareName = [key for key, value in shares.items() if value == currentPath][0]
    else:
        #see if we can find a partial match
        # favor longest path
        best = ""
        pathLength = 0
        for share, path in shares.items():
            # path can be empty due to IPC$ share
            if path:
                # Is the share even applicable?
                if path in currentPath:
                    # Favor the non default/admin share (DriveLetter$)
                    if share.endswith('$'):
                        defaultShare = currentPath.replace(path[:-1], share)
                    else:
                        if len(path) > pathLength:
                            shareName = currentPath.replace(path[:-1], share)

        # No other share was found
        if (defaultShare and not shareName):
            shareName = defaultShare
    x = os.path.join(r"\\" + os.environ['COMPUTERNAME'], shareName)
    print("Current folder {} maps to {}".format(currentPath, x))
    
    return os.path.join(r"\\" + os.environ['COMPUTERNAME'], shareName)


def main():
    
    current = os.getcwd()
    #current = "C:\WINDOWS"
    share = translate_local_path_to_share(current)
    print("{} aka {}".format(current, share))

if __name__ == "__main__":
    main()

Takeaways

You probably won't ever need all of the above code to be able to swap out a local path for a network share using python but by golly if you do, have fun. Also, python is still my most favorite language, 14 years running.

Python text parser

@Neil_Hambly is having some file import issues "BCP file Quoted identifier, comma seperated. some data fields have quotes. so client having import issues that issue is that these TEXT files are not going to work form them due to the data already having quotes etc.. 2/2 any suggestions" It sounded like some python scripting might be helpful for standardizing/resetting his input files. Python was my preferred tool for parsing through files as one point in my life and I thought I'd dust the cobwebs off that part of my life and attempt a solution.

Much thanks to Doug Hellmann and his Python Module of the Week for the syntax refresher.

Given these two files f1.txt

header1|"header 2"|"header | 3"|header 4
delimited file|1234|Pop goes the weasel|bob's your uncle
Looks at these c,,,,|5321|abc|bob's your uncle

and f2.txt

h1,h2,h3
a,"b,c",d
",1",2,"3"
Z,Y,"X,"

I created a file called parseIt.py

mport csv
import glob

# helpful refreshers from http://www.doughellmann.com/PyMOTW/csv/index.html
csv.register_dialect('pipes', delimiter='|')
for fname in glob.glob(r'*.txt'):
    fin = open(fname, 'rt')
    print "processing ", fname
    if fname == 'f1.txt' :
        reader = csv.reader(fin, dialect='pipes')
    else:
        reader = csv.reader(fin)

    # Perform whatever action with the parsed data, either rewrite into something simpler or
    # make direct database calls
    # This code will simply rewrite everything into a consistent output format

    fout = open(fname + ".out.csv", 'wt')
    # writer = csv.writer(fout, quoting=csv.QUOTE_ALL)
    # writer = csv.writer(fout, quoting=csv.QUOTE_MINIMAL)
    writer = csv.writer(fout, quoting=csv.QUOTE_NONNUMERIC)
    # writer = csv.writer(fout, quoting=csv.QUOTE_NONE)
    for line in reader:
        print(line)
        writer.writerow(line)
    fin.close()
    fout.close()

So what's that do for us? Depending on which writer/quoting style you select above, it could give you any of the following. QUOTE_ALL, QUOTE_MINIMAL and QUOTE_NONNUMERIC presented below for f1.txt and f2.txt

"header1","header 2","header | 3","header 4"
"delimited file","1234","Pop goes the weasel","bob's your uncle"
"Looks at these c,,,,","5321","abc","bob's your uncle"

header1,header 2,header | 3,header 4
delimited file,1234,Pop goes the weasel,bob's your uncle
"Looks at these c,,,,",5321,abc,bob's your uncle

"header1","header 2","header | 3","header 4"
"delimited file","1234","Pop goes the weasel","bob's your uncle"
"Looks at these c,,,,","5321","abc","bob's your uncle"

"h1","h2","h3"
"a","b,c","d"
",1","2","3"
"Z","Y","X,"

h1,h2,h3
a,"b,c",d
",1",2,3
Z,Y,"X,"

"h1","h2","h3"
"a","b,c","d"
",1","2","3"
"Z","Y","X,"

Unfortunately, I'm going to be late for work or I'd love to show off some of the nifty things you can do with the data or the hacks you can employ to standardize it if it's even less formatted than I'm imagining.