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.)

[b-schmeling]

Apologies for not following up for so long. We are still having this issue come up.

I wonder, by any chance, is your project in the preview list for flexi-name?

It would make sense if we were at the time. It seems like it's GA now, is that correct?

Also, we were able to find which code is adding the underscore:

python-bigquery-sqlalchemy/sqlalchemy_bigquery/base.py

Lines 110 to 121 in 05148cd

	def format_label(self, label, name=None):
	name = name or label.name
	# Fields must start with a letter or underscore
	if not name[0].isalpha() and name[0] != "_":
	name = "_" + name
	# Fields must contain only letters, numbers, and underscores
	name = FIELD_ILLEGAL_CHARACTERS.sub("_", name)
	result = self.quote(name)
	return result

See the lines:

# Fields must start with a letter or underscore
if not name[0].isalpha() and name[0] != "_":
    name = "_" + name

It should be relatively simple to also check whether the first character is a number, and to not prepend the underscore in that case. I believe this would work because the identifier is quoted right after?