eqldb_sqlite/importDataToSqlite.py at main · i010542/eqldb_sqlite · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
from pythonAPI.eqldb import EqlDB
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Text, ForeignKey, Index
from sqlalchemy.orm import relationship
import os

Base = declarative_base()


class SPO(Base):
    __tablename__ = 'spo'
    factID = Column(Text, primary_key=True, index=True)
    s = Column(Text, index=True)
    p = Column(Text, index=True)
    o = Column(Text, index=True)

    qvs = relationship('QV', backref='spo', lazy='dynamic')


class QV(Base):
    __tablename__ = 'qv'
    factID = Column(Text, ForeignKey("spo.factID"), index=True, primary_key=True)
    q = Column(Text, index=True, primary_key=True)
    v = Column(Text, index=True, primary_key=True)


Index('idx_spo_id_s', SPO.factID, SPO.s)
Index('idx_spo_id_p', SPO.factID, SPO.p)
Index('idx_spo_id_o', SPO.factID, SPO.o)
Index('idx_spo_s_p', SPO.s, SPO.p)
Index('idx_spo_s_o', SPO.s, SPO.o)
Index('idx_spo_p_o', SPO.p, SPO.o)

Index('idx_spo_id_s_p', SPO.factID, SPO.s, SPO.p)
Index('idx_spo_id_s_o', SPO.factID, SPO.s, SPO.o)
Index('idx_spo_id_p_o', SPO.factID, SPO.p, SPO.o)
Index('idx_spo_s_p_o', SPO.s, SPO.p, SPO.o)

Index('idx_spo_id_s_p_o', SPO.factID, SPO.s, SPO.p, SPO.o)

Index('idx_qv_id_q', QV.factID, QV.q)
Index('idx_qv_id_v', QV.factID, QV.v)
Index('idx_qv_q_v', QV.q, QV.v)
Index('idx_qv_id_q_v', QV.factID, QV.q, QV.v)


def init():
    if os.path.exists("./dbs/"):
        pass
    else:
        os.mkdir("./dbs")

    if os.path.exists("./dbs/eqldb_db.db"):
        pass
    else:
        engine = create_engine('sqlite:///dbs/eqldb_db.db')
        Base.metadata.create_all(engine, checkfirst=True)


def fillInDatabase(esDBName, sqliteDBName):
    db = EqlDB('lemon.net.cn', 8086, esDBName, '', '')

    res = db.execute("?x:所获奖项:诺贝尔文学奖(?y)")[1]
    engine = create_engine('sqlite:///dbs/' + sqliteDBName + '.db')
    Session = sessionmaker(bind=engine)
    session = Session()

    for r in res:
        x = r.get('?x')
        y = r.get('?y')
        factID = r.get('?factID')
        print(factID, x, '所获奖项', '诺贝尔文学奖')
        newSPO = SPO(factID=factID, s=x, p='所获奖项', o='诺贝尔文学奖')
        session.add(newSPO)

        for yy in y:
            yy = eval(yy)
            for yyy in yy.items():
                key = yyy[0]
                value = yyy[1]
                print(factID, key, value)
                newQV = QV(factID=factID, q=key, v=value)
                session.add(newQV)
    session.commit()


if __name__ == '__main__':
    init()