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 table.
- Parameters:
table_name (str) – name of a table in the currently-connected database
schema_name (str | None) – name of a schema, defaults to
DEFAULT_SCHEMA
whenschema_name=None
column_names (list | tuple | None) – column name(s), defaults to all columns when
column_names=None
conditions (str | None) – conditions in a SQL query statement, defaults to
None
chunk_size (int | None) – number of rows to include 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 where the table data is to be saved, defaults to
None
save_args (dict | None) – optional parameters of the function
pyhelpers.store.save_data()
, defaults toNone
verbose (bool | int) – whether to print relevant information in console, defaults to
False
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() {'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, 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.
See also
Examples for the method
import_data()
.