Skip to content

SQL Alchemy Bugs Resolutions

Issue: SQL table construnction error (multiple errors)

...

surfwatch-server  |   File "/app/server/main.py", line 3, in <module>
surfwatch-server  |     from database.database import db_init, get_db
surfwatch-server  |   File "/app/server/database/database.py", line 10, in <module>
surfwatch-server  |     from database.models.site import DailyCrowdnessPrediction, RawCrowdnessReading, Site, WeeklyCrowdnessPrediction
surfwatch-server  |   File "/app/server/database/models/site.py", line 84, in <module>
surfwatch-server  |     class WaveQualityReading(Base):

...

surfwatch-server  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1652, in _configure_pks
surfwatch-server  |     raise sa_exc.ArgumentError(
surfwatch-server  | sqlalchemy.exc.ArgumentError: Mapper Mapper[WaveQualityReading(wave_quality_readings)] could not assemble any primary key columns for mapped table 'wave_quality_readings'

...

surfwatch-server  | sqlalchemy.exc.CompileError: (in table 'wave_quality_readings', column 'wave_direction'): VARCHAR requires a length on dialect mysql

...

surfwatch-server  | sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'Mapper[Site(sites)]'. Original exception was: Mapper 'Mapper[WaveQualityReading(wave_quality_readings)]' has no property 'sites'.  If this property was indicated from other mappers or configure events, ensure registry.configure() has been called.
server/main.py
1
2
3
from typing import Dict, List, Optional

from database.database import db_init, get_db
server/database/database.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
import os
from typing import AsyncGenerator
from dotenv import load_dotenv
from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker

from database.models.user import Base, UserProfile, UserAuth
from database.models.site import DailyCrowdnessPrediction, RawCrowdnessReading, Site, WeeklyCrowdnessPrediction
server/database/models/site.py
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
class Site(Base):
"""SQLAlchemy Site model corresponding to the database table."""
__tablename__ = "sites"
site_id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
site_name: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)
site_name_short: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)

site_desc: Mapped[str] = mapped_column(Text, nullable=True)
site_url: Mapped[str] = mapped_column(String(255), nullable=True)
site_banner_url: Mapped[str] = mapped_column(String(255), nullable=True)

daily_prediction = relationship("DailyCrowdnessPrediction", uselist=False, back_populates="sites", primaryjoin="Site.site_id==DailyCrowdnessPrediction.site_id")
weekly_prediction = relationship("WeeklyCrowdnessPrediction", uselist=False, back_populates="sites", primaryjoin="Site.site_id==WeeklyCrowdnessPrediction.site_id")
wave_quality = relationship("WaveQualityReading", back_populates="sites", primaryjoin="Site.site_id==WaveQualityReading.site_id")
def __repr__(self):
    return f"<Site(site_id={self.site_id}, name='{self.name}')>"
server/database/models/site.py
84
85
86
87
88
89
90
91
92
class WaveQualityReading(Base):
    """SQLAlchemy WaveQualityReading model corresponding to the database table."""
    __tablename__ = "wave_quality_readings"

    site_id: Mapped[int] = mapped_column(Integer, ForeignKey("sites.site_id"), nullable=False)
    wave_height: Mapped[float] = mapped_column(Integer, nullable=False)
    wave_speed: Mapped[float] = mapped_column(Integer, nullable=False)
    wave_direction: Mapped[float] = mapped_column(String, nullable=False)
    temperature: Mapped[int] = mapped_column(Integer, nullable=False)

Primary Key Error

sqlalchemy.exc.ArgumentError: Mapper Mapper[WaveQualityReading(wave_quality_readings)] could not assemble any primary key columns for mapped table 'wave_quality_readings'

  • The error indicates that SQLAlchemy is unable to determine the primary key for the wave_quality_readings table.
  • This typically happens when the model does not define a primary key column or the primary key is not correctly mapped.

String datatype in SqlAlchemy

  • The wave_direction field is defined as Mapped[float] = mapped_column(String, nullable=False), which is incorrect.
  • it will further cause this error:

    surfwatch-server | sqlalchemy.exc.CompileError: (in table 'wave_quality_readings', column 'wave_direction'): VARCHAR requires a length on dialect mysql

  • The String type should be used with a length parameter, e.g., String(255).

