Python MySQLdb.cursors() 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 _get_conn_config_mysql_client(self, conn):
        conn_config = {
            "user": conn.login,
            "passwd": conn.password or '',
            "host": conn.host or 'localhost',
            "db": self.schema or conn.schema or ''
        }

        # check for authentication via AWS IAM
        if conn.extra_dejson.get('iam', False):
            conn_config['passwd'], conn.port = self.get_iam_token(conn)
            conn_config["read_default_group"] = 'enable-cleartext-plugin'

        conn_config["port"] = int(conn.port) if conn.port else 3306

        if conn.extra_dejson.get('charset', False):
            conn_config["charset"] = conn.extra_dejson["charset"]
            if conn_config["charset"].lower() in ('utf8', 'utf-8'):
                conn_config["use_unicode"] = True
        if conn.extra_dejson.get('cursor', False):
            import MySQLdb.cursors
            if (conn.extra_dejson["cursor"]).lower() == 'sscursor':
                conn_config["cursorclass"] = MySQLdb.cursors.SSCursor
            elif (conn.extra_dejson["cursor"]).lower() == 'dictcursor':
                conn_config["cursorclass"] = MySQLdb.cursors.DictCursor
            elif (conn.extra_dejson["cursor"]).lower() == 'ssdictcursor':
                conn_config["cursorclass"] = MySQLdb.cursors.SSDictCursor
        local_infile = conn.extra_dejson.get('local_infile', False)
        if conn.extra_dejson.get('ssl', False):
            # SSL parameter for MySQL has to be a dictionary and in case
            # of extra/dejson we can get string if extra is passed via
            # URL parameters
            dejson_ssl = conn.extra_dejson['ssl']
            if isinstance(dejson_ssl, str):
                dejson_ssl = json.loads(dejson_ssl)
            conn_config['ssl'] = dejson_ssl
        if conn.extra_dejson.get('unix_socket'):
            conn_config['unix_socket'] = conn.extra_dejson['unix_socket']
        if local_infile:
            conn_config["local_infile"] = 1
        return conn_config 

def from_settings(cls,settings):
        '''1、@classmethod声明一个类方法，而对于平常我们见到的则叫做实例方法。 
           2、类方法的第一个参数cls（class的缩写，指这个类本身），而实例方法的第一个参数是self，表示该类的一个实例
           3、可以通过类来调用，就像C.f()，相当于java中的静态方法'''
        dbparams=dict(
            host=settings['MYSQL_HOST'],#读取settings中的配置
            db=settings['MYSQL_DBNAME'],
            user=settings['MYSQL_USER'],
            passwd=settings['MYSQL_PASSWD'],
            charset='utf8',#编码要加上，否则可能出现中文乱码问题
            cursorclass=MySQLdb.cursors.DictCursor,
            use_unicode=False,
        )
        dbpool=adbapi.ConnectionPool('MySQLdb',**dbparams)#**表示将字典扩展为关键字参数,相当于host=xxx,db=yyy....
        return cls(dbpool)#相当于dbpool付给了这个类，self中可以得到

    #pipeline默认调用 

def from_settings(cls, settings):
        """
        自定义组件或扩展很有用的方法: 这个方法名字固定, 是会被scrapy调用的。
        这里传入的cls是指当前的class
        """
        db_parms = dict(
            host=settings["MYSQL_HOST"],
            db=settings["MYSQL_DBNAME"],
            user=settings["MYSQL_USER"],
            passwd=settings["MYSQL_PASSWORD"],
            charset='utf8mb4',
            cursorclass=MySQLdb.cursors.DictCursor,
            use_unicode=True,
        )
        # 连接池ConnectionPool
        dbpool = adbapi.ConnectionPool("MySQLdb", **db_parms)

        # 此处相当于实例化pipeline, 要在init中接收。
        return cls(dbpool) 

def __init__(self):
        self.db = MySQLdb.connect("localhost", "root", "199194", "tianchi",
                                  cursorclass = MySQLdb.cursors.DictCursor)
        self.cursor = self.db.cursor()
        # 这个数据库连接用来查询train_user_after 31th的数据
        self.db2 = MySQLdb.connect("localhost", "root", "199194", "tianchi",
                                  cursorclass = MySQLdb.cursors.DictCursor)
        self.cursor2 = self.db2.cursor() 

def from_settings(cls, settings):
        dbargs = dict(
            host=settings['MYSQL_HOST'],
            db=settings['MYSQL_DBNAME'],
            user=settings['MYSQL_USER'],
            passwd=settings['MYSQL_PASSWD'],
            charset='utf8',
            cursorclass=MySQLdb.cursors.DictCursor,
            use_unicode=True,
        )
        dbpool = adbapi.ConnectionPool('MySQLdb', **dbargs)
        return cls(dbpool)

    # pipeline默认调用 

def from_settings(cls, settings):
        dbargs = dict(
            host=settings['MYSQL_HOST'],
            db=settings['MYSQL_DBNAME'],
            user=settings['MYSQL_USER'],
            passwd=settings['MYSQL_PASSWD'],
            charset='utf8',
            cursorclass=MySQLdb.cursors.DictCursor,
            use_unicode=True,
        )
        print dbargs
        dbpool = adbapi.ConnectionPool('MySQLdb', **dbargs)
        return cls(dbpool) 

