84
84
database itself, especially if database reflection features are
87
Transaction Isolation Level
88
---------------------------
90
:func:`.create_engine` accepts an ``isolation_level``
91
parameter which results in the command ``SET SESSION
92
TRANSACTION ISOLATION LEVEL <level>`` being invoked for
93
every new connection. Valid values for this parameter are
94
``READ COMMITTED``, ``READ UNCOMMITTED``,
95
``REPEATABLE READ``, and ``SERIALIZABLE``::
97
engine = create_engine(
98
"mysql://scott:tiger@localhost/test",
99
isolation_level="READ UNCOMMITTED"
221
238
an integer. MySQL only allows a length for an index if it is for a CHAR,
222
239
VARCHAR, TEXT, BINARY, VARBINARY and BLOB.
244
Some MySQL storage engines permit you to specify an index type when creating
245
an index or primary key constraint. SQLAlchemy provides this feature via the
246
``mysql_using`` parameter on :class:`.Index`::
248
Index('my_index', my_table.c.data, mysql_using='hash')
250
As well as the ``mysql_using`` parameter on :class:`.PrimaryKeyConstraint`::
252
PrimaryKeyConstraint("data", mysql_using='hash')
254
The value passed to the keyword argument will be simply passed through to the
255
underlying CREATE INDEX or PRIMARY KEY clause, so it *must* be a valid index
256
type for your MySQL storage engine.
224
258
More information can be found at:
225
260
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
262
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
228
266
import datetime, inspect, re, sys
1331
1369
return ', '.join(t._compiler_dispatch(self, asfrom=True, **kw)
1332
1370
for t in [from_table] + list(extra_froms))
1334
def update_from_clause(self, update_stmt, from_table, extra_froms, **kw):
1372
def update_from_clause(self, update_stmt, from_table,
1373
extra_froms, from_hints, **kw):
1421
1460
table_opts.append(joiner.join((opt, arg)))
1422
1461
return ' '.join(table_opts)
1424
1464
def visit_create_index(self, create):
1425
1465
index = create.element
1426
1466
preparer = self.preparer
1467
table = preparer.format_table(index.table)
1468
columns = [preparer.quote(c.name, c.quote) for c in index.columns]
1469
name = preparer.quote(
1470
self._index_identifier(index.name),
1427
1473
text = "CREATE "
1428
1474
if index.unique:
1429
1475
text += "UNIQUE "
1430
text += "INDEX %s ON %s " \
1431
% (preparer.quote(self._index_identifier(index.name),
1432
index.quote),preparer.format_table(index.table))
1476
text += "INDEX %s ON %s " % (name, table)
1478
columns = ', '.join(columns)
1433
1479
if 'mysql_length' in index.kwargs:
1434
1480
length = index.kwargs['mysql_length']
1437
if length is not None:
1439
% (', '.join(preparer.quote(c.name, c.quote)
1440
for c in index.columns), length)
1443
% (', '.join(preparer.quote(c.name, c.quote)
1444
for c in index.columns))
1481
text += "(%s(%d))" % (columns, length)
1483
text += "(%s)" % (columns)
1485
if 'mysql_using' in index.kwargs:
1486
using = index.kwargs['mysql_using']
1487
text += " USING %s" % (preparer.quote(using, index.quote))
1491
def visit_primary_key_constraint(self, constraint):
1492
text = super(MySQLDDLCompiler, self).\
1493
visit_primary_key_constraint(constraint)
1494
if "mysql_using" in constraint.kwargs:
1495
using = constraint.kwargs['mysql_using']
1496
text += " USING %s" % (
1497
self.preparer.quote(using, constraint.quote))
1448
1500
def visit_drop_index(self, drop):
1449
1501
index = drop.element
1451
1503
return "\nDROP INDEX %s ON %s" % \
1452
(self.preparer.quote(self._index_identifier(index.name), index.quote),
1504
(self.preparer.quote(
1505
self._index_identifier(index.name), index.quote
1453
1507
self.preparer.format_table(index.table))
1455
1509
def visit_drop_constraint(self, drop):
1768
1822
_backslash_escapes = True
1769
1823
_server_ansiquotes = False
1771
def __init__(self, use_ansiquotes=None, **kwargs):
1825
def __init__(self, use_ansiquotes=None, isolation_level=None, **kwargs):
1772
1826
default.DefaultDialect.__init__(self, **kwargs)
1827
self.isolation_level = isolation_level
1829
def on_connect(self):
1830
if self.isolation_level is not None:
1832
self.set_isolation_level(conn, self.isolation_level)
1837
_isolation_lookup = set(['SERIALIZABLE',
1838
'READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ'])
1840
def set_isolation_level(self, connection, level):
1841
level = level.replace('_', ' ')
1842
if level not in self._isolation_lookup:
1843
raise exc.ArgumentError(
1844
"Invalid value '%s' for isolation_level. "
1845
"Valid isolation levels for %s are %s" %
1846
(level, self.name, ", ".join(self._isolation_lookup))
1848
cursor = connection.cursor()
1849
cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL %s" % level)
1850
cursor.execute("COMMIT")
1853
def get_isolation_level(self, connection):
1854
cursor = connection.cursor()
1855
cursor.execute('SELECT @@tx_isolation')
1856
val = cursor.fetchone()[0]
1858
return val.upper().replace("-", " ")
1774
1860
def do_commit(self, connection):
1775
1861
"""Execute a COMMIT."""