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 cursor = dbapi_connection.cursor() 

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

401 cursor.close() 

402 

403.. warning:: 

404 

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

406 to emit CREATE or DROP statements for tables that contain 

407 mutually-dependent foreign key constraints; 

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

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

410 no support. 

411 

412.. seealso:: 

413 

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

415 - on the SQLite web site. 

416 

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

418 

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

420 mutually-dependent foreign key constraints. 

421 

422.. _sqlite_on_conflict_ddl: 

423 

424ON CONFLICT support for constraints 

425----------------------------------- 

426 

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

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

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

430 

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

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

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

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

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

436specified with a string conflict resolution algorithm within the 

437:class:`.PrimaryKeyConstraint`, :class:`.UniqueConstraint`, 

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

439there 

440are individual parameters ``sqlite_on_conflict_not_null``, 

441``sqlite_on_conflict_primary_key``, ``sqlite_on_conflict_unique`` which each 

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

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

444 

445.. seealso:: 

446 

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

448 documentation 

449 

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

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

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

453that specifies the IGNORE algorithm:: 

454 

455 some_table = Table( 

456 "some_table", 

457 metadata, 

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

459 Column("data", Integer), 

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

461 ) 

462 

463The above renders CREATE TABLE DDL as: 

464 

465.. sourcecode:: sql 

466 

467 CREATE TABLE some_table ( 

468 id INTEGER NOT NULL, 

469 data INTEGER, 

470 PRIMARY KEY (id), 

471 UNIQUE (id, data) ON CONFLICT IGNORE 

472 ) 

473 

474 

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

476flag to add a UNIQUE constraint 

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

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

479UNIQUE constraint in the DDL:: 

480 

481 some_table = Table( 

482 "some_table", 

483 metadata, 

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

485 Column( 

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

487 ), 

488 ) 

489 

490rendering: 

491 

492.. sourcecode:: sql 

493 

494 CREATE TABLE some_table ( 

495 id INTEGER NOT NULL, 

496 data INTEGER, 

497 PRIMARY KEY (id), 

498 UNIQUE (data) ON CONFLICT IGNORE 

499 ) 

500 

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

502``sqlite_on_conflict_not_null`` is used:: 

503 

504 some_table = Table( 

505 "some_table", 

506 metadata, 

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

508 Column( 

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

510 ), 

511 ) 

512 

513this renders the column inline ON CONFLICT phrase: 

514 

515.. sourcecode:: sql 

516 

517 CREATE TABLE some_table ( 

518 id INTEGER NOT NULL, 

519 data INTEGER NOT NULL ON CONFLICT FAIL, 

520 PRIMARY KEY (id) 

521 ) 

522 

523 

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

525 

526 some_table = Table( 

527 "some_table", 

528 metadata, 

529 Column( 

530 "id", 

531 Integer, 

532 primary_key=True, 

533 sqlite_on_conflict_primary_key="FAIL", 

534 ), 

535 ) 

536 

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

538resolution algorithm is applied to the constraint itself: 

539 

540.. sourcecode:: sql 

541 

542 CREATE TABLE some_table ( 

543 id INTEGER NOT NULL, 

544 PRIMARY KEY (id) ON CONFLICT FAIL 

545 ) 

546 

547.. _sqlite_on_conflict_insert: 

548 

549INSERT...ON CONFLICT (Upsert) 

550----------------------------- 

551 

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

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

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

555 

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

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

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

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

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

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

562to silently skip this row. 

563 

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

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

566columns and conditions that comprise the indexes. 

567 

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

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

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

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

572 

573.. sourcecode:: pycon+sql 

574 

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

576 

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

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

579 ... ) 

580 

581 >>> do_update_stmt = insert_stmt.on_conflict_do_update( 

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

583 ... ) 

584 

585 >>> print(do_update_stmt) 

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

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

588 

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

590 

591 >>> print(do_nothing_stmt) 

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

593 ON CONFLICT (id) DO NOTHING 

594 

595.. versionadded:: 1.4 

596 

597.. seealso:: 

598 

