FastAPI集成SQLAlchemy报'str' object has no attribute 'c'错误

问题场景 #

跟随freeCodeCamp平台教程学习FastAPI框架，集成SQLAlchemy实现数据库操作时，项目运行触发'str' object has no attribute 'c'报错。

项目文件结构 #

App
|_ database.py
|_ main.py
|_ models.py
|_ __init__.py

原始核心代码 #

• main.py

from fastapi import Body, Depends, FastAPI,Response,status,HTTPException
from fastapi.params import Body
from pydantic import BaseModel
from random import randrange
import psycopg2
from psycopg2.extras import RealDictCursor
import time
from sqlalchemy.orm import Session
from database import Base
import models
from database import engine,SessionLocal
app = FastAPI()

models.Base.metadata.create_all(bind=engine)


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

class postparam(BaseModel):
    title: str
    content:str
    Published: bool =True
    
while True:
    try:
        conn = psycopg2.connect(host= 'localhost',database='FastApi',user='postgres',password='2580',cursor_factory=RealDictCursor)
        cursor = conn.cursor()
        print('Database connection Established!!')
        break
    except Exception as error:
        print("connection to database failed !!")
        print('Error: ',error)
        time.sleep(2)


@app.get("/sql")
async def root(db:Session =Depends(get_db)):
    return {"message":"this is a sample fastapi"}

• database.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "postgresql://postgres:2580@localhost/Fastpi"
engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

• models.py

from sqlalchemy import Column,Integer,String,Boolean
from database import Base


class Post(Base):
    __table__ = 'posts'
    id = Column(Integer,primary_key= True,nullable=False)
    name = Column(String,nullable =False)
    content = Column(String,nullable = False)
    published = Column(Boolean,default= False)

报错根因 #

• 核心错误在models.py的ORM表配置：SQLAlchemy声明式模型指定表名的固定属性为 __tablename__，代码中误写为__table__。执行models.Base.metadata.create_all(bind=engine)时，SQLAlchemy会读取每个模型的__table__属性（该属性正常应为Table实例对象），由于此处赋值为字符串'posts'，后续框架尝试访问表对象的列集合属性c时，就会触发'str' object has no attribute 'c'报错。
• 附带拼写错误：database.py中数据库连接URL的库名写为Fastpi，和代码中psycopg2连接的库名FastApi不一致，修复表名问题后会触发数据库不存在的连接错误。

修复步骤 #

1. 修改models.py的表名配置，将__table__ = 'posts'替换为__tablename__ = 'posts'，修正后完整models.py代码如下：

from sqlalchemy import Column,Integer,String,Boolean
from database import Base


class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer,primary_key= True,nullable=False)
    name = Column(String,nullable =False)
    content = Column(String,nullable = False)
    published = Column(Boolean,default= False)

2. 修改database.py中的数据库连接URL，修正库名拼写错误：
   SQLALCHEMY_DATABASE_URL = "postgresql://postgres:2580@localhost/FastApi"
3. 重启FastAPI服务，报错即可消除，表结构会正常自动创建。

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