Python mysql.connector.connect() Examples

def connect(self):
		import mysql.connector as database
		self.dbh = database.connect(**self.dsn)
		self.dbc = self.dbh.cursor()
		try:
			self.query("SELECT db_version FROM version")
		except:
			if self.custom_tables:
				statements = self.create_statements + self.custom_tables + ["COMMIT;", "SET autocommit=1;"]
			else:
				statements = self.create_statements
			for SQL in statements:
				self.execute(SQL)
				
			self.commit()
		self.connected = True 

def wait_for_connection(self, user='root', password=None, database=None,
                            timeout=10):
        """
        Polls mysqld socket until we get a connection or the timeout
        expires (raise WaitTimeoutError). Defaults to root empty/password.
        """
        while timeout > 0:
            try:
                sock = '/var/run/mysqld/mysqld.sock'
                return mysqlconn.connect(unix_socket=sock,
                                         user=user,
                                         password=password,
                                         database=database,
                                         charset='utf8',
                                         connection_timeout=timeout)
            except MySQLError as ex:
                timeout = timeout - 1
                if timeout == 0:
                    raise WaitTimeoutError(ex)
                time.sleep(1) 

def getTotalSoftwares():
    response.set_header("Access-Control-Allow-Origin", "*")
    db = mariadb.connect(user=Settings.MONITOR_DB['user'], password=Settings.MONITOR_DB['password'], database=Settings.MONITOR_DB['database'])
    softwares = collections.OrderedDict()

    maxDays = request.GET.get('maxDays', '31').strip()
    maxDays = int(maxDays) - 1

    query = """SELECT name, SUM(hits) AS total, MAX(dateStats) AS maxDate
               FROM softwares
               GROUP BY name
               HAVING maxDate >= DATE_SUB(NOW(), INTERVAL %s DAY)
               ORDER BY total DESC"""

    results = db.cursor()
    results.execute(query, (maxDays, ))

    for row in results:
        softwares[row[0].encode('utf8')] = str(row[1])

    db.close()

    return simplejson.dumps(softwares) 

def getTotalSchemas():
    response.set_header("Access-Control-Allow-Origin", "*")
    db = mariadb.connect(user=Settings.MONITOR_DB['user'], password=Settings.MONITOR_DB['password'], database=Settings.MONITOR_DB['database'])
    schemas = collections.OrderedDict()

    query = """SELECT `name`, SUM(`hits`) AS `total`
               FROM `schemas`
               GROUP BY `name`
               ORDER BY `total` DESC"""

    results = db.cursor()
    results.execute(query)

    for row in results:
        schemas[str(row[0])] = row[1]

    db.close()

    return simplejson.dumps(schemas) 

def database_connection_mysql(host, port, db, user, password):
    if MySQLdb:
        # TODO: should provide support for MySQL 'option-files' too.
        if host is None:
            host = 'localhost'
        if port is None:
            port = 3306
        if user is None:
            user = getpass.getuser()
        if password:
            try:
                return MySQLdb.connect(host=host, port=port, db=db,
                                       user=user, password=password)
            except:
                # some versions of the MySQL driver use different names
                return MySQLdb.connect(host=host, port=port, db=db,
                                       username=user, passwd=password)
        else:
            return MySQLdb.connect(host=host, port=port, db=db, user=user)
    else:
        print('MySQL driver not available', file=sys.stderr) 

def database_connection_postgres(host, port, db, user, password):
    if pgdb:
        if port is not None:
            host = host + ':' + str(port)
        return pgdb.connect(host=host, database=db,
                            user=user, password=password)
    else:
        print('PostgreSQL driver not available', file=sys.stderr)
        sys.exit(1) 

def __init__(self, user, password, database=None, verbose=LOG_OFF):
        # passwd = input("Password > ")
        # passwd = password
        self._v = verbose

        if database is not None:
            self.database = database
            try:
                self.conn = maria.connect(user=user, password=password, database=self.database)
            except maria.Error as err:
                if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                    print("Something is wrong with your user name or password")
                elif err.errno == errorcode.ER_BAD_DB_ERROR:
                    print("Database does not exist")
                else:
                    print(err)
        else:
            try:
                self.conn = maria.connect(user=user, password=password)
            except maria.Error as err:
                if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                    print("Something is wrong with your user name or password")
                else:
                    print(err) 

