No Description

script.py 1.5KB

12345678910111213141516171819202122232425262728293031323334353637383940414243
  1. #!/usr/bin/python
  2. import time
  3. import sqlite3
  4. db = sqlite3.connect('data-table.db')
  5. cursor = db.cursor()
  6. cursor.execute(""" CREATE TABLE data
  7. (deviceId integer, timestamp integer, sensorReading integer, district text)
  8. """)
  9. print("Table created\n")
  10. cursor.execute(""" INSERT INTO data VALUES (12, datetime('-200 days'), 13, 'E9'),
  11. (12, datetime('now', '-150 days'), 12, 'E9'),
  12. (8, datetime('now', '-120 days'), 14, 'E9'),
  13. (34, datetime('now', '-20 days'), 80, 'E2'),
  14. (45, datetime('now', '-5 days'), 76, 'E2'),
  15. (34, datetime('now', '-6 days'), 70, 'E2'); """)
  16. print("Values inserted\n")
  17. db.commit()
  18. print("Query: give all reading for district E2")
  19. results = cursor.execute(""" SELECT * FROM data WHERE district IS 'E2' """).fetchall()
  20. for result in results:
  21. print(result)
  22. print("\n")
  23. print("Query: All data since last week for district E2")
  24. results = cursor.execute(""" SELECT * FROM data WHERE timestamp >= datetime('now', '-7 days') AND district IS 'E2' """).fetchall()
  25. for result in results:
  26. print(result)
  27. print("\n")
  28. print("Query: Average data since last week for district E2")
  29. result = cursor.execute(""" SELECT avg(sensorReading) FROM data WHERE timestamp >= datetime('now', '-7 days') AND district IS 'E2' """).fetchone()[0]
  30. print(result)
  31. print("\n")
  32. db.close()
  33. print("You can continue querying on your own or drop the table 'data'")