python - How to implement a specific SQL statement as a SQLAlchemy ORM query

Question

I was following a tutorial to make my first Flask API (https://medium.com/@dushan14/create-a-web-application-with-python-flask-postgresql-and-deploy-on-heroku-243d548335cc) I did it but now I want to do queries more custom with SQLAlchemy and PostgreSQL. My question is how I could do something like this:

query = text("""SELECT enc.*, persona."Persona_Nombre", persona."Persona_Apellido", metodo."MetEnt_Nombre", metodo_e."MetPag_Descripcion" 
FROM "Ventas"."Enc_Ventas" AS enc 
INNER JOIN "General"."Persona" AS persona ON enc."PersonaId" = persona."PersonaId" 
INNER JOIN "Ventas"."Metodo_Entrega" AS metodo ON enc."MetodoEntregaId" = metodo."MetodoEntregaId"
INNER JOIN "General"."Metodo_Pago" AS metodo_e ON enc."MetodoPagoId" = metodo_e."MetodoPagoId" 
INNER JOIN "General"."Estatus" AS estado ON enc. """)

but with SQLAlchemy in order to use the models that I created previously. Thanks in advance for any answer!!

Edit:

The columns that I wish to see at the final result are: enc.*, persona."Persona_Nombre", persona."Persona_Apellido", metodo."MetEnt_Nombre", metodo_e."MetPag_Descripcion"

I really wish I could share more info but sadly I can't at the moment.

Answer 1

Doing this from the ORM layer, you would reference model names (I match the names of your query above, but I'm sure some of the model/table names are off - now adjusted slightly).

• Now revised to include the specific columns you only want to see (note that I ignore your SQL aliases, ORM layer handles the actual query construction):

  selection = session.query(Enc_Ventas, Persona.Persona_Nombre, Persona.Persona_Apellido, Metodo_Entrega.MetEnt_Nombre, Metodo_Pago.MetPag_Descripcion).

  join(Persona, Enc_Ventas.PersonaId == Persona.PersonaId). join(Metodo_Entrega, Enc_Ventas.MetodoEntregaId == Metodo_Entrega.MetodoEntregaId).

  join(Metodo_Pago, Enc_Ventas.MetodoPagoId == Metodo_Pago.MetodoPagoId).

  join(Estatus).all()

Referencing the selection collection would be by iteration through the rows of tuples. A more robust and stable solution would be to transform each output row into a dict.

Otherwise, by including whole models, the collection of rows returned can be individually accessed by referencing as dot notation the model names in the query(). If you need further access to the columns in the related tables, use the ORM technique of .options(joinedload(myTable)), which in a single database query will bring in those additional columns, using the relationship name, also as dot notation.

You also need to define sqlalchemy relationships within your models for this to work, as well as defining the underlying SQL foreign keys.

Much more detail and/or a more specific question is needed to help further, imo.