CoCalc -- test_connection.py

GitHub Repository: singlestore-labs/singlestoredb-python
Path: blob/main/singlestoredb/tests/test_connection.py
⁴⁶⁹ views
1
#!/usr/bin/env python
2
# type: ignore
3
"""Basic SingleStoreDB connection testing."""
4
import datetime
5
import decimal
6
import os
7
import unittest
8
import uuid
9

10
import singlestoredb as s2
11
from singlestoredb import connection as sc
12
from singlestoredb.tests import utils
13
# import pandas as pd
14
# import traceback
15

16
try:
17
    import numpy as np
18
    has_numpy = True
19
except ImportError:
20
    has_numpy = False
21

22
try:
23
    import pandas as pd
24
    has_pandas = True
25
except ImportError:
26
    has_pandas = False
27

28

29
class TestConnection(unittest.TestCase):
30

31
    dbname: str = ''
32
    dbexisted: bool = False
33

34
    @classmethod
35
    def setUpClass(cls):
36
        sql_file = os.path.join(os.path.dirname(__file__), 'test.sql')
37
        cls.dbname, cls.dbexisted = utils.load_sql(sql_file)
38

39
    @classmethod
40
    def tearDownClass(cls):
41
        if not cls.dbexisted:
42
            utils.drop_database(cls.dbname)
43

44
    def setUp(self):
45
        self.conn = s2.connect(database=type(self).dbname, local_infile=True)
46
        self.cur = self.conn.cursor()
47

48
    def tearDown(self):
49
        try:
50
            if self.cur is not None:
51
                self.cur.close()
52
        except Exception:
53
            # traceback.print_exc()
54
            pass
55

56
        try:
57
            if self.conn is not None:
58
                self.conn.close()
59
        except Exception:
60
            # traceback.print_exc()
61
            pass
62

63
    def test_connection(self):
64
        self.cur.execute('show databases')
65
        dbs = set([x[0] for x in self.cur.fetchall()])
66
        assert type(self).dbname in dbs, dbs
67

68
    def test_cast_bool_param(self):
69
        cbp = sc.cast_bool_param
70

71
        assert cbp(0) is False, cbp(0)
72
        assert cbp(1) is True, cbp(1)
73
        with self.assertRaises(ValueError):
74
            cbp(10)
75

76
        assert cbp(True) is True, cbp(True)
77
        assert cbp(False) is False, cbp(False)
78
        assert cbp(None) is False, cbp(None)
79

80
        assert cbp('true') is True, cbp('true')
81
        assert cbp('t') is True, cbp('t')
82
        assert cbp('True') is True, cbp('True')
83
        assert cbp('T') is True, cbp('T')
84
        assert cbp('TRUE') is True, cbp('TRUE')
85

86
        assert cbp('on') is True, cbp('on')
87
        assert cbp('yes') is True, cbp('yes')
88
        assert cbp('enable') is True, cbp('enable')
89
        assert cbp('enabled') is True, cbp('enabled')
90

91
        assert cbp('false') is False, cbp('false')
92
        assert cbp('f') is False, cbp('f')
93
        assert cbp('False') is False, cbp('False')
94
        assert cbp('F') is False, cbp('F')
95
        assert cbp('FALSE') is False, cbp('FALSE')
96

97
        assert cbp('off') is False, cbp('off')
98
        assert cbp('no') is False, cbp('no')
99
        assert cbp('disable') is False, cbp('disable')
100
        assert cbp('disabled') is False, cbp('disabled')
101

102
        with self.assertRaises(ValueError):
103
            cbp('nein')
104

105
        with self.assertRaises(ValueError):
106
            cbp(b'no')
107

108
        with self.assertRaises(ValueError):
109
            cbp(['no'])
110

111
    def test_fetchall(self):
112
        self.cur.execute('select * from data')
113

114
        out = self.cur.fetchall()
115

116
        desc = self.cur.description
117
        rowcount = self.cur.rowcount
118
        rownumber = self.cur.rownumber
119
        lastrowid = self.cur.lastrowid
120

121
        assert sorted(out) == sorted([
122
            ('a', 'antelopes', 2),
123
            ('b', 'bears', 2),
124
            ('c', 'cats', 5),
125
            ('d', 'dogs', 4),
126
            ('e', 'elephants', 0),
127
        ]), out
128

129
        assert rowcount in (5, -1), rowcount
130
        assert rownumber == 5, rownumber
131
        assert lastrowid is None, lastrowid
132
        assert len(desc) == 3, desc
133
        assert desc[0].name == 'id', desc[0].name
134
        assert desc[0].type_code in [253, 15], desc[0].type_code
135
        assert desc[1].name == 'name', desc[1].name
136
        assert desc[1].type_code in [253, 15], desc[1].type_code
137
        assert desc[2].name == 'value', desc[2].name
138
        assert desc[2].type_code == 8, desc[2].type_code
139

140
    def test_fetchone(self):
141
        self.cur.execute('select * from data')
142

143
        out = []
144
        while True:
145
            row = self.cur.fetchone()
146
            if row is None:
147
                break
148
            out.append(row)
149
            assert self.cur.rownumber == len(out), self.cur.rownumber
150

151
        desc = self.cur.description
152
        rowcount = self.cur.rowcount
153
        rownumber = self.cur.rownumber
154
        lastrowid = self.cur.lastrowid
155

156
        assert sorted(out) == sorted([
157
            ('a', 'antelopes', 2),
158
            ('b', 'bears', 2),
159
            ('c', 'cats', 5),
160
            ('d', 'dogs', 4),
161
            ('e', 'elephants', 0),
162
        ]), out
163

164
        assert rowcount in (5, -1), rowcount
165
        assert rownumber == 5, rownumber
166
        assert lastrowid is None, lastrowid
167
        assert len(desc) == 3, desc
168
        assert desc[0].name == 'id', desc[0].name
169
        assert desc[0].type_code in [253, 15], desc[0].type_code
170
        assert desc[1].name == 'name', desc[1].name
171
        assert desc[1].type_code in [253, 15], desc[1].type_code
172
        assert desc[2].name == 'value', desc[2].name
173
        assert desc[2].type_code == 8, desc[2].type_code
174

175
    def test_fetchmany(self):
176
        self.cur.execute('select * from data')
177

178
        out = []
179
        while True:
180
            rows = self.cur.fetchmany(size=3)
181
            assert len(rows) <= 3, rows
182
            if not rows:
183
                break
184
            out.extend(rows)
185
            assert self.cur.rownumber == len(out), self.cur.rownumber
186

187
        desc = self.cur.description
188
        rowcount = self.cur.rowcount
189
        rownumber = self.cur.rownumber
190
        lastrowid = self.cur.lastrowid
191

192
        assert sorted(out) == sorted([
193
            ('a', 'antelopes', 2),
194
            ('b', 'bears', 2),
195
            ('c', 'cats', 5),
196
            ('d', 'dogs', 4),
197
            ('e', 'elephants', 0),
198
        ]), out
199

200
        assert rowcount in (5, -1), rowcount
201
        assert rownumber == 5, rownumber
202
        assert lastrowid is None, lastrowid
203
        assert len(desc) == 3, desc
204
        assert desc[0].name == 'id'
205
        assert desc[0].type_code in [253, 15]
206
        assert desc[1].name == 'name'
207
        assert desc[1].type_code in [253, 15]
208
        assert desc[2].name == 'value'
209
        assert desc[2].type_code == 8
210

211
    def test_arraysize(self):
212
        self.cur.execute('select * from data')
213

214
        self.cur.arraysize = 3
215
        assert self.cur.arraysize == 3
216

217
        rows = self.cur.fetchmany()
218
        assert len(rows) == 3, rows
219
        assert self.cur.rownumber == 3, self.cur.rownumber
220

221
        self.cur.arraysize = 1
222
        assert self.cur.arraysize == 1
223

224
        rows = self.cur.fetchmany()
225
        assert len(rows) == 1, rows
226
        assert self.cur.rownumber == 4, self.cur.rownumber
227

228
        rows = self.cur.fetchmany()
229
        assert len(rows) == 1, rows
230
        assert self.cur.rownumber == 5, self.cur.rownumber
231

232
        rows = self.cur.fetchall()
233
        assert len(rows) == 0, rows
234
        assert self.cur.rownumber == 5, self.cur.rownumber
235

236
    def test_execute_with_dict_params(self):
237
        self.cur.execute('select * from data where id < %(name)s', dict(name='d'))
238
        out = self.cur.fetchall()
239

240
        desc = self.cur.description
241
        rowcount = self.cur.rowcount
242
        lastrowid = self.cur.lastrowid
243

244
        assert sorted(out) == sorted([
245
            ('a', 'antelopes', 2),
246
            ('b', 'bears', 2),
247
            ('c', 'cats', 5),
248
        ]), out
249

250
        assert rowcount in (3, -1), rowcount
251
        assert lastrowid is None, lastrowid
252
        assert len(desc) == 3, desc
253
        assert desc[0].name == 'id', desc[0].name
254
        assert desc[0].type_code in [253, 15], desc[0].type_code
255
        assert desc[1].name == 'name', desc[1].name
256
        assert desc[1].type_code in [253, 15], desc[1].type_code
257
        assert desc[2].name == 'value', desc[2].name
258
        assert desc[2].type_code == 8, desc[2].type_code
259

260
    def test_execute_with_positional_params(self):
261
        self.cur.execute('select * from data where id < %s', ['d'])
262
        out = self.cur.fetchall()
263

264
        desc = self.cur.description
265
        rowcount = self.cur.rowcount
266
        lastrowid = self.cur.lastrowid
267

268
        assert sorted(out) == sorted([
269
            ('a', 'antelopes', 2),
270
            ('b', 'bears', 2),
271
            ('c', 'cats', 5),
272
        ]), out
273

274
        assert rowcount in (3, -1), rowcount
275
        assert lastrowid is None, lastrowid
276
        assert len(desc) == 3, desc
277
        assert desc[0].name == 'id', desc[0].name
278
        assert desc[0].type_code in [253, 15], desc[0].type_code
279
        assert desc[1].name == 'name', desc[1].name
280
        assert desc[1].type_code in [253, 15], desc[1].type_code
281
        assert desc[2].name == 'value', desc[2].name
282
        assert desc[2].type_code == 8, desc[2].type_code
283

284
    def test_execute_with_escaped_positional_substitutions(self):
285
        self.cur.execute(
286
            'select `id`, `time` from alltypes where `time` = %s', [
287
                '00:07:00',
288
            ],
289
        )
290
        out = self.cur.fetchall()
291
        assert out[0] == (0, datetime.timedelta(seconds=420)), out[0]
292

293
        self.cur.execute(
294
            'select `id`, `time` from alltypes where `time` = "00:07:00"',
295
        )
296
        out = self.cur.fetchall()
297
        assert out[0] == (0, datetime.timedelta(seconds=420)), out[0]
298

299
#       with self.assertRaises(IndexError):
300
#           self.cur.execute(
301
#               'select `id`, `time` from alltypes where `id` = %1s '
302
#               'or `time` = "00:07:00"', [0],
303
#           )
304

305
        self.cur.execute(
306
            'select `id`, `time` from alltypes where `id` = %s '
307
            'or `time` = "00:07:00"', [0],
308
        )
309
        out = self.cur.fetchall()
310
        assert out[0] == (0, datetime.timedelta(seconds=420)), out[0]
311

312
    def test_execute_with_escaped_substitutions(self):
313
        self.cur.execute(
314
            'select `id`, `time` from alltypes where `time` = %(time)s',
315
            dict(time='00:07:00'),
316
        )
317
        out = self.cur.fetchall()
318
        assert out[0] == (0, datetime.timedelta(seconds=420)), out[0]
319

320
        self.cur.execute(
321
            'select `id`, `time` from alltypes where `time` = %(time)s',
322
            dict(time='00:07:00'),
323
        )
324
        out = self.cur.fetchall()
325
        assert len(out) == 1, out
326

327
        with self.assertRaises(KeyError):
328
            self.cur.execute(
329
                'select `id`, `time`, `char_100` from alltypes '
330
                'where `time` = %(time)s or `char_100` like "foo:bar"',
331
                dict(x='00:07:00'),
332
            )
333

334
        self.cur.execute(
335
            'select `id`, `time`, `char_100` from alltypes '
336
            'where `time` = %(time)s or `char_100` like "foo::bar"',
337
            dict(time='00:07:00'),
338
        )
339
        out = self.cur.fetchall()
340
        assert out[0][:2] == (0, datetime.timedelta(seconds=420)), out[0]
341

342
    def test_is_connected(self):
343
        assert self.conn.is_connected()
344
        assert self.cur.is_connected()
345
        self.cur.close()
346
        assert not self.cur.is_connected()
347
        assert self.conn.is_connected()
348
        self.conn.close()
349
        assert not self.cur.is_connected()
350
        assert not self.conn.is_connected()
351

352
    def test_connection_attr(self):
353
        # Use context manager to get to underlying object (self.conn is a weakref.proxy)
354
        with self.conn as conn:
355
            assert conn is self.conn
356

357
    def test_executemany(self):
358
        # NOTE: Doesn't actually do anything since no rows match
359
        self.cur.executemany(
360
            'delete from data where id > %(name)s',
361
            [dict(name='z'), dict(name='y')],
362
        )
363

364
    def test_executemany_no_args(self):
365
        self.cur.executemany('select * from data where id > "z"')
366

367
    def test_context_managers(self):
368
        with s2.connect() as conn:
369
            with conn.cursor() as cur:
370
                assert cur.is_connected()
371
                assert conn.is_connected()
372
        assert not cur.is_connected()
373
        assert not conn.is_connected()
374

375
    def test_iterator(self):
376
        self.cur.execute('select * from data')
377

378
        out = []
379
        for row in self.cur:
380
            out.append(row)
381

382
        assert sorted(out) == sorted([
383
            ('a', 'antelopes', 2),
384
            ('b', 'bears', 2),
385
            ('c', 'cats', 5),
386
            ('d', 'dogs', 4),
387
            ('e', 'elephants', 0),
388
        ]), out
389

390
    def test_urls(self):
391
        from singlestoredb.connection import build_params
392
        from singlestoredb.config import get_option
393

394
        # Full URL (without scheme)
395
        url = 'me:[email protected]:3307/mydb'
396
        out = build_params(host=url)
397
        assert out['driver'] == get_option('driver'), out['driver']
398
        assert out['host'] == 's2host.com', out['host']
399
        assert out['port'] == 3307, out['port']
400
        assert out['database'] == 'mydb', out['database']
401
        assert out['user'] == 'me', out['user']
402
        assert out['password'] == 'p455w0rd', out['password']
403

404
        # Full URL (with scheme)
405
        url = 'http://me:[email protected]:3307/mydb'
406
        out = build_params(host=url)
407
        assert out['driver'] == 'http', out['driver']
408
        assert out['host'] == 's2host.com', out['host']
409
        assert out['port'] == 3307, out['port']
410
        assert out['database'] == 'mydb', out['database']
411
        assert out['user'] == 'me', out['user']
412
        assert out['password'] == 'p455w0rd', out['password']
413

414
        # No port
415
        url = 'me:[email protected]/mydb'
416
        out = build_params(host=url)
417
        assert out['driver'] == get_option('driver'), out['driver']
418
        assert out['host'] == 's2host.com', out['host']
419
        if out['driver'] in ['http', 'https']:
420
            assert out['port'] in [
421
                get_option(
422
                    'http_port',
423
                ), 80, 443,
424
            ], out['port']
425
        else:
426
            assert out['port'] in [get_option('port'), 3306], out['port']
427
        assert out['database'] == 'mydb', out['database']
428
        assert out['user'] == 'me', out['user']
429
        assert out['password'] == 'p455w0rd', out['password']
430

431
        # No http port
432
        url = 'http://me:[email protected]/mydb'
433
        out = build_params(host=url)
434
        assert out['driver'] == 'http', out['driver']
435
        assert out['host'] == 's2host.com', out['host']
436
        assert out['port'] in [get_option('http_port'), 80], out['port']
437
        assert out['database'] == 'mydb', out['database']
438
        assert out['user'] == 'me', out['user']
439
        assert out['password'] == 'p455w0rd', out['password']
440

441
        # No https port
442
        url = 'https://me:[email protected]/mydb'
443
        out = build_params(host=url)
444
        assert out['driver'] == 'https', out['driver']
445
        assert out['host'] == 's2host.com', out['host']
446
        assert out['port'] in [get_option('http_port'), 443], out['port']
447
        assert out['database'] == 'mydb', out['database']
448
        assert out['user'] == 'me', out['user']
449
        assert out['password'] == 'p455w0rd', out['password']
450

451
        # Invalid port
452
        url = 'https://me:[email protected]:foo/mydb'
453
        with self.assertRaises(ValueError):
454
            build_params(host=url)
455

456
        # Empty password
457
        url = 'me:@s2host.com/mydb'
458
        out = build_params(host=url)
459
        assert out['driver'] == get_option('driver'), out['driver']
