sonnixgres 0.1.2

A Python module for simplifying interactions with PostgreSQL databases, with rich console output for better readability and debugging.

sonnixgres

A Python module for simplifying interactions with PostgreSQL databases, with rich console output for better readability and debugging.

Installation

Install sonnixgres using pip:

pip install sonnixgres

Usage

First, ensure that your PostgreSQL credentials are set as environment variables.

Create a .env file in your CDW with the following database enviornment variables:

DB_HOST=your_database_host
DB_DATABASE=your_database_name
DB_USER=your_database_username
DB_PASSWORD=your_database_password
DB_PORT=5432
DB_SCHEMA=your_database_schema
DB_TABLE=your_database_table

sonnixgres uses these variables to establish database connections.

from sonnixgres import create_connection, query_database, save_results_to_csv, create_and_populate_table, update_records, create_view
import pandas as pd

# Establish a database connection
connection = create_connection()

# Example usage of each function

Functions

create_connection()

Establishes a connection to the PostgreSQL database using credentials from environment variables.

query_database(connection, query, params=None, close_connection=True)

Executes a SQL query on the database and returns the result as a Pandas DataFrame.

• connection: The database connection object.
• query: SQL query string.
• params: Optional parameters for the SQL query.
• close_connection: Whether to close the database connection after executing the query.

save_results_to_csv(dataframe, filename)

Saves a Pandas DataFrame to a CSV file.

• dataframe: The DataFrame to be saved.
• filename: The name of the file where data will be saved.

create_and_populate_table(connection, table_name, dataframe, populate=False)

Creates a new table in the database. If a DataFrame is provided, it optionally populates the table with data from the DataFrame.

• connection: The database connection object.
• table_name: Name of the table to be created.
• dataframe: Optional. A pandas DataFrame whose data will be used to populate the table. If None, a table without predefined columns is created.
• populate: Optional. A boolean flag that indicates whether to populate the table with the data in the provided DataFrame. Effective only if dataframe is not None.

display_results_as_table(dataframe, max_column_width=50)

Displays a pandas DataFrame as a table in the console, with an internal row limit for display.

• dataframe: The pandas DataFrame to be displayed.

• max_column_width: Optional maximum width for each column in the table, defaulting to 50 characters.

• display_limit: Internal parameter set to 50, limits the number of rows displayed to the first 50 rows if the DataFrame exceeds this size.

  import pandas as pd
  from sonnixgres import display_results_as_table
  
  # Create or load a DataFrame
  df = pd.DataFrame({
      'Column1': [1, 2, 3, 4, 5],
      'Column2': ['A', 'B', 'C', 'D', 'E']
  })
  
  # Display the DataFrame in the console as a table
  display_results_as_table(df, max_column_width=20)
  

create_view(connection, view_name, view_query, close_connection=True)

Creates a new view in the database.

• connection: The database connection object.

• view_name: Name of the view to be created.

• view_query: SQL query string for creating the view.

• close_connection: Whether to close the database connection after creating the view.

  def display_results_as_table(dataframe: pd.DataFrame, max_column_width: int = 100) -> None:

  console = Console()

  display_limit = 100

  function

  This function displays a pandas DataFrame as a table in the console using the rich library's Console and Table classes. It is designed to neatly present the data with a limitation on the maximum width of each column and the number of rows displayed.

Parameters

• dataframe (pd.DataFrame): The pandas DataFrame that needs to be displayed.

• max_column_width (int, optional): The maximum width for each column in the table. Defaults to 50 characters.

  Returns

• The function returns None. It directly prints the table to the console.

  Behavior

• The function initializes a rich.console.Console object to handle the console output.

• It sets a display limit of 50 rows. If the DataFrame has more than 50 rows, it only displays the first 50 rows and prints a message indicating this limit. This message also suggests using the 'save_results_to_csv' function to view all data.

• The function creates a rich.table.Table object with headers (bold and magenta style) based on the DataFrame's columns.

• Each column is added to the table with a specified max_column_width to control the display width.

• Rows from the DataFrame (or limited DataFrame, if applicable) are added to the table.

• Finally, the table is printed to the console using the Console object.

  Example

  Here's an example of how to use the display_results_as_table function:

  import pandas as pd
  from rich.console import Console
  from rich.table import Table
  
  # Assuming 'df' is your pandas DataFrame
  display_results_as_table(df)
  

License

BSD License

Contributions

Contributions are welcome. Please open an issue or submit a pull request with your improvements.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.