C3_W3_Assignment.ipynb

Pls help, I am having a problem with starting mysql db because the value of the DBHOST= in the env is not given!

Hello @John_Ross,
My env looks like this:

DBHOST=
DBPORT=3306
DBNAME=sakila_star
DBUSER=
DBPASSWORD=

I could conitnue without setting those variables. Could you try with the rest of the following cells and see. Thank you

Thank you for prompt response.
I have reset the env to default value, as follows:
DBHOST=
DBPORT=3306
DBNAME=sakila_star
DBUSER=
DBPASSWORD=

But I am still encountering the same errors:

I have also modified the following code:
import os

from dotenv import load_dotenv

Load environment variables from the .env file

load_dotenv(‘./src/env’, override=True)

Retrieve environment variables

DBHOST = os.getenv(‘DBHOST’)

DBPORT = os.getenv(‘DBPORT’, ‘3306’)

DBNAME = os.getenv(‘DBNAME’)

DBUSER = os.getenv(‘DBUSER’)

DBPASSWORD = os.getenv(‘DBPASSWORD’)

Construct the connection URL

connection_url = f’mysql+pymysql://{DBUSER}:{DBPASSWORD}@{DBHOST}:{DBPORT}/{DBNAME}’

print(connection_url) # Print the connection URL for debugging

Load the SQL extension

%load_ext sql

Connect to the database

%sql {connection_url}

Hello @John_Ross,
You need these variables:
DBHOST = socket.gethostname()
DBPORT= os.getenv(‘DBPORT’) #no need for ‘3306’ here

If you want to update your notebook and get a new one use this link . It will save time troubleshooting. Thank you

Hi Georgios

I have tried all that including updating my notebook but still get the same error message as follows:

import os
import socket

from dotenv import load_dotenv

load_dotenv(‘./src/env’, override=True)

DBHOST = socket.gethostname()
DBPORT = os.getenv(‘DBPORT’)
DBNAME = os.getenv(‘DBNAME’)
DBUSER = os.getenv(‘DBUSER’)
DBPASSWORD = os.getenv(‘DBPASSWORD’)

connection_url = f’mysql+pymysql://{DBUSER}:{DBPASSWORD}@{DBHOST}:{DBPORT}/{DBNAME}’

%sql {connection_url}

Output exceeds the size limit. Open the full output data in a text editor
Traceback (most recent call last):
File “/home/coder/miniconda/lib/python3.12/site-packages/sqlalchemy/engine/base.py”, line 146, in init
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File “/home/coder/miniconda/lib/python3.12/site-packages/sqlalchemy/engine/base.py”, line 3300, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File “/home/coder/miniconda/lib/python3.12/site-packages/sqlalchemy/pool/base.py”, line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/home/coder/miniconda/lib/python3.12/site-packages/sqlalchemy/pool/base.py”, line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/home/coder/miniconda/lib/python3.12/site-packages/sqlalchemy/pool/base.py”, line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File “/home/coder/miniconda/lib/python3.12/site-packages/sqlalchemy/pool/impl.py”, line 179, in _do_get
with util.safe_reraise():
File “/home/coder/miniconda/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py”, line 146, in exit
raise exc_value.with_traceback(exc_tb)
File “/home/coder/miniconda/lib/python3.12/site-packages/sqlalchemy/pool/impl.py”, line 177, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File “/home/coder/miniconda/lib/python3.12/site-packages/sqlalchemy/pool/base.py”, line 390, in _create_connection
return _ConnectionRecord(self)

Connection info needed in SQLAlchemy format, example:
postgresql://username:password@hostname/dbname
or an existing connection: dict_keys()

DBHOST=
DBPORT=3306
DBNAME=sakila_star
DBUSER=
DBPASSWORD=

@John_Ross, I could reproduce the same error if I change the DBNAME,DBUSER or DBPASSWORD. Your code looks good and should work as it is, could you try a different browser (clear cache) see if it persists. Thank you

Hi Georgios

I have now tried clearing the browser and I have used both Chrome and Brave browsers but the Error message persists. Also I have done a reboot but to no avail.

Surely, the author should have anticipated this problem since we are supported to supply a value for the DBHOST!

Hi John_Ross, No we are not supported to supply any value for the first two cells (only for the graded exercises). The coursera troubleshooter shows the following as instructed:

  • Making sure your ad-blocking software is turned off on your browser or network
  • Making sure you are logged in with the correct account
  • Clearing your cache and cookies
  • Using a different browser or new browser window
  • Check if your computer’s clock is set accurately

I could reproduce the error without making any changes to the env file which is provided and not mentioned to alter it. Hope it helps

Could you make sure you followed this steps like the link I shared:

  1. Rename the existing notebook so that you have a copy for reference. If you don’t need delete the existing notebook.

  2. Click on the help button in the top menu bar (button that looks like a ?)

  3. Click on “Get latest version” button

Note: If you didn’t rename or delete their notebook in step 1, then step 3 will have no effect. Hope it helps

@John_Ross Do you get an output if you print(DBHOST), thanks

I have done all the listed checks but keep getting the same errors.

  1. Ad block OFF

  2. Logged with right account

  3. Cleared cache and cookies

  4. Used 3 different browsers namely Chrome, Safari, Brave so far

  5. Checked computer clock and it is ok

  6. Renamed file and fetched the latest version

Surely, this should be straight forward. Just checked the instructions and it says ’ it’s essential to obtain your credentials and establish a connection to the MySQL database, which is done in the src/env file.’

It says "it’s essential to obtain your credentials and establish a connection to the MySQL database, which is done in the src/env file.
BUT we are not supposed to change anything in those cells to pass. Could you try getting the socket.gethostname() and print(DBHOST) it should provide an alphanumeric output. Sorry for your trouble and thanks

this is the output that I get:

DBHOST: 8836a58ad578

ValueError Traceback (most recent call last)
Cell In[3], line 20
18 # Check if any required environment variables are missing
19 if not all([DBPORT, DBNAME, DBUSER, DBPASSWORD]):
—> 20 raise ValueError(“One or more required environment variables are missing.”)
22 # Construct the connection URL
23 connection_url = f’mysql+pymysql://{DBUSER}:{DBPASSWORD}@{DBHOST}:{DBPORT}/{DBNAME}’

ValueError: One or more required environment variables are missing.

Hello @John_Ross, I couldnt reproduce this error and reported it. Thanks for the detailed error response. I hope its not too long to respond sorry fo the incovenience

@John_Ross could you check if you are getting something similar in the variables tab:

Hey Georgios, thanks for your help. I had to go an around about way, I first established that I had a connection and then I made sure that I could access the sakira db and voila I was in, so its all done and dusted. many thanks:)

1 Like