def get_dbconn(**dbconn_info): db_host = dbconn_info['db_host'] db_user = dbconn_info['db_user'] db_pass = dbconn_info['db_pass'] db_port = dbconn_info['db_port'] db_name = dbconn_info.get('db_name', None) db_charset = dbconn_info.get('db_charset', 'latin1') myconv = {FIELD_TYPE.TIMESTAMP: str, FIELD_TYPE.DATETIME: str} myconv = conversions.copy() del myconv[FIELD_TYPE.TIMESTAMP] del myconv[FIELD_TYPE.DATETIME] ### print "Connect to [%s:%d] using %s" % (db_host, db_port, db_charset) conn = None try: conn = MySQLdb.connect(host=db_host, user=db_user, passwd=db_pass, port=db_port, charset=db_charset, connect_timeout=5, conv=myconv) # use_unicode=False, except MySQLdb.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit(-1)
def connect(self, host, user, passwd, db, **kwargs): conn_params = dict(host=host, user=user, db=db, init_command='set names utf8', **kwargs) if passwd: conn_params['passwd'] = passwd conv = conversions.copy() conv.update({ FIELD_TYPE.TIMESTAMP: None, FIELD_TYPE.DATETIME: None, FIELD_TYPE.TIME: None, FIELD_TYPE.DATE: None, }) conn_params['conv'] = conv conn = connection(**conn_params) if not conn: raise DatabaseError('can not connect to database: %s %s %s' % (host, user, db)) cursor = conn.cursor() #cursor.execute('set sort_buffer_size=2000000') cursor = CursorWrapper(cursor, self) return cursor
def get_db_conn(**db_conn_info): db_host = db_conn_info['db_host'] db_user = db_conn_info['db_user'] db_pass = db_conn_info['db_pass'] db_port = db_conn_info['db_port'] db_name = db_conn_info.get('db_name', None) db_charset = db_conn_info.get('db_charset', 'utf8') # 注意数据库里日期0000 - 00 - 00 # 使用MySQLdb取出后为None,所以连接时使用conv重写了字段映射类型(当做字符串) myconv = {FIELD_TYPE.TIMESTAMP: str, FIELD_TYPE.DATETIME: str} myconv = conversions.copy() del myconv[FIELD_TYPE.TIMESTAMP] del myconv[FIELD_TYPE.DATETIME] print "Connect to [%s:%d] using %s" % (db_host, db_port, db_charset) try: conn = MySQLdb.connect(host=db_host, user=db_user, passwd=db_pass, port=db_port, charset=db_charset, connect_timeout=5, conv=myconv) except MySQLdb.Error as e: print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit(-1) if db_name is not None: conn.select_db(db_name) # 设置binlog format 为statement格式 set_binlog_format(conn) return conn
def CONV(cls): from MySQLdb.constants import FIELD_TYPE from MySQLdb.converters import conversions c = conversions.copy() c[FIELD_TYPE.DATE] = cls.Util.Date.from_db c[FIELD_TYPE.DATETIME] = cls.Util.DateTime.from_db c[FIELD_TYPE.TIMESTAMP] = cls.Util.DateTime.from_db return c
def load_environment(global_conf, app_conf): """Configure the Pylons environment via the ``pylons.config`` object """ config = PylonsConfig() # Pylons paths root = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) # setup themes template_paths = [os.path.join(root, 'templates')] themesbase = app_conf.get('baruwa.themes.base', None) if themesbase and os.path.isabs(themesbase): templatedir = os.path.join(themesbase, 'templates') if os.path.isdir(templatedir): template_paths.append(templatedir) paths = dict(root=root, controllers=os.path.join(root, 'controllers'), static_files=os.path.join(root, 'public'), templates=template_paths) # Initialize config with the basic options config.init_app(global_conf, app_conf, package='baruwa', paths=paths) config['routes.map'] = make_map(config) config['pylons.app_globals'] = app_globals.Globals(config) config['pylons.h'] = baruwa.lib.helpers # Setup cache object as early as possible import pylons pylons.cache._push_object(config['pylons.app_globals'].cache) # Create the Mako TemplateLookup, with the default auto-escaping config['pylons.app_globals'].mako_lookup = TemplateLookup( directories=paths['templates'], error_handler=handle_mako_error, module_directory=os.path.join(app_conf['cache_dir'], 'templates'), input_encoding='utf-8', default_filters=['escape'], imports=['from webhelpers.html import escape']) # Setup the SQLAlchemy database engine surl = config['sqlalchemy.url'] if surl.startswith('mysql'): conv = conversions.copy() conv[246] = float engine = create_engine(surl, pool_recycle=1800, connect_args=dict(conv=conv)) else: engine = engine_from_config(config, 'sqlalchemy.', poolclass=NullPool) init_model(engine) # CONFIGURATION OPTIONS HERE (note: all config options will override # any Pylons config options) return config
def Connect(host, database, user, password): ## fix conversion. datetime as str and not datetime object conv = conversions.copy() conv[12] = conv_date_to_timestamp return MySQLdb.connect(host=host, db=database, user=user, passwd=password, conv=conv, cursorclass=MySQLdb.cursors.SSCursor, charset='utf8', use_unicode=True)
def getConnection(self): ''' ''' my_conv = mysqlconversions.copy() dbConn = MySQLdb.connect(conv=my_conv, host=self.config.db["host"], user=self.config.db["user"], passwd=self.config.db["password"], db=self.config.db["database"]) return dbConn
def __init__(self): self.dbconn = None self.cursor = None self.plain_cursor = None self.escape_string = self.escape_fake self.connection_data = {} self.conversion_dict = conversions.copy() self.conversion_dict[FIELD_TYPE.DECIMAL] = int self.conversion_dict[FIELD_TYPE.LONG] = int self.conversion_dict[FIELD_TYPE.LONGLONG] = int self.conversion_dict[FIELD_TYPE.FLOAT] = float self.conversion_dict[FIELD_TYPE.NEWDECIMAL] = float
def _init_conversions(self): """Register our type conversions.""" self.m_conversions = conversions.copy() self.m_conversions[FIELD_TYPE.BLOB] = [(FLAG.BINARY, create_buffer), (None, None)] self.m_conversions[FIELD_TYPE.DATETIME] = pytimes.DateTime_or_None self.m_conversions[FIELD_TYPE.DATE] = pytimes.Date_or_None self.m_conversions[FIELD_TYPE.TIME] = pytimes.Time_or_None self.m_conversions[FIELD_TYPE.TIMESTAMP] = create_datetime self.m_conversions[datetime.date] = mysql_stringify self.m_conversions[datetime.time] = mysql_stringify self.m_conversions[datetime.datetime] = mysql_stringify self.m_conversions[datetime.timedelta] = mysql_stringify
def __init__(self): self.dbconn = None self.cursor = None self.plain_cursor = None self.escape_string = self.escape_fake self.connection_data = {} self.mysql = MySQLdb self.mysql_exceptions = _mysql_exceptions self.FIELD_TYPE = FIELD_TYPE self.conversion_dict = conversions.copy() self.conversion_dict[self.FIELD_TYPE.DECIMAL] = int self.conversion_dict[self.FIELD_TYPE.LONG] = int self.conversion_dict[self.FIELD_TYPE.FLOAT] = float self.conversion_dict[self.FIELD_TYPE.NEWDECIMAL] = float
def initialize(cfg): global store conv=conversions.copy() conv[246]=float if cfg.get('database', 'dbtype').lower() == 'mysql': store=ReconnectingConnectionPool( 'MySQLdb', cfg.get('database', 'dbhost'), cfg.get('database','dbuser'), cfg.get('database','dbpass'), cfg.get('database','dmdbname'), cp_max=20, cp_reconnect=True, conv=conv) else: log.error("No supported dbtype") return Registry.DBPOOL = store Registry.dmcfg = cfg Registry.Transaction = Transaction()
def __init__(self, host, port, user, passwd, db, charset): self.conn_params = conn_params = dict( host=host, user=user, port=port, db=db, init_command='set names %s'%charset, ) if passwd : conn_params['passwd'] = passwd conv = conversions.copy() conv.update({ FIELD_TYPE.TIMESTAMP: None, FIELD_TYPE.DATETIME: None, FIELD_TYPE.TIME: None, FIELD_TYPE.DATE: None, }) conn_params['conv'] = conv conn_params['maxusage'] = False self._cursor = None
def connect(self, host, user, passwd, db, **kwargs): conn_params = dict(host=host, user=user, db=db, init_command="set names utf8", **kwargs) if passwd: conn_params["passwd"] = passwd conv = conversions.copy() conv.update( {FIELD_TYPE.TIMESTAMP: None, FIELD_TYPE.DATETIME: None, FIELD_TYPE.TIME: None, FIELD_TYPE.DATE: None} ) conn_params["conv"] = conv conn = connection(**conn_params) if not conn: raise DatabaseError("can not connect to database: %s %s %s" % (host, user, db)) cursor = conn.cursor() # cursor.execute('set sort_buffer_size=2000000') cursor = CursorWrapper(cursor, self) return cursor
def __init__(self, host, port, user, passwd, db, charset): self.conn_params = conn_params = dict( host=host, user=user, port=port, db=db, init_command='set names %s' % charset, ) if passwd: conn_params['passwd'] = passwd conv = conversions.copy() conv.update({ FIELD_TYPE.TIMESTAMP: None, FIELD_TYPE.DATETIME: None, FIELD_TYPE.TIME: None, FIELD_TYPE.DATE: None, }) conn_params['conv'] = conv conn_params['maxusage'] = False self._cursor = None
class DB(TM): Database_Error = _mysql.Error defs = { FIELD_TYPE.CHAR: 'i', FIELD_TYPE.DATE: 'd', FIELD_TYPE.DATETIME: 'd', FIELD_TYPE.DECIMAL: 'n', FIELD_TYPE.NEWDECIMAL: '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', } 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.NEWDECIMAL] = float del conv[FIELD_TYPE.TIME] _p_oid = _p_changed = None _sort_key = '1' _registered = False _finalize = False unicode_charset = 'utf8' # hardcoded for now def __init__(self, connection=None, kw_args=None, use_TM=None, mysql_lock=None, transactions=None): self.connection = connection # backwards compat self._kw_args = kw_args self._mysql_lock = mysql_lock self._use_TM = use_TM self._transactions = transactions self._forceReconnection() def close(self): """ Close connection and dereference. """ if getattr(self, 'db', None): self.db.close() self.db = None __del__ = close def _forceReconnection(self): """ (Re)Connect to database. """ try: # try to clean up first self.db.close() except Exception: pass self.db = MySQLdb.connect(**self._kw_args) # Newer mysqldb requires ping argument to attmept a reconnect. # This setting is persistent, so only needed once per connection. self.db.ping(True) @classmethod def _parse_connection_string(cls, connection, use_unicode=False, charset=None, timeout=None): """ Done as a class method to both allow access to class attribute conv (conversion) settings while allowing for wrapping pool class use of this method. The former is important to allow for subclasses to override the conv settings while the latter is important so the connection string doesn't have to be parsed for each instance in the pool. """ kw_args = {'conv': cls.conv} flags = {'kw_args': kw_args, 'connection': connection} kw_args['use_unicode'] = use_unicode if use_unicode: kw_args['charset'] = cls.unicode_charset if charset: kw_args['charset'] = charset items = connection.split() flags['use_TM'] = None if _mysql.get_client_info()[0] >= '5': kw_args['client_flag'] = CLIENT.MULTI_STATEMENTS if items: lockreq, items = items[0], items[1:] if lockreq[0] == '*': flags['mysql_lock'] = lockreq[1:] db_host, items = items[0], items[1:] flags['use_TM'] = True # redundant. eliminate? else: flags['mysql_lock'] = None db_host = lockreq if '@' in db_host: db, host = db_host.split('@', 1) kw_args['db'] = db if ':' in host: host, port = host.split(':', 1) kw_args['port'] = int(port) kw_args['host'] = host else: kw_args['db'] = db_host if kw_args['db'] and kw_args['db'][0] in ('+', '-'): flags['try_transactions'] = kw_args['db'][0] kw_args['db'] = kw_args['db'][1:] else: flags['try_transactions'] = None if not kw_args['db']: del kw_args['db'] if items: kw_args['user'], items = items[0], items[1:] if items: kw_args['passwd'], items = items[0], items[1:] if items: kw_args['unix_socket'], items = items[0], items[1:] if timeout: kw_args['connect_timeout'] = timeout return flags def tables(self, rdb=0, _care=('TABLE', 'VIEW')): """ Returns list of tables. """ t_list = [] db_result = self._query('SHOW TABLE STATUS') charset = self._kw_args.get('charset', 'UTF-8') if charset.startswith('utf8'): charset = 'UTF-8' for row in db_result.fetch_row(0): variables = {} for index, key in ((0, 't_name'), (1, 't_engine'), (4, 't_size'), (14, 't_cs')): value = row[index] if isinstance(value, six.binary_type): value = value.decode(charset) variables[key] = value description = ('%(t_engine)s, %(t_size)s rows, ' 'character set/collation %(t_cs)s' % variables) t_list.append({'table_name': variables['t_name'], 'table_type': 'table', 'description': description}) return t_list def columns(self, table_name): """ Returns list of column descriptions for ``table_name``. """ c_list = [] try: # Field, Type, Null, Key, Default, Extra db_result = self._query('SHOW COLUMNS FROM %s' % table_name) except ProgrammingError: # Table does not exist. Any other error should propagate. LOG.warning('columns query for non-existing table %s' % table_name) return () charset = self._kw_args.get('charset', 'UTF-8') if charset.startswith('utf8'): charset = 'UTF-8' for Field, Type, Null, Key, Default, Extra in db_result.fetch_row(0): if six.PY3: # Force-decoding to make the Browse ZMI tab work across # all supported Python versions if isinstance(Field, six.binary_type): Field = Field.decode(charset) if isinstance(Type, six.binary_type): Type = Type.decode(charset) if isinstance(Null, six.binary_type): Null = Null.decode(charset) if isinstance(Key, six.binary_type): Key = Key.decode(charset) if isinstance(Default, six.binary_type): Default = Default.decode(charset) if isinstance(Extra, six.binary_type): Extra = Extra.decode(charset) info = {'name': Field, 'extra': (Extra,), 'nullable': (Null == 'YES') and 1 or 0} if Default is not None: info['default'] = Default field_default = "DEFAULT '%s'" % Default else: field_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['type'] = short_type nul = (Null == 'NO' and 'NOT NULL' or '') info['description'] = ' '.join([Type, field_default, Extra or '', key_types.get(Key, Key or ''), nul]) if Key: info['index'] = True info['key'] = Key if Key == 'PRI': info['primary_key'] = True info['unique'] = True elif Key == 'UNI': info['unique'] = True c_list.append(info) return c_list def variables(self): """ Return dictionary of current mysql variable/values. """ # variable_name, value variables = self._query('SHOW VARIABLES') return dict((name, value) for name, value in variables.fetch_row(0)) def _query(self, query, force_reconnect=False): """ Send a query to MySQL server. It reconnects automaticaly 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 conection is not transactionnal and has set no MySQL locks, because they are bound to the connection. This check can be overridden by passing force_reconnect with True value. """ try: self.db.query(query) except OperationalError as exc: if exc.args[0] in query_syntax_error: raise OperationalError(exc.args[0], '%s: %s' % (exc.args[1], query)) if not force_reconnect and \ (self._mysql_lock or self._transactions) or \ exc.args[0] not in hosed_connection: if len(query) > 2000: msg = '%s... (truncated at 2000 chars)' % query[:2000] else: msg = query LOG.warning('query failed:\n%s' % msg) raise # Hm. maybe the db is hosed. Let's restart it. if exc.args[0] in hosed_connection: msg = '%s Forcing a reconnect.' % hosed_connection[exc.args[0]] LOG.error(msg) self._forceReconnection() self.db.query(query) except ProgrammingError as exc: if exc.args[0] in hosed_connection: self._forceReconnection() msg = '%s Forcing a reconnect.' % hosed_connection[exc.args[0]] LOG.error(msg) else: if len(query) > 2000: msg = '%s... (truncated at 2000 chars)' % query[:2000] else: msg = query LOG.warning('query failed:\n%s' % msg) raise return self.db.store_result() 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 def string_literal(self, sql_str): """ Called from zope to quote/escape strings for inclusion in a query. """ return self.db.string_literal(sql_str) def unicode_literal(self, sql_str): """ Similar to string_literal but encodes it first. """ return self.db.unicode_literal(sql_str) # Zope 2-phase transaction handling methods def _register(self): """ Override to replace transaction register() call with join(). The primary reason for this is to eliminate issues in both the super's _register() and transaction register(). The former has a bare except that hides useful errors. The latter deals poorly with exceptions raised from the join due to state modifications made before the join attempt. They also both (for our purposes) needlessly add wrapper objects around self, resulting in unnecessary overhead. """ if not self._registered: try: transaction.get().join(self) except TransactionFailedError: msg = 'database connection failed to join transaction.' LOG.error(msg) except ValueError: msg = 'database connection failed to join transaction.' LOG.error(msg, exc_info=True) raise else: self._begin() self._registered = True self._finalize = False def _begin(self, *ignored): """ Begin a transaction, if transactions are enabled. Also called from _register() upon first query. """ try: self._transaction_begun = True self.db.ping() if self._transactions: self._query('BEGIN') if self._mysql_lock: self._query("SELECT GET_LOCK('%s',0)" % self._mysql_lock) except Exception: LOG.error('exception during _begin', exc_info=True) raise ConflictError def _finish(self, *ignored): """ Commit a transaction, if transactions are enabled and the Zope transaction has committed successfully. """ if not self._transaction_begun: return self._transaction_begun = False try: if self._mysql_lock: self._query("SELECT RELEASE_LOCK('%s')" % self._mysql_lock) if self._transactions: self._query('COMMIT') except Exception: LOG.error('exception during _finish', exc_info=True) raise ConflictError def _abort(self, *ignored): """ Roll back the database transaction if the Zope transaction has been aborted, usually due to a transaction error. """ if not self._transaction_begun: return self._transaction_begun = False if self._mysql_lock: self._query("SELECT RELEASE_LOCK('%s')" % self._mysql_lock) if self._transactions: self._query('ROLLBACK') else: LOG.error('aborting when non-transactional') def _mysql_version(self): """ Return mysql server version. """ if getattr(self, '_version', None) is None: self._version = self.variables().get('version') return self._version def savepoint(self): """ Basic savepoint support. """ if not self._transaction_begun: LOG.error('Savepoint used outside of transaction.') raise AttributeError return _SavePoint(self)
""" MySQL database backend for Django. Requires mysqlclient: https://pypi.python.org/pypi/mysqlclient/ MySQLdb is supported for Python 2 only: http://sourceforge.net/projects/mysql-python """ from __future__ import unicode_literals import datetime import re import sys import warnings from django.conf import settings from django.db import utils from django.db.backends import utils as backend_utils from django.db.backends.base.base import BaseDatabaseWrapper from django.utils import six, timezone from django.utils.deprecation import RemovedInDjango20Warning from django.utils.encoding import force_str from django.utils.functional import cached_property from django.utils.safestring import SafeBytes, SafeText try: import MySQLdb as Database except ImportError as e: from django.core.exceptions import ImproperlyConfigured raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e) from MySQLdb.constants import CLIENT, FIELD_TYPE # isort:skip from MySQLdb.converters import Thing2Literal, conversions # isort:skip # Some of these import MySQLdb, so import them after checking if it's installed. from .client import DatabaseClient # isort:skip from .creation import DatabaseCreation # isort:skip from .features import DatabaseFeatures # isort:skip
class DB(TM): """This is the ZMySQLDA Database Connection Object.""" conv = conversions.copy() conv[FIELD_TYPE.LONG] = int conv[FIELD_TYPE.DATETIME] = DATETIME_to_DateTime_or_None conv[FIELD_TYPE.DATE] = DATE_to_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 db = None 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): if self.db is not None: self.db.close() def _forceReconnection(self): db = self.db if db is not None: try: db.close() except Exception as exception: # XXX: MySQLdb seems to think it's smart to use such general SQL # exception for such a specific case error, rather than subclassing # it. In an attempt to be future-proof (if it ever raises the same # exception for unrelated reasons, like errors which would happen in # mysql_close), check also the exception message. # Anyway, this is just to avoid useless log spamming, so it's not a # huge deal either way. if not isinstance( exception, ProgrammingError, ) or exception.message != 'closing a closed connection': LOG( 'ZMySQLDA.db', WARNING, 'Failed to close pre-existing connection, discarding it', error=True, ) self.db = MySQLdb.connect(**self._kw_args) self._query("SET time_zone='+00:00'") 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'] = \ [int(x) for x in 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 as 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 m[0] in query_timeout_error: raise TimeoutReachedError('%s: %s: %s' % (m[0], m[1], query)) if (allow_reconnect or not self._use_TM) and \ m[0] in hosed_connection: self._forceReconnection() self.db.query(query) else: LOG('ZMySQLDA', ERROR, 'query failed: %s' % (query, )) raise except ProgrammingError: LOG('ZMySQLDA', ERROR, 'query failed: %s' % (query, )) raise try: return self.db.store_result() except OperationalError as m: if m[0] in query_timeout_error: raise TimeoutReachedError('%s: %s: %s' % (m[0], m[1], query)) else: raise def query(self, query_string, max_rows=1000): """Execute 'query_string' and return at most 'max_rows'.""" self._use_TM and self._register() desc = None if not isinstance(query_string, bytes): query_string = str2bytes(query_string) # XXX deal with a typical mistake that the user appends # an unnecessary and rather harmful semicolon at the end. # Unfortunately, MySQLdb does not want to be graceful. if query_string[-1:] == b';': query_string = query_string[:-1] for qs in query_string.split(b'\0'): qs = qs.strip() if qs: select_match = match_select(qs) if select_match: query_timeout = getTimeLeft() if query_timeout is not None: statement, select = select_match.groups() if statement: statement += b", max_statement_time=%f" % query_timeout else: statement = b"max_statement_time=%f" % query_timeout qs = b"SET STATEMENT %s FOR SELECT %s" % (statement, select) if max_rows: qs = b"%s LIMIT %d" % (qs, max_rows) c = self._query(qs) if c: if desc is not None is not c.describe(): raise Exception( 'Multiple select schema are not allowed') desc = c.describe() result = c.fetch_row(max_rows) if desc is None: return (), () get_def = self.defs.get items = [{ 'name': d[0], 'type': get_def(d[1], "t"), 'width': d[2], 'null': d[6] } for d in desc] return items, result def string_literal(self, s): return self.db.string_literal(s) def _begin(self, *ignored): """Begin a transaction (when TM is enabled).""" try: self._transaction_begun = True if self._transactions: self._query("BEGIN", allow_reconnect=True) if self._mysql_lock: self._query("SELECT GET_LOCK('%s',0)" % self._mysql_lock, allow_reconnect=not self._transactions) except: LOG('ZMySQLDA', ERROR, "exception during _begin", error=True) raise def tpc_vote(self, *ignored): # Raise if a disconnection is detected, to avoid detecting this later self._query("SELECT 1") return TM.tpc_vote(self, *ignored) def _finish(self, *ignored): """Commit a transaction (when TM is enabled).""" if not self._transaction_begun: return self._transaction_begun = False if self._mysql_lock: self._query("SELECT RELEASE_LOCK('%s')" % self._mysql_lock) if self._transactions: self._query("COMMIT") def _abort(self, *ignored): """Rollback a transaction (when TM is enabled).""" if not self._transaction_begun: return self._transaction_begun = False # Hide hosed connection exceptions: # - if the disconnection caused the abort, we would then hide the # original error traceback # - if the disconnection happened during abort, then we cannot recover # anyway as the transaction is bound to its connection anyway # Note: in any case, we expect server to notice the disconnection and # trigger an abort on its side. try: if self._mysql_lock: self._query("SELECT RELEASE_LOCK('%s')" % self._mysql_lock) if self._transactions: self._query("ROLLBACK") else: LOG('ZMySQLDA', ERROR, "aborting when non-transactional") except OperationalError as m: LOG('ZMySQLDA', ERROR, "exception during _abort", error=True) if m[0] not in hosed_connection: raise def getMaxAllowedPacket(self): # minus 2-bytes overhead from mysql library return self._query("SELECT @@max_allowed_packet-2").fetch_row()[0][0] @contextmanager def lock(self): """Lock for the connected DB""" db = self._kw_args.get('db', '') lock = "SELECT GET_LOCK('ZMySQLDA(%s)', 5)" % db unlock = "SELECT RELEASE_LOCK('ZMySQLDA(%s)')" % db try: while not self.query(lock, 0)[1][0][0]: pass yield finally: self.query(unlock, 0) def _getTableSchema( self, name, create_lstrip=re.compile(r"[^(]+\(\s*").sub, create_rmatch=re.compile(r"(.*\S)\s*\)\s*(.*)$", re.DOTALL).match, create_split=re.compile(r",\n\s*").split, column_match=re.compile(r"`(\w+)`\s+(.+)").match, ): (_, schema), = self.query("SHOW CREATE TABLE " + name)[1] column_list = [] key_set = set() m = create_rmatch(create_lstrip("", schema, 1)) for spec in create_split(m.group(1)): if "KEY" in spec: key_set.add(spec) else: column_list.append(column_match(spec).groups()) return column_list, key_set, m.group(2) _create_search = re.compile(r'\bCREATE\s+TABLE\s+(`?)(\w+)\1\s+', re.I).search _key_search = re.compile(r'\bKEY\s+(`[^`]+`)\s+(.+)').search def upgradeSchema(self, create_sql, create_if_not_exists=False, initialize=None, src__=0): m = self._create_search(create_sql) if m is None: return name = m.group(2) # Lock automatically unless src__ is True, because the caller may have # already done it (in case that it plans to execute the returned query). with (nested if src__ else self.lock)(): try: old_list, old_set, old_default = self._getTableSchema("`%s`" % name) except ProgrammingError as e: if e[0] != ER.NO_SUCH_TABLE or not create_if_not_exists: raise if not src__: self.query(create_sql) return create_sql name_new = '`_%s_new`' % name self.query('CREATE TEMPORARY TABLE %s %s' % (name_new, create_sql[m.end():])) try: new_list, new_set, new_default = self._getTableSchema(name_new) finally: self.query("DROP TEMPORARY TABLE " + name_new) src = [] q = src.append if old_default != new_default: q(new_default) old_dict = {} new = {column[0] for column in new_list} pos = 0 for column, spec in old_list: if column in new: old_dict[column] = pos, spec pos += 1 else: q("DROP COLUMN `%s`" % column) for key in old_set - new_set: if "PRIMARY" in key: q("DROP PRIMARY KEY") else: q("DROP KEY " + self._key_search(key).group(1)) column_list = [] pos = 0 where = "FIRST" for column, spec in new_list: try: old = old_dict[column] except KeyError: q("ADD COLUMN `%s` %s %s" % (column, spec, where)) column_list.append(column) else: if old != (pos, spec): q("MODIFY COLUMN `%s` %s %s" % (column, spec, where)) if old[1] != spec: column_list.append(column) pos += 1 where = "AFTER `%s`" % column for key in new_set - old_set: q("ADD " + key) if src: src = "ALTER TABLE `%s`%s" % (name, ','.join("\n " + q for q in src)) if not src__: self.query(src) if column_list and initialize and self.query( "SELECT 1 FROM `%s`" % name, 1)[1]: initialize(self, column_list) return src
:copyright: (c) 2010 Amit Mendapara. :license: BSD, see LICENSE for more details. """ import MySQLdb as dbapi from MySQLdb.converters import conversions from MySQLdb.constants import FIELD_TYPE from kalapy.db.engines import utils from kalapy.db.engines.relational import RelationalDatabase __all__ = ('DatabaseError', 'IntegrityError', 'Database') DatabaseError = dbapi.DatabaseError IntegrityError = dbapi.IntegrityError CONV = conversions.copy() CONV.update({ FIELD_TYPE.DECIMAL: utils.decimal_to_python, }) class Database(RelationalDatabase): data_types = { "key": "INTEGER AUTO_INCREMENT PRIMARY KEY", "reference": "INTEGER", "char": "VARCHAR(%(size)s)", "text": "LONGTEXT", "integer": "INTEGER", "float": "DOUBLE", "decimal": "DECIMAL(%(max_digits)s, %(decimal_places)s)",
class DB(TM): """This is the ZMySQLDA Database Connection Object.""" conv=conversions.copy() conv[FIELD_TYPE.LONG] = int conv[FIELD_TYPE.DATETIME] = DATETIME_to_DateTime_or_None conv[FIELD_TYPE.DATE] = DATE_to_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 db = None 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): db = self.db if db is not None: try: db.close() except Exception as exception: # XXX: MySQLdb seems to think it's smart to use such general SQL # exception for such a specific case error, rather than subclassing # it. In an attempt to be future-proof (if it ever raises the same # exception for unrelated reasons, like errors which would happen in # mysql_close), check also the exception message. # Anyway, this is just to avoid useless log spamming, so it's not a # huge deal either way. if not isinstance( exception, ProgrammingError, ) or exception.message != 'closing a closed connection': LOG( 'ZMySQLDA.db', WARNING, 'Failed to close pre-existing connection, discarding it', error=True, ) self.db = MySQLdb.connect(**self._kw_args) self._query("SET time_zone='+00:00'") 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 m[0] in query_timeout_error: raise TimeoutReachedError('%s: %s: %s' % (m[0], m[1], query)) if (allow_reconnect or not self._use_TM) and \ m[0] in hosed_connection: self._forceReconnection() self.db.query(query) else: LOG('ZMySQLDA', ERROR, 'query failed: %s' % (query,)) raise except ProgrammingError: LOG('ZMySQLDA', ERROR, 'query failed: %s' % (query,)) raise
def connect_by_uri(uri): """General URI syntax: mysql://user:passwd@host:port/db?opt1=val1&opt2=val2&... where opt_n is in the list of options supported by MySQLdb: host,user,passwd,db,compress,connect_timeout,read_default_file, read_default_group,unix_socket,port NOTE: the authority and the path parts of the URI have precedence over the query part, if an argument is given in both. conv,quote_conv,cursorclass are not (yet?) allowed as complex Python objects are needed, hard to transmit within an URI... See for description of options: http://dustman.net/andy/python/MySQLdb_obsolete/doc/MySQLdb-3.html#ss3.1 http://mysql-python.svn.sourceforge.net/viewvc/mysql-python/trunk/MySQLdb/doc/MySQLdb.txt?revision=438&view=markup&pathrev=438 """ puri = urisup.uri_help_split(uri) params = __dict_from_query(puri[QUERY]) if puri[AUTHORITY]: user, passwd, host, port = puri[AUTHORITY] if user: params['user'] = user if passwd: params['passwd'] = passwd if host: params['host'] = host if port: params['port'] = port if puri[PATH]: params['db'] = puri[PATH] if params['db'] and params['db'][0] == '/': params['db'] = params['db'][1:] __apply_types(params, __typemap) # The next affectation work around a bug in python-mysqldb which # happens when using an unicode charset: the conv parameter is # defaulted to the common dictionary MySQLdb.converters.conversions # when not explicitly given to the __init__() of # MySQLdb.connections.Connection, the _mysql module just store it in # the .converter member in the __init__() method of the base class # _mysql.connection, and later, back in the __init__() of # MySQLdb.connections.Connection, some children of .converter, which # are lists, are prepended by dynamically generated functions. The net # result is that every times a new Mysql connection is asked for with # no individualised conversion dictionary passed to the conv parameter, # a bunch of new functions and tuples are created, on which the process # will keep references forever, effectively leaking some memory as some # won't be used anymore after the termination of any connection. # This work around is believed to be effective because the only # references to the dynamically created conversion functions generated # by MySQLdb.connections will be in this instance-specific copy of # MySQLdb.converters.conversions. A unique reference to this copy will # be held by the connection instance, so when the latter is garbage # collected, the copied conversion dictionary is freed, and eventually # the now orphan tuples and generated functions are too. params['conv'] = CST_CONVERSIONS.copy() params['cursorclass'] = SSCursor return MySQLdb.connect(**params)
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
def connect(module=config.sqlModule): if module == "MySQLdb": import MySQLdb.cursors conv = None try: from MySQLdb.converters import conversions from MySQLdb.constants import FIELD_TYPE # Patch. conv = conversions.copy() conv[FIELD_TYPE.LONG] = int # Get rid of the longs. except: pass # Moist / MysqlDB2 already do this. if config.sqlSocket: if conv: return adbapi.ConnectionPool( module, host=config.sqlHost, unix_socket=config.sqlSocket, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=MySQLdb.cursors.DictCursor, conv=conv, ) else: return adbapi.ConnectionPool( module, host=config.sqlHost, unix_socket=config.sqlSocket, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=MySQLdb.cursors.DictCursor, ) else: if conv: return adbapi.ConnectionPool( module, host=config.sqlHost, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=MySQLdb.cursors.DictCursor, conv=conv, ) else: return adbapi.ConnectionPool( module, host=config.sqlHost, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=MySQLdb.cursors.DictCursor, ) elif module == "mysql-ctypes": import MySQLdb.cursors return adbapi.ConnectionPool( "MySQLdb", host=config.sqlHost, port=3306, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=MySQLdb.cursors.DictCursor, conv=conv, ) elif module == "oursql": try: import oursql except ImportError: print "Falling oursql back to MySQLdb" return connect("MySQLdb") from MySQLdb.constants import FIELD_TYPE from MySQLdb.converters import conversions # Patch. conv = conversions.copy() conv[FIELD_TYPE.LONG] = int if config.sqlSocket: return adbapi.ConnectionPool( module, host=config.sqlHost, unix_socket=config.sqlSocket, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, default_cursor=oursql.DictCursor, ) else: return adbapi.ConnectionPool( module, host=config.sqlHost, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, default_cursor=oursql.DictCursor, ) elif module == "pymysql": # This module is indentical, but uses a diffrent name try: import pymysql import pymysql.cursors except ImportError: print "Falling pymysql back to MySQLdb" return connect("MySQLdb") if config.sqlSocket: return adbapi.ConnectionPool( module, host=config.sqlHost, unix_socket=config.sqlSocket, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=pymysql.cursors.DictCursor, ) else: return adbapi.ConnectionPool( module, host=config.sqlHost, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=pymysql.cursors.DictCursor, ) elif module == "sqlite3": raise Exception("TODO: dictcursor for sqlite3 required!") import sqlite3 # Implode our little hack to allow both sqlite3 and mysql to work together! # This is a bit slower I guess, but it works :) def runQuery(self, *args, **kw): args = list(args) args[0] = ( args[0].replace("%s", "?").replace("%f", "?").replace("%d", "?") ) # String, float and digit support args = tuple(args) return self.runInteraction(self._runQuery, *args) adbapi.ConnectionPool.runQuery = runQuery return adbapi.ConnectionPool(module, config.sqlDatabase, isolation_level=None, check_same_thread=False) else: raise NameError("SQL module %s is invalid" % module)
def connect(module = config.sqlModule): if module == "MySQLdb": import MySQLdb.cursors conv = None try: from MySQLdb.converters import conversions from MySQLdb.constants import FIELD_TYPE # Patch. conv = conversions.copy() conv[FIELD_TYPE.LONG] = int # Get rid of the longs. except: pass # Moist / MysqlDB2 already do this. if config.sqlSocket: if conv: return adbapi.ConnectionPool(module, host=config.sqlHost, unix_socket=config.sqlSocket, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=MySQLdb.cursors.DictCursor, conv=conv) else: return adbapi.ConnectionPool(module, host=config.sqlHost, unix_socket=config.sqlSocket, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=MySQLdb.cursors.DictCursor) else: if conv: return adbapi.ConnectionPool(module, host=config.sqlHost, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=MySQLdb.cursors.DictCursor, conv=conv) else: return adbapi.ConnectionPool(module, host=config.sqlHost, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=MySQLdb.cursors.DictCursor) elif module == "mysql-ctypes": import MySQLdb.cursors return adbapi.ConnectionPool("MySQLdb", host=config.sqlHost, port=3306, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=MySQLdb.cursors.DictCursor, conv=conv) elif module == "oursql": try: import oursql except ImportError: print "Falling oursql back to MySQLdb" return connect("MySQLdb") from MySQLdb.constants import FIELD_TYPE from MySQLdb.converters import conversions # Patch. conv = conversions.copy() conv[FIELD_TYPE.LONG] = int if config.sqlSocket: return adbapi.ConnectionPool(module, host=config.sqlHost, unix_socket=config.sqlSocket, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, default_cursor=oursql.DictCursor) else: return adbapi.ConnectionPool(module, host=config.sqlHost, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, default_cursor=oursql.DictCursor) elif module == "pymysql": # This module is indentical, but uses a diffrent name try: import pymysql import pymysql.cursors except ImportError: print "Falling pymysql back to MySQLdb" return connect("MySQLdb") if config.sqlSocket: return adbapi.ConnectionPool(module, host=config.sqlHost, unix_socket=config.sqlSocket, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=pymysql.cursors.DictCursor) else: return adbapi.ConnectionPool(module, host=config.sqlHost, db=config.sqlDatabase, user=config.sqlUsername, passwd=config.sqlPassword, cp_min=config.sqlMinConnections, cp_max=config.sqlMaxConnections, cp_reconnect=True, cp_noisy=config.sqlDebug, cursorclass=pymysql.cursors.DictCursor) elif module == "sqlite3": raise Exception("TODO: dictcursor for sqlite3 required!") import sqlite3 # Implode our little hack to allow both sqlite3 and mysql to work together! # This is a bit slower I guess, but it works :) def runQuery(self, *args, **kw): args = list(args) args[0] = args[0].replace('%s', '?').replace('%f', '?').replace('%d', '?') # String, float and digit support args = tuple(args) return self.runInteraction(self._runQuery, *args) adbapi.ConnectionPool.runQuery = runQuery return adbapi.ConnectionPool(module, config.sqlDatabase, isolation_level=None, check_same_thread=False) else: raise NameError("SQL module %s is invalid" % module)
class DB(TM): Database_Connection = _mysql.connect Database_Error = _mysql.Error 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", } conv = conversions.copy() conv[FIELD_TYPE.LONG] = int_or_long conv[FIELD_TYPE.DATETIME] = DateTime_or_None conv[FIELD_TYPE.DATE] = DateTime_or_None conv[FIELD_TYPE.DECIMAL] = float del conv[FIELD_TYPE.TIME] _p_oid = _p_changed = _registered = None def __init__(self, connection): self.connection = connection self.kwargs = kwargs = self._parse_connection_string(connection) self.db = apply(self.Database_Connection, (), kwargs) self.transactions = self.db.server_capabilities & CLIENT.TRANSACTIONS if self._try_transactions == '-': self.transactions = 0 elif not self.transactions and self._try_transactions == '+': raise NotSupportedError, "transactions not supported by this server" def _parse_connection_string(self, connection): kwargs = {'conv': self.conv} items = split(connection) if not items: return kwargs db_host, items = items[0], items[1:] if '@' in db_host: db, host = split(db_host, '@', 1) kwargs['db'] = db if ':' in host: host, port = split(host, ':', 1) kwargs['port'] = int(port) kwargs['host'] = host else: kwargs['db'] = db_host if kwargs['db'][0] in ('+', '-'): self._try_transactions = kwargs['db'][0] kwargs['db'] = kwargs['db'][1:] else: self._try_transactions = None if not items: return kwargs kwargs['user'], items = items[0], items[1:] if not items: return kwargs kwargs['passwd'], items = items[0], items[1:] if not items: return kwargs kwargs['unix_socket'], items = items[0], items[1:] return kwargs def tables(self, rdb=0, _care=('TABLE', 'VIEW')): r = [] a = r.append self.db.query("SHOW TABLES") result = self.db.store_result() while 1: row = result.fetch_row(1) if not row: break a({'TABLE_NAME': row[0][0], 'TABLE_TYPE': 'TABLE'}) return r def columns(self, table_name): from string import join try: # Field, Type, Null, Key, Default, Extra self.db.query('SHOW COLUMNS FROM %s' % table_name) c = self.db.store_result() except: return () 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 = rfind(Type, ')') short_type, size = split(Type[:end], '(', 1) if short_type not in ('set', 'enum'): if ',' in size: info['Scale'], info['Precision'] = \ map(int, split(size,',',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') and 1 or 0 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_string, max_rows=1000): if self.transactions: self._register() desc = None result = () db = self.db try: for qs in filter(None, map(strip, split(query_string, '\0'))): qtype = upper(split(qs, None, 1)[0]) if qtype == "SELECT" and max_rows: qs = "%s LIMIT %d" % (qs, max_rows) r = 0 db.query(qs) c = db.store_result() if desc is not None: if c and (c.describe() != desc): raise 'Query Error', ( 'Multiple select schema are not allowed') if c: desc = c.describe() result = c.fetch_row(max_rows) else: desc = None except OperationalError, m: if m[0] not in hosed_connection: raise # Hm. maybe the db is hosed. Let's restart it. db = self.db = apply(self.Database_Connection, (), self.kwargs) return self.query(query_string, max_rows) if desc is None: return (), () items = [] func = items.append defs = self.defs for d in desc: item = { 'name': d[0], 'type': defs.get(d[1], "t"), 'width': d[2], 'null': d[6] } func(item) return items, result
# Raise exceptions for database warnings if DEBUG is on from django.conf import settings if settings.DEBUG: from warnings import filterwarnings filterwarnings("error", category=Database.Warning) DatabaseError = Database.DatabaseError IntegrityError = Database.IntegrityError # MySQLdb-1.2.1 returns TIME columns as timedelta -- they are more like # timedelta in terms of actual behavior as they are signed and include days -- # and Django expects time, so we still need to override that. We also need to # add special handling for SafeUnicode and SafeString as MySQLdb's type # checking is too tight to catch those (see Django ticket #6052). django_conversions = conversions.copy() django_conversions.update({ FIELD_TYPE.TIME: util.typecast_time, FIELD_TYPE.DECIMAL: util.typecast_decimal, FIELD_TYPE.NEWDECIMAL: util.typecast_decimal, }) # This should match the numerical portion of the version numbers (we can treat # versions like 5.0.24 and 5.0.24a as the same). Based on the list of version # at http://dev.mysql.com/doc/refman/4.1/en/news.html and # http://dev.mysql.com/doc/refman/5.0/en/news.html . server_version_re = re.compile(r'(\d{1,2})\.(\d{1,2})\.(\d{1,2})') # MySQLdb-1.2.1 and newer automatically makes use of SHOW WARNINGS on # MySQL-4.1 and newer, so the MysqlDebugWrapper is unnecessary. Since the # point is to raise Warnings as exceptions, this can be done with the Python
""" import MySQLdb as dbapi from MySQLdb.converters import conversions from MySQLdb.constants import FIELD_TYPE from kalapy.db.engines import utils from kalapy.db.engines.relational import RelationalDatabase __all__ = ('DatabaseError', 'IntegrityError', 'Database') DatabaseError = dbapi.DatabaseError IntegrityError = dbapi.IntegrityError CONV = conversions.copy() CONV.update({ FIELD_TYPE.DECIMAL: utils.decimal_to_python, }) class Database(RelationalDatabase): data_types = { "key" : "INTEGER AUTO_INCREMENT PRIMARY KEY", "reference" : "INTEGER", "char" : "VARCHAR(%(size)s)", "text" : "LONGTEXT", "integer" : "INTEGER", "float" : "DOUBLE", "decimal" : "DECIMAL(%(max_digits)s, %(decimal_places)s)", "boolean" : "BOOL",
# Raise exceptions for database warnings if DEBUG is on from django.conf import settings if settings.DEBUG: from warnings import filterwarnings filterwarnings("error", category=Database.Warning) DatabaseError = Database.DatabaseError IntegrityError = Database.IntegrityError # MySQLdb-1.2.1 supports the Python boolean type, and only uses datetime # module for time-related columns; older versions could have used mx.DateTime # or strings if there were no datetime module. However, MySQLdb still returns # TIME columns as timedelta -- they are more like timedelta in terms of actual # behavior as they are signed and include days -- and Django expects time, so # we still need to override that. django_conversions = conversions.copy() django_conversions.update({ FIELD_TYPE.TIME: util.typecast_time, FIELD_TYPE.DECIMAL: util.typecast_decimal, FIELD_TYPE.NEWDECIMAL: util.typecast_decimal, }) # This should match the numerical portion of the version numbers (we can treat # versions like 5.0.24 and 5.0.24a as the same). Based on the list of version # at http://dev.mysql.com/doc/refman/4.1/en/news.html and # http://dev.mysql.com/doc/refman/5.0/en/news.html . server_version_re = re.compile(r'(\d{1,2})\.(\d{1,2})\.(\d{1,2})') # MySQLdb-1.2.1 and newer automatically makes use of SHOW WARNINGS on # MySQL-4.1 and newer, so the MysqlDebugWrapper is unnecessary. Since the # point is to raise Warnings as exceptions, this can be done with the Python
def connect_by_uri(uri): """General URI syntax: mysql://user:passwd@host:port/db?opt1=val1&opt2=val2&... where opt_n is in the list of options supported by MySQLdb: host,user,passwd,db,compress,connect_timeout,read_default_file, read_default_group,unix_socket,port NOTE: the authority and the path parts of the URI have precedence over the query part, if an argument is given in both. conv,quote_conv,cursorclass are not (yet?) allowed as complex Python objects are needed, hard to transmit within an URI... See for description of options: http://dustman.net/andy/python/MySQLdb_obsolete/doc/MySQLdb-3.html#ss3.1 http://mysql-python.svn.sourceforge.net/viewvc/mysql-python/trunk/MySQLdb/doc/MySQLdb.txt?revision=438&view=markup&pathrev=438 """ puri = uri_help_split(uri) params = __dict_from_query(puri[QUERY]) if puri[AUTHORITY]: user, passwd, host, port = puri[AUTHORITY] if user: params['user'] = user if passwd: params['passwd'] = passwd if host: params['host'] = host if port: params['port'] = port if puri[PATH]: params['db'] = puri[PATH] if params['db'] and params['db'][0] == '/': params['db'] = params['db'][1:] __merge_typemap = __typemap.copy() __merge_typemap.update(__conn_typemap) __apply_types(params, __merge_typemap) # The next affectation work around a bug in python-mysqldb which # happens when using an unicode charset: the conv parameter is # defaulted to the common dictionary MySQLdb.converters.conversions # when not explicitly given to the __init__() of # MySQLdb.connections.Connection, the _mysql module just store it in # the .converter member in the __init__() method of the base class # _mysql.connection, and later, back in the __init__() of # MySQLdb.connections.Connection, some children of .converter, which # are lists, are prepended by dynamically generated functions. The net # result is that every times a new Mysql connection is asked for with # no individualised conversion dictionary passed to the conv parameter, # a bunch of new functions and tuples are created, on which the process # will keep references forever, effectively leaking some memory as some # won't be used anymore after the termination of any connection. # This work around is believed to be effective because the only # references to the dynamically created conversion functions generated # by MySQLdb.connections will be in this instance-specific copy of # MySQLdb.converters.conversions. A unique reference to this copy will # be held by the connection instance, so when the latter is garbage # collected, the copied conversion dictionary is freed, and eventually # the now orphan tuples and generated functions are too. params['conv'] = CST_CONVERSIONS.copy() cparams = {} for key, value in iteritems(__conn_typemap): if key in params: cparams[key] = params[key] del params[key] conn = MySQLdb.connect(**params) for key, value in iteritems(cparams): if value is None: continue elif isinstance(value, string_types) and value: conn.query("SET @@session.%s = '%s'" % (key, MySQLdb.escape_string(value))) # pylint: disable=no-member elif isinstance(value, (bool, integer_types)): conn.query("SET @@session.%s = %d" % (key, value)) return conn