460
        assert out['host'] == 's2host.com', out['host']
461
        if out['driver'] in ['http', 'https']:
462
            assert out['port'] in [
463
                get_option(
464
                    'http_port',
465
                ), 80, 443,
466
            ], out['port']
467
        else:
468
            assert out['port'] in [get_option('port'), 3306], out['port']
469
        assert out['database'] == 'mydb', out['database']
470
        assert out['user'] == 'me', out['user']
471
        assert out['password'] == '', out['password']
472

473
        # No user/password
474
        url = 's2host.com/mydb'
475
        out = build_params(host=url)
476
        assert out['driver'] == get_option('driver'), out['driver']
477
        assert out['host'] == 's2host.com', out['host']
478
        if out['driver'] in ['http', 'https']:
479
            assert out['port'] in [
480
                get_option(
481
                    'http_port',
482
                ), 80, 443,
483
            ], out['port']
484
        else:
485
            assert out['port'] in [get_option('port'), 3306], out['port']
486
        assert out['database'] == 'mydb', out['database']
487
        assert 'user' not in out or out['user'] == get_option(
488
            'user',
489
        ), out['user']
490
        assert 'password' not in out or out['password'] == get_option(
491
            'password',
492
        ), out['password']
493

494
        # Just hostname
495
        url = 's2host.com'
496
        out = build_params(host=url)
497
        assert out['driver'] == get_option('driver'), out['driver']
498
        assert out['host'] == 's2host.com', out['host']
499
        if out['driver'] in ['http', 'https']:
500
            assert out['port'] in [
501
                get_option(
502
                    'http_port',
503
                ), 80, 443,
504
            ], out['port']
505
        else:
506
            assert out['port'] in [get_option('port'), 3306], out['port']
507
        assert 'database' not in out
508
        assert 'user' not in out or out['user'] == get_option(
509
            'user',
510
        ), out['user']
511
        assert 'password' not in out or out['password'] == get_option(
512
            'password',
513
        ), out['password']
514

515
        # Just hostname and port
516
        url = 's2host.com:1000'
517
        out = build_params(host=url)
518
        assert out['driver'] == get_option('driver'), out['driver']
519
        assert out['host'] == 's2host.com', out['host']
520
        assert out['port'] == 1000, out['port']
521
        assert 'database' not in out
522
        assert 'user' not in out or out['user'] == get_option(
523
            'user',
524
        ), out['user']
525
        assert 'password' not in out or out['password'] == get_option(
526
            'password',
527
        ), out['password']
528

529
        # Query options
530
        url = 's2host.com:1000?local_infile=1&charset=utf8'
531
        out = build_params(host=url)
532
        assert out['driver'] == get_option('driver'), out['driver']
533
        assert out['host'] == 's2host.com', out['host']
534
        assert out['port'] == 1000, out['port']
535
        assert 'database' not in out
536
        assert 'user' not in out or out['user'] == get_option(
537
            'user',
538
        ), out['user']
539
        assert 'password' not in out or out['password'] == get_option(
540
            'password',
541
        ), out['password']
542
        assert out['local_infile'] is True, out['local_infile']
543
        assert out['charset'] == 'utf8', out['charset']
544

545
    def test_exception(self):
546
        with self.assertRaises(s2.ProgrammingError) as cm:
547
            self.cur.execute('garbage syntax')
548
        exc = cm.exception
549
        assert 'You have an error in your SQL syntax' in exc.errmsg, exc.errmsg
550

551
    def test_alltypes(self):
552
        self.cur.execute('select * from alltypes where id = 0')
553
        names = [x[0] for x in self.cur.description]
554
        types = [x[1] for x in self.cur.description]
555
        out = self.cur.fetchone()
556
        row = dict(zip(names, out))
557
        typ = dict(zip(names, types))
558

559
        bits = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
560

561
        def otype(x):
562
            return x
563

564
        assert row['id'] == 0, row['id']
565
        assert typ['id'] == otype(3), typ['id']
566

567
        assert row['tinyint'] == 80, row['tinyint']
568
        assert typ['tinyint'] == otype(1), typ['tinyint']
569

570
        assert row['unsigned_tinyint'] == 85, row['unsigned_tinyint']
571
        assert typ['unsigned_tinyint'] == otype(1), typ['unsigned_tinyint']
572

573
        assert row['bool'] == 0, row['bool']
574
        assert typ['bool'] == otype(1), typ['bool']
575

576
        assert row['boolean'] == 1, row['boolean']
577
        assert typ['boolean'] == otype(1), typ['boolean']
578

579
        assert row['smallint'] == -27897, row['smallint']
580
        assert typ['smallint'] == otype(2), typ['smallint']
581

582
        assert row['unsigned_smallint'] == 27897, row['unsigned_smallint']
583
        assert typ['unsigned_smallint'] == otype(2), typ['unsigned_smallint']
584

585
        assert row['mediumint'] == 104729, row['mediumint']
586
        assert typ['mediumint'] == otype(9), typ['mediumint']
587

588
        assert row['unsigned_mediumint'] == 120999, row['unsigned_mediumint']
589
        assert typ['unsigned_mediumint'] == otype(9), typ['unsigned_mediumint']
590

591
        assert row['int24'] == -200899, row['int24']
592
        assert typ['int24'] == otype(9), typ['int24']
593

594
        assert row['unsigned_int24'] == 407709, row['unsigned_int24']
595
        assert typ['unsigned_int24'] == otype(9), typ['unsigned_int24']
596

597
        assert row['int'] == -1295369311, row['int']
598
        assert typ['int'] == otype(3), typ['int']
599

600
        assert row['unsigned_int'] == 3872362332, row['unsigned_int']
601
        assert typ['unsigned_int'] == otype(3), typ['unsigned_int']
602

603
        assert row['integer'] == -1741727421, row['integer']
604
        assert typ['integer'] == otype(3), typ['integer']
605

606
        assert row['unsigned_integer'] == 3198387363, row['unsigned_integer']
607
        assert typ['unsigned_integer'] == otype(3), typ['unsigned_integer']
608

609
        assert row['bigint'] == -266883847, row['bigint']
610
        assert typ['bigint'] == otype(8), typ['bigint']
611

612
        assert row['unsigned_bigint'] == 980007287362, row['unsigned_bigint']
613
        assert typ['unsigned_bigint'] == otype(8), typ['unsigned_bigint']
614

615
        assert row['float'] == -146487000.0, row['float']
616
        assert typ['float'] == otype(4), typ['float']
617

618
        assert row['double'] == -474646154.719356, row['double']
619
        assert typ['double'] == otype(5), typ['double']
620

621
        assert row['real'] == -901409776.279346, row['real']
622
        assert typ['real'] == otype(5), typ['real']
623

624
        assert row['decimal'] == decimal.Decimal(
625
            '28111097.610822',
626
        ), row['decimal']
627
        assert typ['decimal'] == otype(246), typ['decimal']
628

629
        assert row['dec'] == decimal.Decimal('389451155.931428'), row['dec']
630
        assert typ['dec'] == otype(246), typ['dec']
631

632
        assert row['fixed'] == decimal.Decimal(
633
            '-143773416.044092',
634
        ), row['fixed']
635
        assert typ['fixed'] == otype(246), typ['fixed']
636

637
        assert row['numeric'] == decimal.Decimal(
638
            '866689461.300046',
639
        ), row['numeric']
640
        assert typ['numeric'] == otype(246), typ['numeric']
641

642
        assert row['date'] == datetime.date(8524, 11, 10), row['date']
643
        assert typ['date'] == 10, typ['date']
644

645
        assert row['time'] == datetime.timedelta(minutes=7), row['time']
646
        assert typ['time'] == 11, typ['time']
647

648
        assert typ['time_6'] == 11, typ['time_6']
649

650
        assert row['datetime'] == datetime.datetime(
651
            9948, 3, 11, 15, 29, 22,
652
        ), row['datetime']
653
        assert typ['datetime'] == 12, typ['datetime']
654

655
        assert row['datetime_6'] == datetime.datetime(
656
            1756, 10, 29, 2, 2, 42, 8,
657
        ), row['datetime_6']
658
        assert typ['datetime_6'] == 12, typ['datetime_6']
659

660
        assert row['timestamp'] == datetime.datetime(
661
            1980, 12, 31, 1, 10, 23,
662
        ), row['timestamp']
663
        assert typ['timestamp'] == otype(7), typ['timestamp']
664

665
        assert row['timestamp_6'] == datetime.datetime(
666
            1991, 1, 2, 22, 15, 10, 6,
667
        ), row['timestamp_6']
668
        assert typ['timestamp_6'] == otype(7), typ['timestamp_6']
669

670
        assert row['year'] == 1923, row['year']
671
        assert typ['year'] == otype(13), typ['year']
672

673
        assert row['char_100'] == \
674
            'This is a test of a 100 character column.', row['char_100']
675
        assert typ['char_100'] == otype(254), typ['char_100']
676

677
        assert row['binary_100'] == bytearray(
678
            bits + [0] * 84,
679
        ), row['binary_100']
680
        assert typ['binary_100'] == otype(254), typ['binary_100']
681

682
        assert row['varchar_200'] == \
683
            'This is a test of a variable character column.', row['varchar_200']
684
        assert typ['varchar_200'] == otype(
685
            253,
686
        ), typ['varchar_200']  # why not 15?
687

688
        assert row['varbinary_200'] == bytearray(
689
            bits * 2,
690
        ), row['varbinary_200']
691
        assert typ['varbinary_200'] == otype(
692
            253,
693
        ), typ['varbinary_200']  # why not 15?
694

695
        assert row['longtext'] == 'This is a longtext column.', row['longtext']
696
        assert typ['longtext'] == otype(251), typ['longtext']
697

698
        assert row['mediumtext'] == 'This is a mediumtext column.', row['mediumtext']
699
        assert typ['mediumtext'] == otype(250), typ['mediumtext']
700

701
        assert row['text'] == 'This is a text column.', row['text']
702
        assert typ['text'] == otype(252), typ['text']
703

704
        assert row['tinytext'] == 'This is a tinytext column.'
705
        assert typ['tinytext'] == otype(249), typ['tinytext']
706

707
        assert row['longblob'] == bytearray(bits * 3), row['longblob']
708
        assert typ['longblob'] == otype(251), typ['longblob']
709

710
        assert row['mediumblob'] == bytearray(bits * 2), row['mediumblob']
711
        assert typ['mediumblob'] == otype(250), typ['mediumblob']
712

713
        assert row['blob'] == bytearray(bits), row['blob']
714
        assert typ['blob'] == otype(252), typ['blob']
715

716
        assert row['tinyblob'] == bytearray(
717
            [10, 11, 12, 13, 14, 15],
718
        ), row['tinyblob']
719
        assert typ['tinyblob'] == otype(249), typ['tinyblob']
720

721
        assert row['json'] == {
722
            'a': 10, 'b': 2.75,
723
            'c': 'hello world',
724
        }, row['json']
725
        assert typ['json'] == otype(245), typ['json']
726

727
        assert row['enum'] == 'one', row['enum']
728
        assert typ['enum'] == otype(253), typ['enum']  # mysql code: 247
729

730
        assert row['set'] == 'two', row['set']
731
        assert typ['set'] == otype(253), typ['set']  # mysql code: 248
732

733
        assert row['bit'] == b'\x00\x00\x00\x00\x00\x00\x00\x80', row['bit']
734
        assert typ['bit'] == otype(16), typ['bit']
735

736
    def test_alltypes_numpy(self):
737
        conn = s2.connect(database=type(self).dbname, results_type='numpy')
738
        cur = conn.cursor()
739

740
        cur.execute('select * from alltypes where id = 0')
741
        names = [x[0] for x in cur.description]
742
        out = cur.fetchone()
743
        row = dict(zip(names, out[0]))
744

745
        dtypes = [
746
            ('id', '<f8'),
747
            ('tinyint', '<f4'),
748
            ('unsigned_tinyint', '<f4'),
749
            ('bool', '<f4'),
750
            ('boolean', '<f4'),
751
            ('smallint', '<f4'),
752
            ('unsigned_smallint', '<f4'),
753
            ('mediumint', '<f8'),
754
            ('unsigned_mediumint', '<f8'),
755
            ('int24', '<f8'),
756
            ('unsigned_int24', '<f8'),
757
            ('int', '<f8'),
758
            ('unsigned_int', '<f8'),
759
            ('integer', '<f8'),
760
            ('unsigned_integer', '<f8'),
761
            ('bigint', '<f8'),
762
            ('unsigned_bigint', '<f8'),
763
            ('float', '<f4'),
764
            ('double', '<f8'),
765
            ('real', '<f8'),
766
            ('decimal', 'O'),
767
            ('dec', 'O'),
768
            ('fixed', 'O'),
769
            ('numeric', 'O'),
770
            ('date', '<M8[D]'),
771
            ('time', '<m8[us]'),
772
            ('time_6', '<m8[us]'),
773
            ('datetime', '<M8[us]'),
774
            ('datetime_6', '<M8[us]'),
775
            ('timestamp', '<M8[us]'),
776
            ('timestamp_6', '<M8[us]'),
777
            ('year', '<f8'),
778
            ('char_100', 'O'),
779
            ('binary_100', 'O'),
780
            ('varchar_200', 'O'),
781
            ('varbinary_200', 'O'),
782
            ('longtext', 'O'),
783
            ('mediumtext', 'O'),
784
            ('text', 'O'),
785
            ('tinytext', 'O'),
786
            ('longblob', 'O'),
787
            ('mediumblob', 'O'),
788
            ('blob', 'O'),
789
            ('tinyblob', 'O'),
790
            ('json', 'O'),
791
            ('enum', 'O'),
792
            ('set', 'O'),
793
            ('bit', 'O'),
794
        ]
795

796
        assert out.dtype == dtypes
797

798
        bits = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
799

800
        assert row['id'] == 0, row['id']
801
        assert row['tinyint'] == 80, row['tinyint']
802
        assert row['unsigned_tinyint'] == 85, row['unsigned_tinyint']
803
        assert row['bool'] == 0, row['bool']
804
        assert row['boolean'] == 1, row['boolean']
805
        assert row['smallint'] == -27897, row['smallint']
806
        assert row['unsigned_smallint'] == 27897, row['unsigned_smallint']
807
        assert row['mediumint'] == 104729, row['mediumint']
808
        assert row['unsigned_mediumint'] == 120999, row['unsigned_mediumint']
809
        assert row['int24'] == -200899, row['int24']
810
        assert row['unsigned_int24'] == 407709, row['unsigned_int24']
811
        assert row['int'] == -1295369311, row['int']
812
        assert row['unsigned_int'] == 3872362332, row['unsigned_int']
813
        assert row['integer'] == -1741727421, row['integer']
814
        assert row['unsigned_integer'] == 3198387363, row['unsigned_integer']
815
        assert row['bigint'] == -266883847, row['bigint']
816
        assert row['unsigned_bigint'] == 980007287362, row['unsigned_bigint']
817
        assert row['float'] - -146487000.0 < 0.00001, row['float']
818
        assert row['double'] == -474646154.719356, row['double']
819
        assert row['real'] == -901409776.279346, row['real']
820
        assert row['decimal'] == decimal.Decimal(
821
            '28111097.610822',
822
        ), row['decimal']
823
        assert row['dec'] == decimal.Decimal('389451155.931428'), row['dec']
824
        assert row['fixed'] == decimal.Decimal(
825
            '-143773416.044092',
826
        ), row['fixed']
827
        assert row['numeric'] == decimal.Decimal(
828
            '866689461.300046',
829
        ), row['numeric']
830
        assert row['date'] == datetime.date(8524, 11, 10), row['date']
831
        assert row['time'] == datetime.timedelta(minutes=7), row['time']
832
        assert row['datetime'] == datetime.datetime(
833
            9948, 3, 11, 15, 29, 22,
834
        ), row['datetime']
835
        assert row['datetime_6'] == datetime.datetime(
836
            1756, 10, 29, 2, 2, 42, 8,
837
        ), row['datetime_6']
838
        assert row['timestamp'] == datetime.datetime(
839
            1980, 12, 31, 1, 10, 23,
840
        ), row['timestamp']
841
        assert row['timestamp_6'] == datetime.datetime(
842
            1991, 1, 2, 22, 15, 10, 6,
843
        ), row['timestamp_6']
844
        assert row['year'] == 1923, row['year']
845
        assert row['char_100'] == \
846
            'This is a test of a 100 character column.', row['char_100']
847
        assert row['binary_100'] == bytearray(
848
            bits + [0] * 84,
849
        ), row['binary_100']
850
        assert row['varchar_200'] == \
851
            'This is a test of a variable character column.', row['varchar_200']
852
        assert row['varbinary_200'] == bytearray(
853
            bits * 2,
854
        ), row['varbinary_200']
855
        assert row['longtext'] == 'This is a longtext column.', row['longtext']
856
        assert row['mediumtext'] == 'This is a mediumtext column.', row['mediumtext']
