Coverage for tests/test_main.py: 96%

153 statements  

« prev     ^ index     » next       coverage.py v7.17.2, created at 2026-11-16 00:02 +0000

1from typing import Annotated 2cbdbebfbgbhbibjbkblbmbnbob

2 

3import pytest 2cbdbebfbgbhbibjbkblbmbnbob

4from sqlalchemy import Engine 2cbdbebfbgbhbibjbkblbmbnbob

5from sqlalchemy.exc import IntegrityError 2cbdbebfbgbhbibjbkblbmbnbob

6from sqlalchemy.orm import RelationshipProperty 2cbdbebfbgbhbibjbkblbmbnbob

7from sqlmodel import Field, Relationship, Session, SQLModel, select 2cbdbebfbgbhbibjbkblbmbnbob

8 

9 

10def test_should_allow_duplicate_row_if_unique_constraint_is_not_passed( 2cbdbebfbgbhbibjbkblbmbnbob

11 database_engine: Engine, 

12): 

13 class Hero(SQLModel, table=True): 1abcdefghijklm

14 id: int | None = Field(default=None, primary_key=True) 1abcdefghijklm

15 name: str 1abcdefghijklm

16 secret_name: str 1abcdefghijklm

17 age: int | None = None 1abcdefghijklm

18 

19 hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson") 1abcdefghijklm

20 hero_2 = Hero(name="Deadpond", secret_name="Dive Wilson") 1abcdefghijklm

21 

22 engine = database_engine 1abcdefghijklm

23 

24 SQLModel.metadata.create_all(engine) 1abcdefghijklm

25 

26 with Session(engine) as session: 1abcdefghijklm

27 session.add(hero_1) 1abcdefghijklm

28 session.commit() 1abcdefghijklm

29 session.refresh(hero_1) 1abcdefghijklm

30 

31 with Session(engine) as session: 1abcdefghijklm

32 session.add(hero_2) 1abcdefghijklm

33 session.commit() 1abcdefghijklm

34 session.refresh(hero_2) 1abcdefghijklm

35 

36 with Session(engine) as session: 1abcdefghijklm

37 heroes = session.exec(select(Hero)).all() 1abcdefghijklm

38 assert len(heroes) == 2 1abcdefghijklm

39 assert heroes[0].name == heroes[1].name 1abcdefghijklm

40 

41 

42def test_should_allow_duplicate_row_if_unique_constraint_is_false( 2cbdbebfbgbhbibjbkblbmbnbob

43 database_engine: Engine, 

44): 

45 class Hero(SQLModel, table=True): 1nopqrstuvwxyz

46 id: int | None = Field(default=None, primary_key=True) 1nopqrstuvwxyz

47 name: str 1nopqrstuvwxyz

48 secret_name: str = Field(unique=False) 1nopqrstuvwxyz

49 age: int | None = None 1nopqrstuvwxyz

50 

51 hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson") 1nopqrstuvwxyz

52 hero_2 = Hero(name="Deadpond", secret_name="Dive Wilson") 1nopqrstuvwxyz

53 

54 engine = database_engine 1nopqrstuvwxyz

55 

56 SQLModel.metadata.create_all(engine) 1nopqrstuvwxyz

57 

58 with Session(engine) as session: 1nopqrstuvwxyz

59 session.add(hero_1) 1nopqrstuvwxyz

60 session.commit() 1nopqrstuvwxyz

61 session.refresh(hero_1) 1nopqrstuvwxyz

62 

63 with Session(engine) as session: 1nopqrstuvwxyz

64 session.add(hero_2) 1nopqrstuvwxyz

65 session.commit() 1nopqrstuvwxyz

66 session.refresh(hero_2) 1nopqrstuvwxyz

67 

68 with Session(engine) as session: 1nopqrstuvwxyz

69 heroes = session.exec(select(Hero)).all() 1nopqrstuvwxyz

70 assert len(heroes) == 2 1nopqrstuvwxyz

71 assert heroes[0].name == heroes[1].name 1nopqrstuvwxyz

72 

73 

74def test_should_raise_exception_when_try_to_duplicate_row_if_unique_constraint_is_true( 2cbdbebfbgbhbibjbkblbmbnbob

75 database_engine: Engine, 

76): 

77 class Hero(SQLModel, table=True): 1%'()*+,-./:;=

78 id: int | None = Field(default=None, primary_key=True) 1%'()*+,-./:;=

