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

1# dialects/sqlite/base.py 

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

3# <see AUTHORS file> 

4# 

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

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

7# mypy: ignore-errors 

8 

9 

10r''' 

11.. dialect:: sqlite 

12 :name: SQLite 

13 :normal_support: 3.12+ 

14 :best_effort: 3.7.16+ 

15 

16.. _sqlite_datetime: 

17 

18Date and Time Types 

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

20 

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

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

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

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

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

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

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

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

29these functions so historical dates are fully supported. 

30 

31Ensuring Text affinity 

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

33 

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

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

36applied to these types. When the 

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

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

39so that the column continues to have textual affinity. 

40 

41.. seealso:: 

42 

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

44 in the SQLite documentation 

45 

46.. _sqlite_autoincrement: 

47 

48SQLite Auto Incrementing Behavior 

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

50 

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

52 

53Key concepts: 

54 

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

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

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

58 

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

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

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

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

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

64 

65Using the AUTOINCREMENT Keyword 

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

67 

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

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

70construct:: 

71 

72 Table( 

73 "sometable", 

74 metadata, 

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

76 sqlite_autoincrement=True, 

77 ) 

78 

79Allowing autoincrement behavior SQLAlchemy types other than Integer/INTEGER 

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

81 

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

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

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

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

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

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

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

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

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

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

92 

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

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

95 

96 table = Table( 

97 "my_table", 

98 metadata, 

99 Column( 

100 "id", 

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

102 primary_key=True, 

103 ), 

104 ) 

105 

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

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

108 

109 from sqlalchemy import BigInteger 

110 from sqlalchemy.ext.compiler import compiles 

111 

112 

113 class SLBigInteger(BigInteger): 

114 pass 

115 

116 

117 @compiles(SLBigInteger, "sqlite") 

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

119 return "INTEGER" 

120 

121 

122 @compiles(SLBigInteger) 

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

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

125 

126 

127 table = Table( 

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

129 ) 

130 

131.. seealso:: 

132 

133 :meth:`.TypeEngine.with_variant` 

134 

135 :ref:`sqlalchemy.ext.compiler_toplevel` 

136 

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

138 

139.. _sqlite_transactions: 

140 

141Transactions with SQLite and the sqlite3 driver 

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

143 

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

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

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

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

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

149constructing a SQLAlchemy application that uses SQLite. 

150 

151Legacy Transaction Mode with the sqlite3 driver 

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

153 

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

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

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

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

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

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

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

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

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

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

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

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

166reasons. Statements such as SAVEPOINT are also skipped. 

167 

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

169mode of operation is referred to as 

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

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

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

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

174 

175The implications of legacy transaction mode include: 

176 

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

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

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

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

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

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

183 case. 

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

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

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

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

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

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

190 failing one of the consistency guarantees of SERIALIZABLE. 

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

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

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

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

195 savepoint. 

196 

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

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

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

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

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

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

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

204Python version (3.16 as of this writing). 

205 

206.. _sqlite_enabling_transactions: 

207 

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

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

210 

211Current SQLAlchemy support allows either for setting the 

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

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

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

215BEGIN. 

216 

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

218 

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

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

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

222 

223 from sqlalchemy import create_engine 

224 

225 engine = create_engine( 

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

227 ) 

228 

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

230 

231 from sqlalchemy.ext.asyncio import create_async_engine 

232 

233 engine = create_async_engine( 

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

235 ) 

236 

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

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

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

240 

241 from sqlalchemy import create_engine, event 

242 

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

244 

245 

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

247 def do_connect(dbapi_connection, connection_record): 

248 # enable autocommit=False mode 

249 dbapi_connection.autocommit = False 

250 

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

252 

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

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

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

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

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

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

259 

260 

261 from sqlalchemy import create_engine, event 

262 

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

264 

265 

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

267 def do_connect(dbapi_connection, connection_record): 

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

269 dbapi_connection.isolation_level = None 

270 

271 

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

273 def do_begin(conn): 

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

275 conn.exec_driver_sql("BEGIN") 

276 

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

278 as in the example below:: 

279 

280 from sqlalchemy import create_engine, event 

281 from sqlalchemy.ext.asyncio import create_async_engine 

282 

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

284 

285 

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

287 def do_connect(dbapi_connection, connection_record): 

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

289 dbapi_connection.isolation_level = None 

290 

291 

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

293 def do_begin(conn): 

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

295 conn.exec_driver_sql("BEGIN") 

296 

297.. _sqlite_isolation_level: 

298 

299Using SQLAlchemy's Driver Level AUTOCOMMIT Feature with SQLite 

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

301 

302SQLAlchemy has a comprehensive database isolation feature with optional 

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

304 

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

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

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

308section at :ref:`sqlite_enabling_transactions`. 

309 

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

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

312parameter to "AUTOCOMMIT":: 

313 

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

315 

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

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

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

319 

320Additional Reading for SQLite / sqlite3 transaction control 

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

322 

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

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

325 

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

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

328 as the legacy isolation_level attribute. 

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

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

331 

332 

333INSERT/UPDATE/DELETE...RETURNING 

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

335 

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

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

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

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

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

341cases for its better performance. 

342 

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

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

345 

346 # INSERT..RETURNING 

347 result = connection.execute( 

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

349 ) 

350 print(result.all()) 

351 

352 # UPDATE..RETURNING 

353 result = connection.execute( 

354 table.update() 

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

356 .values(name="bar") 

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

358 ) 

359 print(result.all()) 

360 

361 # DELETE..RETURNING 

362 result = connection.execute( 

363 table.delete() 

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

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

366 ) 

367 print(result.all()) 

368 

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

370 

371 

372.. _sqlite_foreign_keys: 

373 

374Foreign Key Support 

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

376 

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

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

379table. 

380 

381Constraint checking on SQLite has three prerequisites: 

382 

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

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

385 or SQLITE_OMIT_TRIGGER symbols enabled. 

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

387 connections before use -- including the initial call to 

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

389 

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

391new connections through the usage of events:: 

392 

393 from sqlalchemy.engine import Engine 

394 from sqlalchemy import event 

395 

396 

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

398 def set_sqlite_pragma(dbapi_connection, connection_record): 

399 # the sqlite3 driver will not set PRAGMA foreign_keys 

400 # if autocommit=False; set to True temporarily 

401 ac = dbapi_connection.autocommit 

402 dbapi_connection.autocommit = True 

403 

404 cursor = dbapi_connection.cursor() 

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

406 cursor.close() 

407 

408 # restore previous autocommit setting 

409 dbapi_connection.autocommit = ac 

410 

411.. warning:: 

412 

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

414 to emit CREATE or DROP statements for tables that contain 

415 mutually-dependent foreign key constraints; 

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

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

418 no support. 

419 

420.. seealso:: 

421 

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

423 - on the SQLite web site. 

424 

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

426 

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

428 mutually-dependent foreign key constraints. 

429 

430.. _sqlite_on_conflict_ddl: 

431 

432ON CONFLICT support for constraints 

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

434 

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

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

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

438 

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

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

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

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

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

444specified with a string conflict resolution algorithm within the 

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

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

447there 

448are individual parameters ``sqlite_on_conflict_not_null``, 

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

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

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

452 

453.. seealso:: 

454 

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

456 documentation 

457 

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

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

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

461that specifies the IGNORE algorithm:: 

462 

463 some_table = Table( 

464 "some_table", 

465 metadata, 

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

467 Column("data", Integer), 

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

469 ) 

470 

471The above renders CREATE TABLE DDL as: 

472 

473.. sourcecode:: sql 

474 

475 CREATE TABLE some_table ( 

476 id INTEGER NOT NULL, 

477 data INTEGER, 

478 PRIMARY KEY (id), 

479 UNIQUE (id, data) ON CONFLICT IGNORE 

480 ) 

481 

482 

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

484flag to add a UNIQUE constraint 

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

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

487UNIQUE constraint in the DDL:: 

488 

489 some_table = Table( 

490 "some_table", 

491 metadata, 

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

493 Column( 

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

495 ), 

496 ) 

497 

498rendering: 

499 

500.. sourcecode:: sql 

501 

502 CREATE TABLE some_table ( 

503 id INTEGER NOT NULL, 

504 data INTEGER, 

505 PRIMARY KEY (id), 

506 UNIQUE (data) ON CONFLICT IGNORE 

507 ) 

508 

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

510``sqlite_on_conflict_not_null`` is used:: 

511 

512 some_table = Table( 

513 "some_table", 

514 metadata, 

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

516 Column( 

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

518 ), 

519 ) 

520 

521this renders the column inline ON CONFLICT phrase: 

522 

523.. sourcecode:: sql 

524 

525 CREATE TABLE some_table ( 

526 id INTEGER NOT NULL, 

527 data INTEGER NOT NULL ON CONFLICT FAIL, 

528 PRIMARY KEY (id) 

529 ) 

530 

531 

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

533 

534 some_table = Table( 

535 "some_table", 

536 metadata, 

537 Column( 

538 "id", 

539 Integer, 

540 primary_key=True, 

541 sqlite_on_conflict_primary_key="FAIL", 

542 ), 

543 ) 

544 

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

546resolution algorithm is applied to the constraint itself: 

547 

548.. sourcecode:: sql 

549 

550 CREATE TABLE some_table ( 

551 id INTEGER NOT NULL, 

552 PRIMARY KEY (id) ON CONFLICT FAIL 

553 ) 

554 

555.. _sqlite_on_conflict_insert: 

556 

557INSERT...ON CONFLICT (Upsert) 

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

559 

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

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

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

563 

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

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

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

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

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

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

570to silently skip this row. 

571 

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

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

574columns and conditions that comprise the indexes. 

575 

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

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

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

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

580 

581.. sourcecode:: pycon+sql 

582 

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

584 

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

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

587 ... ) 

588 

589 >>> do_update_stmt = insert_stmt.on_conflict_do_update( 

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

591 ... ) 

592 

593 >>> print(do_update_stmt) 

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

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

596 

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

598 

599 >>> print(do_nothing_stmt) 

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

601 ON CONFLICT (id) DO NOTHING 

602 

603.. versionadded:: 1.4 

604 

605.. seealso:: 

606 

607 `Upsert 

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

609 - in the SQLite documentation. 

610 

611 

612Specifying the Target 

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

614 

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

616 

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

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

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

620 or unique constraint. 

621 

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

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

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

625 

626 .. sourcecode:: pycon+sql 

627 

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

629 

630 >>> do_update_stmt = stmt.on_conflict_do_update( 

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

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

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

634 ... ) 

635 

636 >>> print(do_update_stmt) 

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

638 ON CONFLICT (user_email) 

639 WHERE user_email LIKE '%@gmail.com' 

640 DO UPDATE SET data = excluded.data 

641 

642The SET Clause 

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

644 

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

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

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

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

649parameter accepts a dictionary which consists of direct values 

650for UPDATE: 

651 

652.. sourcecode:: pycon+sql 

653 

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

655 

656 >>> do_update_stmt = stmt.on_conflict_do_update( 

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

658 ... ) 

659 

660 >>> print(do_update_stmt) 

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

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

663 

664.. warning:: 

665 

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

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

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

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

670 they are manually specified in the 

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

672 

673Updating using the Excluded INSERT Values 

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

675 

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

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

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

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

680would have been inserted had the constraint not failed: 

681 

682.. sourcecode:: pycon+sql 

683 

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

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

686 ... ) 

687 

688 >>> do_update_stmt = stmt.on_conflict_do_update( 

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

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

691 ... ) 

692 

693 >>> print(do_update_stmt) 

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

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

696 

697Additional WHERE Criteria 

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

699 

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

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

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

703 

704.. sourcecode:: pycon+sql 

705 

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

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

708 ... ) 

709 

710 >>> on_update_stmt = stmt.on_conflict_do_update( 

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

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

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

714 ... ) 

715 >>> print(on_update_stmt) 

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

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

718 WHERE my_table.status = ? 

719 

720 

721Skipping Rows with DO NOTHING 

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

723 

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

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

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

727 

728.. sourcecode:: pycon+sql 

729 

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

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

732 >>> print(stmt) 

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

734 

735 

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

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

738occurs: 

739 

740.. sourcecode:: pycon+sql 

741 

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

743 >>> stmt = stmt.on_conflict_do_nothing() 

744 >>> print(stmt) 

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

746 

747.. _sqlite_on_conflict_multiple: 

748 

749Specifying Multiple ON CONFLICT Clauses 

750^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

751 

752SQLite accepts more than one ``ON CONFLICT`` clause within a single INSERT 

753statement. The :meth:`_sqlite.Insert.on_conflict_do_update` and 

754:meth:`_sqlite.Insert.on_conflict_do_nothing` methods may therefore be 

755invoked repeatedly against the same construct, and may be combined with each 

756other; each clause renders in the order in which it was established: 

757 

758.. sourcecode:: pycon+sql 

759 

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

761 >>> stmt = stmt.on_conflict_do_update( 

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

763 ... ).on_conflict_do_nothing(index_elements=["data"]) 

764 >>> print(stmt) 

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

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

767 ON CONFLICT (data) DO NOTHING 

768 

769SQLite tests the clauses in the order given, and applies at most one of them 

770to any particular row, that being the first clause whose conflict target 

771matches the constraint that was violated. 

772 

773Only the last ``ON CONFLICT`` clause of a statement may omit its conflict 

774target, in which case it fires for any unique violation not already captured 

775by a preceding clause. A :meth:`_sqlite.Insert.on_conflict_do_nothing` call 

776that omits 

777:paramref:`_sqlite.Insert.on_conflict_do_nothing.index_elements` must 

778therefore be the last clause established, else 

779:class:`.InvalidRequestError` is raised: 

780 

781.. sourcecode:: pycon+sql 

782 

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

784 >>> stmt = stmt.on_conflict_do_update( 

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

786 ... ).on_conflict_do_nothing() 

787 >>> print(stmt) 

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

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

790 ON CONFLICT DO NOTHING 

791 

792.. versionadded:: 2.1 Multiple ``ON CONFLICT`` clauses may be established 

793 on a single :class:`_sqlite.Insert` construct. 

794 

795.. _sqlite_type_reflection: 

796 

797Type Reflection 

798--------------- 

799 

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

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

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

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

804pattern for the type. 

805 

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

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

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

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

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

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

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

813 

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

815name match for the following types: 

816 

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

818:class:`_types.BOOLEAN`, :class:`_types.BOOLEAN`, 

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

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

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

822:class:`_types.INTEGER`, :class:`_types.INTEGER`, 

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

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

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

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

827:class:`_types.NCHAR` 

828 

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

830lookup is used instead: 

831 

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

833 string ``INT`` 

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

835 string ``CHAR``, ``CLOB`` or ``TEXT`` 

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

837 string ``BLOB`` 

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

839 ``REAL``, ``FLOA`` or ``DOUB``. 

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

841 

842.. _sqlite_partial_index: 

843 

844Partial Indexes 

845--------------- 

846 

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

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

849 

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

851 idx = Index( 

852 "test_idx1", 

853 tbl.c.data, 

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

855 ) 

856 

857The index will be rendered at create time as: 

858 

859.. sourcecode:: sql 

860 

861 CREATE INDEX test_idx1 ON testtbl (data) 

862 WHERE data > 5 AND data < 10 

863 

864.. _sqlite_dotted_column_names: 

865 

866Dotted Column Names 

867------------------- 

868 

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

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

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

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

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

874 

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

876 

877 import sqlite3 

878 

879 assert sqlite3.sqlite_version_info < ( 

880 3, 

881 10, 

882 0, 

883 ), "bug is fixed in this version" 

884 

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

886 cursor = conn.cursor() 

887 

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

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

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

891 

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

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

894 

895 cursor.execute(""" 

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

897 union 

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

899 """) 

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

901 c[0] for c in cursor.description 

902 ] 

903 

904The second assertion fails: 

905 

906.. sourcecode:: text 

907 

908 Traceback (most recent call last): 

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

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

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

912 

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

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

915when the UNION is not present. 

916 

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

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

919to filter these out:: 

920 

921 

922 from sqlalchemy import create_engine 

923 

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

925 conn = eng.connect() 

926 

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

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

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

930 

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

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

933 

934 result = conn.exec_driver_sql(""" 

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

936 union 

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

938 """) 

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

940 

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

942names are still addressable*:: 

943 

944 >>> row = result.first() 

945 >>> row["a"] 

946 1 

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

948 1 

949 >>> row["b"] 

950 1 

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

952 1 

953 

954Therefore, the workaround applied by SQLAlchemy only impacts 

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

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

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

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

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

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

961 

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

963 """ 

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

965 union 

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

967 """ 

968 ) 

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

970 

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

972 

973 engine = create_engine( 

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

975 ) 

976 

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

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

979 

980SQLite-specific table options 

981----------------------------- 

982 

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

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

985 

986* ``WITHOUT ROWID``:: 

987 

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

989 

990* 

991 ``STRICT``:: 

992 

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

994 

995 .. versionadded:: 2.0.37 

996 

997Both options are also reflected, so that a :class:`_schema.Table` which is 

998autoloaded from a database that was created using either keyword will render 

999that keyword again when the table is recreated. The reflected values are 

1000also available directly from 

1001:meth:`_engine.Inspector.get_table_options`. 

1002 

1003.. versionadded:: 2.0.53 Added reflection support for the ``WITHOUT ROWID`` 

1004 and ``STRICT`` table options. 

1005 

1006.. seealso:: 

1007 

1008 `SQLite CREATE TABLE options 

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

1010 

1011.. _sqlite_include_internal: 

1012 

1013Reflecting internal schema tables 

1014---------------------------------- 

1015 

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

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

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

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

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

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

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

1023:meth:`.Inspector.get_table_names`. 

1024 

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

1026 Previously, these tables were not ignored by SQLAlchemy reflection 

1027 methods. 

1028 

1029.. note:: 

1030 

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

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

1033 

1034.. seealso:: 

1035 

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

1037 documentation. 

1038 

1039''' # noqa 

