def on_set_base_info(cls, company_id, company_name=None, country=None, city=None, address=None, timezone=None,
currency=None, note=None):
""" 设置公司基础信息 """
session = DBSession()
com = session.query(cls).filter(cls.company_id == company_id).first()
if com:
if company_name:
com.company_name = company_name
if country:
com.country = country
if city:
com.city = city
if address:
com.address = address
if timezone:
com.timezone = timezone
if currency:
com.currency = currency
if note:
com.note = note
try:
session.add(com)
session.commit()
except Exception as why:
session.rollback()
message = "companies.on_set_base_info: {0}".format(why)
logging.info(message)
return -1
return 0
class LagouPipeline(object):
def __init__(self):
self.session = DBSession()
self.pool = redis.Redis(host='raspberrypi', port=6379, db=2)
def process_item(self, item, spider):
if isinstance(item, LagouItem):
if self.session.query(Jobs).filter(
Jobs.positionId == item['positionId'],
Jobs.companyId == item['companyId']).first():
pass
else:
obj = Jobs(
companyId=item['companyId'],
positionId=item['positionId'],
jobNature=item['jobNature'],
companyName=item['companyName'],
financeStage=item['financeStage'],
companyFullName=item['companyFullName'],
companySize=item['companySize'],
industryField=item['industryField'],
positionName=item['positionName'],
city=item['city'],
createTime=item['createTime'],
salary_low=item['salary_low'],
salary_high=item['salary_high'],
workYear=item['workYear'],
education=item['education'],
positionAdvantage=item['positionAdvantage'],
district=item['district'],
# uid=item['uid'],
companyLabelList=item['companyLabelList'],
)
self.session.add(obj)
try:
self.session.commit()
except Exception, e:
print e
self.session.rollback()
# self.session.close()
elif isinstance(item, CompanyItem):
# 公司信息存入mysql数据库
'''
obj=Company(
companyId=item['companyId'],
companyName=item['companyFullName']
)
self.session.add(obj)
try:
self.session.commit()
except Exception, e:
print e
self.session.rollback()
'''
# 公司的数据存入redis
self.pool.set(item['companyId'], item['companyFullName'])
def update(self, grid, data):
session = DBSession()
session.query(Person).filter(Person.grid == grid).update(data)
try:
session.commit()
except Exception as e:
log.debug("Ocorreu um erro ao salvar as configurações !\n" + e)
session.rollback()
def delete(self, grid):
session = DBSession()
session.query(Person).filter(Person.grid == grid).update({'flag': 'D'})
try:
session.commit()
log.info("Comando executado com sucesso !")
log.info(session)
return session
except Exception as e:
log.debug("Ocorreu um erro ao salvar as configurações !\n" +
e.__str__())
session.rollback()
return None
def on_set_admin_user(cls, company_id, user_id):
""" 设置公司管理用户 """
session = DBSession()
com = session.query(cls).filter(cls.company_id == company_id).first()
if com:
com.admin_user_id = user_id
try:
session.add(com)
session.commit()
except Exception as why:
session.rollback()
message = "companies.on_set_admin_user: {0}".format(why)
logging.info(message)
return -1
return 0
def on_set_business_hours(cls, company_id, opening_hours, closing_hours):
""" 设置公司营业时间 """
session = DBSession()
com = session.query(cls).filter(cls.company_id == company_id).first()
if com:
com.opening_hours = opening_hours
com.closing_hours = closing_hours
try:
session.add(com)
session.commit()
except Exception as why:
session.rollback()
message = "companies.on_set_business_hours: {0}".format(why)
logging.info(message)
return -1
return 0
def on_set_state(cls, card_id, state):
""" 设置公司状态 """
if state not in ("00", "01", "02"):
message = "bank_cards.on_set_state: wrong state: {0}".format(state)
logging.info(message)
return -1
session = DBSession()
card = session.query(cls).filter(cls.card_id == card_id).first()
if card:
card.state = state
try:
session.add(card)
session.commit()
except Exception as why:
session.rollback()
message = "bank_cards.on_set_state: {0}".format(why)
logging.info(message)
return -1
return 0
def on_add_new_company(cls, company_name, country, city, address, timezone, currency, note):
""" 添加新公司 """
session = DBSession()
com = cls()
com.company_name = company_name
com.country = country
com.city = city
com.address = address
com.timezone = timezone
com.currency = currency
com.note = note
try:
session.add(com)
session.commit()
except Exception as why:
session.rollback()
message = "companies.on_add_new_company: {0}".format(why)
logging.info(message)
com = None
return com
def routing(message):
session = DBSession()
try:
user = session.query(User).filter_by(
telegramid=message.from_user.id).first()
if not user:
user = User(telegramid=message.from_user.id)
session.add(user)
user.operation = Operation()
session.commit()
if user.operation is None:
user.operation = Operation()
session.commit()
db_connector.save_to_log(
from_who='user',
message=message) # Сохранение входящего сообщения в БД.
text = utils.text_lower_wo_command(message)
if not user.active:
bot.send_message(message.chat.id, 'Вы заблокированы.')
elif text in ('start', 'help'):
handle_start_help(message, session, user)
elif not user.registrationDone or user.operation.current_operation == opRegister:
register_flow(bot, message, session)
elif text in admin_functions.admin_commands:
#Для правильной работы необходим заполненный admin_id в config
admin_functions.admin_flow(bot, message, session)
elif text in standard.create_group or user.operation.current_operation == create_group.opGroup:
create_group.route_flow(bot, message, session)
elif text in standard.find_group or user.operation.current_operation == join_group.opGroup:
join_group.route_flow(bot, message, session)
else:
not_found(message)
session.commit()
except Exception as e:
session.rollback()
raise e
finally:
session.close()
def on_add_new_bank_card(cls, company_id, card_name, card_number,
bank_swift_code, bank_name, bank_address,
currency, amount):
""" 添加银行卡"""
session = DBSession()
card = cls()
card.company_id = company_id
card.card_name = card_name
card.card_number = card_number
card.bank_swift_code = bank_swift_code
card.bank_name = bank_name
card.bank_address = bank_address
card.currency = currency
card.amount = amount
try:
session.add(card)
session.commit()
except Exception as why:
session.rollback()
message = "bank_cards.on_add_new_bank_card: {0}".format(why)
logging.info(message)
card = None
return card
def _initdb(csvfilename, drop_db=False):
session = DBSession()
from models import Name, Addrobj
# if drop_db:
# Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
reader = csv.reader(open(csvfilename))
# next(reader, None) # skip the headers
i = 0
for line in reader:
line = [s.replace("'", "''") for s in line]
(
actstatus,
aoguid,
aoid,
aolevel,
areacode,
autocode,
centstatus,
citycode,
code,
currstatus,
enddate,
formalname,
ifnsfl,
ifnsul,
nextid,
offname,
okato,
oktmo,
operstatus,
parentguid,
placecode,
plaincode,
postalcode,
previd,
regioncode,
shortname,
startdate,
streetcode,
terrifnsfl,
terrifnsul,
updatedate,
ctarcode,
extrcode,
sextcode,
livestatus,
normdoc
) = line
try:
name = Name(
name=formalname,
name_tsvect=formalname,
name_tsquery=formalname)
ts_query1 = name.name_tsquery
session.add(name)
session.commit()
except:
session.rollback()
try:
name = Name(
name=offname,
name_tsvect=offname,
name_tsquery=offname)
ts_query2 = name.name_tsquery
session.add(name)
session.commit()
except:
session.rollback()
# Different names can be translated to one name_tsquery
# so we search name_tsquery to prevent storing of small differences
# in the DB
names = session.query(Name).filter(Name.name_tsquery.in_([
ts_query1, ts_query2
]))
names = names.all()
place = Addrobj(
actstatus=actstatus,
aoguid=aoguid,
aoid=aoid,
aolevel=aolevel,
areacode=areacode,
autocode=autocode,
centstatus=centstatus,
citycode=citycode,
code=code,
currstatus=currstatus,
formalname=formalname,
ifnsfl=ifnsfl,
ifnsul=ifnsul,
nextid=nextid,
offname=offname,
operstatus=operstatus,
parentguid=parentguid,
placecode=placecode,
plaincode=plaincode,
postalcode=postalcode,
previd=previd,
regioncode=regioncode,
shortname=shortname,
streetcode=streetcode,
terrifnsfl=terrifnsfl,
terrifnsul=terrifnsul,
ctarcode=ctarcode,
extrcode=extrcode,
sextcode=sextcode,
livestatus=livestatus,
names=names)
session.add(place)
session.commit()
i += 1
if i % 10000 == 0:
print i
class FangtianxiaRentPipeline(object):
def __init__(self):
self.session = DBSession()
def process_item(self, item, spider):
# 写入小区房价
if isinstance(item, FangtianxiaRentItem):
db_house = self.session.query(House).filter(
House.house_name == item['name']).filter(
House.city_name == item['city_name']).first()
if db_house:
if db_house.property_corp == u'暂无数据':
db_house.property_corp = item['property_corp']
if db_house.developers == u'暂无数据':
db_house.developers = item['developers']
if db_house.building_date == u'暂无数据':
db_house.building_date = item['building_date']
if db_house.building_date == u'暂无数据':
db_house.building_date = item['building_date']
# 需要添加判断, 以防同一个价格数据插入多次
p = Price(price=item['price'],
origin=item['origin'],
months=item['months'],
crawl_time=item['crawl_date'],
uid=db_house.id)
db_house.price.append(p)
else:
house = House(house_name=item['name'],
city_name=item['city_name'],
url=item['url'],
latitude=item['latitude'],
longitude=item['longitude'],
address=item['location'],
building_date=item['building_date'],
building_type=item['building_type'],
property_corp=item['property_corp'],
developers=item['developers'],
district=item['district'])
price = Price(price=item['price'],
origin=item['origin'],
months=item['months'],
crawl_time=item['crawl_date'])
house.price.append(price)
self.session.add(house)
try:
self.session.commit()
except Exception, e:
print e
self.session.rollback()
return item
# 写入小区租金
elif (isinstance(item, RentItem)):
h_id = item['h_id']
house = self.session.query(House).filter(House.id == h_id).first()
print house.id
rent = Rent(rent_price=item['rent_price'],
rent_origin=item['origin'],
publish_date=item['publish_time'],
rent_crawl_time=item['crawl_date'],
rent_type=item['rent_type'],
house_type=item['house_type'],
rent_floor=item['floor'],
rent_url=item['url'],
rent_title=item['title'],
rent_area=item['area'])
house.rent_info.append(rent)
self.session.add(house)
email=data.get('author_email'))
session.add(a)
a = Author.query.filter_by(name=data['author']).one()
r.author = a
if 'license' in data:
query = License.query.filter_by(name=data['license'])
if query.count() == 0:
l = License(name=data['license'])
session.add(l)
l = License.query.filter_by(name=data['license']).one()
r.license = l
session.add(r)
session.commit()
if __name__ == '__main__':
print "Initializing Smarmy..."
engine = create_engine('sqlite:///smarmy.db')
initialize_sql(engine)
try:
populate()
print "Complete!"
except IntegrityError, e:
print "Got an Integrity Error:", str(e)
DBSession.rollback()
def update_links(
rss_feed_url
):
"""Inserts new announcements into the database.
Notes:
* Publishing date is the date the property is listed for sale. It might be very old,
* We insert all entries in the rss feed to the database. The url field is unique so duplicates are not allowed.
* When querying new entries, keep in mind to query the date based on ( timestamp = 'today' & pubDate =
'close enough' )so only new listed properties are queried.
Parameters
----------
rss_feed_url : str
"""
feed = feedparser.parse(rss_feed_url)
entries = feed['entries']
num_new_links = 0
print('Updating liks database ..')
session = DBSession()
url_rs = session.query(Link.url)
url_list = [url for (url,) in url_rs]
session.close()
session = DBSession()
browser = logged_in_browser()
for cnt, entry in enumerate(entries):
link = entry['link']
published_str = entry['published']
print(cnt, ':', link)
if link in url_list:
print('duplicate url, passing..')
continue
published = date_parser.parse(published_str)
pubDate = datetime.fromordinal(published.toordinal())
new_link = Link(url=link, date=pubDate)
session.add(new_link)
time.sleep(random.choice(range(20, 60))/10)
try:
data = crawl_hemnet_page(new_link.url, browser=browser)
except Exception as e:
print('Error crawling hemnet page.', e.message)
continue
new_apt = Apartment(**data)
new_apt.link = new_link
session.add(new_apt)
try:
session.commit()
num_new_links += 1
except IntegrityError as e:
print(e.message)
print(link)
session.rollback()
except Exception as e:
print(e.message)
session.rollback()
finally:
session = DBSession()
print('Done!')
print('%s new links added.' % num_new_links)
add_dependencies(_new_package, session)
dep_as_package = base_query.one()
if dep_as_package not in package.dependencies:
package.dependencies.append(dep_as_package)
print "package: %s has (%i/%i) deps" % (
package.name, len(package.dependencies), len(deps))
session.flush()
def build_comps():
import subprocess
subprocess.call('git clone git://git.fedorahosted.org/comps.git',
shell=True)
subprocess.call('make comps-f16.xml', cwd='comps', shell=True)
if __name__ == '__main__':
print "Initializing LeafyMiracle..."
engine = create_engine('sqlite:///leafymiracle.db')
initialize_sql(engine)
build_comps()
try:
populate()
print "Complete!"
except IntegrityError, e:
DBSession.rollback()
