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_empty_slice_wrong_type(self):
1412 with self.assertRaises(TypeError):
1413 self.blob[5:5] = None
1414
1415 def test_blob_set_empty_slice_wrong_size(self):
1416 with self.assertRaisesRegex(IndexError, "wrong size"):
1417 self.blob[5:5] = b"123"
1418
1419 def test_blob_set_empty_slice_correct(self):
1420 self.blob[5:5] = b""
1421 self.assertEqual(self.blob[:], self.data)
1422
1423 def test_blob_set_slice_with_skip(self):
1424 self.blob[0:10:2] = b"12345"
1425 actual = self.cx.execute("select b from test").fetchone()[0]
1426 expected = b"1h2s3b4o5 " + self.data[10:]
1427 self.assertEqual(actual, expected)
1428
1429 def test_blob_mapping_invalid_index_type(self):
1430 msg = "indices must be integers"
1431 with self.assertRaisesRegex(TypeError, msg):
1432 self.blob[5:5.5]
1433 with self.assertRaisesRegex(TypeError, msg):
1434 self.blob[1.5]
1435 with self.assertRaisesRegex(TypeError, msg):
1436 self.blob["a"] = b"b"
1437
1438 def test_blob_get_item_error(self):
1439 dataset = [len(self.blob), 105, -105]
1440 for idx in dataset:
1441 with self.subTest(idx=idx):
1442 with self.assertRaisesRegex(IndexError, "index out of range"):
1443 self.blob[idx]
1444
1445 # Provoke read error
1446 self.cx.execute("update test set b='aaaa' where rowid=1")
1447 with self.assertRaises(sqlite.OperationalError):
1448 self.blob[0]
1449
1450 def test_blob_get_item_error_bigint(self):
1451 _testcapi = import_helper.import_module("_testcapi")
1452 with self.assertRaisesRegex(IndexError, "cannot fit 'int'"):
1453 self.blob[_testcapi.ULLONG_MAX]
1454
1455 def test_blob_set_item_error(self):
1456 with self.assertRaisesRegex(TypeError, "cannot be interpreted"):
1457 self.blob[0] = b"multiple"
1458 with self.assertRaisesRegex(TypeError, "cannot be interpreted"):
1459 self.blob[0] = b"1"
1460 with self.assertRaisesRegex(TypeError, "cannot be interpreted"):
1461 self.blob[0] = bytearray(b"1")
1462 with self.assertRaisesRegex(TypeError, "doesn't support.*deletion"):
1463 del self.blob[0]
1464 with self.assertRaisesRegex(IndexError, "Blob index out of range"):
1465 self.blob[1000] = 0
1466 with self.assertRaisesRegex(ValueError, "must be in range"):
1467 self.blob[0] = -1
1468 with self.assertRaisesRegex(ValueError, "must be in range"):
1469 self.blob[0] = 256
1470 # Overflow errors are overridden with ValueError
1471 with self.assertRaisesRegex(ValueError, "must be in range"):
1472 self.blob[0] = 2**65
1473
1474 def test_blob_set_slice_error(self):
1475 with self.assertRaisesRegex(IndexError, "wrong size"):
1476 self.blob[5:10] = b"a"
1477 with self.assertRaisesRegex(IndexError, "wrong size"):
1478 self.blob[5:10] = b"a" * 1000
1479 with self.assertRaisesRegex(TypeError, "doesn't support.*deletion"):
1480 del self.blob[5:10]
1481 with self.assertRaisesRegex(ValueError, "step cannot be zero"):
1482 self.blob[5:10:0] = b"12345"
1483 with self.assertRaises(BufferError):
1484 self.blob[5:10] = memoryview(b"abcde")[::2]
1485
1486 def test_blob_sequence_not_supported(self):
1487 with self.assertRaisesRegex(TypeError, "unsupported operand"):
1488 self.blob + self.blob
1489 with self.assertRaisesRegex(TypeError, "unsupported operand"):
1490 self.blob * 5
1491 with self.assertRaisesRegex(TypeError, "is not.+iterable"):
1492 b"a" in self.blob
1493
1494 def test_blob_context_manager(self):
1495 data = b"a" * 50
1496 with self.cx.blobopen("test", "b", 1) as blob:
1497 blob.write(data)
1498 actual = self.cx.execute("select b from test").fetchone()[0]
1499 self.assertEqual(actual, data)
1500
1501 # Check that __exit__ closed the blob
1502 with self.assertRaisesRegex(sqlite.ProgrammingError, "closed blob"):
1503 blob.read()
1504
1505 def test_blob_context_manager_reraise_exceptions(self):
1506 class DummyException(Exception):
1507 pass
1508 with self.assertRaisesRegex(DummyException, "reraised"):
1509 with self.cx.blobopen("test", "b", 1) as blob:
1510 raise DummyException("reraised")
1511
1512
1513 def test_blob_closed(self):
1514 with memory_database() as cx:
1515 cx.execute("create table test(b blob)")
1516 cx.execute("insert into test values (zeroblob(100))")
1517 blob = cx.blobopen("test", "b", 1)
1518 blob.close()
1519
1520 msg = "Cannot operate on a closed blob"
1521 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1522 blob.read()
1523 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1524 blob.write(b"")
1525 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1526 blob.seek(0)
1527 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1528 blob.tell()
1529 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1530 blob.__enter__()
1531 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1532 blob.__exit__(None, None, None)
1533 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1534 len(blob)
1535 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1536 blob[0]
1537 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1538 blob[0:1]
1539 with self.assertRaisesRegex(sqlite.ProgrammingError, msg):
1540 blob[0] = b""
1541
1542 def test_blob_closed_db_read(self):
1543 with memory_database() as cx:
1544 cx.execute("create table test(b blob)")
1545 cx.execute("insert into test(b) values (zeroblob(100))")
1546 blob = cx.blobopen("test", "b", 1)
1547 cx.close()
1548 self.assertRaisesRegex(sqlite.ProgrammingError,
1549 "Cannot operate on a closed database",
1550 blob.read)
1551
1552 def test_blob_32bit_rowid(self):
1553 # gh-100370: we should not get an OverflowError for 32-bit rowids
1554 with memory_database() as cx:
1555 rowid = 2**32
1556 cx.execute("create table t(t blob)")
1557 cx.execute("insert into t(rowid, t) values (?, zeroblob(1))", (rowid,))
1558 cx.blobopen('t', 't', rowid)
1559
1560
1561 @threading_helper.requires_working_threading()
1562 class ThreadTests(unittest.TestCase):
1563 def setUp(self):
1564 self.con = sqlite.connect(":memory:")
1565 self.cur = self.con.cursor()
1566 self.cur.execute("create table test(name text, b blob)")
1567 self.cur.execute("insert into test values('blob', zeroblob(1))")
1568
1569 def tearDown(self):
1570 self.cur.close()
1571 self.con.close()
1572
1573 @threading_helper.reap_threads
1574 def _run_test(self, fn, *args, **kwds):
1575 def run(err):
1576 try:
1577 fn(*args, **kwds)
1578 err.append("did not raise ProgrammingError")
1579 except sqlite.ProgrammingError:
1580 pass
1581 except:
1582 err.append("raised wrong exception")
1583
1584 err = []
1585 t = threading.Thread(target=run, kwargs={"err": err})
1586 t.start()
1587 t.join()
1588 if err:
1589 self.fail("\n".join(err))
1590
1591 def test_check_connection_thread(self):
1592 fns = [
1593 lambda: self.con.cursor(),
1594 lambda: self.con.commit(),
1595 lambda: self.con.rollback(),
1596 lambda: self.con.close(),
1597 lambda: self.con.set_trace_callback(None),
1598 lambda: self.con.set_authorizer(None),
1599 lambda: self.con.create_collation("foo", None),
1600 lambda: self.con.setlimit(sqlite.SQLITE_LIMIT_LENGTH, -1),
1601 lambda: self.con.getlimit(sqlite.SQLITE_LIMIT_LENGTH),
1602 lambda: self.con.blobopen("test", "b", 1),
1603 ]
1604 if hasattr(sqlite.Connection, "serialize"):
1605 fns.append(lambda: self.con.serialize())
1606 fns.append(lambda: self.con.deserialize(b""))
1607 if sqlite.sqlite_version_info >= (3, 25, 0):
1608 fns.append(lambda: self.con.create_window_function("foo", 0, None))
1609
1610 for fn in fns:
1611 with self.subTest(fn=fn):
1612 self._run_test(fn)
1613
1614 def test_check_cursor_thread(self):
1615 fns = [
1616 lambda: self.cur.execute("insert into test(name) values('a')"),
1617 lambda: self.cur.close(),
1618 lambda: self.cur.execute("select name from test"),
1619 lambda: self.cur.fetchone(),
1620 ]
1621 for fn in fns:
1622 with self.subTest(fn=fn):
1623 self._run_test(fn)
1624
1625
1626 @threading_helper.reap_threads
1627 def test_dont_check_same_thread(self):
1628 def run(con, err):
1629 try:
1630 con.execute("select 1")
1631 except sqlite.Error:
1632 err.append("multi-threading not allowed")
1633
1634 with memory_database(check_same_thread=False) as con:
1635 err = []
1636 t = threading.Thread(target=run, kwargs={"con": con, "err": err})
1637 t.start()
1638 t.join()
1639 self.assertEqual(len(err), 0, "\n".join(err))
1640
1641
1642 class ConstructorTests(unittest.TestCase):
1643 def test_date(self):
1644 d = sqlite.Date(2004, 10, 28)
1645
1646 def test_time(self):
1647 t = sqlite.Time(12, 39, 35)
1648
1649 def test_timestamp(self):
1650 ts = sqlite.Timestamp(2004, 10, 28, 12, 39, 35)
1651
1652 def test_date_from_ticks(self):
1653 d = sqlite.DateFromTicks(42)
1654
1655 def test_time_from_ticks(self):
1656 t = sqlite.TimeFromTicks(42)
1657
1658 def test_timestamp_from_ticks(self):
1659 ts = sqlite.TimestampFromTicks(42)
1660
1661 def test_binary(self):
1662 b = sqlite.Binary(b"\0'")
1663
1664 class ExtensionTests(unittest.TestCase):
1665 def setUp(self):
1666 self.con = sqlite.connect(":memory:")
1667 self.cur = self.con.cursor()
1668
1669 def tearDown(self):
1670 self.cur.close()
1671 self.con.close()
1672
1673 def test_script_string_sql(self):
1674 cur = self.cur
1675 cur.executescript("""
1676 -- bla bla
1677 /* a stupid comment */
1678 create table a(i);
1679 insert into a(i) values (5);
1680 """)
1681 cur.execute("select i from a")
1682 res = cur.fetchone()[0]
1683 self.assertEqual(res, 5)
1684
1685 def test_script_syntax_error(self):
1686 with self.assertRaises(sqlite.OperationalError):
1687 self.cur.executescript("""
1688 CREATE TABLE test(x);
1689 asdf;
1690 CREATE TABLE test2(x)
1691 """)
1692
1693 def test_script_error_normal(self):
1694 with self.assertRaises(sqlite.OperationalError):
1695 self.cur.executescript("""
1696 CREATE TABLE test(sadfsadfdsa);
1697 SELECT foo FROM hurz;
1698 """)
1699
1700 def test_cursor_executescript_as_bytes(self):
1701 with self.assertRaises(TypeError):
1702 self.cur.executescript(b"""
1703 CREATE TABLE test(foo);
1704 INSERT INTO test(foo) VALUES (5);
1705 """)
1706
1707 def test_cursor_executescript_with_null_characters(self):
1708 with self.assertRaises(ValueError):
1709 self.cur.executescript("""
1710 CREATE TABLE a(i);\0
1711 INSERT INTO a(i) VALUES (5);
1712 """)
1713
1714 def test_cursor_executescript_with_surrogates(self):
1715 with self.assertRaises(UnicodeEncodeError):
1716 self.cur.executescript("""
1717 CREATE TABLE a(s);
1718 INSERT INTO a(s) VALUES ('\ud8ff');
1719 """)
1720
1721 def test_cursor_executescript_too_large_script(self):
1722 msg = "query string is too large"
1723 with memory_database() as cx, cx_limit(cx) as lim:
1724 cx.executescript("select 'almost too large'".ljust(lim))
1725 with self.assertRaisesRegex(sqlite.DataError, msg):
1726 cx.executescript("select 'too large'".ljust(lim+1))
1727
1728 def test_cursor_executescript_tx_control(self):
1729 con = self.con
1730 con.execute("begin")
1731 self.assertTrue(con.in_transaction)
1732 con.executescript("select 1")
1733 self.assertFalse(con.in_transaction)
1734
1735 def test_connection_execute(self):
1736 result = self.con.execute("select 5").fetchone()[0]
1737 self.assertEqual(result, 5, "Basic test of Connection.execute")
1738
1739 def test_connection_executemany(self):
1740 con = self.con
1741 con.execute("create table test(foo)")
1742 con.executemany("insert into test(foo) values (?)", [(3,), (4,)])
1743 result = con.execute("select foo from test order by foo").fetchall()
1744 self.assertEqual(result[0][0], 3, "Basic test of Connection.executemany")
1745 self.assertEqual(result[1][0], 4, "Basic test of Connection.executemany")
1746
1747 def test_connection_executescript(self):
1748 con = self.con
1749 con.executescript("""
1750 CREATE TABLE test(foo);
1751 INSERT INTO test(foo) VALUES (5);
1752 """)
1753 result = con.execute("select foo from test").fetchone()[0]
1754 self.assertEqual(result, 5, "Basic test of Connection.executescript")
1755
1756
1757 class ClosedConTests(unittest.TestCase):
1758 def check(self, fn, *args, **kwds):
1759 regex = "Cannot operate on a closed database."
1760 with self.assertRaisesRegex(sqlite.ProgrammingError, regex):
1761 fn(*args, **kwds)
1762
1763 def setUp(self):
1764 self.con = sqlite.connect(":memory:")
1765 self.cur = self.con.cursor()
1766 self.con.close()
1767
1768 def test_closed_con_cursor(self):
1769 self.check(self.con.cursor)
1770
1771 def test_closed_con_commit(self):
1772 self.check(self.con.commit)
1773
1774 def test_closed_con_rollback(self):
1775 self.check(self.con.rollback)
1776
1777 def test_closed_cur_execute(self):
1778 self.check(self.cur.execute, "select 4")
1779
1780 def test_closed_create_function(self):
1781 def f(x):
1782 return 17
1783 self.check(self.con.create_function, "foo", 1, f)
1784
1785 def test_closed_create_aggregate(self):
1786 class Agg:
1787 def __init__(self):
1788 pass
1789 def step(self, x):
1790 pass
1791 def finalize(self):
1792 return 17
1793 self.check(self.con.create_aggregate, "foo", 1, Agg)
1794
1795 def test_closed_set_authorizer(self):
1796 def authorizer(*args):
1797 return sqlite.DENY
1798 self.check(self.con.set_authorizer, authorizer)
1799
1800 def test_closed_set_progress_callback(self):
1801 def progress():
1802 pass
1803 self.check(self.con.set_progress_handler, progress, 100)
1804
1805 def test_closed_call(self):
1806 self.check(self.con)
1807
1808
1809 class ClosedCurTests(MemoryDatabaseMixin, unittest.TestCase):
1810 def test_closed(self):
1811 cur = self.cx.cursor()
1812 cur.close()
1813
1814 for method_name in ("execute", "executemany", "executescript", "fetchall", "fetchmany", "fetchone"):
1815 if method_name in ("execute", "executescript"):
1816 params = ("select 4 union select 5",)
1817 elif method_name == "executemany":
1818 params = ("insert into foo(bar) values (?)", [(3,), (4,)])
1819 else:
1820 params = []
1821
1822 with self.assertRaises(sqlite.ProgrammingError):
1823 method = getattr(cur, method_name)
1824 method(*params)
1825
1826
1827 class SqliteOnConflictTests(unittest.TestCase):
1828 """
1829 Tests for SQLite's "insert on conflict" feature.
1830
1831 See https://www.sqlite.org/lang_conflict.html for details.
1832 """
1833
1834 def setUp(self):
1835 self.cx = sqlite.connect(":memory:")
1836 self.cu = self.cx.cursor()
1837 self.cu.execute("""
1838 CREATE TABLE test(
1839 id INTEGER PRIMARY KEY, name TEXT, unique_name TEXT UNIQUE
1840 );
1841 """)
1842
1843 def tearDown(self):
1844 self.cu.close()
1845 self.cx.close()
1846
1847 def test_on_conflict_rollback_with_explicit_transaction(self):
1848 self.cx.isolation_level = None # autocommit mode
1849 self.cu = self.cx.cursor()
1850 # Start an explicit transaction.
1851 self.cu.execute("BEGIN")
1852 self.cu.execute("INSERT INTO test(name) VALUES ('abort_test')")
1853 self.cu.execute("INSERT OR ROLLBACK INTO test(unique_name) VALUES ('foo')")
1854 with self.assertRaises(sqlite.IntegrityError):
1855 self.cu.execute("INSERT OR ROLLBACK INTO test(unique_name) VALUES ('foo')")
1856 # Use connection to commit.
1857 self.cx.commit()
1858 self.cu.execute("SELECT name, unique_name from test")
1859 # Transaction should have rolled back and nothing should be in table.
1860 self.assertEqual(self.cu.fetchall(), [])
1861
1862 def test_on_conflict_abort_raises_with_explicit_transactions(self):
1863 # Abort cancels the current sql statement but doesn't change anything
1864 # about the current transaction.
1865 self.cx.isolation_level = None # autocommit mode
1866 self.cu = self.cx.cursor()
1867 # Start an explicit transaction.
1868 self.cu.execute("BEGIN")
1869 self.cu.execute("INSERT INTO test(name) VALUES ('abort_test')")
1870 self.cu.execute("INSERT OR ABORT INTO test(unique_name) VALUES ('foo')")
1871 with self.assertRaises(sqlite.IntegrityError):
1872 self.cu.execute("INSERT OR ABORT INTO test(unique_name) VALUES ('foo')")
1873 self.cx.commit()
1874 self.cu.execute("SELECT name, unique_name FROM test")
1875 # Expect the first two inserts to work, third to do nothing.
1876 self.assertEqual(self.cu.fetchall(), [('abort_test', None), (None, 'foo',)])
1877
1878 def test_on_conflict_rollback_without_transaction(self):
1879 # Start of implicit transaction
1880 self.cu.execute("INSERT INTO test(name) VALUES ('abort_test')")
1881 self.cu.execute("INSERT OR ROLLBACK INTO test(unique_name) VALUES ('foo')")
1882 with self.assertRaises(sqlite.IntegrityError):
1883 self.cu.execute("INSERT OR ROLLBACK INTO test(unique_name) VALUES ('foo')")
1884 self.cu.execute("SELECT name, unique_name FROM test")
1885 # Implicit transaction is rolled back on error.
1886 self.assertEqual(self.cu.fetchall(), [])
1887
1888 def test_on_conflict_abort_raises_without_transactions(self):
1889 # Abort cancels the current sql statement but doesn't change anything
1890 # about the current transaction.
1891 self.cu.execute("INSERT INTO test(name) VALUES ('abort_test')")
1892 self.cu.execute("INSERT OR ABORT INTO test(unique_name) VALUES ('foo')")
1893 with self.assertRaises(sqlite.IntegrityError):
1894 self.cu.execute("INSERT OR ABORT INTO test(unique_name) VALUES ('foo')")
1895 # Make sure all other values were inserted.
1896 self.cu.execute("SELECT name, unique_name FROM test")
1897 self.assertEqual(self.cu.fetchall(), [('abort_test', None), (None, 'foo',)])
1898
1899 def test_on_conflict_fail(self):
1900 self.cu.execute("INSERT OR FAIL INTO test(unique_name) VALUES ('foo')")
1901 with self.assertRaises(sqlite.IntegrityError):
1902 self.cu.execute("INSERT OR FAIL INTO test(unique_name) VALUES ('foo')")
1903 self.assertEqual(self.cu.fetchall(), [])
1904
1905 def test_on_conflict_ignore(self):
1906 self.cu.execute("INSERT OR IGNORE INTO test(unique_name) VALUES ('foo')")
1907 # Nothing should happen.
1908 self.cu.execute("INSERT OR IGNORE INTO test(unique_name) VALUES ('foo')")
1909 self.cu.execute("SELECT unique_name FROM test")
1910 self.assertEqual(self.cu.fetchall(), [('foo',)])
1911
1912 def test_on_conflict_replace(self):
1913 self.cu.execute("INSERT OR REPLACE INTO test(name, unique_name) VALUES ('Data!', 'foo')")
1914 # There shouldn't be an IntegrityError exception.
1915 self.cu.execute("INSERT OR REPLACE INTO test(name, unique_name) VALUES ('Very different data!', 'foo')")
1916 self.cu.execute("SELECT name, unique_name FROM test")
1917 self.assertEqual(self.cu.fetchall(), [('Very different data!', 'foo')])
1918
1919
1920 @requires_subprocess()
1921 class MultiprocessTests(unittest.TestCase):
1922 CONNECTION_TIMEOUT = 0 # Disable the busy timeout.
1923
1924 def tearDown(self):
1925 unlink(TESTFN)
1926
1927 def test_ctx_mgr_rollback_if_commit_failed(self):
1928 # bpo-27334: ctx manager does not rollback if commit fails
1929 SCRIPT = f"""if 1:
1930 import sqlite3
1931 def wait():
1932 print("started")
1933 assert "database is locked" in input()
1934
1935 cx = sqlite3.connect("{TESTFN}", timeout={self.CONNECTION_TIMEOUT})
1936 cx.create_function("wait", 0, wait)
1937 with cx:
1938 cx.execute("create table t(t)")
1939 try:
1940 # execute two transactions; both will try to lock the db
1941 cx.executescript('''
1942 -- start a transaction and wait for parent
1943 begin transaction;
1944 select * from t;
1945 select wait();
1946 rollback;
1947
1948 -- start a new transaction; would fail if parent holds lock
1949 begin transaction;
1950 select * from t;
1951 rollback;
1952 ''')
1953 finally:
1954 cx.close()
1955 """
1956
1957 # spawn child process
1958 proc = subprocess.Popen(
1959 [sys.executable, "-c", SCRIPT],
1960 encoding="utf-8",
1961 bufsize=0,
1962 stdin=subprocess.PIPE,
1963 stdout=subprocess.PIPE,
1964 )
1965 self.addCleanup(proc.communicate)
1966
1967 # wait for child process to start
1968 self.assertEqual("started", proc.stdout.readline().strip())
1969
1970 cx = sqlite.connect(TESTFN, timeout=self.CONNECTION_TIMEOUT)
1971 try: # context manager should correctly release the db lock
1972 with cx:
1973 cx.execute("insert into t values('test')")
1974 except sqlite.OperationalError as exc:
1975 proc.stdin.write(str(exc))
1976 else:
1977 proc.stdin.write("no error")
1978 finally:
1979 cx.close()
1980
1981 # terminate child process
1982 self.assertIsNone(proc.returncode)
1983 try:
1984 proc.communicate(input="end", timeout=SHORT_TIMEOUT)
1985 except subprocess.TimeoutExpired:
1986 proc.kill()
1987 proc.communicate()
1988 raise
1989 self.assertEqual(proc.returncode, 0)
1990
1991
1992 class RowTests(unittest.TestCase):
1993
1994 def setUp(self):
1995 self.cx = sqlite.connect(":memory:")
1996 self.cx.row_factory = sqlite.Row
1997
1998 def tearDown(self):
1999 self.cx.close()
2000
2001 def test_row_keys(self):
2002 cu = self.cx.execute("SELECT 1 as first, 2 as second")
2003 row = cu.fetchone()
2004 self.assertEqual(row.keys(), ["first", "second"])
2005
2006 def test_row_length(self):
2007 cu = self.cx.execute("SELECT 1, 2, 3")
2008 row = cu.fetchone()
2009 self.assertEqual(len(row), 3)
2010
2011 def test_row_getitem(self):
2012 cu = self.cx.execute("SELECT 1 as a, 2 as b")
2013 row = cu.fetchone()
2014 self.assertEqual(row[0], 1)
2015 self.assertEqual(row[1], 2)
2016 self.assertEqual(row["a"], 1)
2017 self.assertEqual(row["b"], 2)
2018 for key in "nokey", 4, 1.2:
2019 with self.subTest(key=key):
2020 with self.assertRaises(IndexError):
2021 row[key]
2022
2023 def test_row_equality(self):
2024 c1 = self.cx.execute("SELECT 1 as a")
2025 r1 = c1.fetchone()
2026
2027 c2 = self.cx.execute("SELECT 1 as a")
2028 r2 = c2.fetchone()
2029
2030 self.assertIsNot(r1, r2)
2031 self.assertEqual(r1, r2)
2032
2033 c3 = self.cx.execute("SELECT 1 as b")
2034 r3 = c3.fetchone()
2035
2036 self.assertNotEqual(r1, r3)
2037
2038 def test_row_no_description(self):
2039 cu = self.cx.cursor()
2040 self.assertIsNone(cu.description)
2041
2042 row = sqlite.Row(cu, ())
2043 self.assertEqual(row.keys(), [])
2044 with self.assertRaisesRegex(IndexError, "nokey"):
2045 row["nokey"]
2046
2047 def test_row_is_a_sequence(self):
2048 from collections.abc import Sequence
2049
2050 cu = self.cx.execute("SELECT 1")
2051 row = cu.fetchone()
2052
2053 self.assertIsSubclass(sqlite.Row, Sequence)
2054 self.assertIsInstance(row, Sequence)
2055
2056
2057 if __name__ == "__main__":
2058 unittest.main()