Importing SQL data into Elasticsearch

importsqlintoelastic.py

#Importing MySQL database to elasticsearch

# Importing required modules and connecting to elasticsearch

from pyelasticsearch import ElasticSearch
es = ElasticSearch('http://localhost:9200/')
import json
import requests
import MySQLdb

# Make sure ES is up and running

res = requests.get('http://localhost:9200')
print(res.content)


# defining variables that are required
hostname = 'localhost'
username = 'username'
password = 'password'
database = 'full_simple_invoices'
global json_string


# Connecting to MySQL

print "Connecting MySQL db…"
conn = MySQLdb.connect( host=hostname, user=username, passwd=password, db=database )
cur = conn.cursor()
cur.execute( "SELECT id, name, city FROM si_customers" )
rows = cur.fetchall()

# Priting the data for verification
for item in rows :
    print item


# Converting data to json format and assigning to variable, depends on the number of variables we need to change this
jsonout = json.dumps([{"id": row[0], "name": row[1], "city": row[2]} for row in rows],sort_keys=True)


# Converting string object to list 
jsonout1 = json.loads(jsonout)

# Inserting Bulk data into elasticsearch
es.bulk((es.index_op(doc, id=doc.pop('id')) for doc in jsonout1), index='dbmysql', doc_type='person')