class pyhelpers.dbms.MSSQL(host=None, port=None, username=None, password=None, database_name=None, confirm_db_creation=False, verbose=True)

A class for basic communication with Microsoft SQL Server databases.

  • host (str or None) – name of the server running the SQL Server, e.g. 'localhost' or ''; when host=None (default), it is initialized as 'localhost'

  • port (int or None) – listening port; when port=None (default), it is initialized as 1433 (default by installation of the SQL Server)

  • username (str or None) – name of the user or login used to connect; when username=None (default), the instantiation relies on Windows Authentication

  • password (str or int or None) – user’s password; when password=None (default), it is required to mannually type in the correct password to connect the PostgreSQL server

  • database_name (str or None) – name of a database; when database=None (default), it is initialized as 'master'

  • confirm_db_creation (bool) – whether to prompt a confirmation before creating a new database (if the specified database does not exist), defaults to False

  • verbose (bool or int) – whether to print relevant information in console, defaults to True

  • host (str) – host name/address

  • port (str) – listening port used by PostgreSQL

  • username (str) – username

  • database_name (str) – name of a database

  • credentials (dict) – basic information about the server/database being connected

  • auth (str or None) – authentication method (used for establish the connection)

  • address (str) – representation of the database address

  • engine (sqlalchemy.engine.Engine) – a SQLAlchemy connectable engine to a SQL Server; see also [DBMS-MS-3]


>>> from pyhelpers.dbms import MSSQL

>>> mssql = MSSQL()
Connecting <server_name>@localhost:1433/master ... Successfully.

>>> mssql.address

>>> testdb = MSSQL(database_name='testdb')
Creating a database: [testdb] ... Done.
Connecting <server_name>@localhost:1433/testdb ... Successfully.

>>> testdb.database_name

>>> testdb.drop_database(verbose=True)
To drop the database [testdb] from <server_name>@localhost:1433
? [No]|Yes: yes
Dropping [testdb] ... Done.



Default database name.


Default dialect.


Default name of database driver.


Default host (server name).


Default ODBC driver.


Default listening port used by Microsoft SQL Server.


Default schema name.


Default username.


connect_database([database_name, verbose])

Establish a connection to a database.

create_connection([database_name, ...])

Create a SQLAlchemy connection.


Create a pyodbc cursor.

create_database(database_name[, verbose])

Create a database.

create_engine([database_name, auth, password])

Create a SQLAlchemy connectable engine.

create_schema(schema_name[, verbose])

Create a schema.

create_table(table_name, column_specs[, ...])

Create a table.


Check whether a database exists.

disconnect_database([database_name, verbose])

Disconnect a database.

drop_database([database_name, ...])

Delete/drop a database.

drop_table(table_name[, schema_name, ...])

Delete/drop a table.

get_column_names(table_name[, schema_name])

Get column names of a table.


Get names of all existing databases.


Get information about FileTables (if available).

get_primary_keys([table_name, schema_name, ...])

Get the primary keys of table(s).

get_row_count(table_name[, schema_name])

Get row count of a table in a database.


Get names of all tables stored in a schema.

has_dtypes(table_name, dtypes[, schema_name])

Check whether a table contains data of a certain data type or data types.

import_data(data, table_name[, schema_name, ...])

Import tabular data into a table.

read_columns(table_name, column_names[, ...])

Read data of specific columns of a table.

read_table(table_name[, schema_name, ...])

Read data from a table.


Check whether a schema exists.

specify_conn_str([database_name, auth, password])

Specify a string used for establishing a connection.

table_exists(table_name[, schema_name])

Check whether a table exists.