def find(self, text): print(text) self.list.clear() length = len(text) it = root print(length) for index in range(length): if it == 'end': break it = self.find_it(it, text[index]) print(text) if len(self.list) == 0: return self.answer sql = "SELECT answer FROM root" for u in range(0, len(self.list) - 1): sql = sql + "_" + str(self.list[u]) sql = sql + " WHERE q_id = " + str(self.list[len(self.list) - 1]) + ";" print(sql) conn = mysql.connect() if conn is None: return self.answer cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchone() if result is None: return self.answer mysql.close(conn) return result[0]
def get_item_barcode(): conn = None cursor = None item_barcode_list = [] try: # get mysql connection conn = mysql.get_connection() # get cursor cursor = conn.cursor() # sql sql = "SELECT store, sku_id, unit_qty, ware_id, o2o_sku_id FROM `tabItem Barcode`" db_result = cursor.execute(sql) for row in cursor.fetchall(): item_barcode_list.append({ 'store': row[0], 'sku_id': row[1], 'unit_qty': row[2], 'ware_id': row[3], 'o2o_sku_id': row[4] }) return item_barcode_list except Exception as e: logging.warn(e) finally: mysql.close(conn, cursor)
def get_uom(item_code, unit_qty): """ try: db_result = frappe.get_doc( 'UOM Conversion Detail', { 'parent': item_code, 'conversion_factor': unit_qty } ) if db_result: return db_result.get('uom') else: return '' except Exception as e: print e """ conn = None cursor = None try: conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT uom FROM `tabUOM Conversion Detail` WHERE parent = '%s' AND unit_qty = '%s'" % (item_code, unit_qty) db_result = cursor.execute(sql) if db_result: return cursor.fetchone() else: return '' except Exception as e: logging.warn(e) finally: mysql.close(conn, cursor)
def erp_data_item_barcode(): conn = None cursor = None try: #import pdb;pdb.set_trace() conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT sku_id,unit_qty,ware_id,o2o_sku_id,name FROM `tabItem Barcode`" db_result = cursor.execute(sql) j = 0 list = cursor.fetchall() for result in list: item.append({}) item[j]['item_code'] = result[0] item[j]['unit_qty'] = result[1] item[j]['ware_id'] = result[2] item[j]['o2o_sku_id'] = result[3] item[j]['name'] = result[4] if item[j]['ware_id'] and item[j]['o2o_sku_id']: item[j]['isExist'] = 1 #在后期函数调用中通过isExist值判断是否需要进入O2O查询 else: item[j]['isExist'] = 0 j = j + 1 except Exception as e: print (e) logging.warn(e) finally: mysql.close(conn, cursor)
def erp_data_item(erp_item): conn = None cursor = None try: conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT barcode,item_name,net_weight,packing_name,production_location,specification,item_group,shelf_life FROM `tabItem`WHERE item_code=%s"%erp_item['item_code'] db_result = cursor.execute(sql) try: item = cursor.fetchone() except Exception as e: logging.info(e) #erp_item['barcode'] = item[0] erp_item['item_name'] = item[1] erp_item['net_weight'] = item[2] erp_item['packing_name'] = item[3] erp_item['production_location'] = item[4] erp_item['spec_count'] = item[5] erp_item['item_group'] = item[6] erp_item['shelf_life'] = item[7] except Exception as e: logging.warning(e) finally: mysql.close(conn, cursor) return erp_item
def get_item_price(store_id, item_code, uom): conn = None cursor = None try: conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT A.price_list_rate FROM `tabItem Price` A, `tabPrice List` B WHERE A.price_list = B.price_list_name" \ " AND B.store_id = '%s' AND A.item_code = '%s' AND A.unit = '%s'" % (store_id, item_code, uom) db_result = cursor.execute(sql) return cursor.fetchone()[0] except Exception as e: logging.warn(e) finally: mysql.close(conn, cursor)
def compare(erp_item): conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT uom FROM `tabUOM Conversion Detail` " \ "WHERE parent='%s' AND conversion_factor='%s'" % (erp_item['item_code'],erp_item['unit_qty']) db_result = cursor.execute(sql) uom = cursor.fetchone()[0] erp_item['uom'] = uom mysql.close(conn, cursor) erp_item = erp_data_conversion(erp_item) erp_item = erp_data_item(erp_item) erp_item['item_code'] = str(erp_item['item_code']) + '&' + erp_item['uom']#o2o中物料编码为item_code和uom dubbo_client = duduboo.DubboClient() #skuid_spec_data = dubbo_client.get_spec_rule_by_skuid([Long(1000210212)]) wareid_spec_data = dubbo_client.get_spec_rule_by_ware_id([Long(erp_item['ware_id'])]) #skuid_spec_data = skuid_spec_data1[0] if wareid_spec_data is not None: for key, value in dict_erp_o2o.items(): if hasattr(wareid_spec_data,key): if erp_item[value] is not None and getattr(wareid_spec_data,key) is not None: if erp_item[value] != getattr(wareid_spec_data,key): logging.info('%s=%s和%s=%s不相等' %(value,erp_item[value],key,getattr(wareid_spec_data,key))) #if erp_item['barcode'] is not None and wareid_spec_data.itemNum is not None: # if erp_item['barcode'] != wareid_spec_data.itemNum: # logging.info('barcode=%s和itemNum=%s不相等' %(erp_item['barcode'],wareid_spec_data.itemNum)) #if erp_item['item_name'] is not None and wareid_spec_data.title is not None: # if erp_item['item_name'] != wareid_spec_data.title: # logging.info('item_name=%s和title=%s不相等' % (erp_item['item_name'], wareid_spec_data.title)) #if erp_item['net_weight'] is not None and wareid_spec_data.brgew is not None: # if erp_item['net_weight'] != wareid_spec_data.brgew: # logging.info('net_weight=%s和brgew=%s不相等' % (erp_item['net_weight'], wareid_spec_data.brgew)) #if erp_item['packing_name'] is not None and wareid_spec_data.chine is not None: # if erp_item['packing_name'] != wareid_spec_data.chine: # logging.info('packing_name=%s和brgew=%s不相等' % (erp_item['packing_name'], wareid_spec_data.chine)) if erp_item['item_group'] is not None and wareid_spec_data.matkl is not None: if int(erp_item['item_group']) != int(wareid_spec_data.matkl): logging.info('item_group=%s和matkl=%s不相等' % (erp_item['item_group'], wareid_spec_data.matkl)) if erp_item['shelf_life'] is not None and wareid_spec_data.wareLife is not None: if float(erp_item['shelf_life']) != wareid_spec_data.wareLife: logging.info('shelf_life=%s和wareLife=%s不相等' % (float(erp_item['shelf_life']), wareid_spec_data.wareLife)) #if erp_item['is_auxiliary_ware'] is not None and wareid_spec_data.isAuxiliaryWare is not None: # if erp_item['is_auxiliary_ware'] != wareid_spec_data.isAuxiliaryWare: # logging.info('is_auxiliary_ware=%s和isAuxiliaryWare=%s不相等' # % (erp_item['is_auxiliary_ware'], wareid_spec_data.isAuxiliaryWare)) else: logging.info('根据o2o_sku_id=%s未查询到数据!!!!'%Long(erp_item['o2o_sku_id']))
def get_item_name(store_id, item_code): conn = None cursor = None try: conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT short_name FROM `tabStore Item` WHERE store_id = '%s' AND item_code = '%s'" % (store_id, item_code) db_result = cursor.execute(sql) if db_result: return cursor.fetchone()[0] else: return '' except Exception as e: logging.warn(e) finally: mysql.close(conn, cursor)
def erp_data_conversion(erp_item): conn = None cursor = None try: conn = mysql.get_connection() cursor = conn.cursor() if erp_item['unit_qty'] > 1: # 查入数为1的o2o_sku_id sql = "SELECT o2o_sku_id FROM `tabItem Barcode` " \ "WHERE sku_id='%s' AND unit_qty='%s'" %(erp_item['item_code'],1) db_result = cursor.execute(sql) erp_item['is_auxiliary_ware'] = 1 erp_item['ref_sku'] = cursor.fetchone()[0] erp_item['spec_num'] = erp_item['unit_qty'] elif erp_item['unit_qty'] == 1: sql = "SELECT uom FROM `tabUOM Conversion Detail` " \ "WHERE parent='%s' AND conversion_factor='%s'" % (erp_item['item_code'],erp_item['unit_qty']) db_result = cursor.execute(sql) uom = cursor.fetchone()[0] erp_item['uom'] = uom if uom == 'kg' or uom == 'Kg' or uom == 'KG': erp_item['is_auxiliary_ware'] = 1 # 通过uom_cd_doc2记录的入数反查Item Barcode表中的o2o_sku_id sql = "SELECT uom FROM `tabUOM Conversion Detail` " \ "WHERE parent='%s' AND (uom='%s' or uom='%s')"% (erp_item['item_code'], 'g','G') db_result = cursor.execute(sql) conversion_factor = cursor.fetchone()[0] sql = "SELECT o2o_sku_id FROM `tabItem Barcode` " \ "WHERE sku_id='%s' AND unit_qty" % (erp_item['item_code'], conversion_factor) db_result = cursor.execute(sql) o2o_sku_id = cursor.fetchone()[0] erp_item['ref_sku'] = o2o_sku_id erp_item['spec_num'] = 1000 else: erp_item['is_auxiliary_ware'] = 0 erp_item['ref_sku'] = None erp_item['spec_num'] = None else: pass # logging.warning('unit_qty=%s值存在问题'%erp_item['unit_qty']) except Exception as e: logging.info(e) finally: mysql.close(conn, cursor) return erp_item
def shutdown(): # Create necessary database connections to use mysql.close() mongo.close()
import config, mysql # read config file config = config.Config("config.txt") # get connection mysql = mysql.MySQL(config.db_host, config.db_uname, config.db_passwd, config.db_name) # insert for id in range(10): data = (id % 2, "Doe % d" % id, "John", "place", "ville") mysql.insert_or_update(config.db_table, config.db_columns, data) # close connection mysql.close()
def sqlQuery(sql=""): db = mysql.connect() mysql.query(db, sql) mysql.close(db)
host = 'localhost' user = '' password = '' conn = MySQLdb.Connection(db=database, host=host, user=user, passwd=password) mysql = conn.cursor() sql = r"select * from annotations" mysql.execute(sql) fields = mysql.fetchall() mysql.close() conn.close() print fields # -------------------------------------------------------------------- # Reformat array of strings into portfolio of paintings: # -------------------------------------------------------------------- # Make mean and weight images: sum = numpy.zeros(nx, ny) sumsq = numpy.zeros(nx, ny) mean = numpy.zeros(nx, ny)
def queryMysql(self, sql): db = mysql.connect() mysql.query(db, sql) mysql.close(db)
def getMysql(self, sql): db = mysql.connect() result = mysql.getData(db, sql) mysql.close(db) return result