cATq1y61

1import pyignite
2import csv
3
4client = pyignite.Client()
5client.connect('10.78.104.16', 10800)
6
7# Drop table
8SALES_DROP_TABLE_QUERY = '''DROP TABLE IF EXISTS SALES_RECORDS_16;'''
9client.sql(SALES_DROP_TABLE_QUERY)
10
11# Create table
12SALES_CREATE_TABLE_QUERY = '''
13CREATE TABLE IF NOT EXISTS SALES_RECORDS_16(
14   ID INTEGER PRIMARY KEY,
15   Region VARCHAR(50),
16   Country VARCHAR(50),
17   ItemType VARCHAR(50),
18   SalesChannel VARCHAR(50),
19   OrderPriority VARCHAR(50),
20   OrderDate VARCHAR(50),
21   OrderID VARCHAR(50),
22   ShipDate VARCHAR(50),
23   UnitsSold VARCHAR(50),
24   UnitPrice VARCHAR(50),
25   UnitCost VARCHAR(50),
26   TotalRevenue VARCHAR(50),
27   TotalCost VARCHAR(50),
28   TotalProfit VARCHAR(50)
29);'''
30for query in [SALES_CREATE_TABLE_QUERY]:
31    client.sql(query)
32
33# Fill table (longer)
34SALES_RECORDS_INSERT_QUERY = '''INSERT INTO SALES_RECORDS_16 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);'''
35index = 1
36with open("/data/commerce/1500000_Sales_Records.csv", 'r') as f:
37    reader = csv.reader(f, delimiter=',')
38    i = next(reader)
39    for row in reader:
40        row.insert(0, index)
41        client.sql(SALES_RECORDS_INSERT_QUERY, query_args=row)
42        print(index)
43        index = index + 1
44
45# Fill table (shorter)
46with open("/data/commerce/1500000_Sales_Records.csv", 'r') as f1, open("/data/commerce/queries.txt", "w+") as f2:
47    reader = csv.reader(f1, delimiter=',')
48    i = next(reader)
49    index = 1
50    for row in reader:
51        row.insert(0, index)
52        res = ','.join(str(element) for element in row)
53        query = '''INSERT INTO SALES_RECORDS_16 VALUES ({})'''.format(res)
54        print(query)
55        # f2.write(query)
56        index = index + 1
57f1.close()
58f2.close()
59
60# Query table
61SALES_RECORDS_QUERY = '''SELECT * FROM SALES_RECORDS_16 WHERE ORDERID='484937655';'''
62result = client.sql(SALES_RECORDS_QUERY)
63print('Result:')
64for row in result:
65    print(row)