How do I send output from a database query executed in mssql-cli (the Microsoft SQL command line interface application) on a Linux machine to a text file on that machine in CSV format?
The query is to a database on another server. I am already able to connect to that server and get small query result sets back to my screen.
I want to get back much larger query result sets in CSV formatted files that are saved on the same Linux machine on which I've installed mssql-cli.
mssql-cli uses a config file to control output formatting (and a lot of other things).
To get csv output just create a file at ~/.config/mssqlcli/config containing the following:
table_format = tsv
The other options recognized are documented in the usage guide available in the main github project here: mssql-cli config options
Microsoft's mssql-cli does not have any such option.
mssql-cli --help output:
Usage: main.py [OPTIONS]
Options:
-S, --server TEXT SQL Server instance name or address.
-U, --username TEXT Username to connect to the database.
-W, --password Force password prompt.
-E, --integrated Use integrated authentication on windows.
-v, --version Version of mssql-cli.
-d, --database TEXT database name to connect to.
--mssqlclirc TEXT Location of mssqlclirc config file.
--row-limit INTEGER Set threshold for row limit prompt. Use 0 to disable
prompt.
--less-chatty Skip intro on startup and goodbye on exit.
--auto-vertical-output Automatically switch to vertical output mode if the
result is wider than the terminal width.
--help Show this message and exit.
There is another, unrelated project called mssqlcli which does have that option.
~ [ mssqlcli --help Usage: mssqlcli [OPTIONS] COMMAND [ARGS]...
Options:
--version Show the version and exit.
-c, --config-file PATH Override default config file location
(default: ~/.config/pymssql.yml).
-o, --output [json|csv|pretty]
--help Show this message and exit.
Commands:
query Run a query against an MS-SQL Database.
template_query
~ [ mssqlcli query --help
Usage: mssqlcli query [OPTIONS] QUERY
Options:
--help Show this message and exit.
~ [ mssqlcli template_query --help
Usage: mssqlcli template_query [OPTIONS] QUERY
Options:
-v, --variable TEXT Variable for substitution in template. ex:"-v
first_name:russell" to replace {{ first_name }}
--help Show this message and exit.
As you can see, with that tool, you can do something like mssqlcli -o csv query 'SELECT * FROM whatever' > big_query.csv . However, that is not the tool you asked about: Microsoft's mssql-cli.
To complement @jvd10's answer, here are the possible formats at time of writing. I wouldn't expect them to change often.
# Table format. Possible values: psql, plain, simple, grid, fancy_grid, pipe,
# ascii, double, github, orgtbl, rst, mediawiki, html, latex, latex_booktabs,
# textile, moinmoin, jira, vertical, tsv, csv.
# Recommended: psql, fancy_grid and grid.
table_format = psql
mssql-cli config file uses table_format with arg --output, I expected output_format
TLDR: If you want JSON output then skip mssql-cli and go for mssqlcli. You need a Windows install of Python3.9 and then run PS C:\> python -m pip install mssqlcli pyyaml==5.4.1. If you want nice a nice interactive SQL console, then try mssqlcli instead. You will have to decide for ONE of them, since they seem to break each other. mssqlcli seems to be abandoned and buggy beta. So use with care.
I'm expanding a little on the work of https://stackoverflow.com/a/55656483/4247268 above.
And also I'm deviating completely from the "How to do this on Linux?" question, since I'm doing all of this on python on Windows 10. -- Feel free to comment if you got it to work like this on Linux.
So there are two projects with very similar names: mssql-cli and mssqlcli. One has a hyphen, the other one has no hyphen.
python3 -m pip install pyyaml==5.4.1 otherwise with the current v6.0 I got an error: TypeError: load() missing 1 required positional argument: 'Loader' in Google ColabI had some trouble getting them to work on my Win10 machine in 2022. But eventually I managed to get both to work. But NOT BOTH AT ONCE. Setup was fiddly and unpleasant.
For the Microsoft's mssql-cli this here in PowerShell worked:
PS C:\> python --version
Python 3.9.12
PS C:\>
PS C:\>
PS C:\> python -m pip install mssql-cli
This worked for me. Afterwards you will have this new command:
PS C:\> Get-Command mssql-cli.bat | Format-List
Name : mssql-cli.bat
CommandType : Application
Definition : C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssql-cli.bat
Extension : .bat
Path : C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssql-cli.bat
FileVersionInfo : File: C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssql-cli.bat
InternalName:
OriginalFilename:
FileVersion:
FileDescription:
Product:
ProductVersion:
Debug: False
Patched: False
PreRelease: False
PrivateBuild: False
SpecialBuild: False
Language:
Built-in usage help is decent:
PS C:\> mssql-cli.bat --help
usage: mssql-cli [-h] [-U] [-P] [-d] [-S] [-E] [-v] [--mssqlclirc] [--row-limit] [--less-chatty] [--auto-vertical-output] [-N] [-C] [-l] [-K] [-M] [-a] [-A] [-Q] [-i] [-o] [--enable-sqltoolsservice-logging] [--prompt]
Microsoft SQL Server CLI. Version 1.0.0
optional arguments:
-h, --help show this help message and exit
-U , --username Username to connect to the database
-P , --password If not supplied, defaults to value in environment variable MSSQL_CLI_PASSWORD.
-d , --database database name to connect to.
-S , --server SQL Server instance name or address.
-E, --integrated Use integrated authentication on windows.
-v, --version Version of mssql-cli.
--mssqlclirc Location of mssqlclirc config file.
--row-limit Set threshold for row limit prompt. Use 0 to disable prompt.
--less-chatty Skip intro on startup and goodbye on exit.
--auto-vertical-output
Automatically switch to vertical output mode if the result is wider than the terminal width.
-N, --encrypt SQL Server uses SSL encryption for all data if the server has a certificate installed.
-C, --trust-server-certificate
The channel will be encrypted while bypassing walking the certificate chain to validate trust.
-l , --connect-timeout
Time in seconds to wait for a connection to the server before terminating request.
-K , --application-intent
Declares the application workload type when connecting to a database in a SQL Server Availability Group.
-M, --multi-subnet-failover
If application is connecting to AlwaysOn AG on different subnets, setting this provides faster detection and connection to currently active server.
-a , --packet-size Size in bytes of the network packets used to communicate with SQL Server.
-A, --dac-connection Connect to SQL Server using the dedicated administrator connection.
-Q , --query Executes a query outputting the results to stdout and exits.
-i , --input_file Specifies the file that contains a batch of SQL statements for processing.
-o , --output_file Specifies the file that receives output from a query.
--enable-sqltoolsservice-logging
Enables diagnostic logging for the SqlToolsService.
--prompt Prompt format (Default: \d>
This has both an interactive and a non-interactive mode.
In non-interactive mode you just fire off the contents of a script via the --input_file parameter like so:
PS C:\> mssql-cli.bat --server db.example.com --username exampleuser --password examplepassword --database exampledatabase --input_file exampleinputfile.sql
+---------+-----------+
| count | fruitname |
|---------+-----------|
| 30 | apples |
+---------+-----------+
(1 rows affected)
The output is for human consumption only. Sadly.
I don't see an advantage of this of mssql-cli.bat command over the usual "Invoke-Sqlcmd" commandlet -- BTW: Invoke-Sqlcmd also exists for Linux: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-manage-powershell-core
However: there is also INTERACTIVE mode. And this is really nice. You get very nice colors, command-line history, animated dropdowns as you type and tab-completion. All inside the PowerShell window.
They used to have an animated GIF to demo this directly on the GitHub page (https://github.com/dbcli/mssql-cli) -- but currently (2022-04-14) that image link is broken.
However this site here also has some animated images to demo the intellisense-like autocompletion feature: https://mssqlquery.com/using-python-mssql-cli-in-sql-server (archived here)
Unfortunately it does not work inside Mobaxterm's "xterm" console. -- Then it asks you to install it directly via the python inside mobaxterm. And I didn't manage to do that. (Non-interactive mode however works just fine inside Mobaxterm's window.)
Since I really wanted some machine-readable output of queries, Microsoft's mssql-cli didn't work for me.
So I tried mssqcli next. Nearly the same name as "mssql-cli". Just the hyphen is missing.
I installed it like this:
PS C:\> python -m pip install mssqlcli pyyaml==5.4.1
And afterwards you will have this new command:
PS C:\> Get-Command mssqlcli | Format-List
Name : mssqlcli.exe
CommandType : Application
Definition : C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssqlcli.exe
Extension : .exe
Path : C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssqlcli.exe
FileVersionInfo : File: C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssqlcli.exe
InternalName:
OriginalFilename:
FileVersion:
FileDescription:
Product:
ProductVersion:
Debug: False
Patched: False
PreRelease: False
PrivateBuild: False
SpecialBuild: False
Language:
Usage help is very bare bones:
PS C:\> mssqlcli.exe --help
Usage: mssqlcli [OPTIONS] COMMAND [ARGS]...
Placeholder Function for click group.
Options:
--version Show the version and exit.
-c, --config-file PATH Override default config file location
(default: ~/.config/pymssql.yml).
-o, --output [csv|json|pretty]
--help Show this message and exit.
Commands:
query Run a query against an MS-SQL Database.
template-query Run Jinga2 templated query on an MS-SQL Database
Sidenote: must choose one I'm not sure why, but installing mssqlcli breaks mssql-cli. Error:
PS C:\> mssql-cli.bat --server db.example.com --username exampleuser --password examplepassword --database exampledatabase --input_file exampleinputfile.sql
Traceback (most recent call last):
File "C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\runpy.py", line 197, in _run_module_as_main
return _run_code(code, main_globals, None,
File "C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\runpy.py", line 87, in _run_code
exec(code, run_globals)
File "C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\main.py", line 11, in <module>
from mssqlcli.config import config_location
ImportError: cannot import name 'config_location' from 'mssqlcli.config' (C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\config.py)
If you want to unbreak it again, then reinstall like this:
PS C:\> python -m pip uninstall -y mssqlcli mssql-cli pyyaml
and then
PS C:\> python -m pip uninstall -y mssql-cli
So I didn't manage to have BOTH installed and working at the same time.
But nevermind, since I only want the json-output-capable of the two anyway: mssql-cli
End Sidenote
So let's move on to actually running the thing.
It seems to require db-connection details in yaml file like this:
PS C:\> cat .\my-local-mssqlcli.yml
---
username: "exampleuser"
password: "examplepassword"
database: "exampledatabase"
server: "db.example.com"
(!) WARNING: Update 2022-04-26: the "database" line seems to be ignored. It just defaults to SOME database on that server. I have no idea how to actually select the database.
And it seems to require the query in a file like this:
PS C:\> cat examplequery.sql
SELECT * from FRUITTABLE
And then you run it like this:
PS C:\> mssqlcli.exe --config-file my-local-mssqlcli.yml query examplequery.sql
C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\config.py:49: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details.
self.object = yaml.load(f.read())
+-------+-----------+
| count | fruitname |
+-------+-----------+
| 30 | apples |
+-------+-----------+
Yes, you will always get this warning about yaml.load().
There are three output options:
--output [csv|json|pretty]
"pretty" is the default:
PS C:\> mssqlcli.exe --config-file my-local-mssqlcli.yml --output pretty query examplequery.sql
C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\config.py:49: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details.
self.object = yaml.load(f.read())
+-------+-----------+
| count | fruitname |
+-------+-----------+
| 30 | apples |
+-------+-----------+
"csv" looks like this:
PS C:\> mssqlcli.exe --config-file my-local-mssqlcli.yml --output csv query examplequery.sql
C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\config.py:49: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details.
self.object = yaml.load(f.read())
count,fruitname
30,apples
But what I really wanted was "json":
PS C:\> mssqlcli.exe --config-file my-local-mssqlcli.yml --output json query examplequery.sql
C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\config.py:49: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details.
self.object = yaml.load(f.read())
{
"results": [
{
"count": 30,
"fruitname": "apples"
}
]
}
Notice that "count" is correctly displayed as a number type and "fruitname" is displayed as a quoted string type.