857
        assert row['text'] == 'This is a text column.', row['text']
858
        assert row['tinytext'] == 'This is a tinytext column.'
859
        assert row['longblob'] == bytearray(bits * 3), row['longblob']
860
        assert row['mediumblob'] == bytearray(bits * 2), row['mediumblob']
861
        assert row['blob'] == bytearray(bits), row['blob']
862
        assert row['tinyblob'] == bytearray(
863
            [10, 11, 12, 13, 14, 15],
864
        ), row['tinyblob']
865
        assert row['json'] == {
866
            'a': 10, 'b': 2.75,
867
            'c': 'hello world',
868
        }, row['json']
869
        assert row['enum'] == 'one', row['enum']
870
        assert row['set'] == 'two', row['set']
871
        assert row['bit'] == b'\x00\x00\x00\x00\x00\x00\x00\x80', row['bit']
872

873
        conn.close()
874

875
    def test_alltypes_no_nulls_numpy(self):
876
        if self.conn.driver in ['http', 'https']:
877
            self.skipTest('Data API does not surface unsigned int information')
878

879
        conn = s2.connect(database=type(self).dbname, results_type='numpy')
880
        cur = conn.cursor()
881

882
        cur.execute('select * from alltypes_no_nulls where id = 0')
883
        names = [x[0] for x in cur.description]
884
        out = cur.fetchone()
885
        row = dict(zip(names, out[0]))
886

887
        dtypes = [
888
            ('id', '<i4'),
889
            ('tinyint', 'i1'),
890
            ('unsigned_tinyint', 'u1'),
891
            ('bool', 'i1'),
892
            ('boolean', 'i1'),
893
            ('smallint', '<i2'),
894
            ('unsigned_smallint', '<u2'),
895
            ('mediumint', '<i4'),
896
            ('unsigned_mediumint', '<u4'),
897
            ('int24', '<i4'),
898
            ('unsigned_int24', '<u4'),
899
            ('int', '<i4'),
900
            ('unsigned_int', '<u4'),
901
            ('integer', '<i4'),
902
            ('unsigned_integer', '<u4'),
903
            ('bigint', '<i8'),
904
            ('unsigned_bigint', '<u8'),
905
            ('float', '<f4'),
906
            ('double', '<f8'),
907
            ('real', '<f8'),
908
            ('decimal', 'O'),
909
            ('dec', 'O'),
910
            ('fixed', 'O'),
911
            ('numeric', 'O'),
912
            ('date', '<M8[D]'),
913
            ('time', '<m8[us]'),
914
            ('time_6', '<m8[us]'),
915
            ('datetime', '<M8[us]'),
916
            ('datetime_6', '<M8[us]'),
917
            ('timestamp', '<M8[us]'),
918
            ('timestamp_6', '<M8[us]'),
919
            ('year', '<i2'),
920
            ('char_100', 'O'),
921
            ('binary_100', 'O'),
922
            ('varchar_200', 'O'),
923
            ('varbinary_200', 'O'),
924
            ('longtext', 'O'),
925
            ('mediumtext', 'O'),
926
            ('text', 'O'),
927
            ('tinytext', 'O'),
928
            ('longblob', 'O'),
929
            ('mediumblob', 'O'),
930
            ('blob', 'O'),
931
            ('tinyblob', 'O'),
932
            ('json', 'O'),
933
            ('enum', 'O'),
934
            ('set', 'O'),
935
            ('bit', 'O'),
936
        ]
937

938
        assert out.dtype == dtypes
939

940
        bits = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
941

942
        assert row['id'] == 0, row['id']
943
        assert row['tinyint'] == 80, row['tinyint']
944
        assert row['unsigned_tinyint'] == 85, row['unsigned_tinyint']
945
        assert row['bool'] == 0, row['bool']
946
        assert row['boolean'] == 1, row['boolean']
947
        assert row['smallint'] == -27897, row['smallint']
948
        assert row['unsigned_smallint'] == 27897, row['unsigned_smallint']
949
        assert row['mediumint'] == 104729, row['mediumint']
950
        assert row['unsigned_mediumint'] == 120999, row['unsigned_mediumint']
951
        assert row['int24'] == -200899, row['int24']
952
        assert row['unsigned_int24'] == 407709, row['unsigned_int24']
953
        assert row['int'] == -1295369311, row['int']
954
        assert row['unsigned_int'] == 3872362332, row['unsigned_int']
955
        assert row['integer'] == -1741727421, row['integer']
956
        assert row['unsigned_integer'] == 3198387363, row['unsigned_integer']
957
        assert row['bigint'] == -266883847, row['bigint']
958
        assert row['unsigned_bigint'] == 980007287362, row['unsigned_bigint']
959
        assert row['float'] - -146487000.0 < 0.00001, row['float']
960
        assert row['double'] == -474646154.719356, row['double']
961
        assert row['real'] == -901409776.279346, row['real']
962
        assert row['decimal'] == decimal.Decimal(
963
            '28111097.610822',
964
        ), row['decimal']
965
        assert row['dec'] == decimal.Decimal('389451155.931428'), row['dec']
966
        assert row['fixed'] == decimal.Decimal(
967
            '-143773416.044092',
968
        ), row['fixed']
969
        assert row['numeric'] == decimal.Decimal(
970
            '866689461.300046',
971
        ), row['numeric']
972
        assert row['date'] == datetime.date(8524, 11, 10), row['date']
973
        assert row['time'] == datetime.timedelta(minutes=7), row['time']
974
        assert row['datetime'] == datetime.datetime(
975
            9948, 3, 11, 15, 29, 22,
976
        ), row['datetime']
977
        assert row['datetime_6'] == datetime.datetime(
978
            1756, 10, 29, 2, 2, 42, 8,
979
        ), row['datetime_6']
980
        assert row['timestamp'] == datetime.datetime(
981
            1980, 12, 31, 1, 10, 23,
982
        ), row['timestamp']
983
        assert row['timestamp_6'] == datetime.datetime(
984
            1991, 1, 2, 22, 15, 10, 6,
985
        ), row['timestamp_6']
986
        assert row['year'] == 1923, row['year']
987
        assert row['char_100'] == \
988
            'This is a test of a 100 character column.', row['char_100']
989
        assert row['binary_100'] == bytearray(
990
            bits + [0] * 84,
991
        ), row['binary_100']
992
        assert row['varchar_200'] == \
993
            'This is a test of a variable character column.', row['varchar_200']
994
        assert row['varbinary_200'] == bytearray(
995
            bits * 2,
996
        ), row['varbinary_200']
997
        assert row['longtext'] == 'This is a longtext column.', row['longtext']
998
        assert row['mediumtext'] == 'This is a mediumtext column.', row['mediumtext']
999
        assert row['text'] == 'This is a text column.', row['text']
1000
        assert row['tinytext'] == 'This is a tinytext column.'
1001
        assert row['longblob'] == bytearray(bits * 3), row['longblob']
1002
        assert row['mediumblob'] == bytearray(bits * 2), row['mediumblob']
1003
        assert row['blob'] == bytearray(bits), row['blob']
1004
        assert row['tinyblob'] == bytearray(
1005
            [10, 11, 12, 13, 14, 15],
1006
        ), row['tinyblob']
1007
        assert row['json'] == {
1008
            'a': 10, 'b': 2.75,
1009
            'c': 'hello world',
1010
        }, row['json']
1011
        assert row['enum'] == 'one', row['enum']
1012
        assert row['set'] == 'two', row['set']
1013
        assert row['bit'] == b'\x00\x00\x00\x00\x00\x00\x00\x80', row['bit']
1014

1015
        conn.close()
1016

1017
    def test_alltypes_min_max_numpy(self):
1018
        if self.conn.driver in ['http', 'https']:
1019
            self.skipTest('Data API does not surface unsigned int information')
1020

1021
        conn = s2.connect(database=type(self).dbname, results_type='numpy')
1022
        cur = conn.cursor()
1023

1024
        cur.execute('select * from alltypes_no_nulls')
1025
        cur.fetchall()
1026

1027
        cur.execute('select * from alltypes')
1028
        cur.fetchall()
1029

1030
        conn.close()
1031

1032
    def test_alltypes_nulls_numpy(self):
1033
        conn = s2.connect(database=type(self).dbname, results_type='numpy')
1034
        cur = conn.cursor()
1035

1036
        cur.execute('select * from alltypes where id = 1')
1037
        names = [x[0] for x in cur.description]
1038
        out = cur.fetchone()
1039
        row = dict(zip(names, out[0]))
1040

1041
        assert row['id'] == 1, row['id']
1042
        assert np.isnan(row['tinyint']), row['tinyint']
1043
        assert np.isnan(row['bool']), row['bool']
1044
        assert np.isnan(row['boolean']), row['boolean']
1045
        assert np.isnan(row['smallint']), row['smallint']
1046
        assert np.isnan(row['mediumint']), row['mediumint']
1047
        assert np.isnan(row['int24']), row['int24']
1048
        assert np.isnan(row['int']), row['int']
1049
        assert np.isnan(row['integer']), row['integer']
1050
        assert np.isnan(row['bigint']), row['bigint']
1051
        assert np.isnan(row['float']), row['float']
1052
        assert np.isnan(row['double']), row['double']
1053
        assert np.isnan(row['real']), row['real']
1054
        assert row['decimal'] is None, row['decimal']
1055
        assert row['dec'] is None, row['dec']
1056
        assert row['fixed'] is None, row['fixed']
1057
        assert row['numeric'] is None, row['numeric']
1058
        assert np.isnat(row['date']), row['date']
1059
        assert np.isnat(row['time']), row['time']
1060
        assert np.isnat(row['time']), row['time']
1061
        assert np.isnat(row['datetime']), row['datetime']
1062
        assert np.isnat(row['datetime_6']), row['datetime_6']
1063
        assert np.isnat(row['timestamp']), row['timestamp']
1064
        assert np.isnat(row['timestamp_6']), row['timestamp_6']
1065
        assert np.isnan(row['year']), row['year']
1066
        assert row['char_100'] is None, row['char_100']
1067
        assert row['binary_100'] is None, row['binary_100']
1068
        assert row['varchar_200'] is None, row['varchar_200']
1069
        assert row['varbinary_200'] is None, row['varbinary_200']
1070
        assert row['longtext'] is None, row['longtext']
1071
        assert row['mediumtext'] is None, row['mediumtext']
1072
        assert row['text'] is None, row['text']
1073
        assert row['tinytext'] is None, row['tinytext']
1074
        assert row['longblob'] is None, row['longblob']
1075
        assert row['mediumblob'] is None, row['mediumblob']
1076
        assert row['blob'] is None, row['blob']
1077
        assert row['tinyblob'] is None, row['tinyblob']
1078
        assert row['json'] is None, row['json']
1079
        assert row['enum'] is None, row['enum']
1080
        assert row['set'] is None, row['set']
1081
        assert row['bit'] is None, row['bit']
1082

1083
        conn.close()
1084

1085
    def test_alltypes_pandas(self):
1086
        conn = s2.connect(database=type(self).dbname, results_type='pandas')
1087
        cur = conn.cursor()
1088

1089
        cur.execute('select * from alltypes where id = 0')
1090
        names = [x[0] for x in cur.description]
1091
        out = cur.fetchone()
1092
        row = dict(zip(names, out.iloc[0]))
1093

1094
        dtypes = [
1095
            ('id', 'float64'),
1096
            ('tinyint', 'float32'),
1097
            ('unsigned_tinyint', 'float32'),
1098
            ('bool', 'float32'),
1099
            ('boolean', 'float32'),
1100
            ('smallint', 'float32'),
1101
            ('unsigned_smallint', 'float32'),
1102
            ('mediumint', 'float64'),
1103
            ('unsigned_mediumint', 'float64'),
1104
            ('int24', 'float64'),
1105
            ('unsigned_int24', 'float64'),
1106
            ('int', 'float64'),
1107
            ('unsigned_int', 'float64'),
1108
            ('integer', 'float64'),
1109
            ('unsigned_integer', 'float64'),
1110
            ('bigint', 'float64'),
1111
            ('unsigned_bigint', 'float64'),
1112
            ('float', 'float32'),
1113
            ('double', 'float64'),
1114
            ('real', 'float64'),
1115
            ('decimal', 'object'),
1116
            ('dec', 'object'),
1117
            ('fixed', 'object'),
1118
            ('numeric', 'object'),
1119
            ('date', 'datetime64[s]'),
1120
            ('time', 'timedelta64[us]'),
1121
            ('time_6', 'timedelta64[us]'),
1122
            ('datetime', 'datetime64[us]'),
1123
            ('datetime_6', 'datetime64[us]'),
1124
            ('timestamp', 'datetime64[us]'),
1125
            ('timestamp_6', 'datetime64[us]'),
1126
            ('year', 'float64'),
1127
            ('char_100', 'object'),
1128
            ('binary_100', 'object'),
1129
            ('varchar_200', 'object'),
1130
            ('varbinary_200', 'object'),
1131
            ('longtext', 'object'),
1132
            ('mediumtext', 'object'),
1133
            ('text', 'object'),
1134
            ('tinytext', 'object'),
1135
            ('longblob', 'object'),
1136
            ('mediumblob', 'object'),
1137
            ('blob', 'object'),
1138
            ('tinyblob', 'object'),
1139
            ('json', 'object'),
1140
            ('enum', 'object'),
1141
            ('set', 'object'),
1142
            ('bit', 'object'),
1143
        ]
1144

1145
        assert [(x[0], str(x[1])) for x in out.dtypes.items()] == dtypes
1146

1147
        bits = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
1148

1149
        assert row['id'] == 0, row['id']
1150
        assert row['tinyint'] == 80, row['tinyint']
1151
        assert row['unsigned_tinyint'] == 85, row['unsigned_tinyint']
1152
        assert row['bool'] == 0, row['bool']
1153
        assert row['boolean'] == 1, row['boolean']
1154
        assert row['smallint'] == -27897, row['smallint']
1155
        assert row['unsigned_smallint'] == 27897, row['unsigned_smallint']
1156
        assert row['mediumint'] == 104729, row['mediumint']
1157
        assert row['unsigned_mediumint'] == 120999, row['unsigned_mediumint']
1158
        assert row['int24'] == -200899, row['int24']
1159
        assert row['unsigned_int24'] == 407709, row['unsigned_int24']
1160
        assert row['int'] == -1295369311, row['int']
1161
        assert row['unsigned_int'] == 3872362332, row['unsigned_int']
1162
        assert row['integer'] == -1741727421, row['integer']
1163
        assert row['unsigned_integer'] == 3198387363, row['unsigned_integer']
1164
        assert row['bigint'] == -266883847, row['bigint']
1165
        assert row['unsigned_bigint'] == 980007287362, row['unsigned_bigint']
1166
        assert row['float'] - -146487000.0 < 0.00001, row['float']
1167
        assert row['double'] == -474646154.719356, row['double']
1168
        assert row['real'] == -901409776.279346, row['real']
1169
        assert row['decimal'] == decimal.Decimal(
1170
            '28111097.610822',
1171
        ), row['decimal']
1172
        assert row['dec'] == decimal.Decimal('389451155.931428'), row['dec']
1173
        assert row['fixed'] == decimal.Decimal(
1174
            '-143773416.044092',
1175
        ), row['fixed']
1176
        assert row['numeric'] == decimal.Decimal(
1177
            '866689461.300046',
1178
        ), row['numeric']
1179
        assert row['date'] == datetime.datetime(8524, 11, 10), row['date']
1180
        assert row['time'] == datetime.timedelta(minutes=7), row['time']
1181
        assert row['datetime'] == datetime.datetime(
1182
            9948, 3, 11, 15, 29, 22,
1183
        ), row['datetime']
1184
        assert row['datetime_6'] == datetime.datetime(
1185
            1756, 10, 29, 2, 2, 42, 8,
1186
        ), row['datetime_6']
1187
        assert row['timestamp'] == datetime.datetime(
1188
            1980, 12, 31, 1, 10, 23,
1189
        ), row['timestamp']
1190
        assert row['timestamp_6'] == datetime.datetime(
1191
            1991, 1, 2, 22, 15, 10, 6,
1192
        ), row['timestamp_6']
1193
        assert row['year'] == 1923, row['year']
1194
        assert row['char_100'] == \
1195
            'This is a test of a 100 character column.', row['char_100']
1196
        assert row['binary_100'] == bytearray(
1197
            bits + [0] * 84,
1198
        ), row['binary_100']
1199
        assert row['varchar_200'] == \
1200
            'This is a test of a variable character column.', row['varchar_200']
1201
        assert row['varbinary_200'] == bytearray(
1202
            bits * 2,
1203
        ), row['varbinary_200']
1204
        assert row['longtext'] == 'This is a longtext column.', row['longtext']
1205
        assert row['mediumtext'] == 'This is a mediumtext column.', row['mediumtext']
1206
        assert row['text'] == 'This is a text column.', row['text']
1207
        assert row['tinytext'] == 'This is a tinytext column.'
