Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/sqlite/base.py: 32%

1# dialects/sqlite/base.py 

2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors 

3# <see AUTHORS file> 

4# 

5# This module is part of SQLAlchemy and is released under 

6# the MIT License: https://www.opensource.org/licenses/mit-license.php 

7# mypy: ignore-errors 

8 

9 

10r''' 

11.. dialect:: sqlite 

12 :name: SQLite 

13 :normal_support: 3.12+ 

14 :best_effort: 3.7.16+ 

15 

16.. _sqlite_datetime: 

17 

18Date and Time Types 

19------------------- 

20 

21SQLite does not have built-in DATE, TIME, or DATETIME types, and pysqlite does 

22not provide out of the box functionality for translating values between Python 

23`datetime` objects and a SQLite-supported format. SQLAlchemy's own 

24:class:`~sqlalchemy.types.DateTime` and related types provide date formatting 

25and parsing functionality when SQLite is used. The implementation classes are 

26:class:`_sqlite.DATETIME`, :class:`_sqlite.DATE` and :class:`_sqlite.TIME`. 

27These types represent dates and times as ISO formatted strings, which also 

28nicely support ordering. There's no reliance on typical "libc" internals for 

29these functions so historical dates are fully supported. 

30 

31Ensuring Text affinity 

32^^^^^^^^^^^^^^^^^^^^^^ 

33 

34The DDL rendered for these types is the standard ``DATE``, ``TIME`` 

35and ``DATETIME`` indicators. However, custom storage formats can also be 

36applied to these types. When the 

37storage format is detected as containing no alpha characters, the DDL for 

38these types is rendered as ``DATE_CHAR``, ``TIME_CHAR``, and ``DATETIME_CHAR``, 

39so that the column continues to have textual affinity. 

40 

41.. seealso:: 

42 

43 `Type Affinity <https://www.sqlite.org/datatype3.html#affinity>`_ - 

44 in the SQLite documentation 

45 

46.. _sqlite_autoincrement: 

47 

48SQLite Auto Incrementing Behavior 

49---------------------------------- 

50 

51Background on SQLite's autoincrement is at: https://sqlite.org/autoinc.html 

52 

53Key concepts: 

54 

55* SQLite has an implicit "auto increment" feature that takes place for any 

56 non-composite primary-key column that is specifically created using 

57 "INTEGER PRIMARY KEY" for the type + primary key. 

58 

59* SQLite also has an explicit "AUTOINCREMENT" keyword, that is **not** 

60 equivalent to the implicit autoincrement feature; this keyword is not 

61 recommended for general use. SQLAlchemy does not render this keyword 

62 unless a special SQLite-specific directive is used (see below). However, 

63 it still requires that the column's type is named "INTEGER". 

64 

65Using the AUTOINCREMENT Keyword 

66^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

67 

68To specifically render the AUTOINCREMENT keyword on the primary key column 

69when rendering DDL, add the flag ``sqlite_autoincrement=True`` to the Table 

70construct:: 

71 

72 Table( 

73 "sometable", 

74 metadata, 

75 Column("id", Integer, primary_key=True), 

76 sqlite_autoincrement=True, 

77 ) 

78 

79Allowing autoincrement behavior SQLAlchemy types other than Integer/INTEGER 

80^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

81 

82SQLite's typing model is based on naming conventions. Among other things, this 

83means that any type name which contains the substring ``"INT"`` will be 

84determined to be of "integer affinity". A type named ``"BIGINT"``, 

85``"SPECIAL_INT"`` or even ``"XYZINTQPR"``, will be considered by SQLite to be 

86of "integer" affinity. However, **the SQLite autoincrement feature, whether 

87implicitly or explicitly enabled, requires that the name of the column's type 

88is exactly the string "INTEGER"**. Therefore, if an application uses a type 

89like :class:`.BigInteger` for a primary key, on SQLite this type will need to 

90be rendered as the name ``"INTEGER"`` when emitting the initial ``CREATE 

91TABLE`` statement in order for the autoincrement behavior to be available. 

92 

93One approach to achieve this is to use :class:`.Integer` on SQLite 

94only using :meth:`.TypeEngine.with_variant`:: 

95 

96 table = Table( 

97 "my_table", 

98 metadata, 

99 Column( 

100 "id", 

101 BigInteger().with_variant(Integer, "sqlite"), 

102 primary_key=True, 

103 ), 

104 ) 

105 

106Another is to use a subclass of :class:`.BigInteger` that overrides its DDL 

107name to be ``INTEGER`` when compiled against SQLite:: 

108 

109 from sqlalchemy import BigInteger 

110 from sqlalchemy.ext.compiler import compiles 

111 

112 

113 class SLBigInteger(BigInteger): 

114 pass 

115 

116 

117 @compiles(SLBigInteger, "sqlite") 

118 def bi_c(element, compiler, **kw): 

119 return "INTEGER" 

120 

121 

122 @compiles(SLBigInteger) 

123 def bi_c(element, compiler, **kw): 

124 return compiler.visit_BIGINT(element, **kw) 

125 

126 

127 table = Table( 

128 "my_table", metadata, Column("id", SLBigInteger(), primary_key=True) 

129 ) 

130 

131.. seealso:: 

132 

133 :meth:`.TypeEngine.with_variant` 

134 

135 :ref:`sqlalchemy.ext.compiler_toplevel` 

136 

137 `Datatypes In SQLite Version 3 <https://sqlite.org/datatype3.html>`_ 

138 

139.. _sqlite_transactions: 

140 

141Transactions with SQLite and the sqlite3 driver 

142----------------------------------------------- 

143 

144As a file-based database, SQLite's approach to transactions differs from 

145traditional databases in many ways. Additionally, the ``sqlite3`` driver 

146standard with Python (as well as the async version ``aiosqlite`` which builds 

147on top of it) has several quirks, workarounds, and API features in the 

148area of transaction control, all of which generally need to be addressed when 

149constructing a SQLAlchemy application that uses SQLite. 

150 

151Legacy Transaction Mode with the sqlite3 driver 

152^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

153 

154The most important aspect of transaction handling with the sqlite3 driver is 

155that it defaults (which will continue through Python 3.15 before being 

156removed in Python 3.16) to legacy transactional behavior which does 

157not strictly follow :pep:`249`. The way in which the driver diverges from the 

158PEP is that it does not "begin" a transaction automatically as dictated by 

159:pep:`249` except in the case of DML statements, e.g. INSERT, UPDATE, and 

160DELETE. Normally, :pep:`249` dictates that a BEGIN must be emitted upon 

161the first SQL statement of any kind, so that all subsequent operations will 

162be established within a transaction until ``connection.commit()`` has been 

163called. The ``sqlite3`` driver, in an effort to be easier to use in 

164highly concurrent environments, skips this step for DQL (e.g. SELECT) statements, 

165and also skips it for DDL (e.g. CREATE TABLE etc.) statements for more legacy 

166reasons. Statements such as SAVEPOINT are also skipped. 

167 

168In modern versions of the ``sqlite3`` driver as of Python 3.12, this legacy 

169mode of operation is referred to as 

170`"legacy transaction control" <https://docs.python.org/3/library/sqlite3.html#sqlite3-transaction-control-isolation-level>`_, and is in 

171effect by default due to the ``Connection.autocommit`` parameter being set to 

172the constant ``sqlite3.LEGACY_TRANSACTION_CONTROL``. Prior to Python 3.12, 

173the ``Connection.autocommit`` attribute did not exist. 

174 

175The implications of legacy transaction mode include: 

176 

177* **Incorrect support for transactional DDL** - statements like CREATE TABLE, ALTER TABLE, 

178 CREATE INDEX etc. will not automatically BEGIN a transaction if one were not 

179 started already, leading to the changes by each statement being 

180 "autocommitted" immediately unless BEGIN were otherwise emitted first. Very 

181 old (pre Python 3.6) versions of SQLite would also force a COMMIT for these 

182 operations even if a transaction were present, however this is no longer the 

183 case. 

184* **SERIALIZABLE behavior not fully functional** - SQLite's transaction isolation 

185 behavior is normally consistent with SERIALIZABLE isolation, as it is a file- 

186 based system that locks the database file entirely for write operations, 

187 preventing COMMIT until all reader transactions (and associated file locks) 

188 have completed. However, sqlite3's legacy transaction mode fails to emit BEGIN for SELECT 

189 statements, which causes these SELECT statements to no longer be "repeatable", 

190 failing one of the consistency guarantees of SERIALIZABLE. 

191* **Incorrect behavior for SAVEPOINT** - as the SAVEPOINT statement does not 

192 imply a BEGIN, a new SAVEPOINT emitted before a BEGIN will function on its 

193 own but fails to participate in the enclosing transaction, meaning a ROLLBACK 

194 of the transaction will not rollback elements that were part of a released 

195 savepoint. 

196 

197Legacy transaction mode first existed in order to facilitate working around 

198SQLite's file locks. Because SQLite relies upon whole-file locks, it is easy to 

199get "database is locked" errors, particularly when newer features like "write 

200ahead logging" are disabled. This is a key reason why ``sqlite3``'s legacy 

201transaction mode is still the default mode of operation; disabling it will 

202produce behavior that is more susceptible to locked database errors. However 

203note that **legacy transaction mode will no longer be the default** in a future 

204Python version (3.16 as of this writing). 

205 

206.. _sqlite_enabling_transactions: 

207 

208Enabling Non-Legacy SQLite Transactional Modes with the sqlite3 or aiosqlite driver 

209^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

210 

211Current SQLAlchemy support allows either for setting the 

212``.Connection.autocommit`` attribute, most directly by using a 

213:func:`._sa.create_engine` parameter, or if on an older version of Python where 

214the attribute is not available, using event hooks to control the behavior of 

215BEGIN. 

216 

217* **Enabling modern sqlite3 transaction control via the autocommit connect parameter** (Python 3.12 and above) 

218 

219 To use SQLite in the mode described at `Transaction control via the autocommit attribute <https://docs.python.org/3/library/sqlite3.html#transaction-control-via-the-autocommit-attribute>`_, 

220 the most straightforward approach is to set the attribute to its recommended value 

221 of ``False`` at the connect level using :paramref:`_sa.create_engine.connect_args``:: 

222 

223 from sqlalchemy import create_engine 

224 

225 engine = create_engine( 

226 "sqlite:///myfile.db", connect_args={"autocommit": False} 

227 ) 

228 

229 This parameter is also passed through when using the aiosqlite driver:: 

230 

231 from sqlalchemy.ext.asyncio import create_async_engine 

232 

233 engine = create_async_engine( 

234 "sqlite+aiosqlite:///myfile.db", connect_args={"autocommit": False} 

235 ) 

236 

237 The parameter can also be set at the attribute level using the :meth:`.PoolEvents.connect` 

238 event hook, however this will only work for sqlite3, as aiosqlite does not yet expose this 

239 attribute on its ``Connection`` object:: 

240 

241 from sqlalchemy import create_engine, event 

242 

243 engine = create_engine("sqlite:///myfile.db") 

244 

245 

246 @event.listens_for(engine, "connect") 

247 def do_connect(dbapi_connection, connection_record): 

248 # enable autocommit=False mode 

249 dbapi_connection.autocommit = False 

250 

251* **Using SQLAlchemy to emit BEGIN in lieu of SQLite's transaction control** (all Python versions, sqlite3 and aiosqlite) 

252 

253 For older versions of ``sqlite3`` or for cross-compatibility with older and 

254 newer versions, SQLAlchemy can also take over the job of transaction control. 

255 This is achieved by using the :meth:`.ConnectionEvents.begin` hook 

256 to emit the "BEGIN" command directly, while also disabling SQLite's control 

257 of this command using the :meth:`.PoolEvents.connect` event hook to set the 

258 ``Connection.isolation_level`` attribute to ``None``:: 

259 

260 

261 from sqlalchemy import create_engine, event 

262 

263 engine = create_engine("sqlite:///myfile.db") 

264 

265 

266 @event.listens_for(engine, "connect") 

267 def do_connect(dbapi_connection, connection_record): 

268 # disable sqlite3's emitting of the BEGIN statement entirely. 

269 dbapi_connection.isolation_level = None 

270 

271 

272 @event.listens_for(engine, "begin") 

273 def do_begin(conn): 

274 # emit our own BEGIN. sqlite3 still emits COMMIT/ROLLBACK correctly 

275 conn.exec_driver_sql("BEGIN") 

276 

277 When using the asyncio variant ``aiosqlite``, refer to ``engine.sync_engine`` 

278 as in the example below:: 

279 

280 from sqlalchemy import create_engine, event 

281 from sqlalchemy.ext.asyncio import create_async_engine 

282 

283 engine = create_async_engine("sqlite+aiosqlite:///myfile.db") 

284 

285 

286 @event.listens_for(engine.sync_engine, "connect") 

287 def do_connect(dbapi_connection, connection_record): 

288 # disable aiosqlite's emitting of the BEGIN statement entirely. 

289 dbapi_connection.isolation_level = None 

290 

291 

292 @event.listens_for(engine.sync_engine, "begin") 

293 def do_begin(conn): 

294 # emit our own BEGIN. aiosqlite still emits COMMIT/ROLLBACK correctly 

295 conn.exec_driver_sql("BEGIN") 

296 

297.. _sqlite_isolation_level: 

298 

299Using SQLAlchemy's Driver Level AUTOCOMMIT Feature with SQLite 

300^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

301 

302SQLAlchemy has a comprehensive database isolation feature with optional 

303autocommit support that is introduced in the section :ref:`dbapi_autocommit`. 

304 

305For the ``sqlite3`` and ``aiosqlite`` drivers, SQLAlchemy only includes 

306built-in support for "AUTOCOMMIT". Note that this mode is currently incompatible 

307with the non-legacy isolation mode hooks documented in the previous 

308section at :ref:`sqlite_enabling_transactions`. 

309 

310To use the ``sqlite3`` driver with SQLAlchemy driver-level autocommit, 

311create an engine setting the :paramref:`_sa.create_engine.isolation_level` 

312parameter to "AUTOCOMMIT":: 

313 

314 eng = create_engine("sqlite:///myfile.db", isolation_level="AUTOCOMMIT") 

315 

316When using the above mode, any event hooks that set the sqlite3 ``Connection.autocommit`` 

317parameter away from its default of ``sqlite3.LEGACY_TRANSACTION_CONTROL`` 

318as well as hooks that emit ``BEGIN`` should be disabled. 

319 

320Additional Reading for SQLite / sqlite3 transaction control 

321^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

322 

323Links with important information on SQLite, the sqlite3 driver, 

324as well as long historical conversations on how things got to their current state: 

325 

326* `Isolation in SQLite <https://www.sqlite.org/isolation.html>`_ - on the SQLite website 

327* `Transaction control <https://docs.python.org/3/library/sqlite3.html#transaction-control>`_ - describes the sqlite3 autocommit attribute as well 

328 as the legacy isolation_level attribute. 

329* `sqlite3 SELECT does not BEGIN a transaction, but should according to spec <https://github.com/python/cpython/issues/54133>`_ - imported Python standard library issue on github 

330* `sqlite3 module breaks transactions and potentially corrupts data <https://github.com/python/cpython/issues/54949>`_ - imported Python standard library issue on github 

331 

332 

333INSERT/UPDATE/DELETE...RETURNING 

334--------------------------------- 

335 

336The SQLite dialect supports SQLite 3.35's ``INSERT|UPDATE|DELETE..RETURNING`` 

337syntax. ``INSERT..RETURNING`` may be used 

338automatically in some cases in order to fetch newly generated identifiers in 

339place of the traditional approach of using ``cursor.lastrowid``, however 

340``cursor.lastrowid`` is currently still preferred for simple single-statement 

341cases for its better performance. 

342 

343To specify an explicit ``RETURNING`` clause, use the 

344:meth:`._UpdateBase.returning` method on a per-statement basis:: 

345 

346 # INSERT..RETURNING 

347 result = connection.execute( 

348 table.insert().values(name="foo").returning(table.c.col1, table.c.col2) 

349 ) 

350 print(result.all()) 

351 

352 # UPDATE..RETURNING 

353 result = connection.execute( 

354 table.update() 

355 .where(table.c.name == "foo") 

356 .values(name="bar") 

357 .returning(table.c.col1, table.c.col2) 

358 ) 

359 print(result.all()) 

360 

361 # DELETE..RETURNING 

362 result = connection.execute( 

363 table.delete() 

364 .where(table.c.name == "foo") 

365 .returning(table.c.col1, table.c.col2) 

366 ) 

367 print(result.all()) 

368 

369.. versionadded:: 2.0 Added support for SQLite RETURNING 

370 

371 

372.. _sqlite_foreign_keys: 

373 

374Foreign Key Support 

375------------------- 

376 

377SQLite supports FOREIGN KEY syntax when emitting CREATE statements for tables, 

378however by default these constraints have no effect on the operation of the 

379table. 

380 

381Constraint checking on SQLite has three prerequisites: 

382 

383* At least version 3.6.19 of SQLite must be in use 

384* The SQLite library must be compiled *without* the SQLITE_OMIT_FOREIGN_KEY 

385 or SQLITE_OMIT_TRIGGER symbols enabled. 

386* The ``PRAGMA foreign_keys = ON`` statement must be emitted on all 

387 connections before use -- including the initial call to 

388 :meth:`sqlalchemy.schema.MetaData.create_all`. 

389 

390SQLAlchemy allows for the ``PRAGMA`` statement to be emitted automatically for 

391new connections through the usage of events:: 

392 

393 from sqlalchemy.engine import Engine 

394 from sqlalchemy import event 

395 

396 

397 @event.listens_for(Engine, "connect") 

398 def set_sqlite_pragma(dbapi_connection, connection_record): 

399 # the sqlite3 driver will not set PRAGMA foreign_keys 

400 # if autocommit=False; set to True temporarily 

401 ac = dbapi_connection.autocommit 

402 dbapi_connection.autocommit = True 

403 

404 cursor = dbapi_connection.cursor() 

405 cursor.execute("PRAGMA foreign_keys=ON") 

406 cursor.close() 

407 

408 # restore previous autocommit setting 

409 dbapi_connection.autocommit = ac 

410 

411.. warning:: 

412 

413 When SQLite foreign keys are enabled, it is **not possible** 

414 to emit CREATE or DROP statements for tables that contain 

415 mutually-dependent foreign key constraints; 

416 to emit the DDL for these tables requires that ALTER TABLE be used to 

417 create or drop these constraints separately, for which SQLite has 

418 no support. 

419 

420.. seealso:: 

421 

422 `SQLite Foreign Key Support <https://www.sqlite.org/foreignkeys.html>`_ 

423 - on the SQLite web site. 

424 

425 :ref:`event_toplevel` - SQLAlchemy event API. 

426 

427 :ref:`use_alter` - more information on SQLAlchemy's facilities for handling 

428 mutually-dependent foreign key constraints. 

429 

430.. _sqlite_on_conflict_ddl: 

431 

432ON CONFLICT support for constraints 

433----------------------------------- 

434 

435.. seealso:: This section describes the :term:`DDL` version of "ON CONFLICT" for 

436 SQLite, which occurs within a CREATE TABLE statement. For "ON CONFLICT" as 

437 applied to an INSERT statement, see :ref:`sqlite_on_conflict_insert`. 

438 

439SQLite supports a non-standard DDL clause known as ON CONFLICT which can be applied 

440to primary key, unique, check, and not null constraints. In DDL, it is 

441rendered either within the "CONSTRAINT" clause or within the column definition 

442itself depending on the location of the target constraint. To render this 

443clause within DDL, the extension parameter ``sqlite_on_conflict`` can be 

444specified with a string conflict resolution algorithm within the 

445:class:`.PrimaryKeyConstraint`, :class:`.UniqueConstraint`, 

446:class:`.CheckConstraint` objects. Within the :class:`_schema.Column` object, 

447there 

448are individual parameters ``sqlite_on_conflict_not_null``, 

449``sqlite_on_conflict_primary_key``, ``sqlite_on_conflict_unique`` which each 

450correspond to the three types of relevant constraint types that can be 

451indicated from a :class:`_schema.Column` object. 

452 

453.. seealso:: 

454 

455 `ON CONFLICT <https://www.sqlite.org/lang_conflict.html>`_ - in the SQLite 

456 documentation 

457 

458The ``sqlite_on_conflict`` parameters accept a string argument which is just 

459the resolution name to be chosen, which on SQLite can be one of ROLLBACK, 

460ABORT, FAIL, IGNORE, and REPLACE. For example, to add a UNIQUE constraint 

461that specifies the IGNORE algorithm:: 

462 

463 some_table = Table( 

464 "some_table", 

465 metadata, 

466 Column("id", Integer, primary_key=True), 

467 Column("data", Integer), 

468 UniqueConstraint("id", "data", sqlite_on_conflict="IGNORE"), 

469 ) 

470 

471The above renders CREATE TABLE DDL as: 

472 

473.. sourcecode:: sql 

474 

475 CREATE TABLE some_table ( 

476 id INTEGER NOT NULL, 

477 data INTEGER, 

478 PRIMARY KEY (id), 

479 UNIQUE (id, data) ON CONFLICT IGNORE 

480 ) 

481 

482 

483When using the :paramref:`_schema.Column.unique` 

484flag to add a UNIQUE constraint 

485to a single column, the ``sqlite_on_conflict_unique`` parameter can 

486be added to the :class:`_schema.Column` as well, which will be added to the 

487UNIQUE constraint in the DDL:: 

488 

489 some_table = Table( 

490 "some_table", 

491 metadata, 

492 Column("id", Integer, primary_key=True), 

493 Column( 

494 "data", Integer, unique=True, sqlite_on_conflict_unique="IGNORE" 

495 ), 

496 ) 

497 

498rendering: 

499 

500.. sourcecode:: sql 

501 

502 CREATE TABLE some_table ( 

503 id INTEGER NOT NULL, 

504 data INTEGER, 

505 PRIMARY KEY (id), 

506 UNIQUE (data) ON CONFLICT IGNORE 

507 ) 

508 

509To apply the FAIL algorithm for a NOT NULL constraint, 

510``sqlite_on_conflict_not_null`` is used:: 

511 

512 some_table = Table( 

513 "some_table", 

514 metadata, 

515 Column("id", Integer, primary_key=True), 

516 Column( 

517 "data", Integer, nullable=False, sqlite_on_conflict_not_null="FAIL" 

518 ), 

519 ) 

520 

521this renders the column inline ON CONFLICT phrase: 

522 

523.. sourcecode:: sql 

524 

525 CREATE TABLE some_table ( 

526 id INTEGER NOT NULL, 

527 data INTEGER NOT NULL ON CONFLICT FAIL, 

528 PRIMARY KEY (id) 

529 ) 

530 

531 

532Similarly, for an inline primary key, use ``sqlite_on_conflict_primary_key``:: 

533 

534 some_table = Table( 

535 "some_table", 

536 metadata, 

537 Column( 

538 "id", 

539 Integer, 

540 primary_key=True, 

541 sqlite_on_conflict_primary_key="FAIL", 

542 ), 

543 ) 

544 

545SQLAlchemy renders the PRIMARY KEY constraint separately, so the conflict 

546resolution algorithm is applied to the constraint itself: 

547 

548.. sourcecode:: sql 

549 

550 CREATE TABLE some_table ( 

551 id INTEGER NOT NULL, 

552 PRIMARY KEY (id) ON CONFLICT FAIL 

553 ) 

554 

555.. _sqlite_on_conflict_insert: 

556 

557INSERT...ON CONFLICT (Upsert) 

558----------------------------- 

559 

560.. seealso:: This section describes the :term:`DML` version of "ON CONFLICT" for 

561 SQLite, which occurs within an INSERT statement. For "ON CONFLICT" as 

562 applied to a CREATE TABLE statement, see :ref:`sqlite_on_conflict_ddl`. 

563 

564From version 3.24.0 onwards, SQLite supports "upserts" (update or insert) 

565of rows into a table via the ``ON CONFLICT`` clause of the ``INSERT`` 

566statement. A candidate row will only be inserted if that row does not violate 

567any unique or primary key constraints. In the case of a unique constraint violation, a 

568secondary action can occur which can be either "DO UPDATE", indicating that 

569the data in the target row should be updated, or "DO NOTHING", which indicates 

570to silently skip this row. 

571 

572Conflicts are determined using columns that are part of existing unique 

573constraints and indexes. These constraints are identified by stating the 

574columns and conditions that comprise the indexes. 

575 

576SQLAlchemy provides ``ON CONFLICT`` support via the SQLite-specific 

577:func:`_sqlite.insert()` function, which provides 

578the generative methods :meth:`_sqlite.Insert.on_conflict_do_update` 

579and :meth:`_sqlite.Insert.on_conflict_do_nothing`: 

580 

581.. sourcecode:: pycon+sql 

582 

583 >>> from sqlalchemy.dialects.sqlite import insert 

584 

585 >>> insert_stmt = insert(my_table).values( 

586 ... id="some_existing_id", data="inserted value" 

587 ... ) 

588 

589 >>> do_update_stmt = insert_stmt.on_conflict_do_update( 

590 ... index_elements=["id"], set_=dict(data="updated value") 

591 ... ) 

592 

593 >>> print(do_update_stmt) 

594 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) 

595 ON CONFLICT (id) DO UPDATE SET data = ?{stop} 

596 

597 >>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(index_elements=["id"]) 

598 

599 >>> print(do_nothing_stmt) 

600 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) 

601 ON CONFLICT (id) DO NOTHING 

602 

603.. versionadded:: 1.4 

604 

605.. seealso:: 

606 

607 `Upsert 

608 <https://sqlite.org/lang_UPSERT.html>`_ 

609 - in the SQLite documentation. 

610 

611 

612Specifying the Target 

613^^^^^^^^^^^^^^^^^^^^^ 

614 

615Both methods supply the "target" of the conflict using column inference: 

616 

617* The :paramref:`_sqlite.Insert.on_conflict_do_update.index_elements` argument 

618 specifies a sequence containing string column names, :class:`_schema.Column` 

619 objects, and/or SQL expression elements, which would identify a unique index 

620 or unique constraint. 

621 

622* When using :paramref:`_sqlite.Insert.on_conflict_do_update.index_elements` 

623 to infer an index, a partial index can be inferred by also specifying the 

624 :paramref:`_sqlite.Insert.on_conflict_do_update.index_where` parameter: 

625 

626 .. sourcecode:: pycon+sql 

627 

628 >>> stmt = insert(my_table).values(user_email="a@b.com", data="inserted data") 

629 

630 >>> do_update_stmt = stmt.on_conflict_do_update( 

631 ... index_elements=[my_table.c.user_email], 

632 ... index_where=my_table.c.user_email.like("%@gmail.com"), 

633 ... set_=dict(data=stmt.excluded.data), 

634 ... ) 

635 

636 >>> print(do_update_stmt) 

637 {printsql}INSERT INTO my_table (data, user_email) VALUES (?, ?) 

638 ON CONFLICT (user_email) 

639 WHERE user_email LIKE '%@gmail.com' 

640 DO UPDATE SET data = excluded.data 

641 

642The SET Clause 

643^^^^^^^^^^^^^^^ 

644 

645``ON CONFLICT...DO UPDATE`` is used to perform an update of the already 

646existing row, using any combination of new values as well as values 

647from the proposed insertion. These values are specified using the 

648:paramref:`_sqlite.Insert.on_conflict_do_update.set_` parameter. This 

649parameter accepts a dictionary which consists of direct values 

650for UPDATE: 

651 

652.. sourcecode:: pycon+sql 

653 

654 >>> stmt = insert(my_table).values(id="some_id", data="inserted value") 

655 

656 >>> do_update_stmt = stmt.on_conflict_do_update( 

657 ... index_elements=["id"], set_=dict(data="updated value") 

658 ... ) 

659 

660 >>> print(do_update_stmt) 

661 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) 

662 ON CONFLICT (id) DO UPDATE SET data = ? 

663 

664.. warning:: 

665 

666 The :meth:`_sqlite.Insert.on_conflict_do_update` method does **not** take 

667 into account Python-side default UPDATE values or generation functions, 

668 e.g. those specified using :paramref:`_schema.Column.onupdate`. These 

669 values will not be exercised for an ON CONFLICT style of UPDATE, unless 

670 they are manually specified in the 

671 :paramref:`_sqlite.Insert.on_conflict_do_update.set_` dictionary. 

672 

673Updating using the Excluded INSERT Values 

674^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

675 

676In order to refer to the proposed insertion row, the special alias 

677:attr:`~.sqlite.Insert.excluded` is available as an attribute on 

678the :class:`_sqlite.Insert` object; this object creates an "excluded." prefix 

679on a column, that informs the DO UPDATE to update the row with the value that 

680would have been inserted had the constraint not failed: 

681 

682.. sourcecode:: pycon+sql 

683 

684 >>> stmt = insert(my_table).values( 

685 ... id="some_id", data="inserted value", author="jlh" 

686 ... ) 

687 

688 >>> do_update_stmt = stmt.on_conflict_do_update( 

689 ... index_elements=["id"], 

690 ... set_=dict(data="updated value", author=stmt.excluded.author), 

691 ... ) 

692 

693 >>> print(do_update_stmt) 

694 {printsql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?) 

695 ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author 

696 

697Additional WHERE Criteria 

698^^^^^^^^^^^^^^^^^^^^^^^^^ 

699 

700The :meth:`_sqlite.Insert.on_conflict_do_update` method also accepts 

701a WHERE clause using the :paramref:`_sqlite.Insert.on_conflict_do_update.where` 

702parameter, which will limit those rows which receive an UPDATE: 

703 

704.. sourcecode:: pycon+sql 

705 

706 >>> stmt = insert(my_table).values( 

707 ... id="some_id", data="inserted value", author="jlh" 

708 ... ) 

709 

710 >>> on_update_stmt = stmt.on_conflict_do_update( 

711 ... index_elements=["id"], 

712 ... set_=dict(data="updated value", author=stmt.excluded.author), 

713 ... where=(my_table.c.status == 2), 

714 ... ) 

715 >>> print(on_update_stmt) 

716 {printsql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?) 

717 ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author 

718 WHERE my_table.status = ? 

719 

720 

721Skipping Rows with DO NOTHING 

722^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

723 

724``ON CONFLICT`` may be used to skip inserting a row entirely 

725if any conflict with a unique constraint occurs; below this is illustrated 

726using the :meth:`_sqlite.Insert.on_conflict_do_nothing` method: 

727 

728.. sourcecode:: pycon+sql 

729 

730 >>> stmt = insert(my_table).values(id="some_id", data="inserted value") 

731 >>> stmt = stmt.on_conflict_do_nothing(index_elements=["id"]) 

732 >>> print(stmt) 

733 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT (id) DO NOTHING 

734 

735 

736If ``DO NOTHING`` is used without specifying any columns or constraint, 

737it has the effect of skipping the INSERT for any unique violation which 

738occurs: 

739 

740.. sourcecode:: pycon+sql 

741 

742 >>> stmt = insert(my_table).values(id="some_id", data="inserted value") 

743 >>> stmt = stmt.on_conflict_do_nothing() 

744 >>> print(stmt) 

745 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT DO NOTHING 

746 

747.. _sqlite_type_reflection: 

748 

749Type Reflection 

750--------------- 

751 

752SQLite types are unlike those of most other database backends, in that 

753the string name of the type usually does not correspond to a "type" in a 

754one-to-one fashion. Instead, SQLite links per-column typing behavior 

755to one of five so-called "type affinities" based on a string matching 

756pattern for the type. 

757 

758SQLAlchemy's reflection process, when inspecting types, uses a simple 

759lookup table to link the keywords returned to provided SQLAlchemy types. 

760This lookup table is present within the SQLite dialect as it is for all 

761other dialects. However, the SQLite dialect has a different "fallback" 

762routine for when a particular type name is not located in the lookup map; 

763it instead implements the SQLite "type affinity" scheme located at 

764https://www.sqlite.org/datatype3.html section 2.1. 

765 

766The provided typemap will make direct associations from an exact string 

767name match for the following types: 

768 

769:class:`_types.BIGINT`, :class:`_types.BLOB`, 

770:class:`_types.BOOLEAN`, :class:`_types.BOOLEAN`, 

771:class:`_types.CHAR`, :class:`_types.DATE`, 

772:class:`_types.DATETIME`, :class:`_types.FLOAT`, 

773:class:`_types.DECIMAL`, :class:`_types.FLOAT`, 

774:class:`_types.INTEGER`, :class:`_types.INTEGER`, 

775:class:`_types.NUMERIC`, :class:`_types.REAL`, 

776:class:`_types.SMALLINT`, :class:`_types.TEXT`, 

777:class:`_types.TIME`, :class:`_types.TIMESTAMP`, 

778:class:`_types.VARCHAR`, :class:`_types.NVARCHAR`, 

779:class:`_types.NCHAR` 

780 

781When a type name does not match one of the above types, the "type affinity" 

782lookup is used instead: 

783 

784* :class:`_types.INTEGER` is returned if the type name includes the 

785 string ``INT`` 

786* :class:`_types.TEXT` is returned if the type name includes the 

787 string ``CHAR``, ``CLOB`` or ``TEXT`` 

788* :class:`_types.NullType` is returned if the type name includes the 

789 string ``BLOB`` 

790* :class:`_types.REAL` is returned if the type name includes the string 

791 ``REAL``, ``FLOA`` or ``DOUB``. 

792* Otherwise, the :class:`_types.NUMERIC` type is used. 

793 

794.. _sqlite_partial_index: 

795 

796Partial Indexes 

797--------------- 

798 

799A partial index, e.g. one which uses a WHERE clause, can be specified 

800with the DDL system using the argument ``sqlite_where``:: 

801 

802 tbl = Table("testtbl", m, Column("data", Integer)) 

803 idx = Index( 

804 "test_idx1", 

805 tbl.c.data, 

806 sqlite_where=and_(tbl.c.data > 5, tbl.c.data < 10), 

807 ) 

808 

809The index will be rendered at create time as: 

810 

811.. sourcecode:: sql 

812 

813 CREATE INDEX test_idx1 ON testtbl (data) 

814 WHERE data > 5 AND data < 10 

815 

816.. _sqlite_dotted_column_names: 

817 

818Dotted Column Names 

819------------------- 

820 

821Using table or column names that explicitly have periods in them is 

822**not recommended**. While this is generally a bad idea for relational 

823databases in general, as the dot is a syntactically significant character, 

824the SQLite driver up until version **3.10.0** of SQLite has a bug which 

825requires that SQLAlchemy filter out these dots in result sets. 

826 

827The bug, entirely outside of SQLAlchemy, can be illustrated thusly:: 

828 

829 import sqlite3 

830 

831 assert sqlite3.sqlite_version_info < ( 

832 3, 

833 10, 

834 0, 

835 ), "bug is fixed in this version" 

836 

837 conn = sqlite3.connect(":memory:") 

838 cursor = conn.cursor() 

839 

840 cursor.execute("create table x (a integer, b integer)") 

841 cursor.execute("insert into x (a, b) values (1, 1)") 

842 cursor.execute("insert into x (a, b) values (2, 2)") 

843 

844 cursor.execute("select x.a, x.b from x") 

845 assert [c[0] for c in cursor.description] == ["a", "b"] 

846 

847 cursor.execute(""" 

848 select x.a, x.b from x where a=1 

849 union 

850 select x.a, x.b from x where a=2 

851 """) 

852 assert [c[0] for c in cursor.description] == ["a", "b"], [ 

853 c[0] for c in cursor.description 

854 ] 

855 

856The second assertion fails: 

857 

858.. sourcecode:: text 

859 

860 Traceback (most recent call last): 

861 File "test.py", line 19, in <module> 

862 [c[0] for c in cursor.description] 

863 AssertionError: ['x.a', 'x.b'] 

864 

865Where above, the driver incorrectly reports the names of the columns 

866including the name of the table, which is entirely inconsistent vs. 

867when the UNION is not present. 

868 

869SQLAlchemy relies upon column names being predictable in how they match 

870to the original statement, so the SQLAlchemy dialect has no choice but 

871to filter these out:: 

872 

873 

874 from sqlalchemy import create_engine 

875 

876 eng = create_engine("sqlite://") 

877 conn = eng.connect() 

878 

879 conn.exec_driver_sql("create table x (a integer, b integer)") 

880 conn.exec_driver_sql("insert into x (a, b) values (1, 1)") 

881 conn.exec_driver_sql("insert into x (a, b) values (2, 2)") 

882 

883 result = conn.exec_driver_sql("select x.a, x.b from x") 

884 assert result.keys() == ["a", "b"] 

885 

886 result = conn.exec_driver_sql(""" 

887 select x.a, x.b from x where a=1 

888 union 

889 select x.a, x.b from x where a=2 

890 """) 

891 assert result.keys() == ["a", "b"] 

892 

893Note that above, even though SQLAlchemy filters out the dots, *both 

894names are still addressable*:: 

895 

896 >>> row = result.first() 

897 >>> row["a"] 

898 1 

899 >>> row["x.a"] 

900 1 

901 >>> row["b"] 

902 1 

903 >>> row["x.b"] 

904 1 

905 

906Therefore, the workaround applied by SQLAlchemy only impacts 

907:meth:`_engine.CursorResult.keys` and :meth:`.Row.keys()` in the public API. In 

908the very specific case where an application is forced to use column names that 

909contain dots, and the functionality of :meth:`_engine.CursorResult.keys` and 

910:meth:`.Row.keys()` is required to return these dotted names unmodified, 

911the ``sqlite_raw_colnames`` execution option may be provided, either on a 

912per-:class:`_engine.Connection` basis:: 

913 

914 result = conn.execution_options(sqlite_raw_colnames=True).exec_driver_sql( 

915 """ 

916 select x.a, x.b from x where a=1 

917 union 

918 select x.a, x.b from x where a=2 

919 """ 

920 ) 

921 assert result.keys() == ["x.a", "x.b"] 

922 

923or on a per-:class:`_engine.Engine` basis:: 

924 

925 engine = create_engine( 

926 "sqlite://", execution_options={"sqlite_raw_colnames": True} 

927 ) 

928 

929When using the per-:class:`_engine.Engine` execution option, note that 

930**Core and ORM queries that use UNION may not function properly**. 

931 

932SQLite-specific table options 

933----------------------------- 

934 

935One option for CREATE TABLE is supported directly by the SQLite 

936dialect in conjunction with the :class:`_schema.Table` construct: 

937 

938* ``WITHOUT ROWID``:: 

939 

940 Table("some_table", metadata, ..., sqlite_with_rowid=False) 

941 

942* 

943 ``STRICT``:: 

944 

945 Table("some_table", metadata, ..., sqlite_strict=True) 

946 

947 .. versionadded:: 2.0.37 

948 

949.. seealso:: 

950 

951 `SQLite CREATE TABLE options 

952 <https://www.sqlite.org/lang_createtable.html>`_ 

953 

954.. _sqlite_include_internal: 

955 

956Reflecting internal schema tables 

957---------------------------------- 

958 

959Reflection methods that return lists of tables will omit so-called 

960"SQLite internal schema object" names, which are considered by SQLite 

961as any object name that is prefixed with ``sqlite_``. An example of 

962such an object is the ``sqlite_sequence`` table that's generated when 

963the ``AUTOINCREMENT`` column parameter is used. In order to return 

964these objects, the parameter ``sqlite_include_internal=True`` may be 

965passed to methods such as :meth:`_schema.MetaData.reflect` or 

966:meth:`.Inspector.get_table_names`. 

967 

968.. versionadded:: 2.0 Added the ``sqlite_include_internal=True`` parameter. 

969 Previously, these tables were not ignored by SQLAlchemy reflection 

970 methods. 

971 

972.. note:: 

973 

974 The ``sqlite_include_internal`` parameter does not refer to the 

975 "system" tables that are present in schemas such as ``sqlite_master``. 

976 

977.. seealso:: 

978 

979 `SQLite Internal Schema Objects <https://www.sqlite.org/fileformat2.html#intschema>`_ - in the SQLite 

980 documentation. 

981 

982''' # noqa 

