[Python 3.X] FastAPI et requête Oracle

FastAPI et requête Oracle

Bonjour,

Dans mon API FastAPI connectée à une base Oracle, j'ai une requête qui récupère les champs d'une table, dont un champ géographique que je récupère en GML :

Code:

1
2
SELECT sit_csit, sit_nom, sit_type, SDO_UTIL.TO_GMLGEOMETRY(sge_geometry) geom
FROM sit

Cette requête fonctionne parfaitement quand je la lance dans SQL Developer.

Mais quand je la lance dans FastAPI, j'ai le message d'erreur :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
Traceback (most recent call last):
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\encoders.py", line 325, in jsonable_encoder
    data = dict(obj)
TypeError: 'oracledb.thick_impl.ThickDbObjectImpl' object is not iterable
 
During handling of the above exception, another exception occurred:
 
Traceback (most recent call last):
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\encoders.py", line 330, in jsonable_encoder
    data = vars(obj)
TypeError: vars() argument must have __dict__ attribute
 
The above exception was the direct cause of the following exception:
 
Traceback (most recent call last):
  File "C:\monrepertoire\.venv\Lib\site-packages\starlette\middleware\errors.py", line 164, in __call__
    await self.app(scope, receive, _send)
  File "C:\monrepertoire\.venv\Lib\site-packages\starlette\middleware\exceptions.py", line 63, in __call__
    await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)
  File "C:\monrepertoire\.venv\Lib\site-packages\starlette\_exception_handler.py", line 53, in wrapped_app
    raise exc
  File "C:\monrepertoire\.venv\Lib\site-packages\starlette\_exception_handler.py", line 42, in wrapped_app
    await app(scope, receive, sender)
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\middleware\asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "C:\monrepertoire\.venv\Lib\site-packages\starlette\routing.py", line 716, in __call__
    await self.middleware_stack(scope, receive, send)
  File "C:\monrepertoire\.venv\Lib\site-packages\starlette\routing.py", line 736, in app
    await route.handle(scope, receive, send)
  File "C:\monrepertoire\.venv\Lib\site-packages\starlette\routing.py", line 290, in handle
    await self.app(scope, receive, send)
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\routing.py", line 123, in app
    await wrap_app_handling_exceptions(app, request)(scope, receive, send)
  File "C:\monrepertoire\.venv\Lib\site-packages\starlette\_exception_handler.py", line 53, in wrapped_app
    raise exc
  File "C:\monrepertoire\.venv\Lib\site-packages\starlette\_exception_handler.py", line 42, in wrapped_app
    await app(scope, receive, sender)
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\routing.py", line 109, in app
    response = await f(request)
               ^^^^^^^^^^^^^^^^
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\routing.py", line 409, in app
    content = await serialize_response(
              ^^^^^^^^^^^^^^^^^^^^^^^^^
    ...<9 lines>...
    )
    ^
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\routing.py", line 277, in serialize_response
    return jsonable_encoder(response_content)
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\encoders.py", line 304, in jsonable_encoder
    jsonable_encoder(
    ~~~~~~~~~~~~~~~~^
        item,
        ^^^^^
    ...<7 lines>...
        sqlalchemy_safe=sqlalchemy_safe,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\encoders.py", line 290, in jsonable_encoder
    encoded_value = jsonable_encoder(
        value,
    ...<4 lines>...
        sqlalchemy_safe=sqlalchemy_safe,
    )
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\encoders.py", line 334, in jsonable_encoder
    return jsonable_encoder(
        data,
    ...<7 lines>...
        sqlalchemy_safe=sqlalchemy_safe,
    )
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\encoders.py", line 290, in jsonable_encoder
    encoded_value = jsonable_encoder(
        value,
    ...<4 lines>...
        sqlalchemy_safe=sqlalchemy_safe,
    )
  File "C:\monrepertoire\.venv\Lib\site-packages\fastapi\encoders.py", line 333, in jsonable_encoder
    raise ValueError(errors) from e
ValueError: [TypeError("'oracledb.thick_impl.ThickDbObjectImpl' object is not iterable"), TypeError('vars() argument must have __dict__ attribute')]

J'ai le même message d'erreur quand j'essaie de transformer ma géométrie en WKT au lieu du GML.

Toutes mes autres requêtes non géographiques fonctionnent parfaitement avec FastAPI.

Qu'est ce qui cloche ?

Merci,

Nico

Voici ma fonction de endpoint dans FastAPI :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
def get_sites():
    try:
        with pool_bigood.acquire() as connection:
            with connection.cursor() as cursor:
                cursor.execute("""
                               SELECT sit_csit, sit_nom, sit_type, SDO_UTIL.TO_WKTGEOMETRY(sge_geometry) geom
                               FROM sit
                               WHERE sit_csit > 20
                               """,
                )
                result = cursor.fetchall()
                if result:
                    print(result)
                    objects = [
                        {
                            "sit_csit": data[0],
                            "sit_nom": data[1],
                            "sit_type": data[2],
                            "geom": data[3]
                        }
                        for data in result
                    ]
                    print(objects)
                    return objects
                else:
                    raise HTTPException(status_code=404, detail=f"Sites not found")
    except oracledb.Error as e:
        raise HTTPException(status_code=500, detail=f"Database error: {e}")

Si je fait un print du résultat de ma requête, j'ai :

Code:

1
2
[(32, '001-092', 'JURI', <oracledb.LOB object at 0x000001BCB0AA6A50>), (33, '001-093', 'JURI', <oracledb.LOB object at 0x000001BCB0A3AC10>), (34, '001-094', 'JURI', <oracledb.LOB objec
t at 0x000001BCB0A39A90>), (35, '003-002', 'JURI', <oracledb.LOB object at 0x000001BCB0A762C0>), (36, '003-003', 'JURI', <oracledb.LOB object at 0x000001BCB0A76520>), (37, '003-004', 'JURI', <oracledb.LOB object at 0x000001BCB0A6E9F0>)]

Les géométries restent au format Objet Oracle, et ne sont pas au format WKT...

Pourquoi ?

Nico

Hello,

Petite erreur,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
def get_sites():
    try:
        with pool_bigood.acquire() as connection:
            with connection.cursor() as cursor:
                cursor.execute("""
                    SELECT sit_csit, sit_nom, sit_type, 
                           SDO_UTIL.TO_WKTGEOMETRY(sge_geometry) geom
                    FROM sit
                    WHERE sit_csit > 20
                """)
                result = cursor.fetchall()
 
                if result:
                    objects = [
                        {
                            "sit_csit": data[0],
                            "sit_nom": data[1],
                            "sit_type": data[2],
                            "geom": data[3].read() if data[3] else None  # Lecture du CLOB
                        }
                        for data in result
                    ]
                    return objects
                else:
                    raise HTTPException(status_code=404, detail="Sites not found")
 
    except oracledb.Error as e:
        raise HTTPException(status_code=500, detail=f"Database error: {e}")

La modification clé : data[3].read() au lieu de juste data[3]

super merci !

Nico