Python mysql.insert方法代碼示例

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def Save_Topic_Words(model,feature_names, uid,n_top_words=20):
    _,engine=Connect('../conf.yaml')
    conn = engine.connect()
    metadata = MetaData(engine)
    wb_topic = Table('wb_topic', metadata, autoload=True)
    for topic_idx, topic in enumerate(model.components_):
        topics=topic_idx                                            #主題
        topic_conts=([feature_names[i]
                        for i in topic.argsort()[:-n_top_words - 1:-1]])#主題
        print("Topic #%d:" % topics)
        print(topic_conts)
        for topic_cont in topic_conts:
            ins = insert(wb_topic).values(uid=uid,topic=topics,topic_cont=topic_cont)
            ins = ins.on_duplicate_key_update(
                topic=topics
            )
            conn.execute(ins)

    conn.close()

#使用LDA進行微博動態主題建模與分析 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def create_upsert_mysql(table, record):
    """Creates a statement for inserting the passed record to the passed
    table; if the record already exists, the existing record will be updated.
    This uses MySQL `on_duplicate_key_update` (hence upsert), and that
    why the returned statement is valid only for MySQL tables. Refer to this
    `SqlAlchemy MySQL documentation`_ for more information.

    The created statement is not executed by this function.

    Args:
        table (sqlalchemy.sql.schema.Table): database table metadata.
        record (dict): a data record, corresponding to one row, to be inserted.

    Returns:
        sqlalchemy.sql.dml.Insert: a statement for inserting the passed
        record to the specified table.

    .. _SqlAlchemy MySQL documentation:
       https://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql-inser-on-duplicate-key-update
    """
    insert_stmt = mysql_insert(table).values(record)
    return insert_stmt.on_duplicate_key_update(**record)
    # passing dict, i.e. ...update(record), isn't working 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def insert_data(self, id, last_time, hostname, ip, status):
        insert_stmt = insert(Host). \
        values(id=id, last_time=last_time, hostname=hostname, ip=ip, status=status)

        on_conflict_stmt = insert_stmt.on_duplicate_key_update(
            last_time=last_time, status=status)
        try:
            self.session.execute(on_conflict_stmt)
            self.session.commit()
            return True
        except InvalidRequestError:
            self.session.rollback()
        except Exception as e:
            print(e)
        finally:
            self.session.close() 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def test_bad_args(self):
        assert_raises(
            ValueError,
            insert(self.tables.foos, values={}).on_duplicate_key_update,
        )
        assert_raises(
            exc.ArgumentError,
            insert(self.tables.foos, values={}).on_duplicate_key_update,
            {"id": 1, "bar": "b"},
            id=1,
            bar="b",
        )
        assert_raises(
            exc.ArgumentError,
            insert(self.tables.foos, values={}).on_duplicate_key_update,
            {"id": 1, "bar": "b"},
            {"id": 2, "bar": "baz"},
        ) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def test_on_duplicate_key_update_expression(self):
        foos = self.tables.foos
        with testing.db.connect() as conn:
            conn.execute(insert(foos, dict(id=1, bar="b", baz="bz")))
            stmt = insert(foos).values(
                [dict(id=1, bar="ab"), dict(id=2, bar="b")]
            )
            stmt = stmt.on_duplicate_key_update(
                bar=func.concat(stmt.inserted.bar, "_foo")
            )
            result = conn.execute(stmt)
            eq_(result.inserted_primary_key, (2,))
            eq_(
                conn.execute(foos.select().where(foos.c.id == 1)).fetchall(),
                [(1, "ab_foo", "bz", False)],
            ) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def test_last_inserted_id(self):
        foos = self.tables.foos
        with testing.db.connect() as conn:
            stmt = insert(foos).values({"bar": "b", "baz": "bz"})
            result = conn.execute(
                stmt.on_duplicate_key_update(
                    bar=stmt.inserted.bar, baz="newbz"
                )
            )
            eq_(result.inserted_primary_key, (1,))

            stmt = insert(foos).values({"id": 1, "bar": "b", "baz": "bz"})
            result = conn.execute(
                stmt.on_duplicate_key_update(
                    bar=stmt.inserted.bar, baz="newbz"
                )
            )
            eq_(result.inserted_primary_key, (1,)) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def test_update_sql_expr(self):
        stmt = insert(self.table).values(
            [{"id": 1, "bar": "ab"}, {"id": 2, "bar": "b"}]
        )
        stmt = stmt.on_duplicate_key_update(
            bar=func.coalesce(stmt.inserted.bar),
            baz=stmt.inserted.baz + "some literal",
        )
        expected_sql = (
            "INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) ON "
            "DUPLICATE KEY UPDATE bar = coalesce(VALUES(bar)), "
            "baz = (concat(VALUES(baz), %s))"
        )
        self.assert_compile(
            stmt,
            expected_sql,
            checkparams={
                "id_m0": 1,
                "bar_m0": "ab",
                "id_m1": 2,
                "bar_m1": "b",
                "baz_1": "some literal",
            },
        ) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def set_update_state(self, entity_id: int, row: Any) -> None:
        t = self.UpdateState.__table__
        values = dict(pts=row.pts, qts=row.qts, date=row.date.timestamp(),
                      seq=row.seq, unread_count=row.unread_count)
        with self.engine.begin() as conn:
            conn.execute(insert(t)
                         .values(session_id=self.session_id, entity_id=entity_id, **values)
                         .on_duplicate_key_update(**values)) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def process_entities(self, tlo: Any) -> None:
        rows = self._entities_to_rows(tlo)
        if not rows:
            return

        t = self.Entity.__table__
        with self.engine.begin() as conn:
            for row in rows:
                values = dict(hash=row[1], username=row[2], phone=row[3], name=row[4])
                conn.execute(insert(t)
                             .values(session_id=self.session_id, id=row[0], **values)
                             .on_duplicate_key_update(**values)) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def cache_file(self, md5_digest: str, file_size: int,
                   instance: Union[InputDocument, InputPhoto]) -> None:
        if not isinstance(instance, (InputDocument, InputPhoto)):
            raise TypeError("Cannot cache {} instance".format(type(instance)))

        t = self.SentFile.__table__
        values = dict(id=instance.id, hash=instance.access_hash)
        with self.engine.begin() as conn:
            conn.execute(insert(t)
                         .values(session_id=self.session_id, md5_digest=md5_digest,
                                 type=_SentFileType.from_type(type(instance)).value,
                                 file_size=file_size, **values)
                         .on_duplicate_key_update(**values)) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def test_on_duplicate_key_update(self):
        foos = self.tables.foos
        with testing.db.connect() as conn:
            conn.execute(insert(foos, dict(id=1, bar="b", baz="bz")))
            stmt = insert(foos).values(
                [dict(id=1, bar="ab"), dict(id=2, bar="b")]
            )
            stmt = stmt.on_duplicate_key_update(bar=stmt.inserted.bar)
            result = conn.execute(stmt)
            eq_(result.inserted_primary_key, (2,))
            eq_(
                conn.execute(foos.select().where(foos.c.id == 1)).fetchall(),
                [(1, "ab", "bz", False)],
            ) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def test_on_duplicate_key_update_null(self):
        foos = self.tables.foos
        with testing.db.connect() as conn:
            conn.execute(insert(foos, dict(id=1, bar="b", baz="bz")))
            stmt = insert(foos).values(
                [dict(id=1, bar="ab"), dict(id=2, bar="b")]
            )
            stmt = stmt.on_duplicate_key_update(updated_once=None)
            result = conn.execute(stmt)
            eq_(result.inserted_primary_key, (2,))
            eq_(
                conn.execute(foos.select().where(foos.c.id == 1)).fetchall(),
                [(1, "b", "bz", None)],
            ) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def test_from_literal(self):
        stmt = insert(self.table).values(
            [{"id": 1, "bar": "ab"}, {"id": 2, "bar": "b"}]
        )
        stmt = stmt.on_duplicate_key_update(bar=literal_column("bb"))
        expected_sql = (
            "INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) "
            "ON DUPLICATE KEY UPDATE bar = bb"
        )
        self.assert_compile(stmt, expected_sql) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def test_python_values(self):
        stmt = insert(self.table).values(
            [{"id": 1, "bar": "ab"}, {"id": 2, "bar": "b"}]
        )
        stmt = stmt.on_duplicate_key_update(bar="foobar")
        expected_sql = (
            "INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) "
            "ON DUPLICATE KEY UPDATE bar = %s"
        )
        self.assert_compile(stmt, expected_sql) 

