개정판 48cabe25
issue #1171: MSSQL 지원 - MSQL을 지원하도록 프로그램 개선
Change-Id: I165ff5d2a26a0f442bac2475b25601a9fb4474ee
| App.spec | ||
|---|---|---|
| 40 | 40 |
strip=False, |
| 41 | 41 |
upx=True, |
| 42 | 42 |
console=False, |
| 43 |
icon='ID2.ico', |
|
| 43 | 44 |
version='version.rc') |
| 44 | 45 |
coll = COLLECT(exe, |
| 45 | 46 |
a.binaries, |
| DTI_PID/DTI_PID/AppDatabase.py | ||
|---|---|---|
| 1 |
# coding: utf-8 |
|
| 2 |
""" This is AppDatabase module """ |
|
| 3 |
|
|
| 4 |
import sqlite3 |
|
| 5 |
import pymssql |
|
| 6 |
|
|
| 7 |
class AppDatabase: |
|
| 8 |
""" This is AppDatabase class """ |
|
| 9 |
def __init__(self, type, host, user, password, db_path): |
|
| 10 |
self._DBType = type |
|
| 11 |
self._host = host |
|
| 12 |
self._user = user |
|
| 13 |
self._password = password |
|
| 14 |
self._db_path = db_path |
|
| 15 |
|
|
| 16 |
@property |
|
| 17 |
def db_type(self): |
|
| 18 |
""" return database type """ |
|
| 19 |
return self._DBType |
|
| 20 |
|
|
| 21 |
@db_type.setter |
|
| 22 |
def db_type(self, value): |
|
| 23 |
self._DBType = value |
|
| 24 |
|
|
| 25 |
@property |
|
| 26 |
def host(self): |
|
| 27 |
""" return host for mssql """ |
|
| 28 |
return self._host |
|
| 29 |
|
|
| 30 |
@host.setter |
|
| 31 |
def host(self, value): |
|
| 32 |
self._host = value |
|
| 33 |
|
|
| 34 |
@property |
|
| 35 |
def user(self): |
|
| 36 |
""" return user for mssql """ |
|
| 37 |
return self._user |
|
| 38 |
|
|
| 39 |
@user.setter |
|
| 40 |
def user(self, value): |
|
| 41 |
self._user = value |
|
| 42 |
|
|
| 43 |
@property |
|
| 44 |
def password(self): |
|
| 45 |
""" return password for mssql """ |
|
| 46 |
return self._password |
|
| 47 |
|
|
| 48 |
@password.setter |
|
| 49 |
def password(self, value): |
|
| 50 |
self._password = value |
|
| 51 |
|
|
| 52 |
@property |
|
| 53 |
def db_name(self): |
|
| 54 |
""" return database name """ |
|
| 55 |
return self._db_path |
|
| 56 |
|
|
| 57 |
@property |
|
| 58 |
def file_path(self): |
|
| 59 |
""" return sqlite database file path """ |
|
| 60 |
return self._db_path |
|
| 61 |
|
|
| 62 |
@file_path.setter |
|
| 63 |
def file_path(self, value): |
|
| 64 |
self._db_path = value |
|
| 65 |
|
|
| 66 |
@property |
|
| 67 |
def place_holder(self): |
|
| 68 |
""" return database placeholder """ |
|
| 69 |
return '?' if self.db_type == 'SQLite' else '%s' if self.db_type == 'MSSQL' else None |
|
| 70 |
|
|
| 71 |
def connect(self): |
|
| 72 |
""" return database connection depends on database type """ |
|
| 73 |
conn = None |
|
| 74 |
if self._DBType == 'SQLite': |
|
| 75 |
conn = sqlite3.connect(self.file_path) |
|
| 76 |
conn.row_factory = sqlite3.Row |
|
| 77 |
#cursor = conn.cursor() |
|
| 78 |
elif self._DBType == 'MSSQL': |
|
| 79 |
conn = pymssql.connect(host=self._host, user=self._user, password=self._password, database=self.db_name, charset='utf8', autocommit=False) |
|
| 80 |
#cursor = conn.cursor(as_dic=True) |
|
| 81 |
|
|
| 82 |
return conn |
|
| 83 |
|
|
| 84 |
def to_sql(self, sql): |
|
| 85 |
""" convert given sql string for database """ |
|
| 86 |
|
|
| 87 |
return sql.replace('?', '%s') if self.db_type == "MSSQL" else sql
|
|
| DTI_PID/DTI_PID/AppDocData.py | ||
|---|---|---|
| 230 | 230 |
if self._colors is None or self._colors == []: |
| 231 | 231 |
self._colors = [] |
| 232 | 232 |
try: |
| 233 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath() , AppDocData.DATABASE) |
|
| 234 |
|
|
| 235 |
conn = sqlite3.connect(dbPath) |
|
| 236 |
cursor = conn.cursor() |
|
| 237 |
sql = 'SELECT UID,RED,GREEN,BLUE FROM Colors' |
|
| 238 |
cursor.execute(sql) |
|
| 239 |
rows = cursor.fetchall() |
|
| 240 |
for row in rows: |
|
| 241 |
self._colors.append(Color(int(row[0]), int(row[1]), int(row[2]), int(row[3]))) |
|
| 233 |
conn = self.project.database.connect() |
|
| 234 |
with conn: |
|
| 235 |
cursor = conn.cursor() |
|
| 236 |
sql = 'SELECT UID,RED,GREEN,BLUE FROM Colors' |
|
| 237 |
cursor.execute(sql) |
|
| 238 |
rows = cursor.fetchall() |
|
| 239 |
for row in rows: |
|
| 240 |
self._colors.append(Color(int(row[0]), int(row[1]), int(row[2]), int(row[3]))) |
|
| 242 | 241 |
# Catch the exception |
| 243 | 242 |
except Exception as ex: |
| 244 | 243 |
# Roll back any change if something goes wrong |
| ... | ... | |
| 247 | 246 |
from App import App |
| 248 | 247 |
message = 'error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno)
|
| 249 | 248 |
App.mainWnd().addMessage.emit(MessageType.Error, message) |
| 250 |
finally: |
|
| 251 |
conn.close() |
|
| 252 | 249 |
|
| 253 | 250 |
return self._colors.pop(random.randrange(0, len(self._colors))) |
| 254 | 251 |
|
| ... | ... | |
| 418 | 415 |
return self.currentPidSource |
| 419 | 416 |
|
| 420 | 417 |
''' |
| 421 |
@brief Check if data exists or not |
|
| 422 |
@author Jeongwoo |
|
| 423 |
@date 2018.05.03 |
|
| 424 |
''' |
|
| 425 |
def isExistData(self, fieldName, data): |
|
| 426 |
rows = None |
|
| 427 |
try: |
|
| 428 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), "ITI_PID.db") |
|
| 429 |
|
|
| 430 |
conn = sqlite3.connect(dbPath) |
|
| 431 |
cursor = conn.cursor() |
|
| 432 |
sql = "" |
|
| 433 |
if isinstance(data, str): |
|
| 434 |
sql = "SELECT * FROM Symbol WHERE " + fieldName + " = '"+ data +"'" |
|
| 435 |
else: |
|
| 436 |
sql = "SELECT * FROM Symbol WHERE " + fieldName + " = "+ str(data) +"" |
|
| 437 |
cursor.execute(sql) |
|
| 438 |
rows = cursor.fetchall() |
|
| 439 |
# Catch the exception |
|
| 440 |
except Exception as ex: |
|
| 441 |
# Roll back any change if something goes wrong |
|
| 442 |
conn.rollback() |
|
| 443 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 444 |
finally: |
|
| 445 |
conn.close() |
|
| 446 |
if rows is not None and len(rows) > 0: |
|
| 447 |
return True |
|
| 448 |
else: |
|
| 449 |
return False |
|
| 450 |
|
|
| 451 |
''' |
|
| 452 | 418 |
@brief Check if exist file name or not |
| 453 | 419 |
@author Jeongwoo |
| 454 | 420 |
@date 2018.05.03 |
| ... | ... | |
| 456 | 422 |
def isExistFileName(self, name): |
| 457 | 423 |
rows = None |
| 458 | 424 |
try: |
| 459 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), "ITI_PID.db")
|
|
| 460 |
conn = sqlite3.connect(dbPath)
|
|
| 461 |
cursor = conn.cursor() |
|
| 462 |
sql = "SELECT * FROM Symbol WHERE name = '"+ name +"'" |
|
| 463 |
cursor.execute(sql) |
|
| 464 |
rows = cursor.fetchall() |
|
| 425 |
conn = self.project.database.connect()
|
|
| 426 |
with conn:
|
|
| 427 |
cursor = conn.cursor()
|
|
| 428 |
sql = "SELECT * FROM Symbol WHERE name = '"+ name +"'"
|
|
| 429 |
cursor.execute(sql)
|
|
| 430 |
rows = cursor.fetchall()
|
|
| 465 | 431 |
# Catch the exception |
| 466 | 432 |
except Exception as ex: |
| 467 | 433 |
# Roll back any change if something goes wrong |
| 468 | 434 |
conn.rollback() |
| 469 | 435 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 470 | 436 |
finally: |
| 471 |
conn.close() |
|
| 472 | 437 |
if rows is not None and len(rows) > 0: |
| 473 | 438 |
return True |
| 474 | 439 |
else: |
| ... | ... | |
| 481 | 446 |
''' |
| 482 | 447 |
def insertSymbol(self, symbol): |
| 483 | 448 |
isAdded = False |
| 484 |
try: |
|
| 485 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 486 |
conn = sqlite3.connect(dbPath) |
|
| 487 |
|
|
| 488 |
INSERT_SYMBOL_SQL = """ |
|
| 489 |
INSERT INTO Symbol(name, SymbolType_UID, threshold, minMatchPoint, isDetectOrigin, rotationCount, ocrOption, isContainChild, originalPoint, connectionPoint, baseSymbol, additionalSymbol, isExceptDetect, hasInstrumentLabel, width, height, flip) |
|
| 490 |
VALUES(?, (select UID from SymbolType where Type=?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); |
|
| 491 |
""" |
|
| 449 |
conn = self.project.database.connect() |
|
| 450 |
with conn: |
|
| 451 |
try: |
|
| 452 |
sql = self.project.database.to_sql("""
|
|
| 453 |
INSERT INTO Symbol(name, SymbolType_UID, threshold, minMatchPoint, isDetectOrigin, rotationCount, ocrOption, isContainChild, originalPoint, connectionPoint, baseSymbol, additionalSymbol, isExceptDetect, hasInstrumentLabel, width, height, flip) |
|
| 454 |
VALUES(?, (select UID from SymbolType where Type=?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); |
|
| 455 |
""") |
|
| 492 | 456 |
|
| 493 |
cursor = conn.cursor() |
|
| 494 |
query = ( symbol.getName(), symbol.getType(), symbol.getThreshold() |
|
| 495 |
, symbol.getMinMatchCount(), symbol.getIsDetectOnOrigin(), symbol.getRotationCount() |
|
| 496 |
, symbol.getOcrOption(), symbol.getIsContainChild() |
|
| 497 |
, symbol.getOriginalPoint(), symbol.getConnectionPoint() |
|
| 498 |
, symbol.getBaseSymbol(), symbol.getAdditionalSymbol(), symbol.getIsExceptDetect(), symbol.getHasInstrumentLabel() |
|
| 499 |
, symbol.width, symbol.height, symbol.detectFlip) |
|
| 500 |
cursor.execute(INSERT_SYMBOL_SQL, query) |
|
| 501 |
conn.commit() |
|
| 502 |
isAdded = True |
|
| 503 |
# Catch the exception |
|
| 504 |
except Exception as ex: |
|
| 505 |
# Roll back any change if something goes wrong |
|
| 506 |
conn.rollback() |
|
| 507 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 508 |
finally: |
|
| 509 |
conn.close() |
|
| 510 |
return (isAdded, symbol.getType(), symbol.getName(), symbol.getPath()) |
|
| 457 |
cursor = conn.cursor() |
|
| 458 |
query = ( symbol.getName(), symbol.getType(), symbol.getThreshold() |
|
| 459 |
, symbol.getMinMatchCount(), symbol.getIsDetectOnOrigin(), symbol.getRotationCount() |
|
| 460 |
, symbol.getOcrOption(), symbol.getIsContainChild() |
|
| 461 |
, symbol.getOriginalPoint(), symbol.getConnectionPoint() |
|
| 462 |
, symbol.getBaseSymbol(), symbol.getAdditionalSymbol(), symbol.getIsExceptDetect(), symbol.getHasInstrumentLabel() |
|
| 463 |
, symbol.width, symbol.height, symbol.detectFlip) |
|
| 464 |
cursor.execute(sql, query) |
|
| 465 |
conn.commit() |
|
| 466 |
isAdded = True |
|
| 467 |
# Catch the exception |
|
| 468 |
except Exception as ex: |
|
| 469 |
# Roll back any change if something goes wrong |
|
| 470 |
conn.rollback() |
|
| 471 |
|
|
| 472 |
from App import App |
|
| 473 |
message = 'error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno)
|
|
| 474 |
App.mainWnd().addMessage.emit(MessageType.Error, message) |
|
| 475 |
finally: |
|
| 476 |
return (isAdded, symbol.getType(), symbol.getName(), symbol.getPath()) |
|
| 511 | 477 |
|
| 512 | 478 |
''' |
| 513 | 479 |
@brief Update symbol in Symbol Table, Moved from SG_DbHelper |
| ... | ... | |
| 516 | 482 |
''' |
| 517 | 483 |
def updateSymbol(self, symbol): |
| 518 | 484 |
isUpdated = False |
| 519 |
try: |
|
| 520 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 521 |
conn = sqlite3.connect(dbPath) |
|
| 522 |
|
|
| 523 |
UPDATE_SYMBOL_SQL = """ |
|
| 524 |
UPDATE Symbol |
|
| 525 |
SET |
|
| 526 |
name = ?, SymbolType_UID=(select UID from SymbolType where Type=?), threshold = ?, minMatchPoint = ?, isDetectOrigin = ?, |
|
| 527 |
rotationCount = ?, ocrOption = ?, isContainChild = ?, originalPoint = ?, connectionPoint = ?, |
|
| 528 |
baseSymbol = ?, additionalSymbol = ?, isExceptDetect = ?, hasInstrumentLabel = ?, width = ?, height = ?, flip = ? |
|
| 529 |
WHERE uid = ? |
|
| 530 |
""" |
|
| 531 |
|
|
| 532 |
cursor = conn.cursor() |
|
| 533 |
query = (symbol.getName(), symbol.getType(), symbol.getThreshold() |
|
| 534 |
, symbol.getMinMatchCount(), symbol.getIsDetectOnOrigin(), symbol.getRotationCount() |
|
| 535 |
, symbol.getOcrOption(), symbol.getIsContainChild() |
|
| 536 |
, symbol.getOriginalPoint(), symbol.getConnectionPoint() |
|
| 537 |
, symbol.getBaseSymbol(), symbol.getAdditionalSymbol(), symbol.getIsExceptDetect(), symbol.getHasInstrumentLabel(), symbol.width, symbol.height, symbol.detectFlip, symbol.getUid()) |
|
| 538 |
cursor.execute(UPDATE_SYMBOL_SQL, query) |
|
| 539 |
conn.commit() |
|
| 540 |
isUpdated = True |
|
| 541 |
# Catch the exception |
|
| 542 |
except Exception as ex: |
|
| 543 |
# Roll back any change if something goes wrong |
|
| 544 |
conn.rollback() |
|
| 545 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 546 |
finally: |
|
| 547 |
conn.close() |
|
| 548 |
return (isUpdated, symbol.getType(), symbol.getName(), symbol.getPath()) |
|
| 485 |
|
|
| 486 |
conn = self.project.database.connect() |
|
| 487 |
with conn: |
|
| 488 |
try: |
|
| 489 |
sql = self.project.database.to_sql("""
|
|
| 490 |
UPDATE Symbol |
|
| 491 |
SET |
|
| 492 |
name = ?, SymbolType_UID=(select UID from SymbolType where Type=?), threshold = ?, minMatchPoint = ?, isDetectOrigin = ?, |
|
| 493 |
rotationCount = ?, ocrOption = ?, isContainChild = ?, originalPoint = ?, connectionPoint = ?, |
|
| 494 |
baseSymbol = ?, additionalSymbol = ?, isExceptDetect = ?, hasInstrumentLabel = ?, width = ?, height = ?, flip = ? |
|
| 495 |
WHERE uid = ? |
|
| 496 |
""") |
|
| 497 |
|
|
| 498 |
cursor = conn.cursor() |
|
| 499 |
query = (symbol.getName(), symbol.getType(), symbol.getThreshold() |
|
| 500 |
, symbol.getMinMatchCount(), symbol.getIsDetectOnOrigin(), symbol.getRotationCount() |
|
| 501 |
, symbol.getOcrOption(), symbol.getIsContainChild() |
|
| 502 |
, symbol.getOriginalPoint(), symbol.getConnectionPoint() |
|
| 503 |
, symbol.getBaseSymbol(), symbol.getAdditionalSymbol(), symbol.getIsExceptDetect(), symbol.getHasInstrumentLabel(), symbol.width, symbol.height, symbol.detectFlip, symbol.getUid()) |
|
| 504 |
cursor.execute(sql, query) |
|
| 505 |
conn.commit() |
|
| 506 |
isUpdated = True |
|
| 507 |
# Catch the exception |
|
| 508 |
except Exception as ex: |
|
| 509 |
# Roll back any change if something goes wrong |
|
| 510 |
conn.rollback() |
|
| 511 |
|
|
| 512 |
from App import App |
|
| 513 |
message = 'error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno)
|
|
| 514 |
App.mainWnd().addMessage.emit(MessageType.Error, message) |
|
| 515 |
finally: |
|
| 516 |
return (isUpdated, symbol.getType(), symbol.getName(), symbol.getPath()) |
|
| 549 | 517 |
|
| 550 | 518 |
''' |
| 551 | 519 |
@brief Get Detecting Target Symbol List (Field 'isExceptDetect' == False(0)) |
| ... | ... | |
| 556 | 524 |
def getTargetSymbolList(self): |
| 557 | 525 |
targetSymbolList = [] |
| 558 | 526 |
|
| 559 |
try: |
|
| 560 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 561 |
|
|
| 562 |
conn = sqlite3.connect(dbPath) |
|
| 527 |
conn = self.database.connect() |
|
| 528 |
with conn: |
|
| 563 | 529 |
cursor = conn.cursor() |
| 564 | 530 |
sql = """SELECT a.UID,a.Name,b.Type,a.Threshold,a.MinMatchPoint,a.IsDetectOrigin,a.RotationCount,a.OCROption,a.IsContainChild,a.OriginalPoint,a.ConnectionPoint, |
| 565 | 531 |
a.BaseSymbol,a.AdditionalSymbol,a.IsExceptDetect,a.HasInstrumentLabel,a.flip FROM Symbol a inner join SymbolType b on a.SymbolType_UID=b.UID WHERE a.IsExceptDetect = 0 order by width * height desc""" |
| ... | ... | |
| 571 | 537 |
targetSymbolList.append(sym) |
| 572 | 538 |
except Exception as ex: |
| 573 | 539 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 574 |
finally: |
|
| 575 |
conn.close() |
|
| 576 | 540 |
|
| 577 | 541 |
return targetSymbolList |
| 578 | 542 |
|
| ... | ... | |
| 692 | 656 |
self.project = project |
| 693 | 657 |
self.makeChildDir() |
| 694 | 658 |
try: |
| 695 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 696 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath() , "ITI_PID.db") |
|
| 697 |
|
|
| 698 |
if not os.path.isfile(dbPath): |
|
| 699 |
templatePath = self.getTemplateDbPath() |
|
| 700 |
templateFile = QFile(templatePath) |
|
| 701 |
templateFile.copy(dbPath) |
|
| 702 |
else: |
|
| 703 |
try: |
|
| 704 |
conn = sqlite3.connect(dbPath) |
|
| 705 |
# Get a cursor object |
|
| 706 |
cursor = conn.cursor() |
|
| 659 |
if self.project.database.db_type == 'SQLite': |
|
| 660 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 661 |
db_path = self.project.database.file_path |
|
| 662 |
|
|
| 663 |
if not os.path.isfile(db_path): |
|
| 664 |
templatePath = self.getTemplateDbPath() |
|
| 665 |
templateFile = QFile(templatePath) |
|
| 666 |
templateFile.copy(db_path) |
|
| 707 | 667 |
|
| 708 |
fileNames = os.listdir(os.path.join(os.path.dirname(os.path.realpath(__file__)), 'Scripts')) |
|
| 709 |
for fileName in fileNames: |
|
| 710 |
if fileName.endswith(".sql") and (1 == len(os.path.splitext(fileName)[0].split('.'))):
|
|
| 711 |
try: |
|
| 712 |
file = QFile(os.path.join(os.path.dirname(os.path.realpath(__file__)), 'Scripts', fileName)) |
|
| 713 |
file.open(QFile.ReadOnly) |
|
| 714 |
sql = file.readAll() |
|
| 715 |
sql = str(sql, encoding='utf8') |
|
| 716 |
cursor.executescript(sql) |
|
| 717 |
finally: |
|
| 718 |
file.close() |
|
| 719 |
conn.commit() |
|
| 668 |
try: |
|
| 669 |
conn = self.project.database.connect() |
|
| 670 |
with conn: |
|
| 671 |
# Get a cursor object |
|
| 672 |
cursor = conn.cursor() |
|
| 673 |
|
|
| 674 |
fileNames = os.listdir(os.path.join(os.path.dirname(os.path.realpath(__file__)), 'Scripts')) |
|
| 675 |
for fileName in fileNames: |
|
| 676 |
if fileName.endswith(".sql") and (1 == len(os.path.splitext(fileName)[0].split('.'))):
|
|
| 677 |
try: |
|
| 678 |
file = QFile(os.path.join(os.path.dirname(os.path.realpath(__file__)), 'Scripts', fileName)) |
|
| 679 |
file.open(QFile.ReadOnly) |
|
| 680 |
sql = file.readAll() |
|
| 681 |
sql = str(sql, encoding='utf8') |
|
| 682 |
cursor.executescript(sql) |
|
| 683 |
finally: |
|
| 684 |
file.close() |
|
| 685 |
conn.commit() |
|
| 720 | 686 |
except Exception as ex: |
| 721 | 687 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 722 |
finally: |
|
| 723 |
conn.close() |
|
| 724 | 688 |
# Catch the exception |
| 725 | 689 |
except Exception as ex: |
| 726 | 690 |
# Roll back any change if something goes wrong |
| ... | ... | |
| 836 | 800 |
deletedTitleBlockProps.append(originTitleBlockProp[0]) |
| 837 | 801 |
|
| 838 | 802 |
# Creates or opens a file called mydb with a SQLite3 DB |
| 839 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), "ITI_PID.db")
|
|
| 840 |
conn = sqlite3.connect(dbPath)
|
|
| 841 |
# Get a cursor object |
|
| 842 |
cursor = conn.cursor() |
|
| 803 |
conn = self.project.database.connect()
|
|
| 804 |
with conn:
|
|
| 805 |
# Get a cursor object
|
|
| 806 |
cursor = conn.cursor()
|
|
| 843 | 807 |
|
| 844 |
for deletedTitleBlockProp in deletedTitleBlockProps: |
|
| 845 |
sql = "delete from TitleBlockProperties where UID='{}'".format(deletedTitleBlockProp)
|
|
| 846 |
cursor.execute(sql) |
|
| 808 |
for deletedTitleBlockProp in deletedTitleBlockProps:
|
|
| 809 |
sql = "delete from TitleBlockProperties where UID='{}'".format(deletedTitleBlockProp)
|
|
| 810 |
cursor.execute(sql)
|
|
| 847 | 811 |
|
| 848 |
for titleBlockProp in titleBlockProps: |
|
| 849 |
sql = "insert or replace into TitleBlockProperties values(?,?,?)" |
|
| 850 |
param = (titleBlockProp[0], titleBlockProp[1], titleBlockProp[2]) # uid, name, area |
|
| 851 |
cursor.execute(sql, param) |
|
| 852 |
conn.commit() |
|
| 812 |
for titleBlockProp in titleBlockProps:
|
|
| 813 |
sql = "insert or replace into TitleBlockProperties values(?,?,?)"
|
|
| 814 |
param = (titleBlockProp[0], titleBlockProp[1], titleBlockProp[2]) # uid, name, area
|
|
| 815 |
cursor.execute(sql, param)
|
|
| 816 |
conn.commit()
|
|
| 853 | 817 |
# Catch the exception |
| 854 | 818 |
except Exception as ex: |
| 855 | 819 |
# Roll back any change if something goes wrong |
| 856 | 820 |
conn.rollback() |
| 857 | 821 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 858 |
finally: |
|
| 859 |
# Close the db connection |
|
| 860 |
conn.close() |
|
| 861 | 822 |
|
| 862 | 823 |
self._titleBlockProperties = None |
| 863 | 824 |
|
| ... | ... | |
| 873 | 834 |
self._titleBlockProperties = [] |
| 874 | 835 |
|
| 875 | 836 |
# Creates or opens a file called mydb with a SQLite3 DB |
| 876 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE)
|
|
| 877 |
db = sqlite3.connect(dbPath)
|
|
| 878 |
# Get a cursor object |
|
| 879 |
cursor = db.cursor()
|
|
| 837 |
conn = self.project.database.connect()
|
|
| 838 |
with conn:
|
|
| 839 |
# Get a cursor object
|
|
| 840 |
cursor = conn.cursor()
|
|
| 880 | 841 |
|
| 881 |
sql = "select UID, Name, AREA from TitleBlockProperties" |
|
| 882 |
cursor.execute(sql) |
|
| 883 |
rows = cursor.fetchall() |
|
| 884 |
for row in rows: |
|
| 885 |
if row[1].split('!@!')[0] != self.imgName:
|
|
| 886 |
continue |
|
| 887 |
else: |
|
| 888 |
attr = [] |
|
| 889 |
attr.append(row[0]) # uid |
|
| 890 |
attr.append(row[1].split('!@!')[1]) # name
|
|
| 891 |
attr.append(row[2]) # area |
|
| 892 |
self._titleBlockProperties.append(attr) |
|
| 893 |
|
|
| 894 |
res = self._titleBlockProperties |
|
| 842 |
sql = "select UID, Name, AREA from TitleBlockProperties"
|
|
| 843 |
cursor.execute(sql)
|
|
| 844 |
rows = cursor.fetchall()
|
|
| 845 |
for row in rows:
|
|
| 846 |
if row[1].split('!@!')[0] != self.imgName:
|
|
| 847 |
continue
|
|
| 848 |
else:
|
|
| 849 |
attr = []
|
|
| 850 |
attr.append(row[0]) # uid
|
|
| 851 |
attr.append(row[1].split('!@!')[1]) # name
|
|
| 852 |
attr.append(row[2]) # area
|
|
| 853 |
self._titleBlockProperties.append(attr)
|
|
| 854 |
|
|
| 855 |
res = self._titleBlockProperties
|
|
| 895 | 856 |
# Catch the exception |
| 896 | 857 |
except Exception as ex: |
| 897 | 858 |
# Roll back any change if something goes wrong |
| 898 |
db.rollback()
|
|
| 859 |
conn.rollback()
|
|
| 899 | 860 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 900 |
finally: |
|
| 901 |
# Close the db connection |
|
| 902 |
db.close() |
|
| 903 | 861 |
else: |
| 904 | 862 |
res = self._titleBlockProperties |
| 905 | 863 |
|
| ... | ... | |
| 922 | 880 |
self._lineNoProperties = [] |
| 923 | 881 |
|
| 924 | 882 |
# Creates or opens a file called mydb with a SQLite3 DB |
| 925 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 926 |
conn = sqlite3.connect(dbPath) |
|
| 883 |
conn = self.project.database.connect() |
|
| 927 | 884 |
with conn: |
| 928 | 885 |
# Get a cursor object |
| 929 | 886 |
cursor = conn.cursor() |
| ... | ... | |
| 962 | 919 |
res = [] |
| 963 | 920 |
try: |
| 964 | 921 |
# Creates or opens a file called mydb with a SQLite3 DB |
| 965 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE)
|
|
| 966 |
db = sqlite3.connect(dbPath)
|
|
| 967 |
# Get a cursor object |
|
| 968 |
cursor = db.cursor()
|
|
| 922 |
conn = self.project.database.connect()
|
|
| 923 |
with conn:
|
|
| 924 |
# Get a cursor object
|
|
| 925 |
cursor = conn.cursor()
|
|
| 969 | 926 |
|
| 970 |
sql = "select UID, Name, DisplayName, Type, LimitNumber, [index] from LineProperties where uid = '{}'".format(UID)
|
|
| 971 |
cursor.execute(sql) |
|
| 972 |
rows = cursor.fetchall() |
|
| 973 |
for row in rows: |
|
| 974 |
attr = SymbolAttr() |
|
| 975 |
attr.UID = row[0] |
|
| 976 |
attr.Attribute = row[1] |
|
| 977 |
attr.DisplayAttribute = row[2] |
|
| 978 |
attr.AttributeType = row[3] |
|
| 979 |
attr.Length = row[4] |
|
| 980 |
res.append(attr) |
|
| 927 |
sql = "select UID, Name, DisplayName, Type, LimitNumber, [index] from LineProperties where uid = '{}'".format(UID)
|
|
| 928 |
cursor.execute(sql)
|
|
| 929 |
rows = cursor.fetchall()
|
|
| 930 |
for row in rows:
|
|
| 931 |
attr = SymbolAttr()
|
|
| 932 |
attr.UID = row[0]
|
|
| 933 |
attr.Attribute = row[1]
|
|
| 934 |
attr.DisplayAttribute = row[2]
|
|
| 935 |
attr.AttributeType = row[3]
|
|
| 936 |
attr.Length = row[4]
|
|
| 937 |
res.append(attr)
|
|
| 981 | 938 |
# Catch the exception |
| 982 | 939 |
except Exception as ex: |
| 983 | 940 |
# Roll back any change if something goes wrong |
| 984 |
db.rollback()
|
|
| 941 |
conn.rollback()
|
|
| 985 | 942 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 986 |
finally: |
|
| 987 |
# Close the db connection |
|
| 988 |
db.close() |
|
| 989 | 943 |
|
| 990 | 944 |
return res |
| 991 | 945 |
|
| ... | ... | |
| 998 | 952 |
from LineTypeConditions import LineTypeConditions |
| 999 | 953 |
|
| 1000 | 954 |
res = [] |
| 1001 |
try: |
|
| 1002 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1003 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1004 |
db = sqlite3.connect(dbPath) |
|
| 1005 |
# Get a cursor object |
|
| 1006 |
cursor = db.cursor() |
|
| 1007 |
|
|
| 1008 |
sql = "select UID,Name,Type1,Conditions1,Type2,Conditions2 from LineTypes order by Name" |
|
| 1009 |
cursor.execute(sql) |
|
| 1010 |
rows = cursor.fetchall() |
|
| 1011 |
for row in rows: |
|
| 1012 |
line_type = LineTypeConditions(row[0], row[1]) |
|
| 1013 |
line_type._conditions[0][0] = row[2] |
|
| 1014 |
line_type._conditions[0][1] = row[3] |
|
| 1015 |
line_type._conditions[1][0] = row[4] |
|
| 1016 |
line_type._conditions[1][1] = row[5] |
|
| 1017 |
res.append(line_type) |
|
| 1018 |
# Catch the exception |
|
| 1019 |
except Exception as ex: |
|
| 1020 |
# Roll back any change if something goes wrong |
|
| 1021 |
db.rollback() |
|
| 1022 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 1023 |
finally: |
|
| 1024 |
# Close the db connection |
|
| 1025 |
db.close() |
|
| 955 |
conn = self.project.database.connect() |
|
| 956 |
with conn: |
|
| 957 |
try: |
|
| 958 |
cursor = conn.cursor() if self.project.database.db_type == 'SQLite' else conn.cursor(as_dict=True) |
|
| 959 |
sql = "select UID,Name,Type1,Conditions1,Type2,Conditions2 from LineTypes order by Name" |
|
| 960 |
cursor.execute(sql) |
|
| 961 |
rows = cursor.fetchall() |
|
| 962 |
for row in rows: |
|
| 963 |
line_type = LineTypeConditions(row['UID'], row['Name']) |
|
| 964 |
line_type._conditions[0][0] = row['Type1'] |
|
| 965 |
line_type._conditions[0][1] = row['Conditions1'] |
|
| 966 |
line_type._conditions[1][0] = row['Type2'] |
|
| 967 |
line_type._conditions[1][1] = row['Conditions2'] |
|
| 968 |
res.append(line_type) |
|
| 969 |
# Catch the exception |
|
| 970 |
except Exception as ex: |
|
| 971 |
# Roll back any change if something goes wrong |
|
| 972 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 1026 | 973 |
|
| 1027 | 974 |
return res |
| 1028 | 975 |
|
| ... | ... | |
| 1074 | 1021 |
conn.close() |
| 1075 | 1022 |
|
| 1076 | 1023 |
''' |
| 1077 |
@brief Update Project UpdatedDate Field
|
|
| 1024 |
@brief update project
|
|
| 1078 | 1025 |
@author Jeongwoo |
| 1079 | 1026 |
@date 2018.04.06 |
| 1080 | 1027 |
@history humkyung 2018.04.19 use getPrjDatabasePath function instead of PROJECT_DB_PATH variable |
| 1081 | 1028 |
''' |
| 1082 |
def updateProjectUpdatedDate(self, id, desc, prj_unit): |
|
| 1083 |
try: |
|
| 1084 |
prjDatabaseFilePath = self.getPrjDatabasePath() |
|
| 1085 |
conn = sqlite3.connect(prjDatabaseFilePath) |
|
| 1086 |
nowDate = datetime.datetime.now().strftime('%Y.%m.%d %H:%M')
|
|
| 1087 |
sql = ''' |
|
| 1088 |
UPDATE Projects |
|
| 1089 |
SET UpdatedDate = ?,[Desc]=?,[Unit]=? |
|
| 1090 |
WHERE Id = ? |
|
| 1091 |
''' |
|
| 1092 |
cur = conn.cursor() |
|
| 1093 |
cur.execute(sql, (nowDate, desc, prj_unit, id)) |
|
| 1094 |
conn.commit() |
|
| 1095 |
except Exception as ex: |
|
| 1096 |
# Roll back any change if something goes wrong |
|
| 1097 |
conn.rollback() |
|
| 1098 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 1099 |
finally: |
|
| 1100 |
conn.close() |
|
| 1029 |
def updateProjectUpdatedDate(self, project): |
|
| 1030 |
prjDatabaseFilePath = self.getPrjDatabasePath() |
|
| 1031 |
conn = sqlite3.connect(prjDatabaseFilePath) |
|
| 1032 |
with conn: |
|
| 1033 |
try: |
|
| 1034 |
nowDate = datetime.datetime.now().strftime('%Y.%m.%d %H:%M')
|
|
| 1035 |
sql = ''' |
|
| 1036 |
UPDATE Projects |
|
| 1037 |
SET UpdatedDate = ?,[Desc]=?,[Unit]=? |
|
| 1038 |
WHERE Id = ? |
|
| 1039 |
''' |
|
| 1040 |
cur = conn.cursor() |
|
| 1041 |
cur.execute(sql, (nowDate, project.desc, project.prj_unit, project.getId())) |
|
| 1042 |
|
|
| 1043 |
sql = 'update DBSettings set DBTypes_UID=(select UID from DBTypes where Name=?),Host=?,User=?,Password=? where Projects_UID=?' |
|
| 1044 |
cur = conn.cursor() |
|
| 1045 |
cur.execute(sql, (project.database.db_type, project.database.host, project.database.user, project.database.password, project.getId())) |
|
| 1046 |
conn.commit() |
|
| 1047 |
except Exception as ex: |
|
| 1048 |
# Roll back any change if something goes wrong |
|
| 1049 |
conn.rollback() |
|
| 1050 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 1101 | 1051 |
|
| 1102 | 1052 |
''' |
| 1103 | 1053 |
@brief get project list from database |
| ... | ... | |
| 1108 | 1058 |
|
| 1109 | 1059 |
projectList = [] |
| 1110 | 1060 |
|
| 1111 |
try: |
|
| 1112 |
conn = sqlite3.connect(self.getPrjDatabasePath()) |
|
| 1061 |
conn = sqlite3.connect(self.getPrjDatabasePath()) |
|
| 1062 |
with conn: |
|
| 1063 |
conn.row_factory = sqlite3.Row |
|
| 1113 | 1064 |
cursor = conn.cursor() |
| 1114 |
sql = 'SELECT id,name,[desc],[unit],path,createddate,updateddate FROM Projects ORDER BY UpdatedDate DESC' |
|
| 1065 |
sql = "select a.Id,a.Name,a.[Desc],a.[Unit],a.Path,a.CreatedDate,a.UpdatedDate,\ |
|
| 1066 |
(select \ |
|
| 1067 |
CASE \ |
|
| 1068 |
WHEN b.DBTypes_UID is NULL THEN 'SQLite' \ |
|
| 1069 |
ELSE (select Name from DBTypes where UID=b.DBTypes_UID) \ |
|
| 1070 |
END \ |
|
| 1071 |
) DBType,b.Host,b.User,b.Password \ |
|
| 1072 |
from projects a left join dbsettings b on a.Id=b.Projects_UID order by strftime(a.UpdatedDate) desc" |
|
| 1115 | 1073 |
try: |
| 1116 | 1074 |
cursor.execute(sql) |
| 1117 | 1075 |
rows = cursor.fetchall() |
| 1118 | 1076 |
for row in rows: |
| 1119 |
if os.path.isdir(row[4]): # check if folder exists
|
|
| 1120 |
projectList.append(Project(row[0], row[1], desc=row[2], prj_unit=row[3], path=row[4], createDate=row[5], updateDate=row[6]))
|
|
| 1077 |
if os.path.isdir(row['Path']): # check if folder exists
|
|
| 1078 |
projectList.append(Project(row)) |
|
| 1121 | 1079 |
except Exception as ex: |
| 1122 | 1080 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1123 |
finally: |
|
| 1124 |
conn.close() |
|
| 1125 | 1081 |
|
| 1126 | 1082 |
return projectList |
| 1127 | 1083 |
|
| ... | ... | |
| 1153 | 1109 |
res = None |
| 1154 | 1110 |
try: |
| 1155 | 1111 |
# Creates or opens a file called mydb with a SQLite3 DB |
| 1156 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1157 |
conn = sqlite3.connect(dbPath) |
|
| 1158 |
# Get a cursor object |
|
| 1159 |
cursor = conn.cursor() |
|
| 1160 |
|
|
| 1161 |
delimiter = None |
|
| 1162 |
sql = "select * from configuration where section='Line No' and key='Delimiter" |
|
| 1163 |
cursor.execute(sql) |
|
| 1164 |
rows = cursor.fetchall() |
|
| 1165 |
if len(rows) == 1: |
|
| 1166 |
delimiter = rows[0][2] |
|
| 1112 |
conn = self.project.database.connect() |
|
| 1113 |
with conn: |
|
| 1114 |
# Get a cursor object |
|
| 1115 |
cursor = conn.cursor() |
|
| 1167 | 1116 |
|
| 1168 |
if delimiter is not None:
|
|
| 1169 |
sql = "select * from configuration where section='Line No' and key='Configuration'"
|
|
| 1117 |
delimiter = None
|
|
| 1118 |
sql = "select * from configuration where section='Line No' and key='Delimiter"
|
|
| 1170 | 1119 |
cursor.execute(sql) |
| 1171 | 1120 |
rows = cursor.fetchall() |
| 1172 | 1121 |
if len(rows) == 1: |
| 1173 |
res = rows[0][2].split(delimiter) |
|
| 1122 |
delimiter = rows[0][2] |
|
| 1123 |
|
|
| 1124 |
if delimiter is not None: |
|
| 1125 |
sql = "select * from configuration where section='Line No' and key='Configuration'" |
|
| 1126 |
cursor.execute(sql) |
|
| 1127 |
rows = cursor.fetchall() |
|
| 1128 |
if len(rows) == 1: |
|
| 1129 |
res = rows[0][2].split(delimiter) |
|
| 1174 | 1130 |
# Catch the exception |
| 1175 | 1131 |
except Exception as ex: |
| 1176 | 1132 |
# Roll back any change if something goes wrong |
| 1177 | 1133 |
conn.rollback() |
| 1178 | 1134 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1179 |
finally: |
|
| 1180 |
# Close the db connection |
|
| 1181 |
conn.close() |
|
| 1182 | 1135 |
|
| 1183 | 1136 |
return res |
| 1184 | 1137 |
|
| ... | ... | |
| 1193 | 1146 |
if len(self._areas) == 0: |
| 1194 | 1147 |
try: |
| 1195 | 1148 |
# Creates or opens a file called mydb with a SQLite3 DB |
| 1196 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE)
|
|
| 1197 |
conn = sqlite3.connect(dbPath)
|
|
| 1198 |
# Get a cursor object |
|
| 1199 |
cursor = conn.cursor() |
|
| 1149 |
conn = self.project.database.connect()
|
|
| 1150 |
with conn:
|
|
| 1151 |
# Get a cursor object
|
|
| 1152 |
cursor = conn.cursor()
|
|
| 1200 | 1153 |
|
| 1201 |
sql = "select * from configuration where section='Area'" |
|
| 1202 |
cursor.execute(sql) |
|
| 1203 |
rows = cursor.fetchall() |
|
| 1204 |
for row in rows: |
|
| 1205 |
name = row[1] |
|
| 1206 |
area = Area(name) |
|
| 1207 |
area.parse(row[2]) |
|
| 1208 |
self._areas.append(area) |
|
| 1154 |
sql = "select * from configuration where section='Area'"
|
|
| 1155 |
cursor.execute(sql)
|
|
| 1156 |
rows = cursor.fetchall()
|
|
| 1157 |
for row in rows:
|
|
| 1158 |
name = row[1]
|
|
| 1159 |
area = Area(name)
|
|
| 1160 |
area.parse(row[2])
|
|
| 1161 |
self._areas.append(area)
|
|
| 1209 | 1162 |
# Catch the exception |
| 1210 | 1163 |
except Exception as ex: |
| 1211 | 1164 |
# Roll back any change if something goes wrong |
| 1212 | 1165 |
conn.rollback() |
| 1213 | 1166 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1214 |
finally: |
|
| 1215 |
# Close the db connection |
|
| 1216 |
conn.close() |
|
| 1217 | 1167 |
|
| 1218 | 1168 |
return self._areas |
| 1219 | 1169 |
|
| ... | ... | |
| 1230 | 1180 |
|
| 1231 | 1181 |
return None |
| 1232 | 1182 |
|
| 1233 |
''' |
|
| 1234 |
@brief get configurations |
|
| 1235 |
@author humkyung |
|
| 1236 |
@date 2018.04.16 |
|
| 1237 |
@history kyouho 2018.07.09 change query method |
|
| 1238 |
''' |
|
| 1239 |
''' |
|
| 1240 | 1183 |
def getConfigs(self, section, key=None): |
| 1184 |
""" get configurations from database """ |
|
| 1241 | 1185 |
res = [] |
| 1242 | 1186 |
|
| 1243 |
try: |
|
| 1244 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1245 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1246 |
conn = sqlite3.connect(dbPath) |
|
| 1247 |
# Get a cursor object |
|
| 1248 |
cursor = conn.cursor() |
|
| 1249 |
|
|
| 1250 |
if key is not None: |
|
| 1251 |
sql = "select * from configuration where section=? and key=?" |
|
| 1252 |
param = (section, key) |
|
| 1253 |
else: |
|
| 1254 |
sql = "select * from configuration where section=?" |
|
| 1255 |
param = (section,) |
|
| 1187 |
if self.configTable is None: |
|
| 1188 |
self.configTable = [] |
|
| 1189 |
try: |
|
| 1190 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1191 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1192 |
conn = self.project.database.connect() |
|
| 1193 |
with conn: |
|
| 1194 |
# Get a cursor object |
|
| 1195 |
cursor = conn.cursor() |
|
| 1256 | 1196 |
|
| 1257 |
cursor.execute(sql, param) |
|
| 1258 |
rows = cursor.fetchall() |
|
| 1259 |
for row in rows: |
|
| 1260 |
res.append(Config(row[0], row[1], row[2])) |
|
| 1261 |
# Catch the exception |
|
| 1262 |
except Exception as ex: |
|
| 1263 |
# Roll back any change if something goes wrong |
|
| 1264 |
conn.rollback() |
|
| 1265 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 1266 |
finally: |
|
| 1267 |
# Close the db connection |
|
| 1268 |
conn.close() |
|
| 1269 |
|
|
| 1270 |
return res |
|
| 1271 |
''' |
|
| 1272 |
def getConfigs(self, section, key=None): |
|
| 1273 |
res = [] |
|
| 1274 |
|
|
| 1275 |
if self.configTable is None: |
|
| 1276 |
self.configTable = [] |
|
| 1277 |
try: |
|
| 1278 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1279 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1280 |
conn = sqlite3.connect(dbPath) |
|
| 1281 |
# Get a cursor object |
|
| 1282 |
cursor = conn.cursor() |
|
| 1283 |
|
|
| 1284 |
sql = "select * from configuration" |
|
| 1285 |
|
|
| 1286 |
cursor.execute(sql) |
|
| 1287 |
rows = cursor.fetchall() |
|
| 1288 |
for row in rows: |
|
| 1289 |
self.configTable.append(Config(row[0], row[1], row[2])) |
|
| 1197 |
sql = "select * from configuration" |
|
| 1198 |
|
|
| 1199 |
cursor.execute(sql) |
|
| 1200 |
rows = cursor.fetchall() |
|
| 1201 |
for row in rows: |
|
| 1202 |
self.configTable.append(Config(row[0], row[1], row[2])) |
|
| 1290 | 1203 |
# Catch the exception |
| 1291 | 1204 |
except Exception as ex: |
| 1292 | 1205 |
# Roll back any change if something goes wrong |
| 1293 | 1206 |
conn.rollback() |
| 1294 | 1207 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1295 |
finally: |
|
| 1296 |
# Close the db connection |
|
| 1297 |
conn.close() |
|
| 1298 | 1208 |
|
| 1299 | 1209 |
if key is not None: |
| 1300 | 1210 |
for con in self.configTable: |
| ... | ... | |
| 1353 | 1263 |
kyouho 2018.07.09 change query method |
| 1354 | 1264 |
''' |
| 1355 | 1265 |
def saveConfigs(self, configs): |
| 1356 |
try: |
|
| 1357 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1358 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1359 |
conn = sqlite3.connect(dbPath, isolation_level=None) |
|
| 1360 |
# Get a cursor object |
|
| 1361 |
cursor = conn.cursor() |
|
| 1362 |
|
|
| 1363 |
for config in configs: |
|
| 1364 |
if type(config) is Config: |
|
| 1365 |
value = config.value |
|
| 1366 |
if type(value) is str and "'" in value: |
|
| 1367 |
value = value.replace("'", "''")
|
|
| 1368 |
|
|
| 1369 |
sql = "insert or replace into configuration values(?,?,?)" |
|
| 1370 |
param = (config.section, config.key, value) |
|
| 1266 |
conn = self.project.database.connect() |
|
| 1267 |
with conn: |
|
| 1268 |
try: |
|
| 1269 |
# Get a cursor object |
|
| 1270 |
cursor = conn.cursor() |
|
| 1371 | 1271 |
|
| 1372 |
cursor.execute(sql, param) |
|
| 1373 |
elif hasattr(config, 'toSql'): |
|
| 1374 |
sql = config.toSql() |
|
| 1375 |
if type(sql) is list: |
|
| 1376 |
for item in sql: |
|
| 1377 |
if item is not None and 2 == len(item): |
|
| 1378 |
cursor.execute(item[0], item[1]) |
|
| 1379 |
else: |
|
| 1380 |
if sql is not None and 2 == len(sql): |
|
| 1381 |
cursor.execute(sql[0], sql[1]) |
|
| 1272 |
for config in configs: |
|
| 1273 |
if type(config) is Config: |
|
| 1274 |
value = config.value |
|
| 1275 |
if type(value) is str and "'" in value: |
|
| 1276 |
value = value.replace("'", "''")
|
|
| 1277 |
|
|
| 1278 |
sql = self.project.database.to_sql("insert into configuration(Section,[Key],Value) select ?,?,? where not exists(select 1 from configuration where Section=? and [Key]=?)")
|
|
| 1279 |
param = (config.section, config.key, str(value), config.section, config.key) |
|
| 1280 |
cursor.execute(sql, param) |
|
| 1281 |
sql = self.project.database.to_sql("update configuration set Value=? where Section=? and [Key]=?")
|
|
| 1282 |
param = (str(value), config.section, config.key) |
|
| 1283 |
cursor.execute(sql, param) |
|
| 1284 |
elif hasattr(config, 'toSql'): |
|
| 1285 |
sql = config.toSql() |
|
| 1286 |
if type(sql) is list: |
|
| 1287 |
for item in sql: |
|
| 1288 |
if item is not None and 2 == len(item): |
|
| 1289 |
cursor.execute(self.project.database.to_sql(item[0]), item[1]) |
|
| 1290 |
else: |
|
| 1291 |
if sql is not None and 2 == len(sql): |
|
| 1292 |
cursor.execute(self.project.database.to_sql(sql[0]), sql[1]) |
|
| 1293 |
conn.commit() |
|
| 1294 |
# Catch the exception |
|
| 1295 |
except Exception as ex: |
|
| 1296 |
# Roll back any change if something goes wrong |
|
| 1297 |
conn.rollback() |
|
| 1382 | 1298 |
|
| 1383 |
conn.commit() |
|
| 1384 |
# Catch the exception |
|
| 1385 |
except Exception as ex: |
|
| 1386 |
# Roll back any change if something goes wrong |
|
| 1387 |
conn.rollback() |
|
| 1388 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 1389 |
finally: |
|
| 1390 |
# Close the db connection |
|
| 1391 |
conn.close() |
|
| 1299 |
from App import App |
|
| 1300 |
message = 'error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno)
|
|
| 1301 |
App.mainWnd().addMessage.emit(MessageType.Error, message) |
|
| 1392 | 1302 |
|
| 1393 | 1303 |
def saveAppConfigs(self, configs): |
| 1394 | 1304 |
""" |
| ... | ... | |
| 1430 | 1340 |
''' |
| 1431 | 1341 |
def deleteConfigs(self, section, key=None): |
| 1432 | 1342 |
try: |
| 1433 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1434 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), "ITI_PID.db") |
|
| 1435 |
conn = sqlite3.connect(dbPath) |
|
| 1436 |
# Get a cursor object |
|
| 1437 |
cursor = conn.cursor() |
|
| 1343 |
conn = self.project.database.connect() |
|
| 1344 |
with conn: |
|
| 1345 |
# Get a cursor object |
|
| 1346 |
cursor = conn.cursor() |
|
| 1438 | 1347 |
|
| 1439 |
if key is not None: |
|
| 1440 |
sql = "delete from configuration where section='{}' and key='{}'".format(section, key)
|
|
| 1441 |
else: |
|
| 1442 |
sql = "delete from configuration where section='{}'".format(section)
|
|
| 1443 |
cursor.execute(sql) |
|
| 1348 |
if key is not None:
|
|
| 1349 |
sql = "delete from configuration where section='{}' and key='{}'".format(section, key)
|
|
| 1350 |
else:
|
|
| 1351 |
sql = "delete from configuration where section='{}'".format(section)
|
|
| 1352 |
cursor.execute(sql)
|
|
| 1444 | 1353 |
|
| 1445 |
conn.commit() |
|
| 1354 |
conn.commit()
|
|
| 1446 | 1355 |
# Catch the exception |
| 1447 | 1356 |
except Exception as ex: |
| 1448 | 1357 |
# Roll back any change if something goes wrong |
| 1449 | 1358 |
conn.rollback() |
| 1450 | 1359 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1451 |
finally: |
|
| 1452 |
# Close the db connection |
|
| 1453 |
conn.close() |
|
| 1454 | 1360 |
|
| 1455 | 1361 |
def deleteAppConfigs(self, section, key=None): |
| 1456 | 1362 |
""" |
| ... | ... | |
| 1498 | 1404 |
elif 0 == len(matches): |
| 1499 | 1405 |
self._areas.append(area) |
| 1500 | 1406 |
|
| 1501 |
try:
|
|
| 1502 |
# Creates or opens a file called mydb with a SQLite3 DB
|
|
| 1503 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), "ITI_PID.db")
|
|
| 1504 |
conn = sqlite3.connect(dbPath)
|
|
| 1505 |
# Get a cursor object |
|
| 1506 |
cursor = conn.cursor() |
|
| 1407 |
# Creates or opens a file called mydb with a SQLite3 DB
|
|
| 1408 |
conn = self.project.database.connect()
|
|
| 1409 |
with conn:
|
|
| 1410 |
try:
|
|
| 1411 |
# Get a cursor object
|
|
| 1412 |
cursor = conn.cursor()
|
|
| 1507 | 1413 |
|
| 1508 |
for area in self._areas: |
|
| 1509 |
sql = "insert or replace into configuration values('Area','{}','({},{}),({},{})')".format(area.name, round(area.x), round(area.y), round(area.width), round(area.height))
|
|
| 1510 |
cursor.execute(sql) |
|
| 1414 |
for area in self._areas: |
|
| 1415 |
sql = "insert into configuration select 'Area','{}','({},{}),({},{})' where not exists(select 1 from configuration where Section='Area' and [Key]='{}')".format(
|
|
| 1416 |
area.name, round(area.x), round(area.y), round(area.width), round(area.height), area.name) |
|
| 1417 |
cursor.execute(sql) |
|
| 1418 |
sql = "update configuration set Value='({},{}),({},{})' where Section='Area' and [Key]='{}'".format(round(area.x), round(area.y), round(area.width), round(area.height), area.name)
|
|
| 1419 |
cursor.execute(sql) |
|
| 1420 |
conn.commit() |
|
| 1421 |
# Catch the exception |
|
| 1422 |
except Exception as ex: |
|
| 1423 |
# Roll back any change if something goes wrong |
|
| 1424 |
conn.rollback() |
|
| 1511 | 1425 |
|
| 1512 |
conn.commit() |
|
| 1513 |
# Catch the exception |
|
| 1514 |
except Exception as ex: |
|
| 1515 |
# Roll back any change if something goes wrong |
|
| 1516 |
conn.rollback() |
|
| 1517 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 1518 |
finally: |
|
| 1519 |
# Close the db connection |
|
| 1520 |
self._areas = [] |
|
| 1521 |
conn.close() |
|
| 1426 |
from App import App |
|
| 1427 |
message = 'error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno)
|
|
| 1428 |
App.mainWnd().addMessage.emit(MessageType.Error, message) |
|
| 1429 |
finally: |
|
| 1430 |
# Close the db connection |
|
| 1431 |
self._areas = [] |
|
| 1522 | 1432 |
|
| 1523 |
''' |
|
| 1524 |
@brief get symbol name list |
|
| 1525 |
''' |
|
| 1526 | 1433 |
def getSymbolNameList(self): |
| 1434 |
""" get symbol name list """ |
|
| 1527 | 1435 |
symbolNametList = [] |
| 1528 | 1436 |
|
| 1529 |
try: |
|
| 1530 |
dbPath = self.getCurrentProject().getDbFilePath() + "/ITI_PID.db" |
|
| 1531 |
|
|
| 1532 |
conn = sqlite3.connect(dbPath) |
|
| 1437 |
conn = self.project.database.connect() |
|
| 1438 |
with conn: |
|
| 1533 | 1439 |
cursor = conn.cursor() |
| 1534 | 1440 |
sql = 'SELECT * FROM SymbolName' |
| 1535 | 1441 |
try: |
| ... | ... | |
| 1539 | 1445 |
symbolNametList.append(row[4]) # Name String |
| 1540 | 1446 |
except Exception as ex: |
| 1541 | 1447 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1542 |
finally: |
|
| 1543 |
conn.close() |
|
| 1544 | 1448 |
|
| 1545 | 1449 |
return symbolNametList |
| 1546 | 1450 |
|
| ... | ... | |
| 1553 | 1457 |
def getSymbolNameListByType(self, type): |
| 1554 | 1458 |
symbolNametList = [] |
| 1555 | 1459 |
|
| 1556 |
try: |
|
| 1557 |
dbPath = self.getCurrentProject().getDbFilePath() + "/ITI_PID.db" |
|
| 1558 |
|
|
| 1559 |
conn = sqlite3.connect(dbPath) |
|
| 1460 |
conn = self.project.database.connect() |
|
| 1461 |
with conn: |
|
| 1560 | 1462 |
cursor = conn.cursor() |
| 1561 | 1463 |
sql = '' |
| 1562 | 1464 |
if type is not None: |
| 1563 |
sql = 'SELECT * FROM SymbolName WHERE type = "' + type + '"'
|
|
| 1465 |
sql = self.project.database.to_sql('SELECT * FROM SymbolName WHERE [Type]=?')
|
|
| 1564 | 1466 |
try: |
| 1565 |
cursor.execute(sql) |
|
| 1467 |
cursor.execute(sql, (type,))
|
|
| 1566 | 1468 |
rows = cursor.fetchall() |
| 1567 | 1469 |
for row in rows: |
| 1568 | 1470 |
symbolNametList.append(row[4]) # Name String |
| 1569 | 1471 |
except Exception as ex: |
| 1570 | 1472 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1571 |
finally: |
|
| 1572 |
conn.close() |
|
| 1573 | 1473 |
|
| 1574 | 1474 |
return symbolNametList |
| 1575 | 1475 |
|
| ... | ... | |
| 1578 | 1478 |
''' |
| 1579 | 1479 |
def deleteSymbol(self, fileName): |
| 1580 | 1480 |
ret = False |
| 1581 |
try: |
|
| 1582 |
dbPath = self.getCurrentProject().getPath() + "/db/ITI_PID.db" |
|
| 1583 |
conn = sqlite3.connect(dbPath) |
|
| 1584 |
cursor = conn.cursor() |
|
| 1585 |
sql = "DELETE FROM Symbol WHERE name = ?" |
|
| 1481 |
|
|
| 1482 |
conn = self.project.database.connect() |
|
| 1483 |
with conn: |
|
| 1586 | 1484 |
try: |
| 1587 |
cursor.execute(sql, (fileName,)) |
|
| 1588 |
conn.commit() |
|
| 1589 |
ret = True |
|
| 1590 |
except Exception as ex: |
|
| 1591 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 1592 |
ret = False |
|
| 1593 |
finally: |
|
| 1594 |
conn.close() |
|
| 1595 |
return (ret, fileName) |
|
| 1485 |
cursor = conn.cursor() |
|
| 1486 |
sql = self.project.database.to_sql("DELETE FROM Symbol WHERE name = ?")
|
|
| 1487 |
try: |
|
| 1488 |
cursor.execute(sql, (fileName,)) |
|
| 1489 |
conn.commit() |
|
| 1490 |
ret = True |
|
| 1491 |
except Exception as ex: |
|
| 1492 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 1493 |
ret = False |
|
| 1494 |
finally: |
|
| 1495 |
return (ret, fileName) |
|
| 1596 | 1496 |
|
| 1597 | 1497 |
''' |
| 1598 | 1498 |
@brief get symbol name |
| ... | ... | |
| 1601 | 1501 |
def getSymbolByQuery(self, fieldName, param): |
| 1602 | 1502 |
ret = None |
| 1603 | 1503 |
|
| 1604 |
try: |
|
| 1605 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1606 |
conn = sqlite3.connect(dbPath) |
|
| 1504 |
conn = self.project.database.connect() |
|
| 1505 |
with conn: |
|
| 1607 | 1506 |
cursor = conn.cursor() |
| 1608 |
sql = """SELECT a.UID,a.Name,b.Type,a.Threshold,a.MinMatchPoint,a.IsDetectOrigin,a.RotationCount,a.OCROption,a.IsContainChild,a.OriginalPoint,a.ConnectionPoint, |
|
| 1609 |
a.BaseSymbol,a.AdditionalSymbol,a.IsExceptDetect,a.HasInstrumentLabel,a.flip FROM Symbol a inner join SymbolType b on a.SymbolType_UID=b.UID WHERE """ + "a." + fieldName + '=?' |
|
| 1507 |
sql = self.project.database.to_sql("""SELECT a.UID,a.Name,b.Type,a.Threshold,a.MinMatchPoint,a.IsDetectOrigin,a.RotationCount,a.OCROption,a.IsContainChild,a.OriginalPoint,a.ConnectionPoint,
|
|
| 1508 |
a.BaseSymbol,a.AdditionalSymbol,a.IsExceptDetect,a.HasInstrumentLabel,a.flip FROM Symbol a inner join SymbolType b on a.SymbolType_UID=b.UID WHERE """ + "a." + fieldName + '=?')
|
|
| 1610 | 1509 |
try: |
| 1611 | 1510 |
cursor.execute(sql, (param,)) |
| 1612 | 1511 |
rows = cursor.fetchall() |
| ... | ... | |
| 1617 | 1516 |
, symbolTuple[9], symbolTuple[10], symbolTuple[11], symbolTuple[12], symbolTuple[13], symbolTuple[14], symbolTuple[0], detectFlip=symbolTuple[15]) ## uid is last item |
| 1618 | 1517 |
except Exception as ex: |
| 1619 | 1518 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1620 |
finally: |
|
| 1621 |
conn.close() |
|
| 1622 | 1519 |
|
| 1623 | 1520 |
return ret |
| 1624 | 1521 |
|
| ... | ... | |
| 1629 | 1526 |
def getSymbolListByType(self, field_name=None, param=None): |
| 1630 | 1527 |
ret = [] |
| 1631 | 1528 |
|
| 1632 |
try: |
|
| 1633 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1634 |
conn = sqlite3.connect(dbPath) |
|
| 1529 |
conn = self.project.database.connect() |
|
| 1530 |
with conn: |
|
| 1635 | 1531 |
cursor = conn.cursor() |
| 1636 | 1532 |
if field_name is not None and param is not None: |
| 1637 | 1533 |
sql = """SELECT a.UID,a.Name,b.Type,a.Threshold,a.MinMatchPoint,a.IsDetectOrigin,a.RotationCount,a.OCROption,a.IsContainChild,a.OriginalPoint,a.ConnectionPoint, |
| 1638 | 1534 |
a.BaseSymbol,a.AdditionalSymbol,a.IsExceptDetect,a.HasInstrumentLabel,a.flip |
| 1639 |
FROM Symbol a inner join SymbolType b on a.SymbolType_UID=b.UID WHERE SymbolType_UID = (select UID from SymbolType where {}=?)""".format(field_name)
|
|
| 1535 |
FROM Symbol a inner join SymbolType b on a.SymbolType_UID=b.UID WHERE SymbolType_UID = (select UID from SymbolType where {}={})""".format(field_name, self.project.database.place_holder)
|
|
| 1640 | 1536 |
else: |
| 1641 | 1537 |
sql = """SELECT a.UID,a.Name,b.Type,a.Threshold,a.MinMatchPoint,a.IsDetectOrigin,a.RotationCount,a.OCROption,a.IsContainChild,a.OriginalPoint,a.ConnectionPoint, |
| 1642 | 1538 |
a.BaseSymbol,a.AdditionalSymbol,a.IsExceptDetect,a.HasInstrumentLabel,a.flip FROM Symbol a inner join SymbolType b on a.SymbolType_UID=b.UID""" |
| ... | ... | |
| 1651 | 1547 |
ret.append(sym) |
| 1652 | 1548 |
except Exception as ex: |
| 1653 | 1549 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1654 |
finally: |
|
| 1655 |
conn.close() |
|
| 1656 | 1550 |
|
| 1657 | 1551 |
return ret |
| 1658 | 1552 |
|
| ... | ... | |
| 1666 | 1560 |
''' |
| 1667 | 1561 |
def getNomialPipeSizeData(self, forCheckLineNumber = False, orderStr = "CODE"): |
| 1668 | 1562 |
res = [] |
| 1669 |
try: |
|
| 1670 |
configs = self.getConfigs('Line No', 'Size Unit')
|
|
| 1671 |
sizeUnit = configs[0].value if 1 == len(configs) else 'Metric' |
|
| 1563 |
configs = self.getConfigs('Line No', 'Size Unit')
|
|
| 1564 |
sizeUnit = configs[0].value if 1 == len(configs) else 'Metric' |
|
| 1672 | 1565 |
|
| 1673 |
# Creates or opens a file called mydb with a SQLite3 DB
|
|
| 1674 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE)
|
|
| 1675 |
conn = sqlite3.connect(dbPath)
|
|
| 1676 |
# Get a cursor object |
|
| 1677 |
cursor = conn.cursor() |
|
| 1566 |
conn = self.project.database.connect()
|
|
| 1567 |
with conn:
|
|
| 1568 |
try:
|
|
| 1569 |
# Get a cursor object
|
|
| 1570 |
cursor = conn.cursor()
|
|
| 1678 | 1571 |
|
| 1679 |
sql = "select UID,Code,Metric,Inch,InchStr,AllowableInchStr,MetricStr,AllowableMetricStr from NominalDiameter ORDER BY {} ASC".format(orderStr)
|
|
| 1680 |
cursor.execute(sql) |
|
| 1681 |
rows = cursor.fetchall() |
|
| 1682 |
for row in rows: |
|
| 1683 |
pipeSize = NominalPipeSize(row[0], row[1], float(row[2]) if row[2] else None, float(row[3]) if row[3] else None, row[4], row[5], row[6], row[7]) |
|
| 1684 |
pipeSize.sizeUnit = sizeUnit |
|
| 1685 |
if forCheckLineNumber: |
|
| 1686 |
if sizeUnit == 'Inch' and pipeSize.inchStr: |
|
| 1687 |
res.append(pipeSize.inchStr) |
|
| 1688 |
elif sizeUnit == 'Metric' and pipeSize.metricStr: |
|
| 1689 |
res.append(pipeSize.metricStr) |
|
| 1690 |
else: |
|
| 1691 |
res.append(pipeSize) |
|
| 1572 |
sql = "select UID,Code,Metric,Inch,InchStr,AllowableInchStr,MetricStr,AllowableMetricStr from NominalDiameter ORDER BY {} ASC".format(orderStr)
|
|
| 1573 |
cursor.execute(sql) |
|
| 1574 |
rows = cursor.fetchall() |
|
| 1575 |
for row in rows: |
|
| 1576 |
pipeSize = NominalPipeSize(row[0], row[1], float(row[2]) if row[2] else None, float(row[3]) if row[3] else None, row[4], row[5], row[6], row[7]) |
|
| 1577 |
pipeSize.sizeUnit = sizeUnit |
|
| 1578 |
if forCheckLineNumber: |
|
| 1579 |
if sizeUnit == 'Inch' and pipeSize.inchStr: |
|
| 1580 |
res.append(pipeSize.inchStr) |
|
| 1581 |
elif sizeUnit == 'Metric' and pipeSize.metricStr: |
|
| 1582 |
res.append(pipeSize.metricStr) |
|
| 1583 |
else: |
|
| 1584 |
res.append(pipeSize) |
|
| 1585 |
# Catch the exception |
|
| 1586 |
except Exception as ex: |
|
| 1587 |
# Roll back any change if something goes wrong |
|
| 1588 |
conn.rollback() |
|
| 1692 | 1589 |
|
| 1693 |
# Catch the exception |
|
| 1694 |
except Exception as ex: |
|
| 1695 |
# Roll back any change if something goes wrong |
|
| 1696 |
conn.rollback() |
|
| 1697 |
|
|
| 1698 |
from App import App |
|
| 1699 |
message = 'error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno)
|
|
| 1700 |
App.mainWnd().addMessage.emit(MessageType.Error, message) |
|
| 1701 |
finally: |
|
| 1702 |
# Close the db connection |
|
| 1703 |
conn.close() |
|
| 1590 |
from App import App |
|
| 1591 |
message = 'error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno)
|
|
| 1592 |
App.mainWnd().addMessage.emit(MessageType.Error, message) |
|
| 1704 | 1593 |
|
| 1705 | 1594 |
return res |
| 1706 | 1595 |
|
| ... | ... | |
| 1710 | 1599 |
@date 2018.07.16 |
| 1711 | 1600 |
''' |
| 1712 | 1601 |
def insertNomialPipeSize(self, pipeSizes): |
| 1713 |
try: |
|
| 1714 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1715 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1716 |
conn = sqlite3.connect(dbPath) |
|
| 1717 |
# Get a cursor object |
|
| 1718 |
cursor = conn.cursor() |
|
| 1719 |
for pipeSize in pipeSizes: |
|
| 1720 |
sql = pipeSize.toSql() |
|
| 1721 |
if type(sql) is list and len(sql) == 1: |
|
| 1722 |
cursor.execute(sql[0][0], sql[0][1]) |
|
| 1602 |
conn = self.project.database.connect() |
|
| 1603 |
with conn: |
|
| 1604 |
try: |
|
| 1605 |
# Get a cursor object |
|
| 1606 |
cursor = conn.cursor() |
|
| 1607 |
for pipeSize in pipeSizes: |
|
| 1608 |
sql = pipeSize.toSql() |
|
| 1609 |
if type(sql) is list and len(sql) == 1: |
|
| 1610 |
cursor.execute(self.project.database.to_sql(sql[0][0]), sql[0][1]) |
|
| 1723 | 1611 |
|
| 1724 |
conn.commit() |
|
| 1612 |
conn.commit()
|
|
| 1725 | 1613 |
# Catch the exception |
| 1726 |
except Exception as ex: |
|
| 1727 |
# Roll back any change if something goes wrong |
|
| 1728 |
conn.rollback() |
|
| 1729 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
|
| 1730 |
finally:
|
|
| 1731 |
# Close the db connection
|
|
| 1732 |
conn.close()
|
|
| 1614 |
except Exception as ex:
|
|
| 1615 |
# Roll back any change if something goes wrong
|
|
| 1616 |
conn.rollback()
|
|
| 1617 |
|
|
| 1618 |
from App import App
|
|
| 1619 |
message = 'error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno)
|
|
| 1620 |
App.mainWnd().addMessage.emit(MessageType.Error, message)
|
|
| 1733 | 1621 |
|
| 1734 | 1622 |
''' |
| 1735 | 1623 |
@brief delete NominalDiameter table |
| ... | ... | |
| 1737 | 1625 |
@date 2018.07.16 |
| 1738 | 1626 |
''' |
| 1739 | 1627 |
def deleteNomialPipeSize(self): |
| 1740 |
try: |
|
| 1741 |
dbPath = os.path.join(self.getCurrentProject().getPath(), 'db', AppDocData.DATABASE) |
|
| 1742 |
conn = sqlite3.connect(dbPath) |
|
| 1628 |
conn = self.project.database.connect() |
|
| 1629 |
with conn: |
|
| 1743 | 1630 |
cursor = conn.cursor() |
| 1744 | 1631 |
sql = "DELETE FROM NominalDiameter" |
| 1745 | 1632 |
try: |
| ... | ... | |
| 1747 | 1634 |
conn.commit() |
| 1748 | 1635 |
except Exception as ex: |
| 1749 | 1636 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1750 |
finally: |
|
| 1751 |
conn.close() |
|
| 1752 | 1637 |
|
| 1753 | 1638 |
''' |
| 1754 | 1639 |
@brief convert inch to metric |
| ... | ... | |
| 1758 | 1643 |
def convertInchToMetric(self, inch): |
| 1759 | 1644 |
result = '' |
| 1760 | 1645 |
try: |
| 1761 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1762 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1763 |
conn = sqlite3.connect(dbPath) |
|
| 1764 |
# Get a cursor object |
|
| 1765 |
cursor = conn.cursor() |
|
| 1766 |
|
|
| 1767 |
sql = "select MetricStr from NominalDiameter WHERE InchStr = ?" |
|
| 1768 |
param = (inch,) |
|
| 1769 |
cursor.execute(sql, param) |
|
| 1770 |
rows = cursor.fetchall() |
|
| 1646 |
conn = self.project.database.connect() |
|
| 1647 |
with conn: |
|
| 1648 |
# Get a cursor object |
|
| 1649 |
cursor = conn.cursor() |
|
| 1650 |
|
|
| 1651 |
sql = "select MetricStr from NominalDiameter WHERE InchStr = ?" |
|
| 1652 |
param = (inch,) |
|
| 1653 |
cursor.execute(sql, param) |
|
| 1654 |
rows = cursor.fetchall() |
|
| 1771 | 1655 |
|
| 1772 |
if rows: |
|
| 1773 |
result = rows[0][0] |
|
| 1774 |
# Catch the exception |
|
| 1656 |
if rows:
|
|
| 1657 |
result = rows[0][0]
|
|
| 1658 |
# Catch the exception
|
|
| 1775 | 1659 |
except Exception as ex: |
| 1776 | 1660 |
# Roll back any change if something goes wrong |
| 1777 | 1661 |
conn.rollback() |
| 1778 | 1662 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1779 |
finally: |
|
| 1780 |
# Close the db connection |
|
| 1781 |
conn.close() |
|
| 1782 | 1663 |
|
| 1783 | 1664 |
return result |
| 1784 | 1665 |
|
| ... | ... | |
| 1791 | 1672 |
result = 0 |
| 1792 | 1673 |
|
| 1793 | 1674 |
try: |
| 1794 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1795 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1796 |
conn = sqlite3.connect(dbPath) |
|
| 1797 |
# Get a cursor object |
|
| 1798 |
cursor = conn.cursor() |
|
| 1675 |
conn = self.project.database.connect() |
|
| 1676 |
with conn: |
|
| 1677 |
# Get a cursor object |
|
| 1678 |
cursor = conn.cursor() |
|
| 1799 | 1679 |
|
| 1800 |
sql = "select MAX(UID) from Colors" |
|
| 1801 |
cursor.execute(sql) |
|
| 1802 |
rows = cursor.fetchall() |
|
| 1680 |
sql = "select MAX(UID) from Colors"
|
|
| 1681 |
cursor.execute(sql)
|
|
| 1682 |
rows = cursor.fetchall()
|
|
| 1803 | 1683 |
|
| 1804 |
result = rows[0][0] |
|
| 1805 |
# Catch the exception |
|
| 1684 |
result = rows[0][0]
|
|
| 1685 |
# Catch the exception
|
|
| 1806 | 1686 |
except Exception as ex: |
| 1807 | 1687 |
# Roll back any change if something goes wrong |
| 1808 | 1688 |
conn.rollback() |
| 1809 | 1689 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1810 |
finally: |
|
| 1811 |
# Close the db connection |
|
| 1812 |
conn.close() |
|
| 1813 | 1690 |
|
| 1814 | 1691 |
return result |
| 1815 | 1692 |
|
| ... | ... | |
| 1820 | 1697 |
''' |
| 1821 | 1698 |
def setPropertyColor(self, _color): |
| 1822 | 1699 |
try: |
| 1823 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1824 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1825 |
conn = sqlite3.connect(dbPath) |
|
| 1826 |
# Get a cursor object |
|
| 1827 |
cursor = conn.cursor() |
|
| 1828 |
sql = "INSERT INTO Colors(UID, RED, GREEN, BLUE, PROPERTY, VALUE) VALUES(?,?,?,?,?,?)" |
|
| 1829 |
param = (_color.index, _color.red, _color.green, _color.blue, _color._property, _color.value) |
|
| 1830 |
cursor.execute(sql, param) |
|
| 1831 |
conn.commit() |
|
| 1700 |
conn = self.project.database.connect() |
|
| 1701 |
with conn: |
|
| 1702 |
# Get a cursor object |
|
| 1703 |
cursor = conn.cursor() |
|
| 1704 |
sql = "INSERT INTO Colors(UID, RED, GREEN, BLUE, PROPERTY, VALUE) VALUES(?,?,?,?,?,?)" |
|
| 1705 |
param = (_color.index, _color.red, _color.green, _color.blue, _color._property, _color.value) |
|
| 1706 |
cursor.execute(sql, param) |
|
| 1707 |
conn.commit() |
|
| 1832 | 1708 |
# Catch the exception |
| 1833 | 1709 |
except Exception as ex: |
| 1834 | 1710 |
# Roll back any change if something goes wrong |
| 1835 | 1711 |
conn.rollback() |
| 1836 | 1712 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1837 |
finally: |
|
| 1838 |
# Close the db connection |
|
| 1839 |
conn.close() |
|
| 1840 | 1713 |
|
| 1841 | 1714 |
''' |
| 1842 | 1715 |
@brief delete Color property |
| ... | ... | |
| 1845 | 1718 |
''' |
| 1846 | 1719 |
def deletePropertyColor(self, property): |
| 1847 | 1720 |
try: |
| 1848 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1849 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1850 |
conn = sqlite3.connect(dbPath) |
|
| 1851 |
# Get a cursor object |
|
| 1852 |
cursor = conn.cursor() |
|
| 1721 |
conn = self.project.database.connect() |
|
| 1722 |
with conn: |
|
| 1723 |
# Get a cursor object |
|
| 1724 |
cursor = conn.cursor() |
|
| 1853 | 1725 |
|
| 1854 |
sql = "DELETE FROM Colors WHERE PROPERTY = '{}'".format(property)
|
|
| 1855 |
cursor.execute(sql) |
|
| 1856 |
conn.commit() |
|
| 1726 |
sql = "DELETE FROM Colors WHERE PROPERTY = '{}'".format(property)
|
|
| 1727 |
cursor.execute(sql)
|
|
| 1728 |
conn.commit()
|
|
| 1857 | 1729 |
# Catch the exception |
| 1858 | 1730 |
except Exception as ex: |
| 1859 | 1731 |
# Roll back any change if something goes wrong |
| 1860 | 1732 |
conn.rollback() |
| 1861 | 1733 |
print('error occured({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno))
|
| 1862 |
finally: |
|
| 1863 |
# Close the db connection |
|
| 1864 |
conn.close() |
|
| 1865 | 1734 |
|
| 1866 | 1735 |
''' |
| 1867 | 1736 |
@brief get Fluid Code |
| ... | ... | |
| 1874 | 1743 |
result = [] |
| 1875 | 1744 |
|
| 1876 | 1745 |
try: |
| 1877 |
# Creates or opens a file called mydb with a SQLite3 DB |
|
| 1878 |
dbPath = os.path.join(self.getCurrentProject().getDbFilePath(), AppDocData.DATABASE) |
|
| 1879 |
conn = sqlite3.connect(dbPath) |
|
| 1880 |
# Get a cursor object |
|
| 1881 |
cursor = conn.cursor() |
|
| 1746 |
conn = self.project.database.connect() |
|
| 1747 |
with conn: |
|
| 1748 |
# Get a cursor object |
|
| 1749 |
cursor = conn.cursor() |
|
| 1882 | 1750 |
|
| 1883 |
sql = 'select uid, code, description from FluidCode order by length(code) DESC' |
|
| 1884 |
cursor.execute(sql) |
|
| 1885 |
rows = cursor.fetchall() |
|
| 1886 |
for row in rows: |
|
| 1887 |
data = FluidCodeData(row[0], row[1], row[2]) |
|
| 1888 |
if forCheckLineNumber: |
|
| 1889 |
result.append(data.code) |
|
| 1890 |
else: |
|
| 1891 |
result.append(data) |
|
| 1751 |
sql = 'select uid, code, description from FluidCode order by length(code) DESC' |
|
| 1752 |
cursor.execute(sql) |
|
| 1753 |
rows = cursor.fetchall() |
|
| 1754 |
for row in rows: |
|
| 1755 |
data = FluidCodeData(row[0], row[1], row[2]) |
|
| 1756 |
if forCheckLineNumber: |
|
| 1757 |
result.append(data.code) |
|
내보내기 Unified diff
