Creating flask-sqlalchemy table object of a table on a linked server

Question

I am creating a small flask website with a Microsoft SQL database server. The web server is on a linux machine and I connecting to the database via odbc -> pyodbc & flask-sqlalchemy.

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.engine import URL

connection_url = URL.create(
            "mssql+pyodbc",
            username="user",
            password="password",
            host="192.168.0.128",
            port=1433,
            database="Print",
            query={"driver": "ODBC Driver 17 for SQL Server"})

app = Flask(__name__)
app.config['SECRET_KEY'] = 'hard to guess string'
app.config['SQLALCHEMY_DATABASE_URI'] = connection_url
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

The exsisting local database tables are no problem to connect to.

class Print(db.Model):
    __tablename__ = 'Printers'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(24))

    def __repr__(self):
        return '<Name %r>' % self.name

But when I try to access a table from a linked server the query gets mangled somewhere in the translation.

class Collo(db.Model):
    __tablename__ = 'collo'
    __table_args__ = {'schema': 'sprod..sdba'}
    type = db.Column(db.String(8), primary_key=True)
    description = db.Column(db.String(30))

    def __repr__(self):
        return '<collo %r>' % self.description

The error:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'sprod..sdba.collo'. (208) (SQLExecDirectW)")
[SQL: SELECT TOP 1 [sprod.].sdba.collo.type AS sprod__sdba_collo_type, [sprod.].sdba.collo.description AS sprod__sdba_collo_description
FROM [sprod.].sdba.collo
WHERE [sprod.].sdba.collo.type = ?]
[parameters: ('GOOT2',)]

Under sql management studio the query is: select * from [SPROD]..[sdba].[collo]

selecting in pyodbc: cursor.execute("select * from sprod..sdba.collo where type = 'GOOT2'" ) works fine.

How can I prevent the inclusion of the square brackets in flask-sqlalchemy?

Answer 1

This is a known issue with SQLAlchemy and SQL Server "linked servers" as discussed in this GitHub issue. The current workaround is to create a SYNONYM on the local server for the table on the linked server as described here, e.g.,

CREATE SYNONYM [dbo].[hello_199] FOR [DOCKER199].[myDb].[dbo].[hello]