PyMySQL - getting column data from dictionary by fetchall in

Question

Using PyMySQL python 3.6.3 versions, getting an DictCursor, and then fetchall(). I get all the data and .description says: (('recdate', 12, None, 19, 19, 0, False), ('outdoorhumidity', 246, None, 9, 9, 3, True), ('outdoortemperature', 246, None, 9, 9, 3, True)).

Printing the rows I get, f.ex:

2005-12-31 23:12:00    89.000   -6.667
2005-12-31 23:13:00    89.000   -6.667
2005-12-31 23:15:00    89.000   -6.650
2005-12-31 23:16:00    89.000   -6.650
2005-12-31 23:17:00    89.000   -6.640

Note the missing minute ...23:14:00 - but I do this for a bigger missing data imputations project. So this is sample data around the missing data. Via the dictionary I want to get the incomplete time series as well as f ex 3rd column, in the best way -simple easy readable code? Do I in each case have to know how many rows there are?

import pymysql

dbServerName = "127.0.0.1"
dbUser = "root"
dbPassword = "mypwd"
dbName = "dbname"
charSet = "utf8"
cursorType = pymysql.cursors.DictCursor

connectionObject = pymysql.connect(host=dbServerName, user=dbUser, password=dbPassword,
                                     db=dbName, charset=charSet,cursorclass=cursorType)

try:
    cursorObject = connectionObject.cursor()                                     

    sqlQuery = "SELECT recdate, outdoorhumidity, outdoortemperature FROM mytable WHERE recdate BETWEEN '2005-12-31 23:12:00' AND '2005-12-31 23:17:00';"


    cursorObject.execute(sqlQuery)

    #Fetch all the rows - within the cursor? Can this be done?
    rows = cursorObject.fetchall()

    print(cursorObject.description)

    for row in rows:
        print(row["recdate"], row["outdoorhumidity"], row["outdoortemperature"])   

except Exception as e:
    print("Exeception occured:{}".format(e))

finally:
    cursorObject.close()
    connectionObject.close()

Answer 1

The things I wanted to achieve was much simpler to use with pandas, sqlalchemy, datetime and time. And also made commentes around differerent ways to go about referencing Pandas DataFrames.

Some things are of cause a bit special to just my case, but can be interesting for others, especially to play around with the use of .iloc and .loc, and type convertions handling time.

# Python version
# '3.6.4 |Anaconda, Inc.| (default, Jan 16 2018, 10:22:32) [MSC v.1900 64 
       bit (AMD64)]'
# Running Spyder IDE version 3.2.6
# PANDAS VERSION '0.22.0'enter code here
import pandas as pd
import time
from datetime import datetime
from sqlalchemy import create_engine



def GetSqlData(begdatetime,enddatetime):
    temp_df = pd.read_sql_query("""SELECT recdate, outdoortemperature
                   FROM osterasen 
                   WHERE recdate BETWEEN 
                   %(c1)s
                   AND
                   %(c2)s """,
                   engine,
                   params={'c1': begdatetime, 'c2': enddatetime}
                   )
    temp_df['recdate'] = temp_df['recdate'].astype('datetime64[ns]')
    return temp_df

iso_datetimeformat = "%Y-%m-%d %H:%M:%S"

# BEGIN - CREATE MOCK MISSING DATES
# would have to come from the WeatherData.missings table
print("Step 0.0: ")
print("     Get missing data info from weatherdata.missings...")

num_of_missings=21
range_good_m = num_of_missings * 2
print(range_good_m)
range_good_s = range_good_m * 60
print(range_good_s)
a_complete_end_str = '2005-09-24 09:09:00'

b_complete_beg_str = '2005-09-24 09:31:00'

a_complete_end = time.mktime(datetime.strptime(a_complete_end_str, 
    iso_datetimeformat ).timetuple())
print("a_complete_end: ", a_complete_end)

b_complete_beg = time.mktime(datetime.strptime(b_complete_beg_str, i 
iso_datetimeformat ).timetuple())
print("b_complete_beg: ", b_complete_beg)

a_complete_beg = a_complete_end - range_good_s 
print("a_complete_beg: ", a_complete_beg)

b_complete_end = b_complete_beg + range_good_s
print("b_complete_end: ", b_complete_end)
print("b_complete_end: type: ", type(b_complete_end))

m_missing_beg = a_complete_end + 60
m_missing_end = b_complete_beg - 60
m_missing_beg_str = str(datetime.fromtimestamp(m_missing_beg))
m_missing_end_str = str(datetime.fromtimestamp(m_missing_end))

a_complete_beg_str = str(datetime.fromtimestamp(a_complete_beg ))
b_complete_end_str = str(datetime.fromtimestamp(b_complete_end ))

# Print out the ranges
print(a_complete_beg_str)
print(a_complete_end_str)
print(m_missing_beg_str)
print(m_missing_end_str)
print(b_complete_beg_str)
print(b_complete_end_str)

# END   - CREATE MOCK MISSING DATES

connection_str = 
    'mysql+pymysql://root:mypassword@127.0.0.1:3306/weatherdata'

engine = create_engine(connection_str,encoding='utf8')




print("Step 1.0: ")
df_a = GetSqlData( a_complete_beg_str, a_complete_end_str )
print(type(df_a),"   ", type(df_a.head))
print(df_a)
x1 = df_a.loc[5, 'outdoortemperature']
y1 = df_a.loc[5, 'recdate']
print("x1 = ", x1, type(x1))
print("y1 = ", y1, type(y1))



print("Step 2.0: ")
df_b = GetSqlData( b_complete_beg_str, b_complete_end_str )
print(type(df_b),"   ", type(df_b.head))
print(df_b)
x2 = df_b.loc[5, 'outdoortemperature']
y2 = df_b.loc[5, 'recdate']
print("x2 = ", x2, type(x2))
print("y2 = ", y2, type(y2))