Hi there, i'm lost as i just paid for a full year paid plan, but now i can't seem to connnect to an external database. I have to reach 2 databasebases. It does work on my own local machine, but suddenly not on PythonAnywhere. Expecting this would not be an issue, but it is :(
Error:
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?
Code snippit:
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)
Also this code doesn't work and returns socket connection failure:
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)
By the way, this happens with 2 seperate external databases, let's call them database1 and database2, this is my findings:
My local pc at home on private wifi network: - Database1 & 2 works fine with said code.
On PythonAnywhere: - Database 1 and 2 doesn't work, get the tcp/ip errors
On my local pc on company (wifi) network: - Database 1 works - Database 2 doesn't work, unless i switch to different network like the guest network or a mobile hotspot
Please help, i'm lost! :)