Python CLI for Microsoft SQL.
Project description
MS-SQL Command Line Interface (mssqlcli)
MS-SQL CLI is a unix command line tool for accessing and running arbitrary queries against an Microsoft SQL database.
Binary Dependencies
FreeTDS - Binary Library providing access to MSSQL and Sybase DBs.
Installation
Install the FreeTDS Library
Debian/Ubuntu: sudo apt-get install freetds-dev
Mac OSX: brew install freetds
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:
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:
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:
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
~ [ 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
mssqlcli query {path to query}.sql
Run query and return results in CSV format
mssqlcli -o csv query {path to query}.sql
Redirect csv to File
mssqlcli -o csv query {path to query}.sql > results.csv
Run query and return results as a json blob
mssqlcli -o json query {path to query}.sql
Send a template query to the server in file {path to query}.sql.
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.
SELECT * FROM bogus_db.users WHERE last_name = '{{ last_name }}';
Contributing
MS-SQL CLI is an open-source project, hosted on GitHub. All Contributors are welcome, a current list of open issues is available here.
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:
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.
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.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]
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.