Loads raster data from POSTGIS directly to numpy.array using rasterio

raster_postgis.py

import psycopg2
from rasterio.io import MemoryFile

conn = pscopg2.connect("<connection string>")
cur = conn.cursor()
# ensure that the GTiff driver is available, 
# see https://postgis.net/docs/postgis_gdal_enabled_drivers.html
cur.execute('''
  SET postgis.gdal_enabled_drivers TO 'GTiff';
  WITH temp as (
    SELECT ST_Union(rast,1) As file_rast
    FROM raster.table
  )
  SELECT ST_AsGDALRaster(file_rast, 'GTiff') FROM temp;
 ''')

for row in cur:
    rast = row[0].tobytes()
    with MemoryFile(rast).open() as dataset:
        data_array = dataset.read()
        print(data_array)