pymssql-utils is a small library that wraps pymssql to make your life easier.
Project description
pymssql-utils (ALPHA)
pymssql-utils is a small library that wraps pymssql to make your life easier. It provides a higher-level API, as well as a some utility methods, so that you can think less about connections and cursors.
This module's features:
- Higher-level API that reduces the amount of boilerplate required.
- Baked-in sensible defaults and usage patterns.
- Provides optional execution batching, similar to
pyodbc's
fast_executemany
. - Parses the SQL Types that pymssql misses to native Python types, and vice versa.
- Makes it easy to serialize your data with orjson.
- Provides you with simple and clear options for error handling.
- Extra utility functions, e.g. for building dynamic SQL queries.
- Fixing various edge case bugs that arise when using pymssql.
- Fully type hinted.
This module's enforced opinions (check these work for you):
- Each execution opens and closes a connection using pymssql's context management.
- Execution data is returned as a dictionary, as accessing data by column name is clearer and simpler than by index.
- Converts numeric data to
float
as this is easier to work with thanDecimal
and for the vast majority of cases 'good enough'.
When you shouldn't use this module:
- If you need fine-grained control over your cursors.
- If performance is a must (use pyodbc's)
Please raise any suggestions or issues via GitHub.
Usage
Installation
This library can be installed via pip: pip install --upgrade pymssql-utils
.
This library requires Python >= 3.6
and Pip >= 19.3
.
Quickstart
This library provides two high-level methods:
Query
: non-committing, fetches dataExecute
: committing, optionally fetches data
Running a simple query, accessing the returned data and serialising to JSON:
>>> import pymssqlutils as sql
>>> result = sql.query(
"SELECT SYSDATETIMEOFFSET() as now",
server="..."
)
>>> result.ok
True
>>> result.data
[{'now': datetime.datetime(2021, 1, 21, 23, 31, 11, 272299, tzinfo=datetime.timezone.utc)}]
>>> result.data[0]['now']
datetime.datetime(2021, 1, 21, 23, 31, 11, 272299, tzinfo=datetime.timezone.utc)
>>> result.to_json()
'[{"now":"2021-01-21T23:31:11.272299+00:00"}]'
Running a simple execution:
TODO
Specifying Connection
There are two ways of specifying the connection parameters to the SQL Server:
- Passing the required parameters
(see pymssql docs)
to
query
orexecute
like in the quickstart example above. Note: All extra kwargs passed to these methods are passed on to thepymssql.connection()
. - Specify the connection parameters in the environment like the example below. Note: that parameters given explicitly will take precedence over connection parameters specified in the environment.
import os
import pymssqlutils as sql
os.environ["MSSQL_SERVER"] = "sqlserver.mycompany.com"
os.environ["MSSQL_USER"] = "my_login"
os.environ["MSSQL_PASSWORD"] = "my_password123"
result = sql.execute("INSERT INTO mytable VALUES (1, 'test)")
Executing SQL
This library provides four functions for executing SQL code:
query
, execute
, execute_many
& execute_batched
.
These functions call pymssql's execute
or executemany
functions with varying behaviour to fetching
result data or committing the transaction, see table below.
Function | Uses | commits | fetches |
---|---|---|---|
query | execute | False | True |
execute | execute | True | Optional |
execute_many | executemany | True | False |
execute_batched | execute | True | False |
Splitting query
& execute
into two functions based on whether the execution
commits or not is intended to make your code clearer and more explicit.
Error handling (TODO)
Utility Functions (TODO)
Testing (TODO)
Must install pytest to run main tests, that mock cursor results.
To test on_database tests against an MSSQL instance "TEST_ON_DATABASE"
must be set in the environment
as well as any of the normal env variables to connect to the MSSQL server, pytest-dotenv
can help with this.
Notes
Why pymssql when Microsoft officially recommends pyodbc (opinion)?
The main difference between pyodbc and pymssql is the drivers they use. The ODBC are newer and have various levels of support on differing linux distributions, and if you develop for containers or distribute code onto different platforms you can run into ODBC driver-related issues that FreeTDS tends to not have.
There are other minor reasons someone might prefer pymssql, e.g.:
- pymssql's parameter subsitution is done client-side improving operation visibility.
- pymssql also has support for MSSQL specific data types such as
Datetimeoffset
.
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.
Source Distribution
Built Distribution
Hashes for pymssql_utils-0.0.11-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 8d60a7eda8d7097f5e2c3d73d1a7506bf2d895782c165a0ed893b94e46fb7cdc |
|
MD5 | dcb66f53065da266a7d30eff16c7c6da |
|
BLAKE2b-256 | 3b5d7025b55b37efe26934ef39445f708dca1ace09d2bc6a8d89f62df41ff129 |