1f854799d8438d66d1997e71cb2ea821e29d33472Petri Lehtinen#-*- coding: iso-8859-1 -*- 249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# pysqlite2/test/types.py: tests for type conversion and detection 349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# 449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# Copyright (C) 2005 Gerhard H�ring <gh@ghaering.de> 549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# 649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# This file is part of pysqlite. 749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# 849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# This software is provided 'as-is', without any express or implied 949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# warranty. In no event will the authors be held liable for any damages 1049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# arising from the use of this software. 1149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# 1249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# Permission is granted to anyone to use this software for any purpose, 1349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# including commercial applications, and to alter it and redistribute it 1449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# freely, subject to the following restrictions: 1549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# 1649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# 1. The origin of this software must not be misrepresented; you must not 1749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# claim that you wrote the original software. If you use this software 1849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# in a product, an acknowledgment in the product documentation would be 1949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# appreciated but is not required. 2049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# 2. Altered source versions must be plainly marked as such, and must not be 2149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# misrepresented as being the original software. 2249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters# 3. This notice may not be removed or altered from any source distribution. 2349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 2478ea2023d8aaac460ef921c45a20053159dec613Ezio Melottiimport datetime 2549fd7fa4431da299196d74087df4a04f99f9c46fThomas Woutersimport unittest 2649fd7fa4431da299196d74087df4a04f99f9c46fThomas Woutersimport sqlite3 as sqlite 2778ea2023d8aaac460ef921c45a20053159dec613Ezio Melottitry: 2878ea2023d8aaac460ef921c45a20053159dec613Ezio Melotti import zlib 2978ea2023d8aaac460ef921c45a20053159dec613Ezio Melottiexcept ImportError: 3078ea2023d8aaac460ef921c45a20053159dec613Ezio Melotti zlib = None 3178ea2023d8aaac460ef921c45a20053159dec613Ezio Melotti 3249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 3349fd7fa4431da299196d74087df4a04f99f9c46fThomas Woutersclass SqliteTypeTests(unittest.TestCase): 3449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def setUp(self): 3549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.con = sqlite.connect(":memory:") 3649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur = self.con.cursor() 3749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("create table test(i integer, s varchar, f number, b blob)") 3849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 3949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def tearDown(self): 4049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.close() 4149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.con.close() 4249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 4349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckString(self): 44ef87d6ed94780fe00250a551031023aeb2898365Guido van Rossum self.cur.execute("insert into test(s) values (?)", ("�sterreich",)) 4549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select s from test") 4649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 4704cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], "�sterreich") 4849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 4949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckSmallInt(self): 5049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(i) values (?)", (42,)) 5149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select i from test") 5249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 5304cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], 42) 5449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 5549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckLargeInt(self): 5649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters num = 2**40 5749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(i) values (?)", (num,)) 5849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select i from test") 5949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 6004cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], num) 6149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 6249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckFloat(self): 6349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters val = 3.14 6449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(f) values (?)", (val,)) 6549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select f from test") 6649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 6704cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], val) 6849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 6949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckBlob(self): 7098297ee7815939b124156e438b22bd652d67b5dbGuido van Rossum sample = b"Guglhupf" 7198297ee7815939b124156e438b22bd652d67b5dbGuido van Rossum val = memoryview(sample) 7249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(b) values (?)", (val,)) 7349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select b from test") 7449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 7504cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], sample) 7649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 7749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckUnicodeExecute(self): 78ef87d6ed94780fe00250a551031023aeb2898365Guido van Rossum self.cur.execute("select '�sterreich'") 7949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 8004cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], "�sterreich") 8149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 8249fd7fa4431da299196d74087df4a04f99f9c46fThomas Woutersclass DeclTypesTests(unittest.TestCase): 8349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters class Foo: 8449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def __init__(self, _val): 8598297ee7815939b124156e438b22bd652d67b5dbGuido van Rossum if isinstance(_val, bytes): 8698297ee7815939b124156e438b22bd652d67b5dbGuido van Rossum # sqlite3 always calls __init__ with a bytes created from a 874043001f5d84d4919781e34221449047d0690ac8Brett Cannon # UTF-8 string when __conform__ was used to store the object. 888f36af7a4c9409a673412e4bdfbad76d700abc3aMarc-André Lemburg _val = _val.decode('utf-8') 8949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.val = _val 9049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 9147b9ff6ba11fab4c90556357c437cb4feec1e853Guido van Rossum def __eq__(self, other): 92a60c2fe4807e89a5844979fe46b3ea39572fc3beSerhiy Storchaka if not isinstance(other, DeclTypesTests.Foo): 93a60c2fe4807e89a5844979fe46b3ea39572fc3beSerhiy Storchaka return NotImplemented 94a60c2fe4807e89a5844979fe46b3ea39572fc3beSerhiy Storchaka return self.val == other.val 9547b9ff6ba11fab4c90556357c437cb4feec1e853Guido van Rossum 9649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def __conform__(self, protocol): 9749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters if protocol is sqlite.PrepareProtocol: 9849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters return self.val 9949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters else: 10049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters return None 10149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 10249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def __str__(self): 10349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters return "<%s>" % self.val 10449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 10549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def setUp(self): 10649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES) 10749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur = self.con.cursor() 10881ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes self.cur.execute("create table test(i int, s str, f float, b bool, u unicode, foo foo, bin blob, n1 number, n2 number(5))") 10949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 11049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # override float, make them always return the same number 1110e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters sqlite.converters["FLOAT"] = lambda x: 47.2 11249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 11349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # and implement two custom ones 1140e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters sqlite.converters["BOOL"] = lambda x: bool(int(x)) 1150e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters sqlite.converters["FOO"] = DeclTypesTests.Foo 116fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters sqlite.converters["WRONG"] = lambda x: "WRONG" 11781ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes sqlite.converters["NUMBER"] = float 11849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 11949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def tearDown(self): 1200e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters del sqlite.converters["FLOAT"] 1210e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters del sqlite.converters["BOOL"] 1220e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters del sqlite.converters["FOO"] 12381ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes del sqlite.converters["NUMBER"] 12449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.close() 12549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.con.close() 12649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 12749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckString(self): 12849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # default 12949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(s) values (?)", ("foo",)) 130fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters self.cur.execute('select s as "s [WRONG]" from test') 13149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 13204cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], "foo") 13349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 13449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckSmallInt(self): 13549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # default 13649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(i) values (?)", (42,)) 13749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select i from test") 13849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 13904cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], 42) 14049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 14149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckLargeInt(self): 14249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # default 14349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters num = 2**40 14449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(i) values (?)", (num,)) 14549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select i from test") 14649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 14704cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], num) 14849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 14949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckFloat(self): 15049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # custom 15149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters val = 3.14 15249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(f) values (?)", (val,)) 15349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select f from test") 15449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 15504cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], 47.2) 15649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 15749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckBool(self): 15849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # custom 15949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(b) values (?)", (False,)) 16049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select b from test") 16149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 16204cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], False) 16349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 16449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("delete from test") 16549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(b) values (?)", (True,)) 16649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select b from test") 16749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 16804cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], True) 16949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 17049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckUnicode(self): 17149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # default 172ef87d6ed94780fe00250a551031023aeb2898365Guido van Rossum val = "\xd6sterreich" 17349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(u) values (?)", (val,)) 17449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select u from test") 17549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 17604cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], val) 17749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 17849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckFoo(self): 17949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters val = DeclTypesTests.Foo("bla") 18049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(foo) values (?)", (val,)) 18149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select foo from test") 18249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 18304cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], val) 18449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 18549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckUnsupportedSeq(self): 18649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters class Bar: pass 18749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters val = Bar() 1881003b34c71d53ccb88ae2768aaba503ae6b5bc16Berker Peksag with self.assertRaises(sqlite.InterfaceError): 18949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(f) values (?)", (val,)) 19049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 19149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckUnsupportedDict(self): 19249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters class Bar: pass 19349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters val = Bar() 1941003b34c71d53ccb88ae2768aaba503ae6b5bc16Berker Peksag with self.assertRaises(sqlite.InterfaceError): 19549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(f) values (:val)", {"val": val}) 19649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 19749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckBlob(self): 19849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # default 19998297ee7815939b124156e438b22bd652d67b5dbGuido van Rossum sample = b"Guglhupf" 20098297ee7815939b124156e438b22bd652d67b5dbGuido van Rossum val = memoryview(sample) 20149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(bin) values (?)", (val,)) 20249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select bin from test") 20349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters row = self.cur.fetchone() 20404cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(row[0], sample) 20549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 20681ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes def CheckNumber1(self): 20781ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes self.cur.execute("insert into test(n1) values (5)") 20881ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes value = self.cur.execute("select n1 from test").fetchone()[0] 20981ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes # if the converter is not used, it's an int instead of a float 21004cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(type(value), float) 21181ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes 21281ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes def CheckNumber2(self): 213b5bc353b8893461b0ecdf0e4bc2b299ef2b19bc0Ezio Melotti """Checks whether converter names are cut off at '(' characters""" 21481ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes self.cur.execute("insert into test(n2) values (5)") 21581ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes value = self.cur.execute("select n2 from test").fetchone()[0] 21681ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes # if the converter is not used, it's an int instead of a float 21704cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(type(value), float) 21881ee3efededdaba0a7c0b1a3aac07727cebb6fc1Christian Heimes 21949fd7fa4431da299196d74087df4a04f99f9c46fThomas Woutersclass ColNamesTests(unittest.TestCase): 22049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def setUp(self): 221fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES) 22249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur = self.con.cursor() 22349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("create table test(x foo)") 22449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 225e7ea7451a84636655927da4b9731d2eb37d1cf34Gerhard Häring sqlite.converters["FOO"] = lambda x: "[%s]" % x.decode("ascii") 226e7ea7451a84636655927da4b9731d2eb37d1cf34Gerhard Häring sqlite.converters["BAR"] = lambda x: "<%s>" % x.decode("ascii") 2270e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters sqlite.converters["EXC"] = lambda x: 5/0 228fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters sqlite.converters["B1B1"] = lambda x: "MARKER" 22949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 23049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def tearDown(self): 231e7ea7451a84636655927da4b9731d2eb37d1cf34Gerhard Häring del sqlite.converters["FOO"] 2320e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters del sqlite.converters["BAR"] 2330e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters del sqlite.converters["EXC"] 234fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters del sqlite.converters["B1B1"] 23549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.close() 23649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.con.close() 23749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 238fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters def CheckDeclTypeNotUsed(self): 239fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters """ 240fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters Assures that the declared type is not used when PARSE_DECLTYPES 241fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters is not set. 242fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters """ 24349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(x) values (?)", ("xxx",)) 24449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select x from test") 24549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters val = self.cur.fetchone()[0] 24604cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(val, "xxx") 24749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 24849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckNone(self): 24949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(x) values (?)", (None,)) 25049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select x from test") 25149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters val = self.cur.fetchone()[0] 25204cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(val, None) 25349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 25449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckColName(self): 25549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(x) values (?)", ("xxx",)) 25649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute('select x as "x [bar]" from test') 25749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters val = self.cur.fetchone()[0] 25804cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(val, "<xxx>") 25949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 26049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # Check if the stripping of colnames works. Everything after the first 26149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters # whitespace should be stripped. 26204cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(self.cur.description[0][0], "x") 26349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 264fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters def CheckCaseInConverterName(self): 26598297ee7815939b124156e438b22bd652d67b5dbGuido van Rossum self.cur.execute("select 'other' as \"x [b1b1]\"") 266fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters val = self.cur.fetchone()[0] 26704cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(val, "MARKER") 268fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters 26949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckCursorDescriptionNoRow(self): 27049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters """ 27149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters cursor.description should at least provide the column name(s), even if 27249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters no row returned. 27349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters """ 27449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select * from test where 0 = 1") 275b3aedd48621ed9d33b5f42f946b256bce4a50673Ezio Melotti self.assertEqual(self.cur.description[0][0], "x") 27649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 2776afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag def CheckCursorDescriptionInsert(self): 2786afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur.execute("insert into test values (1)") 2796afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.assertIsNone(self.cur.description) 2806afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag 2816afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag 2826afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag@unittest.skipIf(sqlite.sqlite_version_info < (3, 8, 3), "CTEs not supported") 2836afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksagclass CommonTableExpressionTests(unittest.TestCase): 2846afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag 2856afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag def setUp(self): 2866afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.con = sqlite.connect(":memory:") 2876afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur = self.con.cursor() 2886afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur.execute("create table test(x foo)") 2896afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag 2906afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag def tearDown(self): 2916afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur.close() 2926afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.con.close() 2936afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag 2946afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag def CheckCursorDescriptionCTESimple(self): 2956afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur.execute("with one as (select 1) select * from one") 2966afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.assertIsNotNone(self.cur.description) 2976afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.assertEqual(self.cur.description[0][0], "1") 2986afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag 2996afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag def CheckCursorDescriptionCTESMultipleColumns(self): 3006afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur.execute("insert into test values(1)") 3016afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur.execute("insert into test values(2)") 3026afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur.execute("with testCTE as (select * from test) select * from testCTE") 3036afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.assertIsNotNone(self.cur.description) 3046afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.assertEqual(self.cur.description[0][0], "x") 3056afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag 3066afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag def CheckCursorDescriptionCTE(self): 3076afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur.execute("insert into test values (1)") 3086afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur.execute("with bar as (select * from test) select * from test where x = 1") 3096afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.assertIsNotNone(self.cur.description) 3106afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.assertEqual(self.cur.description[0][0], "x") 3116afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.cur.execute("with bar as (select * from test) select * from test where x = 2") 3126afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.assertIsNotNone(self.cur.description) 3136afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag self.assertEqual(self.cur.description[0][0], "x") 3146afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag 3156afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag 31649fd7fa4431da299196d74087df4a04f99f9c46fThomas Woutersclass ObjectAdaptationTests(unittest.TestCase): 31749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def cast(obj): 31849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters return float(obj) 31949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters cast = staticmethod(cast) 32049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 32149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def setUp(self): 32249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.con = sqlite.connect(":memory:") 32349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters try: 32449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters del sqlite.adapters[int] 32549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters except: 32649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters pass 32749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters sqlite.register_adapter(int, ObjectAdaptationTests.cast) 32849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur = self.con.cursor() 32949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 33049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def tearDown(self): 33149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters del sqlite.adapters[(int, sqlite.PrepareProtocol)] 33249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.close() 33349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.con.close() 33449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 33549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckCasterIsUsed(self): 33649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select ?", (4,)) 33749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters val = self.cur.fetchone()[0] 33804cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(type(val), float) 33949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 34078ea2023d8aaac460ef921c45a20053159dec613Ezio Melotti@unittest.skipUnless(zlib, "requires zlib") 3410e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Woutersclass BinaryConverterTests(unittest.TestCase): 3420e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters def convert(s): 343e7ea7451a84636655927da4b9731d2eb37d1cf34Gerhard Häring return zlib.decompress(s) 3440e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters convert = staticmethod(convert) 3450e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters 3460e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters def setUp(self): 3470e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES) 3480e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters sqlite.register_converter("bin", BinaryConverterTests.convert) 3490e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters 3500e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters def tearDown(self): 3510e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters self.con.close() 3520e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters 3530e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters def CheckBinaryInputForConverter(self): 3546d21456137836b8acd551cf6a51999ad4ff10a91Gerhard Häring testdata = b"abcdefg" * 10 355e7ea7451a84636655927da4b9731d2eb37d1cf34Gerhard Häring result = self.con.execute('select ? as "x [bin]"', (memoryview(zlib.compress(testdata)),)).fetchone()[0] 35604cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(testdata, result) 3570e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters 35849fd7fa4431da299196d74087df4a04f99f9c46fThomas Woutersclass DateTimeTests(unittest.TestCase): 35949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def setUp(self): 36049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES) 36149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur = self.con.cursor() 36249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("create table test(d date, ts timestamp)") 36349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 36449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def tearDown(self): 36549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.close() 36649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.con.close() 36749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 36849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckSqliteDate(self): 36949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters d = sqlite.Date(2004, 2, 14) 37049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(d) values (?)", (d,)) 37149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select d from test") 37249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters d2 = self.cur.fetchone()[0] 37304cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(d, d2) 37449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 37549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckSqliteTimestamp(self): 37649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters ts = sqlite.Timestamp(2004, 2, 14, 7, 15, 0) 37749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(ts) values (?)", (ts,)) 37849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select ts from test") 37949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters ts2 = self.cur.fetchone()[0] 38004cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(ts, ts2) 38149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 382f85bce74db9198498f394cf3b12e3c9edab9fc09Berker Peksag @unittest.skipIf(sqlite.sqlite_version_info < (3, 1), 383f85bce74db9198498f394cf3b12e3c9edab9fc09Berker Peksag 'the date functions are available on 3.1 or later') 38449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckSqlTimestamp(self): 385b47913f54f7b09189f0695144b26e85d87227d3dBerker Peksag now = datetime.datetime.utcnow() 38649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(ts) values (current_timestamp)") 38749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select ts from test") 38849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters ts = self.cur.fetchone()[0] 38904cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(type(ts), datetime.datetime) 39004cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(ts.year, now.year) 39149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 39249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters def CheckDateTimeSubSeconds(self): 39349fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters ts = sqlite.Timestamp(2004, 2, 14, 7, 15, 0, 500000) 39449fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("insert into test(ts) values (?)", (ts,)) 39549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters self.cur.execute("select ts from test") 39649fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters ts2 = self.cur.fetchone()[0] 39704cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(ts, ts2) 39849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 399fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters def CheckDateTimeSubSecondsFloatingPoint(self): 400fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters ts = sqlite.Timestamp(2004, 2, 14, 7, 15, 0, 510241) 401fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters self.cur.execute("insert into test(ts) values (?)", (ts,)) 402fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters self.cur.execute("select ts from test") 403fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters ts2 = self.cur.fetchone()[0] 40404cecafce10abc8906a96aa16c0a7a87d9fb8227Gregory P. Smith self.assertEqual(ts, ts2) 405fc7bb8c786fd9cb3b1ab84e1976620d0ab545777Thomas Wouters 40649fd7fa4431da299196d74087df4a04f99f9c46fThomas Woutersdef suite(): 40749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters sqlite_type_suite = unittest.makeSuite(SqliteTypeTests, "Check") 40849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters decltypes_type_suite = unittest.makeSuite(DeclTypesTests, "Check") 40949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters colnames_type_suite = unittest.makeSuite(ColNamesTests, "Check") 41049fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters adaptation_suite = unittest.makeSuite(ObjectAdaptationTests, "Check") 4110e3f591aeeef9ed715f8770320f4c4c7332a8794Thomas Wouters bin_suite = unittest.makeSuite(BinaryConverterTests, "Check") 41249fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters date_suite = unittest.makeSuite(DateTimeTests, "Check") 4136afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag cte_suite = unittest.makeSuite(CommonTableExpressionTests, "Check") 4146afe85827c209b9d1e76a65ffdb7420b5f46ad3dBerker Peksag return unittest.TestSuite((sqlite_type_suite, decltypes_type_suite, colnames_type_suite, adaptation_suite, bin_suite, date_suite, cte_suite)) 41549fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 41649fd7fa4431da299196d74087df4a04f99f9c46fThomas Woutersdef test(): 41749fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters runner = unittest.TextTestRunner() 41849fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters runner.run(suite()) 41949fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters 42049fd7fa4431da299196d74087df4a04f99f9c46fThomas Woutersif __name__ == "__main__": 42149fd7fa4431da299196d74087df4a04f99f9c46fThomas Wouters test() 422