sqlalchemy_row_to_json.py

from sqlalchemy import func, select, Column, ForeignKey, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.sql.expression import literal_column
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine('postgresql+psycopg2://postgres:password@localhost/test', echo=True)


  
class Author(Base):
  __tablename__ = 'author'
  id = Column(Integer, primary_key=True)
  name = Column(String(255), nullable=False)
  

class Book(Base):
  __tablename__ = 'book'
  id = Column(Integer, primary_key=True)
  name = Column(String(255), nullable=False)
  author_id = Column(Integer, ForeignKey(Author.id))
  
book_table = Book.__table__
author_table = Author.__table__

actual_query = select([book_table.c.id, book_table.c.name, book_table.c.author_id, author_table.c.name]).select_from(
  author_table.join(book_table)).alias('row')

convert_json_query = select([func.cast(func.row_to_json(literal_column('row')), String)]).select_from(actual_query)

for row in conn.execute(convert_json_query):
   print(type(row), row)
    
<class 'sqlalchemy.engine.result.RowProxy'> ('{"id":1,"name":"War and Peace","author_id":1,"name":"Leo Tolstoy"}',)
<class 'sqlalchemy.engine.result.RowProxy'> ('{"id":3,"name":"The metamorphosis","author_id":2,"name":"Kafka"}',)
<class 'sqlalchemy.engine.result.RowProxy'> ('{"id":2,"name":"The Trial","author_id":2,"name":"Kafka"}',)