1040 

1041from __future__ import annotations 

1042 

1043import datetime 

1044import numbers 

1045import re 

1046from typing import Any 

1047from typing import Callable 

1048from typing import Optional 

1049from typing import TYPE_CHECKING 

1050 

1051from .json import JSON 

1052from .json import JSONB 

1053from .json import JSONIndexType 

1054from .json import JSONPathType 

1055from ... import exc 

1056from ... import schema as sa_schema 

1057from ... import sql 

1058from ... import text 

1059from ... import types as sqltypes 

1060from ... import util 

1061from ...engine import default 

1062from ...engine import processors 

1063from ...engine import reflection 

1064from ...engine.reflection import ReflectionDefaults 

1065from ...sql import coercions 

1066from ...sql import compiler 

1067from ...sql import ddl as sa_ddl 

1068from ...sql import elements 

1069from ...sql import roles 

1070from ...sql import schema 

1071from ...types import BLOB # noqa 

1072from ...types import BOOLEAN # noqa 

1073from ...types import CHAR # noqa 

1074from ...types import DECIMAL # noqa 

1075from ...types import FLOAT # noqa 

1076from ...types import INTEGER # noqa 

1077from ...types import NUMERIC # noqa 

1078from ...types import REAL # noqa 

1079from ...types import SMALLINT # noqa 

