MySQL

Support for the MySQL database.

DBAPI Support

The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.

Supported Versions and Features

SQLAlchemy supports MySQL starting with version 4.1 through modern releases.However, no heroic measures are taken to work around major missingSQL features - if your server version does not support sub-selects, forexample, they won’t work in SQLAlchemy either.

See the official MySQL documentation for detailed information about featuressupported in any given server release.

Connection Timeouts and Disconnects

MySQL features an automatic connection close behavior, for connections thathave been idle for a fixed period of time, defaulting to eight hours.To circumvent having this issue, usethe create_engine.pool_recycle option which ensures thata connection will be discarded and replaced with a new one if it has beenpresent in the pool for a fixed number of seconds:

  1. engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)

For more comprehensive disconnect detection of pooled connections, includingaccommodation of server restarts and network issues, a pre-ping approach maybe employed. See Dealing with Disconnects for current approaches.

See also

Dealing with Disconnects - Background on several techniques for dealingwith timed out connections as well as database restarts.

CREATE TABLE arguments including Storage Engines

MySQL’s CREATE TABLE syntax includes a wide array of special options,including ENGINE, CHARSET, MAX_ROWS, ROW_FORMAT,INSERT_METHOD, and many more.To accommodate the rendering of these arguments, specify the formmysql_argument_name="value". For example, to specify a table withENGINE of InnoDB, CHARSET of utf8mb4, and KEY_BLOCK_SIZEof 1024:

  1. Table('mytable', metadata,
  2.       Column('data', String(32)),
  3.       mysql_engine='InnoDB',
  4.       mysql_charset='utf8mb4',
  5.       mysql_key_block_size="1024"
  6.      )

The MySQL dialect will normally transfer any keyword specified asmysql_keyword_name to be rendered as KEYWORD_NAME in theCREATE TABLE statement. A handful of these names will render with a spaceinstead of an underscore; to support this, the MySQL dialect has awareness ofthese particular names, which include DATA DIRECTORY(e.g. mysql_data_directory), CHARACTER SET (e.g.mysql_character_set) and INDEX DIRECTORY (e.g.mysql_index_directory).

The most common argument is mysql_engine, which refers to the storageengine for the table. Historically, MySQL server installations would defaultto MyISAM for this value, although newer versions may be defaultingto InnoDB. The InnoDB engine is typically preferred for its supportof transactions and foreign keys.

A Table that is created in a MySQL database with a storage engineof MyISAM will be essentially non-transactional, meaning anyINSERT/UPDATE/DELETE statement referring to this table will be invoked asautocommit. It also will have no support for foreign key constraints; whilethe CREATE TABLE statement accepts foreign key options, when using theMyISAM storage engine these arguments are discarded. Reflecting such atable will also produce no foreign key constraint information.

For fully atomic transactions as well as support for foreign keyconstraints, all participating CREATE TABLE statements must specify atransactional engine, which in the vast majority of cases is InnoDB.

See also

The InnoDB Storage Engine -on the MySQL website.

Case Sensitivity and Table Reflection

MySQL has inconsistent support for case-sensitive identifiernames, basing support on specific details of the underlyingoperating system. However, it has been observed that no matterwhat case sensitivity behavior is present, the names of tables inforeign key declarations are always received from the databaseas all-lower case, making it impossible to accurately reflect aschema where inter-related tables use mixed-case identifier names.

Therefore it is strongly advised that table names be declared asall lower case both within SQLAlchemy as well as on the MySQLdatabase itself, especially if database reflection features areto be used.

Transaction Isolation Level

All MySQL dialects support setting of transaction isolation level both via adialect-specific parameter create_engine.isolation_level acceptedby create_engine(), as well as theConnection.execution_options.isolation_level argument as passed toConnection.execution_options(). This feature works by issuing thecommand SET SESSION TRANSACTION ISOLATION LEVEL <level> for each newconnection. For the special AUTOCOMMIT isolation level, DBAPI-specifictechniques are used.

To set isolation level using create_engine():

  1. engine = create_engine(
  2.                 "mysql://scott:tiger@localhost/test",
  3.                 isolation_level="READ UNCOMMITTED"
  4.             )

To set using per-connection execution options:

  1. connection = engine.connect()
  2. connection = connection.execution_options(
  3.     isolation_level="READ COMMITTED"
  4. )

Valid values for isolation_level include:

  • READ COMMITTED

  • READ UNCOMMITTED

  • REPEATABLE READ

  • SERIALIZABLE

  • AUTOCOMMIT

The special AUTOCOMMIT value makes use of the various “autocommit”attributes provided by specific DBAPIs, and is currently supported byMySQLdb, MySQL-Client, MySQL-Connector Python, and PyMySQL. Using it,the MySQL connection will return true for the value ofSELECT @@autocommit;.

New in version 1.1: - added support for the AUTOCOMMIT isolation level.

AUTO_INCREMENT Behavior

When creating tables, SQLAlchemy will automatically set AUTO_INCREMENT onthe first Integer primary key column which is not marked as aforeign key:

  1. >>> t = Table('mytable', metadata,
  2. ...   Column('mytable_id', Integer, primary_key=True)
  3. ... )
  4. >>> t.create()
  5. CREATE TABLE mytable (
  6.         id INTEGER NOT NULL AUTO_INCREMENT,
  7.         PRIMARY KEY (id)
  8. )

You can disable this behavior by passing False to theautoincrement argument of Column. This flagcan also be used to enable auto-increment on a secondary column in amulti-column key for some storage engines:

  1. Table('mytable', metadata,
  2.       Column('gid', Integer, primary_key=True, autoincrement=False),
  3.       Column('id', Integer, primary_key=True)
  4.      )

Server Side Cursors

Server-side cursor support is available for the MySQLdb and PyMySQL dialects.From a MySQL point of view this means that the MySQLdb.cursors.SSCursor orpymysql.cursors.SSCursor class is used when building up the cursor whichwill receive results. The most typical way of invoking this feature is via theConnection.execution_options.stream_results connection executionoption. Server side cursors can also be enabled for all SELECT statementsunconditionally by passing server_side_cursors=True tocreate_engine().

New in version 1.1.4: - added server-side cursor support.

Unicode

Charset Selection

Most MySQL DBAPIs offer the option to set the client character set fora connection. This is typically delivered using the charset parameterin the URL, such as:

  1. e = create_engine(
  2.     "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4")

This charset is the client character set for the connection. SomeMySQL DBAPIs will default this to a value such as latin1, and somewill make use of the default-character-set setting in the my.cnffile as well. Documentation for the DBAPI in use should be consultedfor specific behavior.

