使用python遍历获取API接口数据,并解析存储到pg数据库案例
说明:此API接口是先获取到token,然后将token传入到接口内容返回数据
具体代码如下
import requests
import psycopg2
from datetime import datetime
import time
#获取token函数
def get_token():
url = "http://xxx.xxx.xxx.xx:8099/interfacewebservice.asmx/ToLogin"
querystring = {"grant_type":"password","scope":"read","userid":"xxxx","password":"xxxxxxxxx"}
headers = {
'Cache-Control': "no-cache",
'Postman-Token': "ee6f389c-d831-6bd8-1b22-1490a92281ff"
}
response = requests.request("GET", url, headers=headers, params=querystring)
return response.json()['token']
#获取数据库链接函数
def create_database():
try:
connection = psycopg2.connect(database="xxx", user="xxx", password="xxx", host="xxxx", port="xxx")
return connection
except psycopg2.Error as e:
print("数据库连接失败:", e)
return None
#获取api接口函数
def get_data(token_url,time_s):
url = "http://xxx.xxx.xxx.xx:8099/interfacewebservice.asmx/getMethod"
payload = "{\"MethodM\":\"getGDoorRecord\",\"token\":\"%s\",\"Sdate\":\"%s\" , \"page\":\"1\"}\r\n"%(token_url, time_s)
headers = {
'Content-Type': "application/json",
'Cache-Control': "no-cache",
'Postman-Token': "311d893a-82f0-d16a-e28b-6b8291763087"
}
response = requests.request("POST", url, data=payload, headers=headers)
return response.json()
#链接数据库
conn = create_database()
#打开回话框
cur = conn.cursor()
#清空当天数据
cur.execute("delete from getmethod_http_cs where to_char(to_date(sdate,'yyyy-mm-dd'),'yyyy-mm-dd') = SUBSTR(to_char(now(),'yyyy-mm-dd hh24:mi:ss'),1,10)")
#获取当前时间
cur.execute("select to_char(now(),'yyyy-mm-dd hh24:mi:ss')")
#传递参数到rows
rows = cur.fetchall()
#将字符串转换成日期格式
dt_obj = datetime.strptime(rows[0][0], "%Y-%m-%d %H:%M:%S") #这里数据库获取的日期先返回列表有包含了元祖所以需要rows[0][0]获取到字符串格式的日期
#将日期格式转换成时间戳
timestamp = int(time.mktime(dt_obj.timetuple()))
#调取获取数据的函数,并将token值和时间传递进去
content = get_data(get_token(),timestamp)
#循环返回的字典
for i in content['data']:
sql = "insert into getmethod_http_cs (id,doorname,userno,ulname,sdate,fx) values ('%s','%s','%s','%s','%s','%s')" % (i['id'], i['doorname'],i['userno'],i['Ulname'],i['Sdate'],i['fx'])
cur.execute(sql)
conn.commit()
#关闭回话窗口
cur.close()
#关闭数据库
conn.close()