Converting a Python script to a SQL database script. (python, sql, np.arrange, base64, binary)
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)
try:
data_binary = [ int(s) for s in data_binary ] #convert string to integer array
except:
print("ERROR: Run it with python3 for CLI.")
exit(0)
#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")
plt.show()
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)
CONTINUE
END
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)
CONTINUE
END
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.
Subscribe to our email newsletter