4
\$\begingroup\$

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 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()
\$\endgroup\$

1 Answer 1

Reset to default
1
\$\begingroup\$

How can I organize my code for a more Pythonic approach?

Pythonic, you ask? Sure thing.

Import

The ruff tool identified a couple unused imports (shutil and glob). The tool can automatically remove them using the --fix option:

import yfinance as yf
import pandas as pd
import sqlite3
import os
from pathlib import Path

Naming

The PEP 8 style guide recommends snake_case for variable names.

For example, NewFolderPath would be new_folder_path.

Simpler

This line:

if isFile is False:

is simpler and more commonly written as:

if not isFile:

Since that is the only place the isFile variable is used, we can eliminate it by combining these 2 lines:

isFile = os.path.exists(new_folder_path)
if not isFile:

as:

if not os.path.exists(new_folder_path):

Also, since you call mkdir that way, there is no reason to call the exists function. This is simpler:

new_folder_path  = r"C:\Databases\\"
Path(new_folder_path).mkdir(parents=True, exist_ok=True)

In that case, this can be deleted:

import os

Comments

The following comment can be deleted since it just restates the code after it:

#SQL Connect
conn = sqlite3.connect(new_folder_path+'hist_prices.db')

Documentation

The PEP 8 style guide recommends adding docstrings at the top of the code to summarize its purpose.

"""
List and download 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.
"""
\$\endgroup\$

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.