# 需要導入模塊: from sqlalchemy.dialects import mysql [as 別名]
# 或者: from sqlalchemy.dialects.mysql import insert [as 別名]
def getmain(res,uid,table,conn,url,user_agents, cookies,conf,use_proxies=False):
    dynamic = re.compile(r'.*?><span class="ctt">(.*?)<a href', re.S)#匹配動態
    times = re.compile(r'.*?<span class="ct">(.*?)&nbsp',re.S)#匹配動態發布時間
    page_number = re.compile(r'.*/(\d*?)頁</div>',re.S)#匹配動態頁數
    re_nbsp = re.compile(r'&nbsp',re.S) #去除$nbsp
    re_html = re.compile(r'</?\w+[^>]*>',re.S) #去除html標簽
    re_200b = re.compile(r'\u200b',re.S) #去除分隔符
    re_quot = re.compile(r'&quot',re.S)
    dys = re.findall(dynamic,res.text)
    ts = re.findall(times,res.text)
    pages = re.findall(page_number,res.text)
    if(len(pages) <= 0):
        print('\033[1;31mERROR!!! uid:'+str(uid)+' does not have page_number tags. Skip this uid...\033[0m')
        return
    pagenums=pages[0]
    print(pagenums)

    mainurl=url
    label = 0 #標簽用於計數，每十次延時10S
    for pagenum in range(int(pagenums))[1:]:
        if(label ==10 ):
            time.sleep(10)
            label = 0
        # 隨機選擇，防止被ban
        cookie = random.choice(cookies)
        cookie = getcookies(cookie)
        headers = {
            'User_Agent': random.choice(user_agents)
        }
        pagenum+=1
        label += 1
        url=mainurl+'?page='+str(pagenum)
        page=gethtml(url,headers,cookie,conf,use_proxies)
        dys += re.findall(dynamic,page.text)
        ts += re.findall(times,page.text)
    dys = dys[1:]
    print(len(dys))
    print(len(ts))
    for i in range(len(ts)):
        dys[i] = re_nbsp.sub('', dys[i])
        dys[i] = re_html.sub('', dys[i])
        dys[i] = re_200b.sub('', dys[i])
        dys[i] = re_quot.sub('', dys[i])
        ins = insert(table).values(uid=uid,weibo_cont=pymysql.escape_string(dys[i]),create_time=ts[i])
        ins = ins.on_duplicate_key_update(weibo_cont=pymysql.escape_string(dys[i]))
        conn.execute(ins)

#默認不使用代理ip