This is my first real excursion into Python, coming from C#. I wanted to see if my code is well-formatted and efficient.

The following will read the needed codes from a Database Table, then submit the codes to an API, store them in individual objects, and then dump them back into another table in the Database.

import ondemand
import pyodbc
import datetime

#Code purposes
#1.) Get the desired barchart codes for the year
#2.) Load quotes into an array from Barchart API
#3.) Submit quotes to database for today's date

#Qute Class - Stores the quotes as objects
class Quote():
    def __init__(self, seq_dimDate, symbol, month, year, price, crop, ficalYear):
        self.seq_dimDate = seq_dimDate
        self.symbol = symbol
        self.month = month
        self.year = year
        self.price = price
        self.crop = crop
        self.fiscalYear = ficalYear

#Establish a connection to the database
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=MyComputer\\SQLEXPRESS;DATABASE=MarketDB')
cursor = cnxn.cursor()

#get the Date and Fiscal Year
now = datetime.datetime.now().strftime('%m/%d/%Y')
dateRow = cursor.execute('SELECT * from dbo.dimDate WHERE [Date] = ?', now).fetchone()
seqDimDate = dateRow.Sequence
fiscalYear = dateRow.FiscalYear
print('Sequence for %s is %s with a fiscal year of %s' % (now, seqDimDate, fiscalYear))

#Query the Barchart Codes Table
cursor.execute('SELECT * FROM dbo.Codes WHERE [Year] = %s' % (fiscalYear))

#Generate a list of codes to send to API
quotesToQuery = ''

for row in cursor.fetchall():
    for x in range(3,len(row)):
        quotesToQuery = quotesToQuery + ',' + row[x]

#Open connection to the API
od = ondemand.OnDemandClient(api_key='123', end_point='https://marketdata.websol.barchart.com/')

#Get quotes
quotes = od.quote(quotesToQuery)['results']
lQuotes = []

#load the quotes into our array of objects
for q in quotes:
    #Get the Symbol, month, year and price of the quote
    symbol = q['symbol']
    dt = datetime.datetime.strptime(q['tradeTimestamp'], '%Y-%m-%dT00:00:00-%H:%M')
    month = dt.month
    year = dt.year
    price = q['lastPrice']

    #Get the Sequence for the Crop
    if q['name'] == 'Corn':
        crop = 1
        price = price / 100
    elif q['name'] == 'Soybean Meal':
        crop = 2
        crop = 3

    #create a new Quote object and add it to the collection
    nq = Quote(seqDimDate, symbol, month, year, price, crop, fiscalYear)

#Double Check of Quotes
print('%s total Quotes Collected' % (len(lQuotes)))

#Delete any Prices in the Database for this date sequence
cursor.execute('DELETE FROM dbo.MarketPrice WHERE Seq_dimDate = ?', seqDimDate)
print('MarketPrice table cleared of records with Date Sequence %s...' % (seqDimDate)) #confirm records were deleted

#Submit the Quotes to the Database
for n in lQuotes:
    cursor.execute('INSERT INTO dbo.MarketPrice (Seq_dimDate, Seq_Crop, [Month], FiscalYear, Price, Comments) VALUES(?,?,?,?,?,?)', n.seq_dimDate, n.crop, n.month, n.fiscalYear, n.price, n.symbol)
    print('%s Uploaded to MarketPrice Table in Hedging2' % (nq.symbol)) #confirm update

I submit myself to your critique!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.