pymssql

https://pymssql.readthedocs.io
Admin
pymssql is a simple database interface for Python that builds on top of FreeTDS to provide a Python...
Tokens:69,771
Snippets:522
Trust Score:9.6
Update:6 days ago
Show doc for...
Tokens:
Raw
### pyformat paramstyle examples

Source: https://pymssql.readthedocs.io/en/latest/migrate_1_x_to_2_x.html

Shows the PEP 249 pyformat paramstyle examples for string and numeric substitution.

```python
%(name)s
```

```python
%(name)d
%(name)f
```

--------------------------------

### Install required Python packages for building

Source: https://pymssql.readthedocs.io/en/latest/building_and_developing.html

Upgrade pip and install the development requirements from dev/requirements-dev.txt.

```bash
pip install -U pip
pip install -r dev/requirements-dev.txt

```

--------------------------------

### Build pymssql with custom FreeTDS prefix

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Example of setting the PYMSSQL_FREETDS environment variable to specify the FreeTDS installation prefix before building.

```console
PYMSSQL_FREETDS=/tmp/freetds python3 setup.py bdist_wheel
```

--------------------------------

### Install pymssql with pip

Source: https://pymssql.readthedocs.io/en/latest/_sources/intro.rst.txt

Upgrades pip and installs the pymssql package. This is the recommended installation method for most users.

```bash
pip install -U pip
pip install pymssql
```

--------------------------------

### Install build dependencies on Alpine Linux

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Command to install all necessary packages for building pymssql on Alpine Linux using apk.

```console
apk add git gcc musl-dev krb5-dev openssl-dev freetds-dev
```

--------------------------------

### Run tests with AllTestsWillRun section

Source: https://pymssql.readthedocs.io/en/latest/building_and_developing.html

Example of running tests with the AllTestsWillRun section.

```bash
py.test --pymssql-section=AllTestsWillRun

```

--------------------------------

### Install testing dependencies

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Install pytest and SQLAlchemy using easy_install, required for running the pymssql test suite.

```bash
easy_install pytest SQLAlchemy
```

--------------------------------

### Install required Python packages for building pymssql

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Commands to upgrade pip and install development requirements from dev/requirements-dev.txt.

```console
pip install -U pip
pip install -r dev/requirements-dev.txt
```

--------------------------------

### Connect with pymssql

Source: https://pymssql.readthedocs.io/en/latest/_sources/faq.rst.txt

Python example showing how to connect to SQL Server using pymssql and execute a query. The output displays the SQL Server version.

```python
>>> import pymssql
>>> conn = pymssql.connect(
...     server="sqlserverhost",
...     port=1433,
...     user="user",
...     password="password",
...     database="tempdb")
>>> conn
<pymssql.Connection object at 0x10107a3f8>
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT @@VERSION")
>>> print(cursor.fetchone()[0])
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
  Feb 10 2012 19:39:15
  Copyright (c) Microsoft Corporation
  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
```

--------------------------------

### Sample output from custom message handler

Source: https://pymssql.readthedocs.io/en/latest/_mssql_examples.html

Example output that the custom message handler might print to standard output.

```text
my_msg_handler: msgstate = x, severity = y, procname = '', line = 1, msgtext = 'Changed database context to 'mydatabase'.'

```

--------------------------------

### Print pymssql version information

Source: https://pymssql.readthedocs.io/en/latest/_sources/ref/pymssql.rst.txt

Command-line example to output version information about pymssql, FreeTDS, Python, and OS. Useful when reporting bugs.

```bash
/<path to your python>/python -c "import pymssql; print(pymssql.version_info())"
```

--------------------------------

### Install build dependencies on Debian-based distributions

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Commands to update apt and install required packages for building pymssql on Debian-based systems.

```console
apt update
apt install apt-utils git build-essential freetds-dev libssl-dev libkrb5-dev
```

--------------------------------

### Build pymssql from source with pip