79 name: str 1%'()*+,-./:;=

80 secret_name: str = Field(unique=True) 1%'()*+,-./:;=

81 age: int | None = None 1%'()*+,-./:;=

82 

83 hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson") 1%'()*+,-./:;=

84 hero_2 = Hero(name="Deadpond", secret_name="Dive Wilson") 1%'()*+,-./:;=

85 

86 engine = database_engine 1%'()*+,-./:;=

87 

88 SQLModel.metadata.create_all(engine) 1%'()*+,-./:;=

89 

90 with Session(engine) as session: 1%'()*+,-./:;=

91 session.add(hero_1) 1%'()*+,-./:;=

92 session.commit() 1%'()*+,-./:;=

93 session.refresh(hero_1) 1%'()*+,-./:;=

94 

95 with pytest.raises(IntegrityError): 1%'()*+,-./:;=

96 with Session(engine) as session: 1%'()*+,-./:;=

97 session.add(hero_2) 1%'()*+,-./:;=

98 session.commit() 1%'()*+,-./:;=

99 

100 

101def test_sa_relationship_property(database_engine: Engine): 2cbdbebfbgbhbibjbkblbmbnbob

102 """Test https://github.com/tiangolo/sqlmodel/issues/315#issuecomment-1272122306""" 

103 

104 class Team(SQLModel, table=True): 1NOPQRSTUVWXYZ

105 id: int | None = Field(default=None, primary_key=True) 1NOPQRSTUVWXYZ

106 name: str = Field(unique=True) 1NOPQRSTUVWXYZ

107 heroes: list["Hero"] = Relationship( # noqa: F821 1NOPQRSTUVWXYZ

108 sa_relationship=RelationshipProperty("Hero", back_populates="team") 

109 ) 

110 

111 class Hero(SQLModel, table=True): 1NOPQRSTUVWXYZ

112 id: int | None = Field(default=None, primary_key=True) 1NOPQRSTUVWXYZ

113 name: str = Field(unique=True) 1NOPQRSTUVWXYZ

114 team_id: int | None = Field(default=None, foreign_key="team.id") 1NOPQRSTUVWXYZ

115 team: Team | None = Relationship( 1NOPQRSTUVWXYZ

116 sa_relationship=RelationshipProperty("Team", back_populates="heroes") 

117 ) 

118 

119 team_preventers = Team(name="Preventers") 1NOPQRSTUVWXYZ

120 hero_rusty_man = Hero(name="Rusty-Man", team=team_preventers) 1NOPQRSTUVWXYZ

121 

122 engine = database_engine 1NOPQRSTUVWXYZ

123 

124 SQLModel.metadata.create_all(engine) 1NOPQRSTUVWXYZ

125 

126 with Session(engine) as session: 1NOPQRSTUVWXYZ

127 session.add(hero_rusty_man) 1NOPQRSTUVWXYZ

128 session.commit() 1NOPQRSTUVWXYZ

129 session.refresh(hero_rusty_man) 1NOPQRSTUVWXYZ

130 # The next statement should not raise an AttributeError 

131 assert hero_rusty_man.team 1NOPQRSTUVWXYZ

132 assert hero_rusty_man.team.name == "Preventers" 1NOPQRSTUVWXYZ

133 

134 

135def test_composite_primary_key(database_engine: Engine): 2cbdbebfbgbhbibjbkblbmbnbob

136 class UserPermission(SQLModel, table=True): 10123456789!#$

137 user_id: int = Field(primary_key=True) 10123456789!#$

138 resource_id: int = Field(primary_key=True) 10123456789!#$

139 permission: str 10123456789!#$

140 

141 engine = database_engine 10123456789!#$

142 SQLModel.metadata.create_all(engine) 10123456789!#$

143 

144 pk_column_names = {column.name for column in UserPermission.__table__.primary_key} 10123456789!#$

145 assert pk_column_names == {"user_id", "resource_id"} 10123456789!#$

146 

147 with Session(engine) as session: 10123456789!#$

148 perm1 = UserPermission(user_id=1, resource_id=1, permission="read") 10123456789!#$

149 perm2 = UserPermission(user_id=1, resource_id=2, permission="write") 10123456789!#$

150 session.add(perm1) 10123456789!#$

151 session.add(perm2) 10123456789!#$

152 session.commit() 10123456789!#$

153 

154 with pytest.raises(IntegrityError): 10123456789!#$

155 with Session(engine) as session: 10123456789!#$

