MSSQL

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

A class for basic communication with Microsoft SQL Server databases.

Parameters:

• host (str | None) – Name or IP address of the SQL Server, e.g. 'localhost' or '127.0.0.1'; defaults to 'localhost' if not specified.

• port (int | None) – Listening port of the SQL Server; defaults to 1433 if not specified (default by installation of the SQL Server).

• username (str | None) – Username for authentication; if not provided, Windows Authentication is used.

• password (str | int | None) – Password for the specified username; required for non-Windows Authentication.

• database_name (str | None) – Name of the initial database to connect to; defaults to 'master' if not specified.

• confirm_db_creation (bool) – Whether to prompt for confirmation before creating a new database (if the specified database does not exist); defaults to False.

• verbose (bool | int) – Whether to print connection and operation details to the console; defaults to False.

• raise_error (bool) – Whether to raise the provided exception; if raise_error=False (default), the error will be suppressed.

Variables:

• host (str) – Name or IP address of the SQL Server.

• port (str) – Listening port of the SQL Server.

• username (str) – Username used for authentication.

• database_name (str) – Name of the connected database.

• credentials (dict) – Contains basic information about the server and database.

• auth (str | None) – Authentication method used for connection.

• address (str) – String representation of the database connection address.

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

Examples:

>>> from pyhelpers.dbms import MSSQL
>>> mssql = MSSQL(verbose=True)
Connecting <server_name>@localhost:1433/master ... Successfully.
>>> mssql.address
'<server_name>@localhost:1433/master'
>>> testdb = MSSQL(database_name='testdb', verbose=True)
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	Names of built-in schemas of Microsoft SQL Server.
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

add_primary_key(column_name, table_name[, ...])	Adds a primary key constraint to a table.
connect_database([database_name, verbose, ...])	Establishes a connection to a database.
create_connection([database_name, mode])	Creates a SQLAlchemy connection to a Microsoft SQL Server database.
create_cursor([database_name])	Creates a pyodbc cursor.
create_database(database_name[, verbose])	Creates a database.
create_engine([database_name, auth, password])	Creates a SQLAlchemy connectable engine for connecting to a SQL Server database.
create_schema(schema_name[, verbose, ...])	Creates a schema.
create_table(table_name, column_specs[, ...])	Creates a table with specified columns.
database_exists([database_name])	Checks whether a database exists.
disconnect_database([database_name, ...])	Disconnects from a database.
drop_database([database_name, ...])	Deletes/drops a database.
drop_schema(schema_names[, ...])	Deletes/drops one or multiple schemas.
drop_table(table_name[, schema_name, ...])	Deletes/drops a table.
get_column_info(table_name[, schema_name, ...])	Retrieves information about columns of a table.
get_column_names(table_name[, schema_name])	Retrieves column names of a table.
get_database_names([names_only])	Gets names of all existing databases.
get_file_tables([names_only])	Retrieves information about FileTables from the database (if available).
get_primary_keys([table_name, schema_name, ...])	Retrieves the primary keys of table(s) from the currently-connected database.
get_row_count(table_name[, schema_name])	Gets the row count of a table.
get_schema_info([names_only, include_all, ...])	Retrieves information about existing schemas.
get_table_names([schema_name, verbose])	Gets names of all tables stored in one or multiple schemas.
has_dtypes(table_name, dtypes[, schema_name])	Checks whether a table contains columns of specified data types.
import_data(data, table_name[, schema_name, ...])	Imports tabular data into a table.
read_columns(table_name, column_names[, ...])	Reads data of specific columns of a table.
read_sql_query(sql_query[, method, ...])	Executes a SQL query and read the result into a DataFrame.
read_table(table_name[, schema_name, ...])	Reads data from a specified table.
schema_exists(schema_name)	Checks whether a schema exists.
specify_conn_str([database_name, auth, password])	Specify the connection string for establishing a connection to a database.
table_exists(table_name[, schema_name])	Checks whether a table exists.
validate_column_names(table_name[, ...])	Validates column names for use in a SQL query statement.
varchar_to_geometry_dtype(table_name, ...[, ...])	Alters a VARCHAR column containing geometry data to a geometry data type.