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, **kwargs)

Read data from a table.

Parameters

table_name (str) – name of a table in the currently-connected database
schema_name (str or None) – name of a schema, defaults to DEFAULT_SCHEMA when schema_name=None
column_names (list or tuple or None) – column name(s), defaults to all columns when column_names=None
conditions (str or None) – conditions in a SQL query statement, defaults to None
chunk_size (int or None) – number of rows to include in each chunk (if specified), defaults to None
save_as (str or None) – file extension (if specified) for saving table data locally, defaults to None
data_dir (str or None) – directory where the table data is to be saved, defaults to None
kwargs – [optional] parameters of pandas.read_sql

Returns

data of the queried table

Return type

pandas.DataFrame

Examples:

>>> from pyhelpers.dbms import MSSQL
>>> from pyhelpers._cache import example_dataframe

>>> mssql = MSSQL()
Connecting <server_name>@localhost:1433/master ... Successfully.

>>> mssql.get_table_names()
['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, table_name=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(table_name=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.