def get_iam_mysql_token(host, port, user):
    logger.debug('getting iam token')
    # connect to RDS and obtain a token for IAM authorization
    rds = boto3.client('rds')
    try:
        mytoken = rds.generate_db_auth_token(host, port, user, Region=None)
        return mytoken
    except botocore.exceptions.ClientError as e:
        raise
# connect to mysql using the token or password and insert 100k rows of data 

def __init__(self, db_host, db_name, db_user, db_pass, db_port=3306):
		self.dsn = {
				"database": db_name,
				"host": db_host,
				"port": int(db_port),
				"user": str(db_user),
				"password": str(db_pass),
				"buffered": True
		}
		self.connect() 

def connect(self):
		self.dbh = database.connect(self.dbf, check_same_thread=False)
		self.dbc = self.dbh.cursor()
		with self.db_lock:
			try:
				self.query("SELECT db_version FROM version")
			except:
				if self.custom_tables:
					statements = self.create_statements + self.custom_tables
				else:
					statements = self.create_statements
				for SQL in statements:
					self.execute(SQL)
				self.commit()
		self.connected = True 

def __init__(self, DB_Object=None):
		if DB_Object is None:
			self.dbf = vfs.join(CACHE, 'cached.db')
			self.db_lock = FileLock(self.dbf + '.lock')
			self.dbc = False
			self.dbh = False
			self.connect()
		else:
			self.DB=DB_Object
			def cache_response(url, response, cache_limit):
				if cache_limit == 0:
					return False
				self.DB.execute("REPLACE INTO request_cache(url, response) VALUES(?,?)", [url, response])
				self.DB.commit()
			self.cache_response = cache_response
			def get_cached_response(url, cache_limit):
				if cache_limit == 0:
					return False
				else:
					cache_limit = float(cache_limit) * 3600
				if cache_limit == -1:
					results = self.DB.query("SELECT response FROM cached_requests WHERE url=?", [url], force_double_array=False)
				elif callable(cache_limit):
					if cache_limit():
						results = self.DB.query("SELECT response FROM cached_requests WHERE url=?", [url], force_double_array=False)
					else: return False
				else:
					results = self.DB.query("SELECT response FROM cached_requests WHERE age < ? AND url=?", [cache_limit, url], force_double_array=False)
				if results:
					return results[0]
				return False
			self.get_cached_response = get_cached_response 

def __init__(self, host, dbname, username, password, port, version=1, quiet=False, connect=True):
		self.quiet=quiet
		self.db_type = 'mysql'
		self.lastrowid = None
		self.host = host
		self.dbname = dbname
		self.username=username
		self.password = password
		self.port = port
		self.db_version = version
		if connect: self._connect()
		if self.do_init(): self._initialize() 

def _connect(self):
		if self.quiet is False:
			kodi.log("Connecting to " + self.db_file)
		try:
			from sqlite3 import dbapi2 as database
			if self.quiet is False:
				kodi.log("%s loading sqlite3 as DB engine" % kodi.get_name())
		except:
			from pysqlite2 import dbapi2 as database
			if self.quiet is False:
				kodi.log("%s loading pysqlite2 as DB engine"  % kodi.get_name())
		if self.quiet is False:
			kodi.log("Connecting to SQLite on: " + self.db_file)
		directory = kodi.vfs.dirname(self.db_file)
		if not kodi.vfs.exists(directory): kodi.vfs.mkdir(directory)
		self.DBH = database.connect(self.db_file, check_same_thread=False)
		try:
			self.DBC = self.DBH.cursor()
			self.__connected = True	
		except Exception as e:
			self.handel_error(DatabaseException("SQLite Database Error: %s" % e))
			kodi.log("SQLite Database Error: %s" % e) 

