Python 2 SQL Base64 Encoded Data

Converting a Python script to a SQL database script. (python, sql, np.arrange, base64, binary)

Raivis Strogonovs
Raivis Strogonovs
Python and SQL
Python and SQL

This post needs a little bit of back story. UDLive were working with a Texas Instruments device to drive an ultrasound transciever; the data from the device dump outputs is a base64 encoded string. We needed to be able to analyse these dumps en masse for thousands of devices at a time and drive decisions.

This happens a fair amount to us. Someone will put together a python script to give us some data analysis for to be used in the lab. It then needs to be available for use by the database. As we want the result in the database and we are using SQL Azure here, a quick solution is to convert to SQL script.

Lets take a look at the python:

                import base64
                import matplotlib
                import matplotlib.pyplot as plt
                import numpy as np
                import sys
                #example data should have 45cm on P1. This is an actual DATA generated by a device and transmitted via telemetry input_data_b64 = "ABAAAEAIIkpXV2uIpb3Q3ufs7e3o39jQzMrKysvLysnJycrKysrKycfEvbGij3xqWEg7LyYgGy4rLi4qLDA6PkJKT1dXUE9YWE07MDM /RkU8WoS34Pz////617CmjnlxWUhJPzRBU2NvbWpiWVRAN0VJS0tQTkQ+MytGU1ROQkBBPTsuKRAAUAAAQP////////////////////////++yc51pNP//////9aFYI 3s8tH//93firyb///////W/////8KTl9L/////qpyf////////////////5ZmCuZB/0uT//5jr2lqQy8j////////HkJ+g8v/cyuv///////+4xuH///f//////6nAGg=="
                if len(sys.argv) == 2:
                print("Taking input data from command line")
                input_data_b64 = str(sys.argv[1]).strip()
                #1. let's convert it back to binary
                #Byte map is as follows: [4][130][4][130] = [rectime P1][Echo dump P1][rectime P2][Echo Dump P2] = [uint32_t][char*][uint32_t][char*]
                # Record times (rectime) are in microseconds. Remember that it includes round trip. So results need to be divided by 2
                data_binary = base64.b64decode(input_data_b64)
                data_binary = [ int(s) for s in data_binary ] #convert string to integer array
                print("ERROR: Run it with python3 for CLI.")
                #2. extract rectime 1
                rectimeP1 = ( data_binary[3] << 24)
                rectimeP1 += ( data_binary[2] << 16)
                rectimeP1 += ( data_binary[1] << 8 )
                rectimeP1 += data_binary[0]
                print("Record time P1 (us) {}".format(rectimeP1))
                #3. extract echo data for P1
                p1EchoDumpData = data_binary[4:134]
                p1XUsValues = np.arange(0, rectimeP1, rectimeP1/130)
                p1CmValues = np.arange(0, rectimeP1*0.0343/2, ((rectimeP1*0.0343)/130/2)) #distance in CM. Speed of sound is 0.0343 cm/uS
                #4. extract rectime 2
                rectimeP2 = ( data_binary[137] << 24)
                rectimeP2 += ( data_binary[136] << 16)
                rectimeP2 += ( data_binary[135] << 8 )
                rectimeP2 += data_binary[134]
                print("Record time P2 (us) {}".format(rectimeP2))
                #5. extract echo data for P2
                p2EchoDumpData = data_binary[138:]
                p2XUsValues = np.arange(0, rectimeP2, rectimeP2/130)
                p2CmValues = np.arange(0, rectimeP2*0.0343/2, ((rectimeP2*0.0343)/130/2)) #distance in CM. Speed of sound is 0.0343 cm/uS
                #6. plot the data
                fig = plt.figure(1)
                fig.suptitle('P1 Echo Dump', fontsize=20)
                p1_ax1 = fig.add_subplot(111)
                p1_ax2 = p1_ax1.twiny()
                p1_ax1.plot(p1XUsValues, p1EchoDumpData)
                p1_ax1.set_xlabel("Time (us)")
                p1_ax2.plot(p1CmValues, p1EchoDumpData)
                p1_ax2.set_xlabel("Distance (cm)")
                p1_ax1.set_ylabel("P1 Echo Intensity")
                fig2 = plt.figure(2)
                fig2.suptitle('P2 Echo Dump', fontsize=20)
                p2_ax1 = fig2.add_subplot(111)
                p2_ax2 = p2_ax1.twiny()
                p2_ax1.plot(p2XUsValues, p2EchoDumpData)
                p2_ax1.set_xlabel("Time (us)")
                p2_ax2.plot(p2CmValues, p2EchoDumpData)
                p2_ax2.set_xlabel("Distance (cm)")
                p2_ax1.set_ylabel("P2 Echo Intensity")

