def get_fileds_type(tablename):
query = "SHOW FIELDS FROM %s " % tablename
filed_type = my_query(query)
# 对外键不能设空值，排除带有id的字段
filed_to_datatype = {
filed['Field']: re.search(pattern, filed['Type']).group()
for filed in filed_type if not filed['Field'].endswith('_id')
}
return filed_to_datatype
def related_lot_id(auction_id):
query = "select t.id as t_id,t.lot, l.lot_no,l.id as l_id from transaction as t " \
"left join lot as l on t.lot= l.lot_no where l.auction_id='%s' and t.auction_id=%s" % (auction_id,auction_id)
count = 0
lots = my_query(query)
args = []
for lot in lots:
args.append([lot['l_id'], lot['t_id'], auction_id])
query_update = "UPDATE transaction set lot_id=%s where id=%s and auction_id=%s"
my_insert(query_update, args)
print '-------------------------------------------------'
print u'<总结>第%s届拍卖会关联' % (auction_id)
print '--------------------------------------------------'
def related_lot_id(auction_id):
query = "select t.id as t_id,t.lot, l.lot_no,l.id as l_id from transaction as t " \
"left join lot as l on t.lot= l.lot_no where l.auction_id='%s' and t.auction_id=%s" % (auction_id,auction_id)
count = 0
lots = my_query(query)
args = []
for lot in lots:
args.append([lot['l_id'], lot['t_id'], auction_id])
query_update = "UPDATE transaction set lot_id=%s where id=%s and auction_id=%s"
my_insert(query_update, args)
print '-------------------------------------------------'
print u'<总结>第%s届拍卖会关联' % (auction_id)
print '--------------------------------------------------'
def crm_client():
query = "select number from crms_paper where number <>'' "
ids_from_pg = pg_query('CRM', query)
ids_from_pg = [item[0] for item in ids_from_pg]
for num in ids_from_pg:
try:
# print '->>',num
match = re.search(r'[\w-]+', num).group()
# print match
except re.error as e:
print e
query = "select identity_number from contract where identity_number LIKE '%s'" % ('%%%s%%' % match)
records = my_query(query)
if records:
print match
def check_identity():
query = "select identity_num from sellers where identity_num <>'' "
ids_from_pg = pg_query(db, query)
ids_from_pg = [item[0] for item in ids_from_pg]
for num in ids_from_pg:
try:
# print '->>',num
match = re.search(r'[\w-]+', num).group()
# print match
except re.error as e:
print e
query = "select number from customer_identity where number LIKE '%s'" % ('%%%s%%' % match)
records = my_query(query)
if records:
print match
def related_lot_id(auction_id):
"""
合同id和拍品信息关联起来
:param auction_id:
:return:
"""
query = (
"select l.id, c.id,c.electronic_id from lot as l left join contract as c on c.electronic_id = l.contract_id "
"where l.auction_id=%s" % auction_id)
contract_ids = my_query(query)
args = []
for item in contract_ids:
args.append([item['c.id'], item['id'], auction_id])
print(item['c.id'], item['id'], auction_id)
query_update = 'update lot set contract_id=%s where id=%s and auction_id=%s'
my_insert(query_update, args)
print '%关联成功'
def handle_fileds(tablename, conn):
"""
set specail filed(Null,NONE) to a defualt value
:return:
"""
cursor = conn.cursor()
filed_to_datatype = get_fileds_type(tablename)
fileds = [filed for filed in filed_to_datatype.keys()]
query = "SELECT * FROM %s " % tablename
records = my_query(query)
null_filed = []
for record in records:
flag = False
for fd in fileds:
if record[fd] in [None, 'NULL']: # 如果该字段为空的处理 fd 为字段
null_filed.append(fd)
flag = True
if filed_to_datatype[fd] in TYPE_MAPPING:
default_values = TYPE_MAPPING[
filed_to_datatype[fd]] # 获得字段的类型及对应的默认值
record[fd] = default_values
else:
print u'请把%s加入映射表' % filed_to_datatype[fd]
else:
pass
if flag:
filed_id = record.get('id') if record.get('id') else record.get(
'ID')
# 代码片段：更新数据库
query = update_table(tablename, record, null_filed, filed_id)
cursor.execute(query)
# 清空列表内容
null_filed = []
# 一次性提交，效率较高
conn.commit()
cursor.close()
def handle_fileds(tablename, conn):
"""
set specail filed(Null,NONE) to a defualt value
:return:
"""
cursor = conn.cursor()
filed_to_datatype = get_fileds_type(tablename)
fileds = [filed for filed in filed_to_datatype.keys()]
query = "SELECT * FROM %s " % tablename
records = my_query(query)
null_filed = []
for record in records:
flag = False
for fd in fileds:
if record[fd] in [None, 'NULL']: # 如果该字段为空的处理 fd 为字段
null_filed.append(fd)
flag = True
if filed_to_datatype[fd] in TYPE_MAPPING:
default_values = TYPE_MAPPING[filed_to_datatype[fd]] # 获得字段的类型及对应的默认值
record[fd] = default_values
else:
print u'请把%s加入映射表' % filed_to_datatype[fd]
else:
pass
if flag:
filed_id = record.get('id') if record.get('id') else record.get('ID')
# 代码片段：更新数据库
query = update_table(tablename, record, null_filed, filed_id)
cursor.execute(query)
# 清空列表内容
null_filed = []
# 一次性提交，效率较高
conn.commit()
cursor.close()
def check_customer(name):
query = "select name from customer where name like '%s' " % ('%%%s%%' % name)
records = my_query(query)
if not records:
print name
def get_fileds_type(tablename):
query = "SHOW FIELDS FROM %s " % tablename
filed_type = my_query(query)
# 对外键不能设空值，排除带有id的字段
filed_to_datatype = {filed['Field']: re.search(pattern, filed['Type']).group() for filed in filed_type if not filed['Field'].endswith('_id')}
return filed_to_datatype