mssqlcli 1.0.4

Python CLI for Microsoft SQL.

# MS-SQL Command Line Interface (mssqlcli)

[](https://pypi.python.org/pypi/mssqlcli)
[](https://travis-ci.org/rtrox/mssqlcli)
[](https://coveralls.io/github/rtrox/mssqlcli?branch=master)

MS-SQL CLI is a unix command line tool for accessing and running arbitrary
queries against an Microsoft SQL database.


## Binary Dependencies

- [FreeTDS][1] - Binary Library providing access to MSSQL and Sybase DBs.



## Installation

1. Install the FreeTDS Library
- Debian/Ubuntu: `sudo apt-get install freetds-dev`
- Mac OSX: `brew install freetds`
2. Install mssqlcli
- `pip install mssqlcli`
*OR*
- Clone and `python setup.py install`

### DB_VERSION_80 Errors during Installation (OS X)

On Mac OS X, there is a bug with `pymssql` v2.1.2 which may cause this error:
```bash
error: use of undeclared identifier 'DBVERSION_80'
```
If you see this error, simply install the latest version of `pymssql` from git prior to installation of mssqlcli:
```bash
pip install -e git+https://github.com/pymssql/pymssql.git#egg=pymssql-2.1.2
```


## Configuration

Configuration is handled with a single YAML configuration file, located by
default at `~/.config/mssqlcli.yml`.

Example Config:
```yaml
keyring_app_name: another_app # Optional, defaults to mssqlcli
username: USE_KEYRING("global:LDAPUser")
password: USE_KEYRING("global:LDAP")
# OR
# username: my_plaintext_username
# password: my_plaintext_password
server: MY_MSSQL.example.com

# The below is optional, and should be used if
# Windows Auth will be used instead of MSSQL Auth.
windows_authentication: true
domain: MY_DOMAIN
```


## Usage

```bash
~ [ 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.
```


## Examples
The general usage model is to store your SQL queries in flat files, and
access them with the CLI client. Personally, I store my queries in
~/sql_queries.


Run Query and return results as a json blob
```bash
mssqlcli query {path to query}.sql
```

Run query and return results in CSV format
```bash
mssqlcli -o csv query {path to query}.sql
```

Redirect csv to File
```bash
mssqlcli -o csv query {path to query}.sql > results.csv
```

Run query and return results as a json blob
```bash
mssqlcli -o json query {path to query}.sql
```

Send a template query to the server in file {path to query}.sql.
```bash
mssqlcli template_query -v 'last_name: Ugur' {path to query}.sql
```

### Template Queries
An example of a templated SQL query is also given below. These are
the kind of queries that can be used alongside template_query command.
```bash
SELECT * FROM bogus_db.users WHERE last_name = '{{ last_name }}';
```


[1]: http://www.freetds.org/
[2]: http://pymssql.org/en/stable/
[3]: http://click.pocoo.org/5/
[4]: https://github.com/pymssql/pymssql/issues/432

# MS-SQL Command Line Interface (mssqlcli)

[](https://pypi.python.org/pypi/mssqlcli)
[](https://travis-ci.org/rtrox/mssqlcli)
[](https://coveralls.io/github/rtrox/mssqlcli?branch=master)

MS-SQL CLI is a unix command line tool for accessing and running arbitrary
queries against an Microsoft SQL database.


## Binary Dependencies

- [FreeTDS][1] - Binary Library providing access to MSSQL and Sybase DBs.



## Installation

1. Install the FreeTDS Library
- Debian/Ubuntu: `sudo apt-get install freetds-dev`
- Mac OSX: `brew install freetds`
2. Install mssqlcli
- `pip install mssqlcli`
*OR*
- Clone and `python setup.py install`

### DB_VERSION_80 Errors during Installation (OS X)

On Mac OS X, there is a bug with `pymssql` v2.1.2 which may cause this error:
```bash
error: use of undeclared identifier 'DBVERSION_80'
```
If you see this error, simply install the latest version of `pymssql` from git prior to installation of mssqlcli:
```bash
pip install -e git+https://github.com/pymssql/pymssql.git#egg=pymssql-2.1.2
```


## Configuration

Configuration is handled with a single YAML configuration file, located by
default at `~/.config/mssqlcli.yml`.

Example Config:
```yaml
keyring_app_name: another_app # Optional, defaults to mssqlcli
username: USE_KEYRING("global:LDAPUser")
password: USE_KEYRING("global:LDAP")
# OR
# username: my_plaintext_username
# password: my_plaintext_password
server: MY_MSSQL.example.com

# The below is optional, and should be used if
# Windows Auth will be used instead of MSSQL Auth.
windows_authentication: true
domain: MY_DOMAIN
```


## Usage

```bash
~ [ 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.
```


## Examples
The general usage model is to store your SQL queries in flat files, and
access them with the CLI client. Personally, I store my queries in
~/sql_queries.


Run Query and return results as a json blob
```bash
mssqlcli query {path to query}.sql
```

Run query and return results in CSV format
```bash
mssqlcli -o csv query {path to query}.sql
```

Redirect csv to File
```bash
mssqlcli -o csv query {path to query}.sql > results.csv
```

Run query and return results as a json blob
```bash
mssqlcli -o json query {path to query}.sql
```

Send a template query to the server in file {path to query}.sql.
```bash
mssqlcli template_query -v 'last_name: Ugur' {path to query}.sql
```

### Template Queries
An example of a templated SQL query is also given below. These are
the kind of queries that can be used alongside template_query command.
```bash
SELECT * FROM bogus_db.users WHERE last_name = '{{ last_name }}';
```


[1]: http://www.freetds.org/
[2]: http://pymssql.org/en/stable/
[3]: http://click.pocoo.org/5/
[4]: https://github.com/pymssql/pymssql/issues/432

# Contributing

MS-SQL CLI is an open-source project, hosted [on GitHub][1]. All Contributors
are welcome, a current list of open issues is available [here][2].

## Development Environment

No additional dependencies are required for development, simply follow the
standard installation instructions from README.md. I recommend that you use
`python setup.py develop`, rather than `install` to allow quick code changes.

## Testing

To test your code prior to submission, simply:

```bash
python setup.py test
```

This will ensure that tox and virtualenv are installed, and then run the test
suite against the interpreters available locally.

## Acceptance Criteria

### Pull requests should be made against the `develop` branch.
This repository utilizes git-flow for the most part. as such, new PRs should be
made against the `develop` branch. Changes will be merged from `develop` into
master during releases.

### Pull requests should follow full pep8 guidelines (the above testing will verify this).

### Pull requests should have thought-out test cases for any new code.
Test Coverage is currently at 100%. Pull requests should not reduce this
coverage, and should contain thought out test cases for new code.

### Pull requests should contain only a single commit (please squash commits prior to submitting Pull Request)
Pull Requests will be merged via rebase, as we use gitchangelog to generate
changelogs. As such, The commit message in your pull request should describe
what is being changed, and should follow the gitchangelog commit message
specs (below).

### Pull request commit messages should follow GitChangeLog specifications
These specifications can be found [here][3].


As an example, if I was fixing documentation, I might create the following commit text:

```fix: doc: Fixed Installation Instructions to reference proper pip package.```


The above requirements are verified via Travis-CI and Coveralls for Python versions 2.7, 3.4, and 3.5.


[1]: https://github.com/rtrox/mssqlcli
[2]: https://github.com/rtrox/mssqlcli/issues
[3]: https://github.com/vaab/gitchangelog/blob/master/gitchangelog.rc.reference

# Contributing

MS-SQL CLI is an open-source project, hosted [on GitHub][1]. All Contributors
are welcome, a current list of open issues is available [here][2].

## Development Environment

No additional dependencies are required for development, simply follow the
standard installation instructions from README.md. I recommend that you use
`python setup.py develop`, rather than `install` to allow quick code changes.

## Testing

To test your code prior to submission, simply:

```bash
python setup.py test
```

This will ensure that tox and virtualenv are installed, and then run the test
suite against the interpreters available locally.

## Acceptance Criteria

### Pull requests should be made against the `develop` branch.
This repository utilizes git-flow for the most part. as such, new PRs should be
made against the `develop` branch. Changes will be merged from `develop` into
master during releases.

### Pull requests should follow full pep8 guidelines (the above testing will verify this).

### Pull requests should have thought-out test cases for any new code.
Test Coverage is currently at 100%. Pull requests should not reduce this
coverage, and should contain thought out test cases for new code.

### Pull requests should contain only a single commit (please squash commits prior to submitting Pull Request)
Pull Requests will be merged via rebase, as we use gitchangelog to generate
changelogs. As such, The commit message in your pull request should describe
what is being changed, and should follow the gitchangelog commit message
specs (below).

### Pull request commit messages should follow GitChangeLog specifications
These specifications can be found [here][3].


As an example, if I was fixing documentation, I might create the following commit text:

```fix: doc: Fixed Installation Instructions to reference proper pip package.```


The above requirements are verified via Travis-CI and Coveralls for Python versions 2.7, 3.4, and 3.5.


[1]: https://github.com/rtrox/mssqlcli
[2]: https://github.com/rtrox/mssqlcli/issues
[3]: https://github.com/vaab/gitchangelog/blob/master/gitchangelog.rc.reference

1.0.2 (2016-10-25)
------------------

New
~~~

- Add gitchangelog configuration, CONTRIBUTING.md. (#54) [Russell
Troxel]

- Adds a ``CONTRIBUTING.md`` File to provide guildelines around development and pull requests.
- Adds a .gitchangelog.rc file to start programmatically generating ``CHANGELOG.rst`` files for PyPI releases.

Other
~~~~~

- Stage Release v1.0.2. [Russell Troxel]

- Resolve mssql.drivers issue (#50) with setup.py. (#51) [Russell
Troxel]

* Resolve mssql.drivers issue (#50) with setup.py.

* Resolve mssql.drivers issue (#50) with setup.py.

* Resolve mssql.drivers issue (#50) with setup.py.

- Automate PanDoc conversion during registration. (#49) [Russell Troxel]

- Update README files: usage and examples (#48) [İlim Uğur]

* Update readme files for the new usage.

* Add template_query command and fix minor prior issues from manual editing.

* Add example usage of template_query and templated SQL query.

- Set default option to `pretty`, updated options & renamed both tests
(#46) [the-zebulan]

* Set default option to 'pretty'

* Updated options and renamed both tests, #34

- Implement template_query subcommand. (#45) [Russell Troxel]

- Add --version flag (#33) (#38) [Dmitriy]

Closes #33
* Add --version flag (#33)

* PEP8 fix

- Version bump to 1.0.1b (#37) [Russell Troxel]

- Version bump to 1.0.1a (#35) [Russell Troxel]

- Add shield.io badges (#31) [Thomas Nys]

- Stage Release 1.0.1 (#29) [Russell Troxel]

- Closes #12: Allow testing via setup.py test - Calls Tox. (#28)
[Russell Troxel]