Python help making vectors!

[ronparker]

Hello all,

To begin, I am working with a MySQL database using python. In the database, I am working with 3 columns: REF_ENTRY_TIME, REF_EXIT_TIME, REF_ENTRY_VALUE . My goal is to graph the net value position against time. For the time portion, both REF_ENTRY_TIME and REF_EXIT_TIME are random various times between 9:00 and 12:00. REF_ENTRY_VALUE is just a number around 100. In total I have a bout 50 columns.

The net value position is basically the sum of the REF_ENTRY_VALUE ’s, but it is only between the entry and exit times. With the following code, I can calculate the net value position at the single time 9:00. However is there anyway I can run a loop to get the net value position for all the times between 9 and 12? And get the corresponding times, so I can graph the results?

I don’t have any trouble with the plotting function matplotlib, I just need help on making the two vectors.

c.execute('SELECT REF_ENTRY_VALUE FROM actual_data_table WHERE REF_ENTRY_TIME<"2010-06-29 09:00:00" and REF_EXIT_TIME>"2010-06-29 09:00:00"')
result2=c.fetchall()
net_position_value=[sum(result2[0:i]) for i in range(1, len(result2)+1)]

[bvdet]

This code will loop between 9:00:00 and 12:00:00 inclusive for each minute. To loop on each second, change the stride argument to 1 in the range function call:

import time

start = "2010-06-29 09:00:00"
end = "2010-06-29 12:00:00"

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

startTime = int(time.mktime(time.strptime(start, timeFormat)))
endTime = int(time.mktime(time.strptime(end, timeFormat)))
results = []
for seconds in range(startTime, endTime+1, 60):
    timeStr = time.strftime(timeFormat, time.localtime(seconds))
    results.append(timeStr)
    print timeStr

[ronparker]

BVDET,
Thank you for your help on creating the vector for the time dimension. However, do you have any suggestions for the net postion vector? what I mean is with the code i showed earlier, I can get the value at any given time. However, how do i put all of those values into a list or something so i can later graph it against the time with the code you provided?
I hope I'm being clear.
Thank you once again!

[ronparker]

So I have gotten a little further. However I am getting an odd error with the code I provided.
The error is:

Traceback (most recent call last):
  File "<stdin>", line 6, in <module>
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'timeStr' in 'where clause'")

I think the problem is my code is reading timeStr as a string instead of as a datetime value like the format of the entry and exit values
any suggestions on how to fix this?

import time
import MySQLdb as mysql
import datetime
start = "2010-06-29 09:00:00"
end = "2010-06-29 12:00:00"
timeFormat="%Y-%m-%d %H:%M:%S"
startTime = int(time.mktime(time.strptime(start, timeFormat)))
endTime=int(time.mktime(time.strptime(end,timeFormat)))
results=[]
result_3=[]
sum_result2=[]
for seconds in range(startTime,endTime+1, 60):
	timeStr=time.strftime(timeFormat,time.localtime(seconds))
	results.append(timeStr)
	db=mysql.connect(db_info)
	c=db.cursor()
	c.execute('SELECT REF_ENTRY_VALUE FROM actual_data_table WHERE REF_ENTRY_TIME<timeStr and REF_EXIT_TIME>timeStr') 
	something=c.fetchall()
	sum_result2=[sum(something[0:i]) for i in range(1, len(something)+1)]
	result_3.extend([sum_result2]) 
	print result_3

[ronparker]

also, timeStr is in the format of yyyy-mm-dd hh:mm:ss which is the same format the entry and exit times are in which is why this is confusing me...

[bvdet]

It's easy to overlook and easy to fix!

'SELECT REF_ENTRY_VALUE FROM actual_data_table WHERE REF_ENTRY_TIME<%s and REF_EXIT_TIME>%s' % (timeStr)

[ronparker]

ALMOST THERE!
I was mistaken for the format of REF_ENTRY_TIME. So timeStr is in the yyyy-mm-dd hh:mm:ss format, but REF_ENTRY_TIME is in the format of datetime.dateti me(y, m, d, h, m, s)
So is there anyway I can clean this up with these formats?

