Forums

Traceback (most recent call last):
  File "/home/retailreportingbenelux/Cycle Counts/Test.py", line 97, in redshift_2_connection
    conn = psycopg2.connect(
  File "/usr/local/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "retailops-custom-analytics.cr6qzgsjn4qe.eu-west-1.rds.amazonaws.com" (10.1.87.193), port 5432 failed: Connection timed out
        Is the server running on that host and accepting TCP/IP connections?
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/home/retailreportingbenelux/Cycle Counts/Test.py", line 122, in <module>
    df_trading_days = query_in_df(connection=redshift_2_connection(), query=sql_query)
  File "/home/retailreportingbenelux/Cycle Counts/Test.py", line 107, in redshift_2_connection
    raise Exception(f"Error connecting to Redshift database: {e}")
Exception: Error connecting to Redshift database: connection to server at "retailops-custom-analytics.cr6qzgsjn4qe.eu-west-1.rds.amazonaws.com" (10.1.87.193), port 5432 failed: Connection timed out
        Is the server running on that host and accepting TCP/IP connections?

def retailrep_uk_connection():
    # Connection details
    host = 'retailops-custom-analytics.cr6qzgsjn4qe.eu-west-1.rds.amazonaws.com'
    port = '5432'
    dbname = 'abc'
    user = 'user'
    password = '*******'

    # Create a connection to the RetailRepUK database
    try:
        conn = psycopg2.connect(
            dbname=dbname,
            user=user,
            password=password,
            port=port,
            host=host,
            sslmode='require'
        )
        return conn
    except psycopg2.Error as e:
        raise Exception(f"Error connecting to RetailRepUK database: {e}")

def query_in_df(connection, query):
    conn = connection
    max_retries = 0  # for a total of 3 tries including the first attempt
    retries = 0

    while retries <= max_retries:
        try:
            with conn.cursor() as cursor:
                cursor.execute(query)
                # Attempt to fetch data
                try:
                    rows = cursor.fetchall()
                    col_names = [desc[0] for desc in cursor.description]
                    df = pd.DataFrame(rows, columns=col_names)
                except psycopg2.ProgrammingError:
                    # If no data to fetch (e.g., for CREATE, INSERT, UPDATE)
                    df = pd.DataFrame()
                    print("Query executed successfully, but no data to return.")
                return df
        except psycopg2.Error as e:
            if retries == max_retries:
                # Exhausted retries, raising the last error
                raise Exception(f"Error executing query after {retries+1} attempts: {e}")
            else:
                # Wait for 30 seconds before retrying
                print(f"Attempt {retries+1} failed with error: {e}. Retrying in 30 seconds...")
                time.sleep(30)
                retries += 1
        except Exception as e:
            raise Exception(f"Unexpected error: {e}")
    # This point should not be reached due to the raise in the except block
    return pd.DataFrame()

# Check sales dates
sql_query = f"""
select          *
from            stock.sku_new
where           category_id in (107,207)
"""

df = query_in_df(connection=retailrep_uk_connection(), query=sql_query)

import socket

def test_network_connectivity(host, port):
    sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    try:
        result = sock.connect_ex((host, int(port)))
        if result == 0:
            print("Socket connection established.")
        else:
            print("Socket connection failed.")
    except Exception as e:
        print(f"Network test failed: {e}")
    finally:
        sock.close()


test_network_connectivity('retailops-custom-analytics.cr6qzgsjn4qe.eu-west-1.rds.amazonaws.com', 5432)