Hello everyone I have a problem concerning the connection of

Question

Hello everyone I have a problem concerning the connection of sqlalchemy with mysqlserver which occurs every morning around 9 00 AM and then it does not reproduce Knowing that my database exists and all is good here is the code used in `catalog yml` ``` mysql amp 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 lt lt mysql sql select from underwriter insurers table ccns lt lt mysql sql select from underwriter ccns table departments lt lt 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 · Answer

hey < Rachid Cherqaoui> let me see if I understand your code works well usually but at 09 00 AM it fails and the failure cannot be reproduced at any other time

Rachid Cherqaoui · Answer

hello < Juan Luis> yes every morning it generates an error of this kind and then it works well for the rest of the day

Juan Luis · Answer

my first thought is that I don t think Kedro is the root cause here could be wrong of course computers are evil but I ll suggest trying to set a cron job that attempts a connection with the same database at the same time but without using Kedro at all maybe do a `SELECT 1` or something like that if the dummy connection works but the Kedro job fails we can keep debugging

Rachid Cherqaoui · Answer

bah in fact in another project when I use a python script and I make the connection using sqlalchemy I got the same error when I use a single connection for all the recovered tables but when I make a connection create connexion and close for each table I don t get this error so I don t know how I could do the same in the yml files

Rachid Cherqaoui · Answer

```with 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```

Rachid Cherqaoui · Answer

my problem is that I don t want to use python cript I want to find a solution directly on the catalog