MSSQL

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

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.

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[, ...])

Add a primary key constraint to a table.

connect_database([database_name, verbose])

Establish a connection to a database.

create_connection([database_name, mode])

Create a SQLAlchemy connection to a Microsoft SQL Server database.

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 for connecting to a SQL Server database.

create_schema(schema_name[, verbose])

Create a schema.

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

Create a table with specified columns.

database_exists([database_name])

Check whether a database exists.

disconnect_database([database_name, verbose])

Disconnect from 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, ...])

Retrieve information about columns of a table.

get_column_names(table_name[, schema_name])

Retrieve column names of a table.

get_database_names([names_only])

Get names of all existing databases.

get_file_tables([names_only])

Retrieve information about FileTables from the database (if available).

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

Retrieve the primary keys of table(s) from the currently-connected database.

get_row_count(table_name[, schema_name])

Get the row count of a table.

get_schema_info([names_only, include_all, ...])

Retrieve information about existing schemas.

get_table_names([schema_name, verbose])

Get names of all tables stored in one or multiple schemas.

has_dtypes(table_name, dtypes[, schema_name])

Check whether a table contains columns of specified 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, ...)

Execute a SQL query and read the result into a DataFrame.

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

Read data from a specified table.

schema_exists(schema_name)

Check 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])

Check whether a table exists.

validate_column_names(table_name[, ...])

Validate column names for use in a SQL query statement.

varchar_to_geometry_dtype(table_name, ...[, ...])

Alter a VARCHAR column containing geometry data to a geometry data type.