Python MySQLdb.connect() Examples

def connect_mysql(instance, role='admin'):
    """Connect to a MySQL instance as admin

    Args:
    hostaddr - object describing which mysql instance to connect to
    role - a string of the name of the mysql role to use. A bootstrap role can
           be called for MySQL instances lacking any grants. This user does not
           exit in zk.

    Returns:
    a connection to the server as administrator
    """
    if role == 'bootstrap':
        socket = host_utils.get_cnf_setting('socket', instance.port)
        username = 'root'
        password = ''
        db = MySQLdb.connect(unix_socket=socket,
                             user=username,
                             passwd=password,
                             cursorclass=MySQLdb.cursors.DictCursor)

    else:
        username, password = get_mysql_user_for_role(role)
        db = MySQLdb.connect(host=instance.hostname,
                             port=instance.port,
                             user=username,
                             passwd=password,
                             cursorclass=MySQLdb.cursors.DictCursor,
                             connect_timeout=CONNECT_TIMEOUT)
    return db 

def emit(self, record):
        """
        Connect to DB, execute SQL Request, disconnect from DB
        @param record:
        @return: 
        """ 
        # Use default formatting:
        self.format(record)
        # Set the database time up:
        self.formatDBTime(record)
        if record.exc_info:
            record.exc_text = logging._defaultFormatter.formatException(record.exc_info)
        else:
            record.exc_text = ""
        # Insert log record:
        sql = mySQLHandler.insertion_sql
        try:
            conn=MySQLdb.connect(host=self.db['host'],port=self.db['port'],user=self.db['dbuser'],passwd=self.db['dbpassword'],db=self.db['dbname'])
        except _mysql_exceptions, e:
            from pprint import pprint
            print("The Exception during db.connect")           
            pprint(e)
            raise Exception(e)
            exit(-1) 