599 `Upsert 

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

601 - in the SQLite documentation. 

602 

603 

604Specifying the Target 

605^^^^^^^^^^^^^^^^^^^^^ 

606 

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

608 

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

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

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

612 or unique constraint. 

613 

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

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

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

617 

618 .. sourcecode:: pycon+sql 

619 

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

621 

622 >>> do_update_stmt = stmt.on_conflict_do_update( 

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

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

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

626 ... ) 

627 

628 >>> print(do_update_stmt) 

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

630 ON CONFLICT (user_email) 

631 WHERE user_email LIKE '%@gmail.com' 

632 DO UPDATE SET data = excluded.data 

633 

634The SET Clause 

635^^^^^^^^^^^^^^^ 

636 

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

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

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

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

641parameter accepts a dictionary which consists of direct values 

642for UPDATE: 

643 

644.. sourcecode:: pycon+sql 

645 

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

647 

648 >>> do_update_stmt = stmt.on_conflict_do_update( 

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

650 ... ) 

651 

652 >>> print(do_update_stmt) 

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

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

655 

656.. warning:: 

657 

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

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

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

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

662 they are manually specified in the 

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

664 

665Updating using the Excluded INSERT Values 

666^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

667 

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

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

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

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

672would have been inserted had the constraint not failed: 

673 

674.. sourcecode:: pycon+sql 

675 

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

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

678 ... ) 

679 

680 >>> do_update_stmt = stmt.on_conflict_do_update( 

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

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

683 ... ) 

684 

685 >>> print(do_update_stmt) 

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

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

688 

689Additional WHERE Criteria 

690^^^^^^^^^^^^^^^^^^^^^^^^^ 

691 

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

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

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

695 

696.. sourcecode:: pycon+sql 

697 

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

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

700 ... ) 

701 

702 >>> on_update_stmt = stmt.on_conflict_do_update( 

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

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

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

706 ... ) 

707 >>> print(on_update_stmt) 

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

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

710 WHERE my_table.status = ? 

711 

712 

713Skipping Rows with DO NOTHING 

714^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

715 

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

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

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

719 

720.. sourcecode:: pycon+sql 

721 

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

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

724 >>> print(stmt) 

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

726 

727 

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

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

730occurs: 

731 

732.. sourcecode:: pycon+sql 

733 

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

735 >>> stmt = stmt.on_conflict_do_nothing() 

736 >>> print(stmt) 

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

738 

739.. _sqlite_type_reflection: 

740 

741Type Reflection 

742--------------- 

743 

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

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

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

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

748pattern for the type. 

749 

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

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

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

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

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

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

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

757 

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

759name match for the following types: 

760 

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

762:class:`_types.BOOLEAN`, :class:`_types.BOOLEAN`, 

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

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

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

766:class:`_types.INTEGER`, :class:`_types.INTEGER`, 

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

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

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

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

771:class:`_types.NCHAR` 

772 

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

774lookup is used instead: 

775 

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

777 string ``INT`` 

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

779 string ``CHAR``, ``CLOB`` or ``TEXT`` 

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

781 string ``BLOB`` 

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

783 ``REAL``, ``FLOA`` or ``DOUB``. 

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

785 

786.. _sqlite_partial_index: 

787 

788Partial Indexes 

789--------------- 

790 

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

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

793 

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

795 idx = Index( 

796 "test_idx1", 

797 tbl.c.data, 

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

799 ) 

800 

801The index will be rendered at create time as: 

802 

803.. sourcecode:: sql 

804 

805 CREATE INDEX test_idx1 ON testtbl (data) 

806 WHERE data > 5 AND data < 10 

807 

808.. _sqlite_dotted_column_names: 

809 

810Dotted Column Names 

811------------------- 

812 

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

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

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

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

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

818 

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

820 

821 import sqlite3 

822 

823 assert sqlite3.sqlite_version_info < ( 

824 3, 

825 10, 

826 0, 

827 ), "bug is fixed in this version" 

828 

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

830 cursor = conn.cursor() 

831 

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

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

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

835 

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

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

838 

839 cursor.execute( 

840 """ 

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

842 union 

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

844 """ 

845 ) 

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

847 c[0] for c in cursor.description 

848 ] 

849 

850The second assertion fails: 

851 

852.. sourcecode:: text 

853 

854 Traceback (most recent call last): 

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

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

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

858 

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

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

861when the UNION is not present. 

862 

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

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

865to filter these out:: 

866 

867 

868 from sqlalchemy import create_engine 

869 

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

871 conn = eng.connect() 

872 

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

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

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

876 

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

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

879 

880 result = conn.exec_driver_sql( 

881 """ 

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

883 union 

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

885 """ 

886 ) 

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

888 

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

890names are still addressable*:: 

891 

892 >>> row = result.first() 

893 >>> row["a"] 

894 1 

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

896 1 

897 >>> row["b"] 

898 1 

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

900 1 

901 

902Therefore, the workaround applied by SQLAlchemy only impacts 

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

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

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

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

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

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

909 

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

911 """ 

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

913 union 

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

915 """ 

916 ) 

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

918 

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

920 

921 engine = create_engine( 

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

923 ) 

924 

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

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

927 

928SQLite-specific table options 

929----------------------------- 

930 

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

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

933 

934* ``WITHOUT ROWID``:: 

935 

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

937 

938* 

939 ``STRICT``:: 

940 

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

942 

943 .. versionadded:: 2.0.37 

944 

945.. seealso:: 

946 

947 `SQLite CREATE TABLE options 

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

