def insertcgbl(): db = sql.connect( host="localhost", user="******", password="", db="rest", port=3306, charset='utf8', ) # 使用cursor()方法获取操作游标 cur = db.cursor() # # 备份数据库,备份上一次数据库 try: YN = index_common.table_exists(cur, 'gkrestcgbl.bak') if YN == 1: cur.execute( 'drop table if exists `gkrestcgbl.bak` ') # 在插入之前清除数据库 cur.execute( "CREATE TABLE if not exists `gkrestcgbl.bak` SELECT * FROM `gkrestcgbl`; " ) cur.execute( "ALTER TABLE `gkrestcgbl.bak` ADD PRIMARY KEY(`id_cgbl`); " ) # 设置主键 else: # 删除表之前先备份 cur.execute( "CREATE TABLE if not exists `gkrestcgbl.bak` SELECT * FROM `gkrestcgbl`; " ) cur.execute( "ALTER TABLE `gkrestcgbl.bak` ADD PRIMARY KEY(`id_cgbl`); " ) # 设置主键 except Exception as e: db.rollback() #出错回滚 db.commit() #插入数据之前清空数据 cur.execute('DELETE FROM `gkrestcgbl` WHERE 1') # 在插入之前清除数据库数据 print('清除成功') # 插入到gkrestcgbl get_cgbl = operaterestcgbl() for i in get_cgbl: auto_increment = cur.lastrowid #获取最新自增id # print(auto_increment) i.insert(0, auto_increment + 1) #解决持股比例为空的情况,导入数据库会报错,i[7]表示持股比例 if i[7] == '': i[7] = float(0.0) cur.execute( 'INSERT INTO `gkrestcgbl` VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', i) # cur.executemany('INSERT INTO `gkrestcgbl` VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', get_cgbl) cur.connection.commit() # 执行commit操作,插入语句才能生效 print("cgbl insert ok")
def insertjcxx(): db = sql.connect( host="localhost", user="******", password="", db="rest", port=3306, charset='utf8', ) # 使用cursor()方法获取操作游标 cur = db.cursor() # 插入到restdw get_jcxx = operatorjcxx() # 备份数据库,备份上一次数据库 try: if index_common.table_exists(cur, 'gkrestjcxx.bak') == 1: cur.execute( 'drop table if exists `gkrestjcxx.bak` ') # 在插入之前清除数据库 cur.execute( "CREATE TABLE if not exists `gkrestjcxx.bak` SELECT * FROM `gkrestjcxx`; " ) cur.execute( "ALTER TABLE `gkrestjcxx.bak` ADD PRIMARY KEY(`jcxx_id`); " ) # 设置主键 else: # 删除表之前先备份 cur.execute( "CREATE TABLE if not exists `gkrestjcxx.bak` SELECT * FROM `gkrestjcxx`; " ) cur.execute( "ALTER TABLE `gkrestjcxx.bak` ADD PRIMARY KEY(`jcxx_id`); " ) # 设置主键 except Exception as e: db.rollback() # 出错回滚 db.commit() # 入数据之前清空数据 cur.execute('DELETE FROM `gkrestjcxx` WHERE 1') # 在插入之前清除数据库 cur.connection.commit() print('清除成功') # print(get_jcxx) # 单条插入 for i in get_jcxx: auto_increment = cur.lastrowid # 获取最新自增id i.insert(0, auto_increment + 1) # print(i) cur.execute( 'INSERT INTO `gkrestjcxx` VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', i) # #多条插入 # cur.executemany('INSERT INTO `gkrestjcxx` VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', get_jcxx) cur.connection.commit() # 执行commit操作,插入语句才能生效 print('jcxx insert over')
def insert_rest_personlist(): conn = sql.connect( host="localhost", user="******", password="", db="rest", port=3306, charset='utf8', ) # 使用cursor()方法获取操作游标 cur = conn.cursor() # 备份数据库,备份上一次数据库 try: YN = index_common.table_exists(cur, 'gk_rest_relation.bak') if YN == 1: cur.execute( 'drop table if exists `gk_rest_relation.bak` ') # 在插入之前清除数据库 cur.execute( "CREATE TABLE if not exists `gk_rest_relation.bak` SELECT * FROM `gk_rest_relation`; " ) cur.execute( "ALTER TABLE `gk_rest_relation.bak` ADD PRIMARY KEY(`id`); " ) # 设置主键 else: # 删除表之前先备份 cur.execute( "CREATE TABLE if not exists `gk_rest_relation.bak` SELECT * FROM `gk_rest_relation`; " ) cur.execute( "ALTER TABLE `gk_rest_relation.bak` ADD PRIMARY KEY(`id`); " ) # 设置主键 except Exception as e: conn.rollback() # 出错回滚 conn.commit() # 获取数据 data = out_relation_in() # 插入数据之前清空数据 cur.execute('DELETE FROM `gk_rest_relation` WHERE 1') # 在插入之前清除数据库数据 print('清除成功') #单条插入数据 for i in data: auto_increment = cur.lastrowid # 获取自增id i.insert(0, auto_increment + 1) cur.execute('INSERT INTO `gk_rest_relation` VALUES (%s,%s,%s,%s)', i) print("ok")
def getName(): temp_data = [] #临时存放id----name值,最终插入数据库需要 url = 'http://111.198.138.113:81/seeyon/rest/token/lichun/psw.seeyon@7898' result = requests.get(url) # print(result.cookies) json_str = json.loads(result.text) # print(json_str["id"]) token = json_str["id"] headers = \ { # 'Host' : '111.198.138.113:81', "Accept": "application/json", "User-Agent": "Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/32.0.1700.76 Safari/537.36", "content-type": "application/json", # 'cookie':"JSESSIONID=0CAFC4421EF851D9498B17B553237B12", 'token': token, "Content-Type": "application/json;charset=UTF-8", } #连接数据库的参数 db = sql.connect( host="localhost", user="******", password="", db="rest", port=3306, charset='utf8', ) # SQL:使用cursor()方法获取操作游标 cur = db.cursor() param = id() #获得了所有公司的id编码 print("所有公司id输出:", param) #输出现有公司编号 # 备份数据库,备份上一次数据库 try: if index_common.table_exists(cur, 'gkrest_id_name.bak') == 1: cur.execute( 'drop table if exists `gkrest_id_name.bak` ') # 在插入之前清除数据库 cur.execute( "CREATE TABLE if not exists `gkrest_id_name.bak` SELECT * FROM `gkrest_id_name`; " ) cur.execute( "ALTER TABLE `gkrest_id_name.bak` ADD PRIMARY KEY(`id`); " ) # 设置主键 else: # 删除表之前先备份 cur.execute( "CREATE TABLE if not exists `gkrest_id_name.bak` SELECT * FROM `gkrest_id_name`; " ) cur.execute( "ALTER TABLE `gkrest_id_name.bak` ADD PRIMARY KEY(`id`); " ) # 设置主键 except Exception as e: db.rollback() # 出错回滚 db.commit() cur.execute('DELETE FROM `gkrest_id_name` WHERE 1') #在插入之前清除数据库 cur.connection.commit() print('清除成功') count = 0 #计算更新了几个公司名 for pa in param: url = 'http://111.198.138.113:81/seeyon/rest/orgDepartment/' + pa #获取不同公司的信息 response = requests.get(url, headers=headers) result = json.loads(response.text) #转化为json数据 name = result['name'] temp_data = [pa, name] # print(temp_data) cur.execute('INSERT INTO `gkrest_id_name` VALUES (%s,%s)', temp_data) cur.connection.commit() count += 1 print("%d" % (count)) print("更新%d条数据" % len(param))