983 

984from __future__ import annotations 

985 

986import datetime 

987import numbers 

988import re 

989from typing import Any 

990from typing import Callable 

991from typing import Optional 

992from typing import TYPE_CHECKING 

993 

994from .json import JSON 

995from .json import JSONB 

996from .json import JSONIndexType 

997from .json import JSONPathType 

998from ... import exc 

999from ... import schema as sa_schema 

1000from ... import sql 

1001from ... import text 

1002from ... import types as sqltypes 

1003from ... import util 

1004from ...engine import default 

1005from ...engine import processors 

1006from ...engine import reflection 

1007from ...engine.reflection import ReflectionDefaults 

1008from ...sql import coercions 

1009from ...sql import compiler 

1010from ...sql import ddl as sa_ddl 

1011from ...sql import elements 

1012from ...sql import roles 

1013from ...sql import schema 

1014from ...types import BLOB # noqa 

1015from ...types import BOOLEAN # noqa 

1016from ...types import CHAR # noqa 

1017from ...types import DECIMAL # noqa 

1018from ...types import FLOAT # noqa 

1019from ...types import INTEGER # noqa 

1020from ...types import NUMERIC # noqa 

1021from ...types import REAL # noqa 

1022from ...types import SMALLINT # noqa 

1023from ...types import TEXT # noqa 

