BUG: pandas dataframe column definition or mapping does not cater for upper case values.

[c8-devops]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
import psycopg2
from sqlalchemy import create_engine

conn_string = "postgresql://postgres:password@localhost/database"
engine = create_engine(conn_string)

query = """
select 
	p.id as CustomerId
from table
"""

df = pd.read_sql(query, engine)

dtype_mappings = {
	'CustomerId': int
}

df = df.astype(dtype_mappings)

Issue Description

When running the code we get the following error

.venv\lib\site-packages\pandas\core\generic.py", line 6605, in astype
raise KeyError(
KeyError: "Only a column name can be used for the key in a dtype mappings argument. 'CustomerId' not found in columns."

If we change the column name to all lower case, the code works.

Expected Behavior

pandas should support both upper and lower case for the column names

Installed Versions

Name: pandas
Version: 2.2.2

[Aloqeely]

Thanks for the report! Could you provide a reproducer without using read_sql?
Manually creating a DataFrame with a column named CustomerId and then running astype(dtype_mappings) works fine for me.