def _get_db(self):
con = self.connection
if con is not None:
con = con()
if con is None:
raise ProgrammingError("cursor closed")
return con
def query(self, sql_string, max_rows=1000):
""" Execute ``sql_string`` and return at most ``max_rows``.
"""
self._use_TM and self._register()
desc = None
rows = ()
for qs in filter(None, [q.strip() for q in sql_string.split('\0')]):
qtype = qs.split(None, 1)[0].upper()
if qtype == 'SELECT' and max_rows:
qs = '%s LIMIT %d' % (qs, max_rows)
db_results = self._query(qs)
if desc is not None and \
db_results and \
db_results.describe() != desc:
msg = 'Multiple select schema are not allowed.'
raise ProgrammingError(msg)
if db_results:
desc = db_results.describe()
rows = db_results.fetch_row(max_rows)
else:
desc = None
if qtype == 'CALL':
# For stored procedures, skip the status result
self.db.next_result()
if desc is None:
return (), ()
items = []
for info in desc:
items.append({'name': info[0],
'type': self.defs.get(info[1], 't'),
'width': info[2],
'null': info[6]})
return items, rows
class DB(TM):
"""This is the ZMySQLDA Database Connection Object."""
conv = conversions.copy()
conv[FIELD_TYPE.LONG] = int
conv[FIELD_TYPE.DATETIME] = DateTime_or_None
conv[FIELD_TYPE.DATE] = DateTime_or_None
conv[FIELD_TYPE.DECIMAL] = float
conv[FIELD_TYPE.BIT] = ord_or_None
del conv[FIELD_TYPE.TIME]
_sort_key = TM._sort_key
def __init__(self, connection):
"""
Parse the connection string.
Initiate a trial connection with the database to check
transactionality once instead of once per DB instance.
"""
self._connection = connection
self._parse_connection_string()
self._forceReconnection()
transactional = self.db.server_capabilities & CLIENT.TRANSACTIONS
if self._try_transactions == '-':
transactional = 0
elif not transactional and self._try_transactions == '+':
raise NotSupportedError, "transactions not supported by this server"
self._transactions = transactional
self._use_TM = transactional or self._mysql_lock
def _parse_connection_string(self):
self._mysql_lock = self._try_transactions = None
self._kw_args = kwargs = {'conv': self.conv}
items = self._connection.split()
if not items:
return
if items[0] == "~":
kwargs['compress'] = True
del items[0]
if items[0][0] == "*":
self._mysql_lock = items.pop(0)[1:]
db = items.pop(0)
if '@' in db:
db, host = db.split('@', 1)
if os.path.isabs(host):
kwargs['unix_socket'] = host
else:
if host.startswith('['):
host, port = host[1:].split(']', 1)
if port.startswith(':'):
kwargs['port'] = int(port[1:])
elif ':' in host:
host, port = host.split(':', 1)
kwargs['port'] = int(port)
kwargs['host'] = host
if db:
if db[0] in '+-':
self._try_transactions = db[0]
db = db[1:]
if db:
kwargs['db'] = db
if items:
kwargs['user'] = items.pop(0)
if items:
kwargs['passwd'] = items.pop(0)
if items: # BBB
assert 'unix_socket' not in kwargs
warnings.warn(
"use '<db>@<unix_socket> ...' syntax instead",
DeprecationWarning)
kwargs['unix_socket'] = items.pop(0)
defs = {
FIELD_TYPE.CHAR: "i",
FIELD_TYPE.DATE: "d",
FIELD_TYPE.DATETIME: "d",
FIELD_TYPE.DECIMAL: "n",
FIELD_TYPE.DOUBLE: "n",
FIELD_TYPE.FLOAT: "n",
FIELD_TYPE.INT24: "i",
FIELD_TYPE.LONG: "i",
FIELD_TYPE.LONGLONG: "l",
FIELD_TYPE.SHORT: "i",
FIELD_TYPE.TIMESTAMP: "d",
FIELD_TYPE.TINY: "i",
FIELD_TYPE.YEAR: "i",
}
_p_oid = _p_changed = _registered = None
def __del__(self):
self.db.close()
def _forceReconnection(self):
self.db = MySQLdb.connect(**self._kw_args)
def tables(self, rdb=0, _care=('TABLE', 'VIEW')):
"""Returns a list of tables in the current database."""
r = []
a = r.append
result = self._query("SHOW TABLES")
row = result.fetch_row(1)
while row:
a({'TABLE_NAME': row[0][0], 'TABLE_TYPE': 'TABLE'})
row = result.fetch_row(1)
return r
def columns(self, table_name):
"""Returns a list of column descriptions for 'table_name'."""
try:
c = self._query('SHOW COLUMNS FROM %s' % table_name)
except Exception:
return ()
from string import join
r = []
for Field, Type, Null, Key, Default, Extra in c.fetch_row(0):
info = {}
field_default = Default and "DEFAULT %s" % Default or ''
if Default: info['Default'] = Default
if '(' in Type:
end = Type.rfind(')')
short_type, size = Type[:end].split('(', 1)
if short_type not in ('set', 'enum'):
if ',' in size:
info['Scale'], info['Precision'] = \
map(int, size.split(',', 1))
else:
info['Scale'] = int(size)
else:
short_type = Type
if short_type in field_icons:
info['Icon'] = short_type
else:
info['Icon'] = icon_xlate.get(short_type, "what")
info['Name'] = Field
info['Type'] = type_xlate.get(short_type, 'string')
info['Extra'] = Extra,
info['Description'] = join([
Type, field_default, Extra or '',
key_types.get(Key, Key or ''), Null != 'YES' and 'NOT NULL'
or ''
]),
info['Nullable'] = Null == 'YES'
if Key:
info['Index'] = 1
if Key == 'PRI':
info['PrimaryKey'] = 1
info['Unique'] = 1
elif Key == 'UNI':
info['Unique'] = 1
r.append(info)
return r
def _query(self, query, allow_reconnect=False):
"""
Send a query to MySQL server.
It reconnects automatically if needed and the following conditions are
met:
- It has not just tried to reconnect (ie, this function will not
attempt to connect twice per call).
- This connection is not transactional and has set not MySQL locks,
because they are bound to the connection. This check can be
overridden by passing allow_reconnect with True value.
"""
try:
self.db.query(query)
except OperationalError, m:
if m[0] in query_syntax_error:
raise OperationalError(m[0], '%s: %s' % (m[1], query))
if m[0] in lock_error:
raise ConflictError('%s: %s: %s' % (m[0], m[1], query))
if not allow_reconnect and self._use_TM or \
m[0] not in hosed_connection:
LOG('ZMySQLDA', ERROR, 'query failed: %s' % (query, ))
raise
# Hm. maybe the db is hosed. Let's restart it.
self._forceReconnection()
self.db.query(query)
except ProgrammingError, exception:
LOG('ZMySQLDA', ERROR, 'query failed: %s' % (query, ))
# XXX sometimes, after a programming error, the database object
# gets fully broken and non-functional. So recover it by
# recreation.
self._forceReconnection()
if exception[0] == ER.PARSE_ERROR:
# You have an error in your SQL syntax
# Replace MySQL brain dead error message with a more meaningful
# one. (MySQL only reports the SQL query *from* the error place,
# which strips important contextual information).
error_text = exception[1]
prefix, suffix = error_text.split("'", 1)
if prefix == "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ":
sql, suffix = suffix.rsplit("'", 1)
try:
line_number = int(suffix.rsplit(' ', 1)[-1])
except TypeError:
pass
else:
reference_sql = query
split_reference_sql = reference_sql.split('\n')
candidate_sql = '\n'.join(
split_reference_sql[line_number - 1:])
error_position = len(reference_sql) - len(
candidate_sql) + candidate_sql.find(sql)
if error_position > -1:
raise ProgrammingError(
exception[0], "%s '%s' HERE '%s' %s" %
(prefix, reference_sql[:error_position],
reference_sql[error_position:], suffix))
raise exception