fmcsaDataScraper/sql_store.py at master · mikewolfd/fmcsaDataScraper · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
from peewee import *
from playhouse.pool import PooledSqliteDatabase
from generate_data import task_runner
# import logging
import concurrent.futures
import settings

# logging.basicConfig(format='%(levelname)s:%(message)s', level=logging.DEBUG)

def get_name():
   return '/'.join([settings.DATA_DIR, settings.DATABASENAME])

db = PooledSqliteDatabase(get_name(), max_connections=32,
                          pragmas={
                              'journal_mode': 'wal',
                              'cache_size': -1 * 512000,  # 64MB
                              'foreign_keys': 1,
                              'ignore_check_constraints': 0,
                              'synchronous': 0})


class dbBuild:
    @classmethod
    def populate_store(cls, values, *arg, **kwargs):
        index = Carrier.select().count()
        task_runner(cls.create_objects_atomic, values, index,
                    chunk_size=1000, *arg, **kwargs)

    @classmethod
    def create_objects_atomic(cls, objs, *arg, **kwargs):
        # logging.info('atomic')
        db.connect(reuse_if_open=True)
        cls.create_objects(objs)
        db.close()

    @classmethod
    def create_objects(cls, items):
        carrier = []
        freight = []
        vehicles = []
        for i in items:
            car, fre, veh = cls.build_object(i)
            carrier.append(car)
            freight.extend(fre)
            vehicles.extend(veh)
        Carrier.bulk_create(carrier)
        CarrierFreight.bulk_create(freight)
        CarrierVehicles.bulk_create(vehicles)

    @staticmethod
    def build_object(item):
        carrier_id = item.get('carrier_id')
        carrier = Carrier(id=carrier_id)
        freight = [CarrierFreight(value=v, freighttype=k, carrier_id=carrier)
                    for k, v in item['cargo'].items()]
        vehicles = []
        for vtype in item['types']:
            val = vtype.pop('vehicle_type')
            vehicles.append(CarrierVehicles(vehicletype=val,
                                            carrier_id=carrier_id, **vtype))
        return carrier, freight, vehicles


class BaseModel(Model):
    class Meta:
        database = db


class Carrier(BaseModel):
    id = IntegerField(primary_key=True)


class CarrierVehicles(BaseModel):
    owned = IntegerField()
    term_leased = IntegerField()
    trip_leased = IntegerField()
    carrier = ForeignKeyField(Carrier, backref='vehicles')
    vehicletype = CharField()

    class Meta:
        indexes = (
            (('carrier', 'vehicletype'), True),
        )


class CarrierFreight(BaseModel):
    value = BooleanField()
    carrier = ForeignKeyField(Carrier, backref='freight')
    freighttype = CharField()

    class Meta:
        indexes = (
            (('carrier', 'freighttype'), True),
        )


def create_tables():
    with db:
        db.create_tables([Carrier, CarrierVehicles, CarrierFreight], safe=True)

create_tables()