# mysql.py# Copyright (C) 2005, 2006, 2007, 2008 Michael Bayer mike_mp@zzzcomputing.com## This module is part of SQLAlchemy and is released under# the MIT License: http://www.opensource.org/licenses/mit-license.php"""Support for the MySQL database.SQLAlchemy supports 6 major MySQL versions: 3.23, 4.0, 4.1, 5.0, 5.1 and 6.0,with capablities increasing with more modern servers.Versions 4.1 and higher support the basic SQL functionality that SQLAlchemyuses in the ORM and SQL expressions.  These versions pass the applicabletests in the suite 100%.  No heroic measures are taken to work around majormissing SQL features- if your server version does not support sub-selects, forexample, they won't work in SQLAlchemy either.Currently, the only DB-API driver supported is `MySQL-Python` (also referred toas `MySQLdb`).  Either 1.2.1 or 1.2.2 are recommended.  The alpha, beta andgamma releases of 1.2.1 and 1.2.2 should be avoided.  Support for Jython andIronPython is planned.=====================================  ===============Feature                                Minimum Version=====================================  ===============sqlalchemy.orm                         4.1.1Table Reflection                       3.23.xDDL Generation                         4.1.1utf8/Full Unicode Connections          4.1.1Transactions                           3.23.15Two-Phase Transactions                 5.0.3Nested Transactions                    5.0.3=====================================  ===============See the official MySQL documentation for detailed information about featuressupported in any given server release.Many MySQL server installations default to a ``latin1`` encoding for clientconnections.  All data sent through the connection will be convertedinto ``latin1``, even if you have ``utf8`` or another character set on yourtables and columns.  With versions 4.1 and higher, you can change theconnection character set either through server configuration or by passingthe  ``charset`` parameter to  ``create_engine``.  The ``charset`` option ispassed through to MySQL-Python and has the side-effect of also enabling``use_unicode`` in the driver by default.  For regular encoded strings, alsopass ``use_unicode=0`` in the connection arguments.Most MySQL server installations have a default table type of `MyISAM`, anon-transactional table type.  During a transaction, non-transactionalstorage engines do not participate and continue to store table changes inautocommit mode.  For fully atomic transactions, all participating tablesmust use a transactional engine such as `InnoDB`, `Falcon`, `SolidDB`,`PBXT`, etc.  Storage engines can be elected when creating tables inSQLAlchemy by supplying a ``mysql_engine='whatever'`` to the ``Table``constructor.  Any MySQL table creation option can be specified in this syntax.Not all MySQL storage engines support foreign keys.  For `MyISAM` and similarengines, the information loaded by table reflection will not include foreignkeys.  For these tables, you may supply ``ForeignKeyConstraints`` at reflectiontime::  Table('mytable', metadata, autoload=True,        ForeignKeyConstraint(['other_id'], ['othertable.other_id']))When creating tables, SQLAlchemy will automatically set AUTO_INCREMENT on aninteger primary key column::  >>> t = Table('mytable', metadata,  ...   Column('mytable_id', Integer, primary_key=True))  >>> t.create()  CREATE TABLE mytable (          id INTEGER NOT NULL AUTO_INCREMENT,          PRIMARY KEY (id)  )You can disable this behavior by supplying ``autoincrement=False`` in addition.This can also be used to enable auto-increment on a secondary column in amulti-column key for some storage engines::  Table('mytable', metadata,        Column('gid', Integer, primary_key=True, autoincrement=False),        Column('id', Integer, primary_key=True))MySQL SQL modes are supported.  Modes that enable ``ANSI_QUOTES`` (such as``ANSI``) require an engine option to modify SQLAlchemy's quoting style.When using an ANSI-quoting mode, supply ``use_ansiquotes=True`` whencreating your ``Engine``::  create_engine('mysql://localhost/test', use_ansiquotes=True)This is an engine-wide option and is not toggleable on a per-connection basis.SQLAlchemy does not presume to ``SET sql_mode`` for you with this option.For the best performance, set the quoting style server-wide in ``my.cnf`` orby supplying ``--sql-mode`` to ``mysqld``.  You can also use a ``Pool`` hookto issue a ``SET SESSION sql_mode='...'`` on connect to configure eachconnection.If you do not specify 'use_ansiquotes', the regular MySQL quoting style isused by default.  Table reflection operations will query the server If you do issue a 'SET sql_mode' through SQLAlchemy, the dialect must beupdated if the quoting style is changed.  Again, this change will affect allconnections::  connection.execute('SET sql_mode="ansi"')  connection.dialect.use_ansiquotes = TrueFor normal SQLAlchemy usage, loading this module is unnescesary.  It will beloaded on-demand when a MySQL connection is needed.  The generic column typeslike ``String`` and ``Integer`` will automatically be adapted to the optimalmatching MySQL column type.But if you would like to use one of the MySQL-specific or enhanced columntypes when creating tables with your ``Table`` definitions, then you willneed to import them from this module::  from sqlalchemy.databases import mysql  Table('mytable', metadata,        Column('id', Integer, primary_key=True),        Column('ittybittyblob', mysql.MSTinyBlob),        Column('biggy', mysql.MSBigInteger(unsigned=True)))All standard MySQL column types are supported.  The OpenGIS types areavailable for use via table reflection but have no special support ormapping to Python classes.  If you're using these types and have opinionsabout how OpenGIS can be smartly integrated into SQLAlchemy please jointhe mailing list!Many of the MySQL SQL extensions are handled through SQLAlchemy's genericfunction and operator support::  table.select(table.c.password==func.md5('plaintext'))  table.select(table.c.username.op('regexp')('^[a-d]'))And of course any valid statement can be executed as a string rather thanthrough the SQL expression language.Some limited support for MySQL extensions to SQL expressions is currentlyavailable.  * SELECT pragma::      select(..., prefixes=['HIGH_PRIORITY', 'SQL_SMALL_RESULT'])  * UPDATE with LIMIT::      update(..., mysql_limit=10)If you have problems that seem server related, first check that you areusing the most recent stable MySQL-Python package available.  The DatabaseNotes page on the wiki at http://www.sqlalchemy.org is a good resource fortimely information affecting MySQL in SQLAlchemy."""import datetime, inspect, re, sysfrom array import array as _arrayfrom sqlalchemy import exceptions, logging, schema, sql, utilfrom sqlalchemy.sql import operators as sql_operatorsfrom sqlalchemy.sql import compilerfrom sqlalchemy.engine import base as engine_base, defaultfrom sqlalchemy import types as sqltypes__all__ = (    'MSBigInteger', 'MSBinary', 'MSBit', 'MSBlob', 'MSBoolean',    'MSChar', 'MSDate', 'MSDateTime', 'MSDecimal', 'MSDouble',    'MSEnum', 'MSFloat', 'MSInteger', 'MSLongBlob', 'MSLongText',    'MSMediumBlob', 'MSMediumText', 'MSNChar', 'MSNVarChar',    'MSNumeric', 'MSSet', 'MSSmallInteger', 'MSString', 'MSText',    'MSTime', 'MSTimeStamp', 'MSTinyBlob', 'MSTinyInteger',    'MSTinyText', 'MSVarBinary', 'MSYear' )RESERVED_WORDS = util.Set(    ['accessible', 'add', 'all', 'alter', 'analyze','and', 'as', 'asc',     'asensitive', 'before', 'between', 'bigint', 'binary', 'blob', 'both',     'by', 'call', 'cascade', 'case', 'change', 'char', 'character', 'check',     'collate', 'column', 'condition', 'constraint', 'continue', 'convert',     'create', 'cross', 'current_date', 'current_time', 'current_timestamp',     'current_user', 'cursor', 'database', 'databases', 'day_hour',     'day_microsecond', 'day_minute', 'day_second', 'dec', 'decimal',     'declare', 'default', 'delayed', 'delete', 'desc', 'describe',     'deterministic', 'distinct', 'distinctrow', 'div', 'double', 'drop',     'dual', 'each', 'else', 'elseif', 'enclosed', 'escaped', 'exists',     'exit', 'explain', 'false', 'fetch', 'float', 'float4', 'float8',     'for', 'force', 'foreign', 'from', 'fulltext', 'grant', 'group', 'having',     'high_priority', 'hour_microsecond', 'hour_minute', 'hour_second', 'if',     'ignore', 'in', 'index', 'infile', 'inner', 'inout', 'insensitive',     'insert', 'int', 'int1', 'int2', 'int3', 'int4', 'int8', 'integer',     'interval', 'into', 'is', 'iterate', 'join', 'key', 'keys', 'kill',     'leading', 'leave', 'left', 'like', 'limit', 'linear', 'lines', 'load',     'localtime', 'localtimestamp', 'lock', 'long', 'longblob', 'longtext',     'loop', 'low_priority', 'master_ssl_verify_server_cert', 'match',     'mediumblob', 'mediumint', 'mediumtext', 'middleint',     'minute_microsecond', 'minute_second', 'mod', 'modifies', 'natural',     'not', 'no_write_to_binlog', 'null', 'numeric', 'on', 'optimize',     'option', 'optionally', 'or', 'order', 'out', 'outer', 'outfile',     'precision', 'primary', 'procedure', 'purge', 'range', 'read', 'reads',     'read_only', 'read_write', 'real', 'references', 'regexp', 'release',     'rename', 'repeat', 'replace', 'require', 'restrict', 'return',     'revoke', 'right', 'rlike', 'schema', 'schemas', 'second_microsecond',     'select', 'sensitive', 'separator', 'set', 'show', 'smallint', 'spatial',     'specific', 'sql', 'sqlexception', 'sqlstate', 'sqlwarning',     'sql_big_result', 'sql_calc_found_rows', 'sql_small_result', 'ssl',     'starting', 'straight_join', 'table', 'terminated', 'then', 'tinyblob',     'tinyint', 'tinytext', 'to', 'trailing', 'trigger', 'true', 'undo',     'union', 'unique', 'unlock', 'unsigned', 'update', 'usage', 'use',     'using', 'utc_date', 'utc_time', 'utc_timestamp', 'values', 'varbinary',     'varchar', 'varcharacter', 'varying', 'when', 'where', 'while', 'with',     'write', 'x509', 'xor', 'year_month', 'zerofill', # 5.0     'fields', # 4.1     'accessible', 'linear', 'master_ssl_verify_server_cert', 'range',     'read_only', 'read_write', # 5.1     ])AUTOCOMMIT_RE = re.compile(    r'\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER|LOAD +DATA|REPLACE)',    re.I | re.UNICODE)SELECT_RE = re.compile(    r'\s*(?:SELECT|SHOW|DESCRIBE|XA RECOVER)',    re.I | re.UNICODE)class _NumericType(object):    """Base for MySQL numeric types."""    def __init__(self, unsigned=False, zerofill=False, **kw):        self.unsigned = unsigned        self.zerofill = zerofill    def _extend(self, spec):        "Extend a numeric-type declaration with MySQL specific extensions."        if self.unsigned:            spec += ' UNSIGNED'        if self.zerofill:            spec += ' ZEROFILL'        return specclass _StringType(object):    """Base for MySQL string types."""    def __init__(self, charset=None, collation=None,                 ascii=False, unicode=False, binary=False,                 national=False, **kwargs):        self.charset = charset        # allow collate= or collation=        self.collation = kwargs.get('collate', collation)        self.ascii = ascii        self.unicode = unicode        self.binary = binary        self.national = national    def _extend(self, spec):        """Extend a string-type declaration with standard SQL CHARACTER SET /        COLLATE annotations and MySQL specific extensions.        """        if self.charset:            charset = 'CHARACTER SET %s' % self.charset        elif self.ascii:            charset = 'ASCII'        elif self.unicode:            charset = 'UNICODE'        else:            charset = None        if self.collation:            collation = 'COLLATE %s' % self.collation        elif self.binary:            collation = 'BINARY'        else:            collation = None        if self.national:            # NATIONAL (aka NCHAR/NVARCHAR) trumps charsets.            return ' '.join([c for c in ('NATIONAL', spec, collation)                             if c is not None])        return ' '.join([c for c in (spec, charset, collation)                         if c is not None])    def __repr__(self):        attributes = inspect.getargspec(self.__init__)[0][1:]        attributes.extend(inspect.getargspec(_StringType.__init__)[0][1:])        params = {}        for attr in attributes:            val = getattr(self, attr)            if val is not None and val is not False:                params[attr] = val        return "%s(%s)" % (self.__class__.__name__,                           ', '.join(['%s=%r' % (k, params[k]) for k in params]))class MSNumeric(sqltypes.Numeric, _NumericType):    """MySQL NUMERIC type."""    def __init__(self, precision=10, length=2, asdecimal=True, **kw):        """Construct a NUMERIC.        precision          Total digits in this number.  If length and precision are both          None, values are stored to limits allowed by the server.        length          The number of digits after the decimal point.        unsigned          Optional.        zerofill          Optional. If true, values will be stored as strings left-padded with          zeros. Note that this does not effect the values returned by the          underlying database API, which continue to be numeric.        """        _NumericType.__init__(self, **kw)        sqltypes.Numeric.__init__(self, precision, length, asdecimal=asdecimal)    def get_col_spec(self):        if self.precision is None:            return self._extend("NUMERIC")        else:            return self._extend("NUMERIC(%(precision)s, %(length)s)" % {'precision': self.precision, 'length' : self.length})    def bind_processor(self, dialect):        return None    def result_processor(self, dialect):        if not self.asdecimal:            def process(value):                if isinstance(value, util.decimal_type):                    return float(value)                else:                    return value            return process        else:            return Noneclass MSDecimal(MSNumeric):    """MySQL DECIMAL type."""    def __init__(self, precision=10, length=2, asdecimal=True, **kw):        """Construct a DECIMAL.        precision          Total digits in this number.  If length and precision are both None,          values are stored to limits allowed by the server.        length          The number of digits after the decimal point.        unsigned          Optional.        zerofill          Optional. If true, values will be stored as strings left-padded with          zeros. Note that this does not effect the values returned by the          underlying database API, which continue to be numeric.        """        super(MSDecimal, self).__init__(precision, length, asdecimal=asdecimal, **kw)    def get_col_spec(self):        if self.precision is None:            return self._extend("DECIMAL")        elif self.length is None:            return self._extend("DECIMAL(%(precision)s)" % {'precision': self.precision})        else:            return self._extend("DECIMAL(%(precision)s, %(length)s)" % {'precision': self.precision, 'length' : self.length})class MSDouble(sqltypes.Float, _NumericType):    """MySQL DOUBLE type."""    def __init__(self, precision=None, length=None, asdecimal=True, **kw):        """Construct a DOUBLE.