forked from hightoon/harbour_database
-
Notifications
You must be signed in to change notification settings - Fork 0
/
ServerDbLite.py
100 lines (86 loc) · 2.32 KB
/
ServerDbLite.py
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
91
92
93
94
95
96
97
98
99
100
# -*- coding: utf-8 -*-
'''
filename: VehicleDbFe.py
description: deamon running in front end,
read/sync entry records.
Database is checked once an hour,
and new data will be send to server side,
to be restore to sql server db.
'''
import sqlite3, socket, SocketServer
from datetime import datetime
from SqlCmdHelper import sqlite_cmds
rec_db = None
def connect(dbfile='serverDb.db'):
return sqlite3.connect(dbfile)
def create_table(tabname, tabfmt):
if rec_db:
c = rec_db.cursor()
try:
c.execute("CREATE TABLE %s %s"%(tabname, tabfmt))
rec_db.commit()
except sqlite3.OperationalError as e:
print e
c.close()
def drop_table(tabname):
if rec_db:
c = rec_db.cursor()
c.execute("DROP TABLE IF EXISTS %s"%(tabname,))
rec_db.commit()
c.close()
def execute_sql(sql):
if rec_db is not None:
c = rec_db.cursor()
try:
c.execute(sql)
rec_db.commit()
except Exception as e:
print e
c.close()
def db_init():
global rec_db
if rec_db is None:
rec_db = connect()
rec_db.text_factory = str
for tn in sqlite_cmds.keys():
create_table(tn, sqlite_cmds[tn])
rec_db.commit()
def drop_all():
for tn in sqlite_cmds.keys():
drop_table(tn)
def get_stations_from_driver_recs():
conn = connect()
conn.text_factory = str
cur = conn.cursor()
res = cur.execute('SELECT station FROM driver_rec_table').fetchall()
cur.close()
conn.close()
return res
class ClientSockSvr(SocketServer.BaseRequestHandler):
def handle(self):
# self.request is the TCP socket connected to the client
self.data = self.request.recv(1024).strip().decode('utf-8')
print "{} wrote:".format(self.client_address[0])
self._process_data()
def _process_data(self):
print self.data
if self.data.startswith('sql:'):
sql = self.data[4:].strip()
dbconn = connect()
dbconn.text_factory = str
c = dbconn.cursor()
c.execute(sql)
dbconn.commit()
c.close()
dbconn.close()
print 'table updated'
def run_sock_svr():
HOST, PORT = '0.0.0.0', 9999
print 'DB started at ', HOST, PORT
server = SocketServer.TCPServer((HOST, PORT), ClientSockSvr)
server.serve_forever()
def main():
if rec_db is None:
db_init()
if __name__ == '__main__':
main()