CODEWITHSUNDEEP
pythonsql-serversqlalchemypyodbc
Elisson Silva
Elisson Silva

Reputation: 71

SQLAlchemy puts square brackets around SQL Server table name

I'm trying to use SQLAlchemy with pyodbc using the Microsoft ODBC Driver for Linux, but when I use the session.query() function, I get the following error:

pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'TableName'. (208) (SQLExecDirectW)")

So I saw that the query created by ORM had square brackets on TableName.

SELECT 
    [mb.Tecnico].cod_tecnico AS [mb.Tecnico_cod_tecnico], 
    [mb.Tecnico].nome AS [mb.Tecnico_nome], 
    [mb.Tecnico].login AS [mb.Tecnico_login], 
    [mb.Tecnico].senha AS [mb.Tecnico_senha] 
FROM 
    [mb.Tecnico] 
ORDER BY 
    [mb.Tecnico].nome

Upvotes: 4

Views: 1271

Answers (2)

Ed Avis
Ed Avis

Reputation: 1502

This is because in MSSQL, the square brackets can't surround the fully qualified table name including schema, but must appear around the schema and name-within-schema separately. In other words you can't write [mb.Tecnico] but have to say [mb].[Tecnico] or else leave it unquoted as mb.Tecnico. The same annoying rule applies if you quote with "" instead of [].

So if you pass the string mb.tecnico as the table name it gets quoted in its entirety, generating invalid SQL.

class Technico(Base):
    __tablename__ = "mb.technico"
    cod_technico: Mapped[int] = mapped_column(primary_key=True)


qry = select(Technico)
print(qry.compile(engine))
"""
SELECT [mb.technico].cod_technico 
FROM [mb.technico]
"""

In this case we need to specify the schema name mb and the name-within-schema tecnico as separate strings

class Technico(Base):
    __tablename__ = "technico"
    __table_args__ = {"schema": "mb"}
    cod_technico: Mapped[int] = mapped_column(primary_key=True)


qry = select(Technico)
print(qry.compile(engine))
"""
SELECT mb.technico.cod_technico 
FROM mb.technico
"""

Upvotes: 1

SqlZim
SqlZim

Reputation: 38063

When the names of identifiers do not comply with the rules for regular identifiers, they must be delimited by double quotation marks or brackets.

Under "Rules for Regular Identifiers" on MSDN

  1. Embedded spaces or special characters are not allowed.

Upvotes: 0

Related Questions