API tools faq
paste
Advertisement
xiahanlu

funny

xiahanlu
Feb 25th, 2020
861
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 53.20 KB | None | 0 0
raw download clone embed print report
  1. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  2. ' Codec:utf-8
  3. '
  4. ' 简单的SQL 增删查改演示程序, 用于练习python
  5. '
  6. ' 程序很简单, 一个主窗口
  7. '
  8. ' 一个TreeWidget 控件, 用来显示所有建立的链接以及table的信息
  9. ' 一个ListWidget 控件, 用于显示指定的表的field头以及基本数据类型[tinyint, blob], 位域名, 长度以及小数长度\
  10. '                      并提供简单的插入, 删除修改操作
  11. ' 一个ListWidget 控件2, 用来显示当前表的所有Field的所有数据项目, [此部分增删查改可能会有BUG..]
  12. '
  13. ' 以及若干模态对话框以及菜单和 Tracker菜单
  14. ' 没写完, 但基础功能都有了, 后面的功能感觉写不写的区别也不大..
  15. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  16.  
  17. import mysql.connector
  18. import sys
  19. import datetime
  20.  
  21. # 导入Qt 库
  22. from PyQt5.QtWidgets import QApplication, QWidget, QPushButton
  23. from PyQt5.QtGui import QIcon
  24. from PyQt5.QtCore import pyqtSlot
  25.  
  26. # 导入Qt UI文件
  27. from DlgSQLSettings import *
  28. from UiMain import *
  29. from DlgFieldNameAppend import *
  30. from DlgFieldAdjust import *
  31. from DlgDataSettings_Int import *
  32. from DlgDataSettings_Float import *
  33. from DlgDataSettings_Char import *
  34. from DlgDataSettings_Datetime import *
  35. from DlgDataSettings_Time import *
  36. from DlgDataSettings_Date import *
  37. from DlgDataSettings_Year import *
  38.  
  39. # 全局使用的SQL类型
  40. # 为了简单, 只提供一些标准SQL数据类型
  41. listStrSQLDataTyple =\
  42. [
  43.   # FLOAT
  44.   'float',    # 0  附带浮点精度位
  45.   'double',   # 1  附带浮点精度位
  46.   'decimal',  # 2  附带浮点精度位
  47.  
  48.   # CHAR
  49.   'char',     # 3
  50.   'varchar',  # 4
  51.  
  52.   # INT
  53.   'tinyint',   # 5
  54.   'smallint',  # 6
  55.   'mediumint', # 7
  56.   'integer',   # 8
  57.   'int',       # 9
  58.   'bigint',    #10
  59.  
  60.   # DATE
  61.   'datetime',  #11
  62.   'timestamp', #12
  63.   'time',      #13
  64.   'date',      #14 不需要长度计数
  65.   'year',      #15 不需要长度计数
  66.  
  67.   # TEXT || BLOB
  68.   'tinyblob',  #16 自此以下不需要长度计数
  69.   'tinytext',  #17
  70.   'blob',      #18
  71.   'text',      #19
  72.   'mediumblob',#20
  73.   'mediumtext',#21
  74.   'longblob',  #22
  75.   'longtext'   #23
  76. ]
  77.  
  78. listStrSQLDataDefault =\
  79. [
  80.   # FLOAT
  81.   '0.0',    # 0  附带浮点精度位
  82.   '0.0',   # 1  附带浮点精度位
  83.   '0.0',  # 2  附带浮点精度位
  84.  
  85.   # CHAR
  86.   "'String'",     # 3
  87.   "'String'",  # 4
  88.  
  89.   # INT
  90.   '0',   # 5
  91.   '0',  # 6
  92.   '0', # 7
  93.   '0',   # 8
  94.   '0',       # 9
  95.   '0',    #10
  96.  
  97.   # DATE
  98.   "'1970-01-01 00:00:00'",  #11
  99.   "'1970-01-01 00:00:00'", #12
  100.   "'00:00:00'",      #13
  101.   "'1970-01-01'",      #14 不需要长度计数
  102.   "'1970'",      #15 不需要长度计数
  103.  
  104.   # TEXT || BLOB
  105.   'tinyblob',  #16 自此以下不需要长度计数
  106.   'tinytext',  #17
  107.   'blob',      #18
  108.   'text',      #19
  109.   'mediumblob',#20
  110.   'mediumtext',#21
  111.   'longblob',  #22
  112.   'longtext'   #23
  113. ]
  114.  
  115. # 对于SQL的某些命名, 不能直接使用数字, 需要用``这种另类引号把括起来, 否则会报错
  116. def linkSQLVarName(strName):
  117.   return '`' + strName + '`'
  118.  
  119. # 获取指定参数位域拟合的数据类型长度+小数类型的字符串
  120.  # 形如 char(200), float(3, 5)的字符串
  121. def makeTypeRange(intField, strFieldType, main, sub):
  122.   strMain = str(main)
  123.   strSub = str(sub)
  124.   str_ = strFieldType
  125.  
  126.   if main != 0:
  127.     if intField <= 2:
  128.       str_ += '(' + strMain + ',' + strSub + ')'
  129.     elif intField <= 13:
  130.       str_ += '(' + strMain + ')'
  131.   else:
  132.     if intField >=3 \
  133.       or intField <=4:  # [var]char
  134.       str_ += '(0)'
  135.   return str_
  136.  
  137. # 获取指定类型字符串在表中的索引位置
  138. def getDataTypeIndex(typeString):
  139.   for iter, item in enumerate(listStrSQLDataTyple):
  140.     if typeString == item:
  141.       return iter
  142.     else:
  143.       pass
  144.   return -1
  145.  
  146. # 根据数据索引获取类型字符串
  147. def getDataTypeString(typeIndex):
  148.   if typeIndex < len(listStrSQLDataTyple):
  149.     return listStrSQLDataTyple[typeIndex]
  150.   else:
  151.     return None
  152.  
  153. # SQL 位域类
  154. class SQL_field(object):
  155.   def __init__(self):
  156.     self.strName = '' # 字段名
  157.     self.mainLen = 0 # 数据类型整数位进度
  158.     self.subLen = 0 # 数据类型小数位进度
  159.     self.collName = '' # 形如 char(200), float(3, 5)的字符串
  160.     self.dataList = []
  161.     self.intField = 0
  162.     self.strFieldType = ''
  163.     self.bPrimaryKey = False
  164.     self.bAutoINC = False
  165.     self.bNullable = False
  166.     self.strComment = None
  167.    
  168.   def getMainlen(self):
  169.     return self.mainLen
  170.  
  171.   def getSublen(self):
  172.     return self.subLen
  173.  
  174.   def updateNumber(self, collName):
  175.  
  176.     self.mainLen = 0
  177.     self.subLen = 0
  178.     self.intField = -1
  179.    
  180.     for iter, item in enumerate(listStrSQLDataTyple):
  181.       keyLen = len(item)
  182.       collLen = len(collName)
  183.      
  184.       if keyLen <= collLen\
  185.         and item == collName[:keyLen]:
  186.         # 判断数据类型, 获取整数, 小数部分精度
  187.         # INFORMATION_SCHEMA.COLUMNS 获取的关于此部分的信息有可能不准确
  188.         # 需要手动提取信息  /* 附带信息, 零填充和 符号标记 */
  189.         self.intField = iter
  190.        
  191.         if keyLen == collLen:
  192.           pass
  193.         elif iter <= 2:
  194.           splitPos = collName.index(',')
  195.          
  196.           self.mainLen = int(collName[keyLen + 1:splitPos])
  197.           self.subLen = int(collName[splitPos+ 1:collName.index(')')])
  198.         elif iter <= 13:
  199.           self.mainLen = int(collName[keyLen+1:collName.index(')')])
  200.         return None
  201.  
  202.   def updateDataByTuple(self, sqlTuple):
  203.    
  204.     # python 查询 INFORMATION_SCHEMA 返回的字段元组示意组
  205.    
  206.     # [1] 数据库名
  207.     # [2] 表名
  208.     # [3] 字段名
  209.     # [6] 空约束 可以返回"YES", 强制约束返回"NO"
  210.     # [7] 数据类型
  211.     # [15] 数据拟合字段 (e.g. int(255) unsigned)
  212.     # [16] 主键标识 ('PRI')
  213.     # [17] 自增字段标识 ('auto_increment')
  214.     # [19] 注释
  215.    
  216.     self.strName = sqlTuple[3]
  217.     self.strFieldType = sqlTuple[7]
  218.     self.collName = sqlTuple[15]
  219.     self.intField = getDataTypeIndex(self.strFieldType)
  220.     self.bPrimaryKey = False
  221.     self.bAutoINC = False
  222.     self.bNullable = False
  223.    
  224.     if sqlTuple[16] == 'PRI':
  225.       self.bPrimaryKey = True
  226.       if sqlTuple[17] == 'auto_increment':
  227.         self.bAutoINC = True
  228.        
  229.     if sqlTuple[16] == 'YES':
  230.       self.bNullable = True
  231.     if sqlTuple[19] != None:
  232.       self.strComment = sqlTuple[19]
  233.      
  234.     self.updateNumber(self.collName)
  235.    
  236. # SQL 表类, 存放表名和位域集合
  237. class SQL_table(object):
  238.   def __init__(self):
  239.     self.strName = ''
  240.     self.fieldList = []
  241.    
  242. # SQL 数据库类, 存放数据库名和表集合
  243. class SQL_database(object):
  244.   def __init__(self):
  245.     self.strName = ''
  246.     self.tableList = []
  247.  
  248. # SQL 链接简化类
  249. class SQL_handle_helper(object):
  250.   def __init__(self, handle_, str_database=None, str_table=None):
  251.     self.handle = handle_
  252.     self.cursor = handle_.cursor(buffered=True)
  253.     self.bDestroy = False
  254.     self.strDatabase = str_database
  255.     self.strTable = str_table
  256.    
  257.   def __del__(self):
  258.     if self.bDestroy == False:
  259.       self.handle.close()
  260.      
  261.   def close(self):
  262.     self.handle.close()
  263.     self.bDestroy = True
  264.    
  265.   def command(self, command_):
  266.     self.cursor.execute(command_)
  267.  
  268.   def commandNoexcept(self, command_):
  269.    
  270.     try:
  271.       self.cursor.execute(command_)
  272.       return None
  273.     except Exception as e:
  274.       return str(e)
  275.    
  276.   def fetchone(self):
  277.     return self.cursor.fetchone()
  278.    
  279.   def fetchall(self):
  280.     return self.cursor.fetchall()
  281.  
  282.   def commit(self):
  283.     self.handle.commit()
  284.  
  285.   def command_drop_table_field(self, strField):
  286.     self.command("ALTER TABLE {TABLE} DROP COLUMN {FIELD}"\
  287.                  .format(TABLE=linkSQLVarName(self.strTable), FIELD=linkSQLVarName(strField)))
  288.  
  289.   def command_drop_table_prikey(self):
  290.     self.command("ALTER TABLE {TABLE} DROP PRIMARY KEY"\
  291.                  .format(TABLE=linkSQLVarName(self.strTable)))
  292.                  
  293.   def command_get_table_prikey(self):
  294.     self.command( "SELECT "                                                  \
  295.                   "column_name "                                             \
  296.                   "FROM "                                                    \
  297.                   "INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` "                   \
  298.                   "WHERE table_name = '{TABLE}' "                            \
  299.                   "AND constraint_name = 'PRIMARY'"                          \
  300.                 .format(TABLE = self.strTable) )
  301.    
  302.   def command_get_column_infos(self):
  303.     command_ = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS "\
  304.                                                            \
  305.                       "WHERE TABLE_SCHEMA = '{DATABASE}' " \
  306.                       "AND TABLE_NAME = '{TABLE}' "\
  307.                     .format(DATABASE=self.strDatabase, TABLE=self.strTable)
  308.    
  309.     self.command(command_)
  310.    
  311.   def string_alter_table(self):
  312.     return "ALTER TABLE {TABLE} ".format(TABLE=linkSQLVarName(self.strTable))
  313.  
  314.   def string_alter_table_toadd(self, strToAdd):
  315.     return "ALTER TABLE {TABLE} {LINK} ".format(TABLE=linkSQLVarName(self.strTable), LINK=strToAdd)
  316.  
  317.   # 迭代重载
  318.   def __iter__(self):
  319.     return iter(self.cursor.fetchone, None)
  320.  
  321. # SQL 链接类, 提供基础的SQL链接句柄, 和数据库集合
  322. class SQL_connect(object):
  323.   def __init__(self, _strConnectName = '', _strHost = '', _strPort = '', _strUserName = '', _strPassword = ''):
  324.     self.strConnectName = _strConnectName
  325.     self.strHost = _strHost
  326.     self.strPort = _strPort
  327.     self.strUserName = _strUserName
  328.     self.strPassword = _strPassword
  329.     self.databaseList = []
  330.    
  331.   def openSQLConnect(self):
  332.     return mysql.connector.connect(host=self.strHost, port=self.strPort, user=self.strUserName, passwd=self.strPassword)
  333.  
  334.   def openSQLConnect2(self, database_):
  335.     return mysql.connector.connect(host=self.strHost, port=self.strPort, user=self.strUserName, passwd=self.strPassword, database = database_)
  336.  
  337.   def openSQLConnect_helper(self):
  338.     return SQL_handle_helper(self.openSQLConnect())
  339.  
  340.   def openSQLConnect2_helper(self, database, table = None):
  341.     return SQL_handle_helper(self.openSQLConnect2(database), database, table)
  342.  
  343.   def enumALLInfos(self):
  344.    
  345.     # 打开链接句柄,
  346.     # 打开多个, 因为SQL handle 获得的光标貌似不能同时使用多个命令\
  347.     # 不然需要同时数个cursor上下文信息的时候, \
  348.     # execute执行后先前需要的上下文Cursor内容会被覆盖 (不然就是我记错了....?)
  349.     commandHelper = self.openSQLConnect_helper()
  350.     commandHelper2 = self.openSQLConnect_helper()
  351.    
  352.     # 显示当前链接所拥有的所有数据库信息
  353.     commandHelper.command("SHOW DATABASES")
  354.    
  355.     # 迭代, 收集数据库
  356.     # iter是个元组, 第一个元素为数据库名字
  357.     for iter in commandHelper:
  358.       databaseItem = SQL_database()
  359.       databaseItem.strName = iter[0]
  360.  
  361.       # 切换光标到当前的数据库
  362.       commandHelper2.command("USE " + linkSQLVarName(iter[0]))
  363.      
  364.       # information_schema 这个数据库的表类型是SYSTEM VIEW\
  365.       # 需要额外判断一下
  366.       if iter[0] != 'information_schema':
  367.         commandHelper2.command("SHOW FULL TABLES WHERE TABLE_TYPE = 'BASE TABLE'; ")
  368.       else:
  369.         commandHelper2.command("SHOW FULL TABLES WHERE TABLE_TYPE = 'SYSTEM VIEW'; ")
  370.        
  371.       # 迭代, 收集数据库下所有表信息
  372.       # iter2也是个元组, 第一个元素为表的名字
  373.       for iter2 in commandHelper2:
  374.         table = SQL_table()
  375.         table.strName = iter2[0]
  376.         databaseItem.tableList.append(table)
  377.  
  378.       self.databaseList.append(databaseItem)
  379.    
  380. # SQL 索引上下文
  381. class SQL_indexctx:
  382.   def __init__(self, listField_, cmdHelper_, strTable_):
  383.    
  384.     self.listPrevPrimaryKey = []
  385.     self.strIndexField = None
  386.     self.strTable = strTable_
  387.     self.cmdHelper = cmdHelper_
  388.     self.listField = listField_
  389.     self.strCommandDropAtomicINC = None
  390.     self.strCommandResumeAtomicINC = None
  391.    
  392.     # 缓存之前的主键字段集合
  393.     self.cmdHelper.command_get_table_prikey()
  394.    
  395.     # 增加主键缓存
  396.     for iter in self.cmdHelper:
  397.       self.listPrevPrimaryKey.append(iter[0])
  398.  
  399.     # 查看自增字段是否存在
  400.     # 缓存 SQL 扔掉/ 挂上原子自增旗标命令
  401.     for iter in listField_:
  402.       if iter.bAutoINC != False:
  403.         cmdBase = self.cmdHelper.string_alter_table_toadd('MODIFY COLUMN') \
  404.                   + linkSQLVarName(iter.strName) \
  405.                   + " {COLLNAME} NOT NULL".format(COLLNAME=iter.collName)
  406.    
  407.         self.strCommandDropAtomicINC = cmdBase
  408.         self.strCommandResumeAtomicINC = cmdBase + " AUTO_INCREMENT "
  409.    
  410.         if iter.strComment != None:
  411.           cmdComment = " COMMENT '{sComment}'".format(sComment=iter.strComment)
  412.           self.strCommandDropAtomicINC += cmdComment
  413.           self.strCommandResumeAtomicINC += cmdComment
  414.         break
  415.        
  416.   def resumeOldKey(self):
  417.  
  418.     # 恢复之前的主键设置
  419.     # FIXME:这里依赖查询语句的默认排序为键顺序从小到大升序表示
  420.     if len(self.listPrevPrimaryKey) > 0:
  421.       strCombin = self.cmdHelper.string_alter_table_toadd("ADD PRIMARY KEY ") \
  422.                 + "({PRI_KEY_SET})".format(PRI_KEY_SET=",".join('`' + i + '`' for i in self.listPrevPrimaryKey))
  423.       self.cmdHelper.command(strCombin)
  424.     if self.strCommandResumeAtomicINC != None:
  425.       self.cmdHelper.command(self.strCommandResumeAtomicINC)
  426.      
  427.   def createIndex(self):
  428.     if self.strIndexField == None:
  429.       # 创建临时主键索引
  430.       strFieldTryUpdate = 'fefefefe'
  431.       cntTimeoutCount = 400
  432.       cntTrySetCount = 0
  433.      
  434.       # 测试消除之前存在的主键自增旗标
  435.       # 否则新设置尝试会报错
  436.       if self.strCommandDropAtomicINC != None:
  437.         self.cmdHelper.command(self.strCommandDropAtomicINC)
  438.  
  439.       if len(self.listPrevPrimaryKey) > 0:
  440.         self.cmdHelper.command_drop_table_prikey()
  441.      
  442.       while cntTrySetCount < cntTimeoutCount:
  443.         try:
  444.           command_ = self.cmdHelper.string_alter_table() \
  445.                     + "ADD COLUMN {field}  int(255) UNSIGNED NOT NULL AUTO_INCREMENT FIRST , " \
  446.                       "ADD PRIMARY KEY ({field})".format(field=strFieldTryUpdate)
  447.           self.cmdHelper.command(command_)
  448.           self.strIndexField = strFieldTryUpdate
  449.           return self.strIndexField
  450.         except Exception as e:
  451.           cntTrySetCount += 1
  452.           strFieldTryUpdate += '_'
  453.        
  454.     # 失败恢复主键设置, 按理说不应该会失败
  455.     assert False
  456.     self.resumeOldKey()
  457.    
  458.     return None
  459.  
  460.   def closeIndex(self):
  461.     if self.strIndexField != None:
  462.       self.cmdHelper.command_drop_table_field(self.strIndexField)
  463.       self.strIndexField = None
  464.      
  465.       # 恢复之前的主键设置
  466.       self.resumeOldKey()
  467.  
  468. # Qt 模态对话框类- 资源对话框基类
  469. class CDialogUI_T(QtWidgets.QDialog):
  470.   def __init__(self, Ui_ctor, attachFrame_ = None):
  471.     # 执行基础的模态对话框类基类构造函数
  472.     QtWidgets.QDialog.__init__(self)
  473.    
  474.     # 挂载 QtDesigner UI 资源文件
  475.     self.ui = Ui_ctor()
  476.     self.ui.setupUi(self)
  477.     self.setModal(True)
  478.     self.frame0 = attachFrame_
  479.  
  480. # Qt 模态对话框类- 用于设置新链接的基本参数 [链接名, socket host, 端口, 用户名和密码]
  481. #                  点击主窗口菜单栏 创建新链接时被启用
  482. class CDialogSQLSettings(CDialogUI_T):
  483.   def __init__(self, SQL_connect_ref_list, frame_):
  484.     # 执行基础的模态对话框类基类构造函数
  485.     CDialogUI_T.__init__(self, Ui_Dialog, frame_)
  486.    
  487.     self.setWindowIcon(QIcon("settings.ico"))
  488.     self.newItem = None;
  489.     self.isInsertDone_ = False;
  490.     self.connectList = SQL_connect_ref_list
  491.    
  492.     # 设置 Qt 槽函数映射控件消息
  493.     self.ui.btOk.clicked.connect(self.onSettings)
  494.     self.ui.btCancel.clicked.connect(self.close)
  495.    
  496.     # 填充控件默认参数
  497.     self.ui.etConnectName.setPlainText('samples-link')
  498.     self.ui.etHostName.setPlainText('localhost')
  499.     self.ui.etPort.setPlainText('3306')
  500.     self.ui.etUserName.setPlainText('root')
  501.  
  502.   def onSettings(self):
  503.     # 此回调在用户设置完成按下确定按钮时被启用,
  504.     # 用于验证链接的正确性
  505.    
  506.     # 获取 text控件上设置的文本参数
  507.     strConnect = self.ui.etConnectName.toPlainText()
  508.     strHost = self.ui.etHostName.toPlainText()
  509.     strPort = self.ui.etPort.toPlainText()
  510.     strUserName = self.ui.etUserName.toPlainText()
  511.     strPassword = self.ui.etPassword.toPlainText()
  512.    
  513.     connectCurrent = SQL_connect(strConnect, strHost, strPort, strUserName, strPassword)
  514.    
  515.     # 尝试链接数据库
  516.     # 如果失败会提示用户矫正错误
  517.     try:
  518.       handle = mysql.connector.connect(host=strHost, port=strPort, user=strUserName, passwd=strPassword)
  519.     except Exception as e:
  520.       QtWidgets.QMessageBox().critical(None, 'MySQL 新链接设置', str(e), QtWidgets.QMessageBox.Ok)
  521.     else:
  522.       # 到这一步, 新的链接没有问题, 一个可用的新链接创建成功
  523.       # 插入到数据对象表中
  524.       # 关闭模态对话框
  525.       self.isInsertDone_ = True;
  526.       self.connectList.append(connectCurrent)
  527.       self.newItem = connectCurrent
  528.       self.close()
  529.     finally:
  530.       handle.close()
  531.  
  532. # Qt 模态对话框类- 浮点 型数据编辑
  533. class CDialogDataSettings_Float(CDialogUI_T):
  534.   def __init__(self, CMainFrame_, attachFloat_):
  535.     # 执行基础的模态对话框类基类构造函数
  536.     CDialogUI_T.__init__(self, Ui_DlgDataSettings_Float, CMainFrame_)
  537.  
  538.     # 设置 Qt 槽函数映射控件消息/ 填充控件默认参数
  539.     self.ui.btOk.clicked.connect(self.onSettings)
  540.     self.ui.btCancel.clicked.connect(self.close)
  541.    
  542.     self.ui.etSettingsFloat.setPlainText(str(attachFloat_))
  543.  
  544.   def onSettings(self):
  545.     # 获取 text控件上设置的文本参数
  546.     strSettingsFloat = self.ui.etSettingsFloat.toPlainText()
  547.    
  548.     res = self.frame0.updateData(str(strSettingsFloat))
  549.     if res != None:
  550.       QtWidgets.QMessageBox().critical(None, 'CDialogDataSettings_Float', res, QtWidgets.QMessageBox.Ok)
  551.     else:
  552.       self.close()
  553.       self.frame0.updateDataUI(strSettingsFloat)
  554.  
  555. # Qt 模态对话框类- 字符 型数据编辑
  556. class CDialogDataSettings_Char(CDialogUI_T):
  557.   def __init__(self, CMainFrame_, attachChar_):
  558.     # 执行基础的模态对话框类基类构造函数
  559.     CDialogUI_T.__init__(self, Ui_DlgDataSettings_Char, CMainFrame_)
  560.  
  561.     # 设置 Qt 槽函数映射控件消息
  562.     self.ui.btOk.clicked.connect(self.onSettings)
  563.     self.ui.btCancel.clicked.connect(self.close)
  564.    
  565.     # 填充控件默认参数
  566.     self.ui.etSettingsChar.setPlainText(attachChar_)
  567.  
  568.   def onSettings(self):
  569.     # 获取 text控件上设置的文本参数
  570.     strSettingsChar = self.ui.etSettingsChar.toPlainText()
  571.    
  572.     res = self.frame0.updateData("'" + strSettingsChar + "'")
  573.     if res != None:
  574.       QtWidgets.QMessageBox().critical(None, 'CDialogDataSettings_Char', res, QtWidgets.QMessageBox.Ok)
  575.     else:
  576.       self.close()
  577.       self.frame0.updateDataUI(strSettingsChar)
  578.      
  579. # Qt 模态对话框类- INT 型数据编辑
  580. class CDialogDataSettings_Int(CDialogUI_T):
  581.   def __init__(self, CMainFrame_, attachInt_):
  582.     # 执行基础的模态对话框类基类构造函数
  583.     CDialogUI_T.__init__(self, Ui_DlgDataSettings_Int, CMainFrame_)
  584.    
  585.     # 设置 Qt 槽函数映射控件消息 / 填充控件默认参数
  586.     self.ui.btOk.clicked.connect(self.onSettings)
  587.     self.ui.btCancel.clicked.connect(self.close)
  588.    
  589.   def onSettings(self):
  590.     strSettingsInt = self.ui.etSettingsInt.toPlainText()
  591.    
  592.     res = self.frame0.updateData(str(strSettingsInt))
  593.     if res != None:
  594.       QtWidgets.QMessageBox().critical(None, 'CDialogDataSettings_Int', res, QtWidgets.QMessageBox.Ok)
  595.     else:
  596.       self.close()
  597.       self.frame0.updateDataUI(strSettingsInt)
  598.  
  599. # Qt 模态对话框类- 日期 型数据-datetime 编辑
  600. class CDialogDataSettings_Datetime(CDialogUI_T):
  601.   def __init__(self, CMainFrame_, attachDatetime_):
  602.     # 执行基础的模态对话框类基类构造函数
  603.     CDialogUI_T.__init__(self, Ui_DlgDataSettings_Datetime, CMainFrame_)
  604.    
  605.     # 设置 Qt 槽函数映射控件消息
  606.     self.ui.btOk.clicked.connect(self.onSettings)
  607.     self.ui.btCancel.clicked.connect(self.close)
  608.    
  609.     if attachDatetime_ != None:
  610.       self.ui.tmSettings.setTime(QtCore.QTime(attachDatetime_.hour, attachDatetime_.minute, attachDatetime_.second, 0))
  611.       self.ui.caSettings.setSelectedDate(QtCore.QDate(attachDatetime_.year, attachDatetime_.month, attachDatetime_.day))
  612.      
  613.   def onSettings(self):
  614.     # 获取 时间数据
  615.     qTime = self.ui.tmSettings.time()
  616.     qDate = self.ui.caSettings.selectedDate()
  617.    
  618.     dtSettings = "{YEAR}-{MONTH}-{DAY} {HOUR}:{MIN}:{SEC}"\
  619.               .format(YEAR =qDate.year()\
  620.                      ,MONTH=qDate.month()\
  621.                      ,DAY = qDate.day()\
  622.                      ,HOUR=qTime.hour()\
  623.                      ,MIN=qTime.minute()\
  624.                      ,SEC=qTime.second())
  625.    
  626.     res = self.frame0.updateData("'" + dtSettings + "'")
  627.     if res != None:
  628.       QtWidgets.QMessageBox().critical(None, 'CDialogDataSettings_Datetime', res, QtWidgets.QMessageBox.Ok)
  629.     else:
  630.       self.close()
  631.       self.frame0.updateDataUI(dtSettings)
  632.  
  633. # Qt 模态对话框类- 日期 型数据-date 编辑
  634. class CDialogDataSettings_Date(CDialogUI_T):
  635.   def __init__(self, CMainFrame_, attachDatetime_):
  636.     # 执行基础的模态对话框类基类构造函数
  637.     CDialogUI_T.__init__(self, Ui_DlgDataSettings_Date, CMainFrame_)
  638.    
  639.     # 设置 Qt 槽函数映射控件消息
  640.     self.ui.btOk.clicked.connect(self.onSettings)
  641.     self.ui.btCancel.clicked.connect(self.close)
  642.    
  643.     if attachDatetime_ != None:
  644.       self.ui.caSettings.setSelectedDate(QtCore.QDate(attachDatetime_.year, attachDatetime_.month, attachDatetime_.day))
  645.  
  646.   def onSettings(self):
  647.     # 获取 时间数据
  648.     qDate = self.ui.caSettings.selectedDate()
  649.    
  650.     dtSettings = "{YEAR}-{MONTH}-{DAY}" \
  651.       .format(YEAR=qDate.year() \
  652.               , MONTH=qDate.month() \
  653.               , DAY=qDate.day() )
  654.    
  655.     res = self.frame0.updateData("'" + dtSettings + "'")
  656.     if res != None:
  657.       QtWidgets.QMessageBox().critical(None, 'CDialogDataSettings_Date', res, QtWidgets.QMessageBox.Ok)
  658.     else:
  659.       self.close()
  660.       self.frame0.updateDataUI(dtSettings)
  661.  
  662. # Qt 模态对话框类- 日期 型数据-TIME 编辑
  663. class CDialogDataSettings_Time(CDialogUI_T):
  664.   def __init__(self, CMainFrame_, attachDatetime_):
  665.     # 执行基础的模态对话框类基类构造函数
  666.     CDialogUI_T.__init__(self, Ui_DlgDataSettings_Time, CMainFrame_)
  667.    
  668.     # 设置 Qt 槽函数映射控件消息
  669.     self.ui.btOk.clicked.connect(self.onSettings)
  670.     self.ui.btCancel.clicked.connect(self.close)
  671.    
  672.     if attachDatetime_ != None:
  673.       hour = attachDatetime_.seconds/3600
  674.       min = attachDatetime_.seconds%3600/60
  675.       sec = attachDatetime_.seconds%60
  676.      
  677.       self.ui.tmSettings.setTime(QtCore.QTime(hour, min, sec, 0))
  678.  
  679.   def onSettings(self):
  680.     # 获取 时间数据
  681.     qTime = self.ui.tmSettings.time()
  682.    
  683.     dtSettings = "{HOUR}:{MIN}:{SEC}" \
  684.       .format(HOUR=qTime.hour() \
  685.               , MIN=qTime.minute() \
  686.               , SEC=qTime.second())
  687.    
  688.     res = self.frame0.updateData("'" + dtSettings + "'")
  689.     if res != None:
  690.       QtWidgets.QMessageBox().critical(None, 'CDialogDataSettings_Datetime', res, QtWidgets.QMessageBox.Ok)
  691.     else:
  692.       self.close()
  693.       self.frame0.updateDataUI(dtSettings)
  694.  
  695. # Qt 模态对话框类- 日期 型数据-YEAR 编辑
  696. class CDialogDataSettings_Year(CDialogUI_T):
  697.   def __init__(self, CMainFrame_, attachInt_):
  698.     # 执行基础的模态对话框类基类构造函数
  699.     CDialogUI_T.__init__(self, Ui_DlgDataSettings_Year, CMainFrame_)
  700.    
  701.     # 设置 Qt 槽函数映射控件消息
  702.     self.ui.btOk.clicked.connect(self.onSettings)
  703.     self.ui.btCancel.clicked.connect(self.close)
  704.    
  705.     # 填充控件默认参数
  706.     self.ui.etSettingsInt.setPlainText(str(attachInt_))
  707.  
  708.   def onSettings(self):
  709.     # 此回调在用户设置完成按下确定按钮时被启用,
  710.     # 用于验证设置整数的正确性
  711.    
  712.     # 获取 text控件上设置的文本参数
  713.     strSettingsInt = self.ui.etSettingsInt.toPlainText()
  714.    
  715.     res = self.frame0.updateData(str(strSettingsInt))
  716.     if res != None:
  717.       QtWidgets.QMessageBox().critical(None, 'CDialogDataSettings_Int', res, QtWidgets.QMessageBox.Ok)
  718.     else:
  719.       self.close()
  720.       self.frame0.updateDataUI(strSettingsInt)
  721.      
  722. ''' Qt 模态对话框类- 用于在表的指定位置增加新的SQL Field
  723.       CMainFrame_:主窗口引用指针
  724.       pos_ : 当前选中的位置
  725.       strFieldNameAfter_: 在当前位置之前的Field名字, 如果 pos_ 为0. 此值则为nullptr
  726. '''
  727. class CDialogAppendField(CDialogUI_T):
  728.   def __init__(self, CMainFrame_, pos_, strFieldNameAfter_):
  729.    
  730.     assert CMainFrame_ != None
  731.     assert CMainFrame_.pickAvailable() != False
  732.    
  733.     # 执行基础的模态对话框类基类构造函数
  734.     CDialogUI_T.__init__(self, Ui_dlgFieldNameAppend, CMainFrame_)
  735.    
  736.     self.strFieldNameAfter = strFieldNameAfter_
  737.     self.insertPos = pos_
  738.    
  739.     self.ui.btOk.clicked.connect(self.onSettings)
  740.  
  741.     self.ui.etFieldName.setPlainText('samples')
  742.  
  743.     # 准备comboBox 下拉信息
  744.     for iter, item in enumerate(listStrSQLDataTyple):
  745.       self.ui.cbField.insertItem(iter, item)
  746.     self.ui.cbField.setCurrentIndex(4)
  747.    
  748.   def onSettings(self):
  749.  
  750.     # 用户按下设置完成按钮,
  751.     # 进行新位域的检查, 测试, 成功则添加此Field
  752.     assert self.frame0.pickAvailable() != False
  753.    
  754.     frame = self.frame0
  755.     index = self.ui.cbField.currentIndex()
  756.    
  757.     strFieldName = self.ui.etFieldName.toPlainText()
  758.     commandHelper = frame.openSQLConnect2_helper()
  759.    
  760.     strType = getDataTypeString(index)
  761.     strColl = makeTypeRange(index, strType, 2, 0)
  762.    
  763.     command_ = commandHelper.string_alter_table_toadd("ADD COLUMN")\
  764.             + " {FIELD} {COLLNAME}".format(FIELD=linkSQLVarName(strFieldName), COLLNAME=strColl)
  765.    
  766.     if self.strFieldNameAfter == None:
  767.       command_ += ' FIRST' # 第一个元素
  768.       self.insertPos = 0
  769.     else:
  770.       command_ += ' AFTER ' + linkSQLVarName(self.strFieldNameAfter)
  771.      
  772.     try:
  773.       commandHelper.command(command_)
  774.      
  775.       # FIXME: 位域设置有遗漏项
  776.       field = SQL_field()
  777.       field.strName = strFieldName
  778.       field.strFieldType = strType
  779.       field.collName = strColl
  780.       field.mainLen = 2
  781.       field.intField = index
  782.  
  783.       # 更新 Qt的 UI视图
  784.       frame.ui.tbField.insertRow(self.insertPos)
  785.       frame.ui.tbData.insertColumn(self.insertPos)
  786.       frame.fieldList.insert(self.insertPos, field)
  787.       frame.renameTbDataColName(self.insertPos, field.strName)
  788.      
  789.       frame.ui.tbField.setItem(self.insertPos, 0, QtWidgets.QTableWidgetItem(strFieldName))
  790.       frame.ui.tbField.setItem(self.insertPos, 1, QtWidgets.QTableWidgetItem(strType))
  791.       frame.ui.tbField.setItem(self.insertPos, 2, QtWidgets.QTableWidgetItem('2'))
  792.       frame.ui.tbField.setItem(self.insertPos, 3, QtWidgets.QTableWidgetItem('0'))
  793.      
  794.       self.close()
  795.     except Exception as e:
  796.       QtWidgets.QMessageBox().critical(None, 'MySQL Connect', str(e), QtWidgets.QMessageBox.Ok)
  797.     finally:
  798.       commandHelper.close()
  799.  
  800. # Qt 模态对话框类- 用于在表的指定位置修改指定SQL Field类型
  801. # CMainFrame_:主窗口引用指针
  802. # pos_ : 当前选中的位置
  803. class CDialogAdjustField(CDialogUI_T):
  804.   def __init__(self, CMainFrame_, pos_):
  805.    
  806.     assert CMainFrame_ != None
  807.     assert CMainFrame_.pickAvailable() != False
  808.    
  809.     # 执行基础的模态对话框类基类构造函数
  810.     CDialogUI_T.__init__(self, Ui_DlgFieldTypeAdjust, CMainFrame_)
  811.  
  812.     self.updatePos = pos_
  813.    
  814.     # 准备comboBox 下拉信息
  815.     for iter, item in enumerate(listStrSQLDataTyple):
  816.       self.ui.cbField.insertItem(iter, item)
  817.    
  818.     # 设置当前状态索引
  819.     field = CMainFrame_.getField(pos_)
  820.    
  821.     if field.intField >= 0:
  822.       self.ui.cbField.setCurrentIndex(field.intField)
  823.     else:
  824.       self.ui.cbField.insertItem(999999, "UnknowData") # XXX: 999999
  825.       self.ui.cbField.setCurrentIndex(len(listStrSQLDataTyple))
  826.      
  827.     self.ui.btOk.clicked.connect(self.onSettings)
  828.    
  829.   def onSettings(self):
  830.    
  831.     assert self.frame0.pickAvailable() != False
  832.    
  833.     # 用户按下设置完成按钮,
  834.     # 进行新位域的修改, 测试, 成功则更新此位域
  835.     intField = self.ui.cbField.currentIndex()
  836.     pos = self.updatePos
  837.    
  838.     frame = self.frame0
  839.    
  840.     if intField >= len(listStrSQLDataTyple):
  841.       QtWidgets.QMessageBox().critical(None, 'MySQL Field位域矫正', '你设置了未知的数据格式!', QtWidgets.QMessageBox.Ok)
  842.     else:
  843.       try:
  844.         field = frame.getField(pos)
  845.        
  846.         commandHelper = frame.openSQLConnect2_helper()
  847.         commandHelper.command(commandHelper.string_alter_table_toadd("MODIFY COLUMN")\
  848.                           + "{FIELD} {COLLNAME}".format(FIELD=linkSQLVarName(field.strName)\
  849.                                                       ,COLLNAME=makeTypeRange(intField, getDataTypeString(intField), field.getMainlen(), field.getSublen())\
  850.                                                       )\
  851.                               )
  852.         # 更新列数据
  853.         commandHelper.command_get_column_infos()
  854.        
  855.         field.updateDataByTuple(commandHelper.fetchall()[pos])
  856.  
  857.         frame.ui.tbField.item(pos, 2).setText(str(field.getMainlen()))
  858.         frame.ui.tbField.item(pos, 3).setText(str(field.getSublen()))
  859.         frame.ui.tbField.item(pos, 1).setText(field.strFieldType)
  860.        
  861.         self.close()
  862.       except Exception as e:
  863.         QtWidgets.QMessageBox().critical(None, 'MySQL Connect', str(e), QtWidgets.QMessageBox.Ok)
  864.       finally:
  865.         commandHelper.close()
  866.        
  867. # Qt主窗口
  868. class CMainFrame(QtWidgets.QMainWindow):
  869.   def __init__(self):
  870.     # 执行基础的主窗口帧类基类构造函数
  871.     QtWidgets.QMainWindow.__init__(self)
  872.  
  873.     self.onFieldRemove = False
  874.     self.catchDataRow = -1
  875.     self.catchDataCol = -1
  876.    
  877.     self.ui = Ui_MainWindow()
  878.     self.ui.setupUi(self)
  879.     self.ui.cmdNewMySQLConnect.triggered.connect(self.onSQLSettings)
  880.     self.ui.actionQt.triggered.connect(lambda state:QtWidgets.QMessageBox().aboutQt(None, "About QT"))
  881.  
  882.     self.setWindowIcon(QIcon("main.ico"))
  883.    
  884.     # 关联弹出上下文菜单 | 双击消息
  885.     self.ui.tbField.itemDoubleClicked.connect(self.onFieldTypeAdjust)
  886.     self.ui.tbField.currentItemChanged.connect(self.onFieldChanged)
  887.     self.ui.tbField.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
  888.     self.ui.tbField.customContextMenuRequested.connect(self.onFieldCtxMenu)
  889.  
  890.     self.ui.tbData.itemDoubleClicked.connect(self.onDataAdjust)
  891.     self.ui.tbData.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
  892.     self.ui.tbData.customContextMenuRequested.connect(self.onDataCtxMenu)
  893.  
  894.     self.ui.trConnect.itemDoubleClicked.connect(self.onSelectTreeItem)
  895.     self.ui.trConnect.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
  896.     self.ui.trConnect.customContextMenuRequested.connect(self.onOverviewCtxMenu)
  897.  
  898.     # 概览Tree控件的弹出菜单 | 槽回调
  899.     self.tbOverviewMenu = QtWidgets.QMenu(self.ui.trConnect)
  900.     self.actTableAppend = QtWidgets.QAction("添加新表", self.ui.trConnect)
  901.     self.actTableEdit = QtWidgets.QAction("编辑当前表", self.ui.trConnect)
  902.     self.actTableRemove = QtWidgets.QAction("删除当前表", self.ui.trConnect)
  903.    
  904.     '''
  905.    self.actTableAppend.triggered.connect(self.onAppendTable)
  906.    self.actTableEdit.triggered.connect(self.onEditTable)
  907.    self.actTableRemove.triggered.connect(self.onRemoveTable)
  908.  
  909.    self.tbOverviewMenu.addAction(self.actTableAppend)
  910.    self.tbOverviewMenu.addAction(self.actTableEdit)
  911.    self.tbOverviewMenu.addAction(self.actTableRemove)
  912.    
  913.    self.tbDatabaseMenu = QtWidgets.QMenu(self.ui.tbField)
  914.    self.actDatabaseAppend = QtWidgets.QAction("添加新数据库", self.ui.trConnect)
  915.    self.actDatabaseEdit = QtWidgets.QAction("编辑当前数据库", self.ui.trConnect)
  916.    self.actDatabaseRemove = QtWidgets.QAction("删除当前数据库", self.ui.trConnect)
  917.  
  918.    self.actDatabaseAppend.triggered.connect(self.onAppendDatabase)
  919.    self.actDatabaseEdit.triggered.connect(self.onEditDatabase)
  920.    self.actDatabaseRemove.triggered.connect(self.onRemoveDatabase)
  921.  
  922.    self.tbDatabaseMenu.addAction(self.actDatabaseAppend)
  923.    self.tbDatabaseMenu.addAction(self.actDatabaseEdit)
  924.    self.tbDatabaseMenu.addAction(self.actDatabaseRemove)
  925.  
  926.    self.tbLinkMenu = QtWidgets.QMenu(self.ui.tbField)
  927.    self.actLinkAppend = QtWidgets.QAction("添加新链接", self.ui.trConnect)
  928.    self.actLinkEdit = QtWidgets.QAction("编辑当前链接", self.ui.trConnect)
  929.    self.actLinkRemove = QtWidgets.QAction("删除当前链接", self.ui.trConnect)
  930.  
  931.    self.actLinkAppend.triggered.connect(self.onAppendLink)
  932.    self.actLinkEdit.triggered.connect(self.onEditLink)
  933.    self.actLinkRemove.triggered.connect(self.onRemoveLink)
  934.  
  935.    self.tbLinkMenu.addAction(self.actLinkAppend)
  936.    self.tbLinkMenu.addAction(self.actLinkEdit)
  937.    self.tbLinkMenu.addAction(self.actLinkRemove)
  938.    '''
  939.    
  940.     # 字段List控件的弹出菜单 | 槽回调  TODO:设置List控件的多选, 单选一次选中一行等功能
  941.     self.tbFieldMenu = QtWidgets.QMenu(self.ui.tbField)
  942.     self.actFieldInsertBefore = QtWidgets.QAction("新字段插入", self.ui.tbField)
  943.     self.actFieldInsertAfter = QtWidgets.QAction("新字段添至尾部", self.ui.tbField)
  944.     self.actFieldRemoveCurrent = QtWidgets.QAction("移除当前选择字段", self.ui.tbField)
  945.  
  946.     self.actFieldInsertBefore.triggered.connect(self.onInsertNewFieldBefore)
  947.     self.actFieldInsertAfter.triggered.connect(self.onAppendField)
  948.     self.actFieldRemoveCurrent.triggered.connect(self.onRemoveField)
  949.  
  950.     self.tbFieldMenu.addAction(self.actFieldInsertBefore)
  951.     self.tbFieldMenu.addAction(self.actFieldInsertAfter)
  952.     self.tbFieldMenu.addAction(self.actFieldRemoveCurrent)
  953.  
  954.     # 数据List控件的弹出菜单 | 槽回调
  955.     self.tbDataMenu = QtWidgets.QMenu(self.ui.tbData)
  956.     self.actDataInsert = QtWidgets.QAction("新数据插入", self.ui.tbData)
  957.     self.actDataRemoveCurrent = QtWidgets.QAction("移除当前选择数据", self.ui.tbData)
  958.  
  959.     self.actDataInsert.triggered.connect(self.onInsertData)
  960.     self.actDataRemoveCurrent.triggered.connect(self.onRemoveData)
  961.  
  962.     self.tbDataMenu.addAction(self.actDataInsert)
  963.     self.tbDataMenu.addAction(self.actDataRemoveCurrent)
  964.  
  965.     self.fieldList = []
  966.     self.connectList = []
  967.    
  968.     # Qt5 GUI Pick Connect/Database/Table var
  969.     self.pickConnect = None
  970.     self.pickDatabase= None
  971.     self.pickTable = None
  972.  
  973.     # Ctor listview column header (SQL field header)
  974.     self.ui.tbField.setColumnCount(4)
  975.     self.ui.tbField.setHorizontalHeaderLabels(['字段名', '数据类型', '长度', '小数'])
  976.  
  977.     # 使用 QSplitter 切割分割窗口
  978.     qSplitterBlock = QtWidgets.QSplitter(QtCore.Qt.Horizontal)
  979.     qSplitterBlock.setOpaqueResize(True)
  980.    
  981.     qSplitterSub = QtWidgets.QSplitter(QtCore.Qt.Vertical, qSplitterBlock)
  982.  
  983.     self.ui.trConnect.setParent(qSplitterSub)
  984.     self.ui.tbField.setParent(qSplitterSub)
  985.     self.ui.tbData.setParent(qSplitterBlock)
  986.    
  987.     qSplitterSub.addWidget(self.ui.trConnect)
  988.     qSplitterSub.addWidget(self.ui.tbField)
  989.    
  990.     qSplitterBlock.addWidget(qSplitterSub)
  991.     qSplitterBlock.addWidget(self.ui.tbData)
  992.    
  993.     qSplitterBlock.setStretchFactor(0, 8)
  994.     qSplitterBlock.setStretchFactor(1, 17)
  995.     qSplitterSub.setStretchFactor(0, 1)
  996.     qSplitterSub.setStretchFactor(1, 1)
  997.    
  998.     self.ui.horizontalLayout.addWidget(qSplitterBlock)
  999.     self.showMaximized()
  1000.  
  1001.     self.show()
  1002.    
  1003.   def openSQLConnect_helper(self):
  1004.     assert self.pickAvailable() != False
  1005.     return self.pickConnect.openSQLConnect2_helper()
  1006.  
  1007.   def openSQLConnect2_helper(self):
  1008.     assert self.pickAvailable() != False
  1009.     return self.pickConnect.openSQLConnect2_helper(self.pickDatabase.strName, self.pickTable.strName)
  1010.    
  1011.   # :需要外部捕获异常
  1012.   def updateData(self, strPackage):
  1013.     assert self.pickAvailable() != False
  1014.     assert self.catchDataCol >= 0
  1015.     assert self.catchDataRow >= 0
  1016.    
  1017.     commandHelper = self.openSQLConnect2_helper()
  1018.     indexCtx = SQL_indexctx(self.fieldList, commandHelper, self.pickTable.strName)
  1019.  
  1020.     try:
  1021.    
  1022.       strTable = linkSQLVarName(self.pickTable.strName)
  1023.       strFieldIndexKey = indexCtx.createIndex()  # TODO Check
  1024.       strField = self.fieldList[self.catchDataCol].strName
  1025.      
  1026.       command2 = "UPDATE {TABLE} SET {FIELD}={VALUE} WHERE {KEY} IN (SELECT  {KEY} FROM (SELECT {KEY} FROM {TABLE} LIMIT {ROW_},1) AS TEMP)" \
  1027.         .format(TABLE=strTable, FIELD=linkSQLVarName(strField), KEY=strFieldIndexKey, VALUE=strPackage, ROW_=self.catchDataRow)
  1028.       commandHelper.command(command2)
  1029.       return None
  1030.     except Exception as e:
  1031.       return str(e)
  1032.     finally:
  1033.       indexCtx.closeIndex()
  1034.       commandHelper.close()
  1035.  
  1036.   def updateDataUI(self, strPackage):
  1037.     assert self.pickAvailable() != False
  1038.     assert self.catchDataCol >= 0
  1039.     assert self.catchDataRow >= 0
  1040.  
  1041.     self.ui.tbData.item(self.catchDataRow, self.catchDataCol).setText(strPackage)
  1042.    
  1043.   def getData(self, col, row):
  1044.  
  1045.     assert self.pickAvailable() != False
  1046.    
  1047.     commandHelper = self.openSQLConnect2_helper()
  1048.    
  1049.     commandHelper.command("SELECT {FIELD} FROM {TABLE} LIMIT {ROW}, 1"\
  1050.                        .format(FIELD=linkSQLVarName(self.fieldList[col].strName) \
  1051.                               ,TABLE=linkSQLVarName(self.pickTable.strName)\
  1052.                               ,ROW =str(row) \
  1053.                               )\
  1054.                          )
  1055.     return commandHelper.fetchone()[0]
  1056.    
  1057.   # 函数解耦-测试当前是否点击选中对象
  1058.   def pickAvailable(self):
  1059.     return  self.pickTable != None\
  1060.         and self.pickDatabase != None\
  1061.         and self.pickConnect != None
  1062.        
  1063.   # 函数解耦-获取当前位域指定行field
  1064.   def getField(self, rowSpec):
  1065.     row = self.ui.tbField.rowCount()
  1066.  
  1067.     if rowSpec < row  \
  1068.       and self.pickAvailable() != False:
  1069.       return self.fieldList[rowSpec]
  1070.     else:
  1071.       return None
  1072.  
  1073.   # 函数解耦-设置位域杂项信息
  1074.   def setListFieldMisc(self):
  1075.     # 设置位域List控件列1不可编辑
  1076.     row = self.ui.tbField.rowCount()
  1077.    
  1078.     if row >= 0 \
  1079.       and self.pickAvailable() != False:
  1080.      
  1081.       for iter in range(row):
  1082.         item0 = self.ui.tbField.item(iter, 1)
  1083.         item0.setFlags(item0.flags() & (~QtCore.Qt.ItemIsEditable))
  1084.  
  1085.   def renameTbDataColName(self, col, strName):
  1086.     item = self.ui.tbData.setHorizontalHeaderItem(col, QtWidgets.QTableWidgetItem(strName))
  1087.    
  1088.   # Qt 位域List控件右键菜单-增加Field到指定位置之前
  1089.   def onInsertNewFieldBefore(self):
  1090.     row = self.ui.tbField.currentRow()
  1091.     listLen = len(self.fieldList)
  1092.  
  1093.     if row >= 0 \
  1094.       and listLen > 0\
  1095.       and row < listLen:
  1096.  
  1097.       CDialogAppendField(self, row, self.fieldList[row].strName).exec()
  1098.  
  1099.   # Qt 位域List控件右键菜单-增加Field到末尾
  1100.   def onAppendField(self):
  1101.    
  1102.     listLen = len(self.fieldList)
  1103.     if listLen > 0:
  1104.       CDialogAppendField(self, listLen, self.fieldList[listLen-1].strName).exec()
  1105.     else:
  1106.       CDialogAppendField(self, 0, None).exec()
  1107.  
  1108.   # Qt 位域List控件右键菜单-移除指定位域
  1109.   def onRemoveField(self):
  1110.     row = self.ui.tbField.currentRow()
  1111.    
  1112.     if row >= 0:
  1113.       field = self.fieldList[row]
  1114.  
  1115.       commandHelper = self.openSQLConnect2_helper()
  1116.  
  1117.       try:
  1118.         # 删除指定表字段及其相关的数据结构
  1119.         commandHelper.command('ALTER TABLE ' + linkSQLVarName(self.pickTable.strName)\
  1120.                   + ' DROP COLUMN ' + linkSQLVarName(field.strName))
  1121.  
  1122.         self.onFieldRemove = True
  1123.  
  1124.         self.fieldList.remove(field)
  1125.        
  1126.         # 更新Qt视图, 删除会触发额外的changed消息, 使用onFieldRemove屏蔽他
  1127.         self.ui.tbData.removeColumn(row)
  1128.         self.ui.tbField.removeRow(row)
  1129.        
  1130.         self.onFieldRemove = False
  1131.       except Exception as e:
  1132.         QtWidgets.QMessageBox().critical(None, 'MySQL Connect', str(e), QtWidgets.QMessageBox.Ok)
  1133.       finally:
  1134.         commandHelper.close()
  1135.  
  1136.   # Qt 数据List控件右键菜单-移除指定数据
  1137.   def onRemoveData(self):
  1138.     row = self.ui.tbData.currentRow()
  1139.  
  1140.     if row >= 0:
  1141.      
  1142.       try:
  1143.         # 删除指定表字段及其相关的数据结构
  1144.         # 根据行号删除指定数据
  1145.         # 创建一个临时的自增主键用于寻址指定的行数
  1146.         commandHelper = self.openSQLConnect2_helper()
  1147.        
  1148.         indexCtx = SQL_indexctx(self.fieldList, commandHelper, self.pickTable.strName)
  1149.        
  1150.         strTable = linkSQLVarName(self.pickTable.strName)
  1151.         strField = indexCtx.createIndex() # TODO Check
  1152.        
  1153.         command2 = "delete from {table} where {id} in (select  {id} from (select {id} from {table} limit {row_},1) as temp)"\
  1154.                      .format(table=strTable, id=strField, row_=row)
  1155.        
  1156.         commandHelper.command(command2)
  1157.         commandHelper.commit()
  1158.      
  1159.         indexCtx.closeIndex()
  1160.        
  1161.         self.ui.tbData.removeRow(row)
  1162.       except Exception as e:
  1163.         QtWidgets.QMessageBox().critical(None, 'MySQL Connect', str(e), QtWidgets.QMessageBox.Ok)
  1164.       finally:
  1165.         commandHelper.close()
  1166.  
  1167.   # Qt 数据List控件右键菜单-插入数据
  1168.   def onInsertData(self):
  1169.     try:
  1170.       assert self.pickAvailable() != False
  1171.  
  1172.       commandHelper = self.openSQLConnect2_helper()
  1173.  
  1174.       strField = None
  1175.       strValue = None
  1176.       strCommand= "INSERT INTO `{TABLE}` () VALUES ()".format(TABLE=self.pickTable.strName)
  1177.      
  1178.       # 只关心那些非空约束字段的值设置
  1179.       for iter in self.fieldList:
  1180.         if iter.bNullable == False:
  1181.           strVal_t = '0'
  1182.  
  1183.           if iter.intField < len(listStrSQLDataDefault):
  1184.             strVal_t = listStrSQLDataDefault[iter.intField]
  1185.            
  1186.           if strField != None:
  1187.             strField = strField + ',' + linkSQLVarName(iter.strName)
  1188.             strValue = strValue + ',' + strVal_t
  1189.           else:
  1190.             strField = linkSQLVarName(iter.strName)
  1191.             strValue = strVal_t
  1192.          
  1193.       if strValue != None:
  1194.         strCommand = "INSERT INTO `{TABLE}` ({FIELDS}) VALUES ({VALS})"\
  1195.              .format(TABLE =self.pickTable.strName\
  1196.                     ,FIELDS=strField \
  1197.                     ,VALS = strValue)
  1198.        
  1199.       commandHelper.command(strCommand)
  1200.       commandHelper.commit()
  1201.  
  1202.       self.ui.tbData.setRowCount(self.ui.tbData.rowCount() + 1)
  1203.      
  1204.       for iter2, item in enumerate(self.fieldList):
  1205.         uiItem = QtWidgets.QTableWidgetItem("")
  1206.         uiItem.setFlags(uiItem.flags() & (~QtCore.Qt.ItemIsEditable))
  1207.  
  1208.         self.ui.tbData.setItem(self.ui.tbData.rowCount() - 1, iter2, uiItem)
  1209.  
  1210.     except Exception as e:
  1211.       QtWidgets.QMessageBox().critical(None, 'MySQL Connect', str(e), QtWidgets.QMessageBox.Ok)
  1212.     finally:
  1213.       commandHelper.close()
  1214.        
  1215.   def onFieldCtxMenu(self, point):
  1216.     if self.pickAvailable() != False:
  1217.       self.tbFieldMenu.move(self.cursor().pos())
  1218.       self.tbFieldMenu.show()
  1219.  
  1220.   def onDataCtxMenu(self, point):
  1221.     if self.pickAvailable() != False:
  1222.       self.tbDataMenu.move(self.cursor().pos())
  1223.       self.tbDataMenu.show()
  1224.  
  1225.   def onOverviewCtxMenu(self, point):
  1226.     if self.pickAvailable() != False:
  1227.       self.tbDataMenu.move(self.cursor().pos())
  1228.       self.tbDataMenu.show()
  1229.  
  1230.   '''
  1231.  字段List控件双击消息, 只处理数据类型列(列索引为1)
  1232.  '''
  1233.   def onFieldTypeAdjust(self, qTableItem):
  1234.     assert qTableItem != None
  1235.    
  1236.     if self.ui.tbField.column(qTableItem) == 1:
  1237.       dlg = CDialogAdjustField(self, self.ui.tbField.row(qTableItem))
  1238.       dlg.exec()
  1239.  
  1240.   def onDataAdjust(self, qTableItem):
  1241.     assert qTableItem != None
  1242.    
  1243.     self.catchDataRow = -1
  1244.     self.catchDataCol = -1
  1245.    
  1246.     # 获取点击的行列, 列指示解析类型, 行指示修改行数
  1247.     col = self.ui.tbData.column(qTableItem)
  1248.     row = self.ui.tbData.row(qTableItem)
  1249.    
  1250.     if col >= 0    \
  1251.       and row >= 0 \
  1252.       and self.pickAvailable() != False:
  1253.      
  1254.       self.catchDataRow = row
  1255.       self.catchDataCol = col
  1256.    
  1257.       field = self.fieldList[col]
  1258.       recv = self.getData(col, row)
  1259.      
  1260.       if field.intField >= 0 \
  1261.         and field.intField <= 2:
  1262.         CDialogDataSettings_Float(self, recv).exec()
  1263.       elif field.intField >= 3 \
  1264.         and field.intField <= 4:
  1265.         dlg = CDialogDataSettings_Char(self, recv).exec()
  1266.       elif field.intField >= 5 \
  1267.         and field.intField <= 10:
  1268.         dlg = CDialogDataSettings_Int(self, recv).exec()
  1269.       elif field.intField == 11:
  1270.         CDialogDataSettings_Datetime(self, recv).exec()
  1271.       elif field.intField == 12:
  1272.         QtWidgets.QMessageBox().critical(None, 'onDataAdjust', "timestamp暂时不支持编辑", QtWidgets.QMessageBox.Ok)
  1273.       elif field.intField == 13:
  1274.         CDialogDataSettings_Time(self, recv).exec()
  1275.       elif field.intField == 14:
  1276.         CDialogDataSettings_Date(self, recv).exec()
  1277.       elif field.intField == 15:
  1278.         CDialogDataSettings_Year(self, recv).exec()
  1279.       else:
  1280.         QtWidgets.QMessageBox().critical(None, 'onDataAdjust', "text和blob/其他数据暂时不支持编辑", QtWidgets.QMessageBox.Ok)
  1281.      
  1282.     if self.ui.tbField.column(qTableItem) == 1:
  1283.       dlg = CDialogAdjustField(self, self.ui.tbField.row(qTableItem))
  1284.       dlg.exec()
  1285.      
  1286.   def onFieldChanged(self, current, previous):
  1287.     if previous != None\
  1288.       and self.onFieldRemove == False:
  1289.       col = self.ui.tbField.column(previous)
  1290.       row = self.ui.tbField.row(previous)
  1291.      
  1292.       if col >= 0 \
  1293.         and row >= 0\
  1294.         and col != 1\
  1295.         and row < len(self.fieldList)\
  1296.                 and self.pickAvailable() != False:
  1297.  
  1298.         field = self.fieldList[row]
  1299.        
  1300.         qItemfieldName = self.ui.tbField.item(row, 0)
  1301.        
  1302.         qItemfieldMain = self.ui.tbField.item(row, 2)
  1303.         qItemfieldSub = self.ui.tbField.item(row, 3)
  1304.        
  1305.         commandHelper = self.openSQLConnect2_helper()
  1306.        
  1307.         try:
  1308.           if col == 0:
  1309.             # Modify field-name
  1310.             commandHelper.command('ALTER TABLE ' + linkSQLVarName(self.pickTable.strName)\
  1311.                             +' CHANGE COLUMN ' + linkSQLVarName(field.strName)\
  1312.                             +' ' + linkSQLVarName (previous.text())\
  1313.                             +' ' + field.collName)
  1314.             field.strName = previous.text()
  1315.  
  1316.             self.renameTbDataColName(row, field.strName)
  1317.           else:
  1318.             adjustValueMain = field.getMainlen()
  1319.             adjustValueSub = field.getSublen()
  1320.             setCurrent = int(previous.text())
  1321.            
  1322.             if col == 2:
  1323.               adjustValueMain = setCurrent
  1324.             else:
  1325.               adjustValueSub = setCurrent
  1326.              
  1327.             command_ = 'ALTER TABLE ' + linkSQLVarName(self.pickTable.strName)\
  1328.                             +' MODIFY COLUMN ' + linkSQLVarName(field.strName)\
  1329.                             +' ' + makeTypeRange(field.intField, field.strFieldType,\
  1330.                                                                            adjustValueMain,\
  1331.                                                                            adjustValueSub)
  1332.             commandHelper.command(command_)
  1333.             commandHelper.command_get_column_infos()
  1334.            
  1335.             field.updateDataByTuple(commandHelper.fetchall()[row])
  1336.  
  1337.             qItemfieldName.setText(field.strName)
  1338.             qItemfieldMain.setText(str(field.getMainlen()))
  1339.             qItemfieldSub.setText(str(field.getSublen()))
  1340.            
  1341.         except Exception as e:
  1342.           QtWidgets.QMessageBox().critical(None, 'MySQL Connect', str(e), QtWidgets.QMessageBox.Ok)
  1343.      
  1344.           # Reset old infos
  1345.           qItemfieldName.setText(field.strName)
  1346.           qItemfieldMain.setText(str(field.getMainlen()))
  1347.           qItemfieldSub.setText(str(field.getSublen()))
  1348.  
  1349.           commandHelper.close()
  1350.          
  1351.   def onSelectTreeItem(self, treeItem, dummyUsed):
  1352.    
  1353.     if treeItem.UserType & 0x80000000:
  1354.       try:
  1355.           # Enum field list
  1356.           self.fieldList = []
  1357.    
  1358.           connect = self.connectList     [treeItem.parent().parent().UserType & 0x1FFFFFFF]
  1359.           database= connect.databaseList [treeItem.parent().UserType          & 0x3FFFFFFF]
  1360.           table   = database.tableList   [treeItem.UserType                   & 0x7FFFFFFF]
  1361.  
  1362.           commandHelper = connect.openSQLConnect2_helper(database.strName, table.strName)
  1363.           commandHelper2 = connect.openSQLConnect2_helper(database.strName, table.strName)
  1364.           commandHelper3 = connect.openSQLConnect2_helper(database.strName, table.strName)
  1365.          
  1366.           # Update pick var
  1367.           self.pickConnect = connect
  1368.           self.pickDatabase= database
  1369.           self.pickTable   = table
  1370.          
  1371.           commandHelper.command_get_column_infos()
  1372.          
  1373.           listHeader = []
  1374.           # Serach && Append field
  1375.           for iter in commandHelper:
  1376.             field = SQL_field()
  1377.             field.updateDataByTuple(iter)
  1378.            
  1379.             # Append col header
  1380.             listHeader.append(field.strName)
  1381.        
  1382.             self.fieldList.append(field)
  1383.    
  1384.           self.ui.tbData.setColumnCount(len(listHeader))
  1385.           self.ui.tbData.setHorizontalHeaderLabels(listHeader)
  1386.      
  1387.           commandHelper2.command("SELECT COUNT(*) FROM " + linkSQLVarName(table.strName))
  1388.          
  1389.           self.ui.tbData.setRowCount(commandHelper2.fetchone()[0])
  1390.           self.ui.tbField.setRowCount(len(self.fieldList))
  1391.          
  1392.           for iter, sqlItemField in enumerate(self.fieldList):
  1393.            
  1394.             QTbItem1 = QtWidgets.QTableWidgetItem("supported/unknown data")
  1395.            
  1396.             if sqlItemField.intField >= 0:
  1397.               QTbItem1.setText(sqlItemField.strFieldType)
  1398.    
  1399.             self.ui.tbField.setItem(iter, 0, QtWidgets.QTableWidgetItem(sqlItemField.strName))
  1400.             self.ui.tbField.setItem(iter, 1, QTbItem1)
  1401.             self.ui.tbField.setItem(iter, 2, QtWidgets.QTableWidgetItem(str(sqlItemField.getMainlen())))
  1402.             self.ui.tbField.setItem(iter, 3, QtWidgets.QTableWidgetItem(str(sqlItemField.getSublen())))
  1403.            
  1404.             commandHelper3.command("SELECT " + linkSQLVarName(sqlItemField.strName)\
  1405.                             + " FROM " + linkSQLVarName(table.strName))
  1406.  
  1407.             for iter2, sqlItemField2 in enumerate(commandHelper3):
  1408.               item0 = QtWidgets.QTableWidgetItem(str(sqlItemField2[0]))
  1409.               item0.setFlags(item0.flags() & (~QtCore.Qt.ItemIsEditable))
  1410.              
  1411.               self.ui.tbData.setItem(iter2, iter, item0)
  1412.              
  1413.           self.setListFieldMisc()
  1414.          
  1415.       except Exception as e:
  1416.         QtWidgets.QMessageBox().critical(None, 'MySQL Connect', str(e), QtWidgets.QMessageBox.Ok)
  1417.        
  1418.         self.pickConnect = None
  1419.         self.pickDatabase= None
  1420.         self.pickTable   = None
  1421.        
  1422.         self.ui.tbField.setRowCount(0)
  1423.         self.ui.tbData.setRowCount(0)
  1424.       finally:
  1425.         commandHelper.close()
  1426.         commandHelper2.close()
  1427.         commandHelper3.close()
  1428.        
  1429.   def onSQLSettings(self):
  1430.     dlgSQLSettings = CDialogSQLSettings(self.connectList, self)
  1431.     dlgSQLSettings.exec()
  1432.     if dlgSQLSettings.isInsertDone_ != False:
  1433.       # Reset all view
  1434.       # Append to tail (QTreeWidget)
  1435.       # TODO: check repeat name
  1436.       dlgSQLSettings.newItem.enumALLInfos()
  1437.      
  1438.       topCurrentNode = QtWidgets.QTreeWidgetItem([dlgSQLSettings.newItem.strConnectName])
  1439.       topCountNode = self.ui.trConnect.topLevelItemCount()
  1440.       topCurrentNode.UserType = topCountNode | 1 << 29
  1441.       topCurrentNode.setIcon(0, QtGui.QIcon("link.ico") )
  1442.      
  1443.       for iter in dlgSQLSettings.newItem.databaseList:
  1444.         levelCurrentNode = QtWidgets.QTreeWidgetItem(topCurrentNode, [iter.strName])
  1445.         levelCurrentNode.UserType = topCurrentNode.childCount() - 1 | 1 << 30
  1446.         levelCurrentNode.setIcon(0, QtGui.QIcon("database.ico"))
  1447.         for iter2 in iter.tableList:
  1448.           # FIXME: index | shift, too simple mark range and index
  1449.           levelCurrentNode2 = QtWidgets.QTreeWidgetItem(levelCurrentNode, [iter2.strName])
  1450.           levelCurrentNode2.UserType = levelCurrentNode.childCount() - 1 | 1 << 31
  1451.           levelCurrentNode2.setIcon(0, QtGui.QIcon("table.ico"))
  1452.  
  1453.       self.ui.trConnect.insertTopLevelItem(topCountNode, topCurrentNode)
  1454.      
  1455. if __name__ == '__main__':
  1456.   app = QtWidgets.QApplication(sys.argv)
  1457.   window = CMainFrame()
  1458.   window.show()
  1459.   sys.exit(app.exec_())
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!