Pandas Data Types for SQL Systems

1.5.0 · active · verified Sun Mar 29

db-dtypes is a specialized Python library that provides Pandas Extension Dtypes to represent and interact with data types commonly found in relational databases, particularly BigQuery and Spanner. It aims to ensure data integrity and improve performance by providing faithful representations of types like Date, Time, and JSON. The library is actively maintained by Google, with multiple releases per year, and its current version is 1.5.0.

Warnings

breaking Python 2 support was dropped as of January 1, 2020. The library currently requires Python >= 3.9. Older versions of the library (1.4.3 onwards) also explicitly dropped support for Python 3.7 and 3.8.
Fix: Ensure your environment uses Python 3.9 or newer.
breaking With `google-cloud-bigquery` version 3.0.0, `db-dtypes` became a required dependency for the `pandas` extra. BigQuery's `DATE` and `TIME` data types now map directly to `dbdate` and `dbtime` dtypes, and the `date_as_object` parameter was removed.
Fix: Update `google-cloud-bigquery` to a compatible version and ensure `db-dtypes` is installed. Remove `date_as_object` parameter if previously used.
gotcha For `db-dtypes` extension types (like `dbdate`, `dbtime`, `dbjson`) to be recognized by pandas, the `db_dtypes` module must be imported at least once. This import performs the necessary registration of the custom dtypes.
Fix: Always include `import db_dtypes` at the beginning of your script or session. It's often accompanied by `# noqa: F401` to prevent linting warnings about unused imports.
gotcha When converting BigQuery `DATE` data to pandas `dbdate` dtype, any date values falling outside the range of `pandas.Timestamp.min` (1677-09-22) and `pandas.Timestamp.max` (2262-04-11) will map to the generic `object` dtype in pandas instead of `dbdate`.
Fix: Be aware of the valid date range when processing historical or futuristic date data from BigQuery that uses the `dbdate` dtype, and handle `object` dtype columns appropriately if out-of-bounds dates are expected.
gotcha Standard Pandas `float64` can introduce precision errors when dealing with database `DECIMAL` or `NUMERIC` types. While `db-dtypes` aims to improve type fidelity, there isn't a direct `DecimalDtype` class for explicit import like `DateDtype`.
Fix: Rely on the integration with BigQuery connectors (e.g., `google-cloud-bigquery`) to handle `DECIMAL` types. If working with generic SQL and Pandas, consider explicit conversion to Python's `decimal.Decimal` objects or using other libraries that provide exact decimal dtypes.

Install

pip install db-dtypes Install latest version

Imports

db_dtypes
```
import db_dtypes # noqa: F401
```
Importing the module registers the extension dtypes with pandas. The '# noqa: F401' is often added to suppress 'unused import' warnings as the import's primary purpose is side-effect registration.
DateDtype
```
from db_dtypes import DateDtype
```
While 'import db_dtypes' registers all, explicit import is for direct reference, e.g., in `pd.Series(dtype=DateDtype())`.
TimeDtype
```
from db_dtypes import TimeDtype
```
JSONDtype
```
from db_dtypes import JSONDtype
```

Quickstart

This quickstart demonstrates how to initialize pandas Series with custom `dbdate`, `dbtime`, and `dbjson` dtypes provided by the `db-dtypes` library. The initial `import db_dtypes` is crucial for registering these extension types with pandas.

import datetime
import pandas as pd
import db_dtypes # noqa: F401

# Using dbdate dtype
dates = pd.Series([datetime.date(2023, 1, 1), '2023-01-02'], dtype='dbdate')
print('Dates Series:')
print(dates)

# Using dbtime dtype
times = pd.Series([datetime.time(10, 30, 0), '15:45:00.123'], dtype='dbtime')
print('\nTimes Series:')
print(times)

# Using dbjson dtype
json_data = pd.Series([{'key': 'value'}, [1, 2, 3], 'null'], dtype='dbjson')
print('\nJSON Series:')
print(json_data)

view raw JSON →