1024from ...types import TIMESTAMP # noqa 

1025from ...types import VARCHAR # noqa 

1026 

1027if TYPE_CHECKING: 

1028 from ...engine.interfaces import DBAPIConnection 

1029 from ...engine.interfaces import Dialect 

1030 from ...engine.interfaces import IsolationLevel 

1031 from ...sql.sqltypes import _JSON_VALUE 

1032 from ...sql.type_api import _BindProcessorType 

1033 from ...sql.type_api import _ResultProcessorType 

1034 

1035 

1036class _SQliteJson(JSON): 

1037 def result_processor(self, dialect, coltype): 

1038 default_processor = super().result_processor(dialect, coltype) 

1039 

1040 def process(value): 

1041 try: 

1042 return default_processor(value) 

1043 except TypeError: 

1044 if isinstance(value, numbers.Number): 

1045 return value 

1046 else: 

1047 raise 

1048 

1049 return process 

1050 

1051 

1052class _DateTimeMixin: 

1053 _reg = None 

1054 _storage_format = None 

1055 

1056 def __init__(self, storage_format=None, regexp=None, **kw): 

1057 super().__init__(**kw) 

1058 if regexp is not None: 

1059 self._reg = re.compile(regexp) 

1060 if storage_format is not None: 

1061 self._storage_format = storage_format 

