1# mysql/types.py 2# Copyright (C) 2005-2019 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 """ 446 447 __visit_name__ = "TIMESTAMP" 448 449 def __init__(self, timezone=False, fsp=None): 450 """Construct a MySQL TIMESTAMP type. 451 452 :param timezone: not used by the MySQL dialect. 453 :param fsp: fractional seconds precision value. 454 MySQL 5.6.4 supports storage of fractional seconds; 455 this parameter will be used when emitting DDL 456 for the TIMESTAMP type. 457 458 .. note:: 459 460 DBAPI driver support for fractional seconds may 461 be limited; current support includes 462 MySQL Connector/Python. 463 464 """ 465 super(TIMESTAMP, self).__init__(timezone=timezone) 466 self.fsp = fsp 467 468 469class DATETIME(sqltypes.DATETIME): 470 """MySQL DATETIME type. 471 472 """ 473 474 __visit_name__ = "DATETIME" 475 476 def __init__(self, timezone=False, fsp=None): 477 """Construct a MySQL DATETIME type. 478 479 :param timezone: not used by the MySQL dialect. 480 :param fsp: fractional seconds precision value. 481 MySQL 5.6.4 supports storage of fractional seconds; 482 this parameter will be used when emitting DDL 483 for the DATETIME type. 484 485 .. note:: 486 487 DBAPI driver support for fractional seconds may 488 be limited; current support includes 489 MySQL Connector/Python. 490 491 """ 492 super(DATETIME, self).__init__(timezone=timezone) 493 self.fsp = fsp 494 495 496class YEAR(sqltypes.TypeEngine): 497 """MySQL YEAR type, for single byte storage of years 1901-2155.""" 498 499 __visit_name__ = "YEAR" 500 501 def __init__(self, display_width=None): 502 self.display_width = display_width 503 504 505class TEXT(_StringType, sqltypes.TEXT): 506 """MySQL TEXT type, for text up to 2^16 characters.""" 507 508 __visit_name__ = "TEXT" 509 510 def __init__(self, length=None, **kw): 511 """Construct a TEXT. 512 513 :param length: Optional, if provided the server may optimize storage 514 by substituting the smallest TEXT type sufficient to store 515 ``length`` characters. 516 517 :param charset: Optional, a column-level character set for this string 518 value. Takes precedence to 'ascii' or 'unicode' short-hand. 519 520 :param collation: Optional, a column-level collation for this string 521 value. Takes precedence to 'binary' short-hand. 522 523 :param ascii: Defaults to False: short-hand for the ``latin1`` 524 character set, generates ASCII in schema. 525 526 :param unicode: Defaults to False: short-hand for the ``ucs2`` 527 character set, generates UNICODE in schema. 528 529 :param national: Optional. If true, use the server's configured 530 national character set. 531 532 :param binary: Defaults to False: short-hand, pick the binary 533 collation type that matches the column's character set. Generates 534 BINARY in schema. This does not affect the type of data stored, 535 only the collation of character data. 536 537 """ 538 super(TEXT, self).__init__(length=length, **kw) 539 540 541class TINYTEXT(_StringType): 542 """MySQL TINYTEXT type, for text up to 2^8 characters.""" 543 544 __visit_name__ = "TINYTEXT" 545 546 def __init__(self, **kwargs): 547 """Construct a TINYTEXT. 548 549 :param charset: Optional, a column-level character set for this string 550 value. Takes precedence to 'ascii' or 'unicode' short-hand. 551 552 :param collation: Optional, a column-level collation for this string 553 value. Takes precedence to 'binary' short-hand. 554 555 :param ascii: Defaults to False: short-hand for the ``latin1`` 556 character set, generates ASCII in schema. 557 558 :param unicode: Defaults to False: short-hand for the ``ucs2`` 559 character set, generates UNICODE in schema. 560 561 :param national: Optional. If true, use the server's configured 562 national character set. 563 564 :param binary: Defaults to False: short-hand, pick the binary 565 collation type that matches the column's character set. Generates 566 BINARY in schema. This does not affect the type of data stored, 567 only the collation of character data. 568 569 """ 570 super(TINYTEXT, self).__init__(**kwargs) 571 572 573class MEDIUMTEXT(_StringType): 574 """MySQL MEDIUMTEXT type, for text up to 2^24 characters.""" 575 576 __visit_name__ = "MEDIUMTEXT" 577 578 def __init__(self, **kwargs): 579 """Construct a MEDIUMTEXT. 580 581 :param charset: Optional, a column-level character set for this string 582 value. Takes precedence to 'ascii' or 'unicode' short-hand. 583 584 :param collation: Optional, a column-level collation for this string 585 value. Takes precedence to 'binary' short-hand. 586 587 :param ascii: Defaults to False: short-hand for the ``latin1`` 588 character set, generates ASCII in schema. 589 590 :param unicode: Defaults to False: short-hand for the ``ucs2`` 591 character set, generates UNICODE in schema. 592 593 :param national: Optional. If true, use the server's configured 594 national character set. 595 596 :param binary: Defaults to False: short-hand, pick the binary 597 collation type that matches the column's character set. Generates 598 BINARY in schema. This does not affect the type of data stored, 599 only the collation of character data. 600 601 """ 602 super(MEDIUMTEXT, self).__init__(**kwargs) 603 604 605class LONGTEXT(_StringType): 606 """MySQL LONGTEXT type, for text up to 2^32 characters.""" 607 608 __visit_name__ = "LONGTEXT" 609 610 def __init__(self, **kwargs): 611 """Construct a LONGTEXT. 612 613 :param charset: Optional, a column-level character set for this string 614 value. Takes precedence to 'ascii' or 'unicode' short-hand. 615 616 :param collation: Optional, a column-level collation for this string 617 value. Takes precedence to 'binary' short-hand. 618 619 :param ascii: Defaults to False: short-hand for the ``latin1`` 620 character set, generates ASCII in schema. 621 622 :param unicode: Defaults to False: short-hand for the ``ucs2`` 623 character set, generates UNICODE in schema. 624 625 :param national: Optional. If true, use the server's configured 626 national character set. 627 628 :param binary: Defaults to False: short-hand, pick the binary 629 collation type that matches the column's character set. Generates 630 BINARY in schema. This does not affect the type of data stored, 631 only the collation of character data. 632 633 """ 634 super(LONGTEXT, self).__init__(**kwargs) 635 636 637class VARCHAR(_StringType, sqltypes.VARCHAR): 638 """MySQL VARCHAR type, for variable-length character data.""" 639 640 __visit_name__ = "VARCHAR" 641 642 def __init__(self, length=None, **kwargs): 643 """Construct a VARCHAR. 644 645 :param charset: Optional, a column-level character set for this string 646 value. Takes precedence to 'ascii' or 'unicode' short-hand. 647 648 :param collation: Optional, a column-level collation for this string 649 value. Takes precedence to 'binary' short-hand. 650 651 :param ascii: Defaults to False: short-hand for the ``latin1`` 652 character set, generates ASCII in schema. 653 654 :param unicode: Defaults to False: short-hand for the ``ucs2`` 655 character set, generates UNICODE in schema. 656 657 :param national: Optional. If true, use the server's configured 658 national character set. 659 660 :param binary: Defaults to False: short-hand, pick the binary 661 collation type that matches the column's character set. Generates 662 BINARY in schema. This does not affect the type of data stored, 663 only the collation of character data. 664 665 """ 666 super(VARCHAR, self).__init__(length=length, **kwargs) 667 668 669class CHAR(_StringType, sqltypes.CHAR): 670 """MySQL CHAR type, for fixed-length character data.""" 671 672 __visit_name__ = "CHAR" 673 674 def __init__(self, length=None, **kwargs): 675 """Construct a CHAR. 676 677 :param length: Maximum data length, in characters. 678 679 :param binary: Optional, use the default binary collation for the 680 national character set. This does not affect the type of data 681 stored, use a BINARY type for binary data. 682 683 :param collation: Optional, request a particular collation. Must be 684 compatible with the national character set. 685 686 """ 687 super(CHAR, self).__init__(length=length, **kwargs) 688 689 @classmethod 690 def _adapt_string_for_cast(self, type_): 691 # copy the given string type into a CHAR 692 # for the purposes of rendering a CAST expression 693 type_ = sqltypes.to_instance(type_) 694 if isinstance(type_, sqltypes.CHAR): 695 return type_ 696 elif isinstance(type_, _StringType): 697 return CHAR( 698 length=type_.length, 699 charset=type_.charset, 700 collation=type_.collation, 701 ascii=type_.ascii, 702 binary=type_.binary, 703 unicode=type_.unicode, 704 national=False, # not supported in CAST 705 ) 706 else: 707 return CHAR(length=type_.length) 708 709 710class NVARCHAR(_StringType, sqltypes.NVARCHAR): 711 """MySQL NVARCHAR type. 712 713 For variable-length character data in the server's configured national 714 character set. 715 """ 716 717 __visit_name__ = "NVARCHAR" 718 719 def __init__(self, length=None, **kwargs): 720 """Construct an NVARCHAR. 721 722 :param length: Maximum data length, in characters. 723 724 :param binary: Optional, use the default binary collation for the 725 national character set. This does not affect the type of data 726 stored, use a BINARY type for binary data. 727 728 :param collation: Optional, request a particular collation. Must be 729 compatible with the national character set. 730 731 """ 732 kwargs["national"] = True 733 super(NVARCHAR, self).__init__(length=length, **kwargs) 734 735 736class NCHAR(_StringType, sqltypes.NCHAR): 737 """MySQL NCHAR type. 738 739 For fixed-length character data in the server's configured national 740 character set. 741 """ 742 743 __visit_name__ = "NCHAR" 744 745 def __init__(self, length=None, **kwargs): 746 """Construct an NCHAR. 747 748 :param length: Maximum data length, in characters. 749 750 :param binary: Optional, use the default binary collation for the 751 national character set. This does not affect the type of data 752 stored, use a BINARY type for binary data. 753 754 :param collation: Optional, request a particular collation. Must be 755 compatible with the national character set. 756 757 """ 758 kwargs["national"] = True 759 super(NCHAR, self).__init__(length=length, **kwargs) 760 761 762class TINYBLOB(sqltypes._Binary): 763 """MySQL TINYBLOB type, for binary data up to 2^8 bytes.""" 764 765 __visit_name__ = "TINYBLOB" 766 767 768class MEDIUMBLOB(sqltypes._Binary): 769 """MySQL MEDIUMBLOB type, for binary data up to 2^24 bytes.""" 770 771 __visit_name__ = "MEDIUMBLOB" 772 773 774class LONGBLOB(sqltypes._Binary): 775 """MySQL LONGBLOB type, for binary data up to 2^32 bytes.""" 776 777 __visit_name__ = "LONGBLOB" 778