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

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 

450.. versionadded:: 1.3 

451 

452 

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

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

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

456that specifies the IGNORE algorithm:: 

457 

458 some_table = Table( 

459 "some_table", 

460 metadata, 

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

462 Column("data", Integer), 

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

464 ) 

465 

466The above renders CREATE TABLE DDL as: 

467 

468.. sourcecode:: sql 

469 

470 CREATE TABLE some_table ( 

471 id INTEGER NOT NULL, 

472 data INTEGER, 

473 PRIMARY KEY (id), 

474 UNIQUE (id, data) ON CONFLICT IGNORE 

475 ) 

476 

477 

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

479flag to add a UNIQUE constraint 

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

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

482UNIQUE constraint in the DDL:: 

483 

484 some_table = Table( 

485 "some_table", 

486 metadata, 

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

488 Column( 

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

490 ), 

491 ) 

492 

493rendering: 

494 

495.. sourcecode:: sql 

496 

497 CREATE TABLE some_table ( 

498 id INTEGER NOT NULL, 

499 data INTEGER, 

500 PRIMARY KEY (id), 

501 UNIQUE (data) ON CONFLICT IGNORE 

502 ) 

503 

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

505``sqlite_on_conflict_not_null`` is used:: 

506 

507 some_table = Table( 

508 "some_table", 

509 metadata, 

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

511 Column( 

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

513 ), 

514 ) 

515 

516this renders the column inline ON CONFLICT phrase: 

517 

518.. sourcecode:: sql 

519 

520 CREATE TABLE some_table ( 

521 id INTEGER NOT NULL, 

522 data INTEGER NOT NULL ON CONFLICT FAIL, 

523 PRIMARY KEY (id) 

524 ) 

525 

526 

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

528 

529 some_table = Table( 

530 "some_table", 

531 metadata, 

532 Column( 

533 "id", 

534 Integer, 

535 primary_key=True, 

536 sqlite_on_conflict_primary_key="FAIL", 

537 ), 

538 ) 

539 

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

541resolution algorithm is applied to the constraint itself: 

542 

543.. sourcecode:: sql 

544 

545 CREATE TABLE some_table ( 

546 id INTEGER NOT NULL, 

547 PRIMARY KEY (id) ON CONFLICT FAIL 

548 ) 

549 

550.. _sqlite_on_conflict_insert: 

551 

552INSERT...ON CONFLICT (Upsert) 

553----------------------------- 

554 

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

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

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

558 

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

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

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

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

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

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

565to silently skip this row. 

566 

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

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

569columns and conditions that comprise the indexes. 

570 

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

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

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

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

575 

576.. sourcecode:: pycon+sql 

577 

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

579 

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

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

582 ... ) 

583 

584 >>> do_update_stmt = insert_stmt.on_conflict_do_update( 

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

586 ... ) 

587 

588 >>> print(do_update_stmt) 

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

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

591 

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

593 

594 >>> print(do_nothing_stmt) 

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

596 ON CONFLICT (id) DO NOTHING 

597 

598.. versionadded:: 1.4 

599 

600.. seealso:: 

601 

