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

1# dialects/sqlite/base.py 

2# Copyright (C) 2005-2025 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 faciliate 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-compatiblity 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 """ 

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

850 union 

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

852 """ 

853 ) 

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

855 c[0] for c in cursor.description 

856 ] 

857 

858The second assertion fails: 

859 

860.. sourcecode:: text 

861 

862 Traceback (most recent call last): 

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

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

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

866 

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

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

869when the UNION is not present. 

870 

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

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

873to filter these out:: 

874 

875 

876 from sqlalchemy import create_engine 

877 

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

879 conn = eng.connect() 

880 

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

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

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

884 

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

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

887 

888 result = conn.exec_driver_sql( 

889 """ 

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

891 union 

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

893 """ 

894 ) 

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

896 

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

898names are still addressable*:: 

899 

900 >>> row = result.first() 

901 >>> row["a"] 

902 1 

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

904 1 

905 >>> row["b"] 

906 1 

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

908 1 

909 

910Therefore, the workaround applied by SQLAlchemy only impacts 

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

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

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

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

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

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

917 

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

919 """ 

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

921 union 

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

923 """ 

924 ) 

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

926 

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

928 

929 engine = create_engine( 

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

931 ) 

932 

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

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

935 

936SQLite-specific table options 

937----------------------------- 

938 

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

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

941 

942* ``WITHOUT ROWID``:: 

943 

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

945 

946* 

947 ``STRICT``:: 

948 

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

950 

951 .. versionadded:: 2.0.37 

952 

953.. seealso:: 

954 

955 `SQLite CREATE TABLE options 

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

957 

958.. _sqlite_include_internal: 

959 

960Reflecting internal schema tables 

961---------------------------------- 

962 

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

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

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

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

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

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

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

970:meth:`.Inspector.get_table_names`. 

971 

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

973 Previously, these tables were not ignored by SQLAlchemy reflection 

974 methods. 

975 

976.. note:: 

977 

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

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

980 

981.. seealso:: 

982 

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

984 documentation. 

985 

986''' # noqa 

987from __future__ import annotations 

988 

989import datetime 

990import numbers 

991import re 

992from typing import Optional 

993 

994from .json import JSON 

995from .json import JSONIndexType 

996from .json import JSONPathType 

997from ... import exc 

998from ... import schema as sa_schema 

999from ... import sql 

1000from ... import text 

1001from ... import types as sqltypes 

1002from ... import util 

1003from ...engine import default 

1004from ...engine import processors 

1005from ...engine import reflection 

1006from ...engine.reflection import ReflectionDefaults 

1007from ...sql import coercions 

1008from ...sql import compiler 

1009from ...sql import elements 

1010from ...sql import roles 

1011from ...sql import schema 

1012from ...types import BLOB # noqa 

1013from ...types import BOOLEAN # noqa 

1014from ...types import CHAR # noqa 

1015from ...types import DECIMAL # noqa 

1016from ...types import FLOAT # noqa 

1017from ...types import INTEGER # noqa 

1018from ...types import NUMERIC # noqa 

1019from ...types import REAL # noqa 

1020from ...types import SMALLINT # noqa 

1021from ...types import TEXT # noqa 

1022from ...types import TIMESTAMP # noqa 

1023from ...types import VARCHAR # noqa 

1024 

1025 

1026class _SQliteJson(JSON): 

1027 def result_processor(self, dialect, coltype): 

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

1029 

1030 def process(value): 

1031 try: 

1032 return default_processor(value) 

1033 except TypeError: 

1034 if isinstance(value, numbers.Number): 

1035 return value 

1036 else: 

1037 raise 

1038 

1039 return process 

1040 

1041 

1042class _DateTimeMixin: 

1043 _reg = None 

1044 _storage_format = None 

1045 

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

1047 super().__init__(**kw) 

1048 if regexp is not None: 

1049 self._reg = re.compile(regexp) 

1050 if storage_format is not None: 

1051 self._storage_format = storage_format 

1052 

1053 @property 

1054 def format_is_text_affinity(self): 

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

1056 a TEXT affinity. 

1057 

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

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

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

1061 TIME_CHAR. 

1062 

1063 """ 

1064 spec = self._storage_format % { 

1065 "year": 0, 

1066 "month": 0, 

1067 "day": 0, 

1068 "hour": 0, 

1069 "minute": 0, 

1070 "second": 0, 

1071 "microsecond": 0, 

1072 } 

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

1074 

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

1076 if issubclass(cls, _DateTimeMixin): 

1077 if self._storage_format: 

