MSSQL

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.

Parameters

  • host (str or None) – name of the server running the SQL Server, e.g. 'localhost' or '127.0.0.1'; 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

Variables

  • 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]

Examples:

>>> from pyhelpers.dbms import MSSQL

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

>>> mssql.address
'<server_name>@localhost:1433/master'

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

>>> testdb.database_name
'testdb'

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

Attributes

DEFAULT_DATABASE

Default database name.

DEFAULT_DIALECT

Default dialect.

DEFAULT_DRIVER

Default name of database driver.

DEFAULT_HOST

Default host (server name).

DEFAULT_ODBC_DRIVER

Default ODBC driver.

DEFAULT_PORT

Default listening port used by Microsoft SQL Server.

DEFAULT_SCHEMA

Default schema name.

DEFAULT_USERNAME

Default username.

Methods

connect_database([database_name, verbose])

Establish a connection to a database.

create_connection([database_name, ...])

Create a SQLAlchemy connection.

create_cursor([database_name])

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.

database_exists([database_name])

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_database_names([names_only])

Get names of all existing databases.

get_file_tables([names_only])

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_table_names([schema_name])

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.

schema_exists(schema_name)

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.