Python Sqlparser - Replace :: with cast()

Question

I am trying to create a script which will convert Redshift SQL into SparkSQL. One specific conversion is giving me troubles - converting '::' into cast() function. Sql parser seems to mark '::' as punctuation, and doesn't parse out the entire statement that the double colon is applying to. Could use some suggestions!

Example:

select colA::numeric(18,2) as colA
from tableA

into 

select cast(colA as decimal(18,2)) as colA
from tableA

Sample code

import sqlparse
def __translate_statements():
    for item in parsed.tokens:
        if isinstance(item, sqlparse.sql.Function):
            ....do some logic
for parsed_stmt in sqlparse.parse(sql_stmts):
        translated_token_stream = __translate_statements(parsed_stmt)

Answer 1

You can use sqlglot to do this.

>>> from sqlglot import transpile
>>> transpile("select colA::numeric(18, 2) as colA from tableA", read="redshift", write="spark")[0]
'SELECT CAST(colA AS DECIMAL(18, 2)) AS colA FROM tableA'