add_lion.py

import sqlite3
from model import *
from datetime import datetime
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm.exc import NoResultFound
import re


lion_sqlite = sqlite3.connect('lion_internal.db')
trip_splite = sqlite3.connect('trip_new.db')

lion_db = lion_sqlite.cursor()
trip_db = trip_splite.cursor()

l_main = lion_db.execute('SELECT * FROM RAW_PROD_ITINERARY_MAIN')

engine = create_engine('sqlite:///trip_new.db', echo=False)

Session = sessionmaker(bind=engine)
session = Session()

def cleanhtml(raw_html):
    cleanr = re.compile('<.*?>')
    cleantext = re.sub(cleanr, '', raw_html)
    return cleantext


def commit():
    try:
        session.commit()
    except IntegrityError as e:
        session.rollback()


for l in l_main:
    d = tuple(l)
    trip_data = {}
    trip_data['continent'] = ''
    trip_data['country'] =  d[7]
    trip_data['title'] = d[9]
    trip_data['inumber'] = d[1]
    trip_data['agent'] = '雄獅'
    trip_data['offer'] = ''
    trip_data['description'] = ''

    new_t = Trip(**trip_data)
    session.add(new_t)
    commit()

    trip_id = session.query(Trip).filter_by(
            inumber=trip_data['inumber']).one().id


    date_data = {}
    date_data['tour_code'] = d[17]
    date_data['departure_date'] = datetime.strptime(d[16], "%Y%m%d")
    date_data['total'] = -1
    date_data['available'] = -1
    date_data['price'] = d[15]
    date_data['trip_id'] = trip_id

    new_dt = DateOption(**date_data)
    session.add(new_dt)
    commit()



l_detail = lion_db.execute('SELECT * FROM RAW_PROD_ITINERARY_DETAIL')
s = 0
a = 0
for l in l_detail:
    d = tuple(l)
    a +=1
    try:
        trip_id = session.query(Trip).filter_by(
                inumber=d[1]).one().id
        s += 1
        print(s,a)
    except NoResultFound:
        # print('trip not found')
        continue

    itinerary_data = {}
    itinerary_data['number'] = d[3]
    itinerary_data['title'] = d[4]
    itinerary_data['subtitle'] = cleanhtml(d[5]) if d[5] else ''
    itinerary_data['transportation'] = ''
    itinerary_data['meal'] = ''
    itinerary_data['hotel_name'] = ''
    itinerary_data['trip_id'] = trip_id

    new_i = Itinerary(**itinerary_data)
    session.add(new_i)
    commit()