如何将SQL Server的ON [PRIMARY]转换为SQLAlchemy ORM模型并解决主键错误？

Hey there! Let's break down why you're hitting this error and how to fix it step by step.

The Root Cause #

SQL Server allows creating tables without a primary key, but SQLAlchemy ORM requires a primary key for every mapped model. This is because the ORM needs a unique way to identify individual records, track their state changes, and handle operations like updates/deletes reliably.

Solution 1: Add a Primary Key to the Database (Recommended) #

The best practice is to update your SQL Server table to include a primary key—it boosts performance, ensures data integrity, and plays seamlessly with ORMs. Since userid is likely meant to uniquely identify users, let's set it as the primary key:

Updated SQL Create Table Statement

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[UserModel]( 
    [userid] [nvarchar](255) NOT NULL PRIMARY KEY, 
    [username] [nvarchar](255) NULL, 
    [serialnumber] [nvarchar](255) NULL 
) ON [PRIMARY] 
GO

Corresponding SQLAlchemy Model

Add primary_key=True to the userid column to match the updated database schema:

class UserModel(db.Model):
    __tablename__ = 'UserModel'
    userid = Column('userid', Unicode(255), primary_key=True)
    username = Column('username', Unicode(255))
    serialnumber = Column('serialnumber', Unicode(255))

Solution 2: Map an Existing Table Without a Primary Key (Workaround) #

If you can't modify the existing database table (e.g., it's already populated with data), you can still map it by telling SQLAlchemy to use a column as the "logical" primary key (even if the database doesn't enforce it). Add __table_args__ to handle the existing table, and mark a unique column as the primary key:

class UserModel(db.Model):
    __tablename__ = 'UserModel'
    __table_args__ = {'extend_existing': True}  # Allows mapping an existing table without a PK
    # Use userid as the logical primary key (ensure it's unique in practice!)
    userid = Column('userid', Unicode(255), primary_key=True)
    username = Column('username', Unicode(255))
    serialnumber = Column('serialnumber', Unicode(255))

Note: Even if you use this workaround, it's strongly recommended to add a real primary key to the database eventually. Tables without primary keys can lead to unexpected behavior, slower queries, and data consistency issues.

内容的提问来源于stack exchange，提问作者Dheeraj