Automate conversion of SQL query to python code

Question

I am trying to create a Python script which can convert the SQL query into a Python script using a regex. Can someone throw some ideas to achieve this in Python?

============SQL Query========
SELECT
   alert_id,
   Count(star_rating) as total_rating,
   Max(star_rating) AS best_rating,
   Min(star_rating) AS worst_rating 
FROM
   alerts 
WHERE
   verified_purchase = 'Y' 
   AND review_date BETWEEN '1995-07-22' AND '2015-08-31' 
   AND country IN 
   (
      'DE','US','UK','FR','JP' 
   )
GROUP BY
   alert_id 
ORDER BY
   total_rating asc,
   alert_id desc,
   best_rating 
LIMIT 10;

Below are the expected result:

alerts.filter("verified_purchase = 'Y' AND review_date BETWEEN '1995-07-22' AND '2015-08-31' AND country IN ('DE', 'US', 'UK', 'FR', 'JP')")
.groupBy("alert_id")
.agg(count(col("star_rating")).alias('total_rating'),max(col("star_rating")).alias('best_rating'),min(col("star_rating")).alias('worst_rating')")
.select("alert_id","total_rating","best_rating","worst_rating")
.orderBy(col("total_rating").asc(),col("alert_id").desc(),col("best_rating").asc())
.limit(10)

===code so far i have developed=== from tika import parser import re filename = '/home/linux/sqlfile.txt' # Parse the text parsedtext = parser.from_file(filename) # Extract the text content from the parsed txt txt = parsedtxt["content"] # Convert doublelines into single txt = txt.replace('\n', '') ##################################### # Do something with the txt ##################################### print (txt) — Rahul P, Jul 04 '21 at 05:30
` ===code so far i have developed=== from tika import parser import re filename = '/home/linux/sqlfile.txt' # Parse the text parsedtext = parser.from_file(filename) # Extract the text content from the parsed txt txt = parsedtxt["content"] # Convert doublelines into single txt = txt.replace('\n', '') ##################################### # Do something with the txt ##################################### print (txt) ` — Rahul P, Jul 04 '21 at 05:33
============result so far i have achieved========= `>>> print(txt) SELECT alert_id, Count(star_rating) as total_rating, Max(star_rating) AS best_rating, Min(star_rating) AS worst_rating FROM alerts WHERE verified_purchase = 'Y' AND review_date BETWEEN '1995-07-22' AND '2015-08-31' AND country IN ( 'DE','US','UK','FR','JP' )GROUP BY alert_id ORDER BY total_rating asc, alert_id desc, best_rating LIMIT 10;` — Rahul P, Jul 04 '21 at 05:33
Why would you do this? All SQL adapters allow you to make SQL queries directly. This seems like a waste of time. — Tim Roberts, Jul 04 '21 at 06:04

score 0 · Answer 1 · answered Jul 04 '21 at 06:40

I found a project that does it for SQLAlchemy code.

https://github.com/pglass/sqlitis

It seems that feature matrix is incomplete, but it's open source, so it may be better to contribute to the project than writing from scratch (and it seems like a fun and active project).

Definitely don't use regex. I recomment classic SO answer why not: RegEx match open tags except XHTML self-contained tags

Automate conversion of SQL query to python code

1 Answers1