SQL Server SQL code works in Azure, but Sqlalchemy throws ProgrammingErrors: Invalid column name OR multi-part identifier could not be bound

bgibtngc 于 12个月前发布在其他

关注(0)|答案(1)|浏览(87)

I'm trying to run an sql query that works as native sql code in a program like Azure, but throws programming errors when I try to use the exact code in python using SQLAlchemy with ODBC and pandas.

SQL code:

Select RecordNumbers                                               -- ID des Falles
   , Min(T.Created) As FirstRecord                      -- erste
From Data.Table1 V
   Left Outer Join Data.Table2Version TV 
       On V.VersionNumber=TV.VersionNumber
   Left Outer Join Data.Table3 T 
       On TV.IdT=T.IdT 
       And T.Type=11 -- nur Export
Where RType=1       -- nur was
   And Doob1=1                     -- nur was auch
   And Doob2=1                    -- nur anderes
Group By RecordNumber

This returns the two selected columns.

In SQLAlchemy, after establishing and testing a connection - dbConnection - I store query as text and use pandas to read the query:

from sqlalchemy import text
query = text("Select RecordNumbers,                                                     \
                          Min(T.Created) As FirstRecord    -- erste \
                    From Data.Table1 V    

                                                 \
                    Left Outer Join Data.Table2Version TV On V.VersionNumber=TV.VersionNumber \
                    Left Outer Join Data.Table3 T                                        \
                        ON TV.IdT=T.IdT                                     \
                        AND T.Type=11        -- nur Export       \
                Where (RType=1                        -- nur was  \
                    And Doob=1                          -- nur was auch                   \
                    And Doob2=1 )                        -- nur anderes    \
                Group By IdRecord                                                           \
                Order By 1 Desc")

out = pd.read_sql_query(query, dBConnection)

This throws two errors for both variables in the select part of the query.

ProgrammingError: (pyodbc.ProgrammingError) ('42S22', '[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name "FirstRecord".
The multi-part identifier "T.CreatedAt" could not be bound.

Replacing the selected variables with "Select * From..." solves the problem, but the output is too large to be functional solution.

Trying to solve invalid column name, if I explicitly define the location of V.FirstRecord, it throws the same multi-part identifier error for this varible.

I've read about explicit and implicit queries, but think everything here is pretty explicit... maybe.

SQLAlchemy says: This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.

I'm not very good at SQL, so would greatly appreciate being pointed in the right direction.

sql-server

来源：https://stackoverflow.com/questions/77644424/sql-code-works-in-azure-but-sqlalchemy-throws-programmingerrors-invalid-column

1条答案

按热度按时间

lokaqttq1#

A string created like this:

q = ('SELECT * \
     FROM users  \
     WHERE id > 10 -- some comment \
     ORDER BY id'
)

does not actually contain any end of line characters. When printed, the output is

SELECT *      FROM users       WHERE id > 10 -- some comment      ORDER BY id

The inline comment effectively terminates parsing of the query.

As AlwaysLearning suggested, removing the comments will fix this. Another approach is to use triple-quoted strings which will create a multi-line string.

q = ("""SELECT * 
     FROM users  
     WHERE id > 10 -- some comment 
     ORDER BY id"""
)

赞(0）回复(0）举报 12个月前

我来回答

SQL Server SQL code works in Azure, but Sqlalchemy throws ProgrammingErrors: Invalid column name OR multi-part identifier could not be bound

1条答案

相关问题

热门标签

最新问答