fyN8e6iK

1class DB_Table(object):
2
3    def __init__(self,db_path, *connections):
4        self.conn = sqlite3.connect(db_path)
5        self.c = self.conn.cursor()
6
7        self.T_NAME=""
8        self.COLS = {}
9        self._COLS=[]
10        self.KEY = []
11        self.connections = connections
12
13    def _foreign_key_text(self, col_in_place, table, col):
14
15        return "FOREIGN KEY ({0}) REFERENCES {1}({2})".format(col_in_place,table,col)
16
17    def _find_connections(self):
18        col1,table,col2 = [],[],[]
19        for c in self.connections:
20            if issubclass(c.__class__,DB_Table):
21                for k in c.KEY:
22                    if k in self.KEY:
23                        col1.append(k)
24                        table.append(c.T_NAME)
25                        col2.append(k)
26        return zip(col1,table,col2)
27
28
29
30    def create_table(self,auto_remove=False):
31        if auto_remove:
32            self.c.execute("DROP TABLE IF EXISTS {0};".format(self.T_NAME))
33
34        create_command = "CREATE TABLE IF NOT EXISTS {0}".format(self.T_NAME)
35        col_texts = ["{col} {data_type}".format(col=k,data_type=str(self.COLS[k]).upper()) for k in self._COLS]
36        if self.KEY != []:
37            keys = ",".join(self.KEY)
38            col_texts.append("PRIMARY KEY ({0})".format(keys))
39
40        connections = self._find_connections()
41        foreign_key_text = [self._foreign_key_text(*c[:]) for c in connections]
42        col_texts.extend(foreign_key_text)
43
44        create_command += "({});".format(",\n".join(col_texts))
45
46        self.c.execute(create_command)
47
48
49
50    def add_record(self,**values):
51
52        cols_ = [k for k in values.keys() if k in self._COLS]
53        cols_.sort()
54        cols = ["{0}".format(str(c)) for c in cols_]
55        k = ",".join(cols)
56
57        vals = ["'{0}'".format(str(values[c])) for c in cols_]
58        v = ",".join(vals)
59
60        add_command = "INSERT OR IGNORE INTO {0}({1}) VALUES ({2})".format(self.T_NAME,k,v)
61
62        self.c.execute(add_command)
63
64    def overwrite_record(self,**values):
65
66        cols = [k for k in values.keys() if k in self._COLS]
67        cols.sort()
68        k = ",".join(cols)
69
70        vals = [values[c] for c in cols]
71        v = ",".join(vals)
72
73        add_command = "INSERT OR REPLACE INTO {0}({1}) VALUES ({2})".format(self.T_NAME,k,v)
74
75        self.c.execute(add_command)
76
77    def check_table_exists(self):
78        statement = "SELECT name FROM sqlite_master WHERE type='table';"
79        if (self.T_NAME,) in self.c.execute(statement).fetchall():
80            return True
81        return False
82
83
84    def read_table(self,print_it = False):
85        if not self.check_table_exists():
86            return None
87
88        rows = self.c.execute("SELECT * FROM {0};".format(self.T_NAME)).fetchall()
89
90        cols = self.c.execute("PRAGMA table_info({0});".format(self.T_NAME)).fetchall()
91        col_names = [c[1] for c in cols]
92        # print col_names
93
94        table_data = pd.DataFrame(rows,columns=col_names)
95        if print_it: print table_data
96
97        return table_data
98
99    def get_record_list(self,*cols):
100        records = self.read_table()
101        if isinstance(subjects,pd.DataFrame):
102            valid_keys = [c for c in cols if c in records.columns]
103            return subjects[:][np.array(valid_keys)]
104
105    def save_tables(self):
106        self.conn.commit()
107
108    def __del__(self):
109        self.conn.close()
110
111
112class measurements(DB_Table):
113
114    def __init__(self,connections=None):
115        super(measurements,self).__init__(_db_path,connections)
116
117        self.T_NAME = "MEASUREMENTS"
118        self.COLS = {"id":"text not null",
119                    "n":"numeric not null",
120                    "date_":"text not null"}
121        self._COLS=["id","n","date_"]
122        self.KEY = ["id","n"]
123
124
125
126
127class subjects(DB_Table):
128    def __init__(self,connections=None):
129        super(subjects,self).__init__(_db_path,connections)
130
131        self.T_NAME = "SUBJECTS"
132        self.COLS = {"id": "text not null",
133                    "taj": "numeric not null",
134                    "birth_date": "text",
135                    "sex":"text",
136                    "hand":"text",
137                    "comment":"text"}
138        self._COLS=["id","taj","birth_date","sex","hand","comment"]
139        self.KEY = ["id"]
140
141
142
143class serieses(DB_Table):
144    def __init__(self,connections=None):
145        super(serieses,self).__init__(_db_path,connections)
146
147        self.T_NAME = "SERIES"
148        self.COLS = {"mask": "text not null",
149                    "mod": "text not null",
150                    "version": "numeric not null",
151                    "priority":"numeric not null",
152                    "comment": "text"}
153        self._COLS = ["mask","mod","version","priority","comment"]
154        self.KEY = ["mask"]
155
156    pass
157
158
159
160
161
162def main():
163    su  = subjects()
164    su.create_table()
165    su.add_record(id = "e001",taj=123456789, birth_date = "2017-01-01", sex = "M")
166    su.add_record(id = "e003",taj=123456789, birth_date = "2017-01-01", sex = "M")
167    su.add_record(id = "e002",taj=123456789, birth_date = "2017-01-01", sex = "M")
168    su.add_record(id = "e004",taj=123456789, birth_date = "2017-01-01", sex = "M")
169    su.add_record(id = "",taj=123456789, birth_date = "2017-01-01", sex = "M")
170    su.save_tables()
171
172    s = serieses()
173    s.create_table()
174    s.add_record(mask="asd-qwe",priority=1,mod="test",version=1)
175    s.save_tables()
176
177    m = measurements(su)
178    m.create_table(auto_remove=True)
179    m.add_record(id = "e001", n=1, date_="2017-01-01")
180    m.save_tables()
181
182if __name__ == '__main__':
183    main()