pgmonkey - Reference Guide

CLI Commands Overview

YAML Configuration Options

Full YAML Configuration (Async Pool, SSL, Keepalives, etc.)

postgresql:
  connection_type: 'async_pool'  # Options: 'normal', 'pool', 'async', 'async_pool'
  connection_settings:
    connectionName: 'async_pool_connection'
    description: 'Default PostgreSQL connection setup'
    user: 'postgres'
    password: 'password'
    host: '192.168.0.183'
    port: 5432
    sslmode: 'verify-ca'  # Options: disable, allow, prefer, require, verify-ca, verify-full
    sslcert: '/home/ubuntu/mycerts/clientcerts/work/client.crt'  # Path to the client SSL certificate, if needed
    sslkey: '/home/ubuntu/mycerts/clientcerts/work/client.key'  # Path to the client SSL key, if needed
    sslrootcert: '/home/ubuntu/mycerts/clientcerts/work/ca.crt'  # Path to the root SSL certificate, if needed
    connect_timeout: 10  # Maximum wait for connection, in seconds
    application_name: 'myapp'
    keepalives: 1  # Enable TCP keepalives
    keepalives_idle: 60  # Time in seconds before sending a keepalive probe
    keepalives_interval: 15  # Time in seconds between keepalive probes
    keepalives_count: 5  # Maximum number of keepalive probes before closing the connection

  pool_settings:
    min_size: 5
    max_size: 20
    max_idle: 300  # Time in seconds a connection can remain idle before being closed
    max_lifetime: 3600  # Time in seconds a connection can be reused

  async_settings:
    # idle_in_transaction_session_timeout: '5000'  # Timeout for idle in transaction
    # statement_timeout: '30000'  # Cancel statements exceeding 30 seconds
    # lock_timeout: '10000'  # Timeout for acquiring locks
    # work_mem: '256MB'  # Memory for sort operations and more

  async_pool_settings:
    min_size: 5
    max_size: 20
    max_idle: 300
    max_lifetime: 3600

Basic YAML Configuration (No Pooling, Async, or SSL)

postgresql:
  connection_type: 'normal'  # Basic synchronous connection
  connection_settings:
    connectionName: 'basic_connection'
    user: 'postgres'
    password: 'password'
    host: 'localhost'
    port: 5432
    dbname: 'mydatabase'
    connect_timeout: 10  # Time in seconds to wait for connection
    application_name: 'basic_app'

YAML Configuration for Pooling (No Async or SSL)

postgresql:
  connection_type: 'pool'  # Connection pooling enabled
  connection_settings:
    connectionName: 'pool_connection'
    user: 'postgres'
    password: 'password'
    host: 'localhost'
    port: 5432
    dbname: 'mydatabase'
    connect_timeout: 10  # Time in seconds to wait for connection
    application_name: 'pool_app'

  pool_settings:
    min_size: 5  # Minimum number of connections in the pool
    max_size: 20  # Maximum number of connections in the pool
    max_idle: 300  # Maximum idle time in seconds
    max_lifetime: 3600  # Maximum time in seconds a connection can be reused

Working with PGConnectionManager

In pgmonkey, users interact with the PostgreSQL connection using the PGConnectionManager class, which simplifies both synchronous and asynchronous database connections based on YAML configuration files.

Basic Usage

import asyncio
from pgmonkey import PGConnectionManager

async def main():
    config_file = '/path/to/your/configs/pg_async.yaml'
    connection_manager = PGConnectionManager()

    # Check if connection should be asynchronous or synchronous
    if 'async' in config_file:
        connection = await connection_manager.get_database_connection(config_file)
    else:
        connection = connection_manager.get_database_connection(config_file)  # Sync connection

    try:
        # Handle async connection types
        if connection.connection_type in ['async', 'async_pool']:
            async with connection as conn:
                async with conn.cursor() as cur:
                    await cur.execute('SELECT version();')
                    print(await cur.fetchone())

        # Handle sync connection types
        else:
            with connection as conn:
                with conn.cursor() as cur:
                    cur.execute('SELECT version();')
                    print(cur.fetchone())

    finally:
        await connection.disconnect()