602 `Upsert 

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

604 - in the SQLite documentation. 

605 

606 

607Specifying the Target 

608^^^^^^^^^^^^^^^^^^^^^ 

609 

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

611 

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

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

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

615 or unique constraint. 

616 

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

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

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

620 

621 .. sourcecode:: pycon+sql 

622 

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

624 

625 >>> do_update_stmt = stmt.on_conflict_do_update( 

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

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

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

629 ... ) 

630 

631 >>> print(do_update_stmt) 

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

633 ON CONFLICT (user_email) 

634 WHERE user_email LIKE '%@gmail.com' 

635 DO UPDATE SET data = excluded.data 

636 

637The SET Clause 

638^^^^^^^^^^^^^^^ 

639 

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

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

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

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

644parameter accepts a dictionary which consists of direct values 

645for UPDATE: 

646 

647.. sourcecode:: pycon+sql 

648 

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

650 

651 >>> do_update_stmt = stmt.on_conflict_do_update( 

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

653 ... ) 

654 

655 >>> print(do_update_stmt) 

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

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

658 

659.. warning:: 

660 

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

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

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

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

665 they are manually specified in the 

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

667 

668Updating using the Excluded INSERT Values 

669^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

670 

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

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

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

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

675would have been inserted had the constraint not failed: 

676 

677.. sourcecode:: pycon+sql 

678 

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

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

681 ... ) 

682 

683 >>> do_update_stmt = stmt.on_conflict_do_update( 

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

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

686 ... ) 

687 

688 >>> print(do_update_stmt) 

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

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

691 

692Additional WHERE Criteria 

693^^^^^^^^^^^^^^^^^^^^^^^^^ 

694 

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

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

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

698 

699.. sourcecode:: pycon+sql 

700 

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

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

703 ... ) 

704 

705 >>> on_update_stmt = stmt.on_conflict_do_update( 

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

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

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

709 ... ) 

710 >>> print(on_update_stmt) 

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

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

713 WHERE my_table.status = ? 

714 

715 

716Skipping Rows with DO NOTHING 

717^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

718 

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

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

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

722 

723.. sourcecode:: pycon+sql 

724 

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

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

727 >>> print(stmt) 

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

729 

730 

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

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

733occurs: 

734 

735.. sourcecode:: pycon+sql 

736 

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

738 >>> stmt = stmt.on_conflict_do_nothing() 

739 >>> print(stmt) 

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

741 

742.. _sqlite_type_reflection: 

743 

744Type Reflection 

745--------------- 

746 

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

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

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

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

751pattern for the type. 

752 

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

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

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

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

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

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

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

760 

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

762name match for the following types: 

763 

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

765:class:`_types.BOOLEAN`, :class:`_types.BOOLEAN`, 

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

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

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

769:class:`_types.INTEGER`, :class:`_types.INTEGER`, 

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

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

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

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

774:class:`_types.NCHAR` 

775 

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

777lookup is used instead: 

778 

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

780 string ``INT`` 

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

782 string ``CHAR``, ``CLOB`` or ``TEXT`` 

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

784 string ``BLOB`` 

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

786 ``REAL``, ``FLOA`` or ``DOUB``. 

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

788 

789.. _sqlite_partial_index: 

790 

791Partial Indexes 

792--------------- 

793 

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

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

796 

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

798 idx = Index( 

799 "test_idx1", 

800 tbl.c.data, 

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

802 ) 

803 

804The index will be rendered at create time as: 

805 

806.. sourcecode:: sql 

807 

808 CREATE INDEX test_idx1 ON testtbl (data) 

809 WHERE data > 5 AND data < 10 

810 

811.. _sqlite_dotted_column_names: 

812 

813Dotted Column Names 

814------------------- 

815 

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

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

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

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

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

821 

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

823 

824 import sqlite3 

825 

826 assert sqlite3.sqlite_version_info < ( 

827 3, 

828 10, 

829 0, 

830 ), "bug is fixed in this version" 

831 

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

833 cursor = conn.cursor() 

834 

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

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

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

838 

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

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

841 

842 cursor.execute( 

843 """ 

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

845 union 

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

847 """ 

848 ) 

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

850 c[0] for c in cursor.description 

851 ] 

852 

853The second assertion fails: 

854 

855.. sourcecode:: text 

856 

857 Traceback (most recent call last): 

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

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

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

861 

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

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

864when the UNION is not present. 

865 

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

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

868to filter these out:: 

869 

870 

871 from sqlalchemy import create_engine 

872 

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

874 conn = eng.connect() 

875 

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

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

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

879 

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

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

882 

883 result = conn.exec_driver_sql( 

884 """ 

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

886 union 

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

888 """ 

889 ) 

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

891 

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

893names are still addressable*:: 

894 

895 >>> row = result.first() 

896 >>> row["a"] 

897 1 

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

899 1 

900 >>> row["b"] 

901 1 

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

903 1 

904 

905Therefore, the workaround applied by SQLAlchemy only impacts 

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

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

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

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

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

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

912 

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

914 """ 

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

916 union 

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

918 """ 

919 ) 

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

921 

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

923 

924 engine = create_engine( 

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

926 ) 

927 

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

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

930 

931SQLite-specific table options 

932----------------------------- 

933 

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

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

936 

937* ``WITHOUT ROWID``:: 

938 

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

940 

941* 

942 ``STRICT``:: 

943 

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

945 

946 .. versionadded:: 2.0.37 

947 

948.. seealso:: 

949 

950 `SQLite CREATE TABLE options 

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