1078 kw["storage_format"] = self._storage_format 

1079 if self._reg: 

1080 kw["regexp"] = self._reg 

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

1082 

1083 def literal_processor(self, dialect): 

1084 bp = self.bind_processor(dialect) 

1085 

1086 def process(value): 

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

1088 

1089 return process 

1090 

1091 

1092class DATETIME(_DateTimeMixin, sqltypes.DateTime): 

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

1094 

1095 The default string storage format is:: 

1096 

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

1098 

1099 e.g.: 

1100 

1101 .. sourcecode:: text 

1102 

1103 2021-03-15 12:05:57.105542 

1104 

1105 The incoming storage format is by default parsed using the 

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

1107 

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

1109 datetime string parsing. 

1110 

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

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

1113 

1114 import re 

1115 from sqlalchemy.dialects.sqlite import DATETIME 

1116 

1117 dt = DATETIME( 

1118 storage_format=( 

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

1120 ), 

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

1122 ) 

1123 

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

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

1126 or ``regexp``. 

1127 

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

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

1130 

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

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

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

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

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

1136 is called with positional arguments via 

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

1138 

1139 """ # noqa 

1140 

1141 _storage_format = ( 

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

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

1144 ) 

1145 

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

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

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

1149 if truncate_microseconds: 

1150 assert "storage_format" not in kwargs, ( 

1151 "You can specify only " 

1152 "one of truncate_microseconds or storage_format." 

1153 ) 

1154 assert "regexp" not in kwargs, ( 

1155 "You can specify only one of " 

1156 "truncate_microseconds or regexp." 

1157 ) 

1158 self._storage_format = ( 

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

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

1161 ) 

1162 

1163 def bind_processor(self, dialect): 

1164 datetime_datetime = datetime.datetime 

1165 datetime_date = datetime.date 

1166 format_ = self._storage_format 

1167 

1168 def process(value): 

1169 if value is None: 

1170 return None 

1171 elif isinstance(value, datetime_datetime): 

1172 return format_ % { 

1173 "year": value.year, 

1174 "month": value.month, 

1175 "day": value.day, 

1176 "hour": value.hour, 

1177 "minute": value.minute, 

1178 "second": value.second, 

1179 "microsecond": value.microsecond, 

1180 } 

1181 elif isinstance(value, datetime_date): 

1182 return format_ % { 

1183 "year": value.year, 

1184 "month": value.month, 

1185 "day": value.day, 

1186 "hour": 0, 

1187 "minute": 0, 

1188 "second": 0, 

1189 "microsecond": 0, 

1190 } 

1191 else: 

1192 raise TypeError( 

1193 "SQLite DateTime type only accepts Python " 

1194 "datetime and date objects as input." 

1195 ) 

1196 

1197 return process 

1198 

1199 def result_processor(self, dialect, coltype): 

1200 if self._reg: 

1201 return processors.str_to_datetime_processor_factory( 

1202 self._reg, datetime.datetime 

1203 ) 

1204 else: 

1205 return processors.str_to_datetime 

1206 

1207 

1208class DATE(_DateTimeMixin, sqltypes.Date): 

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

1210 

1211 The default string storage format is:: 

1212 

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

1214 

1215 e.g.: 

1216 

1217 .. sourcecode:: text 

1218 

1219 2011-03-15 

1220 

1221 The incoming storage format is by default parsed using the 

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

1223 

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

1225 date string parsing. 

1226 

1227 

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

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

1230 

1231 import re 

1232 from sqlalchemy.dialects.sqlite import DATE 

1233 

1234 d = DATE( 

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

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

1237 ) 

1238 

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

1240 dict with keys year, month, and day. 

1241 

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

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

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

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

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

1247 constructor is called with positional arguments via 

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

1249 

1250 """ 

1251 

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

1253 

1254 def bind_processor(self, dialect): 

1255 datetime_date = datetime.date 

1256 format_ = self._storage_format 

1257 

1258 def process(value): 

1259 if value is None: 

1260 return None 

1261 elif isinstance(value, datetime_date): 

1262 return format_ % { 

1263 "year": value.year, 

1264 "month": value.month, 

1265 "day": value.day, 

1266 } 

1267 else: 

1268 raise TypeError( 

1269 "SQLite Date type only accepts Python " 

1270 "date objects as input." 

1271 ) 

1272 

1273 return process 

1274 

1275 def result_processor(self, dialect, coltype): 

1276 if self._reg: 

1277 return processors.str_to_datetime_processor_factory( 

1278 self._reg, datetime.date