Source: https://pymssql.readthedocs.io/en/latest/_sources/intro.rst.txt

Installs pymssql by building it against locally installed libraries. Requires the necessary build dependencies (see building_and_developing).

```bash
pip install --no-binary=pymssql pymssql
```

--------------------------------

### pyformat string substitution example

Source: https://pymssql.readthedocs.io/en/latest/_sources/migrate_1_x_to_2_x.rst.txt

PEP 249 shows an example of pyformat string substitution using %(name)s.

```python
%(name)s
```

--------------------------------

### Run tests with a specific tests.cfg section

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Run tests using a specific section of tests.cfg by passing the --pymssql-section option. The example uses the 'AllTestsWillRun' section.

```bash
py.test --pymssql-section=<secname>
```

```bash
py.test --pymssql-section=AllTestsWillRun
```

--------------------------------

### Run the pymssql Docker container with a custom command

Source: https://pymssql.readthedocs.io/en/latest/_sources/docker.rst.txt

Overrides the default IPython shell by specifying a command, here bin/bash, to start a bash shell instead.

```bash
docker run -it --rm pymssql/pymssql bin/bash
```

--------------------------------

### Install wait callback for cooperative multitasking with gevent

Source: https://pymssql.readthedocs.io/en/latest/_sources/pymssql_examples.rst.txt

Use pymssql.set_wait_callback to install a callback that yields to other greenlets. The example uses gevent.socket.wait_read. This is useful for high concurrency with Gunicorn gevent workers.

```python
import gevent.socket
import pymssql

def wait_callback(read_fileno):
    gevent.socket.wait_read(read_fileno)

pymssql.set_wait_callback(wait_callback)
```

--------------------------------

### Set up tests.cfg

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Copy the template tests.cfg.tpl to tests.cfg and edit it with your database connection information. The commands show copying the template and opening it in an editor.

```bash
cp tests/tests.cfg.tpl tests/tests.cfg
vim|emacs|notepad tests/tests.cfg
```

--------------------------------

### Build pymssql wheel with setup.py

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Command to build a wheel using setup.py bdist_wheel.

```console
python3 setup.py bdist_wheel
```

--------------------------------

### Custom message handler with _mssql

Source: https://pymssql.readthedocs.io/en/latest/_sources/_mssql_examples.rst.txt

Shows how to install a custom message handler using set_msghandler on a _mssql connection. The handler is called with informative messages from the server. The example prints a string to stdout. This feature is new in version 2.1.1.

```python
from pymssql import _mssql

def my_msg_handler(msgstate, severity, srvname, procname, line, msgtext):
    """
    Our custom handler -- It simply prints a string to stdout assembled from
    the pieces of information sent by the server.
    """
    print("my_msg_handler: msgstate = %d, severity = %d, procname = '%s', "
          "line = %d, msgtext = '%s'" % (msgstate, severity, procname,
                                         line, msgtext))

cnx = _mssql.connect(server='SQL01', user='user', password='password')
try:
    cnx.set_msghandler(my_msg_handler)  # Install our custom handler
    cnx.execute_non_query("USE mydatabase")  # It gets called at this point
finally:
    cnx.close()
```

--------------------------------

### Fetch rows from a stored procedure with _mssql

Source: https://pymssql.readthedocs.io/en/latest/_sources/_mssql_examples.rst.txt

Shows how to execute a stored procedure that returns multiple result sets and iterate over the connection to get each result set. The example uses sp_spaceused which returns 2 result sets when called without arguments.

```python
# how to fetch rows from a stored procedure
conn.execute_query('sp_spaceused')   # sp_spaceused without arguments returns 2 result sets
res1 = [ row for row in conn ]       # 1st result
res2 = [ row for row in conn ]       # 2nd result
```

--------------------------------

### Example Docker session with pymssql

Source: https://pymssql.readthedocs.io/en/latest/_sources/docker.rst.txt

