import os
import sys
import time
import math
import MySQLdb
import numpy as np
#from pylab import *
#debug settings
DEBUG = True
# MySQL connection object to safely connect and disconnet from database
# Do NOT Touch !!!!!!!! -VY
class mysql_connection:
[docs] """Gives access to database.
Examples
--------
Create a database connection object and use it to fetch data.
>>> with mysql_connection( "localhost", "root", "pass", "test") as con:
>>> status = con.execute(" select count(*) from employee")
>>> print status,con.fetchall()
.. note:: when using `with`` statement, database connection is closed
automatically
**Methods**
__init__ : (internal) initializer
__enter__ : (internal) opens db connection and creates cursor
__exit__ : (internal) closes connection and cursor
"""
def __init__(self, *args, **kwargs ):
if len(args) < 4:
print "Insufficient parameters for database connection."
print "Usage: \r\n\tmysql_connection( DB_HOST, DB_USER_NAME, DB_USER_PASS, DB_NAME)\r\n"
sys.exit();
# We need to consider the order of args
self.db_host = args[0]
self.db_username = args[1]
self.db_userpass = args[2]
self.db_name = args[3]
def __enter__(self):
if DEBUG:
print "Creating database connection"
# Open database connection
self.cnx = MySQLdb.connect(self.db_host, self.db_username, self.db_userpass, self.db_name)
# prepare a cursor object using cursor() method
self.cursor = self.cnx.cursor()
#set things up
return self.cursor
def __exit__(self, type, value, traceback):
#tear things down
self.cursor.close()
self.cnx.close()
if DEBUG:
print "Closed database connection"
def fetchRAWData(**kwargs):
[docs] '''
Returns data from database based on keyword arguments given.
Conveniently get data from a database table. No need to fiddle with
database cursors or anything.
Keyword Arguments
-----------------
debug : bool
flag to print debug messages. If db connection is failing then
setting this variable to True might help locate problem.
idx_start : int
start index for samples selection
idx_end : int
end index for samples selection
db_host : str
database host (whether mysql database is local or remote)
user : str
username for database connection authentication
u_pass : str
password for selected username
dbase : str
database name
table : str
table name
Example
-------
Fetching data from `sensor_co` table in `EXPeriment` database
>>> from db_connector import fetchRAWData
>>> myMat, myLabels = fetchRAWData(debug=False,
idx_start=0,
idx_end=1000,
db_host='localhost',
dbase='EXPeriment',
user = 'analyst',
table='sensor_co',
u_pass = '*******',
)
>>> print myMat.shape
produces following output
.. testoutput::
(1000, 42)
'''
if 'debug' in kwargs:
global DEBUG
DEBUG = kwargs['debug']
else:
DEBUG = False
##--------------Exception catching---------------------##
try:
##
# Checking parameters passed for DB conn
##
if 'idx_start' in kwargs:
index_start = kwargs['idx_start']
else: # ask user to specify?
#index_start = int(raw_input("Enter the starting index: "))
print ("No index provided, using default start index(0), stop index(100)")
index_start = 0
if 'idx_end' in kwargs:
index_end = kwargs['idx_end']
else:
#index_end = int(raw_input("Enter the end index: "))
index_end = 100
if 'db_host' in kwargs:
db_host = kwargs['db_host']
else:
print("No host provided for database connection, default is 'localhost'")
db_host = 'localhost'
if 'dbase' in kwargs:
db_name = kwargs['dbase']
else:
print("No database provided, default is 'EXPeriment'")
db_name = 'EXPeriment'
if 'user' in kwargs:
u_name = kwargs['user']
else:
print("No user for database connection provided, default is root")
u_name = 'root'
if 'u_pass' in kwargs:
upass = kwargs['u_pass']
else:
print("No user password provided, default is '' ")
upass = ''
if 'table' in kwargs:
tbl_name = kwargs['table']
else:
print("No table name provided, default is 'sensor_co'")
tbl_name = 'sensor_co'
# We can also measure how much time took for computations
t1 = time.time()
if index_start == index_end:
if DEBUG:
print "None samples selected ?"
pass
if index_start > index_end: # Swap the integers. when Start_index is larger
# Start_index must be smaller than End_index
temp_int = index_start
index_start = index_end
index_end = temp_int
# Visual Que
#
if DEBUG:
print "Fetching samples between Id:{} and Id:{}".format(index_start, index_end)
# pseudo: We will fetch the sensor data and create row vectors
# database connection parameter required in this step
with mysql_connection( db_host, u_name, upass, db_name) as con:
# SQL query
#fetched_records = con.execute("select * from sensor_co where Id BETWEEN {} AND {}".format(index_start,index_end))
fetched_records = con.execute("select * from {} where Id BETWEEN {} AND {}".format(tbl_name,index_start,index_end))
sample_count = fetched_records
if DEBUG:
print("{} rows returned from database\r\n".format( fetched_records ))
# Column description( NAMES/TITLES/LEGENDS )
column_names = [ c_iter[0] for c_iter in con.description] # Fetch column names
######### Sensor_reading (Columns) => ROW_VECTORS here #########
#
# here we are fetching the relavant data
# However, this is a very specific slice
#
# This FAILS when we select specific fields from database
# Eg "Select Co1, Co2,Co3 from sensors_co"
#
##**************************************************************
records = [ row[3:] for row in con.fetchall()] # In each row of data we
# are Leaveing first three
# fields(columns)
sensor_count = len(records[0]) # each row has same no. of sensors
# we picked the lenth of 0'th row
sensor_mat = np.array(records) # Converted data to numpy array
if DEBUG:
print("::: Module executed in {} seconds, status 0 :::".format(time.time() - t1))
return sensor_mat, column_names[3:] #, time.time() - t1
## NOTE: now each row represents the sensor reading from a single SPND
## Columns represent 'Sample Number'
##
## --> SPNDs
## +--------------------------+
## t_1 | x_11 .. .. .. .. .. x_1m |
## t_2 | x_21 .. .. .. .. .. x_2m |
## .. | .. |
## t_n | x_n1 .. .. .. .. .. x_nm |
## +--------------------------+
##*********************************************
##--------------Exception Handling---------------------##
except MySQLdb.OperationalError as err:
print "Database Connection Failed. Please check parameters passed."
if __name__ == '__main__':
#test Run
print fetchRAWData(debug=False,
idx_start=0,
idx_end=1000,
db_host='localhost',
dbase='EXPeriment',
user = 'analyst',
table='sensor_co',
u_pass = 'chemlab',
)[0].shape