def query_assoc(self, SQL, data=None, force_double_array=True, quiet=False):
		SQL = self.prepaire_sql(SQL)
		with self.db_lock:
			try:
				try:
					from sqlite3 import dbapi2 as database
				except:
					from pysqlite2 import dbapi2 as database
				DBH = database.connect(self.db_file, check_same_thread=False)
				DBH.row_factory = self.dict_factory
				cur = DBH.cursor()
				if data:
					cur.execute(SQL, data)
				else:
					cur.execute(SQL)
				rows = cur.fetchall()
				if(len(rows)==1 and not force_double_array):
					self.db_lock.release()
					return rows[0]
				else:
					self.db_lock.release()
					return rows
			except Exception as e:
				if self.quiet is False or quiet is False and not self.ignore_errors(e):
					self.handel_error(DatabaseException("SQLite Database Error: %s" % e))
					kodi.log("SQLite Database Error: %s" % e)
			finally:
				del DBH
				self.db_lock.release() 

def __init__(self, db_file='', version=1, quiet=False, connect=True):
		self.quiet=quiet
		self.db_type = 'sqlite'
		self.lastrowid = None
		self.db_file = db_file
		self.db_lock = filelock.FileLock(db_file + ".lock")
		self.db_version = version
		if connect: self._connect()
		if self.do_init(): self._initialize() 

def connect(self):
		if self.__connected is False: 
			self._connect() 

def __init__(self, credentials_file=None, charset='utf8',
                 port=3306, engine="mysql", **kwargs):
        """
        Initialise object with credentials file provided
        You can choose between providing file or connection details
        Available parameters:
          https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html
        """
        if credentials_file:
            with open(credentials_file, 'r') as f:
                self.settings = json.load(f)
            if 'port' not in self.settings:
                self.settings['port'] = port
            if 'charset' not in self.settings:
                self.settings['charset'] = charset
        # Merge with kwargs
        self.settings.update(**kwargs)
        self.engine = self.settings.pop('engine', engine)
        # @NOTE Charset parameter not supported in PostgreSQL
        if self.engine == 'postgres':
            self.settings.pop('charset', None)
        self._check_settings()
        self.connection = None
        self.cursor = None
        # Establish connection and set cursor
        self.connect() 

def database_connection_sqlite(host, port, db, user, password):
    if sqlite3:
        conn = sqlite3.connect(db)
        conn.create_function('regexp', 2, regex_matcher)
        return conn
    else:
        print('sqlite driver not available', file=sys.stderr)
        sys.exit(1) 

def bruter(user, passwd, ip, flag=False):
    try:
        con = mysql.connect(user=user, password=passwd, host=ip)
        flag = True
    except:
        pass
    return flag 

def __init__(self,user=mysql_user,password=mysql_password,host=mysql_host,port=mysql_port,db='mysql'):
        try:
            self.__user = user
            self.__password = password
            self.__host = host
            self.__port = port
            self.__db = db
            self.cnx = mysql.connect(user=self.__user,password=self.__password,host=self.__host,port=self.__port,db=self.__db)
            self.cur = self.cnx.cursor(buffered=True)
        except Exception as e:
            logging.error(e) 

def getSchemas():
    response.set_header("Access-Control-Allow-Origin", "*")
    db = mariadb.connect(user=Settings.MONITOR_DB['user'], password=Settings.MONITOR_DB['password'], database=Settings.MONITOR_DB['database'])
    #db.text_factory = lambda x: unicode(x, "utf-8", "ignore")
    schemas = collections.OrderedDict()

    dateStart = request.GET.get('dateStart', str(date('%Y-%m-%d'))).strip()
    dateEnd = request.GET.get('dateEnd', str(date('%Y-%m-%d'))).strip()

    query = """SELECT *
               FROM `schemas`
               WHERE `dateStats` BETWEEN %s AND %s
               ORDER BY `hits` DESC, `dateStats` ASC"""

    results = db.cursor()
    results.execute(query, (dateStart, dateEnd))

    for row in results:
        currentDate = row[2].strftime('%Y-%m-%d')
        if not currentDate in schemas.keys():
            schemas[currentDate] = collections.OrderedDict()

        schemas[currentDate][str(row[0])] = str(row[1])

    db.close()

    return simplejson.dumps(schemas) 