Foreign Key Relationship

  • A hidden issue is not exposed in the error logs, it is:

surfwatch-server | sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'Mapper[Site(sites)]'. Original exception was: Mapper 'Mapper[WaveQualityReading(wave_quality_readings)]' has no property 'sites'. If this property was indicated from other mappers or configure events, ensure registry.configure() has been called.

  • note in the Site model, the wave_quality relationship is defined as wave_quality = relationship("WaveQualityReading", back_populates="sites", ...)
  • but in the WaveQualityReading model, the sites relationship is not defined.

Primary Key Error

  • SQLAlchemy requires each mapped class to have a primary key defined.
  • The primary key can be defined using primary_key=True in the mapped_column definition.
  • If no primary key is defined, SQLAlchemy cannot uniquely identify rows in the table.
  • source: SQLAlchemy Documentation

String datatype in SqlAlchemy

  • In SQLAlchemy, the String type requires a length parameter when used with MySQL.
  • Failure to specify a length will result in a CompileError.
  • source: SQLAlchemy String Type Documentation

Bidirectional relationships model binding

  • In SQLAlchemy, when defining bidirectional relationships, both sides of the relationship must be defined.
  • If one side of the relationship is not defined, SQLAlchemy will raise an InvalidRequestError.
  • This is because SQLAlchemy needs to know how to navigate the relationship in both directions.
  • source: SQLAlchemy Relationships Documentation
server/database/models/site.py
84
85
86
87
88
89
90
91
92
93
94
class WaveQualityReading(Base):
    """SQLAlchemy WaveQualityReading model corresponding to the database table."""
    __tablename__ = "wave_quality_readings"

    site_id: Mapped[int] = mapped_column(Integer, ForeignKey("sites.site_id"), nullable=False, primary_key=True)
    wave_height: Mapped[float] = mapped_column(Integer, nullable=False)
    wave_speed: Mapped[float] = mapped_column(Integer, nullable=False)
    wave_direction: Mapped[str] = mapped_column(String(30), nullable=False)
    temperature: Mapped[int] = mapped_column(Integer, nullable=False)

    sites = relationship("Site", back_populates="wave_quality")

SQLAlchemy lazy loading

