### 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())"
```

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

### 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()
```

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

### 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'
```

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

### 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
```

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

### 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
```

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

### 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
```

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

### 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)
```

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

### 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
```

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

### 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

```

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

### Workaround: fetchall() before starting another query

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

Shows a workaround for the cursor limitation: use fetchall() to recover all results before beginning another query on the same connection.

```python
c1.execute('SELECT ...')
c1_list = c1.fetchall()

c2.execute('SELECT ...')
c2_list = c2.fetchall()

# use c1_list and c2_list here instead of fetching individually from
# c1 and c2
```

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

### 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
```

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

### 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'))
```

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

### 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)

```

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

### 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
```

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

### Sample output from custom message handler

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

Example output that would be printed to standard output when the custom message handler is invoked. The exact values of msgstate and severity are placeholders (x and y).

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

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

### 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)
```

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

### 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
```

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

### Test connection with tsql using server alias

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

Shows the tsql utility usage and an example connection using a server alias defined in freetds.conf. The ellipsis (...) indicates omitted output.

```bash
$ tsql
Usage:  tsql [-S <server> | -H <hostname> -p <port>] -U <username> [-P <password>] [-I <config file>] [-o <options>] [-t delim] [-r delim] [-D database]
(...)
$ tsql -S mydbserver -U user
```

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

### 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
```

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

### Connect using freetds.conf alias

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

Connection call using the alias defined in freetds.conf. Requires the alias to be configured as shown in the previous example.

```python
pymssql.connect("myalias", "username@xxx", "password", "db_name")
```

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

### Run the pymssql Docker container

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

Starts an interactive container from the pymssql/pymssql image. By default, an IPython shell is invoked. The --rm flag removes the container after exit.

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

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

### 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()
```

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

### MSSQLConnection.get_header()

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

Gets the Python DB-API compliant header information. Returns a list of 7-element tuples describing current result header.

```APIDOC
## MSSQLConnection.get_header()

### Description
This method is infrastructure and doesn't need to be called by your code. It gets the Python DB-API compliant header information. Returns a list of 7-element tuples describing current result header. Only name and DB-API compliant type is filled, rest of the data is None, as permitted by the specs.

### Method
Method call

### Returns
List of 7-element tuples describing the current result header.
```

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

### Use execute_scalar and execute_row

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

Shows examples of other query functions: execute_scalar for scalar results and execute_row for a single row. Note that '%' is not a special character in the query string.

```python
# examples of other query functions
numemployees = conn.execute_scalar("SELECT COUNT(*) FROM employees")
numemployees = conn.execute_scalar("SELECT COUNT(*) FROM employees WHERE name LIKE 'J%'")    # note that '%' is not a special character here
employeedata = conn.execute_row("SELECT * FROM employees WHERE id=%d", 13)

```

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

### pymssql 1.x returns str instances

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

In pymssql 1.x, query results are returned as str instances. This example shows fetching a string column and the resulting dictionary.

```python
>>> pymssql.__version__
'1.0.3'
>>> conn.as_dict = True
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT 'hello' AS str FROM foo")
>>> cursor.fetchall()
[{0: 'hello', 'str': 'hello'}]
```

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

### Exception handling with _mssql

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

Shows how to handle _mssql.MssqlDatabaseException when executing a non-query. The example catches the exception and checks the error number and severity to ignore a specific error (table already exists) and re-raises other errors. The connection is closed in a finally block.

```python
from pymssql import _mssql

conn = _mssql.connect(server='SQL01', user='user', password='password',
                      database='mydatabase')
try:
    conn.execute_non_query('CREATE TABLE t1(id INT, name VARCHAR(50))')
except _mssql.MssqlDatabaseException as e:
    if e.number == 2714 and e.severity == 16:
        # table already existed, so quieten the error
    else:
        raise # re-raise real error
finally:
    conn.close()
```

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

### Bulk copy data into a table

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

The fastest way to insert data to a SQL Server table. The example creates a table and uses conn.bulk_copy to insert a million rows. Note that bulk_copy does not verify column data types.

```python
conn = pymssql.connect(server, user, password, "tempdb")
cursor = conn.cursor()
cursor.execute("""
    CREATE TABLE example (
        col1 INT NOT NULL,
        col2 INT NOT NULL
    )
""")
cursor.close()

