wNJNb9GG

1import pymysql
2
3HOST_NAME = '192.168.12.177'
4
5DB_NAME = "sterilis"
6cnx = pymysql.connect(user='sterilis2',password='sterilis2',host=HOST_NAME, database=DB_NAME)
7cursor = cnx.cursor()
8
9
10try:
11    cnx.database = DB_NAME
12except pymysql.Error as err:
13    if err.errno == errorcode.ER_BAD_DB_ERROR:
14        create_database(cursor)
15        cnx.database = DB_NAME
16    else:
17        print(err)
18        exit(1)
19
20cursor.execute('set global max_allowed_packet=67108864')
21
22print "hi"
23eventList = []
24eventList = cursor.execute('select * from events join event_data on event_data.event_id = events.ID order by events.time_stamp desc limit 15')
25#(9860796, 11460, 1496428259, u'Tv', 9686893, 9860796, 127.0)
26#event id| act id| timestamp | type | eventd id | eventid | reading
27
28eventList = list(cursor.fetchall())
29eventLength = len(eventList)
30
31tvPvEventIdList = []
32
33combinedList = []
34newTable = """CREATE TABLE IF NOT EXISTS `new_events` (
35	`id` INT NOT NULL AUTO_INCREMENT,
36	`activity_id` INT NOT NULL DEFAULT '0',
37	`timestamp` INT NOT NULL DEFAULT '0',
38	`pv_reading` INT NOT NULL DEFAULT '0',
39	`tv_reading` INT NOT NULL DEFAULT '0',
40	PRIMARY KEY (`id`)
41    )
42    COLLATE='latin1_swedish_ci'
43    ENGINE=InnoDB
44    ;
45"""
46combinedList.append(newTable)
47for idx, row in enumerate(eventList):
48    #print("row", row, "idx", idx)
49    #print(eventList[idx])
50    print(eventList[idx][2])
51    if (idx + 1) < eventLength:
52        if eventList[idx][2] == eventList[idx+1][2]:
53            insertStatement = 'INSERT INTO new_events (activity_id, timestamp, pv_reading, tv_reading) VALUES('
54            print 'tv and pv pair found'
55            print eventList[idx]
56            print eventList[idx+1]
57            #combined format
58            # combined id | act id | timestamp | pv reading | tv reading
59            insertStatement += str(eventList[idx][1]) + ',' + str(eventList[idx][2]) + ',' + str(eventList[idx][6]) + ',' + str(eventList[idx+1][6]) + ');'
60            #combinedRow = '(' + str(idx) + ',' + str(eventList[idx][1]) + ',' + str(eventList[idx][2]) + ',' + str(eventList[idx][6]) + ',' + str(eventList[idx+1][6]) + ')'
61            combinedList.append(insertStatement)
62            tvPvEventIdList.append(eventList[idx][0])
63            tvPvEventIdList.append(eventList[idx + 1][0])
64
65    else:
66        print 'end of the events'
67
68
69print(combinedList)
70
71print tvPvEventIdList
72
73for tvPv in tvPvEventIdList:
74    print 'tvpv', tvPv
75    deleteStatement = 'DELETE FROM events WHERE events.id = (%s)'
76    try:
77        print "Trying to delete event id: ", tvPv
78        cursor.execute(deleteStatement, (tvPv))
79        cnx.commit()
80    except pymysql.InternalError as error:
81        print "Command skipped: ", error
82
83with open('insertCombinedEvents.sql', 'w') as sqlFile:
84    sqlFile.truncate()
85    for insertStatement in combinedList:
86        sqlFile.write("%s\n" % insertStatement)
87
88
89for command in combinedList:
90    try:
91        print "Trying command", command
92        cursor.execute(command)
93        cnx.commit()
94    except pymysql.InternalError as error:
95        print "Command skipped: ", error
96
97cursor.close()
98cnx.close()