def require_db_connection(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        ####连接数据库
        if hasattr(g, 'conn') and g.conn != None and hasattr(g, 'cursor') and g.cursor != None:
            print 'has db connect, do nothing'
        else:
            (g.conn, g.cursor) = _connect_db()
            print 'create new db connect'
        
        #执行方法
        func = f(*args, **kwargs)
        
        ###关闭数据库连接
        if hasattr(g, 'conn') and g.conn != None and hasattr(g, 'cursor') and g.cursor != None:
            g.cursor.close()
            g.cursor = None 
            g.conn.close()
            g.conn = None
            print 'close db connect'
        else:
            print 'no db connect, no need to close...'
        
        return func
    return decorated_function 

def handle(self, *args, **options):
        self.stdout.write(self.style.SUCCESS('Starting db creation'))

        dbname = options.get('db_name') or settings.DATABASES['default']['NAME']
        user = options.get('user') or settings.DATABASES['default']['USER']
        password = options.get('password') or settings.DATABASES['default']['PASSWORD']
        host = settings.DATABASES['default']['HOST']

        con = db.connect(user=user, host=host, password=password)
        cur = con.cursor()
        cur.execute(f'CREATE DATABASE {dbname}')
        cur.execute(f'ALTER DATABASE `{dbname}` CHARACTER SET utf8')
        cur.close()
        con.close()

        self.stdout.write(self.style.SUCCESS('All Done')) 

def get_iam_token(self, conn):
        """
        Uses AWSHook to retrieve a temporary password to connect to MySQL
        Port is required. If none is provided, default 3306 is used
        """
        from airflow.providers.amazon.aws.hooks.base_aws import AwsBaseHook

        aws_conn_id = conn.extra_dejson.get('aws_conn_id', 'aws_default')
        aws_hook = AwsBaseHook(aws_conn_id, client_type='rds')
        if conn.port is None:
            port = 3306
        else:
            port = conn.port
        client = aws_hook.get_conn()
        token = client.generate_db_auth_token(conn.host, port, conn.login)
        return token, port 

def execute(cur, sql, params=[]):
    global args
    if not (args.sql_silent):
        log("SQL: " + sql)
        log("PARAMS: " + str(params))
    if args.dryrun:
        if not (("select" in sql.lower()) or ("show columns" in sql.lower())):
            print "Not executing, dryrun"
            return
    try:
    	  cur.execute(sql, params)
    except Exception as ex:
        print "Exception in SQL execution, retrying command once"
        print ex
        connect(False)
        cur = getCursor()
        cur.execute(sql, params)
    return cur 

def setUp(self):
        _skip_if_no_MySQLdb()
        import MySQLdb
        try:
            # Try Travis defaults.
            # No real user should allow root access with a blank password.
            self.db = MySQLdb.connect(host='localhost', user='root', passwd='',
                                    db='pandas_nosetest')
        except:
            pass
        else:
            return
        try:
            self.db = MySQLdb.connect(read_default_group='pandas')
        except MySQLdb.ProgrammingError as e:
            raise nose.SkipTest(
                "Create a group of connection parameters under the heading "
                "[pandas] in your system's mysql default file, "
                "typically located at ~/.my.cnf or /etc/.my.cnf. ")
        except MySQLdb.Error as e:
            raise nose.SkipTest(
                "Cannot connect to database. "
                "Create a group of connection parameters under the heading "
                "[pandas] in your system's mysql default file, "
                "typically located at ~/.my.cnf or /etc/.my.cnf. ") 

def dbgetstring(db,tablename,sensorid,columnid,revision=None):
    """
    DEFINITION:
        Perform a select search and return strings
    PARAMETERS:
    Variables:
        - db:           (mysql database) defined by mysql.connect().
        - tablename:    name of the table
        - sensorid:     sensor to match
        - columnid:     column in which search is performed
    Kwargs:
        - revision:     optional sensor revision (not used so far)
    APPLICATION:
        >>>stationid =  dbgetstring(db, 'DATAINFO', 'LEMI25_22_0001', 'StationID')
        returns the stationid from the DATAINFO table which matches the Sensor
    """
    sql = 'SELECT ' + columnid + ' FROM ' + tablename + ' WHERE SensorID = "' + sensorid + '"';
    cursor = db.cursor()
    cursor.execute(sql)
    row = cursor.fetchone()
    try:
        fl = float(row[0])
        return fl
    except:
        return row[0] 

def connect(self, host=None, user=None, passwd=None, database=None):
        '''Connect to specific data base. 
        The first time a valid host, user, passwd and database must be provided,
        Following calls can skip this parameters
        '''
        try:
            if host     is not None: self.host = host
            if user     is not None: self.user = user
            if passwd   is not None: self.passwd = passwd
            if database is not None: self.database = database

            self.con = mdb.connect(self.host, self.user, self.passwd, self.database)
            print "DB: connected to %s@%s -> %s" % (self.user, self.host, self.database)
            return 0
        except mdb.Error, e:
            print "nfvo_db.connect Error connecting to DB %s@%s -> %s Error %d: %s" % (self.user, self.host, self.database, e.args[0], e.args[1])
            return -1 

def connect(self, host=None, user=None, passwd=None, database=None):
        '''Connect to the concrete data base. 
        The first time a valid host, user, passwd and database must be provided,
        Following calls can skip this parameters
        '''
        try:
            if host     is not None: self.host = host
            if user     is not None: self.user = user
            if passwd   is not None: self.passwd = passwd
            if database is not None: self.database = database

            self.con = mdb.connect(self.host, self.user, self.passwd, self.database)
            self.logger.debug("connected to DB %s at %s@%s", self.database,self.user, self.host)
            return 0
        except mdb.Error as e:
            self.logger.error("Cannot connect to DB %s at %s@%s Error %d: %s", self.database, self.user, self.host, e.args[0], e.args[1])
            return -1 

def execute_with_reconnect(self, sql: str, args: Optional[List[ValidSqlArgumentDescription]] = None, fetch_rows: Optional[bool] = False) -> Tuple[int, List[ValidSqlArgumentDescription]]:
        result = None
        # Attempt to execute the query and reconnect 3 times, then give up
        for _ in range(3):
            try:
                p = perf.start()
                n = self.cursor.execute(sql, args)
                perf.check(p, 'slow_query', (f'```{sql}```', f'```{args}```'), 'mysql')
                if fetch_rows:
                    rows = self.cursor.fetchall()
                    result = (n, rows)
                else:
                    result = (n, [])
                break
            except OperationalError as e:
                if 'MySQL server has gone away' in str(e):
                    print('MySQL server has gone away: trying to reconnect')
                    self.connect()
                else:
                    # raise any other exception
                    raise e
        else:
            # all attempts failed
            raise DatabaseException('Failed to execute `{sql}` with `{args}`. MySQL has gone away and it was not possible to reconnect in 3 attemps'.format(sql=sql, args=args))
        return result 

def query_single(_db_config, _sql, _args):
    config = _db_config
    conn = MySQLdb.connect(host=config['host'], port=config['port'], user=config['user'], passwd=config['passwd'],
                           db=config['db'], charset=config['charset'], use_unicode=True)
    cursor = conn.cursor(MySQLdb.cursors.DictCursor)
    result = ()
    try:
        cursor.execute(_sql, _args)
        result = cursor.fetchall()
    except:
        pass
        rootLogger.error("query exception sql is %s ,_args is %s,stacks is %s", _sql, _args, get_caller_info_total())
        rootLogger.exception("message")
    finally:
        cursor.close()
        conn.close()
    return result


# ===============================================
# FUNCTION  更新或者删除
# =============================================== 

def insertOrUpdate_getId(_db_config, _sql, _args):
    result = 0
    id = 0
    config = _db_config
    conn = MySQLdb.connect(host=config['host'], port=config['port'], user=config['user'], passwd=config['passwd'],
                           db=config['db'], charset=config['charset'], use_unicode=True)
    cursor = conn.cursor(MySQLdb.cursors.DictCursor)
    try:
        cursor.execute(_sql, _args)
        id = conn.insert_id()
        conn.commit()
        result = cursor.rowcount
    except:
        pass
        rootLogger.error("exception sql is %s ,_args is %s", _sql, _args)
        rootLogger.exception("message")
        conn.rollback()
    finally:
        print("affected rows = {}".format(cursor.rowcount))
        cursor.close()
        conn.close()
    return result, id 

def __init__(self, sempo_organisation_id, ge_community_token_id=None, user_limit=10000):
        self.poa = POAExplorer()
        timeout = 120
        print('RDSMigrate: Creating a connection, with a %d sec timeout.' % timeout)
        self.connection = MySQLdb.connect(host=current_app.config['GE_DB_HOST'],
                                          db=current_app.config['GE_DB_NAME'],
                                          user=current_app.config['GE_DB_USER'],
                                          port=int(current_app.config['GE_DB_PORT']),
                                          password=current_app.config['GE_DB_PASSWORD'],
                                          connect_timeout=timeout)

        if not self.connection:
            raise RuntimeError('Could not connect to database')
        else:
            print('DB connection successfully created.  Yeah us.')

        self.sempo_organisation_id = sempo_organisation_id
        self.ge_community_token_id = ge_community_token_id
        self.user_limit = user_limit 

def setUp(self):
        self.db = sqlite3.connect(':memory:') 

def get_new_connection(self, conn_params):
        return Database.connect(**conn_params) 

def exec_sql(cmd, conf="../../global_config.ini"):
    config = ConfigParser.ConfigParser()
    config.read(conf)
    user = config.get("AUTOTEST_WEB", "user")
    passwd = config.get("AUTOTEST_WEB", "password")
    db = config.get("AUTOTEST_WEB", "database")
    db_type = config.get("AUTOTEST_WEB", "db_type")
    if db_type != 'mysql':
        print("regression.py: only support mysql database!")
        sys.exit(1)

    conn = MySQLdb.connect(host="localhost", user=user,
                           passwd=passwd, db=db)
    cursor = conn.cursor()
    cursor.execute(cmd)
    rows = cursor.fetchall()
    lines = []
    for row in rows:
        line = []
        for c in row:
            line.append(str(c))
        lines.append(" ".join(line))

    cursor.close()
    conn.close()
    return lines 

def check_mysql_connection():
    """
    Check simple MySQL/MariaDB connection
    """

    try:
        print_color('+', 'Trying to connect to MySQL/MariaDB instance')
        db = MySQLdb.connect(host=hostname, port=port, user=username, passwd=password)
    except MySQLdb.Error, e:
        try:
            print_color('fail', "ERROR [%d]: %s" % (e.args[0], e.args[1]))
            sys.exit(1)
        except IndexError:
            print_color('fail', "ERROR: %s" % str(e))
            sys.exit(1) 

def __connect(self):
        try:
            self.conn = MySQLdb.connect(host = self.host, port = self.port, user = self.user, passwd = self.passwd, db = self.db, charset = self.charset)
            #字典形式
            self.cursor = self.conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
#             print("Mysql Connect to %s: %s" % (self.host, str(self.port)))
        except MySQLdb.Error as e:
            print("Mysql Error %s: %s" % (self.host, e.args[1])) 

def sql_query(queries, return_list=False, exit_fail=True):
    """
    This function will pass queries to the MySQL/MariaDB instance

    :queries: list of queries to execute
    :type queries: list / tuple
    :return_list: if you need a return from the query, set it to True
    :type return_list: boolean
    :exit_fail: you can choose if the program needs to continue on fail or not
    :type exit_fail: boolean

    :returns:
    :rtype: return a list of result

    """
    db = MySQLdb.connect(host=hostname, port=port, user=username, passwd=password)
    cur = db.cursor()

    try:
        query = ' '.join(queries)
        cur.execute(query)
    except MySQLdb.Error, e:
        try:
            print_color('fail', "MySQL Error [%d]: %s" % (e.args[0], e.args[1]))
            if (exit_fail):
                restore_toi()
                sys.exit(1)
        except IndexError:
            print_color('fail', "MySQL Error: %s" % str(e))
            if (exit_fail):
                restore_toi()
                sys.exit(1) 

def __init__(self):
		self.db=MySQLdb.connect(DB_HOST,DB_USER,PASSWORD,DB_NAME,charset="utf8")
		self.cursor = self.db.cursor() 

def __init__(self):
		self.db = MySQLdb.connect('127.0.0.1','root','','Baidu',charset='utf8');
		self.cursor = self.db.cursor() 

def get_conn(self):
        """
        Establishes a connection to a mysql database
        by extracting the connection configuration from the Airflow connection.

        .. note:: By default it connects to the database via the mysqlclient library.
            But you can also choose the mysql-connector-python library which lets you connect through ssl
            without any further ssl parameters required.

        :return: a mysql connection object
        """
        conn = self.connection or self.get_connection(self.mysql_conn_id)  # pylint: disable=no-member

        client_name = conn.extra_dejson.get('client', 'mysqlclient')

        if client_name == 'mysqlclient':
            import MySQLdb
            conn_config = self._get_conn_config_mysql_client(conn)
            return MySQLdb.connect(**conn_config)

        if client_name == 'mysql-connector-python':
            import mysql.connector  # pylint: disable=no-name-in-module
            conn_config = self._get_conn_config_mysql_connector_python(conn)
            return mysql.connector.connect(**conn_config)  # pylint: disable=no-member

        raise ValueError('Unknown MySQL client name provided!') 

def get_new_connection(self, conn_params):
        conn = Database.connect(**conn_params)
        conn.encoders[SafeText] = conn.encoders[six.text_type]
        conn.encoders[SafeBytes] = conn.encoders[bytes]
        return conn 

def get(self):
        connect = pool.get_conn()
        cursor = connect.cursor()
        cursor.execute('SELECT * FROM app_blog LIMIT 1')
        result = cursor.fetchone()
        cursor.close()
        pool.release(connect)
        self.finish(u'<p>{}</p><p>{}</p>'.format(result[1], result)) 

def get(self):
        connect = MySQLdb.connect(user='root',
                                  passwd='',
                                  db='test',
                                  host='localhost',
                                  port=3306,
                                  charset='utf8')
        cursor = connect.cursor()
        cursor.execute('SELECT * FROM app_blog LIMIT 1')
        result = cursor.fetchone()
        cursor.close()
        connect.close()
        self.finish(u'<p>{}</p><p>{}</p>'.format(result[1], result[2])) 

def find_databases(dbs=None):
    """ Returns a map of dbname (string) to DB instances to monitor.

    Args:
        dbs: A map of dbname (string) to DB instances already monitored.
             This map will be modified in place if it's not None.
    """
    if dbs is None:
        dbs = {}
    for sockfile in glob.glob(SOCKET_GLOB):
        try:
            db = mysql_connect(sockfile)
            cursor = db.cursor()
            cursor.execute("SELECT VERSION()")
            version = cursor.fetchone()[0]
            cursor.execute(
                "SELECT VARIABLE_VALUE FROM "
                "INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = "
                "'PORT'")
            port = cursor.fetchone()[0]
            db_type = get_db_type(port)
        except:
            err("Couldn't connect to %s: %s" % (
                sockfile, traceback.format_exc()))
            continue
        dbs[port] = DB(sockfile, db, cursor, version, port, db_type)
    return dbs 

def mysql_connect(sockfile):
    """ Connects to the MySQL server using the specified socket file. """
    user, passwd = mysql_lib.get_mysql_user_for_role(MYSQL_USER_ROLE)
    return MySQLdb.connect(unix_socket=sockfile,
                           connect_timeout=CONNECT_TIMEOUT,
                           user=user, passwd=passwd) 

def getCursor():
    if not db.open: connect(False)
    return db.cursor(MySQLdb.cursors.DictCursor) 

def checkTablePresence(self):
        try:
            conn=MySQLdb.connect(host=self.db['host'],port=self.db['port'],user=self.db['dbuser'],passwd=self.db['dbpassword'],db=self.db['dbname'])
        except _mysql_exceptions, e:
            raise Exception(e)
            exit(-1)