MSSQL.create_table

MSSQL.create_table(table_name, column_specs, schema_name=None, verbose=False)[source]

Create a table with specified columns.

Parameters:

  • table_name (str) – Name of the table to be created.

  • column_specs (str) – Specifications for each column of the table.

  • schema_name (str | None) – Name of the schema where the table will be created; defaults to DEFAULT_SCHEMA (i.e. 'dbo') if schema_name=None.

  • verbose (bool | int) – Whether to print relevant information to the console; defaults to False.

Examples:

>>> from pyhelpers.dbms import MSSQL
>>> testdb = MSSQL(database_name='testdb')
Creating a database: [testdb] ... Done.
Connecting <server_name>@localhost:1433/testdb ... Successfully.
>>> # Create a new table named 'test_table'
>>> tbl_name = 'test_table'
>>> col_spec = 'col_name_1 INT, col_name_2 varchar(255)'
>>> testdb.create_table(table_name=tbl_name, column_specs=col_spec, verbose=True)
Creating a table: [dbo].[test_table] ... Done.
>>> testdb.table_exists(table_name=tbl_name)
True
>>> testdb.get_column_names(table_name=tbl_name)
['col_name_1', 'col_name_2']
>>> test_tbl_col_info = testdb.get_column_info(table_name=tbl_name, as_dict=False)
>>> test_tbl_col_info.head()
                    column_0    column_1
TABLE_CATALOG         testdb      testdb
TABLE_SCHEMA             dbo         dbo
TABLE_NAME        test_table  test_table
COLUMN_NAME       col_name_1  col_name_2
ORDINAL_POSITION           1           2
>>> testdb.validate_column_names(table_name=tbl_name)
'"col_name_1", "col_name_2"'
>>> # Drop the table [dbo].[test_table]
>>> testdb.drop_table(table_name=tbl_name, verbose=True)
To drop the table [dbo].[test_table] from <server_name>@localhost:1433/testdb
? [No]|Yes: yes
Dropping [dbo].[test_table] ... Done.
>>> # Delete the database [testdb]
>>> testdb.drop_database(verbose=True)
To drop the database [testdb] from <server_name>@localhost:1433
? [No]|Yes: yes
Dropping [testdb] ... Done.