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()