1208
        assert row['longblob'] == bytearray(bits * 3), row['longblob']
1209
        assert row['mediumblob'] == bytearray(bits * 2), row['mediumblob']
1210
        assert row['blob'] == bytearray(bits), row['blob']
1211
        assert row['tinyblob'] == bytearray(
1212
            [10, 11, 12, 13, 14, 15],
1213
        ), row['tinyblob']
1214
        assert row['json'] == {
1215
            'a': 10, 'b': 2.75,
1216
            'c': 'hello world',
1217
        }, row['json']
1218
        assert row['enum'] == 'one', row['enum']
1219
        assert row['set'] == 'two', row['set']
1220
        assert row['bit'] == b'\x00\x00\x00\x00\x00\x00\x00\x80', row['bit']
1221

1222
        conn.close()
1223

1224
    def test_alltypes_no_nulls_pandas(self):
1225
        if self.conn.driver in ['http', 'https']:
1226
            self.skipTest('Data API does not surface unsigned int information')
1227

1228
        conn = s2.connect(database=type(self).dbname, results_type='pandas')
1229
        cur = conn.cursor()
1230

1231
        cur.execute('select * from alltypes_no_nulls where id = 0')
1232
        names = [x[0] for x in cur.description]
1233
        out = cur.fetchone()
1234
        row = dict(zip(names, out.iloc[0]))
1235

1236
        dtypes = [
1237
            ('id', 'int32'),
1238
            ('tinyint', 'int8'),
1239
            ('unsigned_tinyint', 'uint8'),
1240
            ('bool', 'int8'),
1241
            ('boolean', 'int8'),
1242
            ('smallint', 'int16'),
1243
            ('unsigned_smallint', 'uint16'),
1244
            ('mediumint', 'int32'),
1245
            ('unsigned_mediumint', 'uint32'),
1246
            ('int24', 'int32'),
1247
            ('unsigned_int24', 'uint32'),
1248
            ('int', 'int32'),
1249
            ('unsigned_int', 'uint32'),
1250
            ('integer', 'int32'),
1251
            ('unsigned_integer', 'uint32'),
1252
            ('bigint', 'int64'),
1253
            ('unsigned_bigint', 'uint64'),
1254
            ('float', 'float32'),
1255
            ('double', 'float64'),
1256
            ('real', 'float64'),
1257
            ('decimal', 'object'),
1258
            ('dec', 'object'),
1259
            ('fixed', 'object'),
1260
            ('numeric', 'object'),
1261
            ('date', 'datetime64[s]'),
1262
            ('time', 'timedelta64[us]'),
1263
            ('time_6', 'timedelta64[us]'),
1264
            ('datetime', 'datetime64[us]'),
1265
            ('datetime_6', 'datetime64[us]'),
1266
            ('timestamp', 'datetime64[us]'),
1267
            ('timestamp_6', 'datetime64[us]'),
1268
            ('year', 'int16'),
1269
            ('char_100', 'object'),
1270
            ('binary_100', 'object'),
1271
            ('varchar_200', 'object'),
1272
            ('varbinary_200', 'object'),
1273
            ('longtext', 'object'),
1274
            ('mediumtext', 'object'),
1275
            ('text', 'object'),
1276
            ('tinytext', 'object'),
1277
            ('longblob', 'object'),
1278
            ('mediumblob', 'object'),
1279
            ('blob', 'object'),
1280
            ('tinyblob', 'object'),
1281
            ('json', 'object'),
1282
            ('enum', 'object'),
1283
            ('set', 'object'),
1284
            ('bit', 'object'),
1285
        ]
1286

1287
        assert [(x[0], str(x[1])) for x in out.dtypes.items()] == dtypes
1288

1289
        bits = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
1290

1291
        assert row['id'] == 0, row['id']
1292
        assert row['tinyint'] == 80, row['tinyint']
1293
        assert row['unsigned_tinyint'] == 85, row['unsigned_tinyint']
1294
        assert row['bool'] == 0, row['bool']
1295
        assert row['boolean'] == 1, row['boolean']
1296
        assert row['smallint'] == -27897, row['smallint']
1297
        assert row['unsigned_smallint'] == 27897, row['unsigned_smallint']
1298
        assert row['mediumint'] == 104729, row['mediumint']
1299
        assert row['unsigned_mediumint'] == 120999, row['unsigned_mediumint']
1300
        assert row['int24'] == -200899, row['int24']
1301
        assert row['unsigned_int24'] == 407709, row['unsigned_int24']
1302
        assert row['int'] == -1295369311, row['int']
1303
        assert row['unsigned_int'] == 3872362332, row['unsigned_int']
1304
        assert row['integer'] == -1741727421, row['integer']
1305
        assert row['unsigned_integer'] == 3198387363, row['unsigned_integer']
1306
        assert row['bigint'] == -266883847, row['bigint']
1307
        assert row['unsigned_bigint'] == 980007287362, row['unsigned_bigint']
1308
        assert row['float'] - -146487000.0 < 0.00001, row['float']
1309
        assert row['double'] == -474646154.719356, row['double']
1310
        assert row['real'] == -901409776.279346, row['real']
1311
        assert row['decimal'] == decimal.Decimal(
1312
            '28111097.610822',
1313
        ), row['decimal']
1314
        assert row['dec'] == decimal.Decimal('389451155.931428'), row['dec']
1315
        assert row['fixed'] == decimal.Decimal(
1316
            '-143773416.044092',
1317
        ), row['fixed']
1318
        assert row['numeric'] == decimal.Decimal(
1319
            '866689461.300046',
1320
        ), row['numeric']
1321
        assert row['date'] == datetime.datetime(8524, 11, 10), row['date']
1322
        assert row['time'] == datetime.timedelta(minutes=7), row['time']
1323
        assert row['datetime'] == datetime.datetime(
1324
            9948, 3, 11, 15, 29, 22,
1325
        ), row['datetime']
1326
        assert row['datetime_6'] == datetime.datetime(
1327
            1756, 10, 29, 2, 2, 42, 8,
1328
        ), row['datetime_6']
1329
        assert row['timestamp'] == datetime.datetime(
1330
            1980, 12, 31, 1, 10, 23,
1331
        ), row['timestamp']
1332
        assert row['timestamp_6'] == datetime.datetime(
1333
            1991, 1, 2, 22, 15, 10, 6,
1334
        ), row['timestamp_6']
1335
        assert row['year'] == 1923, row['year']
1336
        assert row['char_100'] == \
1337
            'This is a test of a 100 character column.', row['char_100']
1338
        assert row['binary_100'] == bytearray(
1339
            bits + [0] * 84,
1340
        ), row['binary_100']
1341
        assert row['varchar_200'] == \
1342
            'This is a test of a variable character column.', row['varchar_200']
1343
        assert row['varbinary_200'] == bytearray(
1344
            bits * 2,
1345
        ), row['varbinary_200']
1346
        assert row['longtext'] == 'This is a longtext column.', row['longtext']
1347
        assert row['mediumtext'] == 'This is a mediumtext column.', row['mediumtext']
1348
        assert row['text'] == 'This is a text column.', row['text']
1349
        assert row['tinytext'] == 'This is a tinytext column.'
1350
        assert row['longblob'] == bytearray(bits * 3), row['longblob']
1351
        assert row['mediumblob'] == bytearray(bits * 2), row['mediumblob']
1352
        assert row['blob'] == bytearray(bits), row['blob']
1353
        assert row['tinyblob'] == bytearray(
1354
            [10, 11, 12, 13, 14, 15],
1355
        ), row['tinyblob']
1356
        assert row['json'] == {
1357
            'a': 10, 'b': 2.75,
1358
            'c': 'hello world',
1359
        }, row['json']
1360
        assert row['enum'] == 'one', row['enum']
1361
        assert row['set'] == 'two', row['set']
1362
        assert row['bit'] == b'\x00\x00\x00\x00\x00\x00\x00\x80', row['bit']
1363

1364
        conn.close()
1365

1366
    def test_alltypes_min_max_pandas(self):
1367
        if self.conn.driver in ['http', 'https']:
1368
            self.skipTest('Data API does not surface unsigned int information')
1369

1370
        conn = s2.connect(database=type(self).dbname, results_type='pandas')
1371
        cur = conn.cursor()
1372

1373
        cur.execute('select * from alltypes_no_nulls')
1374
        cur.fetchall()
1375

1376
        cur.execute('select * from alltypes')
1377
        cur.fetchall()
1378

1379
        conn.close()
1380

1381
    def test_alltypes_nulls_pandas(self):
1382
        conn = s2.connect(database=type(self).dbname, results_type='pandas')
1383
        cur = conn.cursor()
1384

1385
        cur.execute('select * from alltypes where id = 1')
1386
        names = [x[0] for x in cur.description]
1387
        out = cur.fetchone()
1388
        row = dict(zip(names, out.iloc[0]))
1389

1390
        assert row['id'] == 1, row['id']
1391
        assert np.isnan(row['tinyint']), row['tinyint']
1392
        assert np.isnan(row['bool']), row['bool']
1393
        assert np.isnan(row['boolean']), row['boolean']
1394
        assert np.isnan(row['smallint']), row['smallint']
1395
        assert np.isnan(row['mediumint']), row['mediumint']
1396
        assert np.isnan(row['int24']), row['int24']
1397
        assert np.isnan(row['int']), row['int']
1398
        assert np.isnan(row['integer']), row['integer']
1399
        assert np.isnan(row['bigint']), row['bigint']
1400
        assert np.isnan(row['float']), row['float']
1401
        assert np.isnan(row['double']), row['double']
1402
        assert np.isnan(row['real']), row['real']
1403
        assert row['decimal'] is None, row['decimal']
1404
        assert row['dec'] is None, row['dec']
1405
        assert row['fixed'] is None, row['fixed']
1406
        assert row['numeric'] is None, row['numeric']
1407
        assert row['date'] is pd.NaT, row['date']
1408
        assert row['time'] is pd.NaT, row['time']
1409
        assert row['time'] is pd.NaT, row['time']
1410
        assert row['datetime'] is pd.NaT, row['datetime']
1411
        assert row['datetime_6'] is pd.NaT, row['datetime_6']
1412
        assert row['timestamp'] is pd.NaT, row['timestamp']
1413
        assert row['timestamp_6'] is pd.NaT, row['timestamp_6']
1414
        assert np.isnan(row['year']), row['year']
1415
        assert row['char_100'] is None, row['char_100']
1416
        assert row['binary_100'] is None, row['binary_100']
1417
        assert row['varchar_200'] is None, row['varchar_200']
1418
        assert row['varbinary_200'] is None, row['varbinary_200']
1419
        assert row['longtext'] is None, row['longtext']
1420
        assert row['mediumtext'] is None, row['mediumtext']
1421
        assert row['text'] is None, row['text']
1422
        assert row['tinytext'] is None, row['tinytext']
1423
        assert row['longblob'] is None, row['longblob']
1424
        assert row['mediumblob'] is None, row['mediumblob']
1425
        assert row['blob'] is None, row['blob']
1426
        assert row['tinyblob'] is None, row['tinyblob']
1427
        assert row['json'] is None, row['json']
1428
        assert row['enum'] is None, row['enum']
1429
        assert row['set'] is None, row['set']
1430
        assert row['bit'] is None, row['bit']
1431

1432
        conn.close()
1433

1434
    def test_alltypes_polars(self):
1435
        if self.conn.driver in ['http', 'https']:
1436
            self.skipTest('Data API does not surface unsigned int information')
1437

1438
        conn = s2.connect(database=type(self).dbname, results_type='polars')
1439
        cur = conn.cursor()
1440

1441
        cur.execute('select * from alltypes where id = 0')
1442
        names = [x[0] for x in cur.description]
1443
        out = cur.fetchone()
1444
        row = dict(zip(names, out.row(0)))
1445

1446
        # Recent versions of polars have a problem with decimals
1447
        class FixCompare(str):
1448
            def __eq__(self, other):
1449
                return super().__eq__(other.replace('precision=None', 'precision=20'))
1450

1451
        dtypes = [
1452
            ('id', 'Int32'),
1453
            ('tinyint', 'Int8'),
1454
            ('unsigned_tinyint', 'UInt8'),
1455
            ('bool', 'Int8'),
1456
            ('boolean', 'Int8'),
1457
            ('smallint', 'Int16'),
1458
            ('unsigned_smallint', 'UInt16'),
1459
            ('mediumint', 'Int32'),
1460
            ('unsigned_mediumint', 'UInt32'),
1461
            ('int24', 'Int32'),
1462
            ('unsigned_int24', 'UInt32'),
1463
            ('int', 'Int32'),
1464
            ('unsigned_int', 'UInt32'),
1465
            ('integer', 'Int32'),
1466
            ('unsigned_integer', 'UInt32'),
1467
            ('bigint', 'Int64'),
1468
            ('unsigned_bigint', 'UInt64'),
1469
            ('float', 'Float32'),
1470
            ('double', 'Float64'),
1471
            ('real', 'Float64'),
1472
            ('decimal', FixCompare('Decimal(precision=20, scale=6)')),
1473
            ('dec', FixCompare('Decimal(precision=20, scale=6)')),
1474
            ('fixed', FixCompare('Decimal(precision=20, scale=6)')),
1475
            ('numeric', FixCompare('Decimal(precision=20, scale=6)')),
1476
            ('date', 'Date'),
1477
            ('time', "Duration(time_unit='us')"),
1478
            ('time_6', "Duration(time_unit='us')"),
1479
            ('datetime', "Datetime(time_unit='us', time_zone=None)"),
1480
            ('datetime_6', "Datetime(time_unit='us', time_zone=None)"),
1481
            ('timestamp', "Datetime(time_unit='us', time_zone=None)"),
1482
            ('timestamp_6', "Datetime(time_unit='us', time_zone=None)"),
1483
            ('year', 'Int16'),
1484
            ('char_100', 'String'),
1485
            ('binary_100', 'Binary'),
1486
            ('varchar_200', 'String'),
1487
            ('varbinary_200', 'Binary'),
1488
            ('longtext', 'String'),
1489
            ('mediumtext', 'String'),
1490
            ('text', 'String'),
1491
            ('tinytext', 'String'),
1492
            ('longblob', 'Binary'),
1493
            ('mediumblob', 'Binary'),
1494
            ('blob', 'Binary'),
1495
            ('tinyblob', 'Binary'),
1496
            ('json', 'Object'),
1497
            ('enum', 'String'),
1498
            ('set', 'String'),
1499
            ('bit', 'Binary'),
1500
        ]
1501

1502
        assert [(x, str(y)) for x, y in zip(out.columns, out.dtypes)] == dtypes
1503

1504
        bits = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
1505

1506
        assert row['id'] == 0, row['id']
1507
        assert row['tinyint'] == 80, row['tinyint']
1508
        assert row['unsigned_tinyint'] == 85, row['unsigned_tinyint']
1509
        assert row['bool'] == 0, row['bool']
1510
        assert row['boolean'] == 1, row['boolean']
1511
        assert row['smallint'] == -27897, row['smallint']
1512
        assert row['unsigned_smallint'] == 27897, row['unsigned_smallint']
1513
        assert row['mediumint'] == 104729, row['mediumint']
1514
        assert row['unsigned_mediumint'] == 120999, row['unsigned_mediumint']
1515
        assert row['int24'] == -200899, row['int24']
1516
        assert row['unsigned_int24'] == 407709, row['unsigned_int24']
1517
        assert row['int'] == -1295369311, row['int']
1518
        assert row['unsigned_int'] == 3872362332, row['unsigned_int']
1519
        assert row['integer'] == -1741727421, row['integer']
1520
        assert row['unsigned_integer'] == 3198387363, row['unsigned_integer']
1521
        assert row['bigint'] == -266883847, row['bigint']
1522
        assert row['unsigned_bigint'] == 980007287362, row['unsigned_bigint']
1523
        assert row['float'] - -146487000.0 < 0.00001, row['float']
1524
        assert row['double'] == -474646154.719356, row['double']
1525
        assert row['real'] == -901409776.279346, row['real']
1526
        assert row['decimal'] == decimal.Decimal(
1527
            '28111097.610822',
1528
        ), row['decimal']
1529
        assert row['dec'] == decimal.Decimal('389451155.931428'), row['dec']
1530
        assert row['fixed'] == decimal.Decimal(
1531
            '-143773416.044092',
1532
        ), row['fixed']
1533
        assert row['numeric'] == decimal.Decimal(
1534
            '866689461.300046',
1535
        ), row['numeric']
1536
        assert row['date'] == datetime.date(8524, 11, 10), row['date']
1537
        assert row['time'] == datetime.timedelta(minutes=7), row['time']
1538
        assert row['datetime'] == datetime.datetime(
1539
            9948, 3, 11, 15, 29, 22,
1540
        ), row['datetime']
1541
        assert row['datetime_6'] == datetime.datetime(
1542
            1756, 10, 29, 2, 2, 42, 8,
1543
        ), row['datetime_6']
