BigQuery x Python: How to insert GEOGRAPHY into table using dbapi cursor?

Question

I would like to have a reusable insert function for BigQuery that leverages the PEP 249 dbapi implementation provided by the BQ library (google.cloud.bigquery.dbapi), but I am faced with an issue in that I do not know how to handle passing GEOGRAPHY types as bindings.

Here's a simplified example of what I am trying to achieve:

from google.cloud.bigquery import dbapi as bigquery

def insert(table_id, fields, bindings: Any):
    bq_conn = bigquery.Connection()
    cursor = bq_conn.cursor()
    cursor.execute(
        f"""
        INSERT INTO `{table_id}`
        ({','.join(fields)})
        VALUES
        ({','.join(['%s'] * len(fields))})
        """,
        bindings
    )

So the question is: How can I pass GEOGRAPHY types as bindings?

Fiddling around has shown me that verbatim string cannot be converted/inserted as GEOGRAPHY type, as demonstrated below:

CREATE TABLE `project.dataset.tmp` (geo GEOGRAPHY);

INSERT INTO `project.dataset.tmp`
(geo)
VALUES
("POINT(1,3)"); # fails with `Value has type STRING which cannot be inserted into column geo, which has type GEOGRAPHY at [4:2]`

So if verbatim string do not work in SQL, can bound ones work? If not, is there a Python object type I should pass instead, such as bigquery.QueryJobParameter or shapely.geometry.* or geojson.*?

Given the generalized nature of the function, I'd like to avoid having to embed custom SQL handling of different values based on type. I'd really like to simply pass an object and BigQuery handle the typing by itself.

Mikhail Berlyant · Accepted Answer · 2021-09-16 18:41:55Z

1

... does not work in the SQL Workspace due to "Value has type STRING which cannot be inserted into column geo, which has type GEOGRAPHY at [4:2]".

Below is correct syntax to use

CREATE TABLE `project.dataset.tmp` (geo GEOGRAPHY);

INSERT INTO `project.dataset.tmp`
(geo)
VALUES
(ST_GEOGPOINT(1,3));

which inserts properly geography data type

answered Sep 16, 2021 at 18:41

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Philippe Hebert Over a year ago

Hey Mikhail! Agreed. Let me rephrase my question, I think I may have not been clear enough.

Mikhail Berlyant Over a year ago

sure, but at least it addresses the first part of your current question, which I thought is really critical part of your issue. consider at least voting up if this is true :o)

Philippe Hebert Over a year ago

Rewrote the question. If you feel the answer is still relevant, let me know & I'll upvote :)

Mikhail Berlyant Over a year ago

sure. i am not python expert, so not doing any python answers - but my answer is easily applied to your code- so yes, I think it is still quite relevant to BigQuery aspect of the question!

Collectives™ on Stack Overflow

BigQuery x Python: How to insert GEOGRAPHY into table using dbapi cursor?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related