952 

953.. _sqlite_include_internal: 

954 

955Reflecting internal schema tables 

956---------------------------------- 

957 

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

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

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

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

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

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

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

965:meth:`.Inspector.get_table_names`. 

966 

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

968 Previously, these tables were not ignored by SQLAlchemy reflection 

969 methods. 

970 

971.. note:: 

972 

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

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

975 

976.. seealso:: 

977 

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

979 documentation. 

980 

981''' # noqa 

982from __future__ import annotations 

983 

984import datetime 

985import numbers 

986import re 

987from typing import Optional 

988 

989from .json import JSON 

990from .json import JSONIndexType 

991from .json import JSONPathType 

992from ... import exc 

993from ... import schema as sa_schema 

994from ... import sql 

995from ... import text 

996from ... import types as sqltypes 

997from ... import util 

998from ...engine import default 

999from ...engine import processors 

1000from ...engine import reflection 

1001from ...engine.reflection import ReflectionDefaults 

1002from ...sql import coercions 

1003from ...sql import compiler 

1004from ...sql import elements 

1005from ...sql import roles 

1006from ...sql import schema 

1007from ...types import BLOB # noqa 

1008from ...types import BOOLEAN # noqa 

1009from ...types import CHAR # noqa 

1010from ...types import DECIMAL # noqa 

1011from ...types import FLOAT # noqa 

1012from ...types import INTEGER # noqa 

1013from ...types import NUMERIC # noqa 

1014from ...types import REAL # noqa 

1015from ...types import SMALLINT # noqa 

1016from ...types import TEXT # noqa 

1017from ...types import TIMESTAMP # noqa 

1018from ...types import VARCHAR # noqa 

1019 

1020 

1021class _SQliteJson(JSON): 

1022 def result_processor(self, dialect, coltype): 

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

1024 

1025 def process(value): 

1026 try: 

1027 return default_processor(value) 

1028 except TypeError: 

1029 if isinstance(value, numbers.Number): 

1030 return value 

1031 else: 

1032 raise 

1033 

1034 return process 

1035 

1036 

1037class _DateTimeMixin: 

1038 _reg = None 

1039 _storage_format = None 

1040 

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

1042 super().__init__(**kw) 

1043 if regexp is not None: 

1044 self._reg = re.compile(regexp) 

1045 if storage_format is not None: 

1046 self._storage_format = storage_format 

1047 

1048 @property 

1049 def format_is_text_affinity(self): 

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

1051 a TEXT affinity. 

1052 

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

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

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

1056 TIME_CHAR. 

1057 

1058 """ 

1059 spec = self._storage_format % { 

1060 "year": 0, 

1061 "month": 0, 

1062 "day": 0, 

1063 "hour": 0, 

1064 "minute": 0, 

1065 "second": 0, 

1066 "microsecond": 0, 

1067 } 

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

1069 

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

1071 if issubclass(cls, _DateTimeMixin): 

1072 if self._storage_format: 

1073 kw["storage_format"] = self._storage_format 

1074 if self._reg: 

1075 kw["regexp"] = self._reg 

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

1077 

1078 def literal_processor(self, dialect): 

1079 bp = self.bind_processor(dialect) 

1080 

1081 def process(value): 

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

1083 

1084 return process 

1085 

1086 

1087class DATETIME(_DateTimeMixin, sqltypes.DateTime): 

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

1089 

1090 The default string storage format is:: 

1091 

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

1093 

1094 e.g.: 

1095 

1096 .. sourcecode:: text 

1097 

1098 2021-03-15 12:05:57.105542 

1099 

1100 The incoming storage format is by default parsed using the 

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

1102 

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

1104 datetime string parsing. 

1105 

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

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

1108 

1109 import re 

1110 from sqlalchemy.dialects.sqlite import DATETIME 

1111 

1112 dt = DATETIME( 

1113 storage_format=( 

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

1115 ), 

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

1117 ) 

1118 

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

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

1121 or ``regexp``. 

1122 

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

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

1125 

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

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

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

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

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

1131 is called with positional arguments via 

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

1133 

1134 """ # noqa 

1135 

1136 _storage_format = ( 

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

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

1139 ) 

1140 

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

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

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

1144 if truncate_microseconds: 

1145 assert "storage_format" not in kwargs, ( 

1146 "You can specify only " 

1147 "one of truncate_microseconds or storage_format." 

1148 ) 

1149 assert "regexp" not in kwargs, ( 

1150 "You can specify only one of " 

1151 "truncate_microseconds or regexp." 

1152 ) 

1153 self._storage_format = ( 

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

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

1156 ) 

1157 

1158 def bind_processor(self, dialect): 

1159 datetime_datetime = datetime.datetime 

1160 datetime_date = datetime.date 

1161 format_ = self._storage_format 

1162 

1163 def process(value): 

1164 if value is None: 

1165 return None 

1166 elif isinstance(value, datetime_datetime): 

1167 return format_ % { 

1168 "year": value.year, 

1169 "month": value.month, 

1170 "day": value.day, 

1171 "hour": value.hour, 

1172 "minute": value.minute, 

1173 "second": value.second, 

1174 "microsecond": value.microsecond, 

1175 } 

1176 elif isinstance(value, datetime_date): 

1177 return format_ % { 

1178 "year": value.year, 

1179 "month": value.month, 

1180 "day": value.day, 

1181 "hour": 0, 

1182 "minute": 0, 

1183 "second": 0, 

1184 "microsecond": 0, 

1185 } 

1186 else: 

1187 raise TypeError( 

1188 "SQLite DateTime type only accepts Python " 

1189 "datetime and date objects as input." 

1190 ) 

1191 

1192 return process 

1193 

1194 def result_processor(self, dialect, coltype): 

1195 if self._reg: 

1196 return processors.str_to_datetime_processor_factory( 

1197 self._reg, datetime.datetime 

1198 ) 

1199 else: 

1200 return processors.str_to_datetime 

1201 

1202 

1203class DATE(_DateTimeMixin, sqltypes.Date): 

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

1205 

1206 The default string storage format is:: 

1207 

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

1209 

1210 e.g.: 

1211 

1212 .. sourcecode:: text 

1213 

1214 2011-03-15 

1215 

1216 The incoming storage format is by default parsed using the 

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

1218 

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

1220 date string parsing. 

1221 

1222 

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

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

1225 

1226 import re 

1227 from sqlalchemy.dialects.sqlite import DATE 

1228 

1229 d = DATE( 

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

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

1232 ) 

1233 

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

1235 dict with keys year, month, and day. 

1236 

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

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

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

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

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

1242 constructor is called with positional arguments via 

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

1244 

1245 """ 

1246 

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

1248 

1249 def bind_processor(self, dialect): 

1250 datetime_date = datetime.date 

1251 format_ = self._storage_format 

1252 

1253 def process(value): 

1254 if value is None: 

1255 return None 

1256 elif isinstance(value, datetime_date): 

1257 return format_ % { 

1258 "year": value.year, 

1259 "month": value.month, 

1260 "day": value.day, 

1261 } 

1262 else: 

1263 raise TypeError( 

1264 "SQLite Date type only accepts Python " 

1265 "date objects as input." 

1266 ) 

1267 

1268 return process 

1269 

1270 def result_processor(self, dialect, coltype): 

1271 if self._reg: 

1272 return processors.str_to_datetime_processor_factory( 

1273 self._reg, datetime.date 

1274 ) 

1275 else: 

1276 return processors.str_to_date