/bin/db/mysql.py

import MySQLdb

from .dbapi import DBApi, DatabaseError

MySQLError = MySQLdb.DatabaseError


class MySQL(DBApi):
	
	@classmethod
	def connect(cls, pard):
		host = pard['HOST']
		user = pard['USER']
		passwd = pard['PASSWORD']
		db = pard['DB']
		pair = host.split(':')
		if len(pair) == 2:
			host = pair[0]
			port = int(pair[1])
		else:
			port = 3306
		try:
			conn = MySQLdb.connect(host, user, passwd, db, port=port)
			conn.query('set names utf8mb4')
		except MySQLError as xxx_todo_changeme:
			(errno, strerror) = xxx_todo_changeme.args
			raise DatabaseError('%s - %s' % (str(errno), strerror))
		return conn
			
	@classmethod
	def send(cls, pard):
		try:
			return super(MySQL, cls).send(pard)
		except MySQLError as xxx_todo_changeme1:
			(errno, strerror) = xxx_todo_changeme1.args
			raise DatabaseError('%s - %s' % (str(errno), strerror))
		
	@classmethod
	def send_dict(cls, pard):
		try:
			return super(MySQL, cls).send_dict(pard)
		except MySQLError as xxx_todo_changeme2:
			(errno, strerror) = xxx_todo_changeme2.args
			raise DatabaseError('%s - %s' % (str(errno), strerror))	
	
	@classmethod
	def get_db_tables_list(cls, pard):
		"""Return the tables lists in "pard['DB']" database"""
		pard['sql'] = "show tables"
		result = cls.send(pard)
		tables_list = []
		for rec in result:
			tables_list.append(rec[0])
		return tables_list
	
	@classmethod
	def get_db_tables_list_like(cls, pard):
		"""Return the tables lists in "pard['DB']" database
		where table name is like pard['table_search']"""
		pard['sql'] = "show tables like '%(table_search)s%%'" % pard
		result = cls.send(pard)
		tables_list = []
		for rec in result:
			tables_list.append(rec[0])
		return tables_list
	
	@classmethod
	def get_primary_key(cls, pard):
		"""Return the primary key columns lists for pard['db_table']"""
		pard['sql'] = "show index from %(db_table)s where key_name = 'primary'" % pard
		try:
			result = cls.send_dict(pard)
		except DatabaseError:
			pard['sql'] = "show index from %(db_table)s" % pard
			result = cls.send_dict(pard)
		primary_key = []
		for rec in result['rows']:
			if rec['Key_name'] == 'PRIMARY':
				primary_key.append(rec['Column_name'])
		return primary_key
	
	@classmethod
	def show_columns(cls, pard, out='dict'):
		"""Return columns description from pard['table_name']
		The rows are formatted as a list of dictionaries by default.
		"""
		if 'db_table' in pard:
			pard['table_name'] = pard.get('table_name', pard['db_table'])
		pard['sql'] = "show columns from %(table_name)s" % pard
		pard['fetch_all_row'] = True
		result = cls.send_dict(pard)
		if out == 'dict':
			return result['rows']
		elif out == 'list':
			l = []
			for rec in result['rows']:
				l.append(rec['Field'])
			return l
		
	@classmethod
	def show_index(cls, pard):
		"""Return (primary_key_column_list, other_index_description)
		from pard['table_name']
		"""
		pard['sql'] = "show index from %(table_name)s" % pard
		pard['fetch_all_row'] = True
		result = cls.send_dict(pard)
		primary_key = []
		index = {}
		for rec in result['rows']:
			if rec['Key_name'] == 'PRIMARY':
				primary_key.append(rec['Column_name'])
			elif rec['Key_name'] not in index:
				index[rec['Key_name']] = {
					'index_name': rec['Key_name'],
					'columns': [rec['Column_name']],
					'Non_unique': rec['Non_unique']
					}
			else:
				index[rec['Key_name']]['columns'].append(rec['Column_name'])
		index_list = []
		for key in list(index.keys()):
			index_list.append(index[key])
		return primary_key, index_list
	
	@classmethod
	def show_create_table(cls, pard):
		pard['sql']	= 'show create table %(table_name)s' % pard
		result = cls.send(pard)
		if result:
			return result[0][1]
		else:
			return ''