python3 mysql 连接

python3 mysql 连接脚本

# -*- coding: utf-8 -*-



# import MySQLdb,sys,os
import pymysql as MySQLdb,sys,os
import os,sys,traceback
# import MySQLdb.cursors
#import mysql.connector


def singleton(cls):
    instances = {}
    def get_instance():
        if cls not in instances:
            instances[cls] = cls()
        return instances[cls]
    return get_instance()

# 单例模式
@singleton
class GConfig:
    def __init__(self):
        self.db_host = "127.0.0.1"
        self.db_user = "root"
        self.db_pass = "123456"
        self.db_name = "test"
        self.db_port = 3306

class DB(object):
    def __init__(self,host=GConfig.db_host,user=GConfig.db_user,passwd=GConfig.db_pass,
        dbname=GConfig.db_name,charset="utf8",autocommit = True
        ,cursorclass = MySQLdb.cursors.DictCursor,client_flag = None):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.dbname = dbname
        self.charset = charset
        self.autocommit = autocommit
        self.cursor = None
        self.connected = False
        #return No. based list or name based list
        self.cursorclass = cursorclass
        #match by FindRow count or UpdateRow coutn
        self.client_flag = client_flag
    
    
    def connect(self):
        self.conn = MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.dbname,
            charset=self.charset)
        self.conn.autocommit(self.autocommit)
        self.connected = True
        
    def startTransaction(self):
        if not self.connected:
            self.connect()
    
    def commitTransaction(self):
        self.cursor.close() 
        self.conn.commit()  
    
    def endTransaction(self):
        pass
    
    def rollbackTransaction(self):
        self.cursor.close()  
        self.conn.rollback()  
    
    
    def query(self, sql,params = None):
        try:
            if self.cursorclass:
                self.cursor = self.conn.cursor(self.cursorclass)
            else: self.cursor = self.conn.cursor()
            self.cursor.execute(sql,params)
        except (AttributeError, MySQLdb.OperationalError):
            self.connect()
            if self.cursorclass:
                self.cursor = self.conn.cursor(self.cursorclass)
            else: self.cursor = self.conn.cursor()
            self.cursor.execute(sql,params)
        except MySQLdb.Error as e:
            print("Error {}: {}, sql:({}),params:({}) ".format( e.args[0],e.args[1] , sql,params) )
            if not self.autocommit:
                self.rollbackTransaction()
            print (traceback.format_exc())
            return False
        return self.cursor

        
    def getInsertId(self):
        returnid = None
        try:
            returnid = self.conn.insert_id()
        except (AttributeError, MySQLdb.OperationalError):
            self.connect()
            returnid = self.conn.insert_id()
        except MySQLdb.Error as e:
            print("{0}".format(e))
            print (traceback.format_exc())
        return returnid
                
    def close(self):
        if  hasattr(self, 'conn') and self.conn:
            self.conn.close()
class DBTransaction(DB):
    def __init__(self,host=GConfig.db_host,user=GConfig.db_user,passwd=GConfig.db_pass,
        dbname=GConfig.db_name,charset="utf8"):
        # super().__init__(host = host,user = user,passwd = passwd,dbname = dbname,charset = charset,autocommit = False)
        super(self.__class__, self).__init__(host, user,passwd,dbname,charset,False)

if __name__ == '__main__':
    db = DB()   
    cur = db.query("select * from test").fetchone()
    print (cur)
    db.close()
    
    # db =DBTransaction()
    # db.startTransaction()
    # db.query("truncate table test.test")
    # db.query("insert into test.test(state,type,date ) values( '7','5','5')")
    # db.query("insert into test.test(state,type,date ) values( '8','5','5')")
    # db.commitTransaction()
    # db.close()
    
    # db =DBTransaction()
    # db.startTransaction()
    # db.query("insert into test.test(state,type,date ) values( '9','9','5')")
    # db.query("insert into test.test(id,state,type,date ) values( 1,'3','5','5')")
    # db.commitTransaction()
    # db.close()