search

kayak / pypika

PyPika is a python SQL query builder that exposes the full richness of the SQL language using a syntax that reflects the resulting query. PyPika excels at all sorts of SQL queries but is especially useful for data analysis.
http://pypika.readthedocs.io/en/latest/
Apache License 2.0
2.5k stars 295 forks source link

Select from values in insert in Snowflake #743

Closed jeffarrubla closed 1 year ago

jeffarrubla commented 1 year ago

Hello,

so I'm trying to do the next query, using parse_json, into pypika INSERT INTO my_table(col_1, col_2) SELECT $1,parse_json($2) FROM VALUES(:1, :2)

I have:

  Query.into("my_table")
            .columns(
                "col_1",
                "col_2"
            )
            .insert(Parameter(":1"), Parameter(":2"))
            .from_("values")
            .select(
                "$1",
                "parse_json($2)",
            )
            .get_sql(quote_char=None)

But I get: INSERT INTO my_table (col_1,col_2) VALUES (:1,:2)

So i was wondering how can I do a insert into using select from values

Thanks in advance!

jeffarrubla commented 1 year ago

Solution:

from pypika.functions import Function

class parse_json(Function):
    def __init__(self, *terms, **kwargs):
        super(parse_json, self).__init__("parse_json", *terms, **kwargs)

Query.into("my_table")
            .columns(
               "col_1",
               "col_2"
            )
            .from_(
                Query.select(
                    Parameter(":1"),
                    Parameter(":2"),
                )
            )
            .select(
                "$1",
                parse_json("$2"),
            )
            .get_sql(quote_char=None)
            .replace("'", "")  # A hack for removing the ' in the parse_json parameter