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.

CodeMantle · Accepted Answer · 2021-01-09 09:25:49Z

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.

edited Jan 9, 2021 at 9:25

answered Jan 8, 2021 at 15:15

CodeMantle

1,4362 gold badges18 silver badges28 bronze badges

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

5 Comments

5481393 Over a year ago

I saw a solution similar to yours but I don't want to combine all the columns of every table I'm joining, that was precisely why I came here to ask because I found that strange. I'm coming from C# and Entity Framework and there you can join tables specifying the columns you want to add to the final answer and that's what I want to know, if it's posible to do the same with SQLAlchemy 😅

CodeMantle Over a year ago

You can definitely specify the columns you wish to see in the final output, so that the resulting expanded SQL statement is not too large or containing extraneous columns. You can specify those columns by dot notation instead of the full model names in the query(). You then access these columns by index in the resulting tuple. Provide a more detailed requirement, including model fragments, and that can be shown. Despite your misgivings, SQLAlchemy is extremely flexible in its ORM interface (imo).

CodeMantle Over a year ago

Btw, the elaborately join() statements would not be needed if SQLA was allowed to infer from defined model-foreignkeys (must be unambiguous), i.e. the SQLA query could be more terse.

5481393 Over a year ago

Look I added the columns that I wish to see at the question.

CodeMantle Over a year ago

My answer is updated with those columns. Tick to accept if the answer fulfills your original question.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related