1062 

1063 @property 

1064 def format_is_text_affinity(self): 

1065 """return True if the storage format will automatically imply 

1066 a TEXT affinity. 

1067 

1068 If the storage format contains no non-numeric characters, 

1069 it will imply a NUMERIC storage format on SQLite; in this case, 

1070 the type will generate its DDL as DATE_CHAR, DATETIME_CHAR, 

1071 TIME_CHAR. 

1072 

1073 """ 

1074 spec = self._storage_format % { 

1075 "year": 0, 

1076 "month": 0, 

1077 "day": 0, 

1078 "hour": 0, 

1079 "minute": 0, 

1080 "second": 0, 

1081 "microsecond": 0, 

1082 } 

1083 return bool(re.search(r"[^0-9]", spec)) 

1084 

1085 def adapt(self, cls, **kw): 

1086 if issubclass(cls, _DateTimeMixin): 

1087 if self._storage_format: 

1088 kw["storage_format"] = self._storage_format 

1089 if self._reg: 

1090 kw["regexp"] = self._reg 

1091 return super().adapt(cls, **kw) 

1092 

1093 def literal_processor(self, dialect): 

1094 bp = self.bind_processor(dialect) 

1095 

1096 def process(value): 

1097 return "'%s'" % bp(value) 

1098 

