Repository: learning-the-computers/dot-connect (language: python)

Issue:
Add SQL Server as backend



Discussion / hints:

Fix patch:
diff --git a/.pre-commit-config.yaml b/.pre-commit-config.yaml
index e738399..f938086 100644
--- a/.pre-commit-config.yaml
+++ b/.pre-commit-config.yaml
@@ -30,3 +30,17 @@ repos:
     hooks:
     -   id: pyupgrade
         args: ["--py38-plus"]
+-   repo: https://github.com/pre-commit/mirrors-mypy
+    rev: v1.5.0
+    hooks:
+    -   id: mypy
+-   repo: https://github.com/PyCQA/flake8
+    rev: 6.1.0
+    hooks:
+    -   id: flake8
+        args: ['--ignore=E501']
+        additional_dependencies: [flake8-docstrings]
+-   repo: https://github.com/asottile/setup-cfg-fmt
+    rev: v2.4.0
+    hooks:
+    -   id: setup-cfg-fmt
diff --git a/CONTRIBUTING.md b/CONTRIBUTING.md
index adc627b..0094222 100644
--- a/CONTRIBUTING.md
+++ b/CONTRIBUTING.md
@@ -19,6 +19,8 @@ MYSQL_PASSWORD=my-secret-pw
 POSTGRES_HOST=localhost
 POSTGRES_USER=postgres
 POSTGRES_PASSWORD=mysecretpassword
+SA_PASSWORD=YourStrong!Passw0rd
+ACCEPT_EULA=Y
 ```
 
 ## Postgres
@@ -30,3 +32,11 @@ docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:54
 ```
 docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 3306:3306 -d mysql
 ```
+
+## SQL Server
+```
+docker run --name sql_server_container -e ACCEPT_EULA=Y -e SA_PASSWORD=YourStrong!Passw0rd -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
+```
+
+## Snowflake
+[Using the Python Connector](https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-example)
diff --git a/README.md b/README.md
index 2167aa7..5c0fbbc 100644
--- a/README.md
+++ b/README.md
@@ -1,30 +1,28 @@
 # dot-connect
 [![tox](https://github.com/learning-the-computers/dot-connect/actions/workflows/tox.yml/badge.svg)](https://github.com/learning-the-computers/dot-connect/actions/workflows/tox.yml)
+[![PyPI](https://img.shields.io/pypi/v/dot-connect.svg)](https://pypi.org/project/dot-connect)
 
-A Python package designed to simplify the way you fetch connection configurations and establish connections to various database and cloud systems.
+A Python package designed to simplify the way you fetch connection configurations and establish connections to various databases and cloud systems.
 
 ## 🚀 Features
 
-- **Unified Interface**: Single interface - no need to remember different APIs for databases or cloud providers.
-- **Easy Configuration**: Fetch connection configurations from multiple sources like JSON files, environment variables, etc.
-- **Supported Backends**: MySQL, Postgres, and Snowflake. More coming soon.
+- **Unified Interface**: Say goodbye to juggling multiple APIs. With `dot_connect`, you get a standardized interface, making connecting with various databases and cloud providers easier.
 
----
+- **Easy Configuration**: Fetch connection configurations with ease. `dot_connect` supports various sources like JSON files, environment variables, and more, ensuring flexibility and adaptability for your projects.
 
-## 💽 Installation
+- **Supported Backends**: Out-of-the-box support for popular systems such as MySQL, Postgres, and Snowflake. We're continuously working to expand this list.
+
+- **Extensibility**: Designed with developers in mind, it's easy to extend and customize `dot_connect` for your needs.
+
+## 📦 Installation (PyPI)
 
 ```bash
 pip install dot-connect
 ```
 
-## 📚 Quickstart
+## 🔧 Usage
 
 1. Create a .env file.
-```
-SNOWFLAKE_ACCOUNT=orgname-accountname
-SNOWFLAKE_USER=username
-SNOWFLAKE_PASSWORD=password
-```
 
 2. Connect and query various backends!
 ```python
@@ -51,7 +49,7 @@ cursor.fetchall()
 
 ## 🤝 Contributing
 
-We welcome contributions! Please see our [CONTRIBUTING.md](CONTRIBUTING.md) for guidelines.
+We welcome contributions from the community. Whether it's a bug report, a new feature, or an improvement, your insights will help make `dot_connect` even better. Please see our [contributing guidelines](CONTRIBUTING.md) for more details.
 
 ## ⚖️ License
 Licensed under the Apache License, Version 2.0. See [LICENSE](LICENSE) for the full text.
diff --git a/dot_connect/__init__.py b/dot_connect/__init__.py
index e7b8ebc..2b47806 100644
--- a/dot_connect/__init__.py
+++ b/dot_connect/__init__.py
@@ -1,7 +1,14 @@
 """The module for making connections easier."""
