def __init__(self, **kwargs):
self._user = unicode(kwargs.get("user"))
self._passwd = unicode(kwargs.get("passwd", ""))
self._host = unicode(kwargs.get("host", "127.0.0.1"))
self._port = int(kwargs.get("port", 3306))
self._db = unicode(kwargs.get("db"))
self._conn = MySQLdb.connect(
user=self._user,
passwd=self._passwd,
host=self._host,
port=self._port,
db=self._db,
use_unicode=True,
charset="utf8",
)
self._extractor = NumExtractor()
class SqlExtractor(object):
"""Class that deals with extracting various numeric data from a SQL table
and storing the data in the sql afterwards."""
def __init__(self, **kwargs):
self._user = unicode(kwargs.get("user"))
self._passwd = unicode(kwargs.get("passwd", ""))
self._host = unicode(kwargs.get("host", "127.0.0.1"))
self._port = int(kwargs.get("port", 3306))
self._db = unicode(kwargs.get("db"))
self._prefix = unicode(kwargs.get("prefix", ""))
self._intable = unicode(kwargs.get("intable", ""))
self._conn = MySQLdb.connect(
user=self._user,
passwd=self._passwd,
host=self._host,
port=self._port,
db=self._db,
use_unicode=True,
charset="utf8",
)
self._extractor = NumExtractor()
self._create_tables()
def _create_tables(self):
cur = self._conn.cursor()
for command in sql_create_script(self._db, self._prefix).split(";"):
if len(command.strip()) > 3:
cur.execute(command)
def _abs_prefix(self):
return "`" + self._db + "`.`" + self._prefix
def _abs_intable(self):
return self._abs_prefix() + self._intable + "`"
def _insert_rr(self, epiId, field, values):
tuples = []
for entry in values["record_bloodpressure"]:
systolic, diastolic, pulse = None, None, None
if "systolic" in entry:
systolic = entry["systolic"]["value"]
if "diastolic" in entry:
diastolic = entry["diastolic"]["value"]
if "pulse" in entry:
pulse = entry["pulse"]["value"]
tuples.append((epiId, field, systolic, diastolic, pulse))
if len(tuples) > 0:
cur = self._conn.cursor()
cur.execute("begin")
cur.executemany(
"insert into "
+ self._abs_prefix()
+ "rr` (epiId, field, systolic, diastolic, pulse) values (%s, %s, %s, %s, %s)",
tuples,
)
cur.execute("commit")
def _insert_temp(self, epiId, field, values):
tuples = []
for entry in values["record_temperature"]:
temp = entry["temperature"]["value"]
tuples.append((epiId, field, temp))
if len(tuples) > 0:
cur = self._conn.cursor()
cur.execute("begin")
cur.executemany(
"insert into " + self._abs_prefix() + "temp` (epiId, field, temp) values (%s, %s, %s)", tuples
)
cur.execute("commit")
def _insert_kmi(self, epiId, field, values):
pass
def process(self, field):
sql = "SELECT `epiId`, `" + field + "` FROM " + self._abs_intable()
print sql
cur = self._conn.cursor()
cur.execute(sql)
row = cur.fetchone()
while row is not None:
if row[1] is not None:
epiId = long(row[0])
document = unicode(row[1])
values = self._extractor.extract(document)
self._insert_rr(epiId, field, values)
# self._insert_temp(epiId, field, values)
row = cur.fetchone()
class SqlVisitExtractor(object):
"""Extractor for bloodpressure data in splitted epicrisis."""
def __init__(self, **kwargs):
self._user = unicode(kwargs.get("user"))
self._passwd = unicode(kwargs.get("passwd", ""))
self._host = unicode(kwargs.get("host", "127.0.0.1"))
self._port = int(kwargs.get("port", 3306))
self._db = unicode(kwargs.get("db"))
self._conn = MySQLdb.connect(
user=self._user,
passwd=self._passwd,
host=self._host,
port=self._port,
db=self._db,
use_unicode=True,
charset="utf8",
)
self._extractor = NumExtractor()
def to_plain(self, json):
sentences = ast.literal_eval(json)
return u" ".join([word["sone"].decode("unicode_escape", "replace") for sent in sentences for word in sent])
def _insert_rr(self, row, values):
visitId, epiId, epiTime, patId, epiType, fieldName, date, _ = row
tuples = []
for entry in values["record_bloodpressure"]:
systolic, diastolic, pulse = None, None, None
if "systolic" in entry:
systolic = entry["systolic"]["value"]
if "diastolic" in entry:
diastolic = entry["diastolic"]["value"]
if "pulse" in entry:
pulse = entry["pulse"]["value"]
tuples.append((visitId, epiId, epiTime, patId, epiType, fieldName, date, systolic, diastolic, pulse))
if len(tuples) > 0:
cur = self._conn.cursor()
cur.execute("begin")
cur.executemany(
"insert into `"
+ self._db
+ "`.`bloodpressures_visits` (visitID, epiId, epiTime, patId, epiType, fieldName, date, systolic, diastolic, pulse) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
tuples,
)
cur.execute("commit")
def process(self):
sql = "SELECT id, epiId, epiTime, patId, epiType, fieldName, date, json from `" + self._db + "`.`visits`;"
print sql
cur = self._conn.cursor()
cur.execute(sql)
row = cur.fetchone()
while row is not None:
if row[7] is not None:
values = self._extractor.extract(self.to_plain(row[7]))
self._insert_rr(row, values)
# self._insert_temp(epiId, field, values)
row = cur.fetchone()