Python pymysql.cursors() Examples

def get_online_symbol_data(database_id):
    """Get from the server."""
    import pymysql
    import pymysql.cursors

    cfg = get_database_configuration()
    mysql = cfg["mysql_online"]
    connection = pymysql.connect(
        host=mysql["host"],
        user=mysql["user"],
        passwd=mysql["passwd"],
        db=mysql["db"],
        cursorclass=pymysql.cursors.DictCursor,
    )
    cursor = connection.cursor()
    sql = (
        "SELECT `id`, `formula_in_latex`, `unicode_dec`, `font`, "
        "`font_style` FROM  `wm_formula` WHERE  `id` =%i"
    ) % database_id
    cursor.execute(sql)
    datasets = cursor.fetchall()
    if len(datasets) == 1:
        return datasets[0]
    else:
        return None 

def __init__(self, host, user, passwd, db=None, port=3306, charset='utf8', init_command='SET NAMES UTF8',
                 cursorclass=cursors.Cursor, use_unicode=True, autocommit=False):
        self.host = host
        self.port = int(port)
        self.user = user
        self.passwd = passwd
        self.db = db
        self.cursorclass = cursorclass
        self.charset = charset
        self.init_command = init_command
        self.use_unicode = use_unicode
        self.autocommit_mode = bool(autocommit)
        self.connection = self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user,
                                                      passwd=self.passwd, db=self.db, charset=charset,
                                                      init_command=init_command, cursorclass=self.cursorclass,
                                                      use_unicode=self.use_unicode, autocommit=self.autocommit_mode)
        self.cursor = self.cur = self.conn.cursor()
        self.debug = False 

def saveData(self, category):
        import pymysql.cursors
        import pymysql
        # Connect to the database
        connection = pymysql.connect(host='localhost',
                                     user='root',
                                     password='',
                                     db='hht',
                                     charset='utf8mb4',
                                     cursorclass=pymysql.cursors.DictCursor)

        try:

            #  当前分类
            with connection.cursor() as cursor:
                # Read a single record
                sql = "SELECT `id`, `typename`, `py` FROM `type` WHERE `py` = %s"
                cursor.execute(sql, category)
                typeArr = cursor.fetchone()


            # 写入本分类下所有内容
            with connection.cursor() as cursor:
                for resOne in self._res:
                    sql = "INSERT INTO `res` (`type_id`, `name`, `link`) VALUES (%s, %s, %s)"
                    cursor.execute(sql, (typeArr['id'], resOne['name'], resOne['res']))

            connection.commit()
            print 'Insert ' + str(len(self._res)) + ' datas with category ' + typeArr['typename']
            del typeArr


        finally:
            connection.close()
        return

    # 线程状态 

def _fetch_data_from_server(raw_data_id, mysql_cfg):
    """Get the data from raw_data_id from the server.
    :returns: The ``data`` if fetching worked, ``None`` if it failed."""
    import pymysql
    import pymysql.cursors

    # Import configuration file
    cfg = utils.get_database_configuration()
    if cfg is None:
        return None

    # Establish database connection
    connection = pymysql.connect(
        host=cfg[mysql_cfg]["host"],
        user=cfg[mysql_cfg]["user"],
        passwd=cfg[mysql_cfg]["passwd"],
        db=cfg[mysql_cfg]["db"],
        cursorclass=pymysql.cursors.DictCursor,
    )
    logger.info(f"Connection: {connection}")
    cursor = connection.cursor()

    # Download dataset
    sql = ("SELECT `id`, `data` " "FROM `wm_raw_draw_data` WHERE `id`=%i") % raw_data_id
    cursor.execute(sql)
    return cursor.fetchone() 

def insert_symbol_mapping(raw_data_id, symbol_id, user_id, strokes):
    """
    Insert data into `wm_strokes_to_symbol`.

    Parameters
    ----------
    raw_data_id : int
    user_id : int
    strokes: list of int
    """
    mysql = utils.get_mysql_cfg()
    connection = pymysql.connect(
        host=mysql["host"],
        user=mysql["user"],
        passwd=mysql["passwd"],
        db=mysql["db"],
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
    )
    cursor = connection.cursor()
    sql = (
        "INSERT INTO `wm_partial_answer` "
        "(`recording_id`, `symbol_id`, `strokes`, `user_id`, "
        "`is_accepted`) "
        "VALUES (%s, %s, %s, %s, 1);"
    )
    data = (
        raw_data_id,
        symbol_id,
        ",".join([str(stroke) for stroke in strokes]),
        user_id,
    )
    cursor.execute(sql, data)
    connection.commit() 

