Underscore Prepended to Numeric Column Labels

[b-schmeling]

Environment details

• OS type and version: MacOS Sonoma 4.5 (23F79)
• Python version: 3.11.6
• pip version: `23.3.1
• sqlalchemy-bigquery version: 1.11.0

Steps to reproduce

1. Attempt to label a column with a numeric column name (with or without quoted_name)
2. Get the cursor description for this column. The name has a prepended underscore

Code example

import sqlalchemy as sa
engine = ****insert engine creation here****

with engine.connect() as conn:
    a = sa.literal("a")
    labeled_col = a.label(sa.sql.quoted_name("2", quote=True))
    res = conn.execute(sa.select(labeled_col))
    name = res.cursor.description[0].name

    res_2 = conn.execute("SELECT 'a' AS `2`")
    name_2 = res_2.cursor.description[0].name

assert name == name_2 == "2"

Stack trace

AssertionError: assert '_2' == '2'

[Linchin]

Hi @b-schmeling, thanks for opening the issue! I think bigquery requires that column names start with either an underscore or letter, as said in the documentation. A new feature flexible column name is currently in preview, when it's GA'd you would be able to use numerical column names.

I will close the issue now, but feel free to leave a comment if you have any further question!

[b-schmeling]

I think bigquery requires that column names start with either an underscore or letter, as said in the documentation.

You can already use a number as a column name as long as it's quoted. As per the documentation here, "Column names can be quoted identifiers or unquoted identifiers."

This is demonstrated by running the SQL:

CREATE TABLE <dataset_id>.tmp AS (SELECT 'a' as `2`);

Which creates a table with a column name "2" with no problem.

[Linchin]

Thank you, indeed the backtick seems to work in this case, but I'm not entirely sure if this was by design or a happy accident. I will clarify with the docs team about what the intended format is here (I still feel like column name wouldn't allow string starting with numbers, as this is exactly the thing flexi-name project is trying to resolve. I wonder, by any chance, is your project in the preview list for flexi-name? Maybe that will explain why (SELECT 'a' as `2`) works.)