MySQL
Support for the MySQL database.
Supported Versions and Features
SQLAlchemy supports 6 major MySQL versions: 3.23, 4.0, 4.1, 5.0, 5.1 and 6.0,
with capabilities increasing with more modern servers.
Versions 4.1 and higher support the basic SQL functionality that SQLAlchemy
uses in the ORM and SQL expressions. These versions pass the applicable tests
in the suite 100%. No heroic measures are taken to work around major missing
SQL features- if your server version does not support sub-selects, for
example, they won’t work in SQLAlchemy either.
Most available DBAPI drivers are supported; see below.
Feature |
Minimum Version |
sqlalchemy.orm |
4.1.1 |
Table Reflection |
3.23.x |
DDL Generation |
4.1.1 |
utf8/Full Unicode Connections |
4.1.1 |
Transactions |
3.23.15 |
Two-Phase Transactions |
5.0.3 |
Nested Transactions |
5.0.3 |
See the official MySQL documentation for detailed information about features
supported in any given server release.
Connecting
See the API documentation on individual drivers for details on connecting.
Connection Timeouts
MySQL features an automatic connection close behavior, for connections that have
been idle for eight hours or more. To circumvent having this issue, use the
pool_recycle option which controls the maximum age of any connection:
engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)
Storage Engines
Most MySQL server installations have a default table type of MyISAM, a
non-transactional table type. During a transaction, non-transactional storage
engines do not participate and continue to store table changes in autocommit
mode. For fully atomic transactions, all participating tables must use a
transactional engine such as InnoDB, Falcon, SolidDB, PBXT, etc.
Storage engines can be elected when creating tables in SQLAlchemy by supplying
a mysql_engine='whatever' to the Table constructor. Any MySQL table
creation option can be specified in this syntax:
Table('mytable', metadata,
Column('data', String(32)),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
Keys
Not all MySQL storage engines support foreign keys. For MyISAM and
similar engines, the information loaded by table reflection will not include
foreign keys. For these tables, you may supply a
ForeignKeyConstraint at reflection time:
Table('mytable', metadata,
ForeignKeyConstraint(['other_id'], ['othertable.other_id']),
autoload=True
)
When creating tables, SQLAlchemy will automatically set AUTO_INCREMENT` on
an integer 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 to the
Column. This flag can also be used to enable
auto-increment on a secondary column in a multi-column key for some storage
engines:
Table('mytable', metadata,
Column('gid', Integer, primary_key=True, autoincrement=False),
Column('id', Integer, primary_key=True)
)
SQL Mode
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 when
creating 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 or by
supplying --sql-mode to mysqld. You can also use a
sqlalchemy.pool.Pool listener hook to issue a SET SESSION
sql_mode='...' on connect to configure each connection.
If you do not specify use_ansiquotes, the regular MySQL quoting style is
used by default.
If you do issue a SET sql_mode through SQLAlchemy, the dialect must be
updated if the quoting style is changed. Again, this change will affect all
connections:
connection.execute('SET sql_mode="ansi"')
connection.dialect.use_ansiquotes = True
MySQL SQL Extensions
Many of the MySQL SQL extensions are handled through SQLAlchemy’s generic
function 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 MySQL statement can be executed as a string as well.
Some limited direct support for MySQL extensions to SQL is currently
available.
SELECT pragma:
select(..., prefixes=['HIGH_PRIORITY', 'SQL_SMALL_RESULT'])
UPDATE with LIMIT:
update(..., mysql_limit=10)
Troubleshooting
If you have problems that seem server related, first check that you are
using the most recent stable MySQL-Python package available. The Database
Notes page on the wiki at http://www.sqlalchemy.org is a good resource for
timely information affecting MySQL in SQLAlchemy.
MySQL Data Types
As with all SQLAlchemy dialects, all UPPERCASE types that are known to be
valid with MySQL are importable from the top level dialect:
from sqlalchemy.dialects.mysql import \
BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, DATE, \
DATETIME, DECIMAL, DECIMAL, DOUBLE, ENUM, FLOAT, INTEGER, \
LONGBLOB, LONGTEXT, MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, NCHAR, \
NUMERIC, NVARCHAR, REAL, SET, SMALLINT, TEXT, TIME, TIMESTAMP, \
TINYBLOB, TINYINT, TINYTEXT, VARBINARY, VARCHAR, YEAR
Types which are specific to MySQL, or have MySQL-specific
construction arguments, are as follows:
-
class sqlalchemy.dialects.mysql.BIGINT(display_width=None, **kw)
Bases: sqlalchemy.dialects.mysql.base._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 strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.
|
-
class sqlalchemy.dialects.mysql.BINARY(length=None)
Bases: sqlalchemy.types._Binary
The SQL BINARY type.
-
class sqlalchemy.dialects.mysql.BIT(length=None)
Bases: sqlalchemy.types.TypeEngine
MySQL BIT type.
This type is for MySQL 5.0.3 or greater for MyISAM, and 5.0.5 or greater for
MyISAM, MEMORY, InnoDB and BDB. For older versions, use a MSTinyInteger()
type.
-
__init__(length=None)
Construct a BIT.
Parameters: | length – Optional, number of bits. |
-
class sqlalchemy.dialects.mysql.BLOB(length=None)
Bases: sqlalchemy.types.LargeBinary
The SQL BLOB type.
-
__init__(length=None)
Construct a LargeBinary type.
Parameters: | length – optional, a length for the column for use in
DDL statements, for those BLOB types that accept a length
(i.e. MySQL). It does not produce a small BINARY/VARBINARY
type - use the BINARY/VARBINARY types specifically for those.
May be safely omitted if no CREATE
TABLE will be issued. Certain databases may require a
length for use in DDL, and will raise an exception when
the CREATE TABLE DDL is issued. |
-
class sqlalchemy.dialects.mysql.BOOLEAN(create_constraint=True, name=None)
Bases: sqlalchemy.types.Boolean
The SQL BOOLEAN type.
-
__init__(create_constraint=True, name=None)
Construct a Boolean.
Parameters: |
- create_constraint – defaults to True. If the boolean
is generated as an int/smallint, also create a CHECK constraint
on the table that ensures 1 or 0 as a value.
- name – if a CHECK constraint is generated, specify
the name of the constraint.
|
-
class sqlalchemy.dialects.mysql.CHAR(length, **kwargs)
Bases: sqlalchemy.dialects.mysql.base._StringType, sqlalchemy.types.CHAR
MySQL CHAR type, for fixed-length character data.
-
__init__(length, **kwargs)
Construct a CHAR.
Parameters: |
- length – Maximum data length, in characters.
- binary – Optional, use the default binary collation for the
national character set. This does not affect the type of data
stored, use a BINARY type for binary data.
- collation – Optional, request a particular collation. Must be
compatible with the national character set.
|
-
class sqlalchemy.dialects.mysql.DATE(*args, **kwargs)
Bases: sqlalchemy.types.Date
The SQL DATE type.
-
class sqlalchemy.dialects.mysql.DATETIME(timezone=False)
Bases: sqlalchemy.types.DateTime
The SQL DATETIME type.
-
class sqlalchemy.dialects.mysql.DECIMAL(precision=None, scale=None, asdecimal=True, **kw)
Bases: sqlalchemy.dialects.mysql.base._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 precision
are 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 strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.
|
-
class sqlalchemy.dialects.mysql.DOUBLE(precision=None, scale=None, asdecimal=True, **kw)
Bases: sqlalchemy.dialects.mysql.base._FloatType
MySQL DOUBLE type.
-
__init__(precision=None, scale=None, asdecimal=True, **kw)
Construct a DOUBLE.
Parameters: |
- precision – Total digits in this number. If scale and precision
are 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 strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.
|
-
class sqlalchemy.dialects.mysql.ENUM(*enums, **kw)
Bases: sqlalchemy.types.Enum, sqlalchemy.dialects.mysql.base._StringType
MySQL ENUM type.
-
__init__(*enums, **kw)
Construct an ENUM.
Example:
Column(‘myenum’, MSEnum(“foo”, “bar”, “baz”))
Parameters: |
- enums – The range of valid values for this ENUM. Values will be
quoted when generating the schema according to the quoting flag (see
below).
- strict – Defaults to False: ensure that a given value is in this
ENUM’s range of permissible values when inserting or updating rows.
Note that MySQL will not raise a fatal error if you attempt to store
an out of range value- an alternate value will be stored instead.
(See MySQL ENUM documentation.)
- charset – Optional, a column-level character set for this string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.
- collation – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.
- ascii – Defaults to False: short-hand for the latin1
character set, generates ASCII in schema.
- unicode – Defaults to False: short-hand for the ucs2
character set, generates UNICODE in schema.
- binary – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY in schema. This does not affect the type of data stored,
only the collation of character data.
- quoting –
Defaults to ‘auto’: automatically determine enum value
quoting. If all enum values are surrounded by the same quoting
character, then use ‘quoted’ mode. Otherwise, use ‘unquoted’ mode.
‘quoted’: values in enums are already quoted, they will be used
directly when generating the schema - this usage is deprecated.
‘unquoted’: values in enums are not quoted, they will be escaped and
surrounded by single quotes when generating the schema.
Previous versions of this type always required manually quoted
values to be supplied; future versions will always quote the string
literals for you. This is a transitional option.
|
-
class sqlalchemy.dialects.mysql.FLOAT(precision=None, scale=None, asdecimal=False, **kw)
Bases: sqlalchemy.dialects.mysql.base._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 precision
are 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 strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.
|
-
class sqlalchemy.dialects.mysql.INTEGER(display_width=None, **kw)
Bases: sqlalchemy.dialects.mysql.base._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 strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.
|
-
class sqlalchemy.dialects.mysql.LONGBLOB(length=None)
Bases: sqlalchemy.types._Binary
MySQL LONGBLOB type, for binary data up to 2^32 bytes.
-
class sqlalchemy.dialects.mysql.LONGTEXT(**kwargs)
Bases: sqlalchemy.dialects.mysql.base._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 string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.
- collation – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.
- ascii – Defaults to False: short-hand for the latin1
character set, generates ASCII in schema.
- unicode – Defaults to False: short-hand for the ucs2
character set, generates UNICODE in schema.
- national – Optional. If true, use the server’s configured
national character set.
- binary – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY 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.
-
class sqlalchemy.dialects.mysql.MEDIUMINT(display_width=None, **kw)
Bases: sqlalchemy.dialects.mysql.base._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 strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.
|
-
class sqlalchemy.dialects.mysql.MEDIUMTEXT(**kwargs)
Bases: sqlalchemy.dialects.mysql.base._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 string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.
- collation – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.
- ascii – Defaults to False: short-hand for the latin1
character set, generates ASCII in schema.
- unicode – Defaults to False: short-hand for the ucs2
character set, generates UNICODE in schema.
- national – Optional. If true, use the server’s configured
national character set.
- binary – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY 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.base._StringType, sqlalchemy.types.NCHAR
MySQL NCHAR type.
For fixed-length character data in the server’s configured national
character set.
-
__init__(length=None, **kwargs)
Construct an NCHAR.
Parameters: |
- length – Maximum data length, in characters.
- binary – Optional, use the default binary collation for the
national character set. This does not affect the type of data
stored, use a BINARY type for binary data.
- collation – Optional, request a particular collation. Must be
compatible with the national character set.
|
-
class sqlalchemy.dialects.mysql.NUMERIC(precision=None, scale=None, asdecimal=True, **kw)
Bases: sqlalchemy.dialects.mysql.base._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 precision
are 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 strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.
|
-
class sqlalchemy.dialects.mysql.NVARCHAR(length=None, **kwargs)
Bases: sqlalchemy.dialects.mysql.base._StringType, sqlalchemy.types.NVARCHAR
MySQL NVARCHAR type.
For variable-length character data in the server’s configured national
character set.
-
__init__(length=None, **kwargs)
Construct an NVARCHAR.
Parameters: |
- length – Maximum data length, in characters.
- binary – Optional, use the default binary collation for the
national character set. This does not affect the type of data
stored, use a BINARY type for binary data.
- collation – Optional, request a particular collation. Must be
compatible with the national character set.
|
-
class sqlalchemy.dialects.mysql.REAL(precision=None, scale=None, asdecimal=True, **kw)
Bases: sqlalchemy.dialects.mysql.base._FloatType
MySQL REAL type.
-
__init__(precision=None, scale=None, asdecimal=True, **kw)
Construct a REAL.
Parameters: |
- precision – Total digits in this number. If scale and precision
are 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 strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.
|
-
class sqlalchemy.dialects.mysql.SET(*values, **kw)
Bases: sqlalchemy.dialects.mysql.base._StringType
MySQL SET type.
-
__init__(*values, **kw)
Construct a SET.
Example:
Column('myset', MSSet("'foo'", "'bar'", "'baz'"))
Parameters: |
- values – The range of valid values for this SET. Values will be
used exactly as they appear when generating schemas. Strings must
be quoted, as in the example above. Single-quotes are suggested for
ANSI compatibility and are required for portability to servers with
ANSI_QUOTES enabled.
- charset – Optional, a column-level character set for this string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.
- collation – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.
- ascii – Defaults to False: short-hand for the latin1
character set, generates ASCII in schema.
- unicode – Defaults to False: short-hand for the ucs2
character set, generates UNICODE in schema.
- binary – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY in schema. This does not affect the type of data stored,
only the collation of character data.
|
-
class sqlalchemy.dialects.mysql.SMALLINT(display_width=None, **kw)
Bases: sqlalchemy.dialects.mysql.base._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 strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.
|
-
class sqlalchemy.dialects.mysql.TEXT(length=None, **kw)
Bases: sqlalchemy.dialects.mysql.base._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 storage
by substituting the smallest TEXT type sufficient to store
length characters.
- charset – Optional, a column-level character set for this string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.
- collation – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.
- ascii – Defaults to False: short-hand for the latin1
character set, generates ASCII in schema.
- unicode – Defaults to False: short-hand for the ucs2
character set, generates UNICODE in schema.
- national – Optional. If true, use the server’s configured
national character set.
- binary – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY in schema. This does not affect the type of data stored,
only the collation of character data.
|
-
class sqlalchemy.dialects.mysql.TIME(timezone=False)
Bases: sqlalchemy.types.Time
The SQL TIME type.
-
class sqlalchemy.dialects.mysql.TIMESTAMP(timezone=False)
Bases: sqlalchemy.types.TIMESTAMP
MySQL TIMESTAMP type.
-
class sqlalchemy.dialects.mysql.TINYBLOB(length=None)
Bases: sqlalchemy.types._Binary
MySQL TINYBLOB type, for binary data up to 2^8 bytes.
-
class sqlalchemy.dialects.mysql.TINYINT(display_width=None, **kw)
Bases: sqlalchemy.dialects.mysql.base._IntegerType
MySQL TINYINT type.
-
__init__(display_width=None, **kw)
Construct a TINYINT.
Note: following the usual MySQL conventions, TINYINT(1) columns
reflected during Table(..., autoload=True) are treated as
Boolean columns.
Parameters: |
- display_width – Optional, maximum display width for this number.
- unsigned – a boolean, 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.
|
-
class sqlalchemy.dialects.mysql.TINYTEXT(**kwargs)
Bases: sqlalchemy.dialects.mysql.base._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 string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.
- collation – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.
- ascii – Defaults to False: short-hand for the latin1
character set, generates ASCII in schema.
- unicode – Defaults to False: short-hand for the ucs2
character set, generates UNICODE in schema.
- national – Optional. If true, use the server’s configured
national character set.
- binary – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY 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.
-
class sqlalchemy.dialects.mysql.VARCHAR(length=None, **kwargs)
Bases: sqlalchemy.dialects.mysql.base._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 string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.
- collation – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.
- ascii – Defaults to False: short-hand for the latin1
character set, generates ASCII in schema.
- unicode – Defaults to False: short-hand for the ucs2
character set, generates UNICODE in schema.
- national – Optional. If true, use the server’s configured
national character set.
- binary – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY in schema. This does not affect the type of data stored,
only the collation of character data.
|
-
class sqlalchemy.dialects.mysql.YEAR(display_width=None)
Bases: sqlalchemy.types.TypeEngine
MySQL YEAR type, for single byte storage of years 1901-2155.
MySQL-Python Notes
Support for the MySQL database via the MySQL-python adapter.
MySQL-Python is available at:
At least version 1.2.1 or 1.2.2 should be used.
Connecting
Connect string format:
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
Character Sets
Many MySQL server installations default to a latin1 encoding for client
connections. All data sent through the connection will be converted into
latin1, even if you have utf8 or another character set on your tables
and columns. With versions 4.1 and higher, you can change the connection
character set either through server configuration or by including the
charset parameter in the URL used for create_engine. The charset
option is passed through to MySQL-Python and has the side-effect of also
enabling use_unicode in the driver by default. For regular encoded
strings, also pass use_unicode=0 in the connection arguments:
# set client encoding to utf8; all strings come back as unicode
create_engine('mysql+mysqldb:///mydb?charset=utf8')
# set client encoding to utf8; all strings come back as utf8 str
create_engine('mysql+mysqldb:///mydb?charset=utf8&use_unicode=0')
Known Issues
MySQL-python at least as of version 1.2.2 has a serious memory leak related
to unicode conversion, a feature which is disabled via use_unicode=0.
The recommended connection form with SQLAlchemy is:
engine = create_engine('mysql://scott:tiger@localhost/test?charset=utf8&use_unicode=0', pool_recycle=3600)
OurSQL Notes
Support for the MySQL database via the oursql adapter.
OurSQL is available at:
Connecting
Connect string format:
mysql+oursql://<user>:<password>@<host>[:<port>]/<dbname>
Character Sets
oursql defaults to using utf8 as the connection charset, but other
encodings may be used instead. Like the MySQL-Python driver, unicode support
can be completely disabled:
# oursql sets the connection charset to utf8 automatically; all strings come
# back as utf8 str
create_engine('mysql+oursql:///mydb?use_unicode=0')
To not automatically use utf8 and instead use whatever the connection
defaults to, there is a separate parameter:
# use the default connection charset; all strings come back as unicode
create_engine('mysql+oursql:///mydb?default_charset=1')
# use latin1 as the connection charset; all strings come back as unicode
create_engine('mysql+oursql:///mydb?charset=latin1')
MySQL-Connector Notes
Support for the MySQL database via the MySQL Connector/Python adapter.
MySQL Connector/Python is available at:
Connecting
Connect string format:
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
pyodbc Notes
Support for the MySQL database via the pyodbc adapter.
pyodbc is available at:
Connecting
Connect string:
mysql+pyodbc://<username>:<password>@<dsnname>
Limitations
The mysql-pyodbc dialect is subject to unresolved character encoding issues
which exist within the current ODBC drivers available.
(see http://code.google.com/p/pyodbc/issues/detail?id=25). Consider usage
of OurSQL, MySQLdb, or MySQL-connector/Python.
zxjdbc Notes
Support for the MySQL database via Jython’s zxjdbc JDBC connector.
Connecting
Connect string format:
mysql+zxjdbc://<user>:<password>@<hostname>[:<port>]/<database>
Character Sets
SQLAlchemy zxjdbc dialects pass unicode straight through to the
zxjdbc/JDBC layer. To allow multiple character sets to be sent from the
MySQL Connector/J JDBC driver, by default SQLAlchemy sets its
characterEncoding connection property to UTF-8. It may be
overriden via a create_engine URL parameter.