Pi- SQL : Enhancing Text-to- SQL with Fine-Grained Guidance from Pivot Programming Languages (2025)

Abstract: Text-to-SQL transforms the user queries from natural language to executable SQL programs, enabling non-experts to interact with complex databases. Existing prompt-based methods craft meticulous text guidelines and examples to facilitate SQL generation, but their accuracy is hindered by the large semantic gap between the texts and the low-resource SQL programs. In this work, we propose Pi-SQL, which incorporates the high-resource Python program as a pivot to bridge between the natural language query and SQL program. In particular, Pi-SQL first generates Python programs that provide fine-grained step-by-step guidelines in their code blocks or comments, and then produces an SQL program following the guidance of each Python program. The final SQL program matches the reference Python program’s query results and, through selection from candidates generated by different strategies, achieves superior execution speed, with a reward-based valid efficiency score up to 4.55 higher than the best-performing baseline. Extensive experiments demonstrate the effectiveness of Pi-SQL, which improves the execution accuracy of the best-performing baseline by up to 3.20.

## introduction sql is a standard programming language designed for managing and manipulating relational databases @xcite . although popular and general, sql programs can be challenging for non-experts to write, particularly when it comes to complex data querying tasks. text-to-sql models convert natural language queries into executable sql programs @xcite @xcite , enabling non-experts to interact with complex databases and extract insights from big data @xcite @xcite . for the client 1, what is the increase rate of his/her account balance from 2022/3/22 to 2024/12/27? # table: client [(client_id: integer, primary key, … ] # table: loan … baselines: din-sql, dail-sql, etc. you are a data science expert. … before generating the final sql query think step by step on how to write the query. … # load csv files @xmath0.read_csv('client.csv') # step 1: find the loan approved first on or before 2023-07-05 … recently, many text-to-sql models have been proposed based on large language models (llms), using either prompt engineering @xcite @xcite @xcite or supervised fine-tuning @xcite @xcite . the prompt-based methods require meticulously crafted guidelines @xcite @xcite @xcite as well as curated few-shot in-domain examples @xcite @xcite . the fine-tuning-based methods rely on highquality training data, which is expensive to obtain. moreover, tailored to the training data domain, they may not generalize in other domains @xcite . in this paper, we propose pi-sql, a novel prompt-based method that enhances text-to-sql by incorporating a high-resource pivot programming language to provide fine-grained guidance. motivated by multilingual pretraining @xcite @xcite and triangular machine translation @xcite , pi-sql adopts a python program as a pivot to bridge natural language and sql programs. as shown in figure 1 , compared with text-based guidance generated using chain-of-thought reasoning @xcite @xcite , pi-sql utilizes python programs to provide more detailed step-by-step reasoning through code blocks, comments. existing program-of-thought-based text-to-sql methods @xcite overlooked the considerable structure gap between the python and sql programs, while pi-sql contains approaches to mitigate the intrinsic difference between python and sql. specifically, pi-sql consists of an intermediate guidance preparation stage and an sql generation stage guided by the generated python. in the first stage, to enhance subsequent sql generation guided by python, pi-sql employs three strategies to generate python programs tailored for diverse sql application scenarios, and also steers this python generation by incorporating sql adaptation instructions into the prompt. in the second stage, each python program serves as guidance for generating sql programs. sql programs that produce results consistent with the majority output of the python programs are retained, and the one with the best execution efficiency is chosen. as a result, pi-sql fully leverages high-resource programming languages like python to generate highly accurate and efficient sql programs, without requiring few-shot examples or supervised finetuning with labeled data. we compare pi-sql with ten state-of-the-art (sota) baselines on the famous bird @xcite and @xcite benchmarks. the results show that pi-sql outperforms the baselines in both execution accuracy (ex) and reward-based valid efficiency score (r-ves), which are two popular metrics for text-to-sql. compared with the best-performing baseline, the ex improvement of pi-sql is 3.20 on the bird dev set. 1 2 related work prompt-based text-to-sql methods given the strong generalization ability of llms, recent mainstream research has shifted towards leveraging their powerful few-shot and zero-shot capabilities through prompt-based approaches. numer-ous research efforts have focused on enhancing text-to-sql performance from various aspects, including improved schema linking @xcite @xcite , the selection of more effective few-shot demonstrations @xcite @xcite @xcite @xcite , incorporating natural language chain-of-thought reasoning @xcite @xcite , and using the self-consistency method to boost performance with additional test-time computing @xcite @xcite @xcite . different from these works, pi-sql achieves better text-to-sql performance by leveraging the fine-grained guidance from high-resource programming languages in a zero-shot setting. program of thoughts program of thoughts (pot) is an extension of the chain of thought (cot) prompting strategy, aiming to mitigate errors in intermediate reasoning by executing intermediate steps as python programs. pot has been widely adopted in various tasks @xcite @xcite , due to its improved reliability of numerical and logical inference. however, its application to text-to-sql remains limited. r 3 @xcite directly employs pot to generate python code before sql to guide the sql generation, while @xcite incorporates pot into the text-to-sql training process. these approaches overlook the semantic and structural gap between python and sql. in contrast, pi-sql's design provides better sql generation guidance with python, which mitigates the intrinsic difference between python and sql. ## conclusion in this paper, we present pi-sql, a high-resource programming language-guided sql generation system with two key stages: intermediate guidance preparation and guided sql generation. experiments across various benchmarks and difficulty levels prove that our zero-shot method, pi-sql, significantly outperforms all baselines, including those with few-shot examples or requiring finetuning. the success of pi-sql underscores the potential of leveraging programming languages as an intermediate step in guiding code generation, offering new insights for future text-to-sql research. limitations pi-sql depends on multiple generated python codes to guide the llm in generating sql programs. this process introduces additional inference tokens, leading to higher computational costs during test time. one potential solution to alleviate this issue is to introduce a router that selectively schedules text queries for either direct generation or python-guided generation. we plan to explore this approach in future work. xiangjin xie, guangwei xu, lingyan zhao, and ruijie guo. 2025. opensearch-sql: enhancing text-to-sql with dynamic few-shot and consistency alignment. arxiv preprint arxiv:2502.14913. bo xu, shufei li, yifei wu, shouang wei, ming du, hongya wang, and hui song. 2024. chain-ofprogram prompting with open-source large language models for text-to-sql. in 2024 international joint conference on neural networks (ijcnn), pages 1-8. ieee. l xue. 2020. mt5: a massively multilingual pretrained text-to-text transformer. arxiv preprint arxiv:2010.11934. tao yu, rui zhang, kai yang, michihiro yasunaga, dongxu wang, zifan li, james ma, irene li, qingning yao, shanelle roman, et al. 2018. spider: a large-scale human-labeled dataset for complex and cross-domain semantic parsing and text-to-sql task. arxiv preprint arxiv:1809.08887. liang zhang, anwen hu, haiyang xu, ming yan, yichen xu, qin jin, ji zhang, and fei huang. 2024. tinychart: efficient chart understanding with program-of-thoughts learning and visual token merging. in proceedings of the 2024 conference on empirical methods in natural language processing, pages 1882-1898, miami, florida, usa. association for computational linguistics. meng zhang, liangyou li, and qun liu. 2022. triangular transfer: freezing the pivot for triangular machine translation. in proceedings of the 60th annual meeting of the association for computational linguistics (volume 2: short papers), pages 644-650, dublin, ireland. danna zheng, mirella lapata, and jeff pan. 2024. archer: a human-labeled text-to-sql dataset with arithmetic, commonsense and hypothetical reasoning. in proceedings of the 18th conference of the european chapter of the association for computational linguistics (volume 1: long papers), pages 94-111, st. julian's, malta. association for computational linguistics. ruiqi zhong, charlie snell, dan klein, and jason eisner. 2023. non-programmers can label programs indirectly via active examples: a case study with text-to-sql. in proceedings of the 2023 conference on empirical methods in natural language processing, pages 5126-5152, singapore.