conn.bulk_copy("example", [(1, 2)] * 1000000)
conn.commit()
```

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

### Workaround: fetch all results before starting another query

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

A recommended workaround to avoid cursor conflicts: use fetchall() to retrieve all results from the first query before executing the second query. This ensures each cursor's results are available without interference.

```python
c1.execute('SELECT ...')
c1_list = c1.fetchall()

c2.execute('SELECT ...')
c2_list = c2.fetchall()

# use c1_list and c2_list here instead of fetching individually from
# c1 and c2

```

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

### Use execute_scalar and execute_row with _mssql

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

Shows examples of other query functions: execute_scalar for scalar results and execute_row for a single row. Note that '%' is not a special character in the query string.

```python
# examples of other query functions
numemployees = conn.execute_scalar("SELECT COUNT(*) FROM employees")
numemployees = conn.execute_scalar("SELECT COUNT(*) FROM employees WHERE name LIKE 'J%'")    # note that '%' is not a special character here
employeedata = conn.execute_row("SELECT * FROM employees WHERE id=%d", 13)
```

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

### 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
```

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

### Avoid ColumnsWithoutNamesError by aliasing columns

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

Supply a name for all columns to avoid the exception. The example aliases the MAX(x) column as [MAX(x)] and then fetches all rows, returning a list of dictionaries.

```python
>>> cursor.execute("SELECT MAX(x) AS [MAX(x)] FROM (VALUES (1), (2), (3)) AS foo(x)")
>>> cursor.fetchall()
[{'MAX(x)': 3}]

```

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

### 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
```

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

### 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
```

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

### Use context managers with connections and cursors

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

Python's with statement automatically closes connections and cursors. This is a pymssql extension to the DB-API. The example connects to tempdb and queries persons where salesrep matches.

```python
with pymssql.connect(server, user, password, "tempdb") as conn:
    with conn.cursor(as_dict=True) as cursor:
        cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
        for row in cursor:
            print("ID=%d, Name=%s" % (row['id'], row['name']))
```

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

### 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
```

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

### Execute a query and get first row with execute_row

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

Sends a query to the MS SQL Server and returns the first row of the result. Useful when only a single row is needed. An exception is raised on failure. Pending results are silently discarded.

```python
empinfo = conn.execute_row("SELECT * FROM employees WHERE empid=10")

```

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

### 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
```

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

### 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'
)
```

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

### 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.

```

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

### _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>
```
```

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

### 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>
```

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

### 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")

```

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

### pymssql 2.x does not support %(name)d

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

In pymssql 2.x, using %(name)d in a query raises a ProgrammingError. This example shows the error when executing such a query.

```python
>>> pymssql.__version__
u'2.0.1.2'
>>> pymssql.paramstyle
'pyformat'

>>> cursor.execute("select 'hello' where 1 = %(name)d", dict(name=1))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "pymssql.pyx", line 430, in pymssql.Cursor.execute (pymssql.c:5900)
    if not self._source._conn.nextresult():
pymssql.ProgrammingError: (102, "Incorrect syntax near '('.
DB-Lib error message 20018, severity 15:\n
General SQL Server error: Check messages from the SQL Server\n")
```

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

### pymssql.get_max_connections()

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

Gets the current maximum number of simultaneous database connections allowed to be open at any given time. This is a pymssql extension to the DB-API 2.0.

```APIDOC
## pymssql.get_max_connections()

### Description
Gets the current maximum number of simultaneous database connections allowed to be open at any given time. This is a pymssql extension to the DB-API 2.0.

### Method
Function

### Signature
`pymssql.get_max_connections()`

### Parameters
None

### Return Value
- **int** - The current maximum number of simultaneous connections.

### Example
```python
import pymssql
print(pymssql.get_max_connections())
```
```

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

### pymssql.connect() parameters

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

Documentation for the connection parameters accepted by the pymssql.connect() function.

