python - ProgrammingError : (psycopg2. errors.UndefinedColumn)，同时使用 sqlalchemy

Question

我在查询在 postgres db(本地)上使用 sqlalchemy 创建的表时遇到问题。

虽然我能够执行并通过以下方式接收查询结果:

SELECT * FROM olympic_games 

当我尝试访问单列或对表执行任何其他操作时收到错误消息:

SELECT games FROM olympic_games

错误信息是(从波兰语翻译的几句话):

ProgrammingError: (psycopg2.errors.UndefinedColumn) BŁĄD: column "games" does not exist

LINE 1: SELECT COUNT(Sport)
^
HINT: maybe you meant "olympic_games.Games".

SQL: SELECT games FROM olympic_games LIMIT 5;]
(Background on this error at: http://sqlalche.me/e/f405)

它几乎总和该程序看不到，或者可以访问特定列，并显示它不存在。

我尝试使用 table.column 访问格式，它也不起作用。我还可以通过 information_schema.columns 查看列名

数据 (.csv) 已加载 pd.read_csv ，然后 DataFrame.to_sql .代码如下，感谢帮助!

engine = create_engine('postgresql://:@:/olympic_games')

with open('olympic_athletes_2016_14.csv', 'r') as file:
    df = pd.read_csv(file, index_col='ID')
df.to_sql(name = 'olympic_games', con = engine, if_exists = 'replace', index_label = 'ID')

两个执行命令都返回相同的错误:

with engine.connect() as con:
    rs = con.execute("SELECT games FROM olympic_games LIMIT 5;")
    df_fetch = pd.DataFrame(rs.fetchall())

df_fetch2 = engine.execute("""SELECT games FROM olympic_games LIMIT 5;""").fetchall()

Answer 1

本质上，这是 PostgreSQL manual 中提到的列标识符的双引号问题。 :

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other.

当您的任何 Pandas 数据框列包含大小写混合时， DataFrame.to_sql通过在 CREATE TABLE 处创建带双引号的列来保持区分大小写阶段。具体来说，使用replace时的以下Python Pandas代码

df.to_sql(name='olympic_games', con=engine, if_exists='replace', index_label='ID')

如果 Sport 在 Postgres 中翻译如下是数据框中的标题 case 列:

DROP TABLE IF EXISTS public."olympic_games";

CREATE TABLE public."olympic_games"
(
    ...
    "Sport" varchar(255)
    "Games" varchar(255)
    ...
);

一旦标识符被混合大小写引用，它必须始终以这种方式引用。因此 sport与 "Sport" 不一样.请记住，在 SQL 中，双引号实际上与单引号不同，单引号在 Python 中可以互换。

要修复，请考虑将所有 Pandas 列渲染为小写，因为 "games"与 games 相同, Games或 GAMES (但不是 "Games" 或 "GAMES" )。

df.columns = df.columns.str.lower()
df.to_sql(name='olympic_games', con=engine, if_exists='replace', index_label='ID')

或者，保持原样并适当引用:

SELECT "Games" FROM olympic_games