Issue
I'm trying to read sheets and insert them into postgresql to make the staging tables in my etl_table.
The function was working, but now I'm getting a error. I don't know what my problem is:
main.ipynb
db_session = database_handler.create_connection('config.json')
data_handler.read_dataset_create_tables_and_insert_data(db_session)
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
ERROR:root:An error occurred: <urlopen error unknown url type: https>
2023-11-28 00:50:23.483310 - Failed to connect to database (database_handler.py) ##### <urlopen error unknown url type: https>
2023-11-28 00:50:23.485303 - Failed to connect to database (database_handler.py) ##### <urlopen error unknown url type: https>
I'm using this code:
def read_sheet_as_dataframe(sheet_info):
file_type = sheet_info["type"]
file_config_parameter = sheet_info["config"]
sheet_name = sheet_info["sheet_name"]
df = data_handler.read_data_as_dataframe(file_type, file_config_parameter)
return df, sheet_name
def read_dataset_create_tables_and_insert_data(db_session):
sheets_info = [
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=countries', "sheet_name": "countries"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=avg_temperature', "sheet_name": "avg_temperature"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=prevalence_of_undernourishment', "sheet_name": "prevalence_of_undernourishment"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=disaster_types', "sheet_name": "disaster_types"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=gdp', "sheet_name": "gdp"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=world_population', "sheet_name": "world_population"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=natural_disasters', "sheet_name": "natural_disasters"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=disaster_magnitude', "sheet_name": "disaster_magnitude"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=worldcities', "sheet_name": "worldcities"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=share_without_improved_water', "sheet_name": "share_without_improved_water"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=magnitude_scale', "sheet_name": "magnitude_scale"},
{"type": lookups.FileType.CSV, "config": 'https://docs.google.com/spreadsheets/d/18GUCOh6BzZ6eLeM1fbLGrnPpW2DeUUal-jqci93w6R8/gviz/tq?tqx=out:csv&sheet=valuable_country_data', "sheet_name": "valuable_country_data"},
]
for sheet_info in sheets_info:
df, sheet_name = read_sheet_as_dataframe(sheet_info)
create_table_and_insert_data(db_session, df, sheet_name)
so basically i'm trying to read my sheets and insert them in the database it was working up until i got this error , if anyone has a fix please i would really appreciate it . Thank you by advance !
and here is the config.json
{
"db_host" : "localhost",
"db_name" : "Pluto",
"db_user" : "postgres",
"db_pass" : "Laptop2018",
"port_id" : "5433",
"countries": "kaggle",
"share_without_improved_water": "kaggle",
"avg_temperature": "kaggle",
"worldcities": "Simplemaps",
"gdp": "UNdata",
"prevalence_of_undernourishment": "OurWorldInData",
"natural_disasters": "emdat",
"valuable_country_data": "OECD",
"world_population":"kaggle",
"disaster_types":"manual",
"disaster_magnitude":"manual",
"magnitude_scale":"manual"
}
and this is how i create the connection :
def create_connection(config_file):
db_session = None
try:
config_data = file_handler.read_config(config_file)
if config_data is not None:
db_host = config_data.get("db_host")
db_name = config_data.get("db_name")
db_user = config_data.get("db_user")
db_pass = config_data.get("db_pass")
db_port = config_data.get("port_id")
if db_host and db_name and db_user and db_pass:
db_session = psycopg2.connect(
host=db_host,
database=db_name,
user=db_user,
password=db_pass,
port=db_port
)
else:
print("Missing database connection parameters in the config file.")
else:
print("Failed to read the configuration file.")
except Exception as error:
prefix = lookups.ErrorHandling.DB_CONNECTION_ERROR.value
suffix = str(error)
print_error_console(suffix, prefix)
log_error(f'An error occurred: {str(error)}')
finally:
return db_session
Solution
I would recommend that you create a clean Python environment because this could be related to SSL installation (see relevant link).
It would also help answering your question if you include additional details how you create the connection.
Answered By - SenseiH
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.