获取ATimeLogger数据并写入Sqlserver数据库

最新推荐文章于 2021-01-12 06:52:03 发布

qq_42678477

最新推荐文章于 2021-01-12 06:52:03 发布

阅读量309

点赞数

分类专栏： Python

本文链接：https://blog.csdn.net/qq_42678477/article/details/107619957

版权

Python 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

import requests
from requests.auth import HTTPBasicAuth
import json
import time
import datetime

import pymssql
import uuid

def get_connection():
    conn = pymssql.connect(server='127.0.0.1\sqlexpress',port=1433,user='xxx',password='xxx',database='xxxx')
    return conn

def close_connection(conn):
    conn.close

def excute_query(sql,params):
    con = get_connection()
    cursor = con.cursor()
    cursor.execute(sql,params)
    rows = cursor.fetchall()
    return rows

#打开sqlserver数据库，准备写入数据
conn = get_connection()
cursor = conn.cursor()



account_email = input("account:")
password = input("password")

url_types="https://app.atimelogger.com/api/v2/types"
auth_header = HTTPBasicAuth(account_email,password)

r_type = requests.get(url_types,auth=auth_header)

types = json.loads(r_type.text)

# 中文类别
types_dict_key = {}
for x in types['types']:
    act_name = x["name"]
    act_guid_name=x["guid"]
    types_dict_key[act_guid_name] = act_name


url_intervals = "https://app.atimelogger.com/api/v2/intervals"    

start_time =int(time.mktime(time.strptime('2017-2-1',"%Y-%m-%d")))
over_to =int(time.mktime(time.strptime('2020-7-28',"%Y-%m-%d")))

r_intervals = requests.get(url_intervals,auth=auth_header,params={
    'from': start_time,
    'to': over_to,
    'limit': 10000
})
intervals = json.loads(r_intervals.text)




num_count=0
eles = []
for x in intervals['intervals']:
    act_01 = x["type"]["guid"]
    act_02 = types_dict_key[act_01]
    st = datetime.datetime.fromtimestamp(x["from"])
    overtime = datetime.datetime.fromtimestamp(x["to"])
    comment001=x["comment"]
    num_count += 1
    ele = ('{{{0}}}'.format(uuid.uuid1()),act_02,str(st),str(overtime),comment001)
    eles.append(ele)


cursor.executemany(
    "insert into atime (object_id,lb,kssj,jzsj,sm) values (%s,%s,%s,%s,%s)",eles
)
conn.commit()

close_connection(conn)