1080from ...types import TEXT # noqa 

1081from ...types import TIMESTAMP # noqa 

1082from ...types import VARCHAR # noqa 

1083 

1084if TYPE_CHECKING: 

1085 from ...engine.interfaces import DBAPIConnection 

1086 from ...engine.interfaces import Dialect 

1087 from ...engine.interfaces import IsolationLevel 

1088 from ...sql.sqltypes import _JSON_VALUE 

1089 from ...sql.type_api import _BindProcessorType 

1090 from ...sql.type_api import _ResultProcessorType 

1091 

1092 

1093class _SQliteJson(JSON): 

1094 def result_processor(self, dialect, coltype): 

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

1096 

1097 def process(value): 

1098 try: 

1099 return default_processor(value) 

1100 except TypeError: 

1101 if isinstance(value, numbers.Number): 

1102 return value 

1103 else: 

1104 raise 

1105 

1106 return process 

1107 

1108 

1109class _DateTimeMixin: 

1110 _reg = None 

1111 _storage_format = None 

1112 

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

1114 super().__init__(**kw) 

1115 if regexp is not None: 

1116 self._reg = re.compile(regexp) 

1117 if storage_format is not None: 

1118 self._storage_format = storage_format 

1119 

1120 @property 

1121 def format_is_text_affinity(self): 

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

