#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date : 2015年12月08日 00:49:16
# @Author : eddy (278298125@qq.com)
# @Link : http://my.oschina.net/eddylinux
# @Version : 1.0

import pymysql
import re


class Conn_Mysql():
 def __init__(self, db='test'):
 self.host = '192.168.10.195'
 self.user = 'root'
 self.password = '1'
 self.db = db

 # 这些是否可以当做 返回值处理
 def desc(self, sql):
 try:
 db = pymysql.connect(self.host, self.user, self.password, self.db)
 cursor = db.cursor()
 if re.match('(desc.*?)', sql, re.I):
 cursor.execute(sql)
 data = cursor.fetchall()
 print("SQL Result:", list(data))
 else:
 print('please use DESC')
 db.close()
 except pymysql.Error as e:
 # print "Mysql Error %d: %s" %(e.args[0], e.args[1])
 print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

 def show(self, sql):
 try:
 db = pymysql.connect(self.host, self.user, self.password, self.db)
 cursor = db.cursor()
 if re.match('(show.*?)', sql, re.I):
 cursor.execute(sql)
 data = cursor.fetchall()
 print("SQL Result:", list(data))
 else:
 print('please use SHOW')
 db.close()
 except pymysql.Error as e:
 print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

 def select(self, sql):
 try:
 db = pymysql.connect(self.host, self.user, self.password, self.db)
 cursor = db.cursor()
 if re.match('(select.*?)', sql, re.I):
 cursor.execute(sql)
 data = cursor.fetchall()
 print("SQL Result:", list(data))
 else:
 print('please use SELECT SQL')
 db.close()
 except pymysql.Error as e:
 print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

 def update(self, sql):
 try:
 db = pymysql.connect(self.host, self.user, self.password, self.db)
 cursor = db.cursor()
 if re.match('(update.*?)', sql, re.I):
 cursor.execute(sql)
 db.commit()
 else:
 print('please use UPDATE SQL')
 except pymysql.Error as e:
 print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

 def insert(self, sql):
 try:
 db = pymysql.connect(self.host, self.user, self.password, self.db)
 cursor = db.cursor()
 if re.match('(insert.*?)', sql, re.I):
 cursor.execute(sql)
 else:
 print("please use INSERT SQL")
 db.close()
 except pymysql.Error as e:
 print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

 def replace(self, sql):
 try:
 db = pymysql.connect(self.host, self.user, self.password, self.db)
 cursor = db.cursor()
 if re.match('(replace.*?)', sql, re.I):
 cursor.execute(sql)
 else:
 print("please use replace SQL")
 db.close()
 except pymysql.Error as e:
 print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

 def delete(self, sql):
 try:
 db = pymysql.connect(self.host, self.user, self.password, self.db)
 cursor = db.cursor()
 if re.match('(delete.*?)', sql, re.I):
 cursor.execute(sql)
 else:
 print('please use DELETE SQL')
 except pymysql.Error as e:
 print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

 def sql_file(self, file):
 try:
 db = pymysql.connect(self.host, self.user, self.password, self.db)
 cursor = db.cursor()
 with open(file,'r') as sql_file:
 for sql in sql_file:
 cursor.execute(sql)
 db.close()
 except pymysql.Error as e:
 print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

if __name__ == '__main__':
 # 连接数据库名
 my_select = Conn_Mysql('tutorials')
 my_select.select('SELECT * FROM CA WHERE id < 20;')


 my_select = Conn_Mysql('puwell_db')
 my_select.replace('REPLACE INTO T2.csp_device SELECT DISTINCT `B`.* FROM T2.csp_user_auth_device AS `A` INNER JOIN `csp_device` AS B ON `A`.`device_id` = `B`.`device_id`;')

 '''
 my_sql_file1 = Conn_Mysql('test')
 my_sql_file1.sql_file('add_user.sql')

 my_select = Conn_Mysql('user')
 my_select.select('select * from user;')

 my_sql_file1 = Conn_Mysql('user')
 my_sql_file1.sql_file('delete_user.sql')

 my_insert = Conn_Mysql('user')
 my_insert.insert('insert into user values("eddy","123456");')

 my_delete = Conn_Mysql('user')
 my_delete.delete('delete from user where username="eddy";')

 my_update = Conn_Mysql('user')
 my_update.update('update user set password="ok" where username="yangys";')

 my_select.select('select * from user;')

 my_desc = Conn_Mysql('user')
 my_desc.desc('desc user')
 '''