How exactly should I be loading data from a .json file to the database via setup.py?
I have been trying to solve this problem for >6hours now. I have looked at the PonyORM documentation and the JSON documentation. I was unable to find sufficient information to solve the issue.
The entities.py file should be correct, fyi I have attached the intended structure of the database as an image. The populate_database() information is added to the database after it is created. This works as it is expected to.
The config.py is certainly working as it should be.
First I run this code to create the database and tables:
#entities.py
from datetime import date
from pony.orm import *
# creating db
db = Database()
db.bind('sqlite', 'db/mydata.sqlite', create_db=True)
# defining the brand class
class Brand(db.Entity):
name = PrimaryKey(str) #Primary Key - Brand, /only one/
nationality = Required(str) #Nationality, /only one/
cars = Set('Car') #Brand can have multiple cars, /one or more/
# defining the car class
class Car(db.Entity):
id = PrimaryKey(int, auto=True) #Primary Key - Car ID, /only one/
model = Required(str) #Model, /only one/
price = Required(float) #Price, /only one/
availability = Required(int) #Availability, /only one/
brand = Required(Brand) #A car must have only one brand, /only one/
order = Optional('Order') #A car may or may not belong to an order, /one or none/
# defining the order class
class Order(db.Entity):
id = PrimaryKey(int, auto=True) #Primary Key - Order ID, /only one/
dop = Required(date) #Date of purchase, automatically set when record is added, /only one/
cars = Set(Car) #Order can have multiple cars, /one or more/
customer = Required('Customer') #Customer to whom the order belongs, /only one/
# defining the customer class
class Customer(db.Entity):
number = PrimaryKey(str, 11) #Primary Key - Phone Number (because it is a unique attribute), /only one/
name = Required(str) #First name, /only one/
surname = Required(str) #Surname, /only one/
orders = Set(Order) #Order, One customer could have multiple orders, /one or more/
db.generate_mapping(create_tables=True)
@db_session
def populate_database():
Fiat = Brand(name='Fiat', nationality='Italian')
car1 = Car(model='Punto', price='123.00', availability='5', brand=ferrari)
jay= Customer(number='01211100001', name='Jay',surname='Son')
order1 = Order(dop='01/01/2017', cars=car1, customer=jay)
order1.cars.add(car1)
populate_database()
After I run this code to load the data from the json file to the database:
#setup.py
import json
import config
from entities import *
'''
Setup module
===========
The setup module should have all the methods that has to be executed before
the application is started (i.e. seeding the database).
'''
'''
Seed the database with information from the json dump file.
NOTE: this method is specific to each application, which means you have
to write your import method for your application.
'''
@db_session
def seed_database(dump_filename):
# reading the json file
data = json.load(open(dump_filename, 'r'))
# going through the list of customers
for record in data['Customer']:
customer = Customer(name = record['name'],
surname = record['surname'],
number = record['number'])
# going through the list of brands
for record in data['Brand']:
brand = Brand(name = record['name'],
nationality = record['nationality'])
# going through the list of cars
for record in data['Car']:
car = Car(model = record['model'],
price = record['price'],
availability = record['availability'],
brand = record['brand'])
# going throught the list of customers
for record in data['Order']:
order = Order(dop = record['dop'],
cars = record['car'],
customer = record['customer'])
if __name__ == "__main__":
seed_database(config.DB_DUMP_FILE_NAME)
This is the json file contents:
{
"Customer": [{
"number": "01277000000",
"name": "John",
"surname": "Green"
}],
"Brand": [{
"name": "Ferrari",
"nationality": "Italian"
}],
"Car": [{
"model": "Enzo",
"price": 123.00,
"availability": 5,
"brand": "Ferrari"
}, {
"model": "458",
"price": 432.10,
"availability": 4,
"brand": "Ferrari"
}],
"Order": [{
"dop": "01/01/2017",
"car": ,
"customer": "01277000000"
}, {
"dop": "01/02/2017",
"car": ,
"customer": "01277000000"
}]
}
The config.py simply contains:
# DB_FILE_NAME : path to the SQLite database file.
DB_FILE_NAME = 'db/mydata.sqlite'
# DB_DUMP_FILE_NAME : path to the json file that will be used to seed the database.
DB_DUMP_FILE_NAME = 'data/data.json'
Any advice will be greatly appreciated, thanks in advance.
Your loading script is almost correct except for a few details:
1) You should not unconditionally call populate_database() in entities.py, because it will try to insert the same data on each import and throw an error because of a duplicate data. Wrap it with if __name__ == '__main__'.
2) When you load an initial data from JSON file, you need to hardcode primary keys of loaded objects, and so you need to add id value to each Car description:
"Car": [{
"id": 1,
...
}, {
"id": 2,
...
}],
3) The JSON file is not a valid JSON, because you didin't specify a car attribute value for Order. Actually, it should be cars, not car, because in the Order entity definition cars attribute has the Set type. In JSON file you can specify a list of cars ids:
"Order": [{
"dop": "01/01/2017",
"cars": [1],
"customer": "01277000000"
}, {
"dop": "01/02/2017",
"cars": [2],
"customer": "01277000000"
}]
In the seed_database function you need to convert each id to corresponding car from a database:
# going throught the list of customers
for record in data['Order']:
order = Order(dop = record['dop'],
cars = [Car[id] for id in record['cars']], # <<< see here
customer = record['customer'])
After that your loading script should work as expected