The encoding used for Unicode has traditionally been 'utf8'. However,for MySQL versions 5.5.3 on forward, a new MySQL-specific encoding'utf8mb4' has been introduced, and as of MySQL 8.0 a warning is emittedby the server if plain utf8 is specified within any server-sidedirectives, replaced with utf8mb3. The rationale for this new encodingis due to the fact that MySQL’s legacy utf-8 encoding only supportscodepoints up to three bytes instead of four. Therefore,when communicating with a MySQL databasethat includes codepoints more than three bytes in size,this new charset is preferred, if supported by both the database as wellas the client DBAPI, as in:

  1. e = create_engine(
  2.     "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4")

All modern DBAPIs should support the utf8mb4 charset.

In order to use utf8mb4 encoding for a schema that was created with legacyutf8, changes to the MySQL schema and/or server configuration may berequired.

See also

The utf8mb4 Character Set - in the MySQL documentation

Dealing with Binary Data Warnings and Unicode

MySQL versions 5.6, 5.7 and later (not MariaDB at the time of this writing) nowemit a warning when attempting to pass binary data to the database, while acharacter set encoding is also in place, when the binary data itself is notvalid for that encoding:

  1. default.py:509: Warning: (1300, "Invalid utf8mb4 character string:
  2. 'F9876A'")
  3.   cursor.execute(statement, parameters)

This warning is due to the fact that the MySQL client library is attempting tointerpret the binary string as a unicode object even if a datatype suchas LargeBinary is in use. To resolve this, the SQL statement requiresa binary “character set introducer” be present before any non-NULL valuethat renders like this:

  1. INSERT INTO table (data) VALUES (_binary %s)

These character set introducers are provided by the DBAPI driver, assuming theuse of mysqlclient or PyMySQL (both of which are recommended). Add the querystring parameter binary_prefix=true to the URL to repair this warning:

  1. # mysqlclient
  2. engine = create_engine(
  3.     "mysql+mysqldb://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true")
  4.  
  5. # PyMySQL
  6. engine = create_engine(
  7.     "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true")

The binary_prefix flag may or may not be supported by other MySQL drivers.

SQLAlchemy itself cannot render this _binary prefix reliably, as it doesnot work with the NULL value, which is valid to be sent as a bound parameter.As the MySQL driver renders parameters directly into the SQL string, it’s themost efficient place for this additional keyword to be passed.

See also

Character set introducers - on the MySQL website

ANSI Quoting Style

MySQL features two varieties of identifier “quoting style”, one usingbackticks and the other using quotes, e.g. some_identifier vs."some_identifier". All MySQL dialects detect which versionis in use by checking the value of sql_mode when a connection is firstestablished with a particular Engine. This quoting style comesinto play when rendering table and column names as well as when reflectingexisting database structures. The detection is entirely automatic andno special configuration is needed to use either quoting style.

MySQL SQL Extensions

Many of the MySQL SQL extensions are handled through SQLAlchemy’s genericfunction and operator support:

  1. table.select(table.c.password==func.md5('plaintext'))
  2. table.select(table.c.username.op('regexp')('^[a-d]'))

And of course any valid MySQL statement can be executed as a string as well.

Some limited direct support for MySQL extensions to SQL is currentlyavailable.

  1. select(...).prefix_with(['HIGH_PRIORITY', 'SQL_SMALL_RESULT'])
  • UPDATE with LIMIT:
  1. update(..., mysql_limit=10)
  1. select(...).prefix_with("/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */")
  1. select(...).with_hint(some_table, "USE INDEX xyz")

INSERT…ON DUPLICATE KEY UPDATE (Upsert)

MySQL allows “upserts” (update or insert)of rows into a table via the ON DUPLICATE KEY UPDATE clause of theINSERT statement. A candidate row will only be inserted if that row doesnot match an existing primary or unique key in the table; otherwise, an UPDATEwill be performed. The statement allows for separate specification of thevalues to INSERT versus the values for UPDATE.

SQLAlchemy provides ON DUPLICATE KEY UPDATE support via the MySQL-specificmysql.dml.insert() function, which providesthe generative method on_duplicate_key_update():

  1. from sqlalchemy.dialects.mysql import insert
  2.  
  3. insert_stmt = insert(my_table).values(
  4.     id='some_existing_id',
  5.     data='inserted value')
  6.  
  7. on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
  8.     data=insert_stmt.inserted.data,
  9.     status='U'
  10. )
  11.  
  12. conn.execute(on_duplicate_key_stmt)

Unlike PostgreSQL’s “ON CONFLICT” phrase, the “ON DUPLICATE KEY UPDATE”phrase will always match on any primary key or unique key, and will alwaysperform an UPDATE if there’s a match; there are no options for it to raisean error or to skip performing an UPDATE.

ON DUPLICATE KEY UPDATE is used to perform an update of the alreadyexisting row, using any combination of new values as well as valuesfrom the proposed insertion. These values are normally specified usingkeyword arguments passed to theon_duplicate_key_update()given column key values (usually the name of the column, unless itspecifies Column.key) as keys and literal or SQL expressionsas values:

  1. on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
  2.     data="some data",
  3.     updated_at=func.current_timestamp(),
  4. )

In a manner similar to that of UpdateBase.values(), other parameterforms are accepted, including a single dictionary:

  1. on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
  2.     {"data": "some data", "updated_at": func.current_timestamp()},
  3. )

as well as a list of 2-tuples, which will automatically providea parameter-ordered UPDATE statement in a manner similar to that describedat Parameter-Ordered Updates. Unlike the Update object,no special flag is needed to specify the intent since the argument form isthis context is unambiguous:

  1. on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
  2.     [
  3.         ("data", "some data"),
  4.         ("updated_at", func.current_timestamp()),
  5.     ],
  6. )

Changed in version 1.3: support for parameter-ordered UPDATE clause withinMySQL ON DUPLICATE KEY UPDATE

Warning

The Insert.on_duplicate_key_update() method does not take intoaccount Python-side default UPDATE values or generation functions, e.g.e.g. those specified using Column.onupdate.These values will not be exercised for an ON DUPLICATE KEY style of UPDATE,unless they are manually specified explicitly in the parameters.

In order to refer to the proposed insertion row, the special aliasinserted is available as an attribute onthe mysql.dml.Insert object; this object is aColumnCollection which contains all columns of the targettable:

  1. from sqlalchemy.dialects.mysql import insert
  2.  
  3. stmt = insert(my_table).values(
  4.     id='some_id',
  5.     data='inserted value',
  6.     author='jlh')
  7. do_update_stmt = stmt.on_duplicate_key_update(
  8.     data="updated value",
  9.     author=stmt.inserted.author
  10. )
  11. conn.execute(do_update_stmt)