1099 return process 

1100 

1101 

1102class DATETIME(_DateTimeMixin, sqltypes.DateTime): 

1103 r"""Represent a Python datetime object in SQLite using a string. 

1104 

1105 The default string storage format is:: 

1106 

1107 "%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d" 

1108 

1109 e.g.: 

1110 

1111 .. sourcecode:: text 

1112 

1113 2021-03-15 12:05:57.105542 

1114 

1115 The incoming storage format is by default parsed using the 

1116 Python ``datetime.fromisoformat()`` function. 

1117 

1118 .. versionchanged:: 2.0 ``datetime.fromisoformat()`` is used for default 

1119 datetime string parsing. 

1120 

1121 The storage format can be customized to some degree using the 

1122 ``storage_format`` and ``regexp`` parameters, such as:: 

1123 

1124 import re 

1125 from sqlalchemy.dialects.sqlite import DATETIME 

1126 

1127 dt = DATETIME( 

1128 storage_format=( 

1129 "%(year)04d/%(month)02d/%(day)02d %(hour)02d:%(minute)02d:%(second)02d" 

1130 ), 

1131 regexp=r"(\d+)/(\d+)/(\d+) (\d+)-(\d+)-(\d+)", 

1132 ) 

1133 

1134 :param truncate_microseconds: when ``True`` microseconds will be truncated 

1135 from the datetime. Can't be specified together with ``storage_format`` 

1136 or ``regexp``. 

1137 

1138 :param storage_format: format string which will be applied to the dict 

1139 with keys year, month, day, hour, minute, second, and microsecond. 

1140 

1141 :param regexp: regular expression which will be applied to incoming result 

1142 rows, replacing the use of ``datetime.fromisoformat()`` to parse incoming 

1143 strings. If the regexp contains named groups, the resulting match dict is 

1144 applied to the Python datetime() constructor as keyword arguments. 

1145 Otherwise, if positional groups are used, the datetime() constructor 

1146 is called with positional arguments via 

1147 ``*map(int, match_obj.groups(0))``. 

1148 

1149 """ # noqa 