-from dot_connect.backends import load_config, mysql, postgres, snowflake, snowpark
+from dot_connect.backends import (  # noqa F401
+    load_config,
+    mssql,
+    mysql,
+    postgres,
+    snowflake,
+    snowpark,
+)
 
 
 def list_backends() -> list:
     """List compatible dot-connect backends."""
-    return ["mysql", "postgres", "snowflake", "snowpark"]
+    return ["mssql", "mysql", "postgres", "snowflake", "snowpark"]
diff --git a/dot_connect/backends/mssql.py b/dot_connect/backends/mssql.py
new file mode 100644
index 0000000..f150be1
--- /dev/null
+++ b/dot_connect/backends/mssql.py
@@ -0,0 +1,35 @@
+"""Utility for MSSQL connection management."""
+import pyodbc
+
+from dot_connect.backends import load_config
+
+
+def connect(**kwargs):
+    """
+    Establish a connection to a MSSQL database.
+
+    The function fetches default connection configurations and optionally updates
+    them with provided keyword arguments.
+
+    Args:
+        **kwargs: Connection parameters to override default configurations.
+
+    Returns:
+        pyodbc.Connection: Connection to the MSSQL database.
+
+    Example:
+        >>> connection = connect(user='my_user',
+        >>>                      password='my_password',
+        >>>                      database='my_db')
+
+    Note:
+        Ensure 'pyodbc' package is installed and relevant ODBC drivers
+        for MSSQL are set up before utilizing this function.
+    """
+    config = load_config("MSSQL")
+
+    config.update(kwargs)
+
+    connection_string = ";".join([f"{key}={value}" for key, value in config.items()])
+
+    return pyodbc.connect(connection_string)
diff --git a/setup.cfg b/setup.cfg
index 976f09a..35b108c 100644
--- a/setup.cfg
+++ b/setup.cfg
@@ -1,19 +1,20 @@
 [metadata]
-name = dot-connect
-version = 0.2.0
-author = Tyler White
+name = dot_connect
+version = 0.3.0
 description = Utilities for making connections easier.
 long_description = file: README.md
 long_description_content_type = text/markdown
 url = https://github.com/learning-the-computers/dot-connect
-license = Apache License 2.0
+author = Tyler White
+license = Apache-2.0
+license_files = LICENSE
 classifiers =
+    License :: OSI Approved :: Apache Software License
+    Operating System :: OS Independent
     Programming Language :: Python :: 3
     Programming Language :: Python :: 3.8
     Programming Language :: Python :: 3.9
     Programming Language :: Python :: 3.10
-    License :: OSI Approved :: Apache Software License
-    Operating System :: OS Independent
 
 [options]
 packages = find:
@@ -23,12 +24,14 @@ python_requires = >=3.8, <3.11
 dev =
     ipykernel
     pre-commit
-    python-dotenv
     pytest
+    python-dotenv
     tox
     tox-gh-actions
 impala =
     impyla
+mssql =
+    pyodbc
 mysql =
     mysql-connector-python
 postgres =


Test patch:
diff --git a/tests/docker-compose.yml b/tests/docker-compose.yml
new file mode 100644
index 0000000..e10ca2c
--- /dev/null
+++ b/tests/docker-compose.yml
@@ -0,0 +1,12 @@
+version: '3.8'
+
+services:
+  sql_server:
+    image: mcr.microsoft.com/mssql/server:2019-latest
+    container_name: sql_server_container
+    environment:
+      - ACCEPT_EULA=Y
+      - SA_PASSWORD=your_Strong3$Tpassword
+      - MSSQL_PID=Express
+    ports:
+      - "1433:1433"
diff --git a/tests/test_mssql.py b/tests/test_mssql.py
new file mode 100644
index 0000000..46de512
--- /dev/null
+++ b/tests/test_mssql.py
@@ -0,0 +1,35 @@
+"""Tests for the MSSQL connection utility in the dot_connect package."""
+
+import pytest
+from pyodbc import InterfaceError
+
+import dot_connect
+from dot_connect import load_config
+
+
+def test_mssql_connect_valid():
+    """
+    Test valid connection to MSSQL.
+
+    This test assumes that a valid MSSQL test database is set up and
+    will check if the connection can be established.
+    """
+    # Assuming you have a test MSSQL database setup
+    conn = dot_connect.mssql.connect(load_config("MSSQL"))
+    assert conn is not None
+    conn.close()
+
+
+def test_mssql_connect_invalid():
+    """
+    Test invalid connection to MSSQL.
+
+    This test provides invalid user credentials to ensure that the
+    expected InterfaceError is raised by pyodbc.
+    """
+    with pytest.raises(
+        InterfaceError
+    ):  # Typically, pyodbc raises this for connection issues
+        dot_connect.mssql.connect(
+            user="invalid_user", password="invalid_password", database="invalid_db"
+        )
diff --git a/tests/test_mysql.py b/tests/test_mysql.py
index 2974e13..867b95f 100644
--- a/tests/test_mysql.py
+++ b/tests/test_mysql.py
@@ -1,13 +1,29 @@
+"""Tests for the MySQL connection utility in the dot_connect package."""
+
 import dot_connect
 from dot_connect import load_config
 
 
 def test_load_config():
