Use of bcp is blocked where I am working now. But Powershell and sqlcmd are very much available on the Windows workstations we use. Just as with bcp, smithing text for sqlcmd input can be a little tricky, same with Powershell. But Powershell has an EncodedCommand feature which allows you to feed input to it as a base 64 string. This will be a quick demo of the use of this feature and output of a faux comma delimited (csv) file with data.
Disclaimer: scripts that rely extensively on os.system() calls from Python are indeed hacky and mousetrappy. I think the saying goes "Necessity is a mother," or something similar. Onward.
Getting the base 64 string from the original string:
First our SQL code that queries a mock table I made in my mock database:
USE test;
SELECT testpk,
namex,
[value]
FROM testtable
ORDER BY testpk;
namex,
[value]
FROM testtable
ORDER BY testpk;
We will call this file selectdata.sql.
Then the call to sqlcmd/Powershell:
sqlcmd -S localhost -i .\selectdata.sql -E -h -1 -s "," -W | Tee-Object -FilePath .\testoutput
In Python (we have to use Python 2.7 in our environment, so this is Python 2.x specific):
Python 2.7.6 (default, Nov 10 2013, 19:24:24) [MSC v.1500 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import base64
>>> stringx = r'sqlcmd -S localhost -i .\selectdata.sql -E -h -1 -s "," -W | Tee-Object -FilePath .\testoutput'
>>> bytesx = stringx.encode('utf-16-le')
>>> encodedcommandx = base64.b64encode(bytesx)
>>> encodedcommandx
'cwBxAGwAYwBtAGQAIAAtAFMAIABsAG8AYwBhAGwAaABvAHMAdAAgAC0AaQAgAC4AXABzAGUAbABlAGMAdABkAGEAdABhAC4AcwBxAGwAIAAtAEUAIAAtAGgAIAAtADEAIAAtAHMAIAAiACwAIgAgAC0AVwAgAHwAIABUAGUAZQAtAE8AYgBqAGUAYwB0ACAALQBGAGkAbABlAFAAYQB0AGgAIAAuAFwAdABlAHMAdABvAHUAdABwAHUAdAA='
>>>
Type "help", "copyright", "credits" or "license" for more information.
>>> import base64
>>> stringx = r'sqlcmd -S localhost -i .\selectdata.sql -E -h -1 -s "," -W | Tee-Object -FilePath .\testoutput'
>>> bytesx = stringx.encode('utf-16-le')
>>> encodedcommandx = base64.b64encode(bytesx)
>>> encodedcommandx
'cwBxAGwAYwBtAGQAIAAtAFMAIABsAG8AYwBhAGwAaABvAHMAdAAgAC0AaQAgAC4AXABzAGUAbABlAGMAdABkAGEAdABhAC4AcwBxAGwAIAAtAEUAIAAtAGgAIAAtADEAIAAtAHMAIAAiACwAIgAgAC0AVwAgAHwAIABUAGUAZQAtAE8AYgBqAGUAYwB0ACAALQBGAGkAbABlAFAAYQB0AGgAIAAuAFwAdABlAHMAdABvAHUAdABwAHUAdAA='
>>>
I had to type out my command in the Python interpreter. When I pasted it in from GVim, it choked on the UTF encoding.
Now, Powershell:
PS C:\Users\ctrachte> $sqlcmdstring = 'sqlcmd -S localhost -i .\selectdata.sql -E -h -1 -s "," -W | Tee-Object -FilePath
.\testoutput'
PS C:\Users\ctrachte> $encodedcommand = [Convert]::ToBase64String([Text.Encoding]::Unicode.GetBytes($sqlcmdstring))
PS C:\Users\ctrachte> $encodedcommand
cwBxAGwAYwBtAGQAIAAtAFMAIABsAG8AYwBhAGwAaABvAHMAdAAgAC0AaQAgAC4AXABzAGUAbABlAGMAdABkAGEAdABhAC4AcwBxAGwAIAAtAEUAIAAtAGgAIAAtADEAIAAtAHMAIAAiACwAIgAgAC0AVwAgAHwAIABUAGUAZQAtAE8AYgBqAGUAYwB0ACAALQBGAGkAbABlAFAAYQB0AGgAIAAuAFwAdABlAHMAdABvAHUAdABwAHUAdAA=
PS C:\Users\ctrachte>
.\testoutput'
PS C:\Users\ctrachte> $encodedcommand = [Convert]::ToBase64String([Text.Encoding]::Unicode.GetBytes($sqlcmdstring))
PS C:\Users\ctrachte> $encodedcommand
cwBxAGwAYwBtAGQAIAAtAFMAIABsAG8AYwBhAGwAaABvAHMAdAAgAC0AaQAgAC4AXABzAGUAbABlAGMAdABkAGEAdABhAC4AcwBxAGwAIAAtAEUAIAAtAGgAIAAtADEAIAAtAHMAIAAiACwAIgAgAC0AVwAgAHwAIABUAGUAZQAtAE8AYgBqAGUAYwB0ACAALQBGAGkAbABlAFAAYQB0AGgAIAAuAFwAdABlAHMAdABvAHUAdABwAHUAdAA=
PS C:\Users\ctrachte>
OK, the two base 64 strings are the same, so we are good.
Command Execution from os.system() call:
>>> import os
>>> INVOKEPOWERSHELL = 'Powershell -EncodedCommand {0:s}'
>>> INVOKEPOWERSHELL = 'Powershell -EncodedCommand {0:s}'
>>> os.system(INVOKEPOWERSHELL.format(encodedcommandx))
Changed database context to 'test'.
000001,VOLUME,11.0
000002,YEAR,1999.0
Changed database context to 'test'.
000001,VOLUME,11.0
000002,YEAR,1999.0
(2 rows affected)
0
>>>
0
>>>
And, thanks to Powershell's version of UNIX-like system's tee command, we have a faux csv file as well as output to the command line.
Stackoverflow gave me much of what I needed to know for this:
Links:
Powershell's encoded command:
https://blogs.technet.microsoft.com/heyscriptingguy/2015/10/27/powertip-encode-string-and-execute-with-powershell/
sqlcmd's output to faux csv:
https://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd
The UTF encoding stuff just took some trial and error and fiddling.
Thanks for stopping by.
