代码之家 › 专栏 › 技术社区 › Yevhen_Radchenko

将数据从数据库存储到json文件

database json python

-1

Yevhen_Radchenko · 技术社区 · 7 年前

因此,我创建了数据库,将一些数据存储到表中,并希望将其导入到json格式,以便用一些js脚本将其可视化。但当我试图将其写入json时

valueerror:无法解码任何json对象

代码如下:

from PyBambooHR import PyBambooHR
import sqlalchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import json


Base = declarative_base()


class EmployeeData(Base):

    __tablename__ = 'employee_data'
    id = Column(Integer, primary_key=True)
    name = Column(String(120))
    department = Column(String(120))
    jobTitle = Column(String(120))
    email = Column(String(120))

    def __init__(self, name, department, jobTitle, email):
        self.name = name
        self.department = department
        self.jobTitle = jobTitle
        self.email = email


engine = sqlalchemy.create_engine('sqlite:///employee_db.db')

connection = engine.connect()

Base.metadata.create_all(engine)


bamboo = PyBambooHR(subdomain='domain', api_key='apikey')

session_factory = sessionmaker(engine)
session = session_factory()

employees = bamboo.get_employee_directory()
employees_list = [EmployeeData(name=item['displayName'], department=item['department'], jobTitle=item['jobTitle'], email=item['workEmail']) for item in employees]

avoid_duplicates = list(connection.execute('select * from employee_data'))

for i in employees_list:
    if i.name not in [j[1] for j in avoid_duplicates]:
        session.add(i)

session.commit()
session.close()
connection.close()

with open('employee_db.db', 'rb') as input_file:
    content = json.load(input_file)
with open('employee_data.json', 'wb') as output_file:
    json.dump(content,output_file, indent=1)

2 回复 | 直到 7 年前

bruno desthuilliers 7 年前

很明显:

with open('employee_db.db', 'rb') as input_file:
    content = json.load(input_file)

你从哪里得到巴洛克式的想法,sqlite数据库将是json格式的?

Yevhen_Radchenko 7 年前

好吧,如果有人需要它来和斑布尔一起工作

url='带api密钥的请求'必须像 ’ https:// API_KEY @api.bamboohr.com/api/gateway.php/ SUBDOMAIN /v1/employees/directory ’

你可以通过点击右角的照片并点击api键从bamboohr获取api键。

import sqlalchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import xml.etree.ElementTree as ET
import json, requests


Base = declarative_base()


class EmployeeData(Base):

    __tablename__ = 'employee_data'
    id = Column(Integer, primary_key=True)
    name = Column(String(120))
    department = Column(String(120))
    jobTitle = Column(String(120))
    email = Column(String(120))

    def __init__(self, name, department, jobTitle, email):
        self.name = name
        self.department = department
        self.jobTitle = jobTitle
        self.email = email


engine = sqlalchemy.create_engine('sqlite:///employee_db.db')

connection = engine.connect()

Base.metadata.create_all(engine)


url = 'request-with-api-key'
r = requests.get(url)
root = ET.fromstring(r.text)

employees = []
for emp in root.iter('employee'):
        name_photo = {'name': '', 'department': '', 'jobTitle': '', 'email': ''}
        for data in emp.iter('field'):
            if data.attrib['id'] == 'displayName':
                name_photo['name'] = data.text
            elif data.attrib['id'] == 'department':
                name_photo['department'] = data.text
            elif data.attrib['id'] == 'jobTitle':
                name_photo['jobTitle'] = data.text
            elif data.attrib['id'] == 'workEmail':
                name_photo['email'] = data.text
            else:
                continue
        employees.append(name_photo)

session_factory = sessionmaker(engine)
session = session_factory()

employees_list = [EmployeeData(name=item['name'], department=item['department'], jobTitle=item['jobTitle'], email=item['email']) for item in employees]
avoid_duplicates = list(connection.execute('select * from employee_data'))

for i in employees_list:
    if i.name not in [j[1] for j in avoid_duplicates]:
        session.add(i)

session.commit()

write_list = [{'name': i[1], 'department': i[2], 'jobTitle': i[3], 'email': i[4]} for i in list(connection.execute('select * from employee_data'))]

session.close()
connection.close()

with open('employee_data.json', 'w') as file:
    json.dump(write_list, file)
file.close()