surfwatch-server  |   File "/app/server/main.py", line 163, in get_recommendations
surfwatch-server  |     return await site_controller.get_all_sites(db)
surfwatch-server  |   File "/app/server/controllers/site_controller.py", line 33, in get_all_sites
surfwatch-server  |     return [PydanticSite.model_validate(site) for site in sites]
surfwatch-server  |   File "/app/server/controllers/site_controller.py", line 33, in <listcomp>
surfwatch-server  |     return [PydanticSite.model_validate(site) for site in sites]
surfwatch-server  |   File "/usr/local/lib/python3.9/site-packages/pydantic/main.py", line 703, in model_validate
surfwatch-server  |     return cls.__pydantic_validator__.validate_python(
surfwatch-server  | pydantic_core._pydantic_core.ValidationError: 1 validation error for Site
surfwatch-server  | wave_quality
surfwatch-server  |   Error extracting attribute: StatementError: (sqlalchemy.exc.MissingGreenlet) greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place?
surfwatch-server  | [SQL: SELECT wave_quality_readings.site_id AS wave_quality_readings_site_id, wave_quality_readings.wave_height AS wave_quality_readings_wave_height, wave_quality_readings.wave_speed AS wave_quality_readings_wave_speed, wave_quality_readings.wave_direction AS wave_quality_readings_wave_direction, wave_quality_readings.temperature AS wave_quality_readings_temperature
surfwatch-server  | FROM wave_quality_readings
surfwatch-server  | WHERE %s = wave_quality_readings.site_id]
surfwatch-server  | [parameters: [{'%(134176880071200 param)s': 1}]]
surfwatch-server  | (Background on this error at: https://sqlalche.me/e/20/xd2s) [type=get_attribute_error, input_value=<unprintable Site object>, input_type=Site]
surfwatch-server  |     For further information visit https://errors.pydantic.dev/2.11/v/get_attribute_error
server/controllers/site_controller.py
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
async def get_all_sites(db: AsyncSession) -> List[PydanticSite]:
"""
Get all sites from the database.
"""
result = await db.execute(select(Site).options(
    selectinload(Site.daily_prediction),
    selectinload(Site.weekly_prediction)
))
sites = result.scalars().all()

if not sites:
    raise HTTPException(status_code=404, detail="No sites found")

for site in sites:
    # do internal validation make sure the required fields are present
    assert site.site_id, f"Site ID {site.site_id} not found in database."
    assert site.site_name, f"Site ID {site.site_id} has no name"
    assert site.site_name_short, f"Site ID {site.site_id} has no short name"
    assert site.site_desc, f"Site ID {site.site_id} has no description"
    assert site.site_url, f"Site ID {site.site_id} has no URL"
    # assert site.site_banner_url, f"Site ID {site.site_id} has no banner URL"

return [PydanticSite.model_validate(site) for site in sites]
server/models/site.py
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
class WaveQualityReading(BaseModel):
    model_config = ConfigDict(
        from_attributes=True,
        populate_by_name=True
    )
    site_id: int
    wave_height: float
    wave_speed: float
    wave_direction: str
    temperature: int

class Site(BaseModel):
    model_config = ConfigDict(
        from_attributes=True,
        populate_by_name=True,
    )

    site_id: Optional[int] = None
    site_name: str
    site_name_short: str
    site_desc: Optional[str] = None
    site_url: Optional[str] = None
    site_banner_url: Optional[str] = None

    daily_prediction: Optional[DailyCrowdnessPrediction] = None
    weekly_prediction: Optional[WeeklyCrowdnessPrediction] = None
    wave_quality: Optional[WaveQualityReading] = None
server/database/models/site.py
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
class Site(Base):
    """SQLAlchemy Site model corresponding to the database table."""
    __tablename__ = "sites"
    site_id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    site_name: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)
    site_name_short: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)

    site_desc: Mapped[str] = mapped_column(Text, nullable=True)
    site_url: Mapped[str] = mapped_column(String(255), nullable=True)
    site_banner_url: Mapped[str] = mapped_column(String(255), nullable=True)

    daily_prediction = relationship("DailyCrowdnessPrediction", uselist=False, back_populates="sites", primaryjoin="Site.site_id==DailyCrowdnessPrediction.site_id")
    weekly_prediction = relationship("WeeklyCrowdnessPrediction", uselist=False, back_populates="sites", primaryjoin="Site.site_id==WeeklyCrowdnessPrediction.site_id")
    wave_quality = relationship("WaveQualityReading", back_populates="sites", primaryjoin="Site.site_id==WaveQualityReading.site_id")
    def __repr__(self):
        return f"<Site(site_id={self.site_id}, name='{self.name}')>"
server/database/models/site.py
84
85
86
87
88
89
90
91
92
93
94
class WaveQualityReading(Base):
    """SQLAlchemy WaveQualityReading model corresponding to the database table."""
    __tablename__ = "wave_quality_readings"

    site_id: Mapped[int] = mapped_column(Integer, ForeignKey("sites.site_id"), nullable=False, primary_key=True)
    wave_height: Mapped[float] = mapped_column(Integer, nullable=False)
    wave_speed: Mapped[float] = mapped_column(Integer, nullable=False)
    wave_direction: Mapped[str] = mapped_column(String(30), nullable=False)
    temperature: Mapped[int] = mapped_column(Integer, nullable=False)

    sites = relationship("Site", back_populates="wave_quality")

The "greenlet_spawn" error means that SQLAlchemy tried to do an async database operation (like loading a relationship) outside of an async context.

greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place?

It means SQLAlchemy tried to lazy-load a relationship (like wave_quality) after your async session was already done, or outside of an async function.

  • SQLAlchemy's async support requires that all database operations are done within an async context.
  • Lazy loading relationships in SQLAlchemy can only be done within an async function or coroutine.
  • If you try to access a lazy-loaded relationship outside of an async context, it will raise a MissingGreenlet error.
  • source: SQLAlchemy Async Documentation
  • load the wave_quality relationship eagerly using selectinload in the query.
  • ensure that all database operations are done within an async function or coroutine.