+    """
+    Test if the 'load_config' function retrieves valid MySQL configuration.
+
+    The function should load a dictionary containing valid MySQL connection
+    parameters. This test specifically checks for the presence of a 'password'
+    key in the returned configuration.
+    """
     loaded_configs = load_config("MYSQL")
     assert loaded_configs.get("password")
 
 
 def test_connect():
+    """
+    Test a successful MySQL connection using the 'connect' function.
+
+    The function should establish a connection to a MySQL database and
+    be able to execute a simple query. This test checks if a basic "SELECT 1"
+    query can be successfully executed.
+    """
     con = dot_connect.mysql.connect()
     cursor = con.cursor()
     cursor.execute("SELECT 1")
diff --git a/tests/test_postgres.py b/tests/test_postgres.py
index acf960e..f8f7fb3 100644
--- a/tests/test_postgres.py
+++ b/tests/test_postgres.py
@@ -1,13 +1,29 @@
+"""Tests for the Postgres connection utility in the dot_connect package."""
+
 import dot_connect
 from dot_connect import load_config
 
 
 def test_load_config():
+    """
+    Validate the 'load_config' function for PostgreSQL configurations.
+
+    This test ensures that the function retrieves a configuration containing
+    essential connection parameters. Specifically, it checks for the presence of
+    a 'password' key in the returned configuration for PostgreSQL.
+    """
     loaded_configs = load_config("POSTGRES")
     assert loaded_configs.get("password")
 
 
 def test_connect():
+    """
+    Assess the PostgreSQL connection via the 'connect' function.
+
+    This test establishes a connection to a PostgreSQL database using the
+    'connect' function. It then verifies the connection's functionality by
+    executing a basic "SELECT 1" query and checking the results.
+    """
     con = dot_connect.postgres.connect()
     cursor = con.cursor()
     cursor.execute("SELECT 1")
diff --git a/tests/test_snowflake.py b/tests/test_snowflake.py
index 600471a..1975cf9 100644
--- a/tests/test_snowflake.py
+++ b/tests/test_snowflake.py
@@ -1,3 +1,5 @@
+"""Tests for the Snowflake connection utility in the dot_connect package."""
+
 from dotenv import load_dotenv
 from snowflake.connector.connection import SnowflakeConnection
 
@@ -6,11 +8,42 @@
 
 
 def test_load_config():
+    """
+    Validate the 'load_config' function for Snowflake configurations.
+
+    This test ensures that the function retrieves a configuration containing
+    essential connection parameters for Snowflake. Specifically, it verifies
+    the presence of an 'account' key in the returned configuration.
+    """
     loaded_configs = load_config("SNOWFLAKE")
     assert loaded_configs.get("account")
 
 
 def test_connect():
+    """
+    Assess the Snowflake connection via the 'connect' function.
+
+    This test establishes a connection to Snowflake using the 'connect' function
+    and then checks if the returned connection is an instance of the
+    `SnowflakeConnection` class. Environment variables are loaded for the
+    connection.
+    """
     con = dot_connect.snowflake.connect()
     load_dotenv(override=True)
     assert isinstance(con, SnowflakeConnection)
+
+
+def test_basic_query_execution():
+    """
+    Test basic query execution on the Snowflake connection.
+
+    This test establishes a connection, runs a basic query to ensure
+    functionality, and then disconnects.
+    """
+    con = dot_connect.snowflake.connect()
+    load_dotenv(override=True)
+    cursor = con.cursor()
+    cursor.execute("SELECT 1")
+    assert cursor.fetchone()[0] == 1
+    cursor.close()
+    con.close()
diff --git a/tests/test_snowpark.py b/tests/test_snowpark.py
index dda1983..ebfb3d4 100644
--- a/tests/test_snowpark.py
+++ b/tests/test_snowpark.py
@@ -1,3 +1,5 @@
+"""Tests for the Snowpark connection utility in the dot_connect package."""
+
 from dotenv import load_dotenv
 from snowflake.snowpark import Session
 
@@ -6,11 +8,21 @@
 
 
 def test_load_config():
+    """
+    Test valid connection parameters.
+
+    This test assumes that connection parameters work.
+    """
     loaded_configs = load_config("SNOWFLAKE")
     assert loaded_configs.get("account")
 
 
 def test_connect():
+    """
+    Test valid connection parameters.
+
+    This test assumes that connection parameters work.
+    """
     con = dot_connect.snowpark.connect()
     load_dotenv(override=True)
     assert isinstance(con, Session)