INSERT failure with SQLAlchemy ORM / SQL Server 2017 - Invalid Object Name

Question

Firstly, I have seen many answers which is specific to the Invalid Object Name error working with SQL Server, but None of them seem to solve my problem. I don't have much idea on SQL Server dialect, but here is my current setup required on the project.

SQL Server 2017
SQLAlchemy (pyodbc+mssql)
Python 3.9

I'm trying to insert a database row, using the SQLAlchemy ORM, but it fails to resolve the schema and table, giving me the error of type

(pyodbc.ProgrammingError) ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'agent.dbo.tasks'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

I'm creating a session with the following code.

engine = create_engine(connection_string, pool_size=10, max_overflow=5,
                       pool_use_lifo=False, pool_pre_ping=True, echo=True, echo_pool=True)
db_session_manager = sessionmaker()
db_session_manager.configure(bind=engine)
session = db_session_manager()

I have a task object defined like

class Task(BaseModel):
    __tablename__ = "tasks"
    __table_args__ = {"schema": "agent.dbo"}
    # .. field defs

I'm trying to fill the object fields and then do the insert like the usual

task = Task()
task.tid = 1
...
session.add(task)
session.commit()

But this fails, with the error mentioned before. I tried to execute a direct query like

session.execute("SELECT * FROM agent.dbo.tasks")

And it returned a result set. The connection string is a URL object, which prints like

mssql+pyodbc://task-user:******@CRD4E0050L/agent?driver=ODBC+Driver+17+for+SQL+Server

I tried to use the SQL Server Management Studio to insert manually and check, there it shown me a sql dialect with [] as field separators like

INSERT INTO [dbo].[tasks]
           ([tid]..

, but SQLAlchemy on echo did not show that, instead it used the one I see in MySQL like

INSERT INTO agent.dbo.tasks (onbase_handle_id,..

What is that I'm doing wrong ? I thought SQLAlchemy if configured with a supported dialect, should work fine (I use it on MySQL quite well). Am I missing any configuration ? Any help appreciated.

INSERT failure with SQLAlchemy ORM / SQL Server 2017 - Invalid Object Name

0 Answers