```APIDOC
## pymssql.connect() parameters

### Description
Parameters for establishing a connection to SQL Server using pymssql.

### Parameters
#### Keyword Arguments
- **appname** (str) - Optional - Set the application name to use for the connection.
- **port** (str) - Optional - The TCP port to use to connect to the server.
- **conn_properties** (str or iterable of strings) - Optional - SQL queries to send to the server upon connection establishment. Default value: See _mssql.connect().
- **autocommit** (bool) - Optional - Whether to use default autocommitting mode or not.
- **tds_version** (str) - Optional - TDS protocol version to use. Default: None (changed in 2.2.0; previously '7.1').
- **use_datetime2** (bool) - Optional - Whether to use datetime.datetime conversion compatible with DATETIME2. Default: False.
- **arraysize** (int) - Optional - Number of rows to fetch at a time with .fetchmany(). Default: 1.

### Notes
- Setting timeout or login_timeout has a process-wide effect.
- For Azure connections, refer to the Azure topic.
- The tds_version parameter default is None; you must specify it or configure FreeTDS.
- FreeTDS 0.95+ supports TDS 7.3; older versions may not raise errors for invalid values.
```

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

### Get an output parameter from a stored procedure

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

Uses execute_scalar to retrieve a single output parameter from a stored procedure by declaring a variable and selecting it after execution.

```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)

```

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

### Build pymssql wheel with pip

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

Alternative command to build a wheel using pip wheel .

```console
pip wheel .
```

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

### Fetch rows from a table with _mssql

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

Demonstrates how to execute a query and iterate over the connection object to fetch rows. Iterating over query results by iterating over the connection object is new in version 2.1.0.

```python
# how to fetch rows from a table
conn.execute_query('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in conn:
    print "ID=%d, Name=%s" % (row['id'], row['name'])
```

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

### Execute row query with execute_row

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

Example usage of the execute_row method to retrieve a single row from a query. It returns the first row of data from the result set.

```python
empinfo = conn.execute_row("SELECT * FROM employees WHERE empid=10")
```

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

### pymssql 1.x connect with alternative host syntaxes

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

pymssql 1.x supports other host syntaxes, such as using a comma to separate host and port, or specifying a named instance.

```python
conn = pymssql.connect(host=r'SQLHOST,5000')  # specified TCP port at a host
conn = pymssql.connect(host=r'(local)\SQLEXPRESS')  # named instance on local machine [Win]
```

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

### pymssql 2.x returns unicode instances

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

In pymssql 2.x, query results are returned as unicode instances. This example shows fetching a string column and the resulting dictionary.

```python
>>> pymssql.__version__
u'2.0.1.2'
>>> conn.as_dict = True
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT 'hello' AS str FROM foo")
>>> cursor.fetchall()
[{u'str': u'hello'}]
```

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

### Show FreeTDS configuration with tsql -C

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

Use tsql -C to display FreeTDS compile-time settings, which can help diagnose configuration issues. The output shows version, directories, and enabled features.

```bash
$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.92.dev.20140102
             freetds.conf directory: /usr/local/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 5.0
                              iODBC: yes
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: no
                             GnuTLS: no

```

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

### Activate virtual environment on bash

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

Command to activate the virtual environment when using bash.

```console
source <path_to_pve>/bin/activate
```

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

### Execute scalar query with execute_scalar

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

Example usage of the execute_scalar method to retrieve a single value from a query. It returns the first column of the first row from the result set.

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

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

### Minimal FreeTDS Configuration

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

Minimal FreeTDS configuration file contents. Place this in /etc/freetds.conf, C:\freetds.conf, $HOME/.freetds.conf, or %APPDATA%\.freetds.conf. With this configuration, you can connect using just the hostname.

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

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

### Activate virtual environment on Windows

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

Command to activate the virtual environment on Windows.

```console
<path_to_pve>/scripts/activate.bat
```

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

### pymssql 2.x uniqueidentifier returns UUID instance

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

In pymssql 2.x, uniqueidentifier columns are returned as UUID instances. To get the byte string, use the bytes_le attribute.

```python
>>> cursor.execute("SELECT * FROM foo")
>>> id_value = cursor.fetchone()['uniqueidentifier']
>>> id_value
UUID('14cf216a-ce44-42e6-abe0-d9be790c4d4b')
>>> type(id_value)
<class 'uuid.UUID'>
>>> id_value.bytes_le
'j!\xcf\x14D\xce\xe6B\xab\xe0\xd9\xbey\x0cMK'
```

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