Shows a sample session pulling and running the image, then checking versions of pymssql, SQLAlchemy, and pandas inside IPython. The output shows the actual versions observed.

```bash
$ docker pull pymssql/pymssql
...
$ docker run -it --rm pymssql/pymssql
Python 2.7.6 (default, Mar 22 2014, 22:59:56)
Type "copyright", "credits" or "license" for more information.

IPython 2.1.0 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]: import pymssql; pymssql.__version__
Out[1]: u'2.1.1'

In [2]: import sqlalchemy; sqlalchemy.__version__
Out[2]: '0.9.7'

In [3]: import pandas; pandas.__version__
Out[3]: '0.14.1'
```

--------------------------------

### Build package in development mode

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Build the package in development mode using setup.py develop, which is required before running tests.

```bash
python setup.py develop
```

--------------------------------

### Install FreeTDS on macOS with Homebrew

Source: https://pymssql.readthedocs.io/en/latest/_sources/freetds.rst.txt

Installs FreeTDS on macOS using Homebrew. This is the recommended way to install FreeTDS on macOS for use with pymssql.

```bash
brew install freetds
```

--------------------------------

### Show dev/build.py help

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Run this command to see supported options for the dev/build.py script, which downloads and builds a specific FreeTDS version and builds the pymssql wheel.

```bash
python dev/build.py --help
```

--------------------------------

### FreeTDS global configuration example (freeTDS.conf)

Source: https://pymssql.readthedocs.io/en/latest/freetds.html

Minimal FreeTDS configuration to connect to SQL Server using just the hostname in pymssql.connect() and _mssql.connect(). Place it in /etc/freetds.conf or C:\freetds.conf (system-wide) or in $HOME/.freetds.conf (Linux) or %APPDATA%\.freetds.conf (Windows).

```conf
[global]
    port = 1433
    tds version = 7.0

```

--------------------------------

### Connect and create table with _mssql

Source: https://pymssql.readthedocs.io/en/latest/_sources/_mssql_examples.rst.txt

Shows how to connect to a SQL Server database using _mssql.connect and execute non-query statements to create a table and insert rows. Note that the connect method parameters differ from the pymssql module.

```python
from pymssql import _mssql
conn = _mssql.connect(server='SQL01', user='user', password='password', \
    database='mydatabase')
conn.execute_non_query('CREATE TABLE persons(id INT, name VARCHAR(100))')
conn.execute_non_query("INSERT INTO persons VALUES(1, 'John Doe')")
conn.execute_non_query("INSERT INTO persons VALUES(2, 'Jane Doe')")
```

--------------------------------

### Select specific tests to run

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Run specific test files, modules, classes, or individual test cases by specifying paths and node IDs.

```bash
py.test tests/test_types.py
```

```bash
py.test tests/test_types.py tests/test_sprocs.py
```

```bash
py.test tests/test_types.py::TestTypes
```

```bash
py.test tests/test_types.py::TestTypes::test_image
```

--------------------------------

### pyformat non-string substitution examples

Source: https://pymssql.readthedocs.io/en/latest/_sources/migrate_1_x_to_2_x.rst.txt

Examples of pyformat substitution for other types, such as %(name)d and %(name)f, which are not clearly supported by PEP 249.

```python
%(name)d
%(name)f
```

--------------------------------

### Connecting using Windows Authentication

Source: https://pymssql.readthedocs.io/en/latest/_sources/pymssql_examples.rst.txt

Shows how to connect using Windows Authentication by combining the database hostname and instance name, and the Active Directory/Windows Domain name and username. Uses raw strings (r'...') for strings containing backslashes.

```python
conn = pymssql.connect(
    host=r'dbhostname\myinstance',
    user=r'companydomain\username',
    password=PASSWORD,
    database='DatabaseOfInterest'
)
```

--------------------------------

### Install pymssql with conda

Source: https://pymssql.readthedocs.io/en/latest/_sources/intro.rst.txt