def __init__(self, connection):
        self.shared_connection = connection
        self.shared_connection.cursorclass = pymysql.cursors.DictCursor

        self.revisions = Revisions(self)
        """
        An instance of :class:`mw.database.Revisions`.
        """

        self.archives = Archives(self)
        """
        An instance of :class:`mw.database.Archives`.
        """

        self.all_revisions = AllRevisions(self)
        """
        An instance of :class:`mw.database.AllRevisions`.
        """

        self.pages = Pages(self)
        """
        An instance of :class:`mw.database.Pages`.
        """

        self.users = Users(self)
        """
        An instance of :class:`mw.database.Users`.
        """ 

def from_params(cls, *args, **kwargs):
        """
        Constructs a :class:`~mw.database.DB`.  Passes `*args` and `**kwargs`
        to :meth:`oursql.connect` and configures the connection.

        :Parameters:
            args : :class:`argparse.Namespace`
                A collection of argument values returned by :class:`argparse.ArgumentParser`'s :meth:`parse_args()`
        """
        kwargs['cursorclass'] = pymysql.cursors.DictCursor
        if kwargs['db']:
            kwargs['database'] = kwargs['db']
            del kwargs['db']
        connection = pymysql.connect(*args, **kwargs)
        return cls(connection) 

def get_connection(self, cursor=None, **kwargs):
        try:
            return MySQLdb.connect(
                host=self.param['host'],
                user=self.param['user'],
                port=self.param['port'],
                passwd=self.param['password'],
                db=self.param['db'],
                cursorclass=getattr(cursors, cursor or self.default_cursor),
                connect_timeout=12,
                **kwargs)
        except:
            return None 

def get_connection(self, name, cursor='SSDictCursor', **kwargs):
        if self.access[name] is None:
            self.read_config(name)
        if self.access[name] is None:
            self.access[name] = None
            error = 'Configuration missing to access MySQL for %s' % (name)
            if self.log is not None:
                self.log.msq(2, error, 'ERROR')
            else:
                common.console(error)
        else:
            try:
                con = MySQLdb.connect(
                    host=self.access[name]['host'],
                    user=self.access[name]['user'],
                    port=self.access[name]['port'],
                    passwd=self.access[name]['password'],
                    db=self.access[name]['db'],
                    cursorclass=getattr(cursors, cursor),
                    connect_timeout=self.timeout,
                    **kwargs)
                return con
            except:
                error = 'Failed to connect MySQL `%s\'.' % name
                if self.log is not None:
                    self.log.msg(2, error, 'ERROR')
                else:
                    common.console(error)
                return None 

def get(self, table, column, join=None, where=None, insert=False, ifnone=None):
        """
        A simplified method of select, for getting the first result in one column only. A common case of using this
        method is getting id.
        :type table: string
        :type column: str
        :type join: dict
        :type where: dict
        :type insert: bool
        :param insert: If insert==True, insert the input condition if there's no result and return the id of new row.
        :type ifnone: string
        :param ifnone: When ifnone is a non-empty string, raise an error if query returns empty result. insert parameter
                       would not work in this mode.
        """
        select_result = self.select(table=table, columns=[column], join=join, where=where, limit=1)

        if self.debug:
            return select_result

        result = select_result[0] if select_result else None

        if result:
            return result[0 if self.cursorclass is pymysql.cursors.Cursor else column]

        if ifnone:
            raise ValueError(ifnone)

        if insert:
            if any([isinstance(d, dict) for d in where.values()]):
                raise ValueError("The where parameter in get() doesn't support nested condition with insert==True.")
            return self.insert(table=table, value=where)

        return None 

def now(self):
        query = "SELECT NOW() AS now;"
        if self.debug:
            return query

        self.cur.execute(query)
        return self.cur.fetchone()[0 if self.cursorclass is pymysql.cursors.Cursor else 'now'].strftime(
                "%Y-%m-%d %H:%M:%S") 

def last_insert_id(self):
        query = "SELECT LAST_INSERT_ID() AS lid;"
        if self.debug:
            return query

        self.query(query)
        return self.cur.fetchone()[0 if self.cursorclass is pymysql.cursors.Cursor else 'lid'] 

def connect(self):
		await self.instance.db.connect()
		await self.instance.apps.discover()
		await self.instance.db.initiate()

		if self.db_type != 'mysql':
			raise Exception('We only support mysql converting right now!')

		self.connection = pymysql.connect(
			host=self.db_host, user=self.db_user, password=self.db_password, db=self.db_name, charset=self.charset,
			port=self.db_port or 3306,
			cursorclass=pymysql.cursors.DictCursor
		) 

