How to insert SUPER AND GEOMETRY data type values?

[kishaningithub]

https://github.com/aws/amazon-redshift-python-driver#supported-datatypes

From the mapping i see above super and geometry is mapped to string,

• For SUPER, how should dictionaries be inserted? should json.dumps be used for this?
• For GEOMETRY, how should data be inserted? should it be a string like latitude,longitude like "7.323, 13.3434"?

Can you kindly add an example?

[Brooke-white]

Thank you for this feedback. We will add examples to our docs, likely in the form of a tutorial. In the meantime I've added some docs below -- feel free to let me know what other examples/information would be helpful :)

Geometry

When sending Geometry datatype, use WKB (well known binary) format unless passing to a Redshift server method such as ST_GeomFromText(). In that case, a WKT (well known text) representation should be used. Available Redshift server methods can be found, with examples, here.

Geometry datatype is received in WKB format.

Super

When sending super datatype, send it as a string. If your Python datatype is a dict, use json.dumps.

When receiving super datatype, a string is received. json.loads can be used to load data stored in JSON format.

Examples

cursor.execute("create table datatype_test (c1 geometry, c2 super);")
        cursor.execute(
            "insert into datatype_test (c1, c2) values (ST_GeomFromText(%s), json_parse(%s));",
            (
                'LINESTRING(1 2,3 4,5 6,7 8,9 10,11 12,13 14,15 16,17 18,19 20)', # here we use WKT format
                json.dumps({1: "hello", 2: "world"})
            )
        )
        cursor.execute("select c1, c2 from datatype_test;")
        result = cursor.fetchone()
        print("c1={}\nc2={}".format(result[0], result[1]))

c1=01020000000a000000000000000000f03f000000000000004000000000000008400000000000001040000000000000144000000000000018400000000000001c40000000000000204000000000000022400000000000002440000000000000264000000000000028400000000000002a400000000000002c400000000000002e4000000000000030400000000000003140000000000000324000000000000033400000000000003440
c2="{\"1\": \"hello\", \"2\": \"world\"}"

        cursor.execute("create table datatype_test (c1 geometry, c2 super);")
        cursor.execute(
            "insert into datatype_test (c1) values (%s);",
            (
               '0103000020E61000000100000005000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F000000000000F03F000000000000000000000000000000000000000000000000',
# using wkb format
            )
        )
        cursor.execute("select c1 from datatype_test;")
        result = cursor.fetchone()
        print("c1={}\n".format(result[0],))

c1=0103000020e61000000100000005000000000000000000000000000000000000000000000000000000000000000000f03f000000000000f03f000000000000f03f000000000000f03f000000000000000000000000000000000000000000000000

[Brooke-white]

Hi @kishaningithub , we've added a new tutorial in our latest release 2.0.904. Amazon Redshift Datatypes, which can be found linked from our project README, covers uses of Amazon Redshift specific datatypes. Please let me know if there are other functionalities you'd like to see demonstrated in our tutorials.