### Cursor.setinputsizes() and Cursor.setoutputsize()

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

These methods do nothing, as permitted by DB-API specs.

```APIDOC
## Cursor.setinputsizes() and Cursor.setoutputsize()

### Description
These methods do nothing, as permitted by DB-API specs.

### Method
Cursor.setinputsizes / Cursor.setoutputsize
```

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

### Insert row and get identity value

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

Shows how to insert a row into a table with an identity column and then retrieve the identity value of the last inserted row using the 'identity' property.

```python
conn.execute_non_query("INSERT INTO persons (name) VALUES('John Doe')")
print "Last inserted row has id = " + conn.identity

```

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

### Basic features (strict DB-API compliance)

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

Connects to a SQL Server database using environment variables for credentials, creates a table, inserts multiple rows with executemany, commits the transaction, and fetches rows with a while loop. Requires the PYMSSQL_TEST_SERVER, PYMSSQL_TEST_USERNAME, and PYMSSQL_TEST_PASSWORD environment variables.

```python
from os import getenv
import pymssql

server = getenv("PYMSSQL_TEST_SERVER")
user = getenv("PYMSSQL_TEST_USERNAME")
password = getenv("PYMSSQL_TEST_PASSWORD")

conn = pymssql.connect(server, user, password, "tempdb")
cursor = conn.cursor()
cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
    DROP TABLE persons
CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(100),
    salesrep VARCHAR(100),
    PRIMARY KEY(id)
)
""")
cursor.executemany(
    "INSERT INTO persons VALUES (%d, %s, %s)",
    [(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike T.', 'Sarah H.')])
# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()

cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cursor.fetchone()
while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()

conn.close()

```

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

### Get multiple output parameters from a stored procedure

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

Uses execute_row to retrieve multiple output parameters from a stored procedure, passing input parameters and declaring output variables.

```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'))

```

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

### Parameterized queries with _mssql

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

Shows various ways to pass parameters to queries, including positional, named, and tuple parameters for IN clauses.

```python
# examples of queries with parameters
conn.execute_query('SELECT * FROM empl WHERE id=%d', 13)
conn.execute_query('SELECT * FROM empl WHERE name=%s', 'John Doe')
conn.execute_query('SELECT * FROM empl WHERE id IN %s', ((5, 6),))
conn.execute_query('SELECT * FROM empl WHERE name LIKE %s', 'J%')
conn.execute_query('SELECT * FROM empl WHERE name=%(name)s AND city=%(city)s', \
    { 'name': 'John Doe', 'city': 'Nowhere' } )
conn.execute_query('SELECT * FROM cust WHERE salesrep=%s AND id IN %s', \
    ('John Doe', (1, 2, 3)))
conn.execute_query('SELECT * FROM empl WHERE id IN %s', (tuple(xrange(4)),))
conn.execute_query('SELECT * FROM empl WHERE id IN %s', \
    (tuple([3, 5, 7, 11]),))

```

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

### pymssql 2.x connect with server and port

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

In pymssql 2.x, use the server parameter for the host and a separate port parameter.

```python
conn = pymssql.connect(server='SQLHOST', port=1500)
```

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

### pymssql 1.x connect with host and port

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

In pymssql 1.x, the host parameter can include a port separated by a colon.

```python
conn = pymssql.connect(host='SQLHOST:1433')  # specified TCP port at a host
```

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

### Activate virtual environment on Windows

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

Activate the virtual environment on Windows using the activate.bat script.

```batch
<path_to_pve>/scripts/activate.bat

```

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

### Run all tests

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

Run the full test suite with pytest. The optional 'cd tests' changes to the tests directory.

```bash
cd tests # optional
py.test
```

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

### pymssql 1.x uniqueidentifier returns byte string

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

In pymssql 1.x, uniqueidentifier columns are returned as 16-byte strings. To get a UUID object, you must construct it manually using uuid.UUID with bytes_le.

