I’m trying to create a new table in my SQL data base with the data from the data frame msft_data below. The msft_data, which is received by calling an API, looks like below. I’ve been trying for a while with chatgpt, google searches, etc. and keep running into errors. Would be very grateful if people can suggest a code that works to push data to my SQL server.
1. open 2. high 3. low 4. close 5. volume date
2024-01-19 395.76 398.6700 393.5000 398.67 29331136.0
2024-01-18 391.72 393.9900 390.1200 393.87 23392068.0
2024-01-17 387.98 390.1100 384.8100 389.47 22234108.0
2024-01-16 393.66 394.0300 387.6229 390.27 27202268.0
2024-01-12 385.49 388.6800 384.6500 388.47 21661153.0
... ... ... ... ... ...
2023-09-01 331.31 331.9900 326.7800 328.66 14942024.0
2023-08-31 329.20 330.9099 326.7800 327.76 26410954.0
2023-08-30 328.67 329.8100 326.4450 328.79 15222110.0
2023-08-29 321.88 328.9835 321.8800 328.41 19284590.0
2023-08-28 325.66 326.1500 321.7220 323.70 14808482.0
import pandas as pd
import matplotlib.pyplot as plt
import pyodbc
import sqlalchemy
#API SCRIPT BELOW ----------------------------------------------------
# create Alpha Vantage API access
from alpha_vantage.timeseries import TimeSeries
my_api_key = "#####"
# store last 100 days of stock prices
ts = TimeSeries(my_api_key, output_format="pandas")
msft_data, meta_data = ts.get_daily(symbol="MSFT", outputsize="compact")
print(msft_data)
# open connection to SQL
connection = pyodbc.connect("Driver={SQL Server};"
"Server=DESKTOP-MLSEKPL\SQLEXPRESS;"
"Database=tracker;"
"Trusted_Connection=yes;")
cursor = connection.cursor()
- Tried bunch of scripts suggested by chatgpt and got a number of different errors
- Also tried google searches e.g., stack overflow existing scripts and didn’t work for me
- Biggest issue is that i’m completely new to learning how to program so i’m sure there are obvious things that i missed