if __name__ == "__main__":
    asyncio.run(main())

Working with Transactions, Commit, Rollback, and Cursors

With pgmonkey, managing connections, transactions, handling commits, rollbacks, and using cursors is simplified for both synchronous and asynchronous connections. Here’s a detailed guide on how to work with these features.

Connection

Managing connections in pgmonkey is simplified with context managers, allowing you to use async with or with to handle both connection opening and closing automatically. This ensures that resources are properly managed, even if an error occurs during the execution of SQL statements.

Example Usage:

# Asynchronous Connection
    async with connection as conn:
        try:
            async with conn.cursor() as cur:
                await cur.execute('INSERT INTO my_table (name) VALUES (%s)', ('Alice',))
                await cur.execute('SELECT * FROM my_table WHERE name = %s', ('Alice',))
                print(await cur.fetchall())

# Synchronous Connection
    with connection as conn:
        try:
            with conn.cursor() as cur:
                cur.execute('INSERT INTO my_table (name) VALUES (%s)', ('Bob',))
                cur.execute('SELECT * FROM my_table WHERE name = %s', ('Bob',))
                print(cur.fetchall())

Using async with connection as conn or with connection as conn ensures that the connection is automatically closed after the block, even if an error occurs, preserving resources and simplifying connection management.

Transactions

Transactions allow you to execute a series of SQL statements as a single unit of work, ensuring that all operations complete successfully before the changes are committed. pgmonkey provides built-in transaction management, making it easier to handle transactions for all connection types.

Example Usage:

# Asynchronous Transaction
async with connection.transaction():
    async with connection.cursor() as cur:
        await cur.execute('INSERT INTO my_table (name) VALUES (%s)', ('John',))
        await cur.execute('SELECT * FROM my_table WHERE name = %s', ('John',))
        print(await cur.fetchall())

# Synchronous Transaction
with connection.transaction():
    with connection.cursor() as cur:
        cur.execute('INSERT INTO my_table (name) VALUES (%s)', ('Jane',))
        cur.execute('SELECT * FROM my_table WHERE name = %s', ('Jane',))
        print(cur.fetchall())

Using the transaction() method ensures that the operations inside the block are either fully committed or rolled back if an error occurs.

Commit

The commit() method is used to save the changes made during a transaction to the database. In the context of pgmonkey, explicit commits are not needed within a transaction block, as the context manager handles it automatically. However, you can use commit() outside of the transaction context when needed.

Example Usage:

# Manual commit (if not using transaction context)
async with connection.cursor() as cur:
    await cur.execute('UPDATE my_table SET name = %s WHERE id = %s', ('Doe', 1))
await connection.commit()

Note: If you are inside a transaction context, do not call commit() manually, as it will be handled automatically when the block exits.

Rollback

The rollback() method discards all changes made during a transaction, reverting the database to its state before the transaction began. This is useful when an error occurs, and you want to undo the changes made during the transaction.

Example Usage:

# Manual rollback (if not using transaction context)
try:
    async with connection.cursor() as cur:
        await cur.execute('DELETE FROM my_table WHERE id = %s', (1,))
        # Simulate an error
        raise Exception('Simulated error')
    await connection.commit()  # This will not be reached
except Exception as e:
    print(f"Error occurred: {e}. Rolling back...")
    await connection.rollback()

Cursors

Cursors are used to execute SQL queries and retrieve results from the database. pgmonkey provides a simplified way to work with cursors, supporting both async and sync connections.

Example Usage:

# Asynchronous Cursor
async with connection.cursor() as cur:
    await cur.execute('SELECT * FROM my_table WHERE name = %s', ('John',))
    rows = await cur.fetchall()
    for row in rows:
        print(row)

# Synchronous Cursor
with connection.cursor() as cur:
    cur.execute('SELECT * FROM my_table WHERE name = %s', ('Jane',))
    rows = cur.fetchall()
    for row in rows:
        print(row)

The cursor context automatically closes the cursor after the block is exited, ensuring that resources are released properly.

Key Points

• Transactions ensure atomicity of operations, making sure that a series of operations either all succeed or fail together.
• Automatic commit/rollback within a transaction() context eliminates the need for manual commit management in most scenarios.
• The cursor() method is straightforward for executing queries, and works seamlessly with both synchronous and asynchronous operations.

