I have a program that lists and downloads a year of history of two stock tickers (AMZN and GOOG). I download these to a SQL database, C:\Databases\hist_prices.db, inside a table called hist_prices_tickers.
How can I organize my code for a more Pythonic approach?
I intend to use this SQL database for a machine learning-learning project later on. Is this method of calling the database the best way possible?
import yfinance as yf
import pandas as pd
import sqlite3, shutil, glob, os
from pathlib import Path
NewFolderPath = r"C:\Databases\\"
isFile = os.path.exists(NewFolderPath)
if isFile is False:
Path(NewFolderPath).mkdir(parents=True, exist_ok=True)
df = pd.DataFrame()
df2 = pd.DataFrame()
tickers = ["AMZN","GOOG"]
#SQL Connect
conn = sqlite3.connect(NewFolderPath+'hist_prices.db')
c = conn.cursor()
dest = sqlite3.connect(':memory:')
conn.backup(dest)
conn.commit()
for ticker in tickers:
tkr = yf.Ticker(ticker)
hist = tkr.history(period="1y")
df2 = df2.append(hist)
df2['Ticker'] = ticker
df = df.append(df2.reset_index())
df.fillna(0, inplace=True)
df = df.applymap(str)
df = df.apply(lambda x: x.str.replace('.', ','))
df['Date'] = df['Date'].apply(pd.to_datetime)
df.to_sql('hist_prices_tickers', conn, index=False, if_exists='append')
df = pd.read_sql_query("SELECT * from hist_prices_tickers", conn)
df.drop_duplicates(subset=None, keep="last", inplace=True)
df.to_sql('hist_prices_tickers', conn, if_exists='replace', index=False)
print(df)
conn.close()
