def query_with_delete():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
# cursor.execute("SELECT *,from_unixtime(clock) FROM zabbix.history WHERE clock < (UNIX_TIMESTAMP(\"2016-12-31 00:00:01\")) AND itemid IN (SELECT itemid FROM zabbix.items WHERE hostid=10134 OR hostid=10132 OR hostid=10097 OR hostid=10094 OR hostid=10122 OR hostid=10140 OR hostid=10147 OR hostid=10127 OR hostid=10121 OR hostid=10128 OR hostid=10119 OR hostid=10135 OR hostid=10144 OR hostid=10130 OR hostid=10131) LIMIT 10;")
cursor.execute(
"DELETE FROM zabbix.history WHERE clock < (UNIX_TIMESTAMP(\"2016-12-31 00:00:01\")) LIMIT 10000;"
)
conn.commit()
# all = {}
# a = []
# for row in iter_row(cursor, 10):
# print(row)
print('--DELETE--')
print(str(datetime.now()))
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
# return a
if __name__ == '__main__':
query_with_delete()
def update_with_user(uid): # uid = userUID
'''
Обновить статус в мониторинге
0 - Не гость, 1 - Гость
'''
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute('UPDATE dv_calls SET guest=1 WHERE uid={uid};'.format(uid=str(uid)))
all = {}
for row in iter_row(cursor, 10):
all = row
print(all)
return all
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
if __name__ == '__main__':
update_with_user(uid)
def query_with_users():
'''
Выборка онлайн пользователей с депозитом ниже снятия за день,
но больше депозита чем установленный кредит
и с условием что не гостевой доступ.
=================================================================================================
| UID | Тариф ID | Статус | NG | NAS_ID | IP,Депозит | Оплата в день | Кредит | гостевой доступ |
=================================================================================================
MySQL query;
====================================================================================================
SELECT d.uid, d.tp_id, d.status, d.connect_info, d.nas_id, d.framed_ip_address, b.deposit, t.day_fee, u.credit, d.guest
FROM dv_calls d
LEFT JOIN bills b ON (d.uid = b.uid)
LEFT JOIN tarif_plans t ON (d.tp_id = t.id)
LEFT JOIN users u ON (d.uid=u.uid)
WHERE (b.deposit < t.day_fee) AND ((-u.credit) > b.deposit) AND d.guest=0
LIMIT 5000
====================================================================================================
'''
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT d.uid, d.tp_id, d.status, d.connect_info, d.nas_id, d.framed_ip_address, b.deposit, t.day_fee, u.credit, d.guest\
FROM dv_calls d\
LEFT JOIN bills b ON (d.uid = b.uid)\
LEFT JOIN tarif_plans t ON (d.tp_id = t.id)\
LEFT JOIN users u ON (d.uid=u.uid)\
WHERE (b.deposit < t.day_fee) AND ((-u.credit) > b.deposit) AND d.guest=0\
LIMIT 5000;")
all = {}
a = []
for row in iter_row(cursor, 10):
UidDepositDict = dict(zip(['uid','tp_id','status','connect_info','nas_id','framed_ip_address', 'deposit', 'day_fee', 'credit', 'guest'], row))
all = a.append(UidDepositDict)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return a
if __name__ == '__main__':
query_with_users()
def connect():
""" Connect to MySQL database """
db_config = read_db_config()
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def query_with_bills():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT b.uid, b.deposit FROM bills b where b.deposit < t.day_fee LIMIT 1000;")
all = {}
a = []
for row in iter_row(cursor, 10):
print(row)
UidDepositDict = dict(zip(['uid', 'deposit'], row))
all = a.append(UidDepositDict)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return a
if __name__ == '__main__':
query_with_bills()