def getuserid(username, copyright_str):
    """Get the ID of the user with `username` from write-math.com. If he
    doesn't exist by now, create it. Add `copyright_str` as a description.

    Parameters
    ----------
    username : string
        Name of a user.
    copyright_str : string
        Description text of a user in Markdown format.

    Returns
    -------
    int :
        ID on write-math.com of the user.
    """
    global username2id
    if username not in username2id:
        mysql = utils.get_mysql_cfg()
        connection = pymysql.connect(
            host=mysql["host"],
            user=mysql["user"],
            passwd=mysql["passwd"],
            db=mysql["db"],
            charset="utf8mb4",
            cursorclass=pymysql.cursors.DictCursor,
        )
        cursor = connection.cursor()

        sql = (
            "INSERT IGNORE INTO  `wm_users` ("
            "`display_name` , "
            "`password` ,"
            "`account_type` ,"
            "`confirmation_code` ,"
            "`status` ,"
            "`description`"
            ") "
            "VALUES ("
            "%s, '',  'Regular User',  '',  'activated', %s"
            ");"
        )
        cursor.execute(sql, (username, copyright_str))
        connection.commit()

        # Get the id
        try:
            sql = "SELECT  `id` FROM  `wm_users` " "WHERE  `display_name` =  %s LIMIT 1"
            cursor.execute(sql, username)
            uid = cursor.fetchone()["id"]
        except Exception as inst:
            logging.debug("username not found: %s", username)
            print(inst)
        # logging.info("%s: %s", username, uid)
        username2id[username] = uid
    return username2id[username] 

def insert_recording(hw):
    """Insert recording `hw` into database."""
    mysql = utils.get_mysql_cfg()
    connection = pymysql.connect(
        host=mysql["host"],
        user=mysql["user"],
        passwd=mysql["passwd"],
        db=mysql["db"],
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
    )
    try:
        cursor = connection.cursor()
        sql = (
            "INSERT INTO `wm_raw_draw_data` ("
            "`user_id`, "
            "`data`, "
            "`md5data`, "
            "`creation_date`, "
            "`device_type`, "
            "`accepted_formula_id`, "
            "`secret`, "
            "`ip`, "
            "`segmentation`, "
            "`internal_id`, "
            "`description` "
            ") VALUES (%s, %s, MD5(data), "
            "%s, %s, %s, %s, %s, %s, %s, %s);"
        )
        data = (
            hw.user_id,
            hw.raw_data_json,
            getattr(hw, "creation_date", None),
            getattr(hw, "device_type", ""),
            getattr(hw, "formula_id", None),
            getattr(hw, "secret", ""),
            getattr(hw, "ip", None),
            str(getattr(hw, "segmentation", "")),
            getattr(hw, "internal_id", ""),
            getattr(hw, "description", ""),
        )
        cursor.execute(sql, data)
        connection.commit()
        for symbol_id, strokes in zip(hw.symbol_stream, hw.segmentation):
            insert_symbol_mapping(cursor.lastrowid, symbol_id, hw.user_id, strokes)
        logging.info("Insert raw data.")
    except pymysql.err.IntegrityError as e:
        print(f"Error: {e} (can probably be ignored)") 

def select(self, table, columns=None, join=None, where=None, group=None, having=None, order=None, limit=None,
               iterator=False, fetch=True):
        """
        :type table: string
        :type columns: list
        :type join: dict
        :param join: {'[>]table1(t1)': {'user.id': 't1.user_id'}} -> "LEFT JOIN table AS t1 ON user.id = t1.user_id"
        :type where: dict
        :type group: string|list
        :type having: string
        :type order: string|list
        :type limit: int|list
        # TODO: change to offset
        :param limit: The max row number for this query.
                      If it contains offset, limit must be a list like [offset, limit]
        :param iterator: Whether to output the result in a generator. It always returns generator if the cursor is
                         SSCursor or SSDictCursor, no matter iterator is True or False.
        :type fetch: bool
        """
        if not columns:
            columns = ['*']
        where_q, _args = self._where_parser(where)

        # TODO: support multiple table

        _sql = ''.join(['SELECT ', self._backtick_columns(columns),
                        ' FROM ', self._tablename_parser(table)['formatted_tablename'],
                        self._join_parser(join),
                        where_q,
                        (' GROUP BY ' + self._by_columns(group)) if group else '',
                        (' HAVING ' + having) if having else '',
                        (' ORDER BY ' + self._by_columns(order)) if order else '',
                        self._limit_parser(limit), ';'])

        if self.debug:
            return self.cur.mogrify(_sql, _args)

        execute_result = self.cur.execute(_sql, _args)

        if not fetch:
            return execute_result

        if self.cursorclass in (pymysql.cursors.SSCursor, pymysql.cursors.SSDictCursor):
            return self.cur

        if iterator:
            return self._yield_result()

        return self.cur.fetchall()