How do I run a query in Cloud Spanner and download the results to a pandas DataFrame?

A quick and dirty way to get spanner results into a pandas data frame.

import pandas as pd
from google.cloud import spanner

# Initialize client
client = spanner.Client()

# Get a Cloud Spanner instance by ID.
instance = client.instance('instance-name')

# Get a Cloud Spanner database by ID.
database = instance.database('database-name')

with database.snapshot() as snapshot:
    result = snapshot.execute_sql("SELECT * FROM somewhere")
    
    # Stream in rows
    rows = list()
    for row in result:
        rows.append(row)

    # Get column names
    cols = [x.name for x in result.fields]

    # Convert to pandas dataframe
    result_df = pd.DataFrame(rows, columns = cols)

This likely won't scale and you may run into issues with Spanner types vs pandas types, but it will solve the immediate problem of "I want to analyze data from Spanner in pandas."

Pandas lib is using sqlalchemy, so we can use this doc: https://cloud.google.com/spanner/docs/use-sqlalchemy

pip install sqlalchemy-spanner

then in python code (if sqlalchemy version >= 1.4):

import pandas as pd

url = 'spanner+spanner:///projects/project-id/instances/instance-id/databases/database-id'
sql = 'SELECT * FROM my_table;'

df = pd.read_sql(sql, url, index_col='id_column')

or in case it's sqlalchemy version 1.3:

...
url = 'spanner:///projects/project-id/instances/instance-id/databases/database-id'
...

To use Python to query your data in Cloud Spanner yoou need to install and use the Python Cloud Spanner client library.

As of now there is no a straightforward way to download data from Spanner to Pandas DataFrame.

I would suggest to use the "StreamedResultSet API" to export your data to Pandas.

Also please take a look at this post about streaming data from Cloud Spanner to Panda dataframe, as it may be proven helpful implementing your use case as well.