ошибки:
ivan$ python main.py
[I 151215 20:37:48 connectionpool:203] Starting new HTTP connection (1): ***.lfstrm.tv
Traceback (most recent call last):
File "main.py", line 30, in
send_offers()
File "/Users/ivan/lanbilling_fetcher/lanbilling_fetcher/fetcher/offers.py", line 78, in send_offers
user_id = fetch_user(email, data.get('login'))
File "/Users/ivan/lanbilling_fetcher/lanbilling_fetcher/fetcher/users.py", line 41, in fetch_user
r = requests.post(url=LIFE_STREAM_USER_API, json={'email': email, 'username': username})
File "/Library/Python/2.7/site-packages/requests/api.py", line 108, in post
return request('post', url, data=data, json=json, **kwargs)
File "/Library/Python/2.7/site-packages/requests/api.py", line 50, in request
response = session.request(method=method, url=url, **kwargs)
File "/Library/Python/2.7/site-packages/requests/sessions.py", line 464, in request
resp = self.send(prep, **send_kwargs)
File "/Library/Python/2.7/site-packages/requests/sessions.py", line 576, in send
r = adapter.send(request, **kwargs)
File "/Library/Python/2.7/site-packages/requests/adapters.py", line 415, in send
raise ConnectionError(err, request=request)
requests.exceptions.ConnectionError: ('Connection aborted.', error(61, 'Connection refused'))
сам код:
main.py
# coding: utf-8
import sys
import logging
from tornado.options import define, options
from fetcher.offers import send_offers
from fetcher.users import fetch_user, CreateUserException
from web.server import runserver
define('email', default=None, help='New user email')
define('username', default=None, help='New user username')
define('runserver', default=None, help='Run tornado server')
options.parse_command_line()
logging.basicConfig(filename='fetcher.log')
if __name__ == '__main__':
if options.email is not None:
try:
fetch_user(options.email, options.username)
except CreateUserException as e:
logging.error(e.message)
sys.exit(1)
sys.exit(0)
elif options.runserver:
runserver()
else:
send_offers()
_init_py
# coding: utf-8
import pymysql
LIFE_STREAM_USER_API = 'http://***.lfstrm.tv:9191/v1/user'
LIFE_STREAM_SUBSCRIPTION_API = 'http://***.lfstrm.tv:9191/v1/%s/subscriptions'
billing_db_config = {
'host': 'localhost', # your host, usually localhost
'user': 'billing', # your username
'passwd': 'billing', # your password
'db': 'test2',
'charset': 'utf8'
}
fetcher_db_config = {
'host': 'localhost',
'user': 'billing',
'passwd': 'billing',
'db': 'test2',
'charset': 'utf8'
}
billing_db = pymysql.connect(**billing_db_config)
fetcher_db = pymysql.connect(**fetcher_db_config)
subscriptions_ids = ('102', '109', '108')
cur = fetcher_db.cursor()
cur.execute("SHOW TABLES LIKE 'users'")
if cur.fetchone() is None:
cur.execute('''
CREATE TABLE users
(
email VARCHAR(255) NOT NULL UNIQUE,
lfstream_id VARCHAR(32),
message VARCHAR(255),
offers TEXT
);
''')
cur.close()
offers.py
# coding: utf-8
import collections
import logging
import requests
from fetcher import billing_db, subscriptions_ids, LIFE_STREAM_SUBSCRIPTION_API, \
fetcher_db
from fetcher.sql import SELECT_SUBSCRIPTIONS_BY_USERS, SELECT_SUBSCRIPTIONS, \
SELECT_SUBSCRIPTIONS_BY_USER
from fetcher.users import fetch_user, CreateUserException
Record = collections.namedtuple(
'Record', field_names=[
'account_id', 'login', 'email', 'service_id']
)
def fetch_offers(ids=None):
cur = billing_db.cursor()
if ids:
ids = ', '.join(ids)
cur.execute(SELECT_SUBSCRIPTIONS_BY_USERS, (subscriptions_ids, ids))
else:
cur.execute(SELECT_SUBSCRIPTIONS, (subscriptions_ids, ))
result = {}
for row in map(Record._make, cur.fetchall()):
user_offers = result.get(row.email,
{'offers': set(), 'login': row.login})
user_offers['offers'].add(str(row.service_id))
user_offers['account'] = {
'id': row.account_id,
}
result[row.email] = user_offers
return result
def fetch_offers_by_user_id(id):
cur = billing_db.cursor()
cur.execute(SELECT_SUBSCRIPTIONS_BY_USER, (subscriptions_ids, id))
result = []
for row in map(Record._make, cur.fetchall()):
result.append(row.service_id)
return result
def get_offers(user_id):
uri = LIFE_STREAM_SUBSCRIPTION_API % user_id
response = requests.get(uri)
if response.status_code != 200:
logging.error('Can\'t get offer for user %s' % str(user_id))
return None
return [s['id'] for s in response.json()]
def post_offers(user_id, offers):
uri = LIFE_STREAM_SUBSCRIPTION_API % user_id
for id in offers:
response = requests.post(uri, json={'id': str(id), 'valid': True})
if response.status_code != 200:
logging.error('Can\'t add offer %s' % str(id))
def delete_offers(user_id, offers):
uri = LIFE_STREAM_SUBSCRIPTION_API % user_id
for id in offers:
response = requests.post(uri, json={'id': str(id), 'valid': False})
if response.status_code != 200:
logging.error('Can\'t delete offer %s' % str(id))
def send_offers():
cur = fetcher_db.cursor()
utm_offers = fetch_offers()
for email, data in utm_offers.items():
try:
user_id = fetch_user(email, data.get('login'))
except CreateUserException as e:
logging.error('Create user error: %s' % e.message)
continue
post_offers(user_id, data['offers'])
offers = get_offers(user_id)
if offers is not None:
to_delete = list(set(offers).difference(data['offers']))
if to_delete:
delete_offers(user_id, to_delete)
if utm_offers.keys():
query = 'SELECT lfstream_id FROM users WHERE email NOT IN %s'
cur.execute(query, [utm_offers.keys()])
users_without_subscriptions = cur.fetchall()
else:
query = 'SELECT lfstream_id FROM users'
cur.execute(query)
users_without_subscriptions = cur.fetchall()
for u in users_without_subscriptions:
user_id = u[0]
offers = get_offers(user_id)
if offers is not None:
delete_offers(user_id, offers)
cur.close()
users.py
# coding: utf-8
import requests
from fetcher import fetcher_db, LIFE_STREAM_USER_API, billing_db
class CreateUserException(Exception):
pass
insert_error = '''INSERT INTO users (email, message) VALUES (%s, %s)'''
select_user = '''SELECT lfstream_id FROM users WHERE email = %s AND message IS NULL'''
insert_user = '''INSERT INTO users (email, lfstream_id) VALUES (%s, %s)'''
select_user_by_lfstrm_id = '''SELECT email FROM users WHERE lfstream_id = %s '''
select_utm_user_by_email = '''SELECT id FROM users WHERE email = %s '''
def fetch_user_by_lfstrm_id(id):
fetcher_cur = fetcher_db.cursor()
fetcher_cur.execute(select_user_by_lfstrm_id, [id])
user = fetcher_cur.fetchone()
fetcher_cur.close()
if user is None:
return None
utm_cursor = billing_db.cursor()
utm_cursor.execute(select_utm_user_by_email, [user[0]])
user = utm_cursor.fetchone()
utm_cursor.close()
return user[0]
def fetch_user(email, username=None):
cur = fetcher_db.cursor()
cur.execute(select_user, [email])
user = cur.fetchone()
if user is not None:
return user[0]
r = requests.post(url=LIFE_STREAM_USER_API, json={'email': email, 'username': username})
user_resp = r.json()
if 'error' in user_resp:
cur.execute(insert_error, (email, user_resp['error']))
fetcher_db.commit()
cur.close()
raise CreateUserException(user_resp['error'])
cur.execute(insert_user, (email, [user_resp['id']]))
fetcher_db.commit()
cur.close()
return user_resp['id']
sql.py
# coding: utf-8
SELECT_SUBSCRIPTIONS = u'''
SELECT a.uid AS account_id,
a.login AS login,
a.email AS email,
c.uuid AS service_id
FROM
categories c,
usbox_services u,
vgroups v,
accounts a
WHERE
c.cat_idx = u.cat_idx
AND v.vg_id = u.vg_id
AND a.uid = v.uid
AND u.timefrom < u.timeto
AND u.timeto > now()
AND v.archive = 0
AND a.email != ''
AND a.email IS NOT NULL
AND v.blocked = 0
AND c.uuid in %s
'''
SELECT_SUBSCRIPTIONS_BY_USERS = u'''
SELECT a.uid AS account_id,
a.login AS login,
a.email AS email,
c.uuid AS service_id
FROM
categories c,
usbox_services u,
vgroups v,
accounts a
WHERE
c.cat_idx = u.cat_idx
AND v.vg_id = u.vg_id
AND a.uid = v.uid
AND u.timefrom < u.timeto
AND u.timeto > now()
AND v.archive = 0
AND a.email != ''
AND a.email IS NOT NULL
AND v.blocked = 0
AND c.uuid in %s
AND a.uid IN %s
'''
SELECT_SUBSCRIPTIONS_BY_USER = u'''
SELECT a.uid AS account_id,
a.login AS login,
a.email AS email,
c.uuid AS service_id
FROM
categories c,
usbox_services u,
vgroups v,
accounts a
WHERE
c.cat_idx = u.cat_idx
AND v.vg_id = u.vg_id
AND a.uid = v.uid
AND u.timefrom < u.timeto
AND u.timeto > now()
AND v.archive = 0
AND a.email != ''
AND a.email IS NOT NULL
AND v.blocked = 0
AND c.uuid in %s
AND a.uid = %d
'''
Простой
Средний