read_data

pyhelpers.dbms.utils.read_data(db_instance, schema_name, table_name, sql_query=None, data_name='data', prefix='', suffix='', verbose=False, **kwargs)[source]

Read data from the project database.

Parameters:

  • db_instance (pyhelpers.dbms.PostgreSQL | pyhelpers.dbms.MSSQL) – A class instance for handling the database.

  • schema_name (str) – Name of the schema from which to load data.

  • table_name (str) – Name of the table from which to load data.

  • sql_query (str | None) – SQL query statement for custom data retrieval; defaults to None.

  • data_name (str) – Name identifier for the loaded data; defaults to "data".

  • prefix (str) – Prefix to prepend to data_name; defaults to ''.

  • suffix (str) – Suffix to append to data_name; defaults to ''.

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

  • kwargs – [Optional] Additional parameters for the method PostgreSQL.read_sql_query(), PostgreSQL.read_table() or MSSQL.read_table().

Returns:

Queried data as a dataframe.

Return type:

pandas.DataFrame | None

Examples:

>>> from pyhelpers.dbms.utils import import_data, read_data
>>> from pyhelpers.dbms import PostgreSQL
>>> from pyhelpers._cache import example_dataframe
>>> testdb_name = 'testdb'
>>> testdb = PostgreSQL(database_name=testdb_name, verbose=True)
Password (postgres@localhost:5432): ***
Creating a database: "testdb" ... Done.
Connecting postgres:***@localhost:5432/testdb ... Successfully.
>>> # Import an example dataframe into a table named "points"."England"
>>> example_df = example_dataframe()
>>> test_schema_name = 'points'
>>> test_table_name = 'England'
>>> test_data_name = 'the data of "England points"'
>>> import_data(
...     testdb, example_df, test_schema_name, test_table_name, test_data_name, index=True,
...     verbose=True)
To import the data of "England points" into "points"."England"?
 [No]|Yes: yes
Importing the data ... Done.
>>> # Retrieve the data
>>> example_df_ = read_data(
...     testdb, test_schema_name, test_table_name, data_name=test_data_name,
...     index_col='City', verbose=True)
Reading the data of "England points" from "points"."England" ... Done.
>>> # Check whether the retrieved data is the same as the original example dataframe
>>> example_df_.equals(example_df)
True
>>> # Delete the database "testdb"
>>> testdb.drop_database(verbose=True)
To drop the database "testdb" from postgres:***@localhost:5432
? [No]|Yes: yes
Dropping "testdb" ... Done.