1150 

1151 _storage_format = ( 

1152 "%(year)04d-%(month)02d-%(day)02d " 

1153 "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d" 

1154 ) 

1155 

1156 def __init__(self, *args, **kwargs): 

1157 truncate_microseconds = kwargs.pop("truncate_microseconds", False) 

1158 super().__init__(*args, **kwargs) 

1159 if truncate_microseconds: 

1160 assert "storage_format" not in kwargs, ( 

1161 "You can specify only " 

1162 "one of truncate_microseconds or storage_format." 

1163 ) 

1164 assert "regexp" not in kwargs, ( 

1165 "You can specify only one of " 

1166 "truncate_microseconds or regexp." 

1167 ) 

1168 self._storage_format = ( 

1169 "%(year)04d-%(month)02d-%(day)02d " 

1170 "%(hour)02d:%(minute)02d:%(second)02d" 

1171 ) 

1172 

1173 def bind_processor( 

1174 self, dialect: Dialect 

1175 ) -> Optional[_BindProcessorType[Any]]: 

1176 datetime_datetime = datetime.datetime 

1177 datetime_date = datetime.date 

1178 format_ = self._storage_format 

1179 

1180 def process(value): 

1181 if value is None: 

1182 return None 

1183 elif isinstance(value, datetime_datetime): 

