Microsoft SQL Server¶

There are two ways to use SQL Server with mara:

using the official MSSQL Tools for SQL Server on linux (sqlcmd, bcp)
using the linux sql client tool sqsh (legacy)

Currently by default sqsh is used. This will be changed in a future version to the official MSSQL Tools from Microsoft. You can explicitly specify the client tool you want to use, see below.

Prerequisites¶

On Ubuntu/Debian make sure you have the ODBC header files before installing

$ sudo apt install unixodbc-dev

The python module pyodbc requires a ODBC driver to be installed. By default Microsoft ODBC Driver 17 for SQL Server is used. You can find the installation guide here: Installing the Microsoft ODBC Driver for SQL Server (Linux).

Installation¶

Use extras mssql to install all required packages.

$ pip install mara-db[mssql]

Use MSSQL Tools¶

To see how to install the MSSQL Tools, follow this guide: Install the SQL Server command-line tools sqlcmd and bcp on Linux

Use sqsh¶

To install the sqsh shell tool, see here https://sourceforge.net/projects/sqsh/. Usually messy to get working. On ubuntu, use http://ppa.launchpad.net/jasc/sqsh/ubuntu/ backport. On Mac, try the homebrew version or install from source.

Configuration examples¶

import mara_db.dbs
mara_db.config.databases = lambda: {
    'dwh': mara_db.dbs.SQLServerDB(
        host='localhost',
        user='sa',
        password='<my_strong_password>',
        database='dwh'),
}

# explicitly define to use the MSSQL Tools (RECOMMENDED)
mara_db.config.databases = lambda: {
    'dwh': mara_db.dbs.SqlcmdSQLServerDB(
        host='localhost',
        user='sa',
        password='<my_strong_password>',
        database='dwh'),
}

# explicitly define to use sqsh
mara_db.config.databases = lambda: {
    'dwh': mara_db.dbs.SqshSQLServerDB(
        host='localhost',
        user='sa',
        password='<my_strong_password>',
        database='dwh'),
}

import mara_db.dbs
mara_db.config.databases = lambda: {
    'dwh': mara_db.dbs.SQLServerDB(
        host='localhost',
        user='sa',
        password='<my_strong_password>',
        database='dwh',
        odbc_driver='ODBC Driver 18 for SQL Server'),
}

# explicitly define to use the MSSQL Tools (RECOMMENDED)
mara_db.config.databases = lambda: {
    'dwh': mara_db.dbs.SqlcmdSQLServerDB(
        host='localhost',
        user='sa',
        password='<my_strong_password>',
        database='dwh',
        odbc_driver='ODBC Driver 18 for SQL Server'),
}

# explicitly define to use sqsh
mara_db.config.databases = lambda: {
    'dwh': mara_db.dbs.SqshSQLServerDB(
        host='localhost',
        user='sa',
        password='<my_strong_password>',
        database='dwh',
        odbc_driver='ODBC Driver 18 for SQL Server'),
}

API reference¶

This section contains database specific API in the module.

Configuration¶

class mara_db.dbs.SQLServerDB(host: Optional[str] = None, port: Optional[int] = None, database: Optional[str] = None, user: Optional[str] = None, password: Optional[str] = None, odbc_driver: Optional[str] = None, **kargs)¶

__init__(host: Optional[str] = None, port: Optional[int] = None, database: Optional[str] = None, user: Optional[str] = None, password: Optional[str] = None, odbc_driver: Optional[str] = None)¶

property sqlalchemy_url¶: Returns the SQLAlchemy url for a database

class mara_db.dbs.SqlcmdSQLServerDB(host: Optional[str] = None, port: Optional[int] = None, database: Optional[str] = None, user: Optional[str] = None, password: Optional[str] = None, odbc_driver: Optional[str] = None, **kargs)¶

__init__(host: Optional[str] = None, instance: Optional[str] = None, port: Optional[int] = None, database: Optional[str] = None, user: Optional[str] = None, password: Optional[str] = None, odbc_driver: Optional[str] = None, protocol: Optional[str] = None, quoted_identifier: bool = True, trust_server_certificate: bool = False)¶

Connection information for a SQL Server database using the MSSQL Tools e.g. sqlcmd

Parameters

quoted_identifier – If set to true, the SET option QUOTED_IDENTIFIER is set to ON, otherwise OFF.
protocol – can be tcp (TCP/IP connection), np (named pipe) or lcp (using shared memory). See as well: https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-connect-to-the-database-engine?view=sql-server-ver15
trust_server_certificate – Trust the server certificate without validation

property sqlalchemy_url¶: Returns the SQLAlchemy url for a database

class mara_db.dbs.SqshSQLServerDB(host: Optional[str] = None, port: Optional[int] = None, database: Optional[str] = None, user: Optional[str] = None, password: Optional[str] = None, odbc_driver: Optional[str] = None, **kargs)¶

__init__(host: Optional[str] = None, port: Optional[int] = None, database: Optional[str] = None, user: Optional[str] = None, password: Optional[str] = None, odbc_driver: Optional[str] = None)¶: Connection information for a SQL Server database using the unix package sqsh

property sqlalchemy_url¶: Returns the SQLAlchemy url for a database