def insert_by_args(db_name, table_name, user,passwd,data_dict_list, arg_list): """ 将数据批量插入数据库,传入sql参数的是字段名list :param data_dict_list 以字典list的形式传入 :param arg_list 以["arg1", "arg2", "arg3"]的形式传入 """ db_con, db_cur = connect(db_name,user,passwd) insertion_part1 = ','.join(arg_list) insertion_part2 = ','.join(["%s" for i in range(len(arg_list))]) insert_clause = '''INSERT INTO %s (%s) VALUES (%s)''' % (table_name, insertion_part1, insertion_part2) logutils.debug(insert_clause) insert_param = [] for data_dict in data_dict_list: insert_param.append(tuple((data_dict.get(arg) for arg in arg_list))) insert_param_set = set(insert_param) # set去重 insert_param = list(insert_param_set) try: logutils.info(insert_clause) logutils.info('正在插入数据...') logutils.info(insert_clause) db_cur.executemany(insert_clause,insert_param) db_con.commit() logutils.info("插入完成") except Exception as e: logutils.error(e) logutils.error('批量插入失败,进行数据插入回滚') db_con.rollback() db_con.close()
def select_by_clause(dbname, user,passwd,sql_clause): db_con, db_cur = connect(dbname,user,passwd) data_list = [] try: db_cur.execute(sql_clause) data_list = db_cur.fetchall() except Exception: logutils.error("获取数据出错") db_con.close() return data_list
def request_json(url): headers = pass_useragent() try: res = requests.get(url, headers=headers) if res.status_code == 200: return res.json() else: logutils.error("网页请求错误" + str(res.status_code)) return None except requests.ConnectionError: logutils.error("网页请求错误")
def request_text(url): headers = pass_useragent() try: res = requests.get(url, headers=headers) if res.status_code == 200: res.encoding = chardet.detect(res.content)['encoding'] html = res.text return html else: logutils.error("网页请求错误" + str(res.status_code)) return None except requests.ConnectionError: logutils.error("网页请求失败")
def con2db_mysql(host, db_name, user, pass_wd, port=3306, charset='utf8'): while True: try: logutils.info('Connecting to the database: %s >>' % db_name) con = pymysql.connect(host=host, db=db_name, user=user, passwd=pass_wd, port=port, charset=charset) cur = con.cursor() cur.execute('SET NAMES utf8mb4') cur.execute('SET CHARACTER SET utf8mb4') cur.execute('SET character_set_connection=utf8mb4') logutils.info('Success') return con, cur except: logutils.error('Failure, Trying to reconnect>>')
def connect(dbname,user,passwd): connected = False while connected is False: try: db_con = pymysql.connect(host=host,user=user,passwd=passwd,db=dbname,port=3306,charset='utf8mb4') db_cur = db_con.cursor() db_cur.execute('SET NAMES utf8mb4') db_cur.execute("SET CHARACTER SET utf8mb4") db_cur.execute("SET character_set_connection=utf8mb4") logutils.info(str(dbname) + ' 数据库连接成功。') return db_con, db_cur except Exception as e: time.sleep(1) connected = False logutils.error('数据库连接失败{},正在尝试重新连接……'.format(e))
def insert_or_update_by_args(db_name, table_name, user,passwd,data_dict_list, arg_list,pk_list): """ 将数据插入数据库,如果插入失败则改为更新数据,传入的sql参数是字段名list,分为信息字段名list和主键字段名list :param data_dict 以字典的形式传入(不是列表) :param signature_arg_list 以["arg1", "arg2", "arg3"]的形式传入主键字段 :param info_arg_list 以["arg1", "arg2", "arg3"]的形式传入信息字段,不包括主键字段 """ db_con, db_cur = connect(db_name,user,passwd) insertion_part1 = ','.join(arg_list) insertion_part2 = ','.join(["%s" for i in range(len(data_dict_list)+1)]) insert_clause = '''INSERT INTO %s (%s) VALUES (%s)''' % (table_name, insertion_part1, insertion_part2) all_arg_list = list() all_arg_list.extend(arg_list) all_arg_list.extend(pk_list) for data_dict in data_dict_list: insert_param = tuple(data_dict.get(arg) for arg in arg_list ) update_part1 = '=%s,'.join(arg_list) + '=%s' update_part2 = '=%s and '.join(pk_list) + '=%s' update_clause = 'UPDATE %s SET %s WHERE %s' % (table_name, update_part1, update_part2) update_param = tuple(data_dict.get(arg) for arg in all_arg_list) try: logutils.debug(('插入数据:%s %s %s %s' % insert_param)) db_cur.execute(insert_clause,insert_param) db_con.commit() logutils.info("插入完成") except pymysql.IntegrityError: try: logutils.debug(('更新数据:%s %s %s %s %s' % update_param)) db_cur.execute(update_clause,update_param) db_con.commit() logutils.info("更新完成") except Exception: logutils.error("更新数据时发生错误,数据内容:%s" % data_dict) except Exception as e: print(e) logutils.error("插入数据时发生错误,数据内容:%s" % data_dict)
def update_by_args(db_name, table_name,user,passwd,data_dict_list,arg_list,pk_list): db_con, db_cur = connect(db_name, user, passwd) primary_column_list = db_util.primary_column_name(db_con, db_cur, db_name, table_name) all_column_list = db_util.all_column_name(db_con, db_cur, db_name, table_name) for each in primary_column_list: all_column_list.remove(each) if pk_list is None: pk_list = primary_column_list if arg_list is None: arg_list = all_column_list all_arg_list = list() all_arg_list.extend(arg_list) all_arg_list.extend(pk_list) update_part1 = '=%s,'.join(arg_list) + '=%s' update_part2 = '=%s and '.join(pk_list) + '=%s' update_clause = 'UPDATE %s.%s SET %s WHERE %s' % (db_name, table_name, update_part1, update_part2) update_param = [] for data_dict in data_dict_list: update_param.append(tuple(data_dict.get(arg) for arg in all_arg_list)) update_param_set = set(update_param) # set去重 update_param = list(update_param_set) try: logutils.info('正在更新数据...' ) db_cur.executemany(update_clause,update_param) db_con.commit() #count += 1 logutils.info("更新完成") except Exception: logutils.error("更新数据时发生错误,内容:%s") db_con.close()