예제 #1
0
    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()
예제 #2
0
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()
예제 #3
0
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()