server/controllers/site_controller.py
11
12
13
14
15
16
17
18
19
async def get_all_sites(db: AsyncSession) -> List[PydanticSite]:
"""
Get all sites from the database.
"""
result = await db.execute(select(Site).options(
    selectinload(Site.daily_prediction),
    selectinload(Site.weekly_prediction),
    selectinload(Site.wave_quality)
))

Sql Alchemy relationships return list instead of single object

surfwatch-server  |   File "/app/server/controllers/site_controller.py", line 34, in get_all_sites
surfwatch-server  |     return [PydanticSite.model_validate(site) for site in sites]
surfwatch-server  |   File "/usr/local/lib/python3.9/site-packages/pydantic/main.py", line 703, in model_validate
surfwatch-server  |     return cls.__pydantic_validator__.validate_python(
surfwatch-server  | pydantic_core._pydantic_core.ValidationError: 5 validation errors for Site
surfwatch-server  | wave_quality.site_id
surfwatch-server  |   Field required [type=missing, input_value=[], input_type=InstrumentedList]
surfwatch-server  | wave_quality.wave_height
surfwatch-server  |   Field required [type=missing, input_value=[], input_type=InstrumentedList]
surfwatch-server  | wave_quality.wave_speed
surfwatch-server  |   Field required [type=missing, input_value=[], input_type=InstrumentedList]
surfwatch-server  | wave_quality.wave_direction
surfwatch-server  |   Field required [type=missing, input_value=[], input_type=InstrumentedList]
surfwatch-server  | wave_quality.temperature
surfwatch-server  |   Field required [type=missing, input_value=[], input_type=InstrumentedList]
server/database/models/site.py
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
class Site(Base):
    """SQLAlchemy Site model corresponding to the database table."""
    __tablename__ = "sites"
    site_id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    site_name: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)
    site_name_short: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)

    site_desc: Mapped[str] = mapped_column(Text, nullable=True)
    site_url: Mapped[str] = mapped_column(String(255), nullable=True)
    site_banner_url: Mapped[str] = mapped_column(String(255), nullable=True)

    daily_prediction = relationship("DailyCrowdnessPrediction", uselist=False, back_populates="sites", primaryjoin="Site.site_id==DailyCrowdnessPrediction.site_id")
    weekly_prediction = relationship("WeeklyCrowdnessPrediction", uselist=False, back_populates="sites", primaryjoin="Site.site_id==WeeklyCrowdnessPrediction.site_id")
    wave_quality = relationship("WaveQualityReading", back_populates="sites", primaryjoin="Site.site_id==WaveQualityReading.site_id")
    def __repr__(self):
        return f"<Site(site_id={self.site_id}, name='{self.name}')>"

The error occurs because the SQLAlchemy relationship wave_quality returns a list by default, but the Pydantic model expects a single object. This mismatch causes validation errors when converting the SQLAlchemy model to the Pydantic model.

Add uselist=False to the wave_quality relationship definition in the Site model to indicate a one-to-one relationship.

server/database/models/site.py
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
class Site(Base):
    """SQLAlchemy Site model corresponding to the database table."""
    __tablename__ = "sites"
    site_id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    site_name: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)
    site_name_short: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)

    site_desc: Mapped[str] = mapped_column(Text, nullable=True)
    site_url: Mapped[str] = mapped_column(String(255), nullable=True)
    site_banner_url: Mapped[str] = mapped_column(String(255), nullable=True)

    daily_prediction = relationship("DailyCrowdnessPrediction", uselist=False, back_populates="sites", primaryjoin="Site.site_id==DailyCrowdnessPrediction.site_id")
    weekly_prediction = relationship("WeeklyCrowdnessPrediction", uselist=False, back_populates="sites", primaryjoin="Site.site_id==WeeklyCrowdnessPrediction.site_id")
    wave_quality = relationship("WaveQualityReading", uselist=False, back_populates="sites", primaryjoin="Site.site_id==WaveQualityReading.site_id")
    def __repr__(self):
        return f"<Site(site_id={self.site_id}, name='{self.name}')>"

This change ensures that wave_quality is a single object, matching the Pydantic model's expectation.