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?

Answer 1

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