1184 return format_ % { 

1185 "year": value.year, 

1186 "month": value.month, 

1187 "day": value.day, 

1188 "hour": value.hour, 

1189 "minute": value.minute, 

1190 "second": value.second, 

1191 "microsecond": value.microsecond, 

1192 } 

1193 elif isinstance(value, datetime_date): 

1194 return format_ % { 

1195 "year": value.year, 

1196 "month": value.month, 

1197 "day": value.day, 

1198 "hour": 0, 

1199 "minute": 0, 

1200 "second": 0, 

1201 "microsecond": 0, 

1202 } 

1203 else: 

1204 raise TypeError( 

1205 "SQLite DateTime type only accepts Python " 

1206 "datetime and date objects as input." 

1207 ) 

1208 

1209 return process 

1210 

1211 def result_processor( 

1212 self, dialect: Dialect, coltype: object 

1213 ) -> Optional[_ResultProcessorType[Any]]: 

1214 if self._reg: 

1215 return processors.str_to_datetime_processor_factory( 

1216 self._reg, datetime.datetime 

1217 ) 

1218 else: 

1219 return processors.str_to_datetime 

1220 

1221 

1222class DATE(_DateTimeMixin, sqltypes.Date): 

1223 r"""Represent a Python date object in SQLite using a string. 

1224 

1225 The default string storage format is:: 

1226 

1227 "%(year)04d-%(month)02d-%(day)02d" 

1228 

1229 e.g.: 

1230 

1231 .. sourcecode:: text 

1232 

1233 2011-03-15 

1234 

1235 The incoming storage format is by default parsed using the 

1236 Python ``date.fromisoformat()`` function. 

1237 

1238 .. versionchanged:: 2.0 ``date.fromisoformat()`` is used for default 

1239 date string parsing. 

1240 

1241 

1242 The storage format can be customized to some degree using the 

1243 ``storage_format`` and ``regexp`` parameters, such as:: 

1244 

1245 import re 

1246 from sqlalchemy.dialects.sqlite import DATE 

1247 

1248 d = DATE( 

1249 storage_format="%(month)02d/%(day)02d/%(year)04d", 

1250 regexp=re.compile("(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)"), 

1251 ) 

1252 

1253 :param storage_format: format string which will be applied to the 

1254 dict with keys year, month, and day. 

1255 

1256 :param regexp: regular expression which will be applied to 

1257 incoming result rows, replacing the use of ``date.fromisoformat()`` to 

1258 parse incoming strings. If the regexp contains named groups, the resulting 

1259 match dict is applied to the Python date() constructor as keyword 

1260 arguments. Otherwise, if positional groups are used, the date() 

1261 constructor is called with positional arguments via 

1262 ``*map(int, match_obj.groups(0))``. 

1263 

1264 """ 

1265 

1266 _storage_format = "%(year)04d-%(month)02d-%(day)02d" 

1267 

1268 def bind_processor( 

1269 self, dialect: Dialect 

1270 ) -> Optional[_BindProcessorType[Any]]: 

1271 datetime_date = datetime.date 

1272 format_ = self._storage_format 

1273