get SQLSERVER json and using in python

Question

Guys can anyone help? I have a sqlserver json:

SELECT CEQ.CODCARGOEQ AS CODCARGO, INITCAP(ISNULL((CAR.DESCRCARGO), \'SEM DESCRICAO\'))  AS DESCRICAO 
FROM  TFPCAR CAR  
    INNER JOIN AD_TBCARGOEQUIVALENTE CEQ 
        ON CEQ.CODCARGOEQ = CAR.CODCARGO 
WHERE CAR.ATIVO = \'S\' AND CAR.CODCARGO > 0 
FOR JSON PATH, ROOT(\'LISTACARGOS\')

That sql returns to me a follow json

{"LISTACARGOS":[{"CODCARGO":1,"DESCRICAO":"Advogado"},
{"CODCARGO":2,"DESCRICAO":"Agente De Limpeza"},
{"CODCARGO":3,"DESCRICAO":"Agente De Transporte"},
{"CODCARGO":4,"DESCRICAO":"Ajud De Manutenção"},{"CODCARGO":5,"DESCRICAO":"Ajud Farmacia"},{"CODCARGO":6,"DESCRICAO":"Ajudante De Fossa"},{"CODCARGO":7,"DESCRICAO":"Almoxarife"},{"CODCARGO":8,"DESCRICAO":"Analista Comercial"},{"CODCARGO":9,"DESCRICAO":"Analista De Complice"},{"CODCARGO":10,"DESCRICAO":"Analista De Dho"},{"CODCARGO":11,"DESCRICAO":"Analista De Pessoal"},{"CODCARGO":12,"DESCRICAO":"Analista De Projetos"},{"CODCARGO":13,"DESCRICAO":"Analista De Recursos Humanos"},{"CODCARGO":14,"DESCRICAO":"Analista De Ti"},{"CODCARGO":15,"DESCRICAO":"Analista Financeiro"},{"CODCARGO":16,"DESCRICAO":"Apoio Tecnico"},{"CODCARGO":17,"DESCRICAO":"Artificie Eletrônica"},{"CODCARGO":18,"DESCRICAO":"Asses.Tec.Juridico"},{"CODCARGO":19,"DESCRICAO":"Assist De Comunic E Marketing"},{"CODCARGO":20,"DESCRICAO":"Assist. Administrativo Senior"},{"CODCARGO":21,"DESCRICAO":"Assistente Administrativo"},{"CODCARGO":22,"DESCRICAO":"Assistente De Adm De Pessoas"},{"CODCARGO":23,"DESCRICAO":"Assistente Social"}, 
{"CODCARGO":487,"DESCRICAO":"Tecnico Social (Psic)"},{"CODCARGO":488,"DESCRICAO":"Tecnico Social Advogado"},{"CODCARGO":489,"DESCRICAO":"Tecnico Social I"},{"CODCARGO":490,"DESCRICAO":"Tecnico Social I Assistente Social"},{"CODCARGO":491,"DESCRICAO":"Tecnico Social I Psicologo"},{"CODCARGO":492,"DESCRICAO":"Tecnico Social Ii"},{"CODCARGO":493,"DESCRICAO":"Tecnico Social Ii Assistente Social"},{"CODCARGO":494,"DESCRICAO":"Tecnico Social Ii Psicologo"},{"CODCARGO":495,"DESCRICAO":"Tecnico Social Iii"},{"CODCARGO":496,"DESCRICAO":"Tecnico Social Iv"},{"CODCARGO":497,"DESCRICAO":"Tecnico Social Ix"},{"CODCARGO":498,"DESCRICAO":"Tecnico Social Psicologo"},{"CODCARGO":499,"DESCRICAO":"Tecnico Social V"},{"CODCARGO":500,"DESCRICAO":"Tecnico Social Vi"},{"CODCARGO":501,"DESCRICAO":"Tecnico Social Vii"},{"CODCARGO":502,"DESCRICAO":"Tecnico Social Viii"},{"CODCARGO":503,"DESCRICAO":"Tecnico Social X"},{"CODCARGO":504,"DESCRICAO":"Tecnico Social Xi"},{"CODCARGO":505,"DESCRICAO":"Tecnico Social Xii"},{"CODCARGO":506,"DESCRICAO":"Tecnico Social Xiii"},{"CODCARGO":507,"DESCRICAO":"Tecnico Social Xiv"},{"CODCARGO":508,"DESCRICAO":"Tecnico Social Xv"},{"CODCARGO":509,"DESCRICAO":"Técnico Terapeuta Ocupacional"},{"CODCARGO":510,"DESCRICAO":"Teledigifonista"},{"CODCARGO":511,"DESCRICAO":"Telefonista E Recepcionista"},{"CODCARGO":512,"DESCRICAO":"Tratorista"},{"CODCARGO":513,"DESCRICAO":"Varredor (A)"},{"CODCARGO":514,"DESCRICAO":"Viveirista Florestal"},{"CODCARGO":515,"DESCRICAO":"Motorista"},{"CODCARGO":516,"DESCRICAO":"Gestor Financeiro"},{"CODCARGO":517,"DESCRICAO":"Fonoaudiólogo"},{"CODCARGO":518,"DESCRICAO":"Pintor"},
{"CODCARGO":519,"DESCRICAO":"Op Sus Facil"},{"CODCARGO":520,"DESCRICAO":"Téc De Radiologia"},{"CODCARGO":521,"DESCRICAO":"Gerente De  Compliance"},{"CODCARGO":522,"DESCRICAO":"Eletricista"},{"CODCARGO":523,"DESCRICAO":"Técnico Em Mecânica"},{"CODCARGO":524,"DESCRICAO":"Auxiliar De Conservação De Vias"},{"CODCARGO":525,"DESCRICAO":"Jornalista"},{"CODCARGO":526,"DESCRICAO":"Assessor Pedagogico"},{"CODCARGO":527,"DESCRICAO":"Oficial De Manutenção Civil"},{"CODCARGO":528,"DESCRICAO":"Zelador"},{"CODCARGO":529,"DESCRICAO":"Supervisor Geral"},{"CODCARGO":530,"DESCRICAO":"Coordenação De Contratos E Medição"},{"CODCARGO":531,"DESCRICAO":"Supervisor Administrativo"},{"CODCARGO":532,"DESCRICAO":"Nutricionista"},{"CODCARGO":533,"DESCRICAO":"Especialista Em Saúde"},{"CODCARGO":534,"DESCRICAO":"Analista Fiscal"},
{"CODCARGO":535,"DESCRICAO":"Assistente De Projetos Socioassistenciais"},
{"CODCARGO":536,"DESCRICAO":"Auxiliar Geral De Conservação De Vias Permanentes"}]}

I need do catch this full json in my python API, and for that i use the follow function:

def get_listcargo(myquery):
    cursor = conn()
    cursor.execute(myquery)
    for row in cursor:
        cargolist = [elem for elem in row]
    
    return (cargolist)

But python returns to me a truncate data like this

 "},{"CODCARGO":507,"DESCRICAO":"Tecnico Social Xiv"},{"CODCARGO":508,"DESCRICAO":"Tecnico Social Xv"},{"CODCARGO":509,"DESCRICAO":"Técnico Terapeuta Ocupacional"},{"CODCARGO":510,"DESCRICAO":"Teledigifonista"},{"CODCARGO":511,"DESCRICAO":"Telefonista E Recepcionista"},{"CODCARGO":512,"DESCRICAO":"Tratorista"},{"CODCARGO":513,"DESCRICAO":"Varredor (A)"},{"CODCARGO":514,"DESCRICAO":"Viveirista Florestal"},{"CODCARGO":515,"DESCRICAO":"Motorista"},{"CODCARGO":516,"DESCRICAO":"Gestor Financeiro"},{"CODCARGO":517,"DESCRICAO":"Fonoaudiólogo"},{"CODCARGO":518,"DESCRICAO":"Pintor"},{"CODCARGO":519,"DESCRICAO":"Op Sus Facil"},{"CODCARGO":520,"DESCRICAO":"Téc De Radiologia"},{"CODCARGO":521,"DESCRICAO":"Gerente De Compliance"},{"CODCARGO":522,"DESCRICAO":"Eletricista"},{"CODCARGO":523,"DESCRICAO":"Técnico Em Mecânica"},{"CODCARGO":524,"DESCRICAO":"Auxiliar De Conservação De Vias"},{"CODCARGO":525,"DESCRICAO":"Jornalista"},{"CODCARGO":526,"DESCRICAO":"Assessor Pedagogico"},{"CODCARGO":527,"DESCRICAO":"Oficial De Manutenção Civil"},{"CODCARGO":528,"DESCRICAO":"Zelador"},{"CODCARGO":529,"DESCRICAO":"Supervisor Geral"},{"CODCARGO":530,"DESCRICAO":"Coordenação De Contratos E Medição"},{"CODCARGO":531,"DESCRICAO":"Supervisor Administrativo"},{"CODCARGO":532,"DESCRICAO":"Nutricionista"},{"CODCARGO":533,"DESCRICAO":"Especialista Em Saúde"},{"CODCARGO":534,"DESCRICAO":"Analista Fiscal"},{"CODCARGO":535,"DESCRICAO":"Assistente De Projetos Socioassistenciais"},{"CODCARGO":536,"DESCRICAO":"Auxiliar Geral De Conservação De Vias Permanentes"}]}

what i have done wrong?

That query returns a single result and yet your code iterates over the query results, then returns the very last value. Why are you using JSON *at all* in this case? It only wastes network bandwidth. The results are a simple set of rows. You could get that and serialize it to JSON quite easily. If you build an HTTP service, your API library will serialize objects to JSON — Panagiotis Kanavos, Feb 02 '22 at 14:09
Thanks Panagiotis, i have changed my code and it works fine, i follow this post https://stackoverflow.com/questions/3286525/return-sql-table-as-json-in-python — Couto, Feb 02 '22 at 14:25
Does this answer your question? [FOR JSON PATH results in SSMS truncated to 2033 characters](https://stackoverflow.com/questions/54973536/for-json-path-results-in-ssms-truncated-to-2033-characters) — Thom A, Feb 02 '22 at 14:42
Does this answer your question? [FOR JSON PATH results in SSMS truncated to 2033 characters](https://stackoverflow.com/questions/54973536/for-json-path-results-in-ssms-truncated-to-2033-characters) — Charlieface, Feb 02 '22 at 16:14
One simple solution is to nest the whole thing `SELECT (SELECT CEQ.CODCARGOEQ .... FOR JSON PATH, ROOT('LISTACARGOS') )` or you can assign it to a variable — Charlieface, Feb 02 '22 at 16:14
Hi Larnu and Charlie, this do not answer my question because my sql return more then 2033 characters. But thanks all. — Couto, Feb 03 '22 at 16:08

David Browne - Microsoft · Answer 1 · 2022-02-02T14:48:56.943

0

FOR JSON results are streamed to the client using a single-column multi-row resultset. Long JSON results will be broken over multiple rows. So you need to iterate the cursor, concatenating the values in the first column to reconstruct the whole JSON document.

eg

def get_listcargo(myquery):
    cursor = conn()
    cursor.execute(myquery)
    json = ''
    for row in cursor:
        json = json + row[0]
    
    return (json)

edited Feb 02 '22 at 14:48

answered Feb 02 '22 at 14:35

David Browne - Microsoft

80,331
6
39
67

Thanks David i will try do it. – Couto Feb 03 '22 at 16:08

get SQLSERVER json and using in python

1 Answers1