156 perm3 = UserPermission(user_id=1, resource_id=1, permission="admin") 10123456789!#$

157 session.add(perm3) 10123456789!#$

158 session.commit() 10123456789!#$

159 

160 

161def test_composite_primary_key_and_validator(database_engine: Engine): 2cbdbebfbgbhbibjbkblbmbnbob

162 from pydantic import AfterValidator 1ABCDEFGHIJKLM

163 

164 def validate_resource_id(value: int) -> int: 1ABCDEFGHIJKLM

165 if value < 1: 

166 raise ValueError("Resource ID must be positive") 

167 return value 

168 

169 class UserPermission(SQLModel, table=True): 1ABCDEFGHIJKLM

170 user_id: int = Field(primary_key=True) 1ABCDEFGHIJKLM

171 resource_id: Annotated[int, AfterValidator(validate_resource_id)] = Field( 1ABCDEFGHIJKLM

172 primary_key=True 

173 ) 

174 permission: str 1ABCDEFGHIJKLM

175 

176 engine = database_engine 1ABCDEFGHIJKLM

177 SQLModel.metadata.create_all(engine) 1ABCDEFGHIJKLM

178 

179 pk_column_names = {column.name for column in UserPermission.__table__.primary_key} 1ABCDEFGHIJKLM

180 assert pk_column_names == {"user_id", "resource_id"} 1ABCDEFGHIJKLM

181 

182 with Session(engine) as session: 1ABCDEFGHIJKLM

183 perm1 = UserPermission(user_id=1, resource_id=1, permission="read") 1ABCDEFGHIJKLM

184 perm2 = UserPermission(user_id=1, resource_id=2, permission="write") 1ABCDEFGHIJKLM

185 session.add(perm1) 1ABCDEFGHIJKLM

186 session.add(perm2) 1ABCDEFGHIJKLM

187 session.commit() 1ABCDEFGHIJKLM

188 

189 with pytest.raises(IntegrityError): 1ABCDEFGHIJKLM

190 with Session(engine) as session: 1ABCDEFGHIJKLM

191 perm3 = UserPermission(user_id=1, resource_id=1, permission="admin") 1ABCDEFGHIJKLM

192 session.add(perm3) 1ABCDEFGHIJKLM

193 session.commit() 1ABCDEFGHIJKLM

194 

195 

196def test_foreign_key_ondelete_with_annotated(database_engine: Engine): 2cbdbebfbgbhbibjbkblbmbnbob

197 from pydantic import AfterValidator 2? @ [ ] ^ _ ` { | } ~ abbb

198 

199 def ensure_positive(value: int) -> int: 2? @ [ ] ^ _ ` { | } ~ abbb

200 if value < 0: 

201 raise ValueError("Team ID must be positive") 

202 return value 

203 

204 class Team(SQLModel, table=True): 2? @ [ ] ^ _ ` { | } ~ abbb

205 id: int = Field(primary_key=True) 2? @ [ ] ^ _ ` { | } ~ abbb

206 name: str 2? @ [ ] ^ _ ` { | } ~ abbb

207 

208 class Hero(SQLModel, table=True): 2? @ [ ] ^ _ ` { | } ~ abbb

209 id: int = Field(primary_key=True) 2? @ [ ] ^ _ ` { | } ~ abbb

210 team_id: Annotated[int, AfterValidator(ensure_positive)] = Field( 2? @ [ ] ^ _ ` { | } ~ abbb

211 foreign_key="team.id", 

212 ondelete="CASCADE", 

213 ) 

214 name: str 2? @ [ ] ^ _ ` { | } ~ abbb

215 

216 engine = database_engine 2? @ [ ] ^ _ ` { | } ~ abbb

217 SQLModel.metadata.create_all(engine) 2? @ [ ] ^ _ ` { | } ~ abbb

218 

219 team_id_column = Hero.__table__.c.team_id # type: ignore[attr-defined] 2? @ [ ] ^ _ ` { | } ~ abbb

220 foreign_keys = list(team_id_column.foreign_keys) 2? @ [ ] ^ _ ` { | } ~ abbb

221 assert len(foreign_keys) == 1 2? @ [ ] ^ _ ` { | } ~ abbb

222 assert foreign_keys[0].ondelete == "CASCADE" 2? @ [ ] ^ _ ` { | } ~ abbb

223 assert team_id_column.nullable is False 2? @ [ ] ^ _ ` { | } ~ abbb