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'
; whenhost=None
(default), it is initialized as'localhost'
port (int or None) – listening port; when
port=None
(default), it is initialized as1433
(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 Authenticationpassword (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 serverdatabase_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 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.
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.