python - Pandas DataFrame 和 SQL 未提交

Question

我在代码中 t 的第二次迭代中收到以下错误：

Traceback (most recent call last):
  File "D:\Python\GetSharePrices\main.py", line 10, in <module>
    for t in tickers:
pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

如果我删除最后两个语句，整个脚本会按预期运行。但是，当我尝试执行游标时，只有第一行被提交，然后我得到错误。

import yfinance as yf
import pyodbc as py


conn = py.connect('Driver={SQL Server}; Server=ORCHARD; Database=APPLE; Trusted_Connection=yes;')
cursor = conn.cursor()
sql = 'SELECT ticker FROM tickers'
tickers = cursor.execute(sql)
insert_sql = 'INSERT INTO share_price VALUES (?, ?, ?)'
for t in tickers:
    ticker = t[0]
    tickerData = yf.Ticker(ticker)
    tickerDf = tickerData.history(period='1d', start='2021-10-21', end='2021-10-21')
    for index, row in tickerDf.iterrows():
        price = row['Open']
        print(ticker, index, price)
        cursor.execute(insert_sql, (ticker, index, price))
    cursor.commit()

标签： pythonpandaspyodbc

Answer 1

你没有获取结果。做 -tickers = cursor.execute(sql).fetch_all()

假设表大小是合理的，否则一次获取一个块。