无法使用请求从网页获取表格内容

Question

我使用请求库创建了一个脚本，以获取网页中可用的表格内容。当我使用这个link手动访问该站点时，我会看到一个页面，我需要首先点击AGREE按钮才能看到表格内容

这也是website link

我试图在chrome开发工具的网络部分仔细观察，并使用下面的脚本来访问内容。然而，我得到的只是以下内容，而根据开发工具，我应该得到一些json格式的表格内容

我得到的输出：

b'\n\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\n\n\n{}'

预期输出（截断）：

{T:{"Columns":[{"tradeQuantity":"1125000","quantityAsString":"1125000",

我试过：

import json
import requests

start_url = 'https://finra-markets.morningstar.com/BondCenter/BondTradeActivitySearchResult.jsp?'
link = 'https://finra-markets.morningstar.com/bondSearch.jsp'

qsp = {
    'ticker': 'C679131',
    'startdate': '10/03/2019',
    'enddate': '10/03/2020'
}

payload = {
    'postData': {'Keywords':[]},
    'ticker': 'C679131',
    'startDate': '',
    'endDate': '',
    'showResultsAs': 'B',
    'debtOrAssetClass': '',
    'spdsType': ''
}

params = {
    'count': '20',
    'sortfield': 'tradeDate',
    'sorttype': '2',
    'start': '0',
    'searchtype': 'T',
    'query': {"Keywords":[{"Name":"securityId","Value":"C679131"},{"Name":"tradeDate","minValue":"10/03/2019","maxValue":"10/03/2020"}]}
}

with requests.Session() as s:
    s.headers['User-Agent'] = 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.121 Safari/537.36'
    s.headers['Referer'] = 'https://finra-markets.morningstar.com/BondCenter/UserAgreement.jsp'
    r = s.post(start_url,params=qsp,data=payload)
    s.headers['Referer'] = 'https://finra-markets.morningstar.com/BondCenter/BondTradeActivitySearchResult.jsp?ticker=C679131&startdate=10%2F03%2F2019&enddate=10%2F03%2F2020'
    s.headers['X-Requested-With'] = 'XMLHttpRequest'
    r = s.post(link,json=params)
    print(r.status_code)
    print(r.content)

How can I get the tabular content from that webpage using requests?

Answer 1

您需要打电话到：

POST https://finra-markets.morningstar.com/finralogin.jsp

使用requests.Session()存储cookie时。此外，调用Referer头需要：

POST https://finra-markets.morningstar.com/bondSearch.jsp

之后，结果并不像baduker所指出的那样是JSON，您可以使用正则表达式对其进行重新建模：

import requests
from urllib import parse
import json
import re
import pandas as pd 

host = "https://finra-markets.morningstar.com"
path = "/BondCenter/BondTradeActivitySearchResult.jsp"

qsp = {
    'ticker': 'C679131',
    'startdate': '10/03/2019',
    'enddate': '10/03/2020'
}
s = requests.Session()

s.post("https://finra-markets.morningstar.com/finralogin.jsp",
    data = {
        "redirectPage": f"{path}?{parse.urlencode(qsp)}"
    }
)
r = s.post("https://finra-markets.morningstar.com/bondSearch.jsp",
    headers= {
        "Referer": f"{host}{path}?{parse.urlencode(qsp)}",
    },
    data = {
        "count": 20,
        "sortfield": "tradeDate",
        "sorttype": 2,
        "start": 0,
        "searchtype": "T",
        "query": json.dumps({
            "Keywords":[
                {"Name":"securityId","Value": qsp["ticker"]},
                {"Name":"tradeDate","minValue": qsp["startdate"],"maxValue":qsp["enddate"]}
            ]
        })
})

dataReg = re.search('{T:(.*)}', r.text, re.MULTILINE)
data = json.loads(dataReg.group(1))

df = pd.DataFrame(data["Columns"])

print(df)

Try this on repl.it

输出：

   tradeQuantity quantityAsString timeOfExecution settlementDate tradeModifier secondModifier specialPriceIndicator  ...  tradeDate symbol cusip callable commissionIndicator ATSIndicator remuneration
0        1125000          1125000        11:46:02      10/2/2020             _              _                     -  ...  10/2/2020   None  None     None                   N                         N
1          60000            60000        10:23:55      10/5/2020             _              _                     -  ...  10/1/2020   None  None     None                   N                         N
2          60000            60000        10:23:54      10/5/2020             _              _                     -  ...  10/1/2020   None  None     None                   M                         M
3         200000           200000        16:27:43      10/2/2020             _              _                     -  ...  9/30/2020   None  None     None                                              
4         200000           200000        16:27:43      10/2/2020             _              _                     -  ...  9/30/2020   None  None     None                   N                         N
5        2900000          2900000        15:39:16      10/2/2020             _              _                     -  ...  9/30/2020   None  None     None                   M                         M
6          20000            20000        12:24:48      10/2/2020             _              _                     -  ...  9/30/2020   None  None     None                   M                         M
.........

在Chrome开发者控制台中，在网络选项卡中，您可以右键单击：“标题选项/设置Cookies”，以快速捕获正在设置Cookies的呼叫

Answer 2

诀窍是用完全相同的头和cookie正确地模拟请求。我从开发者工具中获取了cookie原始字符串

以下是如何获取原始文本数据：

import json
from http.cookies import SimpleCookie
from urllib.parse import urlencode

import requests

link = 'https://finra-markets.morningstar.com/bondSearch.jsp'

payload = {
    'count': '20',
    'sortfield': 'tradeDate',
    'sorttype': '2',
    'start': '0',
    'searchtype': 'T',
    'query': {"Keywords": [{"Name": "securityId", "Value": "C679131"},
                           {"Name": "tradeDate", "minValue": "10/03/2019", "maxValue": "10/03/2020"}]}
}

cookies_raw_data = "__cfduid=db2d21a652ef313fcff3704bd87e839401602408581; qs_wsid=1CBF0E77A1169ED03A3EB86A6A8A991D; __cfruid=0ef7fb90b47b06df86311ff32918c0c9c441617d-1602408582; SessionID=1CBF0E77A1169ED03A3EB86A6A8A991D; UsrID=41151; UsrName=FINRA.QSAPIDEF@morningstar.com; Instid=FINRA; msFinraHasAgreed=true"
cookie = SimpleCookie()
cookie.load(cookies_raw_data)

cookies = {}
for key, morsel in cookie.items():
    cookies[key] = morsel.value

ref_payload = urlencode(dict(ticker="C679131", startdate="10/03/2019", enddate="10/03/2020"))

referer = f"https://finra-markets.morningstar.com/BondCenter/BondTradeActivitySearchResult.jsp?{ref_payload}"

headers = {
    "Accept": "text/plain, */*; q=0.01",
    "Accept-Encoding": "gzip, deflate, br",
    "Accept-Language": "en-US,en;q=0.9",
    "Connection": "keep-alive",
    "Content-Length": "278",
    "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
    "Host": "finra-markets.morningstar.com",
    "Origin": "https://finra-markets.morningstar.com",
    "Referer": referer,
    "Sec-Fetch-Dest": "empty",
    "Sec-Fetch-Mode": "cors",
    "Sec-Fetch-Site": "same-origin",
    "User-Agent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.75 Safari/537.36",
    "X-Requested-With": "XMLHttpRequest",
}

response = requests.post(link, data=urlencode(payload), headers=headers, cookies=cookies).text
print(response.strip())

输出：

{T:{"Columns":[{"tradeQuantity":"1125000","quantityAsString":"1125000","timeOfExecution":"11:46:02","settlementDate":"10/2/2020","tradeModifier":"_","secondModifier":"_","specialPriceIndicator":"-","asOfTrade":"-","reportingParty":"B","tradeStatus":"T","reportingPartyType":"D","contraPartyType":"C","securityId":"C679131","issueIdentifier":"EXC4479862","descriptionOfIssuer":"EXELON CORP","subproductType":"Corporate Bond","couponRate":3.497,"maturityDate":"06/01/2022","price":104.576,"yield":0.584,"tradeDate":"10/2/2020","symbol":null,"cusip":null,"callable":null,"commissionIndicator":"N","ATSIndicator":" ","remuneration":"N"},{"tradeQuantity":"60000","quantityAsString":"60000","timeOfExecution":"10:23:55","settlementDate":"10/5/2020","tradeModifier":"_","secondModifier":"_","specialPriceIndicator":"-","asOfTrade":"-","reportingParty":"S","tradeStatus":"T","reportingPartyType":"D",
and so on...

数据本身是一个纯文本，结果是无效的JSON。我不能马上解析它。经过几次尝试后，我意识到第一个键T不在"中，因此它不是作为有效的JSON传递的，但是。。。一个简单的黑客就成功了

要获取JSON对象，请使用以下命令（如果我找到一种不太老套的方法，我将编辑此命令）：

data = json.loads(response.strip()[3:-1])
for t in data['Columns']:
    print(f"{t['descriptionOfIssuer']} - {t['tradeQuantity']} - {t['price']}")

输出：

EXELON CORP - 1125000 - 104.576
EXELON CORP - 60000 - 104.642
EXELON CORP - 60000 - 104.618
EXELON CORP - 200000 - 104.612
EXELON CORP - 200000 - 104.612
EXELON CORP - 2900000 - 104.597
EXELON CORP - 20000 - 104.6
EXELON CORP - 225000 - 104.553
EXELON CORP - 64000 - 104.581
EXELON CORP - 64000 - 104.596
EXELON CORP - 50000 - 104.553
EXELON CORP - 2100000 - 104.634
EXELON CORP - 230000 - 104.551
EXELON CORP - 97000 - 104.566
EXELON CORP - 15000 - 104.551
EXELON CORP - 342000 - 104.582
EXELON CORP - 1400000 - 104.616
EXELON CORP - 200000 - 104.501
EXELON CORP - 200000 - 104.511
EXELON CORP - 220000 - 104.397

编辑：

为了证明即使是短期的（硬编码的）cookie也比根本没有数据好，这里有一个修改过的脚本版本，它为您要查找的股票代码生成一个数据转储

即使是那些该死的cookie，这也应该起作用，因为您请求的归档数据不太可能更改。因此，您可以获取它，保存它，然后继续

注意：如果我正在使用的cookie已经过时，只需将它们替换为Developer Tool -> XHR -> bondSearch.jsp -> Headers -> Request Headers -> Cookie中的任何值即可：

• __cfduid
• qs_wsid
• __cfruid
• SessionID（这始终与qs_wsid相同）

守则：

import json
import time
from urllib.parse import urlencode

import requests


ref_payload = urlencode(dict(ticker="C679131", startdate="10/03/2019", enddate="10/03/2020"))
referer = f"https://finra-markets.morningstar.com/BondCenter/BondTradeActivitySearchResult.jsp?{ref_payload}"

headers = {
    "Accept": "text/plain, */*; q=0.01",
    "Accept-Encoding": "gzip, deflate, br",
    "Accept-Language": "en-US,en;q=0.9",
    "Connection": "keep-alive",
    "Content-Length": "278",
    "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
    "Host": "finra-markets.morningstar.com",
    "Origin": "https://finra-markets.morningstar.com",
    "Referer": referer,
    "Sec-Fetch-Dest": "empty",
    "Sec-Fetch-Mode": "cors",
    "Sec-Fetch-Site": "same-origin",
    "User-Agent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.75 Safari/537.36",
    "X-Requested-With": "XMLHttpRequest",
}

cookies = {
    "__cfduid": "d1820cb5f1d1e8ec40513d0f8326ce1881602492151",
    "qs_wsid": "92CD4948C2AC7FCEC0989B34B86C1ADB",
    "__cfruid": "4dec9a2deb6d70c86ee5b8fa4046748994ef6254-1602492151}",
    "SessionID": "92CD4948C2AC7FCEC0989B34B86C1ADB",
    "UsrID": "41151",
    "UsrName": "FINRA.QSAPIDEF@morningstar.com",
    "Instid": "FINRA",
    "msFinraHasAgreed": "true",
}

start_counter = 0
final_output = []
while True:
    payload = {
        'count': '20',
        'sortfield': 'tradeDate',
        'sorttype': '2',
        'start': str(start_counter),
        'searchtype': 'T',
        'query': {
            "Keywords": [
                {"Name": "securityId", "Value": "C679131"},
                {"Name": "tradeDate", "minValue": "10/03/2019", "maxValue": "10/03/2020"},
            ]
        }
    }

    response = requests.post(
        'https://finra-markets.morningstar.com/bondSearch.jsp',
        data=urlencode(payload),
        headers=headers,
        cookies=cookies,
    ).text

    data = json.loads(response.strip()[3:-1])["Columns"]
    if data:
        print(f"Fetching data for counter {start_counter}...")
        final_output.extend(data)
        start_counter += 20
    else:
        break

with open(f"data_dump_securityID_C679131.json", "w") as d:
    json.dump(final_output, d, indent=4, sort_keys=True)