1123 a TEXT affinity. 

1124 

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

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

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

1128 TIME_CHAR. 

1129 

1130 """ 

1131 spec = self._storage_format % { 

1132 "year": 0, 

1133 "month": 0, 

1134 "day": 0, 

1135 "hour": 0, 

1136 "minute": 0, 

1137 "second": 0, 

1138 "microsecond": 0, 

1139 } 

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

1141 

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

1143 if issubclass(cls, _DateTimeMixin): 

1144 if self._storage_format: 

1145 kw["storage_format"] = self._storage_format 

1146 if self._reg: 

1147 kw["regexp"] = self._reg 

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

1149 

1150 def literal_processor(self, dialect): 

1151 bp = self.bind_processor(dialect) 

1152 

1153 def process(value): 

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

1155 

1156 return process 

1157 

1158 

1159class DATETIME(_DateTimeMixin, sqltypes.DateTime): 

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

1161 

1162 The default string storage format is:: 

1163 

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

1165 

1166 e.g.: 

1167 

1168 .. sourcecode:: text 

1169 

1170 2021-03-15 12:05:57.105542 

1171 

1172 The incoming storage format is by default parsed using the 

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

1174 

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

1176 datetime string parsing. 

1177 

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

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

1180 

1181 import re 

1182 from sqlalchemy.dialects.sqlite import DATETIME 

1183 

1184 dt = DATETIME( 

1185 storage_format=( 

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

1187 ), 

1188 regexp=r"(\d+)/(\d+)/(\d+) (\d+):(\d+):(\d+)", 

1189 ) 

1190 

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

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

1193 or ``regexp``. 

1194 

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

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

1197 

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

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

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

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

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

1203 is called with positional arguments via 

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

1205 

1206 """ # noqa 