949 

950.. _sqlite_include_internal: 

951 

952Reflecting internal schema tables 

953---------------------------------- 

954 

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

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

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

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

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

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

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

962:meth:`.Inspector.get_table_names`. 

963 

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

965 Previously, these tables were not ignored by SQLAlchemy reflection 

966 methods. 

967 

968.. note:: 

969 

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

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

972 

973.. seealso:: 

974 

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

976 documentation. 

977 

978''' # noqa 

979from __future__ import annotations 

980 

981import datetime 

982import numbers 

983import re 

984from typing import Optional 

985 

986from .json import JSON 

987from .json import JSONIndexType 

988from .json import JSONPathType 

989from ... import exc 

990from ... import schema as sa_schema 

991from ... import sql 

992from ... import text 

993from ... import types as sqltypes 

994from ... import util 

995from ...engine import default 

996from ...engine import processors 

997from ...engine import reflection 

998from ...engine.reflection import ReflectionDefaults 

999from ...sql import coercions 

1000from ...sql import compiler 

1001from ...sql import elements 

1002from ...sql import roles 

1003from ...sql import schema 

1004from ...types import BLOB # noqa 

1005from ...types import BOOLEAN # noqa 

1006from ...types import CHAR # noqa 

1007from ...types import DECIMAL # noqa 

1008from ...types import FLOAT # noqa 

1009from ...types import INTEGER # noqa 

1010from ...types import NUMERIC # noqa 

1011from ...types import REAL # noqa 

1012from ...types import SMALLINT # noqa 

1013from ...types import TEXT # noqa 

1014from ...types import TIMESTAMP # noqa 

1015from ...types import VARCHAR # noqa 

1016 

1017 

1018class _SQliteJson(JSON): 

1019 def result_processor(self, dialect, coltype): 

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

1021 

1022 def process(value): 

1023 try: 

1024 return default_processor(value) 

1025 except TypeError: 

1026 if isinstance(value, numbers.Number): 

1027 return value 

1028 else: 

1029 raise 

1030 

1031 return process 

1032 

1033 

1034class _DateTimeMixin: 

1035 _reg = None 

1036 _storage_format = None 

1037 

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

1039 super().__init__(**kw) 

1040 if regexp is not None: 

1041 self._reg = re.compile(regexp) 

1042 if storage_format is not None: 

1043 self._storage_format = storage_format 

1044 

1045 @property 

1046 def format_is_text_affinity(self): 

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

1048 a TEXT affinity. 

1049 

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

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

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

1053 TIME_CHAR. 

1054 

1055 """ 

1056 spec = self._storage_format % { 

1057 "year": 0, 

1058 "month": 0, 

1059 "day": 0, 

1060 "hour": 0, 

1061 "minute": 0, 

1062 "second": 0, 

1063 "microsecond": 0, 

1064 } 

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

1066 

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

1068 if issubclass(cls, _DateTimeMixin): 

1069 if self._storage_format: 

1070 kw["storage_format"] = self._storage_format 

1071 if self._reg: 

1072 kw["regexp"] = self._reg 

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

1074 

1075 def literal_processor(self, dialect): 

1076 bp = self.bind_processor(dialect) 

1077 

1078 def process(value): 

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

1080 

1081 return process 

1082 

1083 

1084class DATETIME(_DateTimeMixin, sqltypes.DateTime): 

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

1086 

1087 The default string storage format is:: 

1088 

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

1090 

1091 e.g.: 

1092 

1093 .. sourcecode:: text 

1094 

1095 2021-03-15 12:05:57.105542 

1096 

1097 The incoming storage format is by default parsed using the 

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

1099 

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

1101 datetime string parsing. 

1102 

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

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

1105 

1106 import re 

1107 from sqlalchemy.dialects.sqlite import DATETIME 

1108 

1109 dt = DATETIME( 

1110 storage_format=( 

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

1112 ), 

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

1114 ) 

1115 

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

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

1118 or ``regexp``. 

1119 

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

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

1122 

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

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

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

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

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

1128 is called with positional arguments via 

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

1130 

1131 """ # noqa 

1132 

1133 _storage_format = ( 

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

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

1136 ) 

1137 

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

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

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

1141 if truncate_microseconds: 

1142 assert "storage_format" not in kwargs, ( 

1143 "You can specify only " 

1144 "one of truncate_microseconds or storage_format." 

1145 ) 

1146 assert "regexp" not in kwargs, ( 

1147 "You can specify only one of " 

1148 "truncate_microseconds or regexp." 

1149 ) 

1150 self._storage_format = ( 

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

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

1153 ) 

1154 

1155 def bind_processor(self, dialect): 

1156 datetime_datetime = datetime.datetime 

1157 datetime_date = datetime.date 

1158 format_ = self._storage_format 

1159 

1160 def process(value): 

1161 if value is None: 

1162 return None 

1163 elif isinstance(value, datetime_datetime): 

1164 return format_ % { 

1165 "year": value.year, 

1166 "month": value.month, 

1167 "day": value.day, 

1168 "hour": value.hour, 

1169 "minute": value.minute, 

1170 "second": value.second, 

1171 "microsecond": value.microsecond, 

1172 } 

1173 elif isinstance(value, datetime_date): 

1174 return format_ % { 

1175 "year": value.year, 

1176 "month": value.month, 

1177 "day": value.day, 

1178 "hour": 0, 

1179 "minute": 0, 

1180 "second": 0, 

1181 "microsecond": 0, 

1182 } 

1183 else: 

1184 raise TypeError( 

1185 "SQLite DateTime type only accepts Python " 

1186 "datetime and date objects as input." 

1187 ) 

1188 

1189 return process 

1190 

1191 def result_processor(self, dialect, coltype): 

1192 if self._reg: 

1193 return processors.str_to_datetime_processor_factory( 

1194 self._reg, datetime.datetime 

1195 ) 

1196 else: 

1197 return processors.str_to_datetime 

1198 

1199 

1200class DATE(_DateTimeMixin, sqltypes.Date): 

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

1202 

1203 The default string storage format is:: 

1204 

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

1206 

1207 e.g.: 

1208 

1209 .. sourcecode:: text 

1210 

1211 2011-03-15 

1212 

1213 The incoming storage format is by default parsed using the 

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

1215 

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

1217 date string parsing. 

1218 

1219 

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

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

1222 

1223 import re 

1224 from sqlalchemy.dialects.sqlite import DATE 

1225 

1226 d = DATE( 

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

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

1229 ) 

1230 

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

1232 dict with keys year, month, and day. 

1233 

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

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

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

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

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

1239 constructor is called with positional arguments via 

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

1241 

1242 """ 

1243 

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

1245 

1246 def bind_processor(self, dialect): 

1247 datetime_date = datetime.date 

1248 format_ = self._storage_format 

1249 

1250 def process(value): 

1251 if value is None: 

1252 return None 

1253 elif isinstance(value, datetime_date): 

1254 return format_ % { 

1255 "year": value.year, 

1256 "month": value.month, 

1257 "day": value.day, 

1258 } 

1259 else: 

1260 raise TypeError( 

1261 "SQLite Date type only accepts Python " 

1262 "date objects as input." 

1263 ) 

1264 

1265 return process 

1266 

1267 def result_processor(self, dialect, coltype): 

1268 if self._reg: 

1269 return processors.str_to_datetime_processor_factory( 

1270 self._reg, datetime.date 

1271 ) 

1272 else: 

1273 return processors.str_to_date 

1274 

1275 

1276class TIME(_DateTimeMixin, sqltypes.Time):