When rendered, the “inserted” namespace will produce the expressionVALUES(<columnname>).

New in version 1.2: Added support for MySQL ON DUPLICATE KEY UPDATE clause

rowcount Support

SQLAlchemy standardizes the DBAPI cursor.rowcount attribute to be theusual definition of “number of rows matched by an UPDATE or DELETE” statement.This is in contradiction to the default setting on most MySQL DBAPI drivers,which is “number of rows actually modified/deleted”. For this reason, theSQLAlchemy MySQL dialects always add the constants.CLIENT.FOUND_ROWSflag, or whatever is equivalent for the target dialect, upon connection.This setting is currently hardcoded.

See also

ResultProxy.rowcount

CAST Support

MySQL documents the CAST operator as available in version 4.0.2. When usingthe SQLAlchemy cast() function, SQLAlchemywill not render the CAST token on MySQL before this version, based on serverversion detection, instead rendering the internal expression directly.

CAST may still not be desirable on an early MySQL version post-4.0.2, as itdidn’t add all datatype support until 4.1.1. If your application falls intothis narrow area, the behavior of CAST can be controlled using theCustom SQL Constructs and Compilation Extension system, as per the recipe below:

  1. from sqlalchemy.sql.expression import Cast
  2. from sqlalchemy.ext.compiler import compiles
  3.  
  4. @compiles(Cast, 'mysql')
  5. def _check_mysql_version(element, compiler, **kw):
  6.     if compiler.dialect.server_version_info < (4, 1, 0):
  7.         return compiler.process(element.clause, **kw)
  8.     else:
  9.         return compiler.visit_cast(element, **kw)

The above function, which only needs to be declared oncewithin an application, overrides the compilation of thecast() construct to check for version 4.1.0 beforefully rendering CAST; else the internal element of theconstruct is rendered directly.

MySQL Specific Index Options

MySQL-specific extensions to the Index construct are available.

Index Length

MySQL provides an option to create index entries with a certain length, where“length” refers to the number of characters or bytes in each value which willbecome part of the index. SQLAlchemy provides this feature via themysql_length parameter:

  1. Index('my_index', my_table.c.data, mysql_length=10)
  2.  
  3. Index('a_b_idx', my_table.c.a, my_table.c.b, mysql_length={'a': 4,
  4.                                                            'b': 9})

Prefix lengths are given in characters for nonbinary string types and in bytesfor binary string types. The value passed to the keyword argument must beeither an integer (and, thus, specify the same prefix length value for allcolumns of the index) or a dict in which keys are column names and values areprefix length values for corresponding columns. MySQL only allows a length fora column of an index if it is for a CHAR, VARCHAR, TEXT, BINARY, VARBINARY andBLOB.

Index Prefixes

MySQL storage engines permit you to specify an index prefix when creatingan index. SQLAlchemy provides this feature via themysql_prefix parameter on Index:

  1. Index('my_index', my_table.c.data, mysql_prefix='FULLTEXT')

The value passed to the keyword argument will be simply passed through to theunderlying CREATE INDEX, so it must be a valid index prefix for your MySQLstorage engine.

New in version 1.1.5.

See also

CREATE INDEX - MySQL documentation

Index Types

Some MySQL storage engines permit you to specify an index type when creatingan index or primary key constraint. SQLAlchemy provides this feature via themysql_using parameter on Index:

  1. Index('my_index', my_table.c.data, mysql_using='hash')

As well as the mysql_using parameter on PrimaryKeyConstraint:

  1. PrimaryKeyConstraint("data", mysql_using='hash')

The value passed to the keyword argument will be simply passed through to theunderlying CREATE INDEX or PRIMARY KEY clause, so it must be a valid indextype for your MySQL storage engine.

More information can be found at:

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Index Parsers

CREATE FULLTEXT INDEX in MySQL also supports a “WITH PARSER” option. Thisis available using the keyword argument mysql_with_parser:

  1. Index(
  2.     'my_index', my_table.c.data,
  3.     mysql_prefix='FULLTEXT', mysql_with_parser="ngram")

New in version 1.3.

MySQL Foreign Keys

MySQL’s behavior regarding foreign keys has some important caveats.

Foreign Key Arguments to Avoid

MySQL does not support the foreign key arguments “DEFERRABLE”, “INITIALLY”,or “MATCH”. Using the deferrable or initially keyword argument withForeignKeyConstraint or ForeignKey will have the effect ofthese keywords being rendered in a DDL expression, which will then raise anerror on MySQL. In order to use these keywords on a foreign key while havingthem ignored on a MySQL backend, use a custom compile rule:

  1. from sqlalchemy.ext.compiler import compiles
  2. from sqlalchemy.schema import ForeignKeyConstraint
  3.  
  4. @compiles(ForeignKeyConstraint, "mysql")
  5. def process(element, compiler, **kw):
  6.     element.deferrable = element.initially = None
  7.     return compiler.visit_foreign_key_constraint(element, **kw)

Changed in version 0.9.0: - the MySQL backend no longer silently ignoresthe deferrable or initially keyword arguments ofForeignKeyConstraint and ForeignKey.

The “MATCH” keyword is in fact more insidious, and is explicitly disallowedby SQLAlchemy in conjunction with the MySQL backend. This argument issilently ignored by MySQL, but in addition has the effect of ON UPDATE and ONDELETE options also being ignored by the backend. Therefore MATCH shouldnever be used with the MySQL backend; as is the case with DEFERRABLE andINITIALLY, custom compilation rules can be used to correct a MySQLForeignKeyConstraint at DDL definition time.

New in version 0.9.0: - the MySQL backend will raise aCompileError when the match keyword is used withForeignKeyConstraint or ForeignKey.

Reflection of Foreign Key Constraints

Not all MySQL storage engines support foreign keys. When using thevery common MyISAM MySQL storage engine, the information loaded by tablereflection will not include foreign keys. For these tables, you may supply aForeignKeyConstraint at reflection time:

  1. Table('mytable', metadata,
  2.       ForeignKeyConstraint(['other_id'], ['othertable.other_id']),
  3.       autoload=True
  4.      )

See also

CREATE TABLE arguments including Storage Engines

MySQL Unique Constraints and Reflection

SQLAlchemy supports both the Index construct with theflag unique=True, indicating a UNIQUE index, as well as theUniqueConstraint construct, representing a UNIQUE constraint.Both objects/syntaxes are supported by MySQL when emitting DDL to createthese constraints. However, MySQL does not have a unique constraintconstruct that is separate from a unique index; that is, the “UNIQUE”constraint on MySQL is equivalent to creating a “UNIQUE INDEX”.