1207 

1208 _storage_format = ( 

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

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

1211 ) 

1212 

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

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

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

1216 if truncate_microseconds: 

1217 assert "storage_format" not in kwargs, ( 

1218 "You can specify only " 

1219 "one of truncate_microseconds or storage_format." 

1220 ) 

1221 assert "regexp" not in kwargs, ( 

1222 "You can specify only one of " 

1223 "truncate_microseconds or regexp." 

1224 ) 

1225 self._storage_format = ( 

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

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

1228 ) 

1229 

1230 def bind_processor( 

1231 self, dialect: Dialect 

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

1233 datetime_datetime = datetime.datetime 

1234 datetime_date = datetime.date 

1235 format_ = self._storage_format 

1236 

1237 def process(value): 

1238 if value is None: 

1239 return None 

1240 elif isinstance(value, datetime_datetime): 

1241 return format_ % { 

1242 "year": value.year, 

1243 "month": value.month, 

1244 "day": value.day, 

1245 "hour": value.hour, 

1246 "minute": value.minute, 

1247 "second": value.second, 

1248 "microsecond": value.microsecond, 

1249 } 

1250 elif isinstance(value, datetime_date): 

1251 return format_ % { 

1252 "year": value.year, 

1253 "month": value.month, 

1254 "day": value.day, 

1255 "hour": 0, 

1256 "minute": 0, 

1257 "second": 0, 

1258 "microsecond": 0, 

1259 } 

1260 else: 

1261 raise TypeError( 

1262 "SQLite DateTime type only accepts Python " 

1263 "datetime and date objects as input." 

1264 ) 

1265 

1266 return process 

1267 

1268 def result_processor( 

1269 self, dialect: Dialect, coltype: object 

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

1271 if self._reg: 

1272 return processors.str_to_datetime_processor_factory( 

1273 self._reg, datetime.datetime 

1274 ) 

1275 else: 

1276 return processors.str_to_datetime 

1277 

1278