1544
        assert row['timestamp'] == datetime.datetime(
1545
            1980, 12, 31, 1, 10, 23,
1546
        ), row['timestamp']
1547
        assert row['timestamp_6'] == datetime.datetime(
1548
            1991, 1, 2, 22, 15, 10, 6,
1549
        ), row['timestamp_6']
1550
        assert row['year'] == 1923, row['year']
1551
        assert row['char_100'] == \
1552
            'This is a test of a 100 character column.', row['char_100']
1553
        assert row['binary_100'] == bytearray(
1554
            bits + [0] * 84,
1555
        ), row['binary_100']
1556
        assert row['varchar_200'] == \
1557
            'This is a test of a variable character column.', row['varchar_200']
1558
        assert row['varbinary_200'] == bytearray(
1559
            bits * 2,
1560
        ), row['varbinary_200']
1561
        assert row['longtext'] == 'This is a longtext column.', row['longtext']
1562
        assert row['mediumtext'] == 'This is a mediumtext column.', row['mediumtext']
1563
        assert row['text'] == 'This is a text column.', row['text']
1564
        assert row['tinytext'] == 'This is a tinytext column.'
1565
        assert row['longblob'] == bytearray(bits * 3), row['longblob']
1566
        assert row['mediumblob'] == bytearray(bits * 2), row['mediumblob']
1567
        assert row['blob'] == bytearray(bits), row['blob']
1568
        assert row['tinyblob'] == bytearray(
1569
            [10, 11, 12, 13, 14, 15],
1570
        ), row['tinyblob']
1571
        assert row['json'] == {
1572
            'a': 10, 'b': 2.75,
1573
            'c': 'hello world',
1574
        }, row['json']
1575
        assert row['enum'] == 'one', row['enum']
1576
        assert row['set'] == 'two', row['set']
1577
        assert row['bit'] == b'\x00\x00\x00\x00\x00\x00\x00\x80', row['bit']
1578

1579
        conn.close()
1580

1581
    def test_alltypes_no_nulls_polars(self):
1582
        if self.conn.driver in ['http', 'https']:
1583
            self.skipTest('Data API does not surface unsigned int information')
1584

1585
        conn = s2.connect(database=type(self).dbname, results_type='polars')
1586
        cur = conn.cursor()
1587

1588
        cur.execute('select * from alltypes_no_nulls where id = 0')
1589
        names = [x[0] for x in cur.description]
1590
        out = cur.fetchone()
1591
        row = dict(zip(names, out.row(0)))
1592

1593
        # Recent versions of polars have a problem with decimals
1594
        class FixCompare(str):
1595
            def __eq__(self, other):
1596
                return super().__eq__(other.replace('precision=None', 'precision=20'))
1597

1598
        dtypes = [
1599
            ('id', 'Int32'),
1600
            ('tinyint', 'Int8'),
1601
            ('unsigned_tinyint', 'UInt8'),
1602
            ('bool', 'Int8'),
1603
            ('boolean', 'Int8'),
1604
            ('smallint', 'Int16'),
1605
            ('unsigned_smallint', 'UInt16'),
1606
            ('mediumint', 'Int32'),
1607
            ('unsigned_mediumint', 'UInt32'),
1608
            ('int24', 'Int32'),
1609
            ('unsigned_int24', 'UInt32'),
1610
            ('int', 'Int32'),
1611
            ('unsigned_int', 'UInt32'),
1612
            ('integer', 'Int32'),
1613
            ('unsigned_integer', 'UInt32'),
1614
            ('bigint', 'Int64'),
1615
            ('unsigned_bigint', 'UInt64'),
1616
            ('float', 'Float32'),
1617
            ('double', 'Float64'),
1618
            ('real', 'Float64'),
1619
            ('decimal', FixCompare('Decimal(precision=20, scale=6)')),
1620
            ('dec', FixCompare('Decimal(precision=20, scale=6)')),
1621
            ('fixed', FixCompare('Decimal(precision=20, scale=6)')),
1622
            ('numeric', FixCompare('Decimal(precision=20, scale=6)')),
1623
            ('date', 'Date'),
1624
            ('time', "Duration(time_unit='us')"),
1625
            ('time_6', "Duration(time_unit='us')"),
1626
            ('datetime', "Datetime(time_unit='us', time_zone=None)"),
1627
            ('datetime_6', "Datetime(time_unit='us', time_zone=None)"),
1628
            ('timestamp', "Datetime(time_unit='us', time_zone=None)"),
1629
            ('timestamp_6', "Datetime(time_unit='us', time_zone=None)"),
1630
            ('year', 'Int16'),
1631
            ('char_100', 'String'),
1632
            ('binary_100', 'Binary'),
1633
            ('varchar_200', 'String'),
1634
            ('varbinary_200', 'Binary'),
1635
            ('longtext', 'String'),
1636
            ('mediumtext', 'String'),
1637
            ('text', 'String'),
1638
            ('tinytext', 'String'),
1639
            ('longblob', 'Binary'),
1640
            ('mediumblob', 'Binary'),
1641
            ('blob', 'Binary'),
1642
            ('tinyblob', 'Binary'),
1643
            ('json', 'Object'),
1644
            ('enum', 'String'),
1645
            ('set', 'String'),
1646
            ('bit', 'Binary'),
1647
        ]
1648

1649
        assert [(x, str(y)) for x, y in zip(out.columns, out.dtypes)] == dtypes
1650

1651
        bits = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
1652

1653
        assert row['id'] == 0, row['id']
1654
        assert row['tinyint'] == 80, row['tinyint']
1655
        assert row['unsigned_tinyint'] == 85, row['unsigned_tinyint']
1656
        assert row['bool'] == 0, row['bool']
1657
        assert row['boolean'] == 1, row['boolean']
1658
        assert row['smallint'] == -27897, row['smallint']
1659
        assert row['unsigned_smallint'] == 27897, row['unsigned_smallint']
1660
        assert row['mediumint'] == 104729, row['mediumint']
1661
        assert row['unsigned_mediumint'] == 120999, row['unsigned_mediumint']
1662
        assert row['int24'] == -200899, row['int24']
1663
        assert row['unsigned_int24'] == 407709, row['unsigned_int24']
1664
        assert row['int'] == -1295369311, row['int']
1665
        assert row['unsigned_int'] == 3872362332, row['unsigned_int']
1666
        assert row['integer'] == -1741727421, row['integer']
1667
        assert row['unsigned_integer'] == 3198387363, row['unsigned_integer']
1668
        assert row['bigint'] == -266883847, row['bigint']
1669
        assert row['unsigned_bigint'] == 980007287362, row['unsigned_bigint']
1670
        assert row['float'] - -146487000.0 < 0.00001, row['float']
1671
        assert row['double'] == -474646154.719356, row['double']
1672
        assert row['real'] == -901409776.279346, row['real']
1673
        assert row['decimal'] == decimal.Decimal(
1674
            '28111097.610822',
1675
        ), row['decimal']
1676
        assert row['dec'] == decimal.Decimal('389451155.931428'), row['dec']
1677
        assert row['fixed'] == decimal.Decimal(
1678
            '-143773416.044092',
1679
        ), row['fixed']
1680
        assert row['numeric'] == decimal.Decimal(
1681
            '866689461.300046',
1682
        ), row['numeric']
1683
        assert row['date'] == datetime.date(8524, 11, 10), row['date']
1684
        assert row['time'] == datetime.timedelta(minutes=7), row['time']
1685
        assert row['datetime'] == datetime.datetime(
1686
            9948, 3, 11, 15, 29, 22,
1687
        ), row['datetime']
1688
        assert row['datetime_6'] == datetime.datetime(
1689
            1756, 10, 29, 2, 2, 42, 8,
1690
        ), row['datetime_6']
1691
        assert row['timestamp'] == datetime.datetime(
1692
            1980, 12, 31, 1, 10, 23,
1693
        ), row['timestamp']
1694
        assert row['timestamp_6'] == datetime.datetime(
1695
            1991, 1, 2, 22, 15, 10, 6,
1696
        ), row['timestamp_6']
1697
        assert row['year'] == 1923, row['year']
1698
        assert row['char_100'] == \
1699
            'This is a test of a 100 character column.', row['char_100']
1700
        assert row['binary_100'] == bytearray(
1701
            bits + [0] * 84,
1702
        ), row['binary_100']
1703
        assert row['varchar_200'] == \
1704
            'This is a test of a variable character column.', row['varchar_200']
1705
        assert row['varbinary_200'] == bytearray(
1706
            bits * 2,
1707
        ), row['varbinary_200']
1708
        assert row['longtext'] == 'This is a longtext column.', row['longtext']
1709
        assert row['mediumtext'] == 'This is a mediumtext column.', row['mediumtext']
1710
        assert row['text'] == 'This is a text column.', row['text']
1711
        assert row['tinytext'] == 'This is a tinytext column.'
1712
        assert row['longblob'] == bytearray(bits * 3), row['longblob']
1713
        assert row['mediumblob'] == bytearray(bits * 2), row['mediumblob']
1714
        assert row['blob'] == bytearray(bits), row['blob']
1715
        assert row['tinyblob'] == bytearray(
1716
            [10, 11, 12, 13, 14, 15],
1717
        ), row['tinyblob']
1718
        assert row['json'] == {
1719
            'a': 10, 'b': 2.75,
1720
            'c': 'hello world',
1721
        }, row['json']
1722
        assert row['enum'] == 'one', row['enum']
1723
        assert row['set'] == 'two', row['set']
1724
        assert row['bit'] == b'\x00\x00\x00\x00\x00\x00\x00\x80', row['bit']
1725

1726
        conn.close()
1727

1728
    def test_alltypes_min_max_polars(self):
1729
        if self.conn.driver in ['http', 'https']:
1730
            self.skipTest('Data API does not surface unsigned int information')
1731

1732
        conn = s2.connect(database=type(self).dbname, results_type='polars')
1733
        cur = conn.cursor()
1734

1735
        cur.execute('select * from alltypes_no_nulls')
1736
        cur.fetchall()
1737

1738
        cur.execute('select * from alltypes')
1739
        cur.fetchall()
1740

1741
        conn.close()
1742

1743
    def test_alltypes_nulls_polars(self):
1744
        conn = s2.connect(database=type(self).dbname, results_type='polars')
1745
        cur = conn.cursor()
1746

1747
        cur.execute('select * from alltypes where id = 1')
1748
        names = [x[0] for x in cur.description]
1749
        out = cur.fetchone()
1750
        row = dict(zip(names, out.row(0)))
1751

1752
        assert row['id'] == 1, row['id']
1753
        assert row['tinyint'] is None, row['tinyint']
1754
        assert row['bool'] is None, row['bool']
1755
        assert row['boolean'] is None, row['boolean']
1756
        assert row['smallint'] is None, row['smallint']
1757
        assert row['mediumint'] is None, row['mediumint']
1758
        assert row['int24'] is None, row['int24']
1759
        assert row['int'] is None, row['int']
1760
        assert row['integer'] is None, row['integer']
1761
        assert row['bigint'] is None, row['bigint']
1762
        assert row['float'] is None, row['float']
1763
        assert row['double'] is None, row['double']
1764
        assert row['real'] is None, row['real']
1765
        assert row['decimal'] is None, row['decimal']
1766
        assert row['dec'] is None, row['dec']
1767
        assert row['fixed'] is None, row['fixed']
1768
        assert row['numeric'] is None, row['numeric']
1769
        assert row['date'] is None, row['date']
1770
        assert row['time'] is None, row['time']
1771
        assert row['time'] is None, row['time']
1772
        assert row['datetime'] is None, row['datetime']
1773
        assert row['datetime_6'] is None, row['datetime_6']
1774
        assert row['timestamp'] is None, row['timestamp']
1775
        assert row['timestamp_6'] is None, row['timestamp_6']
1776
        assert row['year'] is None, row['year']
1777
        assert row['char_100'] is None, row['char_100']
1778
        assert row['binary_100'] is None, row['binary_100']
1779
        assert row['varchar_200'] is None, row['varchar_200']
1780
        assert row['varbinary_200'] is None, row['varbinary_200']
1781
        assert row['longtext'] is None, row['longtext']
1782
        assert row['mediumtext'] is None, row['mediumtext']
1783
        assert row['text'] is None, row['text']
1784
        assert row['tinytext'] is None, row['tinytext']
1785
        assert row['longblob'] is None, row['longblob']
1786
        assert row['mediumblob'] is None, row['mediumblob']
1787
        assert row['blob'] is None, row['blob']
1788
        assert row['tinyblob'] is None, row['tinyblob']
1789
        assert row['json'] is None, row['json']
1790
        assert row['enum'] is None, row['enum']
1791
        assert row['set'] is None, row['set']
1792
        assert row['bit'] is None, row['bit']
1793

1794
        conn.close()
1795

1796
    def test_alltypes_arrow(self):
1797
        if self.conn.driver in ['http', 'https']:
1798
            self.skipTest('Data API does not surface unsigned int information')
1799

1800
        conn = s2.connect(database=type(self).dbname, results_type='arrow')
1801
        cur = conn.cursor()
1802

1803
        cur.execute('select * from alltypes where id = 0')
1804
        out = cur.fetchone()
1805
        row = out.to_pylist()[0]
1806

1807
        dtypes = [
1808
            ('id', 'int32'),
1809
            ('tinyint', 'int8'),
1810
            ('unsigned_tinyint', 'uint8'),
1811
            ('bool', 'int8'),
1812
            ('boolean', 'int8'),
1813
            ('smallint', 'int16'),
1814
            ('unsigned_smallint', 'uint16'),
1815
            ('mediumint', 'int32'),
1816
            ('unsigned_mediumint', 'uint32'),
1817
            ('int24', 'int32'),
1818
            ('unsigned_int24', 'uint32'),
1819
            ('int', 'int32'),
1820
            ('unsigned_int', 'uint32'),
1821
            ('integer', 'int32'),
1822
            ('unsigned_integer', 'uint32'),
1823
            ('bigint', 'int64'),
1824
            ('unsigned_bigint', 'uint64'),
1825
            ('float', 'float'),
1826
            ('double', 'double'),
1827
            ('real', 'double'),
1828
            ('decimal', 'decimal128(20, 6)'),
1829
            ('dec', 'decimal128(20, 6)'),
1830
            ('fixed', 'decimal128(20, 6)'),
1831
            ('numeric', 'decimal128(20, 6)'),
1832
            ('date', 'date64[ms]'),
1833
            ('time', 'duration[us]'),
1834
            ('time_6', 'duration[us]'),
1835
            ('datetime', 'timestamp[us]'),
1836
            ('datetime_6', 'timestamp[us]'),
1837
            ('timestamp', 'timestamp[us]'),
1838
            ('timestamp_6', 'timestamp[us]'),
1839
            ('year', 'int16'),
1840
            ('char_100', 'string'),
1841
            ('binary_100', 'binary'),
1842
            ('varchar_200', 'string'),
1843
            ('varbinary_200', 'binary'),
1844
            ('longtext', 'string'),
1845
            ('mediumtext', 'string'),
1846
            ('text', 'string'),
1847
            ('tinytext', 'string'),
1848
            ('longblob', 'binary'),
1849
            ('mediumblob', 'binary'),
1850
            ('blob', 'binary'),
1851
            ('tinyblob', 'binary'),
1852
            ('json', 'string'),
1853
            ('enum', 'string'),
1854
            ('set', 'string'),
1855
            ('bit', 'binary'),
1856
        ]
1857

1858
        assert [(x.name, str(x.type)) for x in out.schema] == dtypes
1859

1860
        bits = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
1861

1862
        assert row['id'] == 0, row['id']
1863
        assert row['tinyint'] == 80, row['tinyint']
1864
        assert row['unsigned_tinyint'] == 85, row['unsigned_tinyint']
1865
        assert row['bool'] == 0, row['bool']
1866
        assert row['boolean'] == 1, row['boolean']
1867
        assert row['smallint'] == -27897, row['smallint']
1868
        assert row['unsigned_smallint'] == 27897, row['unsigned_smallint']
1869
        assert row['mediumint'] == 104729, row['mediumint']
1870
        assert row['unsigned_mediumint'] == 120999, row['unsigned_mediumint']
1871
        assert row['int24'] == -200899, row['int24']
1872
        assert row['unsigned_int24'] == 407709, row['unsigned_int24']
1873
        assert row['int'] == -1295369311, row['int']
1874
        assert row['unsigned_int'] == 3872362332, row['unsigned_int']
1875
        assert row['integer'] == -1741727421, row['integer']
1876
        assert row['unsigned_integer'] == 3198387363, row['unsigned_integer']
1877
        assert row['bigint'] == -266883847, row['bigint']
1878
        assert row['unsigned_bigint'] == 980007287362, row['unsigned_bigint']
1879
        assert row['float'] - -146487000.0 < 0.00001, row['float']
1880
        assert row['double'] == -474646154.719356, row['double']
1881
        assert row['real'] == -901409776.279346, row['real']
1882
        assert row['decimal'] == decimal.Decimal(
1883
            '28111097.610822',
1884
        ), row['decimal']