Installs pymssql via Anaconda/Miniconda. This method avoids editing config files outside the user's home directory on some Unix-like systems and requires no additional compilation or configuration.

```bash
conda install pymssql
```

--------------------------------

### connect()

Source: https://pymssql.readthedocs.io/en/latest/_sources/ref/pymssql.rst.txt

Constructor for creating a connection to the database. Returns a Connection object. Use keyword arguments in most cases.

```APIDOC
## connect()

### Description
Constructor for creating a connection to the database. Returns a Connection object. Note that in most cases you will want to use keyword arguments, instead of positional arguments.

### Signature
connect(server='.', user=None, password=None, database='', timeout=0, login_timeout=60, charset='UTF-8', as_dict=False, host='', appname=None, port='1433', conn_properties=None, autocommit=False, tds_version=None, use_datetime2=False, arraysize=1)

### Parameters
- **server** (str) - Optional - Database host. Default: '.'
- **user** (str) - Optional - Database user to connect as.
- **password** (str) - Optional - User's password.
- **database** (str) - Optional - The database to initialize the connection with. By default SQL Server selects the database which is set as default for specific user.
- **timeout** (int) - Optional - Query timeout in seconds, default 0 (no timeout).
- **login_timeout** (int) - Optional - Timeout for connection and login in seconds, default 60.
- **charset** (str) - Optional - Character set with which to connect to the database.
- **as_dict** (bool) - Optional - Whether rows should be returned as dictionaries instead of tuples. You can access columns by 0-based index or by name.
- **host** (str) - Optional - Database host and instance you want to connect to. Valid examples include: r'.\SQLEXPRESS', r'(local)\SQLEXPRESS', 'SQLHOST', 'SQLHOST,1433', 'SQLHOST:1433', 'SQLHOST,5000', 'SQLHOST:5000'. '.' (the local host) is assumed if host is not provided.
- **appname** (str) - Optional - Application name.
- **port** (str) - Optional - Port number, default '1433'.
- **conn_properties** - Optional - Connection properties.
- **autocommit** (bool) - Optional - Autocommit mode.
- **tds_version** - Optional - TDS version.
- **use_datetime2** (bool) - Optional - Use datetime2.
- **arraysize** (int) - Optional - Array size, default 1.
- **encryption** (str) - Optional - Specify if encryption is desired. Supported for Microsoft servers. Possible values: 'off', 'request', 'require'. Default: 'request' for tds version > 7.1, otherwise 'off'. (Added in version 2.2.8)
- **read_only** (bool) - Optional - Tell server we only intent to do read-only queries. Supported from MSSQL 2012.

### Returns
Connection object.

```

--------------------------------

### Get an output parameter from a stored procedure with _mssql

Source: https://pymssql.readthedocs.io/en/latest/_sources/_mssql_examples.rst.txt

Shows how to get an output parameter from a stored procedure by declaring a variable, executing the procedure with the OUT parameter, and selecting the variable. Uses execute_scalar to retrieve the result.

```python
# how to get an output parameter from a stored procedure
sqlcmd = """
DECLARE @res INT
EXEC usp_mystoredproc @res OUT
SELECT @res
"""
res = conn.execute_scalar(sqlcmd)
```

--------------------------------

### Get multiple output parameters from a stored procedure with _mssql

Source: https://pymssql.readthedocs.io/en/latest/_sources/_mssql_examples.rst.txt

Shows how to get multiple output parameters from a stored procedure by declaring variables, executing the procedure with OUT parameters, and selecting them. Uses execute_row with a tuple of parameters.

```python
# how to get more output parameters from a stored procedure
sqlcmd = """
DECLARE @res1 INT, @res2 TEXT, @res3 DATETIME
EXEC usp_getEmpData %d, %s, @res1 OUT, @res2 OUT, @res3 OUT
SELECT @res1, @res2, @res3
"""
res = conn.execute_row(sqlcmd, (13, 'John Doe'))
```

--------------------------------

