pandas-gbq

import os
import pandas as pd
import pandas_gbq

# Set your Google Cloud Project ID
# It's recommended to set this as an environment variable or via credentials
project_id = os.environ.get('GOOGLE_CLOUD_PROJECT', 'your-gcp-project-id')

# --- Reading data from BigQuery ---
# Example query from a public dataset
sql_query = """
    SELECT country_name, alpha_2_code
    FROM `bigquery-public-data.utility_us.country_code_iso`
    WHERE alpha_2_code LIKE 'U%'
    LIMIT 5
"""

try:
    df_read = pandas_gbq.read_gbq(sql_query, project_id=project_id)
    print("\n--- Data read from BigQuery ---")
    print(df_read)
except Exception as e:
    print(f"Error reading from BigQuery: {e}")
    print("Please ensure GOOGLE_CLOUD_PROJECT is set and you have authenticated (e.g., `gcloud auth application-default login`).")

# --- Writing data to BigQuery ---
# Create a sample DataFrame to upload
data = {
    'col1': [1, 2, 3],
    'col2': ['A', 'B', 'C'],
    'timestamp_col': pd.to_datetime(['2026-01-01', '2026-01-02', '2026-01-03'])
}
df_write = pd.DataFrame(data)

# Define destination table (dataset.tablename)
destination_table = 'my_test_dataset.my_test_table'

# To avoid errors, you might want to replace the table if it exists for testing
# In production, consider 'append' or 'fail' with proper checks
try:
    pandas_gbq.to_gbq(
        df_write,
        destination_table,
        project_id=project_id,
        if_exists='replace' # Options: 'fail', 'replace', 'append'
    )
    print(f"\n--- DataFrame successfully written to {destination_table} in project {project_id} ---")
except Exception as e:
    print(f"Error writing to BigQuery: {e}")
    print("Ensure 'my_test_dataset' exists in BigQuery or remove 'my_test_dataset.' from 'destination_table' to allow automatic dataset creation if permitted.")