1885
        assert row['dec'] == decimal.Decimal('389451155.931428'), row['dec']
1886
        assert row['fixed'] == decimal.Decimal(
1887
            '-143773416.044092',
1888
        ), row['fixed']
1889
        assert row['numeric'] == decimal.Decimal(
1890
            '866689461.300046',
1891
        ), row['numeric']
1892
        assert row['date'] == datetime.date(8524, 11, 10), row['date']
1893
        assert row['time'] == datetime.timedelta(minutes=7), row['time']
1894
        assert row['datetime'] == datetime.datetime(
1895
            9948, 3, 11, 15, 29, 22,
1896
        ), row['datetime']
1897
        assert row['datetime_6'] == datetime.datetime(
1898
            1756, 10, 29, 2, 2, 42, 8,
1899
        ), row['datetime_6']
1900
        assert row['timestamp'] == datetime.datetime(
1901
            1980, 12, 31, 1, 10, 23,
1902
        ), row['timestamp']
1903
        assert row['timestamp_6'] == datetime.datetime(
1904
            1991, 1, 2, 22, 15, 10, 6,
1905
        ), row['timestamp_6']
1906
        assert row['year'] == 1923, row['year']
1907
        assert row['char_100'] == \
1908
            'This is a test of a 100 character column.', row['char_100']
1909
        assert row['binary_100'] == bytearray(
1910
            bits + [0] * 84,
1911
        ), row['binary_100']
1912
        assert row['varchar_200'] == \
1913
            'This is a test of a variable character column.', row['varchar_200']
1914
        assert row['varbinary_200'] == bytearray(
1915
            bits * 2,
1916
        ), row['varbinary_200']
1917
        assert row['longtext'] == 'This is a longtext column.', row['longtext']
1918
        assert row['mediumtext'] == 'This is a mediumtext column.', row['mediumtext']
1919
        assert row['text'] == 'This is a text column.', row['text']
1920
        assert row['tinytext'] == 'This is a tinytext column.'
1921
        assert row['longblob'] == bytearray(bits * 3), row['longblob']
1922
        assert row['mediumblob'] == bytearray(bits * 2), row['mediumblob']
1923
        assert row['blob'] == bytearray(bits), row['blob']
1924
        assert row['tinyblob'] == bytearray(
1925
            [10, 11, 12, 13, 14, 15],
1926
        ), row['tinyblob']
1927
        assert row['json'] == '{"a":10,"b":2.75,"c":"hello world"}', row['json']
1928
        assert row['enum'] == 'one', row['enum']
1929
        assert row['set'] == 'two', row['set']
1930
        assert row['bit'] == b'\x00\x00\x00\x00\x00\x00\x00\x80', row['bit']
1931

1932
        conn.close()
1933

1934
    def test_alltypes_no_nulls_arrow(self):
1935
        if self.conn.driver in ['http', 'https']:
1936
            self.skipTest('Data API does not surface unsigned int information')
1937

1938
        conn = s2.connect(database=type(self).dbname, results_type='arrow')
1939
        cur = conn.cursor()
1940

1941
        cur.execute('select * from alltypes_no_nulls where id = 0')
1942
        out = cur.fetchone()
1943

1944
        row = out.to_pylist()[0]
1945

1946
        dtypes = [
1947
            ('id', 'int32'),
1948
            ('tinyint', 'int8'),
1949
            ('unsigned_tinyint', 'uint8'),
1950
            ('bool', 'int8'),
1951
            ('boolean', 'int8'),
1952
            ('smallint', 'int16'),
1953
            ('unsigned_smallint', 'uint16'),
1954
            ('mediumint', 'int32'),
1955
            ('unsigned_mediumint', 'uint32'),
1956
            ('int24', 'int32'),
1957
            ('unsigned_int24', 'uint32'),
1958
            ('int', 'int32'),
1959
            ('unsigned_int', 'uint32'),
1960
            ('integer', 'int32'),
1961
            ('unsigned_integer', 'uint32'),
1962
            ('bigint', 'int64'),
1963
            ('unsigned_bigint', 'uint64'),
1964
            ('float', 'float'),
1965
            ('double', 'double'),
1966
            ('real', 'double'),
1967
            ('decimal', 'decimal128(20, 6)'),
1968
            ('dec', 'decimal128(20, 6)'),
1969
            ('fixed', 'decimal128(20, 6)'),
1970
            ('numeric', 'decimal128(20, 6)'),
1971
            ('date', 'date64[ms]'),
1972
            ('time', 'duration[us]'),
1973
            ('time_6', 'duration[us]'),
1974
            ('datetime', 'timestamp[us]'),
1975
            ('datetime_6', 'timestamp[us]'),
1976
            ('timestamp', 'timestamp[us]'),
1977
            ('timestamp_6', 'timestamp[us]'),
1978
            ('year', 'int16'),
1979
            ('char_100', 'string'),
1980
            ('binary_100', 'binary'),
1981
            ('varchar_200', 'string'),
1982
            ('varbinary_200', 'binary'),
1983
            ('longtext', 'string'),
1984
            ('mediumtext', 'string'),
1985
            ('text', 'string'),
1986
            ('tinytext', 'string'),
1987
            ('longblob', 'binary'),
1988
            ('mediumblob', 'binary'),
1989
            ('blob', 'binary'),
1990
            ('tinyblob', 'binary'),
1991
            ('json', 'string'),
1992
            ('enum', 'string'),
1993
            ('set', 'string'),
1994
            ('bit', 'binary'),
1995
        ]
1996

1997
        assert [(x.name, str(x.type)) for x in out.schema] == dtypes
1998

1999
        bits = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
2000

2001
        assert row['id'] == 0, row['id']
2002
        assert row['tinyint'] == 80, row['tinyint']
2003
        assert row['unsigned_tinyint'] == 85, row['unsigned_tinyint']
2004
        assert row['bool'] == 0, row['bool']
2005
        assert row['boolean'] == 1, row['boolean']
2006
        assert row['smallint'] == -27897, row['smallint']
2007
        assert row['unsigned_smallint'] == 27897, row['unsigned_smallint']
2008
        assert row['mediumint'] == 104729, row['mediumint']
2009
        assert row['unsigned_mediumint'] == 120999, row['unsigned_mediumint']
2010
        assert row['int24'] == -200899, row['int24']
2011
        assert row['unsigned_int24'] == 407709, row['unsigned_int24']
2012
        assert row['int'] == -1295369311, row['int']
2013
        assert row['unsigned_int'] == 3872362332, row['unsigned_int']
2014
        assert row['integer'] == -1741727421, row['integer']
2015
        assert row['unsigned_integer'] == 3198387363, row['unsigned_integer']
2016
        assert row['bigint'] == -266883847, row['bigint']
2017
        assert row['unsigned_bigint'] == 980007287362, row['unsigned_bigint']
2018
        assert row['float'] - -146487000.0 < 0.00001, row['float']
2019
        assert row['double'] == -474646154.719356, row['double']
2020
        assert row['real'] == -901409776.279346, row['real']
2021
        assert row['decimal'] == decimal.Decimal(
2022
            '28111097.610822',
2023
        ), row['decimal']
2024
        assert row['dec'] == decimal.Decimal('389451155.931428'), row['dec']
2025
        assert row['fixed'] == decimal.Decimal(
2026
            '-143773416.044092',
2027
        ), row['fixed']
2028
        assert row['numeric'] == decimal.Decimal(
2029
            '866689461.300046',
2030
        ), row['numeric']
2031
        assert row['date'] == datetime.date(8524, 11, 10), row['date']
2032
        assert row['time'] == datetime.timedelta(minutes=7), row['time']
2033
        assert row['datetime'] == datetime.datetime(
2034
            9948, 3, 11, 15, 29, 22,
2035
        ), row['datetime']
2036
        assert row['datetime_6'] == datetime.datetime(
2037
            1756, 10, 29, 2, 2, 42, 8,
2038
        ), row['datetime_6']
2039
        assert row['timestamp'] == datetime.datetime(
2040
            1980, 12, 31, 1, 10, 23,
2041
        ), row['timestamp']
2042
        assert row['timestamp_6'] == datetime.datetime(
2043
            1991, 1, 2, 22, 15, 10, 6,
2044
        ), row['timestamp_6']
2045
        assert row['year'] == 1923, row['year']
2046
        assert row['char_100'] == \
2047
            'This is a test of a 100 character column.', row['char_100']
2048
        assert row['binary_100'] == bytearray(
2049
            bits + [0] * 84,
2050
        ), row['binary_100']
2051
        assert row['varchar_200'] == \
2052
            'This is a test of a variable character column.', row['varchar_200']
2053
        assert row['varbinary_200'] == bytearray(
2054
            bits * 2,
2055
        ), row['varbinary_200']
2056
        assert row['longtext'] == 'This is a longtext column.', row['longtext']
2057
        assert row['mediumtext'] == 'This is a mediumtext column.', row['mediumtext']
2058
        assert row['text'] == 'This is a text column.', row['text']
2059
        assert row['tinytext'] == 'This is a tinytext column.'
2060
        assert row['longblob'] == bytearray(bits * 3), row['longblob']
2061
        assert row['mediumblob'] == bytearray(bits * 2), row['mediumblob']
2062
        assert row['blob'] == bytearray(bits), row['blob']
2063
        assert row['tinyblob'] == bytearray(
2064
            [10, 11, 12, 13, 14, 15],
2065
        ), row['tinyblob']
2066
        assert row['json'] == '{"a":10,"b":2.75,"c":"hello world"}', row['json']
2067
        assert row['enum'] == 'one', row['enum']
2068
        assert row['set'] == 'two', row['set']
2069
        assert row['bit'] == b'\x00\x00\x00\x00\x00\x00\x00\x80', row['bit']
2070

2071
        conn.close()
2072

2073
    def test_alltypes_min_max_arrow(self):
2074
        if self.conn.driver in ['http', 'https']:
2075
            self.skipTest('Data API does not surface unsigned int information')
2076

2077
        conn = s2.connect(database=type(self).dbname, results_type='arrow')
2078
        cur = conn.cursor()
2079

2080
        cur.execute('select * from alltypes_no_nulls')
2081
        cur.fetchall()
2082

2083
        cur.execute('select * from alltypes')
2084
        cur.fetchall()
2085

2086
        conn.close()
2087

2088
    def test_alltypes_nulls_arrow(self):
2089
        conn = s2.connect(database=type(self).dbname, results_type='arrow')
2090
        cur = conn.cursor()
2091

2092
        cur.execute('select * from alltypes where id = 1')
2093
        out = cur.fetchone()
2094
        row = out.to_pylist()[0]
2095

2096
        assert row['id'] == 1, row['id']
2097
        assert row['tinyint'] is None, row['tinyint']
2098
        assert row['bool'] is None, row['bool']
2099
        assert row['boolean'] is None, row['boolean']
2100
        assert row['smallint'] is None, row['smallint']
2101
        assert row['mediumint'] is None, row['mediumint']
2102
        assert row['int24'] is None, row['int24']
2103
        assert row['int'] is None, row['int']
2104
        assert row['integer'] is None, row['integer']
2105
        assert row['bigint'] is None, row['bigint']
2106
        assert row['float'] is None, row['float']
2107
        assert row['double'] is None, row['double']
2108
        assert row['real'] is None, row['real']
2109
        assert row['decimal'] is None, row['decimal']
2110
        assert row['dec'] is None, row['dec']
2111
        assert row['fixed'] is None, row['fixed']
2112
        assert row['numeric'] is None, row['numeric']
2113
        assert row['date'] is None, row['date']
2114
        assert row['time'] is None, row['time']
2115
        assert row['time'] is None, row['time']
2116
        assert row['datetime'] is None, row['datetime']
2117
        assert row['datetime_6'] is None, row['datetime_6']
2118
        assert row['timestamp'] is None, row['timestamp']
2119
        assert row['timestamp_6'] is None, row['timestamp_6']
2120
        assert row['year'] is None, row['year']
2121
        assert row['char_100'] is None, row['char_100']
2122
        assert row['binary_100'] is None, row['binary_100']
2123
        assert row['varchar_200'] is None, row['varchar_200']
2124
        assert row['varbinary_200'] is None, row['varbinary_200']
2125
        assert row['longtext'] is None, row['longtext']
2126
        assert row['mediumtext'] is None, row['mediumtext']
2127
        assert row['text'] is None, row['text']
2128
        assert row['tinytext'] is None, row['tinytext']
2129
        assert row['longblob'] is None, row['longblob']
2130
        assert row['mediumblob'] is None, row['mediumblob']
2131
        assert row['blob'] is None, row['blob']
2132
        assert row['tinyblob'] is None, row['tinyblob']
2133
        assert row['json'] is None, row['json']
2134
        assert row['enum'] is None, row['enum']
2135
        assert row['set'] is None, row['set']
2136
        assert row['bit'] is None, row['bit']
2137

2138
        conn.close()
2139

2140
    def test_alltypes_nulls(self):
2141
        self.cur.execute('select * from alltypes where id = 1')
2142
        names = [x[0] for x in self.cur.description]
2143
        types = [x[1] for x in self.cur.description]
2144
        out = self.cur.fetchone()
2145
        row = dict(zip(names, out))
2146
        typ = dict(zip(names, types))
2147

2148
        def otype(x):
2149
            return x
2150

2151
        assert row['id'] == 1, row['id']
2152
        assert typ['id'] == otype(3), typ['id']
2153

2154
        assert row['tinyint'] is None, row['tinyint']
2155
        assert typ['tinyint'] == otype(1), typ['tinyint']
2156

2157
        assert row['bool'] is None, row['bool']
2158
        assert typ['bool'] == otype(1), typ['bool']
2159

2160
        assert row['boolean'] is None, row['boolean']
2161
        assert typ['boolean'] == otype(1), typ['boolean']
2162

2163
        assert row['smallint'] is None, row['smallint']
2164
        assert typ['smallint'] == otype(2), typ['smallint']
2165

2166
        assert row['mediumint'] is None, row['mediumint']
2167
        assert typ['mediumint'] == otype(9), typ['mediumint']
2168

2169
        assert row['int24'] is None, row['int24']
2170
        assert typ['int24'] == otype(9), typ['int24']
2171

2172
        assert row['int'] is None, row['int']
2173
        assert typ['int'] == otype(3), typ['int']
2174

2175
        assert row['integer'] is None, row['integer']
2176
        assert typ['integer'] == otype(3), typ['integer']
2177

2178
        assert row['bigint'] is None, row['bigint']
2179
        assert typ['bigint'] == otype(8), typ['bigint']
2180

2181
        assert row['float'] is None, row['float']
2182
        assert typ['float'] == otype(4), typ['float']
2183

2184
        assert row['double'] is None, row['double']
2185
        assert typ['double'] == otype(5), typ['double']
2186

2187
        assert row['real'] is None, row['real']
2188
        assert typ['real'] == otype(5), typ['real']
2189

2190
        assert row['decimal'] is None, row['decimal']
2191
        assert typ['decimal'] == otype(246), typ['decimal']
2192

2193
        assert row['dec'] is None, row['dec']
2194
        assert typ['dec'] == otype(246), typ['dec']
2195

2196
        assert row['fixed'] is None, row['fixed']
2197
        assert typ['fixed'] == otype(246), typ['fixed']
2198

2199
        assert row['numeric'] is None, row['numeric']
2200
        assert typ['numeric'] == otype(246), typ['numeric']
2201

2202
        assert row['date'] is None, row['date']
2203
        assert typ['date'] == 10, typ['date']
2204

2205
        assert row['time'] is None, row['time']
2206
        assert typ['time'] == 11, typ['time']
2207

2208
        assert row['time'] is None, row['time']
2209
        assert typ['time_6'] == 11, typ['time_6']
2210

2211
        assert row['datetime'] is None, row['datetime']
2212
        assert typ['datetime'] == 12, typ['datetime']
2213

2214
        assert row['datetime_6'] is None, row['datetime_6']
2215
        assert typ['datetime'] == 12, typ['datetime']
2216

2217
        assert row['timestamp'] is None, row['timestamp']
2218
        assert typ['timestamp'] == otype(7), typ['timestamp']
2219

2220
        assert row['timestamp_6'] is None, row['timestamp_6']
2221
        assert typ['timestamp_6'] == otype(7), typ['timestamp_6']
2222

2223
        assert row['year'] is None, row['year']
2224
        assert typ['year'] == otype(13), typ['year']
2225

2226
        assert row['char_100'] is None, row['char_100']
2227
        assert typ['char_100'] == otype(254), typ['char_100']
2228

2229
        assert row['binary_100'] is None, row['binary_100']
2230
        assert typ['binary_100'] == otype(254), typ['binary_100']
2231

2232
        assert row['varchar_200'] is None, typ['varchar_200']
2233
        assert typ['varchar_200'] == otype(
2234
            253,
2235
        ), typ['varchar_200']  # why not 15?
2236

2237
        assert row['varbinary_200'] is None, row['varbinary_200']
