Advertisement
Typhoon

ZVJS SQLITE to ELK 4

Apr 10th, 2015
285
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 2.04 KB | None | 0 0
  1. #!/usr/bin/env python
  2. # -*- coding: utf-8 -*-
  3.  
  4. import sqlite3
  5. import json
  6. import unicodedata
  7. from elasticsearch import Elasticsearch
  8.  
  9. conn = sqlite3.connect('SK_Prison_and_Court_Guard.sqlite')
  10. conn.row_factory = sqlite3.Row
  11. curs = conn.cursor()
  12. cursor = conn.execute('select * from data;')
  13. last_sqlite_id = len(cursor.fetchall())
  14.  
  15. print "Last SQLite ID from DB: ", last_sqlite_id
  16.  
  17. es = Elasticsearch(host='elk.myserver.sk', port=9200, http_auth=('auth_user', 'auth_pass'),)
  18.  
  19. response = es.search(
  20.     index="testpy",
  21.     body={
  22.         "query": {
  23.             "bool": {
  24.                 "must": [{"match": {"_type": "testpy"}}]
  25.             }
  26.         },
  27.         "aggs": {
  28.             "max_price": {"max": {"field": "invoice_id"}}},
  29.         "sort": {"invoice_id": {"order": "desc"}},
  30.         "size": 1
  31.     }
  32. )
  33.  
  34. for hit in response['hits']['hits']:
  35.     last_elastic_id = (hit['_source']['invoice_id'])
  36.     print "Last Elasticsearch ID is: ",(last_elastic_id)
  37.  
  38. # Temp WorkAround if isn't nothing in index
  39. # If is empty use counter with number 1
  40. # last_sqlite_id = 999999
  41. counter = last_elastic_id + 1
  42. try:
  43.     while counter < last_sqlite_id :
  44.        
  45.         conn = sqlite3.connect('SK_Prison_and_Court_Guard.sqlite')
  46.         conn.row_factory = sqlite3.Row
  47.         d = str(counter)
  48.         curs.execute("SELECT * FROM data WHERE invoice_id=" + d + "")
  49.         recs = curs.fetchall()
  50.         rows = [dict(rec) for rec in recs]
  51.         rows_json = json.dumps(rows)
  52.         chunk = unicodedata.normalize('NFKD', unicode(rows_json, 'utf-8', 'ignore')).encode('ASCII', 'ignore').replace('[','').replace(']', '').replace('"null"', 'null').replace('999999.99', '0.0')
  53.         print "Next try to push Document to ELK with ID: ",(counter)
  54.      
  55.         es = Elasticsearch(host='elk.myserver.sk', port=9200, http_auth=('auth_user', 'auth_pass'),)
  56.         res = es.index(index="testpy", doc_type='testpy', body=chunk)
  57.         print "Result - Created : ", (res['created']), "OK\n"
  58.      
  59.         counter = counter + 1
  60. except:
  61.     print "Finish"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement