Pandas DataFrame and SQL not committing

Question

I'm getting the following error on the second iteration of t in tickers:

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.

If I remove the last two statements the entire script runs as expected. However, when I try to execute the cursor only the first row is committed before I get the error.

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()

Tom Ron · Accepted Answer · 2021-10-21 14:13:27Z

1

You didn't fetch the results. Do - tickers = cursor.execute(sql).fetch_all()

Assuming the table size is reasonable otherwise fetch a chunk at a time.

answered Oct 21, 2021 at 14:13

Tom Ron

6,2413 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas DataFrame and SQL not committing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related