2238
        assert typ['varbinary_200'] == otype(
2239
            253,
2240
        ), typ['varbinary_200']  # why not 15?
2241

2242
        assert row['longtext'] is None, row['longtext']
2243
        assert typ['longtext'] == otype(251), typ['longtext']
2244

2245
        assert row['mediumtext'] is None, row['mediumtext']
2246
        assert typ['mediumtext'] == otype(250), typ['mediumtext']
2247

2248
        assert row['text'] is None, row['text']
2249
        assert typ['text'] == otype(252), typ['text']
2250

2251
        assert row['tinytext'] is None, row['tinytext']
2252
        assert typ['tinytext'] == otype(249), typ['tinytext']
2253

2254
        assert row['longblob'] is None, row['longblob']
2255
        assert typ['longblob'] == otype(251), typ['longblob']
2256

2257
        assert row['mediumblob'] is None, row['mediumblob']
2258
        assert typ['mediumblob'] == otype(250), typ['mediumblob']
2259

2260
        assert row['blob'] is None, row['blob']
2261
        assert typ['blob'] == otype(252), typ['blob']
2262

2263
        assert row['tinyblob'] is None, row['tinyblob']
2264
        assert typ['tinyblob'] == otype(249), typ['tinyblob']
2265

2266
        assert row['json'] is None, row['json']
2267
        assert typ['json'] == otype(245), typ['json']
2268

2269
        assert row['enum'] is None, row['enum']
2270
        assert typ['enum'] == otype(253), typ['enum']  # mysql code: 247
2271

2272
        assert row['set'] is None, row['set']
2273
        assert typ['set'] == otype(253), typ['set']  # mysql code: 248
2274

2275
        assert row['bit'] is None, row['bit']
2276
        assert typ['bit'] == otype(16), typ['bit']
2277

2278
    def test_name_check(self):
2279
        nc = sc._name_check
2280
        assert nc('foo') == 'foo'
2281
        assert nc('Foo') == 'Foo'
2282
        assert nc('Foo_Bar') == 'Foo_Bar'
2283
        assert nc('Foo_Bar2') == 'Foo_Bar2'
2284

2285
        with self.assertRaises(ValueError):
2286
            assert nc('foo.bar')
2287

2288
        with self.assertRaises(ValueError):
2289
            assert nc('2foo')
2290

2291
        with self.assertRaises(ValueError):
2292
            assert nc('')
2293

2294
    def test_echo(self):
2295
        self.cur.execute('echo return_int()')
2296

2297
        out = self.cur.fetchall()
2298
        assert list(out) == [(1234567890,)], out
2299

2300
        out = self.cur.nextset()
2301
        assert out is None, out
2302

2303
    def test_echo_with_result_set(self):
2304
        self.cur.execute('echo result_set_and_return_int()')
2305

2306
        out = self.cur.fetchall()
2307
        assert list(out) == [(5,)], out
2308

2309
        out = self.cur.nextset()
2310
        assert out is True, out
2311

2312
        out = self.cur.fetchall()
2313
        assert list(out) == [(1, 2, 3)], out
2314

2315
        out = self.cur.nextset()
2316
        assert out is True, out
2317

2318
        out = self.cur.fetchall()
2319
        assert list(out) == [(1234567890,)], out
2320

2321
        out = self.cur.nextset()
2322
        assert out is None, out
2323

2324
    def test_callproc(self):
2325
        self.cur.callproc('get_animal', ['cats'])
2326

2327
        out = self.cur.fetchall()
2328
        assert list(out) == [(5,)], out
2329

2330
        out = self.cur.nextset()
2331
        assert out is True, out
2332

2333
        out = self.cur.fetchall()
2334
        assert list(out) == [(1, 2, 3)], out
2335

2336
        out = self.cur.nextset()
2337
        assert out is True, out
2338

2339
        # Always get an empty set at the end
2340
        out = self.cur.fetchall()
2341
        assert list(out) == [], out
2342

2343
        out = self.cur.nextset()
2344
        assert out is None, out
2345

2346
    def test_callproc_no_args(self):
2347
        self.cur.callproc('no_args')
2348

2349
        out = self.cur.fetchall()
2350
        assert list(out) == [(4, 5, 6)], out
2351

2352
        out = self.cur.nextset()
2353
        assert out is True, out
2354

2355
        # Always get an empty set at the end
2356
        out = self.cur.fetchall()
2357
        assert list(out) == [], out
2358

2359
        out = self.cur.nextset()
2360
        assert out is None, out
2361

2362
    def test_callproc_return_int(self):
2363
        self.cur.callproc('result_set_and_return_int')
2364

2365
        out = self.cur.fetchall()
2366
        assert list(out) == [(5,)], out
2367

2368
        out = self.cur.nextset()
2369
        assert out is True, out
2370

2371
        out = self.cur.fetchall()
2372
        assert list(out) == [(1, 2, 3)], out
2373

2374
        out = self.cur.nextset()
2375
        assert out is True, out
2376

2377
        # Always get an empty set at the end
2378
        out = self.cur.fetchall()
2379
        assert list(out) == [], out
2380

2381
        out = self.cur.nextset()
2382
        assert out is None, out
2383

2384
    def test_callproc_bad_args(self):
2385
        self.cur.callproc('get_animal', [10])
2386

2387
        out = self.cur.fetchall()
2388
        assert list(out) == [], out
2389

2390
        out = self.cur.nextset()
2391
        assert out is True, out
2392

2393
        out = self.cur.fetchall()
2394
        assert list(out) == [(1, 2, 3)], out
2395

2396
        out = self.cur.nextset()
2397
        assert out is True, out
2398

2399
        # Always get an empty set at the end
2400
        out = self.cur.fetchall()
2401
        assert list(out) == [], out
2402

2403
        out = self.cur.nextset()
2404
        assert out is None, out
2405

2406
    def test_callproc_too_many_args(self):
2407
        with self.assertRaises((
2408
            s2.ProgrammingError,
2409
            s2.OperationalError,
2410
            s2.InternalError,
2411
            TypeError,
2412
        )):
2413
            self.cur.callproc('get_animal', ['cats', 'dogs'])
2414

2415
        with self.assertRaises((
2416
            s2.ProgrammingError,
2417
            s2.OperationalError,
2418
            s2.InternalError,
2419
            TypeError,
2420
        )):
2421
            self.cur.callproc('get_animal', [])
2422

2423
        with self.assertRaises((
2424
            s2.ProgrammingError,
2425
            s2.OperationalError,
2426
            s2.InternalError,
2427
            TypeError,
2428
        )):
2429
            self.cur.callproc('get_animal')
2430

2431
    def test_cursor_close(self):
2432
        self.cur.close()
2433

2434
        self.cur.close()
2435

2436
        with self.assertRaises(s2.ProgrammingError):
2437
            self.cur.callproc('foo')
2438

2439
        with self.assertRaises(s2.ProgrammingError):
2440
            self.cur.execute('select 1')
2441

2442
#       with self.assertRaises(s2.ProgrammingError):
2443
#           self.cur.executemany('select 1')
2444

2445
        with self.assertRaises(s2.ProgrammingError):
2446
            self.cur.fetchone()
2447

2448
        with self.assertRaises(s2.ProgrammingError):
2449
            self.cur.fetchall()
2450

2451
        with self.assertRaises(s2.ProgrammingError):
2452
            self.cur.fetchmany()
2453

2454
        with self.assertRaises(s2.ProgrammingError):
2455
            self.cur.nextset()
2456

2457
#       with self.assertRaises(s2.ProgrammingError):
2458
#           self.cur.setinputsizes([])
2459

2460
#       with self.assertRaises(s2.ProgrammingError):
2461
#           self.cur.setoutputsize(10)
2462

2463
        with self.assertRaises(s2.ProgrammingError):
2464
            self.cur.scroll(2)
2465

2466
        with self.assertRaises(s2.InterfaceError):
2467
            self.cur.next()
2468

2469
        # The following attributes are still accessible after close.
2470

2471
        assert isinstance(self.cur.messages, list), self.cur.messages
2472

2473
        assert isinstance(self.cur.rowcount, (int, type(None))), self.cur.rowcount
2474

2475
        assert isinstance(self.cur.lastrowid, (int, type(None))), self.cur.lastrowid
2476

2477
    def test_setinputsizes(self):
2478
        self.cur.setinputsizes([10, 20, 30])
2479

2480
    def test_setoutputsize(self):
2481
        self.cur.setoutputsize(100)
2482

2483
    def test_scroll(self):
2484
        self.cur.execute('select * from data order by name')
2485

2486
        out = self.cur.fetchone()
2487
        assert out[1] == 'antelopes', out[1]
2488
        assert self.cur.rownumber == 1, self.cur.rownumber
2489

2490
        self.cur.scroll(3)
2491

2492
        out = self.cur.fetchone()
2493
        assert out[1] == 'elephants', out[1]
2494
        assert self.cur.rownumber == 5, self.cur.rownumber
2495

2496
        try:
2497
            self.cur.scroll(0, mode='absolute')
2498
            assert self.cur.rownumber == 0, self.cur.rownumber
2499

2500
            out = self.cur.fetchone()
2501
            assert out[1] == 'antelopes', out[1]
2502
            assert self.cur.rownumber == 1, self.cur.rownumber
2503
        except s2.NotSupportedError:
2504
            pass
2505

2506
        with self.assertRaises((ValueError, s2.ProgrammingError)):
2507
            self.cur.scroll(0, mode='badmode')
2508

2509
    def test_autocommit(self):
2510
        if self.conn.driver in ['http', 'https']:
2511
            self.skipTest('Can not set autocommit in HTTP')
2512

2513
        orig = self.conn.locals.autocommit
2514

2515
        self.conn.autocommit(True)
2516
        val = self.conn.locals.autocommit
2517
        assert val is True, val
2518

2519
        self.conn.autocommit(False)
2520
        val = self.conn.locals.autocommit
2521
        assert val is False, val
2522

2523
        self.conn.locals.autocommit = orig
2524

2525
    def test_conn_close(self):
2526
        self.conn.close()
2527

2528
        with self.assertRaises(s2.Error):
2529
            self.conn.close()
2530

2531
        with self.assertRaises(s2.InterfaceError):
2532
            self.conn.autocommit(False)
2533

2534
        with self.assertRaises(s2.InterfaceError):
2535
            self.conn.commit()
2536

2537
        with self.assertRaises(s2.InterfaceError):
2538
            self.conn.rollback()
2539

2540
#       with self.assertRaises(s2.InterfaceError):
2541
#           self.conn.cursor()
2542

2543
#       with self.assertRaises(s2.InterfaceError):
2544
#           self.conn.messages
2545

2546
        with self.assertRaises(s2.InterfaceError):
2547
            self.conn.globals.autocommit = True
2548

2549
        with self.assertRaises(s2.InterfaceError):
2550
            self.conn.globals.autocommit
2551

2552
        with self.assertRaises(s2.InterfaceError):
2553
            self.conn.locals.autocommit = True
2554

2555
        with self.assertRaises(s2.InterfaceError):
2556
            self.conn.locals.autocommit
2557

2558
        with self.assertRaises(s2.InterfaceError):
2559
            self.conn.enable_data_api()
2560

2561
        with self.assertRaises(s2.InterfaceError):
2562
            self.conn.disable_data_api()
2563

2564
    def test_rollback(self):
2565
        if self.conn.driver in ['http', 'https']:
2566
            self.skipTest('Can not set autocommit in HTTP')
2567

2568
        self.conn.autocommit(False)
2569

2570
        self.cur.execute('select * from data')
2571
        out = self.cur.fetchall()
2572
        assert len(out) == 5, len(out)
2573

2574
        self.cur.execute("INSERT INTO data SET id='f', name='frogs', value=3")
2575

2576
        self.cur.execute('select * from data')
2577
        out = self.cur.fetchall()
2578
        assert len(out) == 6, len(out)
2579

2580
        self.conn.rollback()
2581

2582
        self.cur.execute('select * from data')
2583
        out = self.cur.fetchall()
2584
        assert len(out) == 5, len(out)
2585

2586
    def test_commit(self):
2587
        if self.conn.driver in ['http', 'https']:
2588
            self.skipTest('Can not set autocommit in HTTP')
2589

2590
        self.conn.autocommit(False)
2591

2592
        self.cur.execute('select * from data')
2593
        out = self.cur.fetchall()
2594
        assert len(out) == 5, len(out)
2595

2596
        self.cur.execute("INSERT INTO data SET id='f', name='frogs', value=3")
2597

2598
        self.cur.execute('select * from data')
2599
        out = self.cur.fetchall()
2600
        assert len(out) == 6, len(out)
2601

2602
        self.conn.commit()
2603

2604
        self.cur.execute('select * from data')
2605
        out = self.cur.fetchall()
2606
        assert len(out) == 6, len(out)
2607

2608
        self.cur.execute("delete from data where id='f'")
2609

2610
        self.cur.execute('select * from data')
2611
        out = self.cur.fetchall()
2612
        assert len(out) == 5, len(out)
2613

2614
        self.conn.commit()
2615

2616
        self.cur.execute('select * from data')
2617
        out = self.cur.fetchall()
2618
        assert len(out) == 5, len(out)
2619

2620
    def test_global_var(self):
2621
        orig = self.conn.globals.enable_external_functions
2622

2623
        self.conn.globals.enable_external_functions = True
2624
        val = self.conn.globals.enable_external_functions
2625
        assert val is True, val
2626

2627
        self.conn.globals.enable_external_functions = False
2628
        val = self.conn.globals.enable_external_functions
2629
        assert val is False, val
2630

2631
        self.conn.globals.enable_external_functions = orig
2632
        val = self.conn.globals.enable_external_functions
2633
        assert val == orig, val
2634

2635
    def test_session_var(self):
2636
        if self.conn.driver in ['http', 'https']:
2637
            self.skipTest('Can not change session variable in HTTP')
2638

2639
        orig = self.conn.locals.enable_multipartition_queries
2640

2641
        self.conn.locals.enable_multipartition_queries = True
2642
        val = self.conn.locals.enable_multipartition_queries
2643
        assert val is True, val
2644

2645
        self.conn.locals.enable_multipartition_queries = False
2646
        val = self.conn.locals.enable_multipartition_queries
2647
        assert val is False, val
2648

2649
        self.conn.locals.enable_multipartition_queries = orig
2650
        val = self.conn.locals.enable_multipartition_queries
2651
        assert val == orig, val
2652

2653
    def test_local_infile(self):
2654
        if self.conn.driver in ['http', 'https']:
2655
            self.skipTest('Can not load local files in HTTP')
2656

2657
        path = os.path.join(os.path.dirname(__file__), 'local_infile.csv')
2658
        tblname = ('TEST_' + str(uuid.uuid4())).replace('-', '_')
2659

2660
        self.cur.execute(f'''
2661
            create table `{tblname}` (
2662
                first_name char(20) not null,
2663
                last_name char(30) not null,
2664
                age  int not null
2665
            ) collate="utf8_unicode_ci";
2666
        ''')
2667

2668
        try:
2669
            self.cur.execute(
2670
                f'load data local infile %s into table {tblname} '
2671
                'fields terminated by "," lines terminated by "\n";', [path],
2672
            )
2673

2674
            self.cur.execute(f'select * from {tblname} order by first_name')
2675
            out = list(self.cur)
2676
            assert out == [
2677
                ('John', 'Doe', 34),
2678
                ('Patty', 'Jones', 57),
2679
                ('Sandy', 'Smith', 24),
2680
            ], out
2681

2682
        finally:
2683
            self.cur.execute(f'drop table {tblname};')
2684

2685
    def test_converters(self):
2686
        def upper(x):
2687
            if isinstance(x, str):
2688
                return x.upper()
2689
            return x
2690

2691
        convs = {
2692
            15: upper,
2693
            249: upper,
2694
            250: upper,
2695
            251: upper,
2696
            252: upper,
2697
            253: upper,
2698
            254: upper,
2699
        }
2700

2701
        with s2.connect(database=type(self).dbname, conv=convs) as conn:
2702
            with conn.cursor() as cur:
2703
                cur.execute('select * from alltypes where id = 0')
2704
                names = [x[0] for x in cur.description]
2705
                out = cur.fetchone()
2706
                row = dict(zip(names, out))
2707
                assert row['longtext'] == 'THIS IS A LONGTEXT COLUMN.', \
2708
                    row['longtext']
2709
                assert row['mediumtext'] == 'THIS IS A MEDIUMTEXT COLUMN.', \
2710
                    row['mediumtext']
2711
                assert row['text'] == 'THIS IS A TEXT COLUMN.', \
2712
                    row['text']
2713
                assert row['tinytext'] == 'THIS IS A TINYTEXT COLUMN.', \
2714
                    row['tinytext']
2715

2716
        with s2.connect(database=type(self).dbname) as conn:
2717
            with conn.cursor() as cur:
2718
                cur.execute('select * from alltypes where id = 0')
2719
                names = [x[0] for x in cur.description]
2720
                out = cur.fetchone()
