Source

find-gift / load_data.py

Full commit
import settings
import subprocess
import xlrd

from sqlalchemy import create_engine, orm
from models import Gift

engine = create_engine(settings.DATABASE_DSN)
Session = orm.sessionmaker(bind=engine)()


def load_data():

    data = [
        dict(
            title='jojo',
            price=78,
            sexo='male',
            age=15,
            category='mujer',
            keywords=['auto2', 'cc', 'band'],
            sku='20009',
            gender='male'
        ),
        dict(
            title='carro',
            price=78,
            sexo='male',
            age=15,
            category='mujer',
            keywords=['auto', 'toyota', 'rueda'],
            sku='20001',
            gender='male'
        ),
        dict(
            title='pato',
            price=12,
            sexo='male',
            age=15,
            category='juvenil',
            keywords=['pato', 'animal', 'amarillo'],
            sku='20002',
            gender='male'
        ),
        dict(
            title='silla',
            price=78,
            sexo='male',
            age=40,
            category='hombre',
            keywords=['silla', 'madera'],
            sku='20003',
            gender='male'
        ),
        dict(
            title='guitarra',
            price=78,
            sexo='female',
            age=15,
            category='oportunidades',
            keywords=['guitarra', 'electrica'],
            sku='20004',
            gender='female'
        ),
        dict(
            title='twitter',
            price=78,
            sexo='female',
            age=15,
            category='oportunidades',
            keywords=['twitter', 'electrica'],
            sku='20005',
            gender='female'
        ),
        dict(
            title='skype',
            price=78,
            sexo='female',
            age=15,
            category='oportunidades',
            keywords=['sdsd', 'skype'],
            sku='20006',
            gender='male'
        ),
        dict(
            title='web',
            price=78,
            sexo='female',
            age=15,
            category='oportunidades',
            keywords=['web2', 'web'],
            sku='20007',
            gender='male'
        ),
        dict(
            title='teatro',
            price=78,
            sexo='female',
            age=15,
            category='oportunidades',
            keywords=['teatro', 'peaple'],
            sku='20008',
            gender='female'
        ),
        ]

    return data


def load_data_excel():

    path = '/home/ronald/Desktop/products.xlsx'
    workbook = xlrd.open_workbook(path)
    worksheet = workbook.sheet_by_name('Sheet1')
    num_rows = worksheet.nrows - 1
    curr_row = 1

    items = []

    while curr_row < num_rows:
        curr_row += 1

        title = 'item %s' % curr_row
        marca = worksheet.cell_value(curr_row, 0)
        description = worksheet.cell_value(curr_row, 1)
        sku = worksheet.cell_value(curr_row, 2)
        price = worksheet.cell_value(curr_row, 3)
        gender = worksheet.cell_value(curr_row, 4)
        category = worksheet.cell_value(curr_row, 5)
        age = worksheet.cell_value(curr_row, 9)
        keywords = ['auto2', 'cc', 'band']

        if gender == 'H/M':
            gender = gender.replace('/', ',')

        price = str(price)

        if 'desde' or 'DESDE' in price:
            price = price.replace('desde ', '')
            price = price.replace('DESDE', '')

        if age == 'todas las edades':
            _ages = '0'
        else:
            _ages = []
            for x in age.split('-'):
                try:
                    x = int(x)
                except:
                    x = 0
                _ages.append(str(x))
            _ages = ','.join(_ages)

        items.append({'marca': marca, 'description': description,
                'sku': sku, 'price': price, 'gender': gender,
                'category': category, 'age': _ages, 'keywords': keywords,
                'title': title})
    return items


def delete_data():

    subprocess.call([
    'mysql', '-u', 'root', '-proot', '-e',
    'use saga_gifts; delete from gifts'])


data = load_data_excel()
delete_data()

for x in data:
    gift = Gift()
    gift.title = x.get('title')
    gift.sku = x.get('sku')
    gift.age = x.get('age')
    gift.keywords = ','.join(x.get('keywords'))
    gift.price = x.get('price')
    gift.category = x.get('category')
    gift.stores = 'a, b, c'
    gift.gender = x.get('gender')
    Session.add(gift)

Session.commit()