>>> timeStr
'2010-06-29 10:00:00'
>>> 
>>> c.execute('SELECT REF_ENTRY_TIME FROM actual_data_table WHERE REF_ENTRY_VALUE<700')
16L
>>> something=c.fetchall()
>>> something
((datetime.datetime(2010, 6, 29, 9, 45, 2),), (datetime.datetime(2010, 6, 29, 9, 46),), (datetime.datetime(2010, 6, 29, 9, 46, 2),), (datetime.datetime(2010, 6, 29, 9, 46, 29),), (datetime.datetime(2010, 6, 29, 9, 47),), (datetime.datetime(2010, 6, 29, 9, 47, 3),), (datetime.datetime(2010, 6, 29, 9, 48, 11),), (datetime.datetime(2010, 6, 29, 10, 1, 3),), (datetime.datetime(2010, 6, 29, 9, 45, 2),), (datetime.datetime(2010, 6, 29, 9, 46),), (datetime.datetime(2010, 6, 29, 9, 46, 2),), (datetime.datetime(2010, 6, 29, 9, 46, 29),), (datetime.datetime(2010, 6, 29, 9, 47),), (datetime.datetime(2010, 6, 29, 9, 47, 3),), (datetime.datetime(2010, 6, 29, 9, 48, 11),), (datetime.datetime(2010, 6, 29, 10, 1, 3),))

[bvdet]

That makes the loop much simpler.

start = datetime.datetime(2010, 6, 29, 9, 0, 0)
end = datetime.datetime(2010, 6, 29, 12, 0, 0)

results = []

while start <= end:
    results.append(start)
    start += datetime.timedelta(minutes=1)

[ronparker]

I'm sorry this is taking me so long to get. It seems my problem here is that the datetime.dateti me values are in the square brackets?

import datetime, xlrd
import MySQLdb as mysql
start=datetime.datetime(2010, 6, 29, 9, 0, 0)
end=datetime.datetime(2010, 6, 29, 10, 0, 0)
results=[]
while start<=end:
	results.append(start)
	start+=datetime.timedelta(minutes=1) 
	db=mysql.connect(db_info)
	c=db.cursor()
	something=results
	c.execute('SELECT REF_ENTRY_VALUE FROM actual_data_table WHERE REF_ENTRY_TIME<%s and REF_EXIT_TIME>%s'%(something, something))
	thevalues=c.fetchall() 
	sum_result2=[sum(thevalues[0:i]) for i in range(1, len(thevalues)+1)]
	result_3.extend([sum_result2]) 
	results=[]

Traceback (most recent call last):
  File "<stdin>", line 7, in <module>
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[datetime.datetime(2010, 6, 29, 9, 0)] and REF_EXIT_TIME>[datetime.datetime(2010' at line 1")

c.execute('SELECT REF_ENTRY_TIME FROM actual_data_table WHERE TICKER="CPT"') 
something=c.fetchall() 
for record in something: 
...     record[0] 

datetime.datetime(2010, 6, 29, 9, 47)
datetime.datetime(2010, 6, 29, 9, 47, 3)
datetime.datetime(2010, 6, 29, 9, 48, 11)
datetime.datetime(2010, 6, 29, 10, 1, 3)
datetime.datetime(2010, 6, 29, 9, 45, 2)
datetime.datetime(2010, 6, 29, 9, 46)
datetime.datetime(2010, 6, 29, 9, 46, 2)
datetime.datetime(2010, 6, 29, 9, 46, 29)
datetime.datetime(2010, 6, 29, 9, 47)
datetime.datetime(2010, 6, 29, 9, 47, 3)
datetime.datetime(2010, 6, 29, 9, 48, 11)
datetime.datetime(2010, 6, 29, 10, 1, 3)

[bvdet]

I am am not that familiar with SQL, but I think it should look something like this (untested):

db=mysql.connect(db_info)
c=db.cursor()

timeresults=[]
valueresults = []
while start<=end:
    timeresults.append(start)
    start+=datetime.timedelta(minutes=1) 
    c.execute('SELECT REF_ENTRY_VALUE FROM actual_data_table WHERE REF_ENTRY_TIME<%s and REF_EXIT_TIME>%s'%(timeresults[-1], timeresults[-1]))
    thevalues=c.fetchall() 
    valueresults.append([sum(thevalues[0:i]) for i in range(1, len(thevalues)+1)])

for time, value in zip(timeresults, valueresults):
    print time, value