I'm new to django
I have a local application I wrote in python that writes scientific data to a MySQL database hosted on a digital ocean droplet that also runs django.
I'm experiencing an intermittent problem where occasionally (maybe 1 in 3 tries) a page request produces a 'bad gateway' error message. Refreshing the page will normally produces the correct page. A page example is http://104.131.165.99/garage/
The problem is annoying and I'm not sure what the cause is but I never understood models in django and I'm not sure how to even clearly ask my question.
My local application uses pymysql to connect to the MySQL database thats part of the server where django is hosted, in otherwords a mysql database thats NOT(?) really associated with the instance of django running on the server.
The code in my django app (see below) that runs when a page is requested uses pymysql with a host address of 127.0.0.1 to read and manipulate the MwSQL data.
Whether or not this is the cause of my problem, I always wondered if this was the proper way to read mysql data in django considering the app writing to that database is on a different server.
this is my code that returns the html doc...as its probably obvious i'm not a programmer by trade
thank you in advance for your consideration.
def get_temp_dict(location):
##### start clock ###########
start_time=0
start_time=time.perf_counter()
##### get db user data ###########
user_data=get_user_data()
mysql_user = user_data['mysql_user']
mysql_password = user_data['mysql_password']
mysql_database = user_data['mysql_database']
##### constants ###########
filepath="/home/django/django_project/django_project/media/"
filename="GRAPH_" + location + ".png"
filename_and_path=filepath+filename
short_filename_path="/media/"+filename
now = datetime.now()
formatted_date = now.strftime('%Y-%m-%d')
cur_date=dt.datetime.now().strftime("%m-%d-%Y")
queryString="SELECT sensor_id, label, temp, time, date FROM temps INNER JOIN sensors USING (sensor_id) where label=" + "'" + location + "'" +"and date=" + "'" + formatted_date +"' order by 'time' ASC"
##### connect to database ###########
try:
db = pymysql.connect(host="127.0.0.1", user=mysql_user, password=mysql_password, database=mysql_database)
cursor = db.cursor()
cursor.execute("USE hms")
SQL_Query = pd.read_sql_query(queryString, db)
df = pd.DataFrame(SQL_Query, columns=['sensor_id', 'temp', 'time', 'date'])
STATUS1="success2"
except:
STATUS1="failure"
#### build dataframe ###########
timestring=str(df.at[df.time.size-1,'time'])
convert_time=datetime.strptime(timestring, '%H:%M:%S')
latest_record_timestamp=datetime.strftime(convert_time, '%I:%M %p')
date_str=str(df.at[df.time.size-1,'date'])
convert_date= datetime.strptime(date_str, '%Y-%m-%d')
latest_record_datestamp=datetime.strftime(convert_date, '%b %d')
latest_record_temp=str(df.at[df.time.size-1,'temp'])+ u"\N{DEGREE SIGN}"
str_max_temp=str(df['temp'].max())
time_obj_max_time_temp=datetime.strptime(str(df.at[df['temp'].idxmax(),'time']), '%H:%M:%S')
str_max_time_temp=datetime.strftime(time_obj_max_time_temp,'%I:%M %p')
date_obj_max_date=datetime.strptime(str(df.at[df['temp'].idxmax(),'date']), '%Y-%m-%d')
str_max_date_temp=datetime.strftime(date_obj_max_date, '%b %d')
str_min_temp=str(df['temp'].min())
obj_min_time_temp=datetime.strptime(str(df.at[df['temp'].idxmin(),'time']),'%H:%M:%S' )
str_min_time_temp=datetime.strftime(obj_min_time_temp,'%I:%M %p')
obj_min_date_temp=datetime.strptime(str(df.at[df['temp'].idxmin(),'date']), '%Y-%m-%d')
str_min_date_temp=datetime.strftime(obj_min_date_temp, '%b %d')
first_record_time_string=df.at[1,'time']
first_record_time_object=datetime.strptime(first_record_time_string, '%H:%M:%S')
######### Build HTML doc ##################
message='NOTE, this ' + location +' temperature data report was was produced with data as recent as ' + str(latest_record_timestamp) +' on ' + str(latest_record_datestamp) + ' where the current temperature was: ' + latest_record_temp
MSG2="so far there have been " + str(df.time.size) + " Readings today, with the first reading at " + str(datetime.strftime(first_record_time_object,'%I:%M %p'))
html_doc=html_status_page()
b = {'names': 'TIMOTHY', 'CUR_DATE': str(cur_date),'STATUS1':STATUS1, 'MESSAGE':str(message),
'TEMP1':latest_record_temp,'LOCATION1':location, 'filename_and_path':short_filename_path,
'str_max_temp':str_max_temp, 'str_max_time_temp':str_max_time_temp,
'str_max_date_temp':str_max_date_temp, 'str_min_temp':str_min_temp,
'str_min_time_temp':str_min_time_temp, 'str_min_date_temp':str_min_date_temp,
'MSG2':MSG2 }
for x,y in b.items():
html_doc = html_doc.replace(x, y)
df.time = pd.to_datetime(df.time)
plt.figure(figsize=(14,4))
g=sns.lineplot(x='time', y='temp', data=df,color="darkblue")
plt.ylim(df['temp'].min()-1, df['temp'].max()+1)
plt.xticks(rotation=45)
# compensate for axis labels getting clipped off
plt.subplots_adjust(bottom=.5, left=0.15)
g.xaxis.set_major_formatter(mdates.DateFormatter('%I %p'))
g.xaxis.set_major_locator(mdates.HourLocator(interval = 2))
#create unique filename based on time
plt.savefig(filename_and_path)
endtime=int(time.perf_counter()-start_time)
c={'TOTALTIME':str(endtime)}
for a,b in c.items():
html_doc = html_doc.replace(a, b)
return html_doc