Example: Testing Pooling Capability

import asyncio
from pgmonkey import PGConnectionManager

# Function to test multiple async pool connections
async def test_multiple_async_pool_connections(config_file, num_connections):
    connection_manager = PGConnectionManager()
    connections = []

    # Acquire multiple async connections from the pool
    for _ in range(num_connections):
        connection = await connection_manager.get_database_connection(config_file)
        connections.append(connection)

    try:
        # Use each async connection
        for idx, connection in enumerate(connections):
            async with connection as conn:
                async with conn.cursor() as cur:
                    await cur.execute('SELECT version();')
                    version = await cur.fetchone()
                    print(f"Async Connection {idx + 1}: {version}")
    finally:
        # Disconnect all async connections
        for connection in connections:
            await connection.disconnect()

# Function to test multiple sync pool connections
def test_multiple_sync_pool_connections(config_file, num_connections):
    connection_manager = PGConnectionManager()
    connections = []

    # Acquire multiple sync connections from the pool
    for _ in range(num_connections):
        connection = connection_manager.get_database_connection(config_file)  # Sync call
        connections.append(connection)

    try:
        # Use each sync connection
        for idx, connection in enumerate(connections):
            with connection as conn:
                with conn.cursor() as cur:
                    cur.execute('SELECT version();')
                    version = cur.fetchone()
                    print(f"Sync Connection {idx + 1}: {version}")
    finally:
        # Disconnect all sync connections
        for connection in connections:
            connection.disconnect()

async def main():
    base_dir = '/path/to/your/connection/configs/'
    config_files = {
        'async_pool': base_dir + 'pg_async_pool.yaml',
        'pool': base_dir + 'pg_pool.yaml'
    }

    num_connections = 5  # Number of connections to checkout from the pool

    print("Testing async pool connections:")
    await test_multiple_async_pool_connections(config_files['async_pool'], num_connections)

    print("\nTesting sync pool connections:")
    test_multiple_sync_pool_connections(config_files['pool'], num_connections)

if __name__ == "__main__":
    asyncio.run(main())

Testing Your Connection Configurations with pgmonkey CLI

pgmonkey provides a powerful command-line interface (CLI) that allows users to quickly test their PostgreSQL connection configurations without needing to write or run any code. This feature is perfect for troubleshooting or verifying configurations before they are integrated into an application.

How to Use the CLI to Test a Connection

# Test your PostgreSQL connection using the pgconfig test command
pgmonkey pgconfig test --connconfig /path/to/your/config.yaml

In this example:

• --connconfig: Specifies the path to your YAML configuration file. This file should contain the necessary details for the connection (e.g., host, port, user, password, etc.).

Example Commands with Test Results

# Test for a normal connection
pgmonkey pgconfig test --connconfig ~/myconnectionconfigs/pg_normal.yaml

Output:
Connection successful:  (1,)
Connection closed.
Connection test completed successfully.

# Test for an asynchronous connection
pgmonkey pgconfig test --connconfig ~/myconnectionconfigs/pg_async.yaml

Output:
Async connection successful:  (1,)
Connection closed.
Connection test completed successfully.

# Test for a pooled connection
pgmonkey pgconfig test --connconfig ~/myconnectionconfigs/pg_pool.yaml

Output:
Pool connection successful:  (1,)
Pooling test successful: Acquired 6 connections out of a possible 20
Pooling tested successfully with 6 concurrent connections.
Connection test completed successfully.

# Test for an asynchronous pooled connection
pgmonkey pgconfig test --connconfig ~/myconnectionconfigs/pg_async_pool.yaml

Output:
Async pool connection successful:  (1,)
Pooling test successful: Acquired 6 connections out of a possible 20
Async pooling tested successfully with 6 concurrent connections.
Connection test completed successfully.

These commands will attempt to establish connections based on the details in your respective YAML configuration files. If successful, the output will show details such as the PostgreSQL server version and indicate whether connection pooling has been tested successfully for pooled configurations.

Other CLI Commands

