In my latest work stuff I have a custom debugger module that prints the SQL statements used to stdout. To make the debug output more readable I whipped together this quick script that pretty prints SQL statements with hopefully correct case and indentation. It converts something ugly like this:


select * from foo order by bar;

into this:


SELECT
  *
FROM
  foo
ORDER BY bar;

Try with your own SQL statements

Python file printsql.py

Comments

Post your own comment
djo August 6, 2004

Syntax highlighting!

Peter August 8, 2004

I've put in SilverCity's syntax highlighting (http://silvercity.sourceforge.net/) but it's pretty crap for SQL.

diego (semco) August 6, 2004

it didn't work when i tried some of my scripts ;)

select distinct cl.nom_cli, c.tkt_cor, c.cod_cor, b.nom_bas, r.nom_reg ,l.cod_loc ,l.nom_loc,
T.DSC_TIP_ORS, eee.dsc_tip_eqp, p.dsc_prb, cp.srv_exe,
c.dta_ini, c.hor_ini, c.dta_ter, c.hor_ter,
c.srv_exe, c.dta_abr_cor, c.dsc_his, s.dsc_sta_ors, F.NOM_FUN,
l.id_reg_fat
from corretiva c, cliente cl, statusordemservico s, localidade l,
regiao r, base b, EQUIPE E, FUNCIONARIO F, TIPOORDEMSERVICO T, CORRETIVAEQUIPAMENTO CC,
corretivaproblema cp, problema p, tipoequipamento eee
where cl.id_cli = 16 AND c.id_cli = cl.id_cli and c.id_sta_ors = s.id_sta_ors and c.id_sta_ors = 12 and
c.id_loc = l.id_loc and r.id_reg = c.id_reg and b.id_bas = c.id_bas AND L.COD_EQU = E.COD_EQU
AND F.ID_FUN = E.ID_FUN AND C.ID_TIP_ORS = T.ID_TIP_ORS AND C.COD_COR *= CC.COD_COR
and cp.cod_cor = c.cod_cor and p.id_prb = cp.id_prb and p.id_tip_eqp = eee.id_tip_eqp and c.dta_abr_cor between '2004-05-25 00:00:00' AND '2004-06-25 23:59:59'
order by cl.nom_cli,b.nom_bas, r.nom_reg, s.dsc_sta_ors, C.DTA_ABR_COR

Peter August 7, 2004

That's one ugly SQL statement. What would Ricardo say about that?

At least my script got something right, but it just isn't good enough on all the AND and ',' separated constraint and fields. How do you think it should look?

Cristian May 26, 2006

It should look like this!

select distinct cl.nom_cli,
c.tkt_cor,
c.cod_cor,
b.nom_bas,
r.nom_reg,
l.cod_loc,
l.nom_loc,
T.DSC_TIP_ORS,
eee.dsc_tip_eqp,
p.dsc_prb,
cp.srv_exe,
c.dta_ini,
c.hor_ini,
c.dta_ter,
c.hor_ter,
c.srv_exe,
c.dta_abr_cor,
c.dsc_his,
s.dsc_sta_ors,
F.NOM_FUN,
l.id_reg_fat
from corretiva c,
cliente cl,
statusordemservico s,
localidade l,
regiao r,
base b,
EQUIPE E,
FUNCIONARIO F,
TIPOORDEMSERVICO T,
CORRETIVAEQUIPAMENTO CC,
corretivaproblema cp,
problema p,
tipoequipamento eee
where cl.id_cli = 16
AND c.id_cli = cl.id_cli
and c.id_sta_ors = s.id_sta_ors
and c.id_sta_ors = 12
and c.id_loc = l.id_loc
and r.id_reg = c.id_reg
and b.id_bas = c.id_bas
AND L.COD_EQU = E.COD_EQU
AND F.ID_FUN = E.ID_FUN
AND C.ID_TIP_ORS = T.ID_TIP_ORS
AND C.COD_COR * = CC.COD_COR
and cp.cod_cor = c.cod_cor
and p.id_prb = cp.id_prb
and p.id_tip_eqp = eee.id_tip_eqp
and c.dta_abr_cor between '2004-05-25 00:00:00' AND
'2004-06-25 23:59:59'
order by cl.nom_cli, b.nom_bas, r.nom_reg, s.dsc_sta_ors, C.DTA_ABR_COR

and some tabs because they are wiped out in the comments.

Nick Mellor January 23, 2009

Hi Peter,

This is a poser for you: an MS SQL view with lots of CASE statements. It's ugly as hell, but that's exactly why I need to pretty-print it. I'm not the original author :-)

SELECT TOP (100) PERCENT SEG.segID, SEG.segEndDate, SEG.segTypeID, SEG.segStartDate, SEG.segItemID, SEG.segQuoteCost, SEG.segNetCost,
SEG.segActualCost, SEG.segBookConfDate, SEG.segBookConfID, SEG.TripID, SEG.operatorID, SEG.segFld1, SEG.segStartTime, SEG.segEndTime,
SEG.attractID, SEG.segStartOfficeID, SEG.segEndOfficeID, SEG.segDays, SEG.segPaymentInfo, SEG.segAdminInfo, SEG.routeID, SEG.segAdults,
SEG.segChildren, SEG.segBabies, SEG.segFamilies, SEG.segPensioners, SEG.segBookRef, SEG.segDesc, SEG.segDateEmailed,
SEG.segDateConfirmed, SEG.segAmended, SEG.passID, SEG.segConfirmed, SEG.segConfComments, SEG.segCommPaid, SEG.segError,
SEG.segStartLocID, SEG.segEndLocID, SEG.segLength, SEG.segPriceStartDate, SEG.segPriceEndDate, SEG.segPriceInfo, SEG.segPricedByAgent,
SEG.segPriceSimple, CASE WHEN SEG.segTypeID = 3 THEN Air.name ELSE OPER.operatorName END AS operatorName,
CASE WHEN SEG.segTypeID = 3 THEN segAdminInfo ELSE CASE WHEN SEG.segItemID IS NOT NULL AND
SEG.segItemID != 0 THEN ITEM.segItemTitle WHEN SEG.routeID IS NOT NULL AND
SEG.routeID != 0 THEN ROUTE.routeTitle WHEN SEG.attractID IS NOT NULL AND
SEG.attractID != 0 THEN ATTRACT.attractTitle WHEN SEG.specialID != 0 AND SEG.specialID IS NOT NULL THEN SPECIAL.Title END END AS itemTitle,
SEGTYPE.segTypeName, CASE WHEN SEG.segTypeID = 4 OR
SEG.segTypeID = 5 OR
SEG.segTypeID = 12 THEN ROUTEOFFSTART.officeNameText ELSE OFFSTART.officeNameText END AS startOfficeText,
CASE WHEN SEG.segTypeID = 4 OR
SEG.segTypeID = 5 OR
SEG.segTypeID = 12 THEN ROUTEOFFEND.officeNameText ELSE OFFEND.officeNameText END AS endOfficeText, dbo.tbltrips.tripNetCost AS tripCost,
LOCSTART.locTitle AS startLocTitle, LOCEND.locTitle AS endLocTitle, CASE WHEN SEG.segTypeID = 3 THEN 0 ELSE 1 END AS IsFlight,
OFFSTART.officePhone, OFFSTART.officeAddress, SEG.segPrevCost, SEG.specialID
FROM dbo.tblsegtypes AS SEGTYPE RIGHT OUTER JOIN
dbo.OfficeList AS ROUTEOFFSTART INNER JOIN
dbo.tblRoute AS ROUTE ON ROUTEOFFSTART.officeID = ROUTE.routeStartOfficeID INNER JOIN
dbo.OfficeList AS ROUTEOFFEND ON ROUTE.routeEndOfficeID = ROUTEOFFEND.officeID RIGHT OUTER JOIN
dbo.tbloperator AS OPER RIGHT OUTER JOIN
dbo.tblsegment AS SEG INNER JOIN
dbo.tbltrips ON SEG.TripID = dbo.tbltrips.tripID LEFT OUTER JOIN
dbo.tblSpecial AS SPECIAL ON SEG.specialID = SPECIAL.specialID LEFT OUTER JOIN
dbo.tbllocation AS LOCEND ON SEG.segEndLocID = LOCEND.locID LEFT OUTER JOIN
dbo.tbllocation AS LOCSTART ON SEG.segStartLocID = LOCSTART.locID LEFT OUTER JOIN
dbo.tblsegitem AS ITEM ON SEG.segItemID = ITEM.segItemID ON OPER.operatorID = SEG.operatorID ON ROUTE.routeID = SEG.routeID ON
SEGTYPE.segTypeID = SEG.segTypeID LEFT OUTER JOIN
dbo.OfficeList AS OFFSTART ON SEG.segStartOfficeID = OFFSTART.officeID LEFT OUTER JOIN
dbo.OfficeList AS OFFEND ON SEG.segEndOfficeID = OFFEND.officeID LEFT OUTER JOIN
dbo.tblattractitem AS ATTRACT ON SEG.attractID = ATTRACT.attractID LEFT OUTER JOIN
dbo.tblGalileoAirline AS AIR ON AIR.code = { fn LEFT(SEG.segFld1, 2) }
WHERE (SEG.segID <> 0) AND (ITEM.segItemNonActive = 0)
ORDER BY SEG.segStartDate, SEG.segEndDate, IsFlight, SEG.segStartTime, SEG.segEndTime

Your email will never ever be published.

Previous:
Integer division in programming languages August 4, 2004 Python
Next:
Psychiatric med student Michelle's story August 8, 2004 Misc. links
Related by category:
A Python dict that can report which keys you did not use June 12, 2025 Python
Native connection pooling in Django 5 with PostgreSQL June 25, 2025 Python
How I run standalone Python in 2025 January 14, 2025 Python
How to resolve a git conflict in poetry.lock February 7, 2020 Python
Related by keyword:
How I make my Vite dev server experience faster October 22, 2024 Node, React, JavaScript
Find what indentation your files use July 7, 2015 Python
Speed test between django_mongokit and postgresql_psycopg2 March 9, 2010 Python, Django
How to log ALL PostgreSQL SQL happening July 20, 2015 PostgreSQL, macOS