When reflecting these constructs, the Inspector.get_indexes()and the Inspector.get_unique_constraints() methods will bothreturn an entry for a UNIQUE index in MySQL. However, when performingfull table reflection using Table(…, autoload=True),the UniqueConstraint construct isnot part of the fully reflected Table construct under anycircumstances; this construct is always represented by a Indexwith the unique=True setting present in the Table.indexescollection.

TIMESTAMP Columns and NULL

MySQL historically enforces that a column which specifies theTIMESTAMP datatype implicitly includes a default value ofCURRENT_TIMESTAMP, even though this is not stated, and additionallysets the column as NOT NULL, the opposite behavior vs. that of allother datatypes:

  1. mysql> CREATE TABLE ts_test (
  2.     -> a INTEGER,
  3.     -> b INTEGER NOT NULL,
  4.     -> c TIMESTAMP,
  5.     -> d TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  6.     -> e TIMESTAMP NULL);
  7. Query OK, 0 rows affected (0.03 sec)
  8.  
  9. mysql> SHOW CREATE TABLE ts_test;
  10. +---------+-----------------------------------------------------
  11. | Table   | Create Table
  12. +---------+-----------------------------------------------------
  13. | ts_test | CREATE TABLE `ts_test` (
  14.   `a` int(11) DEFAULT NULL,
  15.   `b` int(11) NOT NULL,
  16.   `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  17.   `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  18.   `e` timestamp NULL DEFAULT NULL
  19. ) ENGINE=MyISAM DEFAULT CHARSET=latin1

Above, we see that an INTEGER column defaults to NULL, unless it is specifiedwith NOT NULL. But when the column is of type TIMESTAMP, an implicitdefault of CURRENT_TIMESTAMP is generated which also coerces the columnto be a NOT NULL, even though we did not specify it as such.

This behavior of MySQL can be changed on the MySQL side using theexplicit_defaults_for_timestamp configuration flag introduced inMySQL 5.6. With this server setting enabled, TIMESTAMP columns behave likeany other datatype on the MySQL side with regards to defaults and nullability.

However, to accommodate the vast majority of MySQL databases that do notspecify this new flag, SQLAlchemy emits the “NULL” specifier explicitly withany TIMESTAMP column that does not specify nullable=False. In order toaccommodate newer databases that specify explicit_defaults_for_timestamp,SQLAlchemy also emits NOT NULL for TIMESTAMP columns that do specifynullable=False. The following example illustrates:

  1. from sqlalchemy import MetaData, Integer, Table, Column, text
  2. from sqlalchemy.dialects.mysql import TIMESTAMP
  3.  
  4. m = MetaData()
  5. t = Table('ts_test', m,
  6.         Column('a', Integer),
  7.         Column('b', Integer, nullable=False),
  8.         Column('c', TIMESTAMP),
  9.         Column('d', TIMESTAMP, nullable=False)
  10.     )
  11.  
  12.  
  13. from sqlalchemy import create_engine
  14. e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
  15. m.create_all(e)

output:

  1. CREATE TABLE ts_test (
  2.     a INTEGER,
  3.     b INTEGER NOT NULL,
  4.     c TIMESTAMP NULL,
  5.     d TIMESTAMP NOT NULL
  6. )

Changed in version 1.0.0: - SQLAlchemy now renders NULL or NOT NULL in allcases for TIMESTAMP columns, to accommodateexplicit_defaults_for_timestamp. Prior to this version, it willnot render “NOT NULL” for a TIMESTAMP column that is nullable=False.

MySQL Data Types

As with all SQLAlchemy dialects, all UPPERCASE types that are known to bevalid with MySQL are importable from the top level dialect:

  1. from sqlalchemy.dialects.mysql import \
  2.         BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, DATE, \
  3.         DATETIME, DECIMAL, DECIMAL, DOUBLE, ENUM, FLOAT, INTEGER, \
  4.         LONGBLOB, LONGTEXT, MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, NCHAR, \
  5.         NUMERIC, NVARCHAR, REAL, SET, SMALLINT, TEXT, TIME, TIMESTAMP, \
  6.         TINYBLOB, TINYINT, TINYTEXT, VARBINARY, VARCHAR, YEAR