The pgmonkey CLI also offers additional commands for managing PostgreSQL configurations and server settings:

• pgmonkey pgconfig create: Create a new configuration template.
• pgmonkey pgconfig generate-code: Generate example Python code to connect using your configuration file.
• pgmonkey pgserverconfig: Generate server configuration recommendations based on your YAML file.
• pgmonkey pgimport: Import data from a CSV or text file into a PostgreSQL table. Requires the table name, connection configuration, and the file to be imported.
• pgmonkey pgexport: Export data from a PostgreSQL table to a CSV file. Requires the table name and connection configuration, with an optional path for the output file.

For more information on how to use the CLI, run the --help command:

pgmonkey --help

Importing and Exporting Data

This section covers the usage of the pgmonkey command-line tool for importing and exporting data with PostgreSQL.

Importing Data

The pgimport command allows you to import CSV or text files into a specified PostgreSQL table. You must specify the table name and a connection configuration file, along with the file to import.

Usage:

pgmonkey pgimport --table <TABLE> --connconfig <CONFIG_FILE> --import_file 

pgmonkey pgimport --table public.my_table --connconfig /path/to/connection_config.yaml --import_file /path/to/data.csv

pgmonkey pgexport --table <TABLE> --connconfig <CONFIG_FILE> [--export_file <CSV_FILE>]

pgmonkey pgexport --table public.my_table --connconfig /path/to/connection_config.yaml --export_file /path/to/output.csv

$ pgmonkey pgconfig generate-code --filepath pg_async_pool.yaml

# Example Python code for an asynchronous pooled connection using pgmonkey

import asyncio
from pgmonkey import PGConnectionManager

async def main():
    connection_manager = PGConnectionManager()
    config_file_path = 'pg_async_pool.yaml'

    # Acquire 6 connections asynchronously from the pool
    connections = [await connection_manager.get_database_connection(config_file_path) for _ in range(6)]

    # Use each connection asynchronously
    for i, connection in enumerate(connections):
        async with connection as conn:
            async with conn.cursor() as cur:
                await cur.execute('SELECT 1;')
                result = await cur.fetchone()
                print(f"Connection {i+1}: {result}")

if __name__ == "__main__":
    asyncio.run(main())

$ pgmonkey pgconfig generate-code --filepath pg_async.yaml

# Example Python code for an asynchronous connection using pgmonkey

import asyncio
from pgmonkey import PGConnectionManager

async def main():
    connection_manager = PGConnectionManager()
    config_file_path = 'pg_async.yaml'

    # Get the PostgreSQL connection asynchronously
    connection = await connection_manager.get_database_connection(config_file_path)


    # Use the connection asynchronously
    async with connection as conn:
        async with conn.cursor() as cur:
            await cur.execute('SELECT 1;')
            result = await cur.fetchone()
            print(result)

if __name__ == "__main__":
    asyncio.run(main())

$ pgmonkey pgconfig generate-code --filepath pg_normal.yaml

# Example Python code for a normal synchronous connection using pgmonkey

from pgmonkey import PGConnectionManager

def main():
    connection_manager = PGConnectionManager()
    config_file_path = 'pg_normal.yaml'

    # Get the PostgreSQL connection
    connection = connection_manager.get_database_connection(config_file_path)


    # Use the connection synchronously
    with connection as conn:
        with conn.cursor() as cur:
            cur.execute('SELECT 1;')
            print(cur.fetchone())

if __name__ == "__main__":
    main()

$ pgmonkey pgconfig generate-code --filepath pg_pool.yaml

# Example Python code for a pooled synchronous connection using pgmonkey

from pgmonkey import PGConnectionManager

def main():
    connection_manager = PGConnectionManager()
    config_file_path = 'pg_pool.yaml'

    # Get the PostgreSQL connection from the pool
    connections = [connection_manager.get_database_connection(config_file_path) for _ in range(6)]


    # Use each connection
    for i, conn in enumerate(connections):
        with conn as connection:
            with connection.cursor() as cur:
                cur.execute('SELECT 1;')
                print(f"Connection {i+1}: {cur.fetchone()}")

if __name__ == "__main__":
    main()

pip3 install pgmonkey