将数据库中的数据存储为JSON文件

Question

所以我创建了数据库，将一些数据存储到表中，并希望将其导入JSON格式，以便使用一些JS脚本将其可视化。但是当我试图把它写成JSON时

ValueError: No JSON object could be decoded

代码如下：

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)

Answer 1

很明显：

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

你从哪里得到了这样一个巴洛克式的想法：sqlite数据库是json格式的？你知道吗

Answer 2

好的，这是工作代码，如果有人需要它来和BambooHR一起工作

url = 'request-with-api-key' must looks like 'https://API_KEY@api.bamboohr.com/api/gateway.php/SUBDOMAIN/v1/employees/directory'

You can get your API key from BambooHR by clicking on yours photo in the right corner and click API Keys

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