def getSoftwares():
    response.set_header("Access-Control-Allow-Origin", "*")
    db = mariadb.connect(user=Settings.MONITOR_DB['user'], password=Settings.MONITOR_DB['password'], database=Settings.MONITOR_DB['database'])
    softwares = collections.OrderedDict()

    dateStart = request.GET.get('dateStart', str(date('%Y-%m-%d'))).strip()
    dateEnd = request.GET.get('dateEnd', str(date('%Y-%m-%d'))).strip()

    query = """SELECT *
               FROM `softwares`
               WHERE `dateStats` BETWEEN %s AND %s
               ORDER BY `hits` DESC, `dateStats` ASC"""

    results = db.cursor()
    results.execute(query, (dateStart, dateEnd))

    for row in results:
        currentDate = row[2].strftime('%Y-%m-%d')
        if not currentDate in softwares.keys():
            softwares[currentDate] = collections.OrderedDict()

        softwares[currentDate][str(row[0])] = str(row[1])

    db.close()

    return simplejson.dumps(softwares) 

def __setstate__(self, state):
        self.__dict__.update(state)
        if isinstance(self._connection, str):
            self.connect() 

def connect(self):
        try:
            self._connection = mysql.connect(**self.settings)
        except mysql.Error as e:
            msg = 'Failure in connecting to database. Error: {0}'.format(e)
            raise DatabaseError(msg) 

def main():
    """The main program"""
    args = parse_args()
    items = []
    now = str(int(time()))

    cnx = connect(
        user=args.user,
        password=args.password,
        host=args.host,
        db=args.dbname,
        unix_socket=args.unix_socket,
    )
    cur = cnx.cursor()
    for query in args.queries:
        cur.execute(query)
        if not cur.rowcount:
            raise Exception('No result')
        rows = [dict(zip(cur.column_names, r)) for r in cur.fetchall()]
        if args.key_column:
            items.extend(get_row_data(rows, args.key_column))
        else:
            items.extend(get_column_data(rows))

    for key, value in items:
        print(args.prefix + '.' + str(key), value, now)

    cur.close()
    cnx.close() 

def main():
    args = parse_args()
    template = args.prefix + '.{}.{} {} ' + str(int(time()))

    db = connect(
        user=args.user,
        passwd=args.password,
        host=args.host,
        unix_socket=args.unix_socket,
    )
    cur = db.cursor()

    # Check for global status
    cur.execute('SHOW GLOBAL STATUS')
    for row in cur.fetchall():
        if row[1].isdigit():
            print(template.format('status', row[0], row[1]))

    cur.execute('SHOW VARIABLES')
    for row in cur.fetchall():
        if row[1].isdigit():
            print(template.format('variables', row[0], row[1]))

    # Find out how much space we can recover by Optimize
    sysdbs = {
        'information_schema',
        'mysql',
        'performance_schema',
        'sys',
        'test',
    }
    free = 0
    cur.execute('SHOW DATABASES')
    for row in cur.fetchall():
        if row[0] in sysdbs:
            continue
        cur.execute(
            'SELECT table_name, '
            'ROUND(data_free / 1024 / 1024), '
            'ROUND((data_length + index_length), 2) '
            'FROM information_schema.tables '
            'WHERE table_type = "BASE TABLE" '
            'AND table_schema = %s',
            [row[0]]
        )
        for value in cur.fetchall():
            print(template.format('table_size', value[0], value[2]))
            free += value[1]
    print(template.format('status', 'optimize_freeable', free)) 

def connect(self):
        """
        Creates connection to database, sets connection and cursor
        Connection to database can be loosed,
          if that happens you can use this function to reconnect to database
        """
        if self.engine == "mysql":
            found_connector = False
            try:
                # Import official mysql connector if exists
                import mysql.connector as mysql_module
                from mysql.connector import errorcode
                found_connector = True
            except ImportError:
                pass
            if not found_connector:
                # Check MySQLdb as secondary option
                try:
                    import MySQLdb as mysql_module
                except ImportError:
                    raise ValueError(
                        'Please, install mysql-connector or mysqlclient module before using this library.'
                    )
            # Connect to db
            try:
                self.connection = mysql_module.connect(**self.settings)
            except mysql_module.Error as err:
                if found_connector:
                    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                        raise ValueError("Wrong credentials, ACCESS DENIED")
                    elif err.errno == errorcode.ER_BAD_DB_ERROR:
                        raise ValueError(
                            "Database %s does not exists" % (self.settings['database'])
                        )
                    else:
                        raise ValueError(err)
                # @TODO Add detailed errors for MySQLdb
                raise err
        elif self.engine == "postgres":
            try:
                import psycopg2
            except ImportError:
                raise ValueError(
                    'Please, install psycopg2 module before using plugin.'
                )
            self.connection = psycopg2.connect(**self.settings)
        else:
            raise NotImplementedError(
                "Database engine %s not implemented!" % self.engine
            )

        self.cursor = self.connection.cursor() 