Types which are specific to MySQL, or have MySQL-specificconstruction arguments, are as follows:

  • class sqlalchemy.dialects.mysql.BIGINT(display_width=None, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._IntegerType, sqlalchemy.types.BIGINT

MySQL BIGINTEGER type.

  • init(display_width=None, **kw)

  • Construct a BIGINTEGER.

    • Parameters

      • display_width – Optional, maximum display width for this number.

      • unsigned – a boolean, optional.

      • zerofill – Optional. If true, values will be stored as stringsleft-padded with zeros. Note that this does not effect the valuesreturned by the underlying database API, which continue to benumeric.

  • class sqlalchemy.dialects.mysql.BINARY(length=None)
  • Bases: sqlalchemy.types._Binary

The SQL BINARY type.

  • init(length=None)

inherited from the init() method of _Binary

Initialize self. See help(type(self)) for accurate signature.

MySQL BIT type.

This type is for MySQL 5.0.3 or greater for MyISAM, and 5.0.5 or greaterfor MyISAM, MEMORY, InnoDB and BDB. For older versions, use aMSTinyInteger() type.

  • init(length=None)

  • Construct a BIT.

    • Parameters
    • length – Optional, number of bits.

The SQL BLOB type.

  • init(length=None)

inherited from theinit()method ofLargeBinary

Construct a LargeBinary type.

  1. - Parameters
  2. -

length – optional, a length for the column for use inDDL statements, for those binary types that accept a length,such as the MySQL BLOB type.

  • class sqlalchemy.dialects.mysql.BOOLEAN(create_constraint=True, name=None, _create_events=True)
  • Bases: sqlalchemy.types.Boolean

The SQL BOOLEAN type.

inherited from theinit()method ofBoolean

Construct a Boolean.

  1. - Parameters
  2. - 
  3.   -

create_constraint – defaults to True. If the booleanis generated as an int/smallint, also create a CHECK constrainton the table that ensures 1 or 0 as a value.

  1.   -

name – if a CHECK constraint is generated, specifythe name of the constraint.

  • class sqlalchemy.dialects.mysql.CHAR(length=None, **kwargs)
  • Bases: sqlalchemy.dialects.mysql.types._StringType, sqlalchemy.types.CHAR

MySQL CHAR type, for fixed-length character data.

  • init(length=None, **kwargs)

  • Construct a CHAR.

    • Parameters

      • length – Maximum data length, in characters.

      • binary – Optional, use the default binary collation for thenational character set. This does not affect the type of datastored, use a BINARY type for binary data.

      • collation – Optional, request a particular collation. Must becompatible with the national character set.

The SQL DATE type.

  • init()

inherited from the init() method of object

Initialize self. See help(type(self)) for accurate signature.

MySQL DATETIME type.

  • init(timezone=False, fsp=None)

  • Construct a MySQL DATETIME type.

    • Parameters

      • timezone – not used by the MySQL dialect.

      • fsp –

fractional seconds precision value.MySQL 5.6.4 supports storage of fractional seconds;this parameter will be used when emitting DDLfor the DATETIME type.

Note

DBAPI driver support for fractional seconds maybe limited; current support includesMySQL Connector/Python.

  • class sqlalchemy.dialects.mysql.DECIMAL(precision=None, scale=None, asdecimal=True, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._NumericType, sqlalchemy.types.DECIMAL

MySQL DECIMAL type.

  • init(precision=None, scale=None, asdecimal=True, **kw)

  • Construct a DECIMAL.

    • Parameters

      • precision – Total digits in this number. If scale and precisionare both None, values are stored to limits allowed by the server.

      • scale – The number of digits after the decimal point.

      • unsigned – a boolean, optional.

      • zerofill – Optional. If true, values will be stored as stringsleft-padded with zeros. Note that this does not effect the valuesreturned by the underlying database API, which continue to benumeric.

  • class sqlalchemy.dialects.mysql.DOUBLE(precision=None, scale=None, asdecimal=True, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._FloatType

MySQL DOUBLE type.

  • init(precision=None, scale=None, asdecimal=True, **kw)
  • Construct a DOUBLE.

Note

The DOUBLE type by default converts from floatto Decimal, using a truncation that defaults to 10 digits.Specify either scale=n or decimal_return_scale=n in orderto change this scale, or asdecimal=False to return valuesdirectly as Python floating points.

  1. - Parameters
  2. - 
  3.   -

precision – Total digits in this number. If scale and precisionare both None, values are stored to limits allowed by the server.

  1.   -

scale – The number of digits after the decimal point.

  1.   -

unsigned – a boolean, optional.

  1.   -

zerofill – Optional. If true, values will be stored as stringsleft-padded with zeros. Note that this does not effect the valuesreturned by the underlying database API, which continue to benumeric.

  • class sqlalchemy.dialects.mysql.ENUM(*enums, **kw)
  • Bases: sqlalchemy.types.NativeForEmulated, sqlalchemy.types.Enum, sqlalchemy.dialects.mysql.enumerated._EnumeratedValues

MySQL ENUM type.

  • init(*enums, **kw)
  • Construct an ENUM.

E.g.:

  1. Column('myenum', ENUM("foo", "bar", "baz"))
  1. - Parameters
  2. - 
  3.   -

enums –

The range of valid values for this ENUM. Values will bequoted when generating the schema according to the quoting flag (seebelow). This object may also be a PEP-435-compliant enumeratedtype.

  1.   -

strict –

This flag has no effect.

Changed in version The: MySQL ENUM type as well as the base Enumtype now validates all Python data values.

  1.   -

charset – Optional, a column-level character set for this stringvalue. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.

  1.   -

collation – Optional, a column-level collation for this stringvalue. Takes precedence to ‘binary’ short-hand.

  1.   -

ascii – Defaults to False: short-hand for the latin1character set, generates ASCII in schema.

  1.   -

unicode – Defaults to False: short-hand for the ucs2character set, generates UNICODE in schema.

  1.   -

binary – Defaults to False: short-hand, pick the binarycollation type that matches the column’s character set. GeneratesBINARY in schema. This does not affect the type of data stored,only the collation of character data.

  1.   -

quoting –

Defaults to ‘auto’: automatically determine enum valuequoting. If all enum values are surrounded by the same quotingcharacter, then use ‘quoted’ mode. Otherwise, use ‘unquoted’ mode.

’quoted’: values in enums are already quoted, they will be useddirectly when generating the schema - this usage is deprecated.

’unquoted’: values in enums are not quoted, they will be escaped andsurrounded by single quotes when generating the schema.

Previous versions of this type always required manually quotedvalues to be supplied; future versions will always quote the stringliterals for you. This is a transitional option.

  • class sqlalchemy.dialects.mysql.FLOAT(precision=None, scale=None, asdecimal=False, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._FloatType, sqlalchemy.types.FLOAT

MySQL FLOAT type.

  • init(precision=None, scale=None, asdecimal=False, **kw)

  • Construct a FLOAT.

    • Parameters

      • precision – Total digits in this number. If scale and precisionare both None, values are stored to limits allowed by the server.

      • scale – The number of digits after the decimal point.

      • unsigned – a boolean, optional.

      • zerofill – Optional. If true, values will be stored as stringsleft-padded with zeros. Note that this does not effect the valuesreturned by the underlying database API, which continue to benumeric.

  • class sqlalchemy.dialects.mysql.INTEGER(display_width=None, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._IntegerType, sqlalchemy.types.INTEGER

MySQL INTEGER type.

  • init(display_width=None, **kw)

  • Construct an INTEGER.

    • Parameters

      • display_width – Optional, maximum display width for this number.

      • unsigned – a boolean, optional.

      • zerofill – Optional. If true, values will be stored as stringsleft-padded with zeros. Note that this does not effect the valuesreturned by the underlying database API, which continue to benumeric.

MySQL JSON type.

MySQL supports JSON as of version 5.7. Note that MariaDB does notsupport JSON at the time of this writing.

The mysql.JSON type supports persistence of JSON valuesas well as the core index operations provided by types.JSONdatatype, by adapting the operations to render the JSON_EXTRACTfunction at the database level.

New in version 1.1.

  • class sqlalchemy.dialects.mysql.LONGBLOB(length=None)
  • Bases: sqlalchemy.types._Binary

MySQL LONGBLOB type, for binary data up to 2^32 bytes.

  • init(length=None)

inherited from the init() method of _Binary

Initialize self. See help(type(self)) for accurate signature.

  • class sqlalchemy.dialects.mysql.LONGTEXT(**kwargs)
  • Bases: sqlalchemy.dialects.mysql.types._StringType

MySQL LONGTEXT type, for text up to 2^32 characters.

  • init(**kwargs)

  • Construct a LONGTEXT.

    • Parameters

      • charset – Optional, a column-level character set for this stringvalue. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.

      • collation – Optional, a column-level collation for this stringvalue. Takes precedence to ‘binary’ short-hand.

      • ascii – Defaults to False: short-hand for the latin1character set, generates ASCII in schema.

      • unicode – Defaults to False: short-hand for the ucs2character set, generates UNICODE in schema.

      • national – Optional. If true, use the server’s configurednational character set.

      • binary – Defaults to False: short-hand, pick the binarycollation type that matches the column’s character set. GeneratesBINARY in schema. This does not affect the type of data stored,only the collation of character data.

  • class sqlalchemy.dialects.mysql.MEDIUMBLOB(length=None)
  • Bases: sqlalchemy.types._Binary

MySQL MEDIUMBLOB type, for binary data up to 2^24 bytes.

  • init(length=None)

inherited from the init() method of _Binary

Initialize self. See help(type(self)) for accurate signature.

  • class sqlalchemy.dialects.mysql.MEDIUMINT(display_width=None, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._IntegerType

MySQL MEDIUMINTEGER type.

  • init(display_width=None, **kw)

  • Construct a MEDIUMINTEGER

    • Parameters

      • display_width – Optional, maximum display width for this number.

      • unsigned – a boolean, optional.

      • zerofill – Optional. If true, values will be stored as stringsleft-padded with zeros. Note that this does not effect the valuesreturned by the underlying database API, which continue to benumeric.

  • class sqlalchemy.dialects.mysql.MEDIUMTEXT(**kwargs)
  • Bases: sqlalchemy.dialects.mysql.types._StringType

MySQL MEDIUMTEXT type, for text up to 2^24 characters.

  • init(**kwargs)

  • Construct a MEDIUMTEXT.

    • Parameters

      • charset – Optional, a column-level character set for this stringvalue. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.

      • collation – Optional, a column-level collation for this stringvalue. Takes precedence to ‘binary’ short-hand.

      • ascii – Defaults to False: short-hand for the latin1character set, generates ASCII in schema.

      • unicode – Defaults to False: short-hand for the ucs2character set, generates UNICODE in schema.

      • national – Optional. If true, use the server’s configurednational character set.

      • binary – Defaults to False: short-hand, pick the binarycollation type that matches the column’s character set. GeneratesBINARY in schema. This does not affect the type of data stored,only the collation of character data.

  • class sqlalchemy.dialects.mysql.NCHAR(length=None, **kwargs)
  • Bases: sqlalchemy.dialects.mysql.types._StringType, sqlalchemy.types.NCHAR

MySQL NCHAR type.

For fixed-length character data in the server’s configured nationalcharacter set.

  • init(length=None, **kwargs)

  • Construct an NCHAR.

    • Parameters

      • length – Maximum data length, in characters.

      • binary – Optional, use the default binary collation for thenational character set. This does not affect the type of datastored, use a BINARY type for binary data.

      • collation – Optional, request a particular collation. Must becompatible with the national character set.

  • class sqlalchemy.dialects.mysql.NUMERIC(precision=None, scale=None, asdecimal=True, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._NumericType, sqlalchemy.types.NUMERIC

MySQL NUMERIC type.

  • init(precision=None, scale=None, asdecimal=True, **kw)

  • Construct a NUMERIC.

    • Parameters

      • precision – Total digits in this number. If scale and precisionare both None, values are stored to limits allowed by the server.

      • scale – The number of digits after the decimal point.

      • unsigned – a boolean, optional.

      • zerofill – Optional. If true, values will be stored as stringsleft-padded with zeros. Note that this does not effect the valuesreturned by the underlying database API, which continue to benumeric.

  • class sqlalchemy.dialects.mysql.NVARCHAR(length=None, **kwargs)
  • Bases: sqlalchemy.dialects.mysql.types._StringType, sqlalchemy.types.NVARCHAR

MySQL NVARCHAR type.

For variable-length character data in the server’s configured nationalcharacter set.

  • init(length=None, **kwargs)

  • Construct an NVARCHAR.

    • Parameters

      • length – Maximum data length, in characters.

      • binary – Optional, use the default binary collation for thenational character set. This does not affect the type of datastored, use a BINARY type for binary data.

      • collation – Optional, request a particular collation. Must becompatible with the national character set.

  • class sqlalchemy.dialects.mysql.REAL(precision=None, scale=None, asdecimal=True, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._FloatType, sqlalchemy.types.REAL

MySQL REAL type.

  • init(precision=None, scale=None, asdecimal=True, **kw)
  • Construct a REAL.

Note

The REAL type by default converts from floatto Decimal, using a truncation that defaults to 10 digits.Specify either scale=n or decimal_return_scale=n in orderto change this scale, or asdecimal=False to return valuesdirectly as Python floating points.

  1. - Parameters
  2. - 
  3.   -

precision – Total digits in this number. If scale and precisionare both None, values are stored to limits allowed by the server.

  1.   -

scale – The number of digits after the decimal point.

  1.   -

unsigned – a boolean, optional.

  1.   -

zerofill – Optional. If true, values will be stored as stringsleft-padded with zeros. Note that this does not effect the valuesreturned by the underlying database API, which continue to benumeric.

  • class sqlalchemy.dialects.mysql.SET(*values, **kw)
  • Bases: sqlalchemy.dialects.mysql.enumerated._EnumeratedValues

MySQL SET type.

  • init(*values, **kw)
  • Construct a SET.

E.g.:

  1. Column('myset', SET("foo", "bar", "baz"))

The list of potential values is required in the case that thisset will be used to generate DDL for a table, or if theSET.retrieve_as_bitwise flag is set to True.

  1. - Parameters
  2. - 
  3.   -

values – The range of valid values for this SET.

  1.   -

convert_unicode – Same flag as that ofString.convert_unicode.

  1.   -

collation – same as that of String.collation

  1.   -

charset – same as that of VARCHAR.charset.

  1.   -

ascii – same as that of VARCHAR.ascii.

  1.   -

unicode – same as that of VARCHAR.unicode.

  1.   -

binary – same as that of VARCHAR.binary.

  1.   -

quoting –

Defaults to ‘auto’: automatically determine set valuequoting. If all values are surrounded by the same quotingcharacter, then use ‘quoted’ mode. Otherwise, use ‘unquoted’ mode.

’quoted’: values in enums are already quoted, they will be useddirectly when generating the schema - this usage is deprecated.

’unquoted’: values in enums are not quoted, they will be escaped andsurrounded by single quotes when generating the schema.

Previous versions of this type always required manually quotedvalues to be supplied; future versions will always quote the stringliterals for you. This is a transitional option.

New in version 0.9.0.

  1.   -

retrieve_as_bitwise –

if True, the data for the set type will bepersisted and selected using an integer value, where a set is coercedinto a bitwise mask for persistence. MySQL allows this mode whichhas the advantage of being able to store values unambiguously,such as the blank string ''. The datatype will appearas the expression col + 0 in a SELECT statement, so that thevalue is coerced into an integer value in result sets.This flag is required if one wishesto persist a set that can store the blank string '' as a value.

Warning

When using mysql.SET.retrieve_as_bitwise, it isessential that the list of set values is expressed in theexact same order as exists on the MySQL database.

New in version 1.0.0.

  • class sqlalchemy.dialects.mysql.SMALLINT(display_width=None, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._IntegerType, sqlalchemy.types.SMALLINT

MySQL SMALLINTEGER type.

  • init(display_width=None, **kw)

  • Construct a SMALLINTEGER.

    • Parameters

      • display_width – Optional, maximum display width for this number.

      • unsigned – a boolean, optional.

      • zerofill – Optional. If true, values will be stored as stringsleft-padded with zeros. Note that this does not effect the valuesreturned by the underlying database API, which continue to benumeric.

  • class sqlalchemy.dialects.mysql.TEXT(length=None, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._StringType, sqlalchemy.types.TEXT

MySQL TEXT type, for text up to 2^16 characters.

  • init(length=None, **kw)

  • Construct a TEXT.

    • Parameters

      • length – Optional, if provided the server may optimize storageby substituting the smallest TEXT type sufficient to storelength characters.

      • charset – Optional, a column-level character set for this stringvalue. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.

      • collation – Optional, a column-level collation for this stringvalue. Takes precedence to ‘binary’ short-hand.

      • ascii – Defaults to False: short-hand for the latin1character set, generates ASCII in schema.

      • unicode – Defaults to False: short-hand for the ucs2character set, generates UNICODE in schema.

      • national – Optional. If true, use the server’s configurednational character set.

      • binary – Defaults to False: short-hand, pick the binarycollation type that matches the column’s character set. GeneratesBINARY in schema. This does not affect the type of data stored,only the collation of character data.

MySQL TIME type.

  • init(timezone=False, fsp=None)

  • Construct a MySQL TIME type.

    • Parameters

      • timezone – not used by the MySQL dialect.

      • fsp –

fractional seconds precision value.MySQL 5.6 supports storage of fractional seconds;this parameter will be used when emitting DDLfor the TIME type.

Note

DBAPI driver support for fractional seconds maybe limited; current support includesMySQL Connector/Python.

MySQL TIMESTAMP type.

  • init(timezone=False, fsp=None)

  • Construct a MySQL TIMESTAMP type.

    • Parameters

      • timezone – not used by the MySQL dialect.

      • fsp –

fractional seconds precision value.MySQL 5.6.4 supports storage of fractional seconds;this parameter will be used when emitting DDLfor the TIMESTAMP type.

Note

DBAPI driver support for fractional seconds maybe limited; current support includesMySQL Connector/Python.

  • class sqlalchemy.dialects.mysql.TINYBLOB(length=None)
  • Bases: sqlalchemy.types._Binary

MySQL TINYBLOB type, for binary data up to 2^8 bytes.

  • init(length=None)

inherited from the init() method of _Binary

Initialize self. See help(type(self)) for accurate signature.

  • class sqlalchemy.dialects.mysql.TINYINT(display_width=None, **kw)
  • Bases: sqlalchemy.dialects.mysql.types._IntegerType

MySQL TINYINT type.

  • init(display_width=None, **kw)

  • Construct a TINYINT.

    • Parameters

      • display_width – Optional, maximum display width for this number.

      • unsigned – a boolean, optional.

      • zerofill – Optional. If true, values will be stored as stringsleft-padded with zeros. Note that this does not effect the valuesreturned by the underlying database API, which continue to benumeric.

  • class sqlalchemy.dialects.mysql.TINYTEXT(**kwargs)
  • Bases: sqlalchemy.dialects.mysql.types._StringType

MySQL TINYTEXT type, for text up to 2^8 characters.

  • init(**kwargs)

  • Construct a TINYTEXT.

    • Parameters

      • charset – Optional, a column-level character set for this stringvalue. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.

      • collation – Optional, a column-level collation for this stringvalue. Takes precedence to ‘binary’ short-hand.

      • ascii – Defaults to False: short-hand for the latin1character set, generates ASCII in schema.

      • unicode – Defaults to False: short-hand for the ucs2character set, generates UNICODE in schema.

      • national – Optional. If true, use the server’s configurednational character set.

      • binary – Defaults to False: short-hand, pick the binarycollation type that matches the column’s character set. GeneratesBINARY in schema. This does not affect the type of data stored,only the collation of character data.

  • class sqlalchemy.dialects.mysql.VARBINARY(length=None)
  • Bases: sqlalchemy.types._Binary

The SQL VARBINARY type.

  • init(length=None)

inherited from the init() method of _Binary

Initialize self. See help(type(self)) for accurate signature.

  • class sqlalchemy.dialects.mysql.VARCHAR(length=None, **kwargs)
  • Bases: sqlalchemy.dialects.mysql.types._StringType, sqlalchemy.types.VARCHAR

MySQL VARCHAR type, for variable-length character data.

  • init(length=None, **kwargs)

  • Construct a VARCHAR.

    • Parameters

      • charset – Optional, a column-level character set for this stringvalue. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.

      • collation – Optional, a column-level collation for this stringvalue. Takes precedence to ‘binary’ short-hand.

      • ascii – Defaults to False: short-hand for the latin1character set, generates ASCII in schema.

      • unicode – Defaults to False: short-hand for the ucs2character set, generates UNICODE in schema.

      • national – Optional. If true, use the server’s configurednational character set.

      • binary – Defaults to False: short-hand, pick the binarycollation type that matches the column’s character set. GeneratesBINARY in schema. This does not affect the type of data stored,only the collation of character data.

MySQL YEAR type, for single byte storage of years 1901-2155.

  • init(display_width=None)
  • Initialize self. See help(type(self)) for accurate signature.

MySQL DML Constructs

  • sqlalchemy.dialects.mysql.dml.insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)
  • Construct a new Insert object.

This constructor is mirrored as a public API function; see insert() for a full usage and argument description.

  • class sqlalchemy.dialects.mysql.dml.Insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)
  • Bases: sqlalchemy.sql.expression.Insert

MySQL-specific implementation of INSERT.

Adds methods for MySQL-specific syntaxes such as ON DUPLICATE KEY UPDATE.

New in version 1.2.

  • property inserted
  • Provide the “inserted” namespace for an ON DUPLICATE KEY UPDATE statement

MySQL’s ON DUPLICATE KEY UPDATE clause allows reference to the rowthat would be inserted, via a special function called VALUES().This attribute provides all columns in this row to be referenceablesuch that they will render within a VALUES() function inside theON DUPLICATE KEY UPDATE clause. The attribute is named .insertedso as not to conflict with the existing Insert.values() method.

See also

INSERT…ON DUPLICATE KEY UPDATE (Upsert) - example of howto use Insert.inserted

  • onduplicate_key_update(args, *kw_)

  • Specifies the ON DUPLICATE KEY UPDATE clause.

    • Parameters
    • **kw – Column keys linked to UPDATE values. Thevalues may be any SQL expression or supported literal Pythonvalues.

Warning

This dictionary does not take into accountPython-specified default UPDATE values or generation functions,e.g. those specified using Column.onupdate.These values will not be exercised for an ON DUPLICATE KEY UPDATEstyle of UPDATE, unless values are manually specified here.

  1. - Parameters
  2. -

*args –

As an alternative to passing key/value parameters,a dictionary or list of 2-tuples can be passed as a single positionalargument.

Passing a single dictionary is equivalent to the keyword argumentform:

  1. insert().on_duplicate_key_update({"name": "some name"})

Passing a list of 2-tuples indicates that the parameter assignmentsin the UPDATE clause should be ordered as sent, in a manner similarto that described for the Update construct overallin Parameter-Ordered Updates:

  1. insert().on_duplicate_key_update(
  2.     [("name", "some name"), ("value", "some value")])

Changed in version 1.3: parameters can be specified as a dictionaryor list of 2-tuples; the latter form provides for parameterordering.

New in version 1.2.

See also

INSERT…ON DUPLICATE KEY UPDATE (Upsert)

mysqlclient (fork of MySQL-Python)

Support for the MySQL database via the mysqlclient (maintained fork of MySQL-Python) driver.

DBAPI

Documentation and download information (if applicable) for mysqlclient (maintained fork of MySQL-Python) is available at:https://pypi.org/project/mysqlclient/

Connecting

Connect String:

  1. mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

Driver Status

The mysqlclient DBAPI is a maintained fork of theMySQL-Python DBAPIthat is no longer maintained. mysqlclient supports Python 2 and Python 3and is very stable.

Unicode

Please see Unicode for current recommendations on unicodehandling.

Using MySQLdb with Google Cloud SQL

Google Cloud SQL now recommends use of the MySQLdb dialect. Connectusing a URL like the following:

  1. mysql+mysqldb://root@/<dbname>?unix_socket=/cloudsql/<projectid>:<instancename>

Server Side Cursors

The mysqldb dialect supports server-side cursors. See Server Side Cursors.

PyMySQL

Support for the MySQL database via the PyMySQL driver.

DBAPI

Documentation and download information (if applicable) for PyMySQL is available at:https://pymysql.readthedocs.io/

Connecting

Connect String:

  1. mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

Unicode

Please see Unicode for current recommendations on unicodehandling.

MySQL-Python Compatibility

The pymysql DBAPI is a pure Python port of the MySQL-python (MySQLdb) driver,and targets 100% compatibility. Most behavioral notes for MySQL-python applyto the pymysql driver as well.

MySQL-Connector

Support for the MySQL database via the MySQL Connector/Python driver.

DBAPI

Documentation and download information (if applicable) for MySQL Connector/Python is available at:https://pypi.org/project/mysql-connector-python/

Connecting

Connect String:

  1. mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

Note

The MySQL Connector/Python DBAPI has had many issues since its release,some of which may remain unresolved, and the mysqlconnector dialect isnot tested as part of SQLAlchemy’s continuous integration.The recommended MySQL dialects are mysqlclient and PyMySQL.

cymysql

Support for the MySQL database via the CyMySQL driver.

DBAPI

Documentation and download information (if applicable) for CyMySQL is available at:https://github.com/nakagami/CyMySQL

Connecting

Connect String:

  1. mysql+cymysql://<username>:<password>@<host>/<dbname>[?<options>]

Note

The CyMySQL dialect is not tested as part of SQLAlchemy’s continuousintegration and may have unresolved issues. The recommended MySQLdialects are mysqlclient and PyMySQL.

OurSQL

Support for the MySQL database via the OurSQL driver.

DBAPI

Documentation and download information (if applicable) for OurSQL is available at:http://packages.python.org/oursql/

Connecting

Connect String:

  1. mysql+oursql://<user>:<password>@<host>[:<port>]/<dbname>

Note

The OurSQL MySQL dialect is legacy and is no longer supported upstream,and is not tested as part of SQLAlchemy’s continuous integration.The recommended MySQL dialects are mysqlclient and PyMySQL.

Unicode

Please see Unicode for current recommendations on unicodehandling.

Google App Engine

Support for the MySQL database via the Google Cloud SQL driver.

This dialect is based primarily on the mysql.mysqldb dialect withminimal changes.

Deprecated since version 1.0: This dialect is no longer necessary forGoogle Cloud SQL; the MySQLdb dialect can be used directly.Cloud SQL now recommends creating connections via themysql dialect using the URL format

mysql+mysqldb://root@/<dbname>?unix_socket=/cloudsql/<projectid>:<instancename>

DBAPI

Documentation and download information (if applicable) for Google Cloud SQL is available at:https://developers.google.com/appengine/docs/python/cloud-sql/developers-guide

Connecting

Connect String:

  1. mysql+gaerdbms:///<dbname>?instance=<instancename>

Pooling

Google App Engine connections appear to be randomly recycled,so the dialect does not pool connections. The NullPoolimplementation is installed within the Engine bydefault.

pyodbc

Support for the MySQL database via the PyODBC driver.

Note

The PyODBC for MySQL dialect is not well supported, andis subject to unresolved character encoding issueswhich exist within the current ODBC drivers available.(see http://code.google.com/p/pyodbc/issues/detail?id=25).Other dialects for MySQL are recommended.

DBAPI

Documentation and download information (if applicable) for PyODBC is available at:http://pypi.python.org/pypi/pyodbc/

Connecting

Connect String:

  1. mysql+pyodbc://<username>:<password>@<dsnname>

zxjdbc

Support for the MySQL database via the zxjdbc for Jython driver.

Note

Jython is not supported by current versions of SQLAlchemy. Thezxjdbc dialect should be considered as experimental.

DBAPI

Drivers for this database are available at:http://dev.mysql.com/downloads/connector/j/

Connecting

Connect String:

  1. mysql+zxjdbc://<user>:<password>@<hostname>[:<port>]/<database>

Character Sets

SQLAlchemy zxjdbc dialects pass unicode straight through to thezxjdbc/JDBC layer. To allow multiple character sets to be sent from theMySQL Connector/J JDBC driver, by default SQLAlchemy sets itscharacterEncoding connection property to UTF-8. It may beoverridden via a create_engine URL parameter.