Selecting from MySQL then making the correct loop in python

[ronparker]

First of all, thank you for taking the time to read my question. With the following code, what I have done is make a database in MySQL and created a table using python. I then imported an excel document with three columns. These three colums are:
1. POS_TYPE which is either the word 'LONG' or 'SHORT.
2. REF_ENTRY_TIME which is a time int eh YYYY-MM-DD 00:00:00 format
3. REF_ENTRY_VALUE which is just a number between 600 and 700

right now at the end of my code if i type in 'real result' I get about 25 REF_ENTRY_VALUE 's. From here I have a two part question.

First, with these values, is there anyway I can multiply the REF_ENTRY_VALUE by (-1) if the corresponding POS_TYPE is 'SHORT' and leave it alone if it is 'LONG'? The important thing for me would be keeping the order.

Second, for 'real result', I currently get 25 values. Is there anyway I can run a loop to aggregate these numbers. What I mean by that is, for example turn a set of numbers like (1,2,3,4,5,6,7, 8,9,10) into (1,1+2,1+2+3,1+ 2+3+4,1+2+3+4+5 ....) I plan on working with a lot of values so doing by hand would not be practical.

REALLY ANY HELP WILL BE GREATLY APPRECIATED!!

from xlrd import open_workbook, cellname
import datetime, xlrd
import MySQLdb as mysql
db=mysql.connect(DB_INFO)
c=db.cursor()
c.execute('CREATE DATABASE actual_data')
db=mysql.connect(DB_INFO)
c=db.cursor()
c.execute('CREATE TABLE actual_data_table (,POS_TYPE VARCHAR(35) NOT NULL, REF_ENTRY_VALUE VARCHAR(35) NOT NULL, ,REF_ENTRY_TIME DATETIME NOT NULL)

file_to_import='actual_daily_report_2010-06-29.xls'
column_count=43
book=open_workbook(file_to_import)
sheet=book.sheet_by_index(0)
db=mysql.connect(DB_INFO)
c=db.cursor()

for row_index in range(sheet.nrows):

       row_num=row_index
       POS_TYPE=sheet.cell(row_index,0).value
       REF_ENTRY_VALUE=sheet.cell(row_index,1).value
       REF_ENTRY_TIME=sheet.cell(row_index,2).value
         REF_ENTRY_TIME_as_datetime=datetime.datetime(*xlrd.xldate_as_tuple(REF_ENTRY_TIME, book.datemode))
              c.execute('insert into actual_data_table values ("%s","%s","%s")’ % (POS_TYPE,REF_ENTRY_VALUE,REF_ENTRY_TIME_as_datetime))
       
db=mysql.connect(DB_INFO)
c=db.cursor()
c.execute('SELECT REF_ENTRY_VALUE FROM actual_data_table WHERE REF_ENTRY_TIME>"2010-06-29 09:45:00" and REF_ENTRY_TIME<"2010-06-29 12:00:00"')
result1=c.fetchall()

for item in result1:
       ...print float(item[0])
[float(item[0]) for item in result1]=real_result

[bvdet]

This should take care of your second question:

>>> numbers = (1,2,3,4,5,6,7,8,9,10)
>>> [sum(numbers[0:i]) for i in range(1, len(numbers)+1)]
[1, 3, 6, 10, 15, 21, 28, 36, 45, 55]
>>>

Following is some interaction that may give you some ideas. I am using a dictionary in one example and an if block in the second example

>>> dd = {"LONG": 1, "SHORT": -1}
>>> value = 100
>>> pos_type = "SHORT"
>>> value *= dd[pos_type]
>>> value
-100


>>> if pos_type == "SHORT":
... 	value *= -1
... 	
>>>

[ronparker]

bdvet, you have been so helpful these past couple days!

I just cannot seem to multiply correctly in this same issue. When I put 'pos[record[2]]' in the correct loop, it returns the positive or negative multiplyer correctly. However, I cannot seem to be able to multiply these values by the list given by 'real_result2'. I think I have an int or list issue somehwere, any suggestions?

>>> for record in result:
...     pos={"LONG":1,"SHORT":-1}
...     print pos[record[2]]
... 
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
1
1
-1
-1
-1
1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1


>>> real_result2
[685.72000000000003, 736.5, 688.59000000000003, 698.88, 698.60000000000002, 689.17999999999995, 667.79960000000005, 717.39999999999998, 694.37, 729.12, 732.97000000000003, 713.70000000000005, 663.75, 715.87, 714.0, 708.75, 724.5, 708.70000000000005, 748.39999999999998, 714.34000000000003, 702.61000000000001, 712.79999999999995, 702.14999999999998, 717.91999999999996, 723.0, 705.29999999999995]

>>> for record in result:
...     pos={"LONG":1,"SHORT":-1}
...     real_result3=real_result2*pos[record[2]]
... 
>>> real_result3
[]

[ronparker]

So, I think I've gotten a little close. However, I'm still not there. I really dont understand why I cannort multiply the two lists here.
Thank you in advance!

>>> result_3=[]

>>> for record in result:
...     pos={"LONG":1, "SHORT":-1}
...     result_3.extend([pos[record[2]]])
... 

>>> result_3
[-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, 1, 1, -1, -1, -1, 1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1]

>>> real_result2
[685.72000000000003, 736.5, 688.59000000000003, 698.88, 698.60000000000002, 689.17999999999995, 667.79960000000005, 717.39999999999998, 694.37, 729.12, 732.97000000000003, 713.70000000000005, 663.75, 715.87, 714.0, 708.75, 724.5, 708.70000000000005, 748.39999999999998, 714.34000000000003, 702.61000000000001, 712.79999999999995, 702.14999999999998, 717.91999999999996, 723.0, 705.29999999999995]

>>> result_3*real_result2
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: can't multiply sequence by non-int of type 'list'

>>> type(real_result2)
<type 'list'>

>>> type(result_3)
<type 'list'>

[ronparker]

Never mind, I figured it out...thank you once again tho!

[bvdet]

Good job figuring it out for yourself. I would have suggested something like this:

>>> pos={"LONG":1, "SHORT":-1}
>>> result = [["LONG", "8/23/10", 100], ["SHORT", "8/23/10", 200], ["LONG", "8/23/10", 300]]
>>> for i, record in enumerate(result):
... 	result[i][2] *= pos[record[0]]
... 	
>>> result
[['LONG', '8/23/10', 100], ['SHORT', '8/23/10', -200], ['LONG', '8/23/10', 300]]
>>>