MSSQL

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

A class for basic communication with Microsoft SQL Server databases.

Parameters:

host (str | 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 | None) – listening port; when port=None (default), it is initialized as 1433 (default by installation of the SQL Server)
username (str | None) – name of the user or login used to connect; when username=None (default), the instantiation relies on Windows Authentication
password (str | int | None) – user’s password; when password=None (default), it is required to mannually type in the correct password to connect the SQL server
database_name (str | 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 | int) – whether to print relevant information in console, defaults to True

Variables:

host (str) – host name/address
port (str) – listening port used by SQL Server
username (str) – username
database_name (str) – name of a database
credentials (dict) – basic information about the server/database being connected
auth (str | 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

`BUILTIN_SCHEMAS`	set: Names of built-in schemas of Microsoft SQL Server.
`DEFAULT_DATABASE`	str: Default database name.
`DEFAULT_DIALECT`	str: Default dialect.
`DEFAULT_DRIVER`	str: Default name of database driver.
`DEFAULT_HOST`	str: Default host (server name).
`DEFAULT_ODBC_DRIVER`	str: Default ODBC driver.
`DEFAULT_PORT`	str: Default listening port used by Microsoft SQL Server.
`DEFAULT_SCHEMA`	str: Default schema name.
`DEFAULT_USERNAME`	str: Default username.

Methods

`add_primary_key`(column_name, table_name[, ...])	Add a primary key to a table.
`connect_database`([database_name, verbose])	Establish a connection to a database.
`create_connection`([database_name, mode])	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_schema`(schema_names[, ...])	Delete/drop one or multiple schemas.
`drop_table`(table_name[, schema_name, ...])	Delete/drop a table.
`get_column_info`(table_name[, schema_name, ...])	Get information about columns of 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_schema_info`([names_only, include_all, ...])	Get the names of existing schemas.
`get_table_names`([schema_name, verbose])	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_sql_query`(sql_query, method, ...)
`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.
`validate_column_names`(table_name[, ...])	Validate column names for query statement.
`varchar_to_geometry_dtype`(table_name, ...[, ...])	Alter a VARCHAR column (of geometry data) to geometry data type.