MSSQL.read_table¶

MSSQL.read_table(table_name, schema_name=None, column_names=None, conditions=None, chunk_size=None, save_as=None, data_dir=None, save_args=None, verbose=False, **kwargs)[source]¶

Read data from a specified table.

Parameters:

table_name (str) – Name of the table to read data from in the currently-connected database.
schema_name (str | None) – Name of the schema where the table resides; defaults to DEFAULT_SCHEMA when schema_name=None.
column_names (list | tuple | None) – Names of columns to retrieve data from; defaults to all columns when column_names=None.
conditions (str | None) – Conditions to apply in the SQL query statement; defaults to None.
chunk_size (int | None) – Number of rows to retrieve in each chunk (if specified); defaults to None.
save_as (str | None) – File extension (if specified) for saving table data locally; defaults to None.
data_dir (str | None) – Directory path where the table data should be saved; defaults to None.
save_args (dict | None) – Optional parameters for the function pyhelpers.store.save_data(); defaults to None.
verbose (bool | int) – Whether to print relevant information in the console; defaults to False.
kwargs – [Optional] Additional parameters for the function pandas.read_sql().

Returns:

Data of the queried table from the currently-connected database.

Return type:

pandas.DataFrame

Examples:

>>> from pyhelpers.dbms import MSSQL
>>> from pyhelpers._cache import example_dataframe
>>> mssql = MSSQL(verbose=True)
Connecting <server_name>@localhost:1433/master ... Successfully.
>>> mssql.get_table_names()
{'dbo': ['MSreplication_options',
  'spt_fallback_db',
  'spt_fallback_dev',
  'spt_fallback_usg',
  'spt_monitor']}
>>> mssql.read_table(table_name='MSreplication_options')
          optname  value  ...  revision  install_failures
0   transactional   True  ...         0                 0
1           merge   True  ...         0                 0
2  security_model   True  ...         0                 0
[3 rows x 6 columns]
>>> mssql.read_table(table_name='MSreplication_options', column_names=['optname'])
          optname
0   transactional
1           merge
2  security_model
>>> # Create a new database for testing
>>> testdb = MSSQL(database_name='testdb')
Creating a database: [testdb] ... Done.
Connecting <server_name>@localhost:1433/testdb ... Successfully.
>>> example_df = example_dataframe()
>>> example_df
            Longitude   Latitude
City
London      -0.127647  51.507322
Birmingham  -1.902691  52.479699
Manchester  -2.245115  53.479489
Leeds       -1.543794  53.797418
>>> test_table_name = 'example_df'
>>> testdb.import_data(example_df, test_table_name, index=True, verbose=2)
To import data into [dbo].[example_df] at <server_name>@localhost:1433/testdb
? [No]|Yes: yes
Importing the data into the table [dbo].[example_df] ... Done.
>>> # Retrieve the imported data
>>> example_df_ret = testdb.read_table(test_table_name, index_col='City')
>>> example_df_ret
            Longitude   Latitude
City
London      -0.127647  51.507322
Birmingham  -1.902691  52.479699
Manchester  -2.245115  53.479489
Leeds       -1.543794  53.797418
>>> # Drop/Delete the testing database [testdb]
>>> testdb.drop_database(verbose=True)
To drop the database [testdb] from <server_name>@localhost:1433
? [No]|Yes: yes
Dropping [testdb] ... Done.