Row now represents individual column types directly withoutTuple¶

SQLAlchemy 2.0 implemented a broad array ofPEP 484 typing throughoutall components, including a new ability for row-returning statements suchasselect() to maintain track of individual column types, whichwere then passed through the execution phase onto theResultobject and then to the individualRow objects. DescribedatSQL Expression / Statement / Result Set Typing, this approach solved several issueswith statement / row typing, but some remained unsolvable. In 2.1, oneof those issues, that the individual column types needed to be packagedinto atyping.Tuple, is now resolved using newPEP 646 integration,which allows for tuple-like types that are not actually typed asTuple.

In SQLAlchemy 2.0, a statement such as:

stmt=select(column("x",Integer),column("y",String))

Would be typed as:

Select[Tuple[int,str]]

In 2.1, it’s now typed as:

Select[int,str]

When executingstmt, theResult andRowobjects will be typed asResult[int,str] andRow[int,str], respectively.The prior workaround usingRow._t to type as a realTupleis no longer needed and projects can migrate off this pattern.

Mypy users will need to make use ofMypy 1.7 or greater for pep-646integration to be available.

Asyncio “greenlet” dependency no longer installs by default¶

SQLAlchemy 1.4 and 2.0 used a complex expression to determine if thegreenlet dependency, needed by theasyncioextension, could be installed from pypi using a pre-built wheel insteadof having to build from source. This because the source build ofgreenletis not always trivial on some platforms.

Disadvantages to this approach included that SQLAlchemy needed to trackexactly which versions ofgreenlet were published as wheels on pypi;the setup expression led to problems with some package management toolssuch aspoetry; it was not possible to install SQLAlchemywithoutgreenlet being installed, even though this is completely feasibleif the asyncio extension is not used.

These problems are all solved by keepinggreenlet entirely within the[asyncio] target. The only downside is that users of the asyncio extensionneed to be aware of this extra installation dependency.

#10197

ORM Relationship allows callable for back_populates¶

To help produce code that is more amenable to IDE-level linting and typechecking, therelationship.back_populates parameter nowaccepts both direct references to a class-bound attribute as well aslambdas which do the same:

classA(Base):__tablename__="a"id:Mapped[int]=mapped_column(primary_key=True)# use a lambda: to link to B.a directly when it existsbs:Mapped[list[B]]=relationship(back_populates=lambda:B.a)classB(Base):__tablename__="b"id:Mapped[int]=mapped_column(primary_key=True)a_id:Mapped[int]=mapped_column(ForeignKey("a.id"))# A.bs already exists, so can link directlya:Mapped[A]=relationship(back_populates=A.bs)

#10050

ORM Mapped Dataclasses no longer populate implicitdefault, collection-baseddefault_factory in__dict__¶

This behavioral change addresses a widely reported issue with SQLAlchemy’sDeclarative Dataclass Mapping feature that was introduced in 2.0.SQLAlchemy ORM has always featured a behavior where a particular attribute onan ORM mapped class will have different behaviors depending on if it has anactively set value, including if that value isNone, versus if theattribute is not set at all. When Declarative Dataclass Mapping was introduced, themapped_column.default parameter introduced a new capabilitywhich is to set up a dataclass-level default to be present in the generated__init__ method. This had the unfortunate side effect of breaking variouspopular workflows, the most prominent of which is creating an ORM object withthe foreign key value in lieu of a many-to-one reference:

classBase(MappedAsDataclass,DeclarativeBase):passclassParent(Base):__tablename__="parent"id:Mapped[int]=mapped_column(primary_key=True,init=False)related_id:Mapped[int|None]=mapped_column(ForeignKey("child.id"),default=None)related:Mapped[Child|None]=relationship(default=None)classChild(Base):__tablename__="child"id:Mapped[int]=mapped_column(primary_key=True,init=False)

In the above mapping, the__init__ method generated forParentwould in Python code look like this:

def__init__(self,related_id=None,related=None):...

This means that creating a newParent withrelated_id only would populatebothrelated_id andrelated in__dict__:

# 2.0 behavior; will INSERT NULL for related_id due to the presence# of related=None>>>p1=Parent(related_id=5)>>>p1.__dict__{'related_id':5,'related':None,'_sa_instance_state':...}

TheNone value for'related' means that SQLAlchemy favors the non-presentrelatedChild over the present value for'related_id', which would bediscarded, andNULL would be inserted for'related_id' instead.

