Why do I get unexpected type when using None for Google BigQuery DB-API?

Question

I'm trying to migrate data from a MySQL table to a BigQuery table using Python, but it gives me the following error when I try to insert a NULL value:

google.cloud.bigquery.dbapi.exceptions.ProgrammingError: Encountered parameter None with value None of unexpected type.

from dotenv import load_dotenv
load_dotenv()  ##GOOGLE_APPLICATION_CREDENTIALS = 'api/google.json'
from google.cloud import bigquery
from google.cloud.bigquery import dbapi
import datetime

client = bigquery.Client()
connection = dbapi.Connection(client)

def write(query, data):
    cursor = connection.cursor()
    cursor.execute(query, data)
    connection.commit()
    return 'OK!'

data = (9, '857', '11013', None, datetime.datetime(2022, 9, 28, 15, 33, 13), datetime.datetime(2022, 9, 28, 15, 33, 13))

query = """
INSERT INTO `<project>.<dataset>.tmp_accountContacts`
(
id,account,contact,jobTitle,createdTimestamp,updatedTimestamp)
VALUES (%s,%s,%s,%s,%s,%s);
"""

write(query, data)

What's the issue?

Welcome and great first question! Assuming the data type of jobTitle is string can you please try: VALUES (%s,%s,%s,%(:string)s,%s,%s); and let me know if that works? — Ermiya Eskandary
– Ermiya Eskandary, Commented Oct 1, 2023 at 11:56
That worked! Why is is this necessary with Bigquery and not with for example MySQL? — croder
– croder, Commented Oct 1, 2023 at 12:05

Ermiya Eskandary · Accepted Answer · 2023-10-01 12:55:48Z

The error (unexpected type) indicates that BigQuery DB-API can't detect the type for the parameter, since it's None.

The docs explain this:

BigQuery requires type information for parameters. The BigQuery DB-API can usually determine parameter types for parameters based on provided values. Sometimes, however, types can’t be determined (for example when None is passed) or are determined incorrectly (for example when passing a floating-point value to a numeric column).

You need to explicitly specify the data type when you want to pass through None.

In your case, you need to specify that jobTitle is a string (and do the same for any other parameter that you could pass None through for).

INSERT INTO `<project>.<dataset>.tmp_accountContacts`
(id,account,contact,jobTitle,createdTimestamp,updatedTimestamp)
VALUES (%s,%s,%(:string)s,%s,%s,%s);

Technically, the library could just convert None to NULL in the final BigQuery SQL query so not sure why it doesn't, as BigQuery itself doesn't need to know the type when storing NULL 🤷🏻‍♂️

The docs saying:

BigQuery requires type information for parameters

should probably say:

BigQuery DB-API requires type information for parameters

Collectives™ on Stack Overflow

Why do I get unexpected type when using None for Google BigQuery DB-API?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related