### _mssql.connect()

Source: https://pymssql.readthedocs.io/en/latest/ref/_mssql.html

Creates an instance of the MSSQLConnection class. Accepts various parameters to configure the database connection.

```APIDOC
## _mssql.connect()

### Description
Creates a new MSSQLConnection instance representing a connection to an MS SQL database.

### Method
connect

### Parameters
- **server** (_str_) - Required - Database server and instance to connect to. Valid examples include: `r'.\SQLEXPRESS'`, `'SQLHOST'`, `'SQLHOST,1433'`, `'SQLHOST:1433'`, etc.
- **encryption** (_str_) - Optional - Specify if encryption is desired. Possible values: `'off'`, `'request'`, `'require'`. Default: `'request'` for tds version > 7.1, otherwise `'off'`.
- **user** (_str_) - Optional - Database user to connect as.
- **password** (_str_) - Optional - User's password.
- **charset** (_str_) - Optional - Character set name for the connection.
- **database** (_str_) - Optional - The database to initially connect to.
- **read_only** (_bool_) - Optional - Tell server we only intend to do read-only queries. Supported from MSSQL 2012.
- **appname** (_str_) - Optional - Application name to use for the connection.
- **port** (_str_) - Optional - TCP port to use to connect to the server.
- **tds_version** (_str_) - Optional - TDS protocol version to ask for. Default: `None`.
- **use_datetime2** (_bool_) - Optional - Whether to use datetime.datetime conversion compatible with DATETIME2. Default: False.
- **conn_properties** - Optional - SQL queries to send to the server upon connection establishment. Can be a string or iterable of strings. Default value is a set of SET statements.

### Request Example
```python
conn = _mssql.connect(server='SQLHOST', user='user', password='password', database='mydb')
```

### Response
#### Success Response
Returns an MSSQLConnection instance.

#### Response Example
```python
<MSSQLConnection object>
```
```

--------------------------------

### Close _mssql connection

Source: https://pymssql.readthedocs.io/en/latest/_sources/_mssql_examples.rst.txt

Closes the _mssql connection. This is a simple example showing the close method.

```python
conn.close()
```

--------------------------------

### get_max_connections()

Source: https://pymssql.readthedocs.io/en/latest/_sources/ref/_mssql.rst.txt

Gets current maximum number of simultaneous connections allowed to be open at any given time.

```APIDOC
## get_max_connections()

### Description
Gets current maximum number of simultaneous connections allowed to be open at any given time.

### Method
Function

### Parameters
None

### Returns
Integer - The current maximum number of simultaneous connections.

### Example
```python
import _mssql
max_conn = _mssql.get_max_connections()
```
```

--------------------------------

### Create Python virtual environment for building pymssql

Source: https://pymssql.readthedocs.io/en/latest/_sources/building_and_developing.rst.txt

Command to create a Python virtual environment, recommended for building pymssql.

```console
python3 -m venv <path_to_pve>
```

--------------------------------

### Connect to SQL Server with pymssql

Source: https://pymssql.readthedocs.io/en/latest/faq.html

Connect to SQL Server using pymssql with explicit server, port, user, password, and database. The example shows a successful connection and query execution.

```python
>>> import pymssql
>>> conn = pymssql.connect(
...     server="sqlserverhost",
...     port=1433,
...     user="user",
...     password="password",
...     database="tempdb")
>>> conn
<pymssql.Connection object at 0x10107a3f8>
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT @@VERSION")
>>> print(cursor.fetchone()[0])
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
  Feb 10 2012 19:39:15
  Copyright (c) Microsoft Corporation
  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

```

--------------------------------

### Execute scalar query to get count

Source: https://pymssql.readthedocs.io/en/latest/ref/_mssql.html

Shows how to use execute_scalar to retrieve a single value from a query, such as the count of rows in a table.

```python
count = conn.execute_scalar("SELECT COUNT(*) FROM employees")

```
Tokens:
Raw