In the new behavior, the__init__ method instead looks like the example below,using a special constantDONT_SET indicating a non-present value for'related'should be ignored. This allows the class to behave more closely to howSQLAlchemy ORM mapped classes traditionally operate:

def__init__(self,related_id=DONT_SET,related=DONT_SET):...

We then get a__dict__ setup that will follow the expected behavior ofomittingrelated from__dict__ and later running an INSERT withrelated_id=5:

# 2.1 behavior; will INSERT 5 for related_id>>>p1=Parent(related_id=5)>>>p1.__dict__{'related_id':5,'_sa_instance_state':...}

classBase(MappedAsDataclass,DeclarativeBase):passclassSomeObject(Base):__tablename__="parent"id:Mapped[int]=mapped_column(primary_key=True,init=False)status:Mapped[str]=mapped_column(default="default_status")

# object is constructed with no value for ``status``>>>s1=SomeObject()# the default value is not placed in ``__dict__``>>>s1.__dict__{'_sa_instance_state':...}# but the default value is delivered at the object level via descriptor>>>s1.status'default_status'# the value still remains unpopulated in ``__dict__``>>>s1.__dict__{'_sa_instance_state':...}

classBase(MappedAsDataclass,DeclarativeBase):passclassSomeObject(Base):__tablename__="parent"id:Mapped[int]=mapped_column(primary_key=True,init=False)status:Mapped[str]=mapped_column(default="default_status",init=False)

>>>so=SomeObject()>>>so.statusdefault_status

classParent(Base):__tablename__="parents"id:Mapped[int]=mapped_column(primary_key=True,init=False)name:Mapped[str]children:Mapped[list["Child"]]=relationship(default_factory=list)

>>>p1=Parent(name="p1")>>>p1.children[]

URL stringify and parse now supports URL escaping for the “database” portion¶

A URL that includes URL-escaped characters in the database portion willnow parse with conversion of those escaped characters:

>>>fromsqlalchemyimportmake_url>>>u=make_url("driver://user:pass@host/database%3Fname")>>>u.database'database?name'

Previously, such characters would not be unescaped:

>>># pre-2.1 behavior>>>fromsqlalchemyimportmake_url>>>u=make_url("driver://user:pass@host/database%3Fname")>>>u.database'database%3Fname'

This change also applies to the stringify side; most special characters inthe database name will be URL escaped, omitting a few such as plus signs andslashes:

>>>fromsqlalchemyimportURL>>>u=URL.create("driver",database="a?b=c")>>>str(u)'driver:///a%3Fb%3Dc'

Where the above URL correctly round-trips to itself:

>>>make_url(str(u))driver:///a%3Fb%3Dc>>>make_url(str(u)).database==u.databaseTrue

Whereas previously, special characters applied programmatically would notbe escaped in the result, leading to a URL that does not represent theoriginal database portion. Below,b=c is part of the query string andnot the database portion:

>>>fromsqlalchemyimportURL>>>u=URL.create("driver",database="a?b=c")>>>str(u)'driver:///a?b=c'

#11234

Potential breaking change to odbc_connect= handling for mssql+pyodbc¶

Fixed a mssql+pyodbc issue where valid plus signs in an already-unquotedodbc_connect= (raw DBAPI) connection string were replaced with spaces.

Previously, the pyodbc connector would always pass the odbc_connect valueto unquote_plus(), even if it was not required. So, if the (unquoted)odbc_connect value containedPWD=pass+word that would get changed toPWD=password, and the login would fail. One workaround was to quotejust the plus sign —PWD=pass%2Bword — which would then get unquotedtoPWD=pass+word.

Implementations using the above workaround withURL.create()to specify a plus sign in thePWD= argument of an odbc_connect stringwill have to remove the workaround and just pass thePWD= value as itwould appear in a valid ODBC connection string (i.e., the same as would berequired if using the connection string directly withpyodbc.connect()).

#11250

Addition ofBitString subclass for handling postgresqlBIT columns¶

Values ofBIT columns in the PostgreSQL dialect arereturned as instances of a newstr subclass,BitString. Previously, the value ofBITcolumns was driver dependent, with most drivers returningstr instancesexceptasyncpg, which usedasyncpg.BitString.

With this change, for thepsycopg,psycopg2, andpg8000 drivers,the newBitString type is mostly compatible withstr, butadds methods for bit manipulation and supports bitwise operators.

AsBitString is a string subclass, hashability as wellas equality tests continue to work against plain strings. This also leavesordering operators intact.

For implementations using theasyncpg driver, the new type is incompatible withthe existingasyncpg.BitString type.

#10556