```python
>>> cursor.execute("SELECT * FROM foo")
>>> id_value = cursor.fetchone()['uniqueidentifier']
>>> id_value
'j!\xcf\x14D\xce\xe6B\xab\xe0\xd9\xbey\x0cMK'
>>> type(id_value)
<type 'str'>
>>> len(id_value)
16
>>> import uuid
>>> id_uuid = uuid.UUID(bytes_le=id_value)
>>> id_uuid
UUID('14cf216a-ce44-42e6-abe0-d9be790c4d4b')
```

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

### Queries with parameters using _mssql

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

Shows various ways to pass parameters to execute_query, including positional placeholders (%d, %s), IN lists, LIKE patterns, named parameters, and tuples. Note that '%' is not a special character in the query string.

```python
# examples of queries with parameters
conn.execute_query('SELECT * FROM empl WHERE id=%d', 13)
conn.execute_query('SELECT * FROM empl WHERE name=%s', 'John Doe')
conn.execute_query('SELECT * FROM empl WHERE id IN %s', ((5, 6),))
conn.execute_query('SELECT * FROM empl WHERE name LIKE %s', 'J%')
conn.execute_query('SELECT * FROM empl WHERE name=%(name)s AND city=%(city)s', \
    { 'name': 'John Doe', 'city': 'Nowhere' } )
conn.execute_query('SELECT * FROM cust WHERE salesrep=%s AND id IN %s', \
    ('John Doe', (1, 2, 3)))
conn.execute_query('SELECT * FROM empl WHERE id IN %s', (tuple(xrange(4)),))
conn.execute_query('SELECT * FROM empl WHERE id IN %s', \
    (tuple([3, 5, 7, 11]),))
```

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

### Select specific tests to run

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

Run specific test files, classes, or individual tests using pytest's selection syntax.

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

```

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

### ImportError: Shared object libsybdb.so.3 not found

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

Shows the ImportError that occurs when the FreeTDS library is unavailable or the dynamic linker cannot find it. Check that libsybdb.so is installed and its path is in /etc/ld.so.conf, then run ldconfig as root. On Solaris, set LD_LIBRARY_PATH to the library directory.

```python
>>> from pymssql import _mssql
Traceback (most recent call last):
File "<stdin>", line 1, in ?
ImportError: Shared object "libsybdb.so.3" not found
```

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

### Call stored procedure using callproc

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

As of pymssql 2.0.0, stored procedures can be called using the RPC interface. The example creates a stored procedure FindPerson and calls it with callproc. You must call commit() to persist data if autocommit is not enabled.

```python
with pymssql.connect(server, user, password, "tempdb") as conn:
    with conn.cursor(as_dict=True) as cursor:
        cursor.execute("""
        CREATE PROCEDURE FindPerson
            @name VARCHAR(100)
        AS BEGIN
            SELECT * FROM persons WHERE name = @name
        END
        """)
        cursor.callproc('FindPerson', ('Jane Doe',))
        # you must call commit() to persist your data if you don't set autocommit to True
        conn.commit()
        for row in cursor:
            print("ID=%d, Name=%s" % (row['id'], row['name']))
```

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

### Avoid exception for unnamed column by aliasing with AS

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

When a query returns a column without a name, pymssql raises an exception. Supply a name using AS to avoid this. The example shows a SELECT with MAX(x) AS [MAX(x)] and the resulting fetchall output.

```python
>>> cursor.execute("SELECT MAX(x) AS [MAX(x)] FROM (VALUES (1), (2), (3)) AS foo(x)")
>>> cursor.fetchall()
[{'MAX(x)': 3}]
```

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

### version_info()

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

Returns version information for pymssql.

```APIDOC
## version_info()

### Description
Returns version information for pymssql.

### Method
Function

### Returns
- **version_info** - Version information (type not specified in source).
```

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

### Test connection with tsql using host and port

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

Tests the connection with tsql using hostname and port notation. Use this form when you have not defined a server alias in freetds.conf.

```bash
$ tsql -H mydbserver -p 1433 -U user
```

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

### Using conn.identity to get last inserted row ID

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

Shows how to retrieve the identity value of the last inserted row after an INSERT statement. Assumes the persons table has an identity column. The identity property returns None if the previous operation did not insert a row into a table with an identity column.

```python
conn.execute_non_query("INSERT INTO persons (name) VALUES('John Doe')")
print "Last inserted row has id = " + conn.identity
```