mysql_connect_python returning null on a recursive function

Question

In a query, im using a recursive function, to generate monts between the first date (start_time) a user creates a session, and the current month.

WITH recursive Date_Ranges AS (
    SELECT (SELECT start_time FROM session WHERE user_id = 'sGcO42wMx1ZHXwZpZH3KWwWXzd13' ORDER BY start_time ASC LIMIT 1) AS d
    UNION ALL
    SELECT d + interval 1 month
    FROM Date_Ranges
    WHERE d < NOW()
)
SELECT * FROM Date_Ranges

This returnes the following table, when executed in ex DataGrip:

d
2023-01-19 13:41:29
2023-02-19 13:41:29
2023-03-19 13:41:29
2023-04-19 13:41:29
2023-05-19 13:41:29

But when i run the command again, through the connector, it returns null.

consumption = DB.query("""
    WITH recursive Date_Ranges AS (
        SELECT (SELECT start_time FROM user.session WHERE user_id = 'sGcO42wMx1ZHXwZpZH3KWwWXzd13' ORDER BY start_time ASC LIMIT 1) AS d
        UNION ALL
        SELECT d + interval 1 month
        FROM Date_Ranges
        WHERE d < NOW()
    )
    SELECT * FROM Date_Ranges
""")

Note that the DB.query() is a implementation, that creates a cursor, and returnes the fetchAll()

It seams like it is a issue in the mysql_connect_python

EDIT

I tried to reproduce the code, without my handler for DB.connections:

import os
import mysql.connector
import pprint as pprint

HOST = os.environ.get('AZURE_MYSQL_HOST')
USER = os.environ.get('AZURE_MYSQL_USER')
PASSWORD = os.environ.get('AZURE_MYSQL_PASSWORD')
DATABASE = os.environ.get('AZURE_MYSQL_NAME')
PORT = os.environ.get('AZURE_MYSQL_PORT')

query = """WITH recursive date_ranges AS (
    SELECT (SELECT start_time FROM session WHERE user_id = 'sGcO42wMx1ZHXwZpZH3KWwWXzd13' ORDER BY start_time ASC LIMIT 1) AS d
    UNION ALL
    SELECT d + interval 1 month
    FROM Date_Ranges
    WHERE d < NOW()
)
SELECT * FROM Date_Ranges"""

for use_pure in (True, False):
    with mysql.connector.connect(
        host= HOST,
        user= USER,
        passwd= PASSWORD,
        database= DATABASE,
        port= PORT,
        ssl_disabled=False,
        use_pure=use_pure
    ) as cnx:
        with cnx.cursor() as cur:
            cur.execute(query)
            res = cur.fetchall()
            print(use_pure)
            print(res)

and i gout the result as i estimated:

True
[(datetime.datetime(2023, 1, 19, 13, 41, 29),), (datetime.datetime(2023, 2, 19, 13, 41, 29),), (datetime.datetime(2023, 3, 19, 13, 41, 29),), (datetime.datetime(2023, 4, 19, 13, 41, 29),), (datetime.datetime(2023, 5, 19, 13, 41, 29),)]
False
[(datetime.datetime(2023, 1, 19, 13, 41, 29),), (datetime.datetime(2023, 2, 19, 13, 41, 29),), (datetime.datetime(2023, 3, 19, 13, 41, 29),), (datetime.datetime(2023, 4, 19, 13, 41, 29),), (datetime.datetime(2023, 5, 19, 13, 41, 29),)]

So the issue must be in my own implementation of the plugin.

My mistake, i have tried many things in the code. But i still get the same result as before. — BenjiBob
– BenjiBob, Commented Apr 20, 2023 at 15:30

Nuno Mariz · Accepted Answer · 2023-04-20 17:15:50Z

1

Can you provide the Connector/Python code part, or a reproducible code snippet?

The following example works as expected:

import mysql.connector

stmt = """
    WITH RECURSIVE cte (n) AS
    (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 5
    )
    SELECT * FROM cte;
"""

for use_pure in (True, False):
    with mysql.connector.connect(
        host="127.0.0.1",
        user="root",
        use_pure=use_pure,
    ) as cnx:
        with cnx.cursor() as cur:
            cur.execute(stmt)
            res = cur.fetchall()
            print(f"Using {use_pure=}: {res=}")

Result:

Using use_pure=True: res=[(1,), (2,), (3,), (4,), (5,)]
Using use_pure=False: res=[(1,), (2,), (3,), (4,), (5,)]

answered Apr 20, 2023 at 17:15

Nuno Mariz

5993 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BenjiBob Over a year ago

i tried to reproduce the code, without my implementation of the plugin, and i could get the data, i was looking for. Thanks for the help, now i will try to locate the issue, in the implementation

Collectives™ on Stack Overflow

mysql_connect_python returning null on a recursive function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related