Debian Codegraph @

Lib/test/test_sqlite3/test_dbapi.py

raw
1 # pysqlite2/test/dbapi.py: tests for DB-API compliance
2 #
3 # Copyright (C) 2004-2010 Gerhard Häring <[email protected]>
4 #
5 # This file is part of pysqlite.
6 #
7 # This software is provided 'as-is', without any express or implied
8 # warranty. In no event will the authors be held liable for any damages
9 # arising from the use of this software.
10 #
11 # Permission is granted to anyone to use this software for any purpose,
12 # including commercial applications, and to alter it and redistribute it
13 # freely, subject to the following restrictions:
14 #
15 # 1. The origin of this software must not be misrepresented; you must not
16 # claim that you wrote the original software. If you use this software
17 # in a product, an acknowledgment in the product documentation would be
18 # appreciated but is not required.
19 # 2. Altered source versions must be plainly marked as such, and must not be
20 # misrepresented as being the original software.
21 # 3. This notice may not be removed or altered from any source distribution.
22
23 import contextlib
24 import functools
25 import os
26 import sqlite3 as sqlite
27 import subprocess
28 import sys
29 import threading
30 import unittest
31 import urllib.parse
32 import warnings
33
34 from test.support import (
35 SHORT_TIMEOUT, check_disallow_instantiation, requires_subprocess
36 )
37 from test.support import gc_collect
38 from test.support import threading_helper, import_helper
39 from os import SEEK_SET, SEEK_CUR, SEEK_END
40 from test.support.os_helper import TESTFN, TESTFN_UNDECODABLE, unlink, temp_dir, FakePath
41
42 from .util import memory_database, cx_limit
43 from .util import MemoryDatabaseMixin
44
45
46 class ModuleTests(unittest.TestCase):
47 def test_api_level(self):
48 self.assertEqual(sqlite.apilevel, "2.0",
49 "apilevel is %s, should be 2.0" % sqlite.apilevel)
50
51 def test_thread_safety(self):
52 self.assertIn(sqlite.threadsafety, {0, 1, 3},
53 "threadsafety is %d, should be 0, 1 or 3" %
54 sqlite.threadsafety)
55
56 def test_param_style(self):
57 self.assertEqual(sqlite.paramstyle, "qmark",
58 "paramstyle is '%s', should be 'qmark'" %
59 sqlite.paramstyle)
60
61 def test_warning(self):
62 self.assertIsSubclass(sqlite.Warning, Exception)
63
64 def test_error(self):
65 self.assertIsSubclass(sqlite.Error, Exception)
66
67 def test_interface_error(self):
68 self.assertIsSubclass(sqlite.InterfaceError, sqlite.Error)
69
70 def test_database_error(self):
71 self.assertIsSubclass(sqlite.DatabaseError, sqlite.Error)
72
73 def test_data_error(self):
74 self.assertIsSubclass(sqlite.DataError, sqlite.DatabaseError)
75
76 def test_operational_error(self):
77 self.assertIsSubclass(sqlite.OperationalError, sqlite.DatabaseError)
78
79 def test_integrity_error(self):
80 self.assertIsSubclass(sqlite.IntegrityError, sqlite.DatabaseError)
81
82 def test_internal_error(self):
83 self.assertIsSubclass(sqlite.InternalError, sqlite.DatabaseError)
84
85 def test_programming_error(self):
86 self.assertIsSubclass(sqlite.ProgrammingError, sqlite.DatabaseError)
87
88 def test_not_supported_error(self):
89 self.assertIsSubclass(sqlite.NotSupportedError, sqlite.DatabaseError)
90
91 def test_module_constants(self):
92 consts = [
93 "SQLITE_ABORT",
94 "SQLITE_ALTER_TABLE",
95 "SQLITE_ANALYZE",
96 "SQLITE_ATTACH",
97 "SQLITE_AUTH",
98 "SQLITE_BUSY",
99 "SQLITE_CANTOPEN",
100 "SQLITE_CONSTRAINT",
101 "SQLITE_CORRUPT",
102 "SQLITE_CREATE_INDEX",
103 "SQLITE_CREATE_TABLE",
104 "SQLITE_CREATE_TEMP_INDEX",
105 "SQLITE_CREATE_TEMP_TABLE",
106 "SQLITE_CREATE_TEMP_TRIGGER",
107 "SQLITE_CREATE_TEMP_VIEW",
108 "SQLITE_CREATE_TRIGGER",
109 "SQLITE_CREATE_VIEW",
110 "SQLITE_CREATE_VTABLE",
111 "SQLITE_DELETE",
112 "SQLITE_DENY",
113 "SQLITE_DETACH",
114 "SQLITE_DONE",
115 "SQLITE_DROP_INDEX",
116 "SQLITE_DROP_TABLE",
117 "SQLITE_DROP_TEMP_INDEX",
118 "SQLITE_DROP_TEMP_TABLE",
119 "SQLITE_DROP_TEMP_TRIGGER",
120 "SQLITE_DROP_TEMP_VIEW",
121 "SQLITE_DROP_TRIGGER",
122 "SQLITE_DROP_VIEW",
123 "SQLITE_DROP_VTABLE",
124 "SQLITE_EMPTY",
125 "SQLITE_ERROR",
126 "SQLITE_FORMAT",
127 "SQLITE_FULL",
128 "SQLITE_FUNCTION",
129 "SQLITE_IGNORE",
130 "SQLITE_INSERT",
131 "SQLITE_INTERNAL",
132 "SQLITE_INTERRUPT",
133 "SQLITE_IOERR",
134 "SQLITE_LIMIT_WORKER_THREADS",
135 "SQLITE_LOCKED",
136 "SQLITE_MISMATCH",
137 "SQLITE_MISUSE",
138 "SQLITE_NOLFS",
139 "SQLITE_NOMEM",
140 "SQLITE_NOTADB",
141 "SQLITE_NOTFOUND",
142 "SQLITE_NOTICE",
143 "SQLITE_OK",
144 "SQLITE_PERM",
145 "SQLITE_PRAGMA",
146 "SQLITE_PROTOCOL",
147 "SQLITE_RANGE",
148 "SQLITE_READ",
149 "SQLITE_READONLY",
150 "SQLITE_RECURSIVE",
151 "SQLITE_REINDEX",
152 "SQLITE_ROW",
153 "SQLITE_SAVEPOINT",
154 "SQLITE_SCHEMA",
155 "SQLITE_SELECT",
156 "SQLITE_TOOBIG",
157 "SQLITE_TRANSACTION",
158 "SQLITE_UPDATE",
159 "SQLITE_WARNING",
160 # Run-time limit categories
161 "SQLITE_LIMIT_LENGTH",
162 "SQLITE_LIMIT_SQL_LENGTH",
163 "SQLITE_LIMIT_COLUMN",
164 "SQLITE_LIMIT_EXPR_DEPTH",
165 "SQLITE_LIMIT_COMPOUND_SELECT",
166 "SQLITE_LIMIT_VDBE_OP",
167 "SQLITE_LIMIT_FUNCTION_ARG",
168 "SQLITE_LIMIT_ATTACHED",
169 "SQLITE_LIMIT_LIKE_PATTERN_LENGTH",
170 "SQLITE_LIMIT_VARIABLE_NUMBER",
171 "SQLITE_LIMIT_TRIGGER_DEPTH",
172 ]
173 consts += ["PARSE_DECLTYPES", "PARSE_COLNAMES"]
174 # Extended result codes
175 consts += [
176 "SQLITE_ABORT_ROLLBACK",
177 "SQLITE_AUTH_USER",
178 "SQLITE_BUSY_RECOVERY",
179 "SQLITE_BUSY_SNAPSHOT",
180 "SQLITE_CANTOPEN_CONVPATH",
181 "SQLITE_CANTOPEN_FULLPATH",
182 "SQLITE_CANTOPEN_ISDIR",
183 "SQLITE_CANTOPEN_NOTEMPDIR",
184 "SQLITE_CONSTRAINT_CHECK",
185 "SQLITE_CONSTRAINT_COMMITHOOK",
186 "SQLITE_CONSTRAINT_FOREIGNKEY",
187 "SQLITE_CONSTRAINT_FUNCTION",
188 "SQLITE_CONSTRAINT_NOTNULL",
189 "SQLITE_CONSTRAINT_PRIMARYKEY",
190 "SQLITE_CONSTRAINT_ROWID",
191 "SQLITE_CONSTRAINT_TRIGGER",
192 "SQLITE_CONSTRAINT_UNIQUE",
193 "SQLITE_CONSTRAINT_VTAB",
194 "SQLITE_CORRUPT_VTAB",
195 "SQLITE_IOERR_ACCESS",
196 "SQLITE_IOERR_AUTH",
197 "SQLITE_IOERR_BLOCKED",
198 "SQLITE_IOERR_CHECKRESERVEDLOCK",
199 "SQLITE_IOERR_CLOSE",
200 "SQLITE_IOERR_CONVPATH",
201 "SQLITE_IOERR_DELETE",
202 "SQLITE_IOERR_DELETE_NOENT",
203 "SQLITE_IOERR_DIR_CLOSE",
204 "SQLITE_IOERR_DIR_FSYNC",
205 "SQLITE_IOERR_FSTAT",
206 "SQLITE_IOERR_FSYNC",
207 "SQLITE_IOERR_GETTEMPPATH",
208 "SQLITE_IOERR_LOCK",
209 "SQLITE_IOERR_MMAP",
210 "SQLITE_IOERR_NOMEM",
211 "SQLITE_IOERR_RDLOCK",
212 "SQLITE_IOERR_READ",
213 "SQLITE_IOERR_SEEK",
214 "SQLITE_IOERR_SHMLOCK",
215 "SQLITE_IOERR_SHMMAP",
216 "SQLITE_IOERR_SHMOPEN",
217 "SQLITE_IOERR_SHMSIZE",
218 "SQLITE_IOERR_SHORT_READ",
219 "SQLITE_IOERR_TRUNCATE",
220 "SQLITE_IOERR_UNLOCK",
221 "SQLITE_IOERR_VNODE",
222 "SQLITE_IOERR_WRITE",
223 "SQLITE_LOCKED_SHAREDCACHE",
224 "SQLITE_NOTICE_RECOVER_ROLLBACK",
225 "SQLITE_NOTICE_RECOVER_WAL",
226 "SQLITE_OK_LOAD_PERMANENTLY",
227 "SQLITE_READONLY_CANTLOCK",
228 "SQLITE_READONLY_DBMOVED",
229 "SQLITE_READONLY_RECOVERY",
230 "SQLITE_READONLY_ROLLBACK",
231 "SQLITE_WARNING_AUTOINDEX",
232 ]
233 if sqlite.sqlite_version_info >= (3, 21, 0):
234 consts += [
235 "SQLITE_IOERR_BEGIN_ATOMIC",
236 "SQLITE_IOERR_COMMIT_ATOMIC",
237 "SQLITE_IOERR_ROLLBACK_ATOMIC",
238 ]
239 if sqlite.sqlite_version_info >= (3, 22, 0):
240 consts += [
241 "SQLITE_ERROR_MISSING_COLLSEQ",
242 "SQLITE_ERROR_RETRY",
243 "SQLITE_READONLY_CANTINIT",
244 "SQLITE_READONLY_DIRECTORY",
245 ]
246 if sqlite.sqlite_version_info >= (3, 24, 0):
247 consts += ["SQLITE_CORRUPT_SEQUENCE", "SQLITE_LOCKED_VTAB"]
248 if sqlite.sqlite_version_info >= (3, 25, 0):
249 consts += ["SQLITE_CANTOPEN_DIRTYWAL", "SQLITE_ERROR_SNAPSHOT"]
250 if sqlite.sqlite_version_info >= (3, 31, 0):
251 consts += [
252 "SQLITE_CANTOPEN_SYMLINK",
253 "SQLITE_CONSTRAINT_PINNED",
254 "SQLITE_OK_SYMLINK",
255 ]
256 if sqlite.sqlite_version_info >= (3, 32, 0):
257 consts += [
258 "SQLITE_BUSY_TIMEOUT",
259 "SQLITE_CORRUPT_INDEX",
260 "SQLITE_IOERR_DATA",
261 ]
262 if sqlite.sqlite_version_info >= (3, 34, 0):
263 consts.append("SQLITE_IOERR_CORRUPTFS")
264 for const in consts:
265 with self.subTest(const=const):
266 self.assertHasAttr(sqlite, const)
267
268 def test_error_code_on_exception(self):
269 err_msg = "unable to open database file"
270 if sys.platform.startswith("win"):
271 err_code = sqlite.SQLITE_CANTOPEN_ISDIR
272 else:
273 err_code = sqlite.SQLITE_CANTOPEN
274
275 with temp_dir() as db:
276 with self.assertRaisesRegex(sqlite.Error, err_msg) as cm:
277 sqlite.connect(db)
278 e = cm.exception
279 self.assertEqual(e.sqlite_errorcode, err_code)
280 self.assertStartsWith(e.sqlite_errorname, "SQLITE_CANTOPEN")
281
282 def test_extended_error_code_on_exception(self):
283 with memory_database() as con:
284 with con:
285 con.execute("create table t(t integer check(t > 0))")
286 errmsg = "constraint failed"
287 with self.assertRaisesRegex(sqlite.IntegrityError, errmsg) as cm:
288 con.execute("insert into t values(-1)")
289 exc = cm.exception
290 self.assertEqual(exc.sqlite_errorcode,
291 sqlite.SQLITE_CONSTRAINT_CHECK)
292 self.assertEqual(exc.sqlite_errorname, "SQLITE_CONSTRAINT_CHECK")
293
294 def test_disallow_instantiation(self):
295 with memory_database() as cx:
296 check_disallow_instantiation(self, type(cx("select 1")))
297 check_disallow_instantiation(self, sqlite.Blob)
298
299 def test_complete_statement(self):
300 self.assertFalse(sqlite.complete_statement("select t"))
301 self.assertTrue(sqlite.complete_statement("create table t(t);"))
302
303
304 class ConnectionTests(unittest.TestCase):
305
306 def setUp(self):
307 self.cx = sqlite.connect(":memory:")
308 cu = self.cx.cursor()
309 cu.execute("create table test(id integer primary key, name text)")
310 cu.execute("insert into test(name) values (?)", ("foo",))
311 cu.close()
312
313 def tearDown(self):
314 self.cx.close()
315
316 def test_commit(self):
317 self.cx.commit()
318
319 def test_commit_after_no_changes(self):
320 """
321 A commit should also work when no changes were made to the database.
322 """
323 self.cx.commit()
324 self.cx.commit()
325
326 def test_rollback(self):
327 self.cx.rollback()
328
329 def test_rollback_after_no_changes(self):
330 """
331 A rollback should also work when no changes were made to the database.
332 """
333 self.cx.rollback()
334 self.cx.rollback()
335
336 def test_cursor(self):
337 cu = self.cx.cursor()
338
339 def test_failed_open(self):
340 YOU_CANNOT_OPEN_THIS = "/foo/bar/bla/23534/mydb.db"
341 with self.assertRaises(sqlite.OperationalError):
342 sqlite.connect(YOU_CANNOT_OPEN_THIS)
343
344 def test_close(self):
345 self.cx.close()
346
347 def test_use_after_close(self):
348 sql = "select 1"
349 cu = self.cx.cursor()
350 res = cu.execute(sql)
351 self.cx.close()
352 self.assertRaises(sqlite.ProgrammingError, res.fetchall)
353 self.assertRaises(sqlite.ProgrammingError, cu.execute, sql)
354 self.assertRaises(sqlite.ProgrammingError, cu.executemany, sql, [])
355 self.assertRaises(sqlite.ProgrammingError, cu.executescript, sql)
356 self.assertRaises(sqlite.ProgrammingError, self.cx.execute, sql)
357 self.assertRaises(sqlite.ProgrammingError,
358 self.cx.executemany, sql, [])
359 self.assertRaises(sqlite.ProgrammingError, self.cx.executescript, sql)
360 self.assertRaises(sqlite.ProgrammingError,
361 self.cx.create_function, "t", 1, lambda x: x)
362 self.assertRaises(sqlite.ProgrammingError, self.cx.cursor)
363 with self.assertRaises(sqlite.ProgrammingError):
364 with self.cx:
365 pass
366
367 def test_exceptions(self):
368 # Optional DB-API extension.
369 self.assertEqual(self.cx.Warning, sqlite.Warning)
370 self.assertEqual(self.cx.Error, sqlite.Error)
371 self.assertEqual(self.cx.InterfaceError, sqlite.InterfaceError)
372 self.assertEqual(self.cx.DatabaseError, sqlite.DatabaseError)
373 self.assertEqual(self.cx.DataError, sqlite.DataError)
374 self.assertEqual(self.cx.OperationalError, sqlite.OperationalError)
375 self.assertEqual(self.cx.IntegrityError, sqlite.IntegrityError)
376 self.assertEqual(self.cx.InternalError, sqlite.InternalError)
377 self.assertEqual(self.cx.ProgrammingError, sqlite.ProgrammingError)
378 self.assertEqual(self.cx.NotSupportedError, sqlite.NotSupportedError)
379
380 def test_in_transaction(self):
381 # Can't use db from setUp because we want to test initial state.
382 with memory_database() as cx:
383 cu = cx.cursor()
384 self.assertEqual(cx.in_transaction, False)
385 cu.execute("create table transactiontest(id integer primary key, name text)")
386 self.assertEqual(cx.in_transaction, False)
387 cu.execute("insert into transactiontest(name) values (?)", ("foo",))
388 self.assertEqual(cx.in_transaction, True)
389 cu.execute("select name from transactiontest where name=?", ["foo"])
390 row = cu.fetchone()
391 self.assertEqual(cx.in_transaction, True)
392 cx.commit()
393 self.assertEqual(cx.in_transaction, False)
394 cu.execute("select name from transactiontest where name=?", ["foo"])
395 row = cu.fetchone()
396 self.assertEqual(cx.in_transaction, False)
397 cu.close()
398
399 def test_in_transaction_ro(self):
400 with self.assertRaises(AttributeError):
401 self.cx.in_transaction = True
402
403 def test_connection_exceptions(self):
404 exceptions = [
405 "DataError",
406 "DatabaseError",
407 "Error",
408 "IntegrityError",
409 "InterfaceError",
410 "NotSupportedError",
411 "OperationalError",
412 "ProgrammingError",
413 "Warning",
414 ]
415 for exc in exceptions:
416 with self.subTest(exc=exc):
417 self.assertHasAttr(self.cx, exc)
418 self.assertIs(getattr(sqlite, exc), getattr(self.cx, exc))
419
420 def test_interrupt_on_closed_db(self):
421 self.cx.close()
422 with self.assertRaises(sqlite.ProgrammingError):
423 self.cx.interrupt()
424
425 def test_interrupt(self):
426 self.assertIsNone(self.cx.interrupt())
427
428 def test_drop_unused_refs(self):
429 for n in range(500):
430 cu = self.cx.execute(f"select {n}")
431 self.assertEqual(cu.fetchone()[0], n)
432
433 def test_connection_limits(self):
434 category = sqlite.SQLITE_LIMIT_SQL_LENGTH
435 saved_limit = self.cx.getlimit(category)
436 try:
437 new_limit = 10
438 prev_limit = self.cx.setlimit(category, new_limit)
439 self.assertEqual(saved_limit, prev_limit)
440 self.assertEqual(self.cx.getlimit(category), new_limit)
441 msg = "query string is too large"
442 self.assertRaisesRegex(sqlite.DataError, msg,
443 self.cx.execute, "select 1 as '16'")
444 finally: # restore saved limit
445 self.cx.setlimit(category, saved_limit)
446
447 def test_connection_bad_limit_category(self):
448 msg = "'category' is out of bounds"
449 cat = 1111
450 self.assertRaisesRegex(sqlite.ProgrammingError, msg,
451 self.cx.getlimit, cat)
452 self.assertRaisesRegex(sqlite.ProgrammingError, msg,
453 self.cx.setlimit, cat, 0)
454
455 def test_connection_init_bad_isolation_level(self):
456 msg = (
457 "isolation_level string must be '', 'DEFERRED', 'IMMEDIATE', or "
458 "'EXCLUSIVE'"
459 )
460 levels = (
461 "BOGUS",
462 " ",
463 "DEFERRE",
464 "IMMEDIAT",
465 "EXCLUSIV",
466 "DEFERREDS",
467 "IMMEDIATES",
468 "EXCLUSIVES",
469 )
470 for level in levels:
471 with self.subTest(level=level):
472 with self.assertRaisesRegex(ValueError, msg):
473 memory_database(isolation_level=level)
474 with memory_database() as cx:
475 with self.assertRaisesRegex(ValueError, msg):
476 cx.isolation_level = level
477 # Check that the default level is not changed
478 self.assertEqual(cx.isolation_level, "")
479
480 def test_connection_init_good_isolation_levels(self):
481 for level in ("", "DEFERRED", "IMMEDIATE", "EXCLUSIVE", None):
482 with self.subTest(level=level):
483 with memory_database(isolation_level=level) as cx:
484 self.assertEqual(cx.isolation_level, level)
485 with memory_database() as cx:
486 self.assertEqual(cx.isolation_level, "")
487 cx.isolation_level = level
488 self.assertEqual(cx.isolation_level, level)
489
490 def test_connection_reinit(self):
491 with memory_database() as cx:
492 cx.text_factory = bytes
493 cx.row_factory = sqlite.Row
494 cu = cx.cursor()
495 cu.execute("create table foo (bar)")
496 cu.executemany("insert into foo (bar) values (?)",
497 ((str(v),) for v in range(4)))
498 cu.execute("select bar from foo")
499
500 rows = [r for r in cu.fetchmany(2)]
501 self.assertTrue(all(isinstance(r, sqlite.Row) for r in rows))
502 self.assertEqual([r[0] for r in rows], [b"0", b"1"])
503
504 cx.__init__(":memory:")
505 cx.execute("create table foo (bar)")
506 cx.executemany("insert into foo (bar) values (?)",
507 ((v,) for v in ("a", "b", "c", "d")))
508
509 # This uses the old database, old row factory, but new text factory
510 rows = [r for r in cu.fetchall()]
511 self.assertTrue(all(isinstance(r, sqlite.Row) for r in rows))
512 self.assertEqual([r[0] for r in rows], ["2", "3"])
513 cu.close()
514
515 def test_connection_bad_reinit(self):
516 cx = sqlite.connect(":memory:")
517 with cx:
518 cx.execute("create table t(t)")
519 with temp_dir() as db:
520 self.assertRaisesRegex(sqlite.OperationalError,
521 "unable to open database file",
522 cx.__init__, db)
523 self.assertRaisesRegex(sqlite.ProgrammingError,
524 "Base Connection.__init__ not called",
525 cx.executemany, "insert into t values(?)",
526 ((v,) for v in range(3)))
527
528 def test_connection_config(self):
529 op = sqlite.SQLITE_DBCONFIG_ENABLE_FKEY
530 with memory_database() as cx:
531 with self.assertRaisesRegex(ValueError, "unknown"):
532 cx.getconfig(-1)
533
534 # Toggle and verify.
535 old = cx.getconfig(op)
536 new = not old
537 cx.setconfig(op, new)
538 self.assertEqual(cx.getconfig(op), new)
539
540 cx.setconfig(op) # defaults to True
541 self.assertTrue(cx.getconfig(op))
542
543 # Check that foreign key support was actually enabled.
544 with cx:
545 cx.executescript("""
546 create table t(t integer primary key);
547 create table u(u, foreign key(u) references t(t));
548 """)
549 with self.assertRaisesRegex(sqlite.IntegrityError, "constraint"):
550 cx.execute("insert into u values(0)")
551
552 def test_connect_positional_arguments(self):
553 regex = (
554 r"Passing more than 1 positional argument to sqlite3.connect\(\)"
555 " is deprecated. Parameters 'timeout', 'detect_types', "
556 "'isolation_level', 'check_same_thread', 'factory', "
557 "'cached_statements' and 'uri' will become keyword-only "
558 "parameters in Python 3.15."
559 )
560 with self.assertWarnsRegex(DeprecationWarning, regex) as cm:
561 cx = sqlite.connect(":memory:", 1.0)
562 cx.close()
563 self.assertEqual(cm.filename, __file__)
564
565 def test_connection_resource_warning(self):
566 with self.assertWarns(ResourceWarning):
567 cx = sqlite.connect(":memory:")
568 del cx
569 gc_collect()
570
571 def test_connection_signature(self):
572 from inspect import signature
573 sig = signature(self.cx)
574 self.assertEqual(str(sig), "(sql, /)")
575
576
577 class UninitialisedConnectionTests(unittest.TestCase):
578 def setUp(self):
579 self.cx = sqlite.Connection.__new__(sqlite.Connection)
580
581 def test_uninit_operations(self):
582 funcs = (
583 lambda: self.cx.isolation_level,
584 lambda: self.cx.total_changes,
585 lambda: self.cx.in_transaction,
586 lambda: self.cx.iterdump(),
587 lambda: self.cx.cursor(),
588 lambda: self.cx.close(),
589 )
590 for func in funcs:
591 with self.subTest(func=func):
592 self.assertRaisesRegex(sqlite.ProgrammingError,
593 "Base Connection.__init__ not called",
594 func)
595
596
597 @unittest.skipUnless(hasattr(sqlite.Connection, "serialize"),
598 "Needs SQLite serialize API")
599 class SerializeTests(unittest.TestCase):
600 def test_serialize_deserialize(self):
601 with memory_database() as cx:
602 with cx:
603 cx.execute("create table t(t)")
604 data = cx.serialize()
605
606 # Remove test table, verify that it was removed.
607 with cx:
608 cx.execute("drop table t")
609 regex = "no such table"
610 with self.assertRaisesRegex(sqlite.OperationalError, regex):
611 cx.execute("select t from t")
612
613 # Deserialize and verify that test table is restored.
614 cx.deserialize(data)
615 cx.execute("select t from t")
616
617 def test_deserialize_wrong_args(self):
618 dataset = (
619 (BufferError, memoryview(b"blob")[::2]),
620 (TypeError, []),
621 (TypeError, 1),
622 (TypeError, None),
623 )
624 for exc, arg in dataset:
625 with self.subTest(exc=exc, arg=arg):
626 with memory_database() as cx:
627 self.assertRaises(exc, cx.deserialize, arg)
628
629 def test_deserialize_corrupt_database(self):
630 with memory_database() as cx:
631 regex = "file is not a database"
632 with self.assertRaisesRegex(sqlite.DatabaseError, regex):
633 cx.deserialize(b"\0\1\3")
634 # SQLite does not generate an error until you try to query the
635 # deserialized database.
636 cx.execute("create table fail(f)")
637
638
639 class OpenTests(unittest.TestCase):
640 _sql = "create table test(id integer)"
641
642 def test_open_with_bytes_path(self):
643 path = os.fsencode(TESTFN)
644 self.addCleanup(unlink, path)
645 self.assertFalse(os.path.exists(path))
646 with contextlib.closing(sqlite.connect(path)) as cx:
647 self.assertTrue(os.path.exists(path))
648 cx.execute(self._sql)
649
650 def test_open_with_path_like_object(self):
651 """ Checks that we can successfully connect to a database using an object that
652 is PathLike, i.e. has __fspath__(). """
653 path = FakePath(TESTFN)
654 self.addCleanup(unlink, path)
655 self.assertFalse(os.path.exists(path))
656 with contextlib.closing(sqlite.connect(path)) as cx:
657 self.assertTrue(os.path.exists(path))
658 cx.execute(self._sql)
659
660 def get_undecodable_path(self):
661 path = TESTFN_UNDECODABLE
662 if not path:
663 self.skipTest("only works if there are undecodable paths")
664 try:
665 open(path, 'wb').close()
666 except OSError:
667 self.skipTest(f"can't create file with undecodable path {path!r}")
668 unlink(path)
669 return path
670
671 @unittest.skipIf(sys.platform == "win32", "skipped on Windows")
672 def test_open_with_undecodable_path(self):
673 path = self.get_undecodable_path()
674 self.addCleanup(unlink, path)
675 with contextlib.closing(sqlite.connect(path)) as cx:
676 self.assertTrue(os.path.exists(path))
677 cx.execute(self._sql)
678
679 def test_open_uri(self):
680 path = TESTFN
681 self.addCleanup(unlink, path)
682 uri = "file:" + urllib.parse.quote(os.fsencode(path))
683 self.assertFalse(os.path.exists(path))
684 with contextlib.closing(sqlite.connect(uri, uri=True)) as cx:
685 self.assertTrue(os.path.exists(path))
686 cx.execute(self._sql)
687
688 def test_open_unquoted_uri(self):
689 path = TESTFN
690 self.addCleanup(unlink, path)
691 uri = "file:" + path
692 self.assertFalse(os.path.exists(path))
693 with contextlib.closing(sqlite.connect(uri, uri=True)) as cx:
694 self.assertTrue(os.path.exists(path))
695 cx.execute(self._sql)
696
697 def test_open_uri_readonly(self):
698 path = TESTFN
699 self.addCleanup(unlink, path)
700 uri = "file:" + urllib.parse.quote(os.fsencode(path)) + "?mode=ro"
701 self.assertFalse(os.path.exists(path))
702 # Cannot create new DB
703 with self.assertRaises(sqlite.OperationalError):
704 sqlite.connect(uri, uri=True)
705 self.assertFalse(os.path.exists(path))
706 sqlite.connect(path).close()
707 self.assertTrue(os.path.exists(path))
708 # Cannot modify new DB
709 with contextlib.closing(sqlite.connect(uri, uri=True)) as cx:
710 with self.assertRaises(sqlite.OperationalError):
711 cx.execute(self._sql)
712
713 @unittest.skipIf(sys.platform == "win32", "skipped on Windows")
714 def test_open_undecodable_uri(self):
715 path = self.get_undecodable_path()
716 self.addCleanup(unlink, path)
717 uri = "file:" + urllib.parse.quote(path)
718 with contextlib.closing(sqlite.connect(uri, uri=True)) as cx:
719 self.assertTrue(os.path.exists(path))
720 cx.execute(self._sql)
721
722 def test_factory_database_arg(self):
723 def factory(database, *args, **kwargs):
724 nonlocal database_arg
725 database_arg = database
726 return sqlite.Connection(":memory:", *args, **kwargs)
727
728 for database in (TESTFN, os.fsencode(TESTFN),
729 FakePath(TESTFN), FakePath(os.fsencode(TESTFN))):
730 database_arg = None
731 sqlite.connect(database, factory=factory).close()
732 self.assertEqual(database_arg, database)
733
734 def test_database_keyword(self):
735 with contextlib.closing(sqlite.connect(database=":memory:")) as cx:
736 self.assertEqual(type(cx), sqlite.Connection)
737
738
739 class CursorTests(unittest.TestCase):
740 def setUp(self):
741 self.cx = sqlite.connect(":memory:")
742 self.cu = self.cx.cursor()
743 self.cu.execute(
744 "create table test(id integer primary key, name text, "
745 "income number, unique_test text unique)"
746 )
747 self.cu.execute("insert into test(name) values (?)", ("foo",))
748
749 def tearDown(self):
750 self.cu.close()
751 self.cx.close()
752
753 def test_execute_no_args(self):
754 self.cu.execute("delete from test")
755
756 def test_execute_illegal_sql(self):
757 with self.assertRaises(sqlite.OperationalError):
758 self.cu.execute("select asdf")
759
760 def test_execute_multiple_statements(self):
761 msg = "You can only execute one statement at a time"
762 dataset = (
763 "select 1; select 2",
764 "select 1; // c++ comments are not allowed",
765 "select 1; *not a comment",
766 "select 1; -*not a comment",
767 "select 1; /* */ a",
768 "select 1; /**/a",
769 "select 1; -",
770 "select 1; /",
771 "select 1; -\n- select 2",
772 """select 1;
773 -- comment
774 select 2
775 """,
776 )
777 for query in dataset:
778 with self.subTest(query=query):
779 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
780 self.cu.execute(query)
781
782 def test_execute_with_appended_comments(self):
783 dataset = (
784 "select 1; -- foo bar",
785 "select 1; --",
786 "select 1; /*", # Unclosed comments ending in \0 are skipped.
787 """
788 select 5+4;
789
790 /*
791 foo
792 */
793 """,
794 )
795 for query in dataset:
796 with self.subTest(query=query):
797 self.cu.execute(query)
798
799 def test_execute_wrong_sql_arg(self):
800 with self.assertRaises(TypeError):
801 self.cu.execute(42)
802
803 def test_execute_arg_int(self):
804 self.cu.execute("insert into test(id) values (?)", (42,))
805
806 def test_execute_arg_float(self):
807 self.cu.execute("insert into test(income) values (?)", (2500.32,))
808
809 def test_execute_arg_string(self):
810 self.cu.execute("insert into test(name) values (?)", ("Hugo",))
811
812 def test_execute_arg_string_with_zero_byte(self):
813 self.cu.execute("insert into test(name) values (?)", ("Hu\x00go",))
814
815 self.cu.execute("select name from test where id=?", (self.cu.lastrowid,))
816 row = self.cu.fetchone()
817 self.assertEqual(row[0], "Hu\x00go")
818
819 def test_execute_non_iterable(self):
820 with self.assertRaises(sqlite.ProgrammingError) as cm:
821 self.cu.execute("insert into test(id) values (?)", 42)
822 self.assertEqual(str(cm.exception), 'parameters are of unsupported type')
823
824 def test_execute_wrong_no_of_args1(self):
825 # too many parameters
826 with self.assertRaises(sqlite.ProgrammingError):
827 self.cu.execute("insert into test(id) values (?)", (17, "Egon"))
828
829 def test_execute_wrong_no_of_args2(self):
830 # too little parameters
831 with self.assertRaises(sqlite.ProgrammingError):
832 self.cu.execute("insert into test(id) values (?)")
833
834 def test_execute_wrong_no_of_args3(self):
835 # no parameters, parameters are needed
836 with self.assertRaises(sqlite.ProgrammingError):
837 self.cu.execute("insert into test(id) values (?)")
838
839 def test_execute_param_list(self):
840 self.cu.execute("insert into test(name) values ('foo')")
841 self.cu.execute("select name from test where name=?", ["foo"])
842 row = self.cu.fetchone()
843 self.assertEqual(row[0], "foo")
844
845 def test_execute_param_sequence(self):
846 class L:
847 def __len__(self):
848 return 1
849 def __getitem__(self, x):
850 assert x == 0
851 return "foo"
852
853 self.cu.execute("insert into test(name) values ('foo')")
854 self.cu.execute("select name from test where name=?", L())
855 row = self.cu.fetchone()
856 self.assertEqual(row[0], "foo")
857
858 def test_execute_param_sequence_bad_len(self):
859 # Issue41662: Error in __len__() was overridden with ProgrammingError.
860 class L:
861 def __len__(self):
862 1/0
863 def __getitem__(slf, x):
864 raise AssertionError
865
866 self.cu.execute("insert into test(name) values ('foo')")
867 with self.assertRaises(ZeroDivisionError):
868 self.cu.execute("select name from test where name=?", L())
869
870 def test_execute_named_param_and_sequence(self):
871 dataset = (
872 ("select :a", (1,)),
873 ("select :a, ?, ?", (1, 2, 3)),
874 ("select ?, :b, ?", (1, 2, 3)),
875 ("select ?, ?, :c", (1, 2, 3)),
876 ("select :a, :b, ?", (1, 2, 3)),
877 )
878 msg = "Binding.*is a named parameter"
879 for query, params in dataset:
880 with self.subTest(query=query, params=params):
881 with self.assertRaisesRegex(sqlite.ProgrammingError, msg) as cm:
882 self.cu.execute(query, params)
883
884 def test_execute_indexed_nameless_params(self):
885 # See gh-117995: "'?1' is considered a named placeholder"
886 for query, params, expected in (
887 ("select ?1, ?2", (1, 2), (1, 2)),
888 ("select ?2, ?1", (1, 2), (2, 1)),
889 ):
890 with self.subTest(query=query, params=params):
891 with warnings.catch_warnings():
892 warnings.simplefilter("error", DeprecationWarning)
893 cu = self.cu.execute(query, params)
894 actual, = cu.fetchall()
895 self.assertEqual(actual, expected)
896
897 def test_execute_too_many_params(self):
898 category = sqlite.SQLITE_LIMIT_VARIABLE_NUMBER
899 msg = "too many SQL variables"
900 with cx_limit(self.cx, category=category, limit=1):
901 self.cu.execute("select * from test where id=?", (1,))
902 with self.assertRaisesRegex(sqlite.OperationalError, msg):
903 self.cu.execute("select * from test where id!=? and id!=?",
904 (1, 2))
905
906 def test_execute_dict_mapping(self):
907 self.cu.execute("insert into test(name) values ('foo')")
908 self.cu.execute("select name from test where name=:name", {"name": "foo"})
909 row = self.cu.fetchone()
910 self.assertEqual(row[0], "foo")
911
912 def test_execute_dict_mapping_mapping(self):
913 class D(dict):
914 def __missing__(self, key):
915 return "foo"
916
917 self.cu.execute("insert into test(name) values ('foo')")
918 self.cu.execute("select name from test where name=:name", D())
919 row = self.cu.fetchone()
920 self.assertEqual(row[0], "foo")
921
922 def test_execute_dict_mapping_too_little_args(self):
923 self.cu.execute("insert into test(name) values ('foo')")
924 with self.assertRaises(sqlite.ProgrammingError):
925 self.cu.execute("select name from test where name=:name and id=:id", {"name": "foo"})
926
927 def test_execute_dict_mapping_no_args(self):
928 self.cu.execute("insert into test(name) values ('foo')")
929 with self.assertRaises(sqlite.ProgrammingError):
930 self.cu.execute("select name from test where name=:name")
931
932 def test_execute_dict_mapping_unnamed(self):
933 self.cu.execute("insert into test(name) values ('foo')")
934 with self.assertRaises(sqlite.ProgrammingError):
935 self.cu.execute("select name from test where name=?", {"name": "foo"})
936
937 def test_close(self):
938 self.cu.close()
939
940 def test_rowcount_execute(self):
941 self.cu.execute("delete from test")
942 self.cu.execute("insert into test(name) values ('foo')")
943 self.cu.execute("insert into test(name) values ('foo')")
944 self.cu.execute("update test set name='bar'")
945 self.assertEqual(self.cu.rowcount, 2)
946
947 def test_rowcount_select(self):
948 """
949 pysqlite does not know the rowcount of SELECT statements, because we
950 don't fetch all rows after executing the select statement. The rowcount
951 has thus to be -1.
952 """
953 self.cu.execute("select 5 union select 6")
954 self.assertEqual(self.cu.rowcount, -1)
955
956 def test_rowcount_executemany(self):
957 self.cu.execute("delete from test")
958 self.cu.executemany("insert into test(name) values (?)", [(1,), (2,), (3,)])
959 self.assertEqual(self.cu.rowcount, 3)
960
961 @unittest.skipIf(sqlite.sqlite_version_info < (3, 35, 0),
962 "Requires SQLite 3.35.0 or newer")
963 def test_rowcount_update_returning(self):
964 # gh-93421: rowcount is updated correctly for UPDATE...RETURNING queries
965 self.cu.execute("update test set name='bar' where name='foo' returning 1")
966 self.assertEqual(self.cu.fetchone()[0], 1)
967 self.assertEqual(self.cu.rowcount, 1)
968
969 def test_rowcount_prefixed_with_comment(self):
970 # gh-79579: rowcount is updated even if query is prefixed with comments
971 self.cu.execute("""
972 -- foo
973 insert into test(name) values ('foo'), ('foo')
974 """)
975 self.assertEqual(self.cu.rowcount, 2)
976 self.cu.execute("""
977 /* -- messy *r /* /* ** *- *--
978 */
979 /* one more */ insert into test(name) values ('messy')
980 """)
981 self.assertEqual(self.cu.rowcount, 1)
982 self.cu.execute("/* bar */ update test set name='bar' where name='foo'")
983 self.assertEqual(self.cu.rowcount, 3)
984
985 def test_rowcount_vaccuum(self):
986 data = ((1,), (2,), (3,))
987 self.cu.executemany("insert into test(income) values(?)", data)
988 self.assertEqual(self.cu.rowcount, 3)
989 self.cx.commit()
990 self.cu.execute("vacuum")
991 self.assertEqual(self.cu.rowcount, -1)
992
993 def test_total_changes(self):
994 self.cu.execute("insert into test(name) values ('foo')")
995 self.cu.execute("insert into test(name) values ('foo')")
996 self.assertLess(2, self.cx.total_changes, msg='total changes reported wrong value')
997
998 # Checks for executemany:
999 # Sequences are required by the DB-API, iterators
1000 # enhancements in pysqlite.
1001
1002 def test_execute_many_sequence(self):
1003 self.cu.executemany("insert into test(income) values (?)", [(x,) for x in range(100, 110)])
1004
1005 def test_execute_many_iterator(self):
1006 class MyIter:
1007 def __init__(self):
1008 self.value = 5
1009
1010 def __iter__(self):
1011 return self
1012
1013 def __next__(self):
1014 if self.value == 10:
1015 raise StopIteration
1016 else:
1017 self.value += 1
1018 return (self.value,)
1019
1020 self.cu.executemany("insert into test(income) values (?)", MyIter())
1021
1022 def test_execute_many_generator(self):
1023 def mygen():
1024 for i in range(5):
1025 yield (i,)
1026
1027 self.cu.executemany("insert into test(income) values (?)", mygen())
1028
1029 def test_execute_many_wrong_sql_arg(self):
1030 with self.assertRaises(TypeError):
1031 self.cu.executemany(42, [(3,)])
1032
1033 def test_execute_many_select(self):
1034 with self.assertRaises(sqlite.ProgrammingError):
1035 self.cu.executemany("select ?", [(3,)])
1036
1037 def test_execute_many_not_iterable(self):
1038 with self.assertRaises(TypeError):
1039 self.cu.executemany("insert into test(income) values (?)", 42)
1040
1041 def test_fetch_iter(self):
1042 # Optional DB-API extension.
1043 self.cu.execute("delete from test")
1044 self.cu.execute("insert into test(id) values (?)", (5,))
1045 self.cu.execute("insert into test(id) values (?)", (6,))
1046 self.cu.execute("select id from test order by id")
1047 lst = []
1048 for row in self.cu:
1049 lst.append(row[0])
1050 self.assertEqual(lst[0], 5)
1051 self.assertEqual(lst[1], 6)
1052
1053 def test_fetchone(self):
1054 self.cu.execute("select name from test")
1055 row = self.cu.fetchone()
1056 self.assertEqual(row[0], "foo")
1057 row = self.cu.fetchone()
1058 self.assertEqual(row, None)
1059
1060 def test_fetchone_no_statement(self):
1061 cur = self.cx.cursor()
1062 row = cur.fetchone()
1063 self.assertEqual(row, None)
1064
1065 def test_array_size(self):
1066 # must default to 1
1067 self.assertEqual(self.cu.arraysize, 1)
1068
1069 # now set to 2
1070 self.cu.arraysize = 2
1071
1072 # now make the query return 2 rows from a table of 3 rows
1073 self.cu.execute("delete from test")
1074 self.cu.execute("insert into test(name) values ('A')")
1075 self.cu.execute("insert into test(name) values ('B')")
1076 self.cu.execute("insert into test(name) values ('C')")
1077 self.cu.execute("select name from test")
1078 res = self.cu.fetchmany()
1079
1080 self.assertEqual(len(res), 2)
1081
1082 def test_invalid_array_size(self):
1083 UINT32_MAX = (1 << 32) - 1
1084 setter = functools.partial(setattr, self.cu, 'arraysize')
1085
1086 self.assertRaises(TypeError, setter, 1.0)
1087 self.assertRaises(ValueError, setter, -3)
1088 self.assertRaises(OverflowError, setter, UINT32_MAX + 1)
1089
1090 def test_fetchmany(self):
1091 # no active SQL statement
1092 res = self.cu.fetchmany()
1093 self.assertEqual(res, [])
1094 res = self.cu.fetchmany(1000)
1095 self.assertEqual(res, [])
1096
1097 # test default parameter
1098 self.cu.execute("select name from test")
1099 res = self.cu.fetchmany()
1100 self.assertEqual(len(res), 1)
1101
1102 # test when the number of requested rows exceeds the actual count
1103 self.cu.execute("select name from test")
1104 res = self.cu.fetchmany(100)
1105 self.assertEqual(len(res), 1)
1106 res = self.cu.fetchmany(100)
1107 self.assertEqual(res, [])
1108
1109 # test when size = 0
1110 self.cu.execute("select name from test")
1111 res = self.cu.fetchmany(0)
1112 self.assertEqual(res, [])
1113 res = self.cu.fetchmany(100)
1114 self.assertEqual(len(res), 1)
1115 res = self.cu.fetchmany(100)
1116 self.assertEqual(res, [])
1117
1118 def test_invalid_fetchmany(self):
1119 UINT32_MAX = (1 << 32) - 1
1120 fetchmany = self.cu.fetchmany
1121
1122 self.assertRaises(TypeError, fetchmany, 1.0)
1123 self.assertRaises(ValueError, fetchmany, -3)
1124 self.assertRaises(OverflowError, fetchmany, UINT32_MAX + 1)
1125
1126 def test_fetchmany_kw_arg(self):
1127 """Checks if fetchmany works with keyword arguments"""
1128 self.cu.execute("select name from test")
1129 res = self.cu.fetchmany(size=100)
1130 self.assertEqual(len(res), 1)
1131
1132 def test_fetchall(self):
1133 self.cu.execute("select name from test")
1134 res = self.cu.fetchall()
1135 self.assertEqual(len(res), 1)
1136 res = self.cu.fetchall()
1137 self.assertEqual(res, [])
1138
1139 def test_setinputsizes(self):
1140 self.cu.setinputsizes([3, 4, 5])
1141
1142 def test_setoutputsize(self):
1143 self.cu.setoutputsize(5, 0)
1144
1145 def test_setoutputsize_no_column(self):
1146 self.cu.setoutputsize(42)
1147
1148 def test_cursor_connection(self):
1149 # Optional DB-API extension.
1150 self.assertEqual(self.cu.connection, self.cx)
1151
1152 def test_wrong_cursor_callable(self):
1153 with self.assertRaises(TypeError):
1154 def f(): pass
1155 cur = self.cx.cursor(f)
1156
1157 def test_cursor_wrong_class(self):
1158 class Foo: pass
1159 foo = Foo()
1160 with self.assertRaises(TypeError):
1161 cur = sqlite.Cursor(foo)
1162
1163 def test_last_row_id_on_replace(self):
1164 """
1165 INSERT OR REPLACE and REPLACE INTO should produce the same behavior.
1166 """
1167 sql = '{} INTO test(id, unique_test) VALUES (?, ?)'
1168 for statement in ('INSERT OR REPLACE', 'REPLACE'):
1169 with self.subTest(statement=statement):
1170 self.cu.execute(sql.format(statement), (1, 'foo'))
1171 self.assertEqual(self.cu.lastrowid, 1)
1172
1173 def test_last_row_id_on_ignore(self):
1174 self.cu.execute(
1175 "insert or ignore into test(unique_test) values (?)",
1176 ('test',))
1177 self.assertEqual(self.cu.lastrowid, 2)
1178 self.cu.execute(
1179 "insert or ignore into test(unique_test) values (?)",
1180 ('test',))
1181 self.assertEqual(self.cu.lastrowid, 2)
1182
1183 def test_last_row_id_insert_o_r(self):
1184 results = []
1185 for statement in ('FAIL', 'ABORT', 'ROLLBACK'):
1186 sql = 'INSERT OR {} INTO test(unique_test) VALUES (?)'
1187 with self.subTest(statement='INSERT OR {}'.format(statement)):
1188 self.cu.execute(sql.format(statement), (statement,))
1189 results.append((statement, self.cu.lastrowid))
1190 with self.assertRaises(sqlite.IntegrityError):
1191 self.cu.execute(sql.format(statement), (statement,))
1192 results.append((statement, self.cu.lastrowid))
1193 expected = [
1194 ('FAIL', 2), ('FAIL', 2),
1195 ('ABORT', 3), ('ABORT', 3),
1196 ('ROLLBACK', 4), ('ROLLBACK', 4),
1197 ]
1198 self.assertEqual(results, expected)
1199
1200 def test_column_count(self):
1201 # Check that column count is updated correctly for cached statements
1202 select = "select * from test"
1203 res = self.cu.execute(select)
1204 old_count = len(res.description)
1205 # Add a new column and execute the cached select query again
1206 self.cu.execute("alter table test add newcol")
1207 res = self.cu.execute(select)
1208 new_count = len(res.description)
1209 self.assertEqual(new_count - old_count, 1)
1210
1211 def test_same_query_in_multiple_cursors(self):
1212 cursors = [self.cx.execute("select 1") for _ in range(3)]
1213 for cu in cursors:
1214 self.assertEqual(cu.fetchall(), [(1,)])
1215
1216
1217 class BlobTests(unittest.TestCase):
1218 def setUp(self):
1219 self.cx = sqlite.connect(":memory:")
1220 self.cx.execute("create table test(b blob)")
1221 self.data = b"this blob data string is exactly fifty bytes long!"
1222 self.cx.execute("insert into test(b) values (?)", (self.data,))
1223 self.blob = self.cx.blobopen("test", "b", 1)
1224
1225 def tearDown(self):
1226 self.blob.close()
1227 self.cx.close()
1228
1229 def test_blob_is_a_blob(self):
1230 self.assertIsInstance(self.blob, sqlite.Blob)
1231
1232 def test_blob_seek_and_tell(self):
1233 self.blob.seek(10)
1234 self.assertEqual(self.blob.tell(), 10)
1235
1236 self.blob.seek(10, SEEK_SET)
1237 self.assertEqual(self.blob.tell(), 10)
1238
1239 self.blob.seek(10, SEEK_CUR)
1240 self.assertEqual(self.blob.tell(), 20)
1241
1242 self.blob.seek(-10, SEEK_END)
1243 self.assertEqual(self.blob.tell(), 40)
1244
1245 def test_blob_seek_error(self):
1246 msg_oor = "offset out of blob range"
1247 msg_orig = "'origin' should be os.SEEK_SET, os.SEEK_CUR, or os.SEEK_END"
1248
1249 dataset = (
1250 (ValueError, msg_oor, lambda: self.blob.seek(1000)),
1251 (ValueError, msg_oor, lambda: self.blob.seek(-10)),
1252 (ValueError, msg_orig, lambda: self.blob.seek(10, -1)),
1253 (ValueError, msg_orig, lambda: self.blob.seek(10, 3)),
1254 )
1255 for exc, msg, fn in dataset:
1256 with self.subTest(exc=exc, msg=msg, fn=fn):
1257 self.assertRaisesRegex(exc, msg, fn)
1258
1259 def test_blob_seek_overflow_error(self):
1260 # Force overflow errors
1261 msg_of = "seek offset results in overflow"
1262 _testcapi = import_helper.import_module("_testcapi")
1263 self.blob.seek(1, SEEK_SET)
1264 with self.assertRaisesRegex(OverflowError, msg_of):
1265 self.blob.seek(_testcapi.INT_MAX, SEEK_CUR)
1266 with self.assertRaisesRegex(OverflowError, msg_of):
1267 self.blob.seek(_testcapi.INT_MAX, SEEK_END)
1268
1269 def test_blob_read(self):
1270 buf = self.blob.read()
1271 self.assertEqual(buf, self.data)
1272
1273 def test_blob_read_oversized(self):
1274 buf = self.blob.read(len(self.data) * 2)
1275 self.assertEqual(buf, self.data)
1276
1277 def test_blob_read_advance_offset(self):
1278 n = 10
1279 buf = self.blob.read(n)
1280 self.assertEqual(buf, self.data[:n])
1281 self.assertEqual(self.blob.tell(), n)
1282
1283 def test_blob_read_at_offset(self):
1284 self.blob.seek(10)
1285 self.assertEqual(self.blob.read(10), self.data[10:20])
1286
1287 def test_blob_read_error_row_changed(self):
1288 self.cx.execute("update test set b='aaaa' where rowid=1")
1289 with self.assertRaises(sqlite.OperationalError):
1290 self.blob.read()
1291
1292 def test_blob_write(self):
1293 new_data = b"new data".ljust(50)
1294 self.blob.write(new_data)
1295 row = self.cx.execute("select b from test").fetchone()
1296 self.assertEqual(row[0], new_data)
1297
1298 def test_blob_write_at_offset(self):
1299 new_data = b"c" * 25
1300 self.blob.seek(25)
1301 self.blob.write(new_data)
1302 row = self.cx.execute("select b from test").fetchone()
1303 self.assertEqual(row[0], self.data[:25] + new_data)
1304
1305 def test_blob_write_advance_offset(self):
1306 self.blob.write(b"d"*10)
1307 self.assertEqual(self.blob.tell(), 10)
1308
1309 def test_blob_write_error_length(self):
1310 with self.assertRaisesRegex(ValueError, "data longer than blob"):
1311 self.blob.write(b"a" * 1000)
1312
1313 self.blob.seek(0, SEEK_SET)
1314 n = len(self.blob)
1315 self.blob.write(b"a" * (n-1))
1316 self.blob.write(b"a")
1317 with self.assertRaisesRegex(ValueError, "data longer than blob"):
1318 self.blob.write(b"a")
1319
1320 def test_blob_write_error_row_changed(self):
1321 self.cx.execute("update test set b='aaaa' where rowid=1")
1322 with self.assertRaises(sqlite.OperationalError):
1323 self.blob.write(b"aaa")
1324
1325 def test_blob_write_error_readonly(self):
1326 ro_blob = self.cx.blobopen("test", "b", 1, readonly=True)
1327 with self.assertRaisesRegex(sqlite.OperationalError, "readonly"):
1328 ro_blob.write(b"aaa")
1329 ro_blob.close()
1330
1331 def test_blob_open_error(self):
1332 dataset = (
1333 (("test", "b", 1), {"name": "notexisting"}),
1334 (("notexisting", "b", 1), {}),
1335 (("test", "notexisting", 1), {}),
1336 (("test", "b", 2), {}),
1337 )
1338 regex = "no such"
1339 for args, kwds in dataset:
1340 with self.subTest(args=args, kwds=kwds):
1341 with self.assertRaisesRegex(sqlite.OperationalError, regex):
1342 self.cx.blobopen(*args, **kwds)
1343
1344 def test_blob_length(self):
1345 self.assertEqual(len(self.blob), 50)
1346
1347 def test_blob_get_item(self):
1348 self.assertEqual(self.blob[5], ord("b"))
1349 self.assertEqual(self.blob[6], ord("l"))
1350 self.assertEqual(self.blob[7], ord("o"))
1351 self.assertEqual(self.blob[8], ord("b"))
1352 self.assertEqual(self.blob[-1], ord("!"))
1353
1354 def test_blob_set_item(self):
1355 self.blob[0] = ord("b")
1356 expected = b"b" + self.data[1:]
1357 actual = self.cx.execute("select b from test").fetchone()[0]
1358 self.assertEqual(actual, expected)
1359
1360 def test_blob_set_item_with_offset(self):
1361 self.blob.seek(0, SEEK_END)
1362 self.assertEqual(self.blob.read(), b"") # verify that we're at EOB
1363 self.blob[0] = ord("T")
1364 self.blob[-1] = ord(".")
1365 self.blob.seek(0, SEEK_SET)
1366 expected = b"This blob data string is exactly fifty bytes long."
1367 self.assertEqual(self.blob.read(), expected)
1368
1369 def test_blob_set_slice_buffer_object(self):
1370 from array import array
1371 self.blob[0:5] = memoryview(b"12345")
1372 self.assertEqual(self.blob[0:5], b"12345")
1373
1374 self.blob[0:5] = bytearray(b"23456")
1375 self.assertEqual(self.blob[0:5], b"23456")
1376
1377 self.blob[0:5] = array("b", [1, 2, 3, 4, 5])
1378 self.assertEqual(self.blob[0:5], b"\x01\x02\x03\x04\x05")
1379
1380 def test_blob_set_item_negative_index(self):
1381 self.blob[-1] = 255
1382 self.assertEqual(self.blob[-1], 255)
1383
1384 def test_blob_get_slice(self):
1385 self.assertEqual(self.blob[5:14], b"blob data")
1386
1387 def test_blob_get_empty_slice(self):
1388 self.assertEqual(self.blob[5:5], b"")
1389
1390 def test_blob_get_empty_slice_oob_indices(self):
1391 self.cx.execute("insert into test(b) values (?)", (b"abc",))
1392 with self.cx.blobopen("test", "b", 2) as blob:
1393 self.assertEqual(blob[5:-5], b"")
1394
1395 def test_blob_get_slice_negative_index(self):
1396 self.assertEqual(self.blob[5:-5], self.data[5:-5])
1397
1398 def test_blob_get_slice_with_skip(self):
1399 self.assertEqual(self.blob[0:10:2], b"ti lb")
1400
1401 def test_blob_set_slice(self):
1402 self.blob[0:5] = b"12345"
1403 expected = b"12345" + self.data[5:]
1404 actual = self.cx.execute("select b from test").fetchone()[0]
1405 self.assertEqual(actual, expected)
1406
1407 def test_blob_set_empty_slice(self):
1408 self.blob[0:0] = b""
1409 self.assertEqual(self.blob[:], self.data)
1410
1411 def test_blob_set_slice_with_skip(self):
1412 self.blob[0:10:2] = b"12345"
1413 actual = self.cx.execute("select b from test").fetchone()[0]
1414 expected = b"1h2s3b4o5 " + self.data[10:]
1415 self.assertEqual(actual, expected)
1416
1417 def test_blob_mapping_invalid_index_type(self):
1418 msg = "indices must be integers"
1419 with self.assertRaisesRegex(TypeError, msg):
1420 self.blob[5:5.5]
1421 with self.assertRaisesRegex(TypeError, msg):
1422 self.blob[1.5]
1423 with self.assertRaisesRegex(TypeError, msg):
1424 self.blob["a"] = b"b"
1425
1426 def test_blob_get_item_error(self):
1427 dataset = [len(self.blob), 105, -105]
1428 for idx in dataset:
1429 with self.subTest(idx=idx):
1430 with self.assertRaisesRegex(IndexError, "index out of range"):
1431 self.blob[idx]
1432
1433 # Provoke read error
1434 self.cx.execute("update test set b='aaaa' where rowid=1")
1435 with self.assertRaises(sqlite.OperationalError):
1436 self.blob[0]
1437
1438 def test_blob_get_item_error_bigint(self):
1439 _testcapi = import_helper.import_module("_testcapi")
1440 with self.assertRaisesRegex(IndexError, "cannot fit 'int'"):
1441 self.blob[_testcapi.ULLONG_MAX]
1442
1443 def test_blob_set_item_error(self):
1444 with self.assertRaisesRegex(TypeError, "cannot be interpreted"):
1445 self.blob[0] = b"multiple"
1446 with self.assertRaisesRegex(TypeError, "cannot be interpreted"):
1447 self.blob[0] = b"1"
1448 with self.assertRaisesRegex(TypeError, "cannot be interpreted"):
1449 self.blob[0] = bytearray(b"1")
1450 with self.assertRaisesRegex(TypeError, "doesn't support.*deletion"):
1451 del self.blob[0]
1452 with self.assertRaisesRegex(IndexError, "Blob index out of range"):
1453 self.blob[1000] = 0
1454 with self.assertRaisesRegex(ValueError, "must be in range"):
1455 self.blob[0] = -1
1456 with self.assertRaisesRegex(ValueError, "must be in range"):
1457 self.blob[0] = 256
1458 # Overflow errors are overridden with ValueError
1459 with self.assertRaisesRegex(ValueError, "must be in range"):
1460 self.blob[0] = 2**65
1461
1462 def test_blob_set_slice_error(self):
1463 with self.assertRaisesRegex(IndexError, "wrong size"):
1464 self.blob[5:10] = b"a"
1465 with self.assertRaisesRegex(IndexError, "wrong size"):
1466 self.blob[5:10] = b"a" * 1000
1467 with self.assertRaisesRegex(TypeError, "doesn't support.*deletion"):
1468 del self.blob[5:10]
1469 with self.assertRaisesRegex(ValueError, "step cannot be zero"):
1470 self.blob[5:10:0] = b"12345"
1471 with self.assertRaises(BufferError):
1472 self.blob[5:10] = memoryview(b"abcde")[::2]
1473
1474 def test_blob_sequence_not_supported(self):
1475 with self.assertRaisesRegex(TypeError, "unsupported operand"):
1476 self.blob + self.blob
1477 with self.assertRaisesRegex(TypeError, "unsupported operand"):
1478 self.blob * 5
1479 with self.assertRaisesRegex(TypeError, "is not.+iterable"):
1480 b"a" in self.blob
1481
1482 def test_blob_context_manager(self):
1483 data = b"a" * 50
1484 with self.cx.blobopen("test", "b", 1) as blob:
1485 blob.write(data)
1486 actual = self.cx.execute("select b from test").fetchone()[0]
1487 self.assertEqual(actual, data)
1488
1489 # Check that __exit__ closed the blob
1490 with self.assertRaisesRegex(sqlite.ProgrammingError, "closed blob"):
1491 blob.read()
1492
1493 def test_blob_context_manager_reraise_exceptions(self):
1494 class DummyException(Exception):
1495 pass
1496 with self.assertRaisesRegex(DummyException, "reraised"):
1497 with self.cx.blobopen("test", "b", 1) as blob:
1498 raise DummyException("reraised")
1499
1500
1501 def test_blob_closed(self):
1502 with memory_database() as cx:
1503 cx.execute("create table test(b blob)")
1504 cx.execute("insert into test values (zeroblob(100))")
1505 blob = cx.blobopen("test", "b", 1)
1506 blob.close()
1507
1508 msg = "Cannot operate on a closed blob"
1509 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1510 blob.read()
1511 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1512 blob.write(b"")
1513 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1514 blob.seek(0)
1515 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1516 blob.tell()
1517 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1518 blob.__enter__()
1519 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1520 blob.__exit__(None, None, None)
1521 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1522 len(blob)
1523 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1524 blob[0]
1525 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1526 blob[0:1]
1527 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1528 blob[0] = b""
1529
1530 def test_blob_closed_db_read(self):
1531 with memory_database() as cx:
1532 cx.execute("create table test(b blob)")
1533 cx.execute("insert into test(b) values (zeroblob(100))")
1534 blob = cx.blobopen("test", "b", 1)
1535 cx.close()
1536 self.assertRaisesRegex(sqlite.ProgrammingError,
1537 "Cannot operate on a closed database",
1538 blob.read)
1539
1540 def test_blob_32bit_rowid(self):
1541 # gh-100370: we should not get an OverflowError for 32-bit rowids
1542 with memory_database() as cx:
1543 rowid = 2**32
1544 cx.execute("create table t(t blob)")
1545 cx.execute("insert into t(rowid, t) values (?, zeroblob(1))", (rowid,))
1546 cx.blobopen('t', 't', rowid)
1547
1548
1549 @threading_helper.requires_working_threading()
1550 class ThreadTests(unittest.TestCase):
1551 def setUp(self):
1552 self.con = sqlite.connect(":memory:")
1553 self.cur = self.con.cursor()
1554 self.cur.execute("create table test(name text, b blob)")
1555 self.cur.execute("insert into test values('blob', zeroblob(1))")
1556
1557 def tearDown(self):
1558 self.cur.close()
1559 self.con.close()
1560
1561 @threading_helper.reap_threads
1562 def _run_test(self, fn, *args, **kwds):
1563 def run(err):
1564 try:
1565 fn(*args, **kwds)
1566 err.append("did not raise ProgrammingError")
1567 except sqlite.ProgrammingError:
1568 pass
1569 except:
1570 err.append("raised wrong exception")
1571
1572 err = []
1573 t = threading.Thread(target=run, kwargs={"err": err})
1574 t.start()
1575 t.join()
1576 if err:
1577 self.fail("\n".join(err))
1578
1579 def test_check_connection_thread(self):
1580 fns = [
1581 lambda: self.con.cursor(),
1582 lambda: self.con.commit(),
1583 lambda: self.con.rollback(),
1584 lambda: self.con.close(),
1585 lambda: self.con.set_trace_callback(None),
1586 lambda: self.con.set_authorizer(None),
1587 lambda: self.con.create_collation("foo", None),
1588 lambda: self.con.setlimit(sqlite.SQLITE_LIMIT_LENGTH, -1),
1589 lambda: self.con.getlimit(sqlite.SQLITE_LIMIT_LENGTH),
1590 lambda: self.con.blobopen("test", "b", 1),
1591 ]
1592 if hasattr(sqlite.Connection, "serialize"):
1593 fns.append(lambda: self.con.serialize())
1594 fns.append(lambda: self.con.deserialize(b""))
1595 if sqlite.sqlite_version_info >= (3, 25, 0):
1596 fns.append(lambda: self.con.create_window_function("foo", 0, None))
1597
1598 for fn in fns:
1599 with self.subTest(fn=fn):
1600 self._run_test(fn)
1601
1602 def test_check_cursor_thread(self):
1603 fns = [
1604 lambda: self.cur.execute("insert into test(name) values('a')"),
1605 lambda: self.cur.close(),
1606 lambda: self.cur.execute("select name from test"),
1607 lambda: self.cur.fetchone(),
1608 ]
1609 for fn in fns:
1610 with self.subTest(fn=fn):
1611 self._run_test(fn)
1612
1613
1614 @threading_helper.reap_threads
1615 def test_dont_check_same_thread(self):
1616 def run(con, err):
1617 try:
1618 con.execute("select 1")
1619 except sqlite.Error:
1620 err.append("multi-threading not allowed")
1621
1622 with memory_database(check_same_thread=False) as con:
1623 err = []
1624 t = threading.Thread(target=run, kwargs={"con": con, "err": err})
1625 t.start()
1626 t.join()
1627 self.assertEqual(len(err), 0, "\n".join(err))
1628
1629
1630 class ConstructorTests(unittest.TestCase):
1631 def test_date(self):
1632 d = sqlite.Date(2004, 10, 28)
1633
1634 def test_time(self):
1635 t = sqlite.Time(12, 39, 35)
1636
1637 def test_timestamp(self):
1638 ts = sqlite.Timestamp(2004, 10, 28, 12, 39, 35)
1639
1640 def test_date_from_ticks(self):
1641 d = sqlite.DateFromTicks(42)
1642
1643 def test_time_from_ticks(self):
1644 t = sqlite.TimeFromTicks(42)
1645
1646 def test_timestamp_from_ticks(self):
1647 ts = sqlite.TimestampFromTicks(42)
1648
1649 def test_binary(self):
1650 b = sqlite.Binary(b"\0'")
1651
1652 class ExtensionTests(unittest.TestCase):
1653 def setUp(self):
1654 self.con = sqlite.connect(":memory:")
1655 self.cur = self.con.cursor()
1656
1657 def tearDown(self):
1658 self.cur.close()
1659 self.con.close()
1660
1661 def test_script_string_sql(self):
1662 cur = self.cur
1663 cur.executescript("""
1664 -- bla bla
1665 /* a stupid comment */
1666 create table a(i);
1667 insert into a(i) values (5);
1668 """)
1669 cur.execute("select i from a")
1670 res = cur.fetchone()[0]
1671 self.assertEqual(res, 5)
1672
1673 def test_script_syntax_error(self):
1674 with self.assertRaises(sqlite.OperationalError):
1675 self.cur.executescript("""
1676 CREATE TABLE test(x);
1677 asdf;
1678 CREATE TABLE test2(x)
1679 """)
1680
1681 def test_script_error_normal(self):
1682 with self.assertRaises(sqlite.OperationalError):
1683 self.cur.executescript("""
1684 CREATE TABLE test(sadfsadfdsa);
1685 SELECT foo FROM hurz;
1686 """)
1687
1688 def test_cursor_executescript_as_bytes(self):
1689 with self.assertRaises(TypeError):
1690 self.cur.executescript(b"""
1691 CREATE TABLE test(foo);
1692 INSERT INTO test(foo) VALUES (5);
1693 """)
1694
1695 def test_cursor_executescript_with_null_characters(self):
1696 with self.assertRaises(ValueError):
1697 self.cur.executescript("""
1698 CREATE TABLE a(i);\0
1699 INSERT INTO a(i) VALUES (5);
1700 """)
1701
1702 def test_cursor_executescript_with_surrogates(self):
1703 with self.assertRaises(UnicodeEncodeError):
1704 self.cur.executescript("""
1705 CREATE TABLE a(s);
1706 INSERT INTO a(s) VALUES ('\ud8ff');
1707 """)
1708
1709 def test_cursor_executescript_too_large_script(self):
1710 msg = "query string is too large"
1711 with memory_database() as cx, cx_limit(cx) as lim:
1712 cx.executescript("select 'almost too large'".ljust(lim))
1713 with self.assertRaisesRegex(sqlite.DataError, msg):
1714 cx.executescript("select 'too large'".ljust(lim+1))
1715
1716 def test_cursor_executescript_tx_control(self):
1717 con = self.con
1718 con.execute("begin")
1719 self.assertTrue(con.in_transaction)
1720 con.executescript("select 1")
1721 self.assertFalse(con.in_transaction)
1722
1723 def test_connection_execute(self):
1724 result = self.con.execute("select 5").fetchone()[0]
1725 self.assertEqual(result, 5, "Basic test of Connection.execute")
1726
1727 def test_connection_executemany(self):
1728 con = self.con
1729 con.execute("create table test(foo)")
1730 con.executemany("insert into test(foo) values (?)", [(3,), (4,)])
1731 result = con.execute("select foo from test order by foo").fetchall()
1732 self.assertEqual(result[0][0], 3, "Basic test of Connection.executemany")
1733 self.assertEqual(result[1][0], 4, "Basic test of Connection.executemany")
1734
1735 def test_connection_executescript(self):
1736 con = self.con
1737 con.executescript("""
1738 CREATE TABLE test(foo);
1739 INSERT INTO test(foo) VALUES (5);
1740 """)
1741 result = con.execute("select foo from test").fetchone()[0]
1742 self.assertEqual(result, 5, "Basic test of Connection.executescript")
1743
1744
1745 class ClosedConTests(unittest.TestCase):
1746 def check(self, fn, *args, **kwds):
1747 regex = "Cannot operate on a closed database."
1748 with self.assertRaisesRegex(sqlite.ProgrammingError, regex):
1749 fn(*args, **kwds)
1750
1751 def setUp(self):
1752 self.con = sqlite.connect(":memory:")
1753 self.cur = self.con.cursor()
1754 self.con.close()
1755
1756 def test_closed_con_cursor(self):
1757 self.check(self.con.cursor)
1758
1759 def test_closed_con_commit(self):
1760 self.check(self.con.commit)
1761
1762 def test_closed_con_rollback(self):
1763 self.check(self.con.rollback)
1764
1765 def test_closed_cur_execute(self):
1766 self.check(self.cur.execute, "select 4")
1767
1768 def test_closed_create_function(self):
1769 def f(x):
1770 return 17
1771 self.check(self.con.create_function, "foo", 1, f)
1772
1773 def test_closed_create_aggregate(self):
1774 class Agg:
1775 def __init__(self):
1776 pass
1777 def step(self, x):
1778 pass
1779 def finalize(self):
1780 return 17
1781 self.check(self.con.create_aggregate, "foo", 1, Agg)
1782
1783 def test_closed_set_authorizer(self):
1784 def authorizer(*args):
1785 return sqlite.DENY
1786 self.check(self.con.set_authorizer, authorizer)
1787
1788 def test_closed_set_progress_callback(self):
1789 def progress():
1790 pass
1791 self.check(self.con.set_progress_handler, progress, 100)
1792
1793 def test_closed_call(self):
1794 self.check(self.con)
1795
1796
1797 class ClosedCurTests(MemoryDatabaseMixin, unittest.TestCase):
1798 def test_closed(self):
1799 cur = self.cx.cursor()
1800 cur.close()
1801
1802 for method_name in ("execute", "executemany", "executescript", "fetchall", "fetchmany", "fetchone"):
1803 if method_name in ("execute", "executescript"):
1804 params = ("select 4 union select 5",)
1805 elif method_name == "executemany":
1806 params = ("insert into foo(bar) values (?)", [(3,), (4,)])
1807 else:
1808 params = []
1809
1810 with self.assertRaises(sqlite.ProgrammingError):
1811 method = getattr(cur, method_name)
1812 method(*params)
1813
1814
1815 class SqliteOnConflictTests(unittest.TestCase):
1816 """
1817 Tests for SQLite's "insert on conflict" feature.
1818
1819 See https://www.sqlite.org/lang_conflict.html for details.
1820 """
1821
1822 def setUp(self):
1823 self.cx = sqlite.connect(":memory:")
1824 self.cu = self.cx.cursor()
1825 self.cu.execute("""
1826 CREATE TABLE test(
1827 id INTEGER PRIMARY KEY, name TEXT, unique_name TEXT UNIQUE
1828 );
1829 """)
1830
1831 def tearDown(self):
1832 self.cu.close()
1833 self.cx.close()
1834
1835 def test_on_conflict_rollback_with_explicit_transaction(self):
1836 self.cx.isolation_level = None # autocommit mode
1837 self.cu = self.cx.cursor()
1838 # Start an explicit transaction.
1839 self.cu.execute("BEGIN")
1840 self.cu.execute("INSERT INTO test(name) VALUES ('abort_test')")
1841 self.cu.execute("INSERT OR ROLLBACK INTO test(unique_name) VALUES ('foo')")
1842 with self.assertRaises(sqlite.IntegrityError):
1843 self.cu.execute("INSERT OR ROLLBACK INTO test(unique_name) VALUES ('foo')")
1844 # Use connection to commit.
1845 self.cx.commit()
1846 self.cu.execute("SELECT name, unique_name from test")
1847 # Transaction should have rolled back and nothing should be in table.
1848 self.assertEqual(self.cu.fetchall(), [])
1849
1850 def test_on_conflict_abort_raises_with_explicit_transactions(self):
1851 # Abort cancels the current sql statement but doesn't change anything
1852 # about the current transaction.
1853 self.cx.isolation_level = None # autocommit mode
1854 self.cu = self.cx.cursor()
1855 # Start an explicit transaction.
1856 self.cu.execute("BEGIN")
1857 self.cu.execute("INSERT INTO test(name) VALUES ('abort_test')")
1858 self.cu.execute("INSERT OR ABORT INTO test(unique_name) VALUES ('foo')")
1859 with self.assertRaises(sqlite.IntegrityError):
1860 self.cu.execute("INSERT OR ABORT INTO test(unique_name) VALUES ('foo')")
1861 self.cx.commit()
1862 self.cu.execute("SELECT name, unique_name FROM test")
1863 # Expect the first two inserts to work, third to do nothing.
1864 self.assertEqual(self.cu.fetchall(), [('abort_test', None), (None, 'foo',)])
1865
1866 def test_on_conflict_rollback_without_transaction(self):
1867 # Start of implicit transaction
1868 self.cu.execute("INSERT INTO test(name) VALUES ('abort_test')")
1869 self.cu.execute("INSERT OR ROLLBACK INTO test(unique_name) VALUES ('foo')")
1870 with self.assertRaises(sqlite.IntegrityError):
1871 self.cu.execute("INSERT OR ROLLBACK INTO test(unique_name) VALUES ('foo')")
1872 self.cu.execute("SELECT name, unique_name FROM test")
1873 # Implicit transaction is rolled back on error.
1874 self.assertEqual(self.cu.fetchall(), [])
1875
1876 def test_on_conflict_abort_raises_without_transactions(self):
1877 # Abort cancels the current sql statement but doesn't change anything
1878 # about the current transaction.
1879 self.cu.execute("INSERT INTO test(name) VALUES ('abort_test')")
1880 self.cu.execute("INSERT OR ABORT INTO test(unique_name) VALUES ('foo')")
1881 with self.assertRaises(sqlite.IntegrityError):
1882 self.cu.execute("INSERT OR ABORT INTO test(unique_name) VALUES ('foo')")
1883 # Make sure all other values were inserted.
1884 self.cu.execute("SELECT name, unique_name FROM test")
1885 self.assertEqual(self.cu.fetchall(), [('abort_test', None), (None, 'foo',)])
1886
1887 def test_on_conflict_fail(self):
1888 self.cu.execute("INSERT OR FAIL INTO test(unique_name) VALUES ('foo')")
1889 with self.assertRaises(sqlite.IntegrityError):
1890 self.cu.execute("INSERT OR FAIL INTO test(unique_name) VALUES ('foo')")
1891 self.assertEqual(self.cu.fetchall(), [])
1892
1893 def test_on_conflict_ignore(self):
1894 self.cu.execute("INSERT OR IGNORE INTO test(unique_name) VALUES ('foo')")
1895 # Nothing should happen.
1896 self.cu.execute("INSERT OR IGNORE INTO test(unique_name) VALUES ('foo')")
1897 self.cu.execute("SELECT unique_name FROM test")
1898 self.assertEqual(self.cu.fetchall(), [('foo',)])
1899
1900 def test_on_conflict_replace(self):
1901 self.cu.execute("INSERT OR REPLACE INTO test(name, unique_name) VALUES ('Data!', 'foo')")
1902 # There shouldn't be an IntegrityError exception.
1903 self.cu.execute("INSERT OR REPLACE INTO test(name, unique_name) VALUES ('Very different data!', 'foo')")
1904 self.cu.execute("SELECT name, unique_name FROM test")
1905 self.assertEqual(self.cu.fetchall(), [('Very different data!', 'foo')])
1906
1907
1908 @requires_subprocess()
1909 class MultiprocessTests(unittest.TestCase):
1910 CONNECTION_TIMEOUT = 0 # Disable the busy timeout.
1911
1912 def tearDown(self):
1913 unlink(TESTFN)
1914
1915 def test_ctx_mgr_rollback_if_commit_failed(self):
1916 # bpo-27334: ctx manager does not rollback if commit fails
1917 SCRIPT = f"""if 1:
1918 import sqlite3
1919 def wait():
1920 print("started")
1921 assert "database is locked" in input()
1922
1923 cx = sqlite3.connect("{TESTFN}", timeout={self.CONNECTION_TIMEOUT})
1924 cx.create_function("wait", 0, wait)
1925 with cx:
1926 cx.execute("create table t(t)")
1927 try:
1928 # execute two transactions; both will try to lock the db
1929 cx.executescript('''
1930 -- start a transaction and wait for parent
1931 begin transaction;
1932 select * from t;
1933 select wait();
1934 rollback;
1935
1936 -- start a new transaction; would fail if parent holds lock
1937 begin transaction;
1938 select * from t;
1939 rollback;
1940 ''')
1941 finally:
1942 cx.close()
1943 """
1944
1945 # spawn child process
1946 proc = subprocess.Popen(
1947 [sys.executable, "-c", SCRIPT],
1948 encoding="utf-8",
1949 bufsize=0,
1950 stdin=subprocess.PIPE,
1951 stdout=subprocess.PIPE,
1952 )
1953 self.addCleanup(proc.communicate)
1954
1955 # wait for child process to start
1956 self.assertEqual("started", proc.stdout.readline().strip())
1957
1958 cx = sqlite.connect(TESTFN, timeout=self.CONNECTION_TIMEOUT)
1959 try: # context manager should correctly release the db lock
1960 with cx:
1961 cx.execute("insert into t values('test')")
1962 except sqlite.OperationalError as exc:
1963 proc.stdin.write(str(exc))
1964 else:
1965 proc.stdin.write("no error")
1966 finally:
1967 cx.close()
1968
1969 # terminate child process
1970 self.assertIsNone(proc.returncode)
1971 try:
1972 proc.communicate(input="end", timeout=SHORT_TIMEOUT)
1973 except subprocess.TimeoutExpired:
1974 proc.kill()
1975 proc.communicate()
1976 raise
1977 self.assertEqual(proc.returncode, 0)
1978
1979
1980 class RowTests(unittest.TestCase):
1981
1982 def setUp(self):
1983 self.cx = sqlite.connect(":memory:")
1984 self.cx.row_factory = sqlite.Row
1985
1986 def tearDown(self):
1987 self.cx.close()
1988
1989 def test_row_keys(self):
1990 cu = self.cx.execute("SELECT 1 as first, 2 as second")
1991 row = cu.fetchone()
1992 self.assertEqual(row.keys(), ["first", "second"])
1993
1994 def test_row_length(self):
1995 cu = self.cx.execute("SELECT 1, 2, 3")
1996 row = cu.fetchone()
1997 self.assertEqual(len(row), 3)
1998
1999 def test_row_getitem(self):
2000 cu = self.cx.execute("SELECT 1 as a, 2 as b")
2001 row = cu.fetchone()
2002 self.assertEqual(row[0], 1)
2003 self.assertEqual(row[1], 2)
2004 self.assertEqual(row["a"], 1)
2005 self.assertEqual(row["b"], 2)
2006 for key in "nokey", 4, 1.2:
2007 with self.subTest(key=key):
2008 with self.assertRaises(IndexError):
2009 row[key]
2010
2011 def test_row_equality(self):
2012 c1 = self.cx.execute("SELECT 1 as a")
2013 r1 = c1.fetchone()
2014
2015 c2 = self.cx.execute("SELECT 1 as a")
2016 r2 = c2.fetchone()
2017
2018 self.assertIsNot(r1, r2)
2019 self.assertEqual(r1, r2)
2020
2021 c3 = self.cx.execute("SELECT 1 as b")
2022 r3 = c3.fetchone()
2023
2024 self.assertNotEqual(r1, r3)
2025
2026 def test_row_no_description(self):
2027 cu = self.cx.cursor()
2028 self.assertIsNone(cu.description)
2029
2030 row = sqlite.Row(cu, ())
2031 self.assertEqual(row.keys(), [])
2032 with self.assertRaisesRegex(IndexError, "nokey"):
2033 row["nokey"]
2034
2035 def test_row_is_a_sequence(self):
2036 from collections.abc import Sequence
2037
2038 cu = self.cx.execute("SELECT 1")
2039 row = cu.fetchone()
2040
2041 self.assertIsSubclass(sqlite.Row, Sequence)
2042 self.assertIsInstance(row, Sequence)
2043
2044
2045 if __name__ == "__main__":
2046 unittest.main()