如何从PostgreSQL仅获取查询值，而非表名及索引信息？

问题场景 #

你使用以下代码从PostgreSQL的premier_league表查询club_url并存入DataFrame：

dataFrame = pds.read_sql("SELECT club_url FROM premier_league WHERE club_name LIKE '%%Chel%%'", dbConnection)

直接将该DataFrame作为URL传入requests.get()时，触发InvalidSchema错误，原因是传入的内容包含了列名club_url和索引0，并非纯URL字符串。

完整报错信息：

"C:\Users\zszen\PycharmProjects\venv\Football\Scripts\import_players_csv.py", line 23, in <module>
    r = s.get(url)
  File "C:\Users\zszen\PycharmProjects\venv\Football\lib\site-packages\requests\sessions.py", line 600, in get
    return self.request("GET", url, **kwargs)
  File "C:\Users\zszen\PycharmProjects\venv\Football\lib\site-packages\requests\sessions.py", line 587, in request
    resp = self.send(prep, **send_kwargs)
  File "C:\Users\zszen\PycharmProjects\venv\Football\lib\site-packages\requests\sessions.py", line 695, in send
    adapter = self.get_adapter(url=request.url)
  File "C:\Users\zszen\PycharmProjects\venv\Football\lib\site-packages\requests\sessions.py", line 792, in get_adapter
    raise InvalidSchema(f"No connection adapters were found for {url!r}")
requests.exceptions.InvalidSchema: No connection adapters were found for 'club_url
0  https://www.transfermarkt.co.uk/fc-chelsea/sta...'
                                            club_url
0  https://www.transfermarkt.co.uk/fc-chelsea/sta...

解决方法 #

1. 提取单行单列的纯值（查询结果仅一条时） #

若查询结果只有一条数据，直接从DataFrame中提取对应单元格的字符串值：

# 方法1：通过位置索引提取
url = dataFrame.iloc[0, 0]
# 方法2：通过列名+位置索引提取
url = dataFrame['club_url'].iloc[0]
# 方法3：通过标签索引提取
url = dataFrame.at[0, 'club_url']

将上述提取的url传入requests.get()即可正常请求。

2. 处理多行查询结果 #

若查询结果包含多条URL，将列数据转换为纯字符串列表后逐个请求：

url_list = dataFrame['club_url'].tolist()
for url in url_list:
    r = s.get(url)
    # 此处添加你的后续处理逻辑

内容的提问来源于stack exchange，提问作者kesbetik