2721
                row = dict(zip(names, out))
2722
                assert row['longtext'] == 'This is a longtext column.', \
2723
                    row['longtext']
2724
                assert row['mediumtext'] == 'This is a mediumtext column.', \
2725
                    row['mediumtext']
2726
                assert row['text'] == 'This is a text column.', \
2727
                    row['text']
2728
                assert row['tinytext'] == 'This is a tinytext column.', \
2729
                    row['tinytext']
2730

2731
    def test_results_type(self):
2732
        columns = [
2733
            'id', 'tinyint', 'unsigned_tinyint', 'bool', 'boolean',
2734
            'smallint', 'unsigned_smallint', 'mediumint', 'unsigned_mediumint',
2735
            'int24', 'unsigned_int24', 'int', 'unsigned_int',
2736
            'integer', 'unsigned_integer', 'bigint', 'unsigned_bigint',
2737
            'float', 'double', 'real', 'decimal', 'dec', 'fixed', 'numeric',
2738
            'date', 'time', 'time_6', 'datetime', 'datetime_6', 'timestamp',
2739
            'timestamp_6', 'year', 'char_100', 'binary_100', 'varchar_200',
2740
            'varbinary_200', 'longtext', 'mediumtext', 'text', 'tinytext',
2741
            'longblob', 'mediumblob', 'blob', 'tinyblob', 'json', 'enum',
2742
            'set', 'bit',
2743
        ]
2744

2745
        with s2.connect(database=type(self).dbname, results_type='tuples') as conn:
2746
            with conn.cursor() as cur:
2747
                cur.execute('select * from alltypes')
2748
                out = cur.fetchall()
2749
                assert type(out[0]) is tuple, type(out[0])
2750
                assert len(out[0]) == len(columns), len(out[0])
2751

2752
        with s2.connect(database=type(self).dbname, results_type='namedtuples') as conn:
2753
            with conn.cursor() as cur:
2754
                cur.execute('select * from alltypes')
2755
                out = cur.fetchall()
2756
                assert type(out[0]).__name__ == 'Row', type(out)
2757
                for i, name in enumerate(columns):
2758
                    assert hasattr(out[0], name)
2759
                    assert out[0][i] == getattr(out[0], name)
2760

2761
        with s2.connect(database=type(self).dbname, results_type='dicts') as conn:
2762
            with conn.cursor() as cur:
2763
                cur.execute('select * from alltypes')
2764
                out = cur.fetchall()
2765
                assert type(out[0]) is dict, type(out)
2766
                assert list(out[0].keys()) == columns, out[0].keys()
2767

2768
    def test_results_format(self):
2769
        with self.assertWarns(DeprecationWarning):
2770
            with s2.connect(database=type(self).dbname, results_format='dicts') as conn:
2771
                with conn.cursor() as cur:
2772
                    cur.execute('select * from alltypes')
2773
                    out = cur.fetchall()
2774
                    assert type(out[0]) is dict, type(out)
2775

2776
    def test_multi_statements(self):
2777
        if self.conn.driver not in ['http', 'https']:
2778
            with s2.connect(database=type(self).dbname, multi_statements=True) as conn:
2779
                with conn.cursor() as cur:
2780
                    cur.execute('SELECT 1; SELECT 2;')
2781
                    self.assertEqual([(1,)], list(cur))
2782

2783
                    r = cur.nextset()
2784
                    self.assertTrue(r)
2785

2786
                    self.assertEqual([(2,)], list(cur))
2787
                    self.assertIsNone(cur.nextset())
2788

2789
    def test_client_found_rows(self):
2790
        if self.conn.driver not in ['http', 'https']:
2791
            with s2.connect(database=type(self).dbname, client_found_rows=False) as conn:
2792
                with conn.cursor() as cur:
2793
                    tag = str(uuid.uuid4()).replace('-', '_')
2794
                    table_name = f'test_client_found_rows_{tag}'
2795
                    cur.execute(f"CREATE TABLE {table_name} (id BIGINT \
2796
                                PRIMARY KEY, s TEXT DEFAULT 'def');")
2797
                    cur.execute(f'INSERT INTO {table_name} (id) \
2798
                        VALUES (1), (2), (3);')
2799
                    cur.execute(f"UPDATE {table_name} SET s = 'def' \
2800
                                WHERE id = 1;")
2801
                    # UPDATE statement above is not changing any rows,
2802
                    # so affected_rows is 0 if client_found_rows is False (default)
2803
                    self.assertEqual(0, conn.affected_rows())
2804
                    cur.execute(f'DROP TABLE {table_name};')
2805

2806
            with s2.connect(database=type(self).dbname, client_found_rows=True) as conn:
2807
                with conn.cursor() as cur:
2808
                    tag = str(uuid.uuid4()).replace('-', '_')
2809
                    table_name = f'test_client_found_rows_{tag}'
2810
                    cur.execute(f"CREATE TABLE {table_name} (id BIGINT \
2811
                                PRIMARY KEY, s TEXT DEFAULT 'def');")
2812
                    cur.execute(f'INSERT INTO {table_name} (id) \
2813
                        VALUES (1), (2), (3);')
2814
                    cur.execute(f"UPDATE {table_name} SET s = 'def' \
2815
                                WHERE id = 1;")
2816
                    # UPDATE statement above is not changing any rows,
2817
                    # but affected_rows is 1 as 1 row is subject to update, and
2818
                    # this is what affected_rows return when client_found_rows is True
2819
                    self.assertEqual(1, conn.affected_rows())
2820
                    cur.execute(f'DROP TABLE {table_name};')
2821

2822
    def test_connect_timeout(self):
2823
        with s2.connect(database=type(self).dbname, connect_timeout=8) as conn:
2824
            with conn.cursor() as cur:
2825
                cur.execute('SELECT 1')
2826
                self.assertEqual([(1,)], list(cur))
2827

2828
    def test_show_accessors(self):
2829
        out = self.conn.show.columns('data')
2830
        assert out.columns == [
2831
            'Name', 'Type', 'Null',
2832
            'Key', 'Default', 'Extra',
2833
        ], out.columns
2834
        assert out.Name == ['id', 'name', 'value'], out.Name
2835
        assert out.Type == ['varchar(255)', 'varchar(255)', 'bigint(20)'], out.Type
2836
        assert str(out).count('varchar(255)') == 2, out
2837

2838
        html = out._repr_html_()
2839
        assert html.count('varchar(255)') == 2
2840
        assert html.count('bigint(20)') == 1
2841
        assert '<table' in html
2842

2843
        out = self.conn.show.tables()
2844
        assert out.columns == ['Name'], out.columns
2845
        assert 'data' in out.Name, out.Name
2846
        assert 'alltypes' in out.Name, out.Name
2847

2848
        out = self.conn.show.warnings()
2849

2850
        out = self.conn.show.errors()
2851

2852
        out = self.conn.show.databases()
2853
        assert out.columns == ['Name'], out.columns
2854
        assert 'information_schema' in out.Name
2855

2856
        out = self.conn.show.database_status()
2857
        assert out.columns == ['Name', 'Value'], out.columns
2858
        assert 'database' in out.Name
2859

2860
        out = self.conn.show.global_status()
2861
        assert out.columns == ['Name', 'Value'], out.columns
2862

2863
        out = self.conn.show.indexes('data')
2864
        assert 'Name' in out.columns, out.columns
2865
        assert 'KeyName' in out.columns, out.columns
2866
        assert out.Name == ['data'], out.Name
2867

2868
        out = self.conn.show.functions()
2869

2870
        out = self.conn.show.partitions()
2871
        assert 'Name' in out.columns, out.columns
2872
        assert 'Role' in out.columns, out.columns
2873

2874
        out = self.conn.show.pipelines()
2875

2876
        # out = self.conn.show.plan(1)
2877

2878
        # out = self.conn.show.plancache()
2879

2880
        out = self.conn.show.processlist()
2881
        assert 'Name' in out.columns, out.columns
2882
        assert 'Command' in out.columns, out.columns
2883

2884
        # out = self.conn.show.reproduction()
2885

2886
        out = self.conn.show.schemas()
2887
        assert out.columns == ['Name'], out.columns
2888
        assert 'information_schema' in out.Name
2889

2890
        out = self.conn.show.session_status()
2891
        assert out.columns == ['Name', 'Value']
2892

2893
        out = self.conn.show.status()
2894
        assert out.columns == ['Name', 'Value']
2895

2896
        out = self.conn.show.table_status()
2897
        assert 'Name' in out.columns, out.columns
2898
        assert 'alltypes' in out.Name, out.Name
2899
        assert 'data' in out.Name, out.Name
2900

2901
        out = self.conn.show.procedures()
2902

2903
        out = self.conn.show.aggregates()
2904

2905
        # out = self.conn.show.create_aggregate('aname')
2906

2907
        # out = self.conn.show.create_function('fname')
2908

2909
        # out = self.conn.show.create_pipeline('pname')
2910

2911
        out = self.conn.show.create_table('data')
2912
        assert 'Name' in out.columns, out.columns
2913
        assert 'CreateTable' in out.columns, out.columns
2914
        assert '`id` varchar(255)' in out.CreateTable[0], out.CreateTable[0]
2915
        assert '`name` varchar(255)' in out.CreateTable[0], out.CreateTable[0]
2916
        assert '`value` bigint(20)' in out.CreateTable[0], out.CreateTable[0]
2917

2918
        # out = self.conn.show.create_view('vname')
2919

2920
    def test_f32_vectors(self):
2921
        if self.conn.driver in ['http', 'https']:
2922
            self.skipTest('Data API does not surface vector information')
2923

2924
        self.cur.execute('show variables like "enable_extended_types_metadata"')
2925
        out = list(self.cur)
2926
        if not out or out[0][1].lower() == 'off':
2927
            self.skipTest('Database engine does not support extended types metadata')
2928

2929
        self.cur.execute('select a from f32_vectors order by id')
2930
        out = list(self.cur)
2931

2932
        if hasattr(out[0][0], 'dtype'):
2933
            assert out[0][0].dtype is np.dtype('float32')
2934
            assert out[1][0].dtype is np.dtype('float32')
2935
            assert out[2][0].dtype is np.dtype('float32')
2936

2937
        np.testing.assert_array_equal(
2938
            out[0][0],
2939
            np.array([0.267261237, 0.534522474, 0.801783681], dtype=np.float32),
2940
        )
2941
        np.testing.assert_array_equal(
2942
            out[1][0],
2943
            np.array([0.371390671, 0.557085991, 0.742781341], dtype=np.float32),
2944
        )
2945
        np.testing.assert_array_equal(
2946
            out[2][0],
2947
            np.array([-0.424264073, -0.565685451, 0.707106829], dtype=np.float32),
2948
        )
2949

2950
    def test_f64_vectors(self):
2951
        if self.conn.driver in ['http', 'https']:
2952
            self.skipTest('Data API does not surface vector information')
2953

2954
        self.cur.execute('show variables like "enable_extended_types_metadata"')
2955
        out = list(self.cur)
2956
        if not out or out[0][1].lower() == 'off':
2957
            self.skipTest('Database engine does not support extended types metadata')
2958

2959
        self.cur.execute('select a from f64_vectors order by id')
2960
        out = list(self.cur)
2961

2962
        if hasattr(out[0][0], 'dtype'):
2963
            assert out[0][0].dtype is np.dtype('float64')
2964
            assert out[1][0].dtype is np.dtype('float64')
2965
            assert out[2][0].dtype is np.dtype('float64')
2966

2967
        np.testing.assert_array_equal(
2968
            out[0][0],
2969
            np.array([0.267261237, 0.534522474, 0.801783681], dtype=np.float64),
2970
        )
2971
        np.testing.assert_array_equal(
2972
            out[1][0],
2973
            np.array([0.371390671, 0.557085991, 0.742781341], dtype=np.float64),
2974
        )
2975
        np.testing.assert_array_equal(
2976
            out[2][0],
2977
            np.array([-0.424264073, -0.565685451, 0.707106829], dtype=np.float64),
2978
        )
2979

2980
    def test_i8_vectors(self):
2981
        if self.conn.driver in ['http', 'https']:
2982
            self.skipTest('Data API does not surface vector information')
2983

2984
        self.cur.execute('show variables like "enable_extended_types_metadata"')
2985
        out = list(self.cur)
2986
        if not out or out[0][1].lower() == 'off':
2987
            self.skipTest('Database engine does not support extended types metadata')
2988

2989
        self.cur.execute('select a from i8_vectors order by id')
2990
        out = list(self.cur)
2991

2992
        if hasattr(out[0][0], 'dtype'):
2993
            assert out[0][0].dtype is np.dtype('int8')
2994
            assert out[1][0].dtype is np.dtype('int8')
2995
            assert out[2][0].dtype is np.dtype('int8')
2996

2997
        np.testing.assert_array_equal(
2998
            out[0][0],
2999
            np.array([1, 2, 3], dtype=np.int8),
3000
        )
3001
        np.testing.assert_array_equal(
3002
            out[1][0],
3003
            np.array([4, 5, 6], dtype=np.int8),
3004
        )
3005
        np.testing.assert_array_equal(
3006
            out[2][0],
3007
            np.array([-1, -4, 8], dtype=np.int8),
3008
        )
3009

3010
    def test_i16_vectors(self):
3011
        if self.conn.driver in ['http', 'https']:
3012
            self.skipTest('Data API does not surface vector information')
3013

3014
        self.cur.execute('show variables like "enable_extended_types_metadata"')
3015
        out = list(self.cur)
3016
        if not out or out[0][1].lower() == 'off':
3017
            self.skipTest('Database engine does not support extended types metadata')
3018

3019
        self.cur.execute('select a from i16_vectors order by id')
3020
        out = list(self.cur)
3021

3022
        if hasattr(out[0][0], 'dtype'):
3023
            assert out[0][0].dtype is np.dtype('int16')
3024
            assert out[1][0].dtype is np.dtype('int16')
3025
            assert out[2][0].dtype is np.dtype('int16')
3026

3027
        np.testing.assert_array_equal(
3028
            out[0][0],
3029
            np.array([1, 2, 3], dtype=np.int16),
3030
        )
3031
        np.testing.assert_array_equal(
3032
            out[1][0],
3033
            np.array([4, 5, 6], dtype=np.int16),
3034
        )
3035
        np.testing.assert_array_equal(
3036
            out[2][0],
3037
            np.array([-1, -4, 8], dtype=np.int16),
3038
        )
3039

3040
    def test_i32_vectors(self):
3041
        if self.conn.driver in ['http', 'https']:
3042
            self.skipTest('Data API does not surface vector information')
3043

3044
        self.cur.execute('show variables like "enable_extended_types_metadata"')
3045
        out = list(self.cur)
3046
        if not out or out[0][1].lower() == 'off':
3047
            self.skipTest('Database engine does not support extended types metadata')
3048

3049
        self.cur.execute('select a from i32_vectors order by id')
3050
        out = list(self.cur)
3051

3052
        if hasattr(out[0][0], 'dtype'):
3053
            assert out[0][0].dtype is np.dtype('int32')
3054
            assert out[1][0].dtype is np.dtype('int32')
3055
            assert out[2][0].dtype is np.dtype('int32')
3056

3057
        np.testing.assert_array_equal(
3058
            out[0][0],
3059
            np.array([1, 2, 3], dtype=np.int32),
3060
        )
3061
        np.testing.assert_array_equal(
3062
            out[1][0],
3063
            np.array([4, 5, 6], dtype=np.int32),
3064
        )
3065
        np.testing.assert_array_equal(
3066
            out[2][0],
3067
            np.array([-1, -4, 8], dtype=np.int32),
3068
        )
3069

3070
    def test_i64_vectors(self):
3071
        if self.conn.driver in ['http', 'https']:
3072
            self.skipTest('Data API does not surface vector information')
3073

3074
        self.cur.execute('show variables like "enable_extended_types_metadata"')
3075
        out = list(self.cur)
3076
        if not out or out[0][1].lower() == 'off':
3077
            self.skipTest('Database engine does not support extended types metadata')
3078

3079
        self.cur.execute('select a from i64_vectors order by id')
3080
        out = list(self.cur)
3081

3082
        if hasattr(out[0][0], 'dtype'):
3083
            assert out[0][0].dtype is np.dtype('int64')
3084
            assert out[1][0].dtype is np.dtype('int64')
3085
            assert out[2][0].dtype is np.dtype('int64')
3086

3087
        np.testing.assert_array_equal(
3088
            out[0][0],
3089
            np.array([1, 2, 3], dtype=np.int64),
3090
        )
3091
        np.testing.assert_array_equal(
3092
            out[1][0],
3093
            np.array([4, 5, 6], dtype=np.int64),
3094
        )
3095
        np.testing.assert_array_equal(
3096
            out[2][0],
3097
            np.array([-1, -4, 8], dtype=np.int64),
3098
        )
3099

3100

3101
if __name__ == '__main__':
3102
    import nose2
3103
    nose2.main()
3104

3105
Product

Resources

Company
