Rachid Cherqaoui
07/28/2023, 8:54 AM_mysql : &mysql
type: pandas.SQLQueryDataSet
credentials:
con: mysql+mysqlconnector://${mysql_connect.username}:${mysql_connect.password}@${mysql_connect.host}:${mysql_connect.port}/${mysql_connect.database}
table_insurers:
<<: *mysql
sql: select * from underwriter_insurers
table_ccns:
<<: *mysql
sql: select * from underwriter_ccns
table_departments:
<<: *mysql
sql: select * from underwriter_departments
and this is the error produced :
2023-07-26 09:33:48 - src.api.tarificateur_compte - ERROR - An error occurred in tarificateur_compte():
Traceback (most recent call last):
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
context = constructor(
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1346, in _init_statement
self.cursor = self.create_cursor()
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1530, in create_cursor
return self.create_default_cursor()
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1533, in create_default_cursor
return self._dbapi_connection.cursor()
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1494, in cursor
return self.dbapi_connection.cursor(*args, **kwargs)
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 678, in cursor
raise OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/kedro/io/core.py", line 210, in load
return self._load()
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/kedro_datasets/pandas/sql_dataset.py", line 512, in _load
return pd.read_sql_query(con=engine, **load_args)
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/pandas/io/sql.py", line 467, in read_sql_query
return pandas_sql.read_query(
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/pandas/io/sql.py", line 1736, in read_query
result = self.execute(sql, params)
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/pandas/io/sql.py", line 1560, in execute
return self.con.exec_driver_sql(sql, *args)
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1772, in exec_driver_sql
ret = self._execute_context(
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
self._handle_dbapi_exception(
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2326, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
context = constructor(
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1346, in _init_statement
self.cursor = self.create_cursor()
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1530, in create_cursor
return self.create_default_cursor()
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1533, in create_default_cursor
return self._dbapi_connection.cursor()
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1494, in cursor
return self.dbapi_connection.cursor(*args, **kwargs)
File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 678, in cursor
raise OperationalError("MySQL Connection not available.")
sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available.
Can anyone help me fix this problem because I have tried everything I can but I have not managed to solve it, thank you in advance.Juan Luis
07/28/2023, 8:56 AMRachid Cherqaoui
07/28/2023, 8:57 AMJuan Luis
07/28/2023, 9:14 AMSELECT 1
or something like that. if the dummy connection works but the Kedro job fails, we can keep debugging.Rachid Cherqaoui
07/28/2023, 9:19 AMwith this code i did't get the error MYSQL_DB_USER = os.getenv('MYSQL_DB_USER')
MYSQL_DB_PWD = os.getenv('MYSQL_DB_PWD')
MYSQL_DB_URL = os.getenv('MYSQL_DB_URL')
MYSQL_DB_PORT = os.getenv('MYSQL_DB_PORT')
MYSQL_DB_SCHEMA = os.getenv('MYSQL_DB_SCHEMA')
url_object = URL.create("mysql+pymysql",
username = MYSQL_DB_USER,
password = MYSQL_DB_PWD,
host = MYSQL_DB_URL,
port = MYSQL_DB_PORT,
database = MYSQL_DB_SCHEMA
)
def underwiter_offers():
sqlEngine = create_engine(url_object)
dbConnection = sqlEngine.connect()
mySql_Dump_Table_Query = text("""SELECT underwriter_offers.id, average_age, hospital_price,
specialist_price, dental_price,optic_price, osteo_price,
ccn_id, free, contribution_structure_id, pack_cotisation_id as pack_cotisation,
status_id, area_id, date, insurer_id, price
FROM underwriter_offers
JOIN underwriter_rates ON underwriter_rates.offer_id=underwriter_offers.id
JOIN underwriter_departments ON underwriter_offers.department_id=underwriter_departments.id
JOIN underwriter_ccns ON underwriter_offers.ccn_id = underwriter_ccns.id
WHERE underwriter_rates.deleted_at is NULL
AND underwriter_offers.deleted_at is NULL;""")
frame_offers = pd.read_sql(mySql_Dump_Table_Query, dbConnection)
pd.set_option('display.expand_frame_repr', False)
dbConnection.close()
return frame_offers
def underwiter_ccns():
sqlEngine = create_engine(url_object)
dbConnection = sqlEngine.connect()
mySql_ccns_Table_Query = text(""" SELECT * FROM underwriter_ccns;""")
frame_ccns = pd.read_sql(mySql_ccns_Table_Query, dbConnection)
pd.set_option('display.expand_frame_repr', False)
dbConnection.close()
frame_ccns = frame_ccns.astype({'id' : str})
return frame_ccns