def execute(self, connections, output=sys.stdout,
                connector=mysql.connector, **kwrds):
        """Execute the search for objects

        This method searches for objects that match a search criteria for
        one or more servers.

        connections[in]    list of connection parameters
        output[in]         file stream to display information
                           default = sys.stdout
        connector[in]      connector to use
                           default = mysql.connector
        kwrds[in]          dictionary of options
          format           format for display
                           default = GRID
        """
        fmt = kwrds.get('format', "grid")
        charset = kwrds.get('charset', None)
        ssl_opts = kwrds.get('ssl_opts', {})
        entries = []
        for info in connections:
            conn = parse_connection(info)
            if not conn:
                msg = "'%s' is not a valid connection specifier" % (info,)
                raise FormatError(msg)
            if charset:
                conn['charset'] = charset
            info = conn
            conn['host'] = conn['host'].replace("[", "")
            conn['host'] = conn['host'].replace("]", "")

            if connector == mysql.connector:
                set_ssl_opts_in_connection_info(ssl_opts, info)

            connection = connector.connect(**info)

            if not charset:
                # If no charset provided, get it from the
                # "character_set_client" server variable.
                cursor = connection.cursor()
                cursor.execute("SHOW VARIABLES LIKE 'character_set_client'")
                res = cursor.fetchall()
                connection.set_charset_collation(charset=str(res[0][1]))
                cursor.close()

            cursor = connection.cursor()
            cursor.execute(self.__sql)
            entries.extend([tuple([_spec(info)] + list(row))
                            for row in cursor])

        headers = ["Connection"]
        headers.extend(col[0].title() for col in cursor.description)
        if len(entries) > 0 and output:
            print_list(output, fmt, headers, entries)
        else:
            msg = "Nothing matches '%s' in any %s" % \
                (self.__pattern, _join_words(self.__types, conjunction="or"))
            raise EmptyResultError(msg) 

def run_mysql(workload):
    """thread worker function"""
    logger.debug('current thread %s', threading.currentThread().getName())
    # get the connection information from the config
    connvals = parse_config()
    # if using IAM, set the password to the IAM token
    if connvals['iam']:
        mytoken=get_iam_mysql_token(connvals['host'], connvals['port'], connvals['user'])
    else:
        mytoken=connvals['token']
    # Connect to the database
    db = my.connect(host=connvals['host'],
                    user=connvals['user'],
                    password=mytoken,
                    db=connvals['db'],
                    ssl_ca=connvals['ssl']
                    )
    db.autocommit = True
    logger.debug('connecting to %s as %s', connvals['host'], connvals['user'])
    cursor = db.cursor()
    
    if workload == 'insert':
        sql = "INSERT INTO myschema.mytesttable (id_pk,random_string,random_number,reverse_string,row_ts) " \
              "VALUES(replace(uuid(),'-',''),concat(replace(uuid(),'-',''), replace(convert(rand(), char), '.', ''), " \
              "replace(convert(rand(), char), '.', '')),rand(),reverse(concat(replace(uuid(),'-',''), " \
              "replace(convert(rand(), char), '.', ''), replace(convert(rand(), char), '.', ''))),current_timestamp)"
        logger.debug('statement being issued %s', sql)
    else:
        workload = 'query'
        sql = "SELECT COUNT(*) as result_value FROM myschema.mytesttable WHERE random_number > rand() LIMIT 100"
        logger.debug('executing %s', sql)

    for i in range (100000):
        cursor.execute(sql)
        if workload == 'query':
            row = cursor.fetchall()
            logger.debug("fetched rows")
        # commit the rows periodically
        # write out a message indicating that progress is being made
        if i % 10000 == 0:
            logger.debug("completed %s executions and commit", str(i))
            db.commit()
    # commit the outstanding rows
    db.commit()
    db.close()
    return

# main program