So clearly there are a few problems to overcome here. Data coversions and string mappings do not work the same. The exceptionally useful 'numpy' library functions (in the case arrange) are not native either (although we could easily create). Albeit our solution below shows the basis for a function if you wanted to make this.

I will add the full solution here first and then discuss a couple of the elements.

                DECLARE @message  VARBINARY(268) --Length of base64 string
                DECLARE @t  TABLE(sql_handle_base64 varchar(360)) --single value variable table to contain the binary
                DECLARE @p1  INT DECLARE @p2  INT
                --p1 and p2 are the strings for the two encoded values DECLARE @cnt  INT =1
                --Simple counter loop DECLARE @output  TABLE ([profile] nvarchar(2),us float, distancecm float, intensity INT)
                --output table variable
                INSERT INTO @t
                --Our base 64 string SELECT 'ABAAAEAIIkpXV2uIpb3Q3ufs7e3o39jQzMrKysvLysnJycrKysrKycfEvbGij3xqWEg7LyYgGy4rLi4qLDA6PkJKT1dXUE9YWE07MDM /RkU8WoS34Pz////617CmjnlxWUhJPzRBU2NvbWpiWVRAN0VJS0tQTkQ+MytGU1ROQkBBPTsuKRAAUAAAQP////////////////////////++yc51pNP//////9aFYI
                3s8tH//93firyb///////W/////8KTl9L/////qpyf////////////////5ZmCuZB/0uT//5jr2lqQy8j////////HkJ+g8v/cyuv///////+4xuH///f//////6nAGg=='' sql_handle_base64
                SET @message  = (select cast(N'' as xml).value('xs:base64Binary(sql:column("t.sql_handle_base64"))', 'varbinary(268)') as sql_handle from @t  as t) --Convert to binary
                SELECT @p1  = CAST((SUBSTRING(@message,4,1)) ++ (SUBSTRING(@message,3,1)) ++ (SUBSTRING(@message,2,1)) ++ (SUBSTRING(@message,1,1)) AS INT)
                SELECT @p2  = CAST((SUBSTRING(@message,138,1)) ++ (SUBSTRING(@message,137,1)) ++ (SUBSTRING(@message,136,1)) ++ (SUBSTRING(@message,135,1)) AS INT)
                INSERT INTO @output  SELECT 'p1', 0, 0, 0
                WHILE @cnt  <=130 BEGIN
                INSERT INTO @output  SELECT 'p1', CAST(@p1  AS FLOAT) * @cnt  /130, (CAST(@p1  AS FLOAT) * @cnt  *0.0343)/2 /130, CAST(SUBSTRING(@message, 4 + @cnt  ,1) AS INT)
                SET @cnt  = (SELECT @cnt  +1)
                SET @cnt  =1
                INSERT INTO @output  SELECT 'p2', 0, 0, 0
                WHILE @cnt  <=130 BEGIN
                INSERT INTO @output  SELECT 'p2', CAST(@p2  AS FLOAT) * @cnt  /130, (CAST(@p2  AS FLOAT) * @cnt  *0.0343)/2 /130, CAST(SUBSTRING(@message, 138 + @cnt, 1) AS INT)
                SET @cnt  = (SELECT @cnt  +1)
                SELECT * FROM @output

The aspects that are of interest are many. How simple working with strings is in python, how easy the conversions are and how easy the graphing is. however we have managed to unpack it all in a way that we can use. Take a look in particular at how the base64 conversion worked differently and how the np.arrange function was replaced by using a while loop to distribute the data.

We hope you have enjoyed this post. Look out for more on this topic. Drop us a comment or add us on linkedin if this has been interesting.

Keep up to date with UDlive

Subscribe to our email newsletter