def __init__(self,dbpool):
        self.dbpool=dbpool
        ''' 这里注释中采用写死在代码中的方式连接线程池，可以从settings配置文件中读取，更加灵活
            self.dbpool=adbapi.ConnectionPool('MySQLdb',
                                          host='127.0.0.1',
                                          db='crawlpicturesdb',
                                          user='root',
                                          passwd='123456',
                                          cursorclass=MySQLdb.cursors.DictCursor,
                                          charset='utf8',
                                          use_unicode=False)''' 

def __init__(self, username):
        warnings.warn(
            (
                "MysqlAuthUser authentication class is deprecated: "
                "use cas_server.auth.SqlAuthUser instead"
            ),
            UserWarning
        )
        # see the connect function at
        # http://mysql-python.sourceforge.net/MySQLdb.html#functions-and-attributes
        # for possible mysql config parameters.
        mysql_config = {
            "user": settings.CAS_SQL_USERNAME,
            "passwd": settings.CAS_SQL_PASSWORD,
            "db": settings.CAS_SQL_DBNAME,
            "host": settings.CAS_SQL_HOST,
            "charset": settings.CAS_SQL_DBCHARSET,
            "cursorclass": MySQLdb.cursors.DictCursor
        }
        if not MySQLdb:
            raise RuntimeError("Please install MySQLdb before using the MysqlAuthUser backend")
        conn = MySQLdb.connect(**mysql_config)
        curs = conn.cursor()
        if curs.execute(settings.CAS_SQL_USER_QUERY, (username,)) == 1:
            self.user = curs.fetchone()
            super(MysqlAuthUser, self).__init__(self.user['username'])
        else:
            super(MysqlAuthUser, self).__init__(username) 

def _conn_db(self, host, user, passwd, db):
        return MySQLdb.connect(host=host,
                               user=user,
                               passwd=passwd,
                               db=db,
                               cursorclass=MySQLdb.cursors.DictCursor) 

def from_settings(cls, settings):
        dbparms = dict(
            host=settings["MYSQL_HOST"],
            db=settings["MYSQL_DBNAME"],
            user=settings["MYSQL_USER"],
            passwd=settings["MYSQL_PASSWORD"],
            charset='utf8',
            cursorclass=MySQLdb.cursors.DictCursor,
            use_unicode=True,
        )
        dbpool = adbapi.ConnectionPool("MySQLdb", **dbparms)

        return cls(dbpool) 

def from_settings(cls, settings):
		dbparams = dict(
			host = settings['HOST_ADDRESS'],  
			db = settings['DB_NAME'],
			user = settings['USER'],
			passwd = settings['PASSWORD'],
			charset = 'utf8',  # 编码要加上，否则可能出现中文乱码问题
			cursorclass = MySQLdb.cursors.DictCursor,
			use_unicode = False,
		)
		dbpool = adbapi.ConnectionPool('MySQLdb', **dbparams)  # **表示将字典扩展为关键字参数,相当于host=xxx,db=yyy....
		return cls(dbpool)  # 相当于dbpool付给了这个类，self中可以得到

	# pipeline默认调用 

def __init__(self):
        self.db = MySQLdb.connect("localhost", "root", "199194", "tianchi",
                                  cursorclass = MySQLdb.cursors.DictCursor)
        self.cursor = self.db.cursor() 

def process(args):
    read_conn = get_mysql_conn(args)
    write_conn = get_mysql_conn(args)

    read_sql = READ_SQL.format(**vars(args))
    update_sql = UPDATE_SQL.format(**vars(args))

    read_cur = MySQLdb.cursors.SSCursor(read_conn)
    read_cur.execute(read_sql)
    for row in read_cur:
        pkey, text = row
        lemmatized = lemmatize(text)
        write_cur = write_conn.cursor()
        write_cur.execute(update_sql, (lemmatized, pkey))
        write_conn.commit() 

def get_cursor(self, ctype=MySQLdb.cursors.Cursor):
        self.ensure_connect()
        return self._connect.cursor(ctype) 

def create_connection(self):
        conn = MySQLdb.connect(
                host=self.config['AIRFLOW_DB_HOST'],
                user=self.config['AIRFLOW_USERNAME'],
                password=self.config['AIRFLOW_PASSWORD'],
                db=self.config['AIRFLOW_DATABASE'],
                cursorclass=MySQLdb.cursors.DictCursor,
                connect_timeout=3
            )
        conn.autocommit(True)
        return conn 

def initialise_database(self):
        self._db_client = MySQLdb.connect ( self._mysql_host,
                                            self._mysql_username,
                                            self._mysql_password,
                                            self._mysql_db,
                                            cursorclass = MySQLdb.cursors.DictCursor
                                            )

        self._influx_client = InfluxDBClient(
                                            self._influx_db_host,
                                            self._influx_db_port,
                                            self._influx_db_username,
                                            self._influx_db_password,
                                            self._influx_db
                                            ) 

def __init__(self, host, port, max_idle_time=8*3600, connect_timeout=3, **kwargs):
        #打开数据库连接
        self.db = None
        self.host = host
        self.port = port
        self.max_idle_time = float(max_idle_time)
        self.last_use_time = time.time()
        self.kwargs = dict(host=host, port=port, charset="utf8", connect_timeout=connect_timeout, cursorclass=MySQLdb.cursors.DictCursor, **kwargs)
        try:
            self.connect()
        except Exception:
            raise 

def conn_mysql():
	verbose("Connecting to MySQL......")
	if args.prompt_password:
		password=getpass.getpass("Please input your MySQL password:")
	else:
		password = args.password
	conn = MySQLdb.connect(user=args.user,host=args.host,port=args.port,unix_socket=args.socket,passwd=args.password,db=args.old_database,cursorclass=MySQLdb.cursors.DictCursor)
	return conn
	# 建立MySQL连接
	# connect to MySQL 

def connect(self):
        kwargs = {}

        if current_app.config['MYSQL_HOST']:
            kwargs['host'] = current_app.config['MYSQL_HOST']

        if current_app.config['MYSQL_USER']:
            kwargs['user'] = current_app.config['MYSQL_USER']

        if current_app.config['MYSQL_PASSWORD']:
            kwargs['passwd'] = current_app.config['MYSQL_PASSWORD']

        if current_app.config['MYSQL_DB']:
            kwargs['db'] = current_app.config['MYSQL_DB']

        if current_app.config['MYSQL_PORT']:
            kwargs['port'] = current_app.config['MYSQL_PORT']

        if current_app.config['MYSQL_UNIX_SOCKET']:
            kwargs['unix_socket'] = current_app.config['MYSQL_UNIX_SOCKET']

        if current_app.config['MYSQL_CONNECT_TIMEOUT']:
            kwargs['connect_timeout'] = \
                current_app.config['MYSQL_CONNECT_TIMEOUT']

        if current_app.config['MYSQL_READ_DEFAULT_FILE']:
            kwargs['read_default_file'] = \
                current_app.config['MYSQL_READ_DEFAULT_FILE']

        if current_app.config['MYSQL_USE_UNICODE']:
            kwargs['use_unicode'] = current_app.config['MYSQL_USE_UNICODE']

        if current_app.config['MYSQL_CHARSET']:
            kwargs['charset'] = current_app.config['MYSQL_CHARSET']

        if current_app.config['MYSQL_SQL_MODE']:
            kwargs['sql_mode'] = current_app.config['MYSQL_SQL_MODE']

        if current_app.config['MYSQL_CURSORCLASS']:
            kwargs['cursorclass'] = getattr(cursors, current_app.config['MYSQL_CURSORCLASS'])

        return MySQLdb.connect(**kwargs) 

def terminate_instances(hostname=None, dry_run=False):
    zk = host_utils.MysqlZookeeper()
    username, password = mysql_lib.get_mysql_user_for_role('admin')
    terminate_instances = get_retirement_queue_servers(TERMINATE_INSTANCE)
    botoconn = boto.ec2.connect_to_region('us-east-1')

    if hostname:
        if hostname in terminate_instances:
            log.info('Only acting on {hostname}'.format(hostname=hostname))
            terminate_instances = {hostname: terminate_instances[hostname]}
        else:
            log.info('Supplied host {hostname} is not ready '
                     'for termination'.format(hostname=hostname))
            return

    for hostname in terminate_instances:
        if hostname in get_protected_hosts('set'):
            log.warning('Host {hostname} is protected from '
                        'retirement'.format(hostname=hostname))
            remove_from_retirement_queue(hostname)
            continue
        for instance in zk.get_all_mysql_instances():
            if instance.hostname == hostname:
                log.warning("It appears {instance} is in zk. This is "
                            "very dangerous!".format(instance=instance))
                remove_from_retirement_queue(hostname)
                continue

        log.info('Confirming mysql is down on '
                 '{hostname}'.format(hostname=hostname))

        try:
            with timeout.timeout(3):
                conn = MySQLdb.connect(
                    host=terminate_instances[hostname]['internal_ip'],
                    user=username,
                    passwd=password,
                    cursorclass=MySQLdb.cursors.DictCursor)
            log.error('Did not get MYSQL_ERROR_CONN_HOST_ERROR, removing {} '
                      'from queue'.format(hostname))
            conn.close()
            remove_from_retirement_queue(hostname)
            continue
        except MySQLdb.OperationalError as detail:
            (error_code, msg) = detail.args
            if error_code != mysql_lib.MYSQL_ERROR_CONN_HOST_ERROR:
                raise
            log.info('MySQL is down')
        log.info('Terminating instance ' '{instance}'.format(
            instance=terminate_instances[hostname]['instance_id']))
        if dry_run:
            log.info('In dry_run mode, not changing state')
        else:
            botoconn.terminate_instances(
                instance_ids=[
                    terminate_instances[hostname]['instance_id']])
            log_to_retirement_queue(hostname, terminate_instances[hostname][
                                    'instance_id'], TERMINATE_INSTANCE)