1# mysql/types.py 2# Copyright (C) 2005-2021 the SQLAlchemy authors and contributors 3# <see AUTHORS file> 4# 5# This module is part of SQLAlchemy and is released under 6# the MIT License: http://www.opensource.org/licenses/mit-license.php 7 8import datetime 9 10from ... import exc 11from ... import types as sqltypes 12from ... import util 13 14 15class _NumericType(object): 16 """Base for MySQL numeric types. 17 18 This is the base both for NUMERIC as well as INTEGER, hence 19 it's a mixin. 20 21 """ 22 23 def __init__(self, unsigned=False, zerofill=False, **kw): 24 self.unsigned = unsigned 25 self.zerofill = zerofill 26 super(_NumericType, self).__init__(**kw) 27 28 def __repr__(self): 29 return util.generic_repr( 30 self, to_inspect=[_NumericType, sqltypes.Numeric] 31 ) 32 33 34class _FloatType(_NumericType, sqltypes.Float): 35 def __init__(self, precision=None, scale=None, asdecimal=True, **kw): 36 if isinstance(self, (REAL, DOUBLE)) and ( 37 (precision is None and scale is not None) 38 or (precision is not None and scale is None) 39 ): 40 raise exc.ArgumentError( 41 "You must specify both precision and scale or omit " 42 "both altogether." 43 ) 44 super(_FloatType, self).__init__( 45 precision=precision, asdecimal=asdecimal, **kw 46 ) 47 self.scale = scale 48 49 def __repr__(self): 50 return util.generic_repr( 51 self, to_inspect=[_FloatType, _NumericType, sqltypes.Float] 52 ) 53 54 55class _IntegerType(_NumericType, sqltypes.Integer): 56 def __init__(self, display_width=None, **kw): 57 self.display_width = display_width 58 super(_IntegerType, self).__init__(**kw) 59 60 def __repr__(self): 61 return util.generic_repr( 62 self, to_inspect=[_IntegerType, _NumericType, sqltypes.Integer] 63 ) 64 65 66class _StringType(sqltypes.String): 67 """Base for MySQL string types.""" 68 69 def __init__( 70 self, 71 charset=None, 72 collation=None, 73 ascii=False, # noqa 74 binary=False, 75 unicode=False, 76 national=False, 77 **kw 78 ): 79 self.charset = charset 80 81 # allow collate= or collation= 82 kw.setdefault("collation", kw.pop("collate", collation)) 83 84 self.ascii = ascii 85 self.unicode = unicode 86 self.binary = binary 87 self.national = national 88 super(_StringType, self).__init__(**kw) 89 90 def __repr__(self): 91 return util.generic_repr( 92 self, to_inspect=[_StringType, sqltypes.String] 93 ) 94 95 96class _MatchType(sqltypes.Float, sqltypes.MatchType): 97 def __init__(self, **kw): 98 # TODO: float arguments? 99 sqltypes.Float.__init__(self) 100 sqltypes.MatchType.__init__(self) 101 102 103class NUMERIC(_NumericType, sqltypes.NUMERIC): 104 """MySQL NUMERIC type.""" 105 106 __visit_name__ = "NUMERIC" 107 108 def __init__(self, precision=None, scale=None, asdecimal=True, **kw): 109 """Construct a NUMERIC. 110 111 :param precision: Total digits in this number. If scale and precision 112 are both None, values are stored to limits allowed by the server. 113 114 :param scale: The number of digits after the decimal point. 115 116 :param unsigned: a boolean, optional. 117 118 :param zerofill: Optional. If true, values will be stored as strings 119 left-padded with zeros. Note that this does not effect the values 120 returned by the underlying database API, which continue to be 121 numeric. 122 123 """ 124 super(NUMERIC, self).__init__( 125 precision=precision, scale=scale, asdecimal=asdecimal, **kw 126 ) 127 128 129class DECIMAL(_NumericType, sqltypes.DECIMAL): 130 """MySQL DECIMAL type.""" 131 132 __visit_name__ = "DECIMAL" 133 134 def __init__(self, precision=None, scale=None, asdecimal=True, **kw): 135 """Construct a DECIMAL. 136 137 :param precision: Total digits in this number. If scale and precision 138 are both None, values are stored to limits allowed by the server. 139 140 :param scale: The number of digits after the decimal point. 141 142 :param unsigned: a boolean, optional. 143 144 :param zerofill: Optional. If true, values will be stored as strings 145 left-padded with zeros. Note that this does not effect the values 146 returned by the underlying database API, which continue to be 147 numeric. 148 149 """ 150 super(DECIMAL, self).__init__( 151 precision=precision, scale=scale, asdecimal=asdecimal, **kw 152 ) 153 154 155class DOUBLE(_FloatType): 156 """MySQL DOUBLE type.""" 157 158 __visit_name__ = "DOUBLE" 159 160 def __init__(self, precision=None, scale=None, asdecimal=True, **kw): 161 """Construct a DOUBLE. 162 163 .. note:: 164 165 The :class:`.DOUBLE` type by default converts from float 166 to Decimal, using a truncation that defaults to 10 digits. 167 Specify either ``scale=n`` or ``decimal_return_scale=n`` in order 168 to change this scale, or ``asdecimal=False`` to return values 169 directly as Python floating points. 170 171 :param precision: Total digits in this number. If scale and precision 172 are both None, values are stored to limits allowed by the server. 173 174 :param scale: The number of digits after the decimal point. 175 176 :param unsigned: a boolean, optional. 177 178 :param zerofill: Optional. If true, values will be stored as strings 179 left-padded with zeros. Note that this does not effect the values 180 returned by the underlying database API, which continue to be 181 numeric. 182 183 """ 184 super(DOUBLE, self).__init__( 185 precision=precision, scale=scale, asdecimal=asdecimal, **kw 186 ) 187 188 189class REAL(_FloatType, sqltypes.REAL): 190 """MySQL REAL type.""" 191 192 __visit_name__ = "REAL" 193 194 def __init__(self, precision=None, scale=None, asdecimal=True, **kw): 195 """Construct a REAL. 196 197 .. note:: 198 199 The :class:`.REAL` type by default converts from float 200 to Decimal, using a truncation that defaults to 10 digits. 201 Specify either ``scale=n`` or ``decimal_return_scale=n`` in order 202 to change this scale, or ``asdecimal=False`` to return values 203 directly as Python floating points. 204 205 :param precision: Total digits in this number. If scale and precision 206 are both None, values are stored to limits allowed by the server. 207 208 :param scale: The number of digits after the decimal point. 209 210 :param unsigned: a boolean, optional. 211 212 :param zerofill: Optional. If true, values will be stored as strings 213 left-padded with zeros. Note that this does not effect the values 214 returned by the underlying database API, which continue to be 215 numeric. 216 217 """ 218 super(REAL, self).__init__( 219 precision=precision, scale=scale, asdecimal=asdecimal, **kw 220 ) 221 222 223class FLOAT(_FloatType, sqltypes.FLOAT): 224 """MySQL FLOAT type.""" 225 226 __visit_name__ = "FLOAT" 227 228 def __init__(self, precision=None, scale=None, asdecimal=False, **kw): 229 """Construct a FLOAT. 230 231 :param precision: Total digits in this number. If scale and precision 232 are both None, values are stored to limits allowed by the server. 233 234 :param scale: The number of digits after the decimal point. 235 236 :param unsigned: a boolean, optional. 237 238 :param zerofill: Optional. If true, values will be stored as strings 239 left-padded with zeros. Note that this does not effect the values 240 returned by the underlying database API, which continue to be 241 numeric. 242 243 """ 244 super(FLOAT, self).__init__( 245 precision=precision, scale=scale, asdecimal=asdecimal, **kw 246 ) 247 248 def bind_processor(self, dialect): 249 return None 250 251 252class INTEGER(_IntegerType, sqltypes.INTEGER): 253 """MySQL INTEGER type.""" 254 255 __visit_name__ = "INTEGER" 256 257 def __init__(self, display_width=None, **kw): 258 """Construct an INTEGER. 259 260 :param display_width: Optional, maximum display width for this number. 261 262 :param unsigned: a boolean, optional. 263 264 :param zerofill: Optional. If true, values will be stored as strings 265 left-padded with zeros. Note that this does not effect the values 266 returned by the underlying database API, which continue to be 267 numeric. 268 269 """ 270 super(INTEGER, self).__init__(display_width=display_width, **kw) 271 272 273class BIGINT(_IntegerType, sqltypes.BIGINT): 274 """MySQL BIGINTEGER type.""" 275 276 __visit_name__ = "BIGINT" 277 278 def __init__(self, display_width=None, **kw): 279 """Construct a BIGINTEGER. 280 281 :param display_width: Optional, maximum display width for this number. 282 283 :param unsigned: a boolean, optional. 284 285 :param zerofill: Optional. If true, values will be stored as strings 286 left-padded with zeros. Note that this does not effect the values 287 returned by the underlying database API, which continue to be 288 numeric. 289 290 """ 291 super(BIGINT, self).__init__(display_width=display_width, **kw) 292 293 294class MEDIUMINT(_IntegerType): 295 """MySQL MEDIUMINTEGER type.""" 296 297 __visit_name__ = "MEDIUMINT" 298 299 def __init__(self, display_width=None, **kw): 300 """Construct a MEDIUMINTEGER 301 302 :param display_width: Optional, maximum display width for this number. 303 304 :param unsigned: a boolean, optional. 305 306 :param zerofill: Optional. If true, values will be stored as strings 307 left-padded with zeros. Note that this does not effect the values 308 returned by the underlying database API, which continue to be 309 numeric. 310 311 """ 312 super(MEDIUMINT, self).__init__(display_width=display_width, **kw) 313 314 315class TINYINT(_IntegerType): 316 """MySQL TINYINT type.""" 317 318 __visit_name__ = "TINYINT" 319 320 def __init__(self, display_width=None, **kw): 321 """Construct a TINYINT. 322 323 :param display_width: Optional, maximum display width for this number. 324 325 :param unsigned: a boolean, optional. 326 327 :param zerofill: Optional. If true, values will be stored as strings 328 left-padded with zeros. Note that this does not effect the values 329 returned by the underlying database API, which continue to be 330 numeric. 331 332 """ 333 super(TINYINT, self).__init__(display_width=display_width, **kw) 334 335 336class SMALLINT(_IntegerType, sqltypes.SMALLINT): 337 """MySQL SMALLINTEGER type.""" 338 339 __visit_name__ = "SMALLINT" 340 341 def __init__(self, display_width=None, **kw): 342 """Construct a SMALLINTEGER. 343 344 :param display_width: Optional, maximum display width for this number. 345 346 :param unsigned: a boolean, optional. 347 348 :param zerofill: Optional. If true, values will be stored as strings 349 left-padded with zeros. Note that this does not effect the values 350 returned by the underlying database API, which continue to be 351 numeric. 352 353 """ 354 super(SMALLINT, self).__init__(display_width=display_width, **kw) 355 356 357class BIT(sqltypes.TypeEngine): 358 """MySQL BIT type. 359 360 This type is for MySQL 5.0.3 or greater for MyISAM, and 5.0.5 or greater 361 for MyISAM, MEMORY, InnoDB and BDB. For older versions, use a 362 MSTinyInteger() type. 363 364 """ 365 366 __visit_name__ = "BIT" 367 368 def __init__(self, length=None): 369 """Construct a BIT. 370 371 :param length: Optional, number of bits. 372 373 """ 374 self.length = length 375 376 def result_processor(self, dialect, coltype): 377 """Convert a MySQL's 64 bit, variable length binary string to a long. 378 379 TODO: this is MySQL-db, pyodbc specific. OurSQL and mysqlconnector 380 already do this, so this logic should be moved to those dialects. 381 382 """ 383 384 def process(value): 385 if value is not None: 386 v = 0 387 for i in value: 388 if not isinstance(i, int): 389 i = ord(i) # convert byte to int on Python 2 390 v = v << 8 | i 391 return v 392 return value 393 394 return process 395 396 397class TIME(sqltypes.TIME): 398 """MySQL TIME type. """ 399 400 __visit_name__ = "TIME" 401 402 def __init__(self, timezone=False, fsp=None): 403 """Construct a MySQL TIME type. 404 405 :param timezone: not used by the MySQL dialect. 406 :param fsp: fractional seconds precision value. 407 MySQL 5.6 supports storage of fractional seconds; 408 this parameter will be used when emitting DDL 409 for the TIME type. 410 411 .. note:: 412 413 DBAPI driver support for fractional seconds may 414 be limited; current support includes 415 MySQL Connector/Python. 416 417 """ 418 super(TIME, self).__init__(timezone=timezone) 419 self.fsp = fsp 420 421 def result_processor(self, dialect, coltype): 422 time = datetime.time 423 424 def process(value): 425 # convert from a timedelta value 426 if value is not None: 427 microseconds = value.microseconds 428 seconds = value.seconds 429 minutes = seconds // 60 430 return time( 431 minutes // 60, 432 minutes % 60, 433 seconds - minutes * 60, 434 microsecond=microseconds, 435 ) 436 else: 437 return None 438 439 return process 440 441 442class TIMESTAMP(sqltypes.TIMESTAMP): 443 """MySQL TIMESTAMP type.""" 444 445 __visit_name__ = "TIMESTAMP" 446 447 def __init__(self, timezone=False, fsp=None): 448 """Construct a MySQL TIMESTAMP type. 449 450 :param timezone: not used by the MySQL dialect. 451 :param fsp: fractional seconds precision value. 452 MySQL 5.6.4 supports storage of fractional seconds; 453 this parameter will be used when emitting DDL 454 for the TIMESTAMP type. 455 456 .. note:: 457 458 DBAPI driver support for fractional seconds may 459 be limited; current support includes 460 MySQL Connector/Python. 461 462 """ 463 super(TIMESTAMP, self).__init__(timezone=timezone) 464 self.fsp = fsp 465 466 467class DATETIME(sqltypes.DATETIME): 468 """MySQL DATETIME type.""" 469 470 __visit_name__ = "DATETIME" 471 472 def __init__(self, timezone=False, fsp=None): 473 """Construct a MySQL DATETIME type. 474 475 :param timezone: not used by the MySQL dialect. 476 :param fsp: fractional seconds precision value. 477 MySQL 5.6.4 supports storage of fractional seconds; 478 this parameter will be used when emitting DDL 479 for the DATETIME type. 480 481 .. note:: 482 483 DBAPI driver support for fractional seconds may 484 be limited; current support includes 485 MySQL Connector/Python. 486 487 """ 488 super(DATETIME, self).__init__(timezone=timezone) 489 self.fsp = fsp 490 491 492class YEAR(sqltypes.TypeEngine): 493 """MySQL YEAR type, for single byte storage of years 1901-2155.""" 494 495 __visit_name__ = "YEAR" 496 497 def __init__(self, display_width=None): 498 self.display_width = display_width 499 500 501class TEXT(_StringType, sqltypes.TEXT): 502 """MySQL TEXT type, for text up to 2^16 characters.""" 503 504 __visit_name__ = "TEXT" 505 506 def __init__(self, length=None, **kw): 507 """Construct a TEXT. 508 509 :param length: Optional, if provided the server may optimize storage 510 by substituting the smallest TEXT type sufficient to store 511 ``length`` characters. 512 513 :param charset: Optional, a column-level character set for this string 514 value. Takes precedence to 'ascii' or 'unicode' short-hand. 515 516 :param collation: Optional, a column-level collation for this string 517 value. Takes precedence to 'binary' short-hand. 518 519 :param ascii: Defaults to False: short-hand for the ``latin1`` 520 character set, generates ASCII in schema. 521 522 :param unicode: Defaults to False: short-hand for the ``ucs2`` 523 character set, generates UNICODE in schema. 524 525 :param national: Optional. If true, use the server's configured 526 national character set. 527 528 :param binary: Defaults to False: short-hand, pick the binary 529 collation type that matches the column's character set. Generates 530 BINARY in schema. This does not affect the type of data stored, 531 only the collation of character data. 532 533 """ 534 super(TEXT, self).__init__(length=length, **kw) 535 536 537class TINYTEXT(_StringType): 538 """MySQL TINYTEXT type, for text up to 2^8 characters.""" 539 540 __visit_name__ = "TINYTEXT" 541 542 def __init__(self, **kwargs): 543 """Construct a TINYTEXT. 544 545 :param charset: Optional, a column-level character set for this string 546 value. Takes precedence to 'ascii' or 'unicode' short-hand. 547 548 :param collation: Optional, a column-level collation for this string 549 value. Takes precedence to 'binary' short-hand. 550 551 :param ascii: Defaults to False: short-hand for the ``latin1`` 552 character set, generates ASCII in schema. 553 554 :param unicode: Defaults to False: short-hand for the ``ucs2`` 555 character set, generates UNICODE in schema. 556 557 :param national: Optional. If true, use the server's configured 558 national character set. 559 560 :param binary: Defaults to False: short-hand, pick the binary 561 collation type that matches the column's character set. Generates 562 BINARY in schema. This does not affect the type of data stored, 563 only the collation of character data. 564 565 """ 566 super(TINYTEXT, self).__init__(**kwargs) 567 568 569class MEDIUMTEXT(_StringType): 570 """MySQL MEDIUMTEXT type, for text up to 2^24 characters.""" 571 572 __visit_name__ = "MEDIUMTEXT" 573 574 def __init__(self, **kwargs): 575 """Construct a MEDIUMTEXT. 576 577 :param charset: Optional, a column-level character set for this string 578 value. Takes precedence to 'ascii' or 'unicode' short-hand. 579 580 :param collation: Optional, a column-level collation for this string 581 value. Takes precedence to 'binary' short-hand. 582 583 :param ascii: Defaults to False: short-hand for the ``latin1`` 584 character set, generates ASCII in schema. 585 586 :param unicode: Defaults to False: short-hand for the ``ucs2`` 587 character set, generates UNICODE in schema. 588 589 :param national: Optional. If true, use the server's configured 590 national character set. 591 592 :param binary: Defaults to False: short-hand, pick the binary 593 collation type that matches the column's character set. Generates 594 BINARY in schema. This does not affect the type of data stored, 595 only the collation of character data. 596 597 """ 598 super(MEDIUMTEXT, self).__init__(**kwargs) 599 600 601class LONGTEXT(_StringType): 602 """MySQL LONGTEXT type, for text up to 2^32 characters.""" 603 604 __visit_name__ = "LONGTEXT" 605 606 def __init__(self, **kwargs): 607 """Construct a LONGTEXT. 608 609 :param charset: Optional, a column-level character set for this string 610 value. Takes precedence to 'ascii' or 'unicode' short-hand. 611 612 :param collation: Optional, a column-level collation for this string 613 value. Takes precedence to 'binary' short-hand. 614 615 :param ascii: Defaults to False: short-hand for the ``latin1`` 616 character set, generates ASCII in schema. 617 618 :param unicode: Defaults to False: short-hand for the ``ucs2`` 619 character set, generates UNICODE in schema. 620 621 :param national: Optional. If true, use the server's configured 622 national character set. 623 624 :param binary: Defaults to False: short-hand, pick the binary 625 collation type that matches the column's character set. Generates 626 BINARY in schema. This does not affect the type of data stored, 627 only the collation of character data. 628 629 """ 630 super(LONGTEXT, self).__init__(**kwargs) 631 632 633class VARCHAR(_StringType, sqltypes.VARCHAR): 634 """MySQL VARCHAR type, for variable-length character data.""" 635 636 __visit_name__ = "VARCHAR" 637 638 def __init__(self, length=None, **kwargs): 639 """Construct a VARCHAR. 640 641 :param charset: Optional, a column-level character set for this string 642 value. Takes precedence to 'ascii' or 'unicode' short-hand. 643 644 :param collation: Optional, a column-level collation for this string 645 value. Takes precedence to 'binary' short-hand. 646 647 :param ascii: Defaults to False: short-hand for the ``latin1`` 648 character set, generates ASCII in schema. 649 650 :param unicode: Defaults to False: short-hand for the ``ucs2`` 651 character set, generates UNICODE in schema. 652 653 :param national: Optional. If true, use the server's configured 654 national character set. 655 656 :param binary: Defaults to False: short-hand, pick the binary 657 collation type that matches the column's character set. Generates 658 BINARY in schema. This does not affect the type of data stored, 659 only the collation of character data. 660 661 """ 662 super(VARCHAR, self).__init__(length=length, **kwargs) 663 664 665class CHAR(_StringType, sqltypes.CHAR): 666 """MySQL CHAR type, for fixed-length character data.""" 667 668 __visit_name__ = "CHAR" 669 670 def __init__(self, length=None, **kwargs): 671 """Construct a CHAR. 672 673 :param length: Maximum data length, in characters. 674 675 :param binary: Optional, use the default binary collation for the 676 national character set. This does not affect the type of data 677 stored, use a BINARY type for binary data. 678 679 :param collation: Optional, request a particular collation. Must be 680 compatible with the national character set. 681 682 """ 683 super(CHAR, self).__init__(length=length, **kwargs) 684 685 @classmethod 686 def _adapt_string_for_cast(self, type_): 687 # copy the given string type into a CHAR 688 # for the purposes of rendering a CAST expression 689 type_ = sqltypes.to_instance(type_) 690 if isinstance(type_, sqltypes.CHAR): 691 return type_ 692 elif isinstance(type_, _StringType): 693 return CHAR( 694 length=type_.length, 695 charset=type_.charset, 696 collation=type_.collation, 697 ascii=type_.ascii, 698 binary=type_.binary, 699 unicode=type_.unicode, 700 national=False, # not supported in CAST 701 ) 702 else: 703 return CHAR(length=type_.length) 704 705 706class NVARCHAR(_StringType, sqltypes.NVARCHAR): 707 """MySQL NVARCHAR type. 708 709 For variable-length character data in the server's configured national 710 character set. 711 """ 712 713 __visit_name__ = "NVARCHAR" 714 715 def __init__(self, length=None, **kwargs): 716 """Construct an NVARCHAR. 717 718 :param length: Maximum data length, in characters. 719 720 :param binary: Optional, use the default binary collation for the 721 national character set. This does not affect the type of data 722 stored, use a BINARY type for binary data. 723 724 :param collation: Optional, request a particular collation. Must be 725 compatible with the national character set. 726 727 """ 728 kwargs["national"] = True 729 super(NVARCHAR, self).__init__(length=length, **kwargs) 730 731 732class NCHAR(_StringType, sqltypes.NCHAR): 733 """MySQL NCHAR type. 734 735 For fixed-length character data in the server's configured national 736 character set. 737 """ 738 739 __visit_name__ = "NCHAR" 740 741 def __init__(self, length=None, **kwargs): 742 """Construct an NCHAR. 743 744 :param length: Maximum data length, in characters. 745 746 :param binary: Optional, use the default binary collation for the 747 national character set. This does not affect the type of data 748 stored, use a BINARY type for binary data. 749 750 :param collation: Optional, request a particular collation. Must be 751 compatible with the national character set. 752 753 """ 754 kwargs["national"] = True 755 super(NCHAR, self).__init__(length=length, **kwargs) 756 757 758class TINYBLOB(sqltypes._Binary): 759 """MySQL TINYBLOB type, for binary data up to 2^8 bytes.""" 760 761 __visit_name__ = "TINYBLOB" 762 763 764class MEDIUMBLOB(sqltypes._Binary): 765 """MySQL MEDIUMBLOB type, for binary data up to 2^24 bytes.""" 766 767 __visit_name__ = "MEDIUMBLOB" 768 769 770class LONGBLOB(sqltypes._Binary): 771 """MySQL LONGBLOB type, for binary data up to 2^32 bytes.""" 772 773 __visit_name__ = "LONGBLOB" 774