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))