import pickle
import time
import ftplib
import MySQLdb
import sys
from sys import argv
import os
import urllib
import subprocess
from ftplib import FTP
from time import sleep
import requests
from rets import Session

db2 = MySQLdb.connect(host="127.0.0.1",    # your host, usually localhost
                      charset='utf8',
                     user="",         # your username
                     passwd="",  # your password
                     db="")        # name of the data base

login_url = ''
username = ''
password = ''
rets_client = Session(login_url, username, password)
rets_client.login()
system_data = rets_client.get_system_metadata()
system_data
resources = rets_client.get_resource_metadata(resource='Agent')
resources
cur2 = db2.cursor()
#cur2.execute('select max(uid) from media;')
#maxuid = cur2.fetchall()
#maxdate = cur2.execute('select CONVERT_TZ(max(modified), @@session.time_zone, '+00:00') from media;')
#date_str = cur2.fetchone()
#created_date = datetime.strptime(date_created, '%Y-%m-%d %H:%M:%S')
#print (maxdate)
search_results = rets_client.search(resource='Property', resource_class='RES', dmql_query='(ModificationTimestamp=2019-02-18T10:16:46+)')  #change search query here

cur2.execute('SET NAMES utf8;')
cur2.execute('SET CHARACTER SET utf8;')
cur2.execute('SET character_set_connection=utf8;')

chunk_size = 40000
chunks = []
chunk_size = max(1, chunk_size)
for i in range(0, len(search_results), chunk_size):
    chunk = search_results[i:i+chunk_size]
    chunks.append(chunk)

for chunk in chunks:

    placeholders = ', '.join(['%s'] * len(chunk[0]))
    columns = "`%s`" % '`,`'.join(chunk[0].keys())

    sql = "INSERT IGNORE INTO houses ( %s ) VALUES ( %s ) ON DUPLICATE KEY UPDATE UID=VALUES(UID)" % (columns, placeholders) #change table here

    chunk_values = []
    for result in chunk:
        chunk_values.append(tuple(result.values()))

    success = False

    try:
        cur2.executemany(sql, chunk_values)
        db2.commit()
        print ("Records inserted")
        success = True
    except:
        db2.rollback()

    if not success:
        for result in chunk:
            placeholders = ', '.join(['%s'] * len(result))
            columns = "`%s`" % '`,`'.join(result.keys())
            sql = "INSERT INTO houses ( %s ) VALUES ( %s ) ON DUPLICATE KEY UPDATE UID=VALUES(UID)" % (columns, placeholders) #change table here
            print (sql)
            cur2.execute(sql, result.values())
            print ("Record inserted")
            db2.commit()