def __search(self):
line = float(self.ui.line_text.text())
point = float(self.ui.point_text.text())
try:
db = Table(self.db_file)
conn = db.create_connection()
with conn:
c = conn.cursor()
current_table = self.ui.comboBox.currentText()
if line > 0 and point > 0:
c.execute(
"SELECT * FROM " + current_table +
" WHERE line =? and point = ? and idx = ?",
(line, point, 1))
else:
c.execute("SELECT * FROM " + current_table)
rows = c.fetchall()
if len(rows) > 0:
for row in rows:
self.ui.output_text.append(str(row))
else:
self.ui.output_text.setText('Not exist')
except Exception as e:
print(str(e))
self.__log('update_db :' + str(e))
def __init__(self, db_file, conn, file_name):
super().__init__()
self.conn = conn
self.file_name = file_name
self.db_file = db_file
self.signals = WorkerSignal()
self.db = Table(self.db_file)
self.parser = Sps21Parser()
def clear_tab(self):
db = Table(self.db_file)
conn = db.create_connection()
current_table = self.ui.comboBox.currentText()
QMessageBox.question(
self, 'Question', 'Do you really want to clear whole table ?',
QMessageBox.Yes | QMessageBox.No | QMessageBox.Cancel,
QMessageBox.No)
if QMessageBox.Yes:
db.clear(conn, current_table)
self.__log('table %s is cleared' % current_table)
def create_db(self):
previous_db_file = self.db_file
self.db_file = filefunction.file_save(self, previous_db_file,
config.db_file_pattern)
if not self.db_file:
return
if not self.db_file.endswith('.sqlite'):
self.db_file += '.sqlite'
db = Table(self.db_file)
print(db)
db.table_crate()
def __update_db(self):
try:
db = Table(self.db_file)
conn = db.create_connection()
# worker = DbUpdate(self.db_file, conn, self.sps_file)
worker = DbXUpdate(self.db_file, conn, self.sps_file)
self.threadpool.start(worker)
worker.signals.start.connect(self.__process_start)
worker.signals.process_max.connect(self.__progress_max)
worker.signals.process.connect(self.__processing)
worker.signals.finished.connect(self.__processed)
except Exception as e:
print(str(e))
self.__log('update_db :' + str(e))
def runner(self):
db = Table(self.db_file)
conn = db.create_connection()
current_table = db.choose_table(self.sps_file)
parser = Sps21Parser()
with open(self.sps_file) as f:
for line in f:
parsed = parser.parse_point(line)
if parsed is not None:
key_list = [parsed[1], parsed[2], parsed[3]]
get_record = db.get_record_for_point(
conn, current_table, key_list)
print(get_record)
def __select_from_db(self):
try:
db = Table(self.db_file)
conn = db.create_connection()
current_table = self.ui.comboBox.currentText()
worker = DbSelect(self.db_file, conn, current_table, self.sps_file,
self.to_file)
self.threadpool.start(worker)
worker.signals.start.connect(self.__process_start)
worker.signals.process_max.connect(self.__progress_max)
worker.signals.process.connect(self.__processing)
worker.signals.finished.connect(self.__processed)
except Exception as e:
print(str(e))
self.__log('update_db :' + str(e))
def run(self):
self.signals.start.emit(False)
line_numbers = check.iter_count(self.from_file_name)
self.signals.process_max.emit(line_numbers)
db = Table(self.db_file)
counter = 0
try:
with open(self.from_file_name) as fr:
with open(self.to_file_name, 'w') as tf:
for line in fr:
parsed = self.parser.parse_point(line)
if parsed:
list = [parsed[1], parsed[2], parsed[3]]
data = db.get_record_for_point(
self.conn, self.table_name, list)
tf.write(str(data) + '\n')
counter += 1
if counter % 10000 == 0:
self.signals.process.emit(counter)
except Exception as e:
print(str(e))
self.signals.process.emit(counter)
self.signals.finished.emit(True)
class DbUpdate(QRunnable):
def __init__(self, db_file, conn, file_name):
super().__init__()
self.conn = conn
self.file_name = file_name
self.db_file = db_file
self.signals = WorkerSignal()
self.db = Table(self.db_file)
self.parser = Sps21Parser()
@pyqtSlot()
def run(self):
self.signals.start.emit(False)
line_numbers = check.iter_count(self.file_name)
# print(line_numbers)
self.signals.process_max.emit(line_numbers)
table_name = self.db.choose_table(self.file_name)
self.conn.execute("PRAGMA synchronous=OFF") # 关闭同步
self.conn.execute("BEGIN TRANSACTION") # 显式开启事务
counter = 0
try:
c = self.conn.cursor()
with open(self.file_name) as fr:
for line in fr:
parsed = self.parser.parse_point(line)
if parsed:
sql_insert = "INSERT OR REPLACE INTO " + table_name + " (line, point, idx, easting, northing, elevation) VALUES (?, ?, ?, ?, ?, ?);"
data = covert_parsed_to_point(parsed)
c.execute(sql_insert, data)
counter += 1
if counter % 100000 == 0:
self.signals.process.emit(counter)
self.conn.commit()
except Exception as e:
print(str(e))
self.signals.process.emit(counter)
self.signals.finished.emit(True)
self.conn.commit()
c.close()
class DbXUpdate(QRunnable):
def __init__(self, db_file, conn, file_name):
super().__init__()
self.conn = conn
self.file_name = file_name
self.db_file = db_file
self.signals = WorkerSignal()
self.db = Table(self.db_file)
self.parser = Sps21Parser()
@pyqtSlot()
def run(self):
self.signals.start.emit(False)
line_numbers = check.iter_count(self.file_name)
self.signals.process_max.emit(line_numbers)
table_name = self.db.choose_table(self.file_name)
self.conn.execute("PRAGMA synchronous=OFF") # 关闭同步
self.conn.execute("BEGIN TRANSACTION") # 显式开启事务
counter = 0
tcounter = 0
try:
c = self.conn.cursor()
osln = 0
ospn = 0
osidx = 0
with open(self.file_name) as fr:
for line in fr:
counter += 1
parsed = self.parser.parse_relation(line)
if parsed:
xps = convert_parsed_to_xps(parsed)
sln = parsed[5]
spn = parsed[6]
spidx = parsed[7]
if sln == osln and spn == ospn and spidx == osidx:
relation.append(xps)
else:
if osln != 0:
sql_insert = "INSERT OR REPLACE INTO " + table_name + " (line, point, idx, relation) VALUES (?, ?, ?, ?);"
data = (osln, ospn, osidx, str(relation))
c.execute(sql_insert, data)
tcounter += 1
relation = []
relation.append(xps)
osln = sln
ospn = spn
osidx = spidx
if tcounter % 10000 == 0:
self.signals.process.emit(counter)
self.conn.commit()
if osln != 0:
sql_insert = "INSERT OR REPLACE INTO " + table_name + " (line, point, idx, relation) VALUES (?, ?, ?, ?);"
data = (osln, ospn, osidx, str(relation))
c.execute(sql_insert, data)
except Exception as e:
print(str(e))
self.signals.process.emit(counter)
self.signals.finished.emit(True)
self.conn.commit()
c.close()