def main():

    DB_wrapper.register(HANA_wrapper)
    DB_wrapper.register(PSQL_wrapper)
    hana_db = HANA_wrapper(cursor=pdbc.connect('DSN=hana_new;UID=SYSTEM;PWD=HANA4ever;DATABASE=MIMIC2').cursor(), db_name="mimic", schema_name='MIMIC2')
    psql_db = PSQL_wrapper(cursor=pdbc.connect('DSN=psql_mimic').cursor(), db_name="MIMIC2", schema_name='mimic2v26')
    test = DB_validator(modeldb=psql_db, testdb=psql_db, failhard=False)

 def setUp(self):
     self.timeStart = datetime.datetime.now()
     self.timeBegin = time.time()
     print('%s Выполняю тест: %s' % (self.timeStart, self.id()))
     # соединиться с БД ТИ и АСП
     DB = TI
     conS = "DRIVER=FreeTDS; SERVER=%s; PORT=%s; DATABASE=%s; UID=sa; PWD=%s; TDS_Version=8.0; ClientCharset=UTF8; autocommit=True" \
            % (DB['DB_address'], DB['DB_port'], DB['DB_name'], DB['DB_password'])
     try:
         self.conTI = pypyodbc.connect(conS)
         self.curTI = self.conTI.cursor()
     except:
         print("Возникла ошибка при соединении с БД ТИ, строка соединения %s" % conS)
         exit(1)
     # соединиться с АСП
     DB = ASP
     conS = "DRIVER=FreeTDS; SERVER=%s; PORT=%s; DATABASE=%s; UID=sa; PWD=%s; TDS_Version=8.0; ClientCharset=UTF8; autocommit=True" % (
         DB['DB_address'], DB['DB_port'], DB['DB_name'], DB['DB_password'])
     try:
         self.conASP = pypyodbc.connect(conS)
         self.curASP = self.conASP.cursor()
     except:
         print("Возникла ошибка при соединении с БД АСП строка соединения %s" % conS)
         exit(1)
     # создать вебдрайвер
     self.base_url = addr
     self.driver = webdriver.Firefox()
     self.driver.implicitly_wait(30)
     # зайти в АСП
     self.driver.get(self.base_url + 'Login.aspx')
     self.verificationErrors = []
     self.accept_next_alert = True
     self.goMainASP()

 def get_db_conn(self):
     db = self.db
     plat_str = platform.system()
     if plat_str == "Windows":
         connection_string ='Driver={SQL Server Native Client 10.0};Server=%s;Database=%s;Uid=%s;Pwd=%s;PORT=%s;' % (self.db["HOST"], self.db["NAME"], self.db["USER"], self.db["PASSWORD"], self.db["PORT"])
         self.db_conn = pypyodbc.connect(connection_string)
     else:
         connection_string ='DSN=MySQLServerDatabase;Server=%s;Database=%s;Uid=%s;Pwd=%s;PORT=%s;' % (self.db["HOST"], self.db["NAME"], self.db["USER"], self.db["PASSWORD"], self.db["PORT"])
         self.db_conn = pypyodbc.connect(connection_string)
     return self.db_conn

 def __init__(self, db, FromParameter=None):
     if self.parameters is None:
         self.parameters = dict()
     if isinstance(db, DbEngine):
         self.connection = pypyodbc.connect(db.connection.connectString)
         for key, parameter in db.parameters.items():
             if key in self.parameters:
                 self.parameters[key].parameters = parameter.parameters
     else:
         self.connection = pypyodbc.connect(db)
     self.cursor = self.connection.cursor()
     self.state = random()
     self.checkstate = random()
     self.refresh()

def test_connection ( odbc_dsn, table_name, table_create_file, debug=gDebug ):
	try:
		db_con = pypyodbc.connect( 'DSN=%s' % odbc_dsn )
		db_cur = db_con.cursor()
	except Exception as e:
		writelog( "***ERROR: unable to connect to DSN=%s: %s" % ( odbc_dsn, e ), True )
		sys.exit(2)
	None
	#time.sleep(0.1)
	#else:
	#	if debug: print "\t odbc connection for %s success" % odbc_dsn
	test_query = '''SELECT * FROM {table_name} LIMIT 1'''
	test_query = test_query.format( table_name = table_name )	
	if debug: print "\t%s" % test_query
	try:
		db_cur.execute( test_query )
	except Exception as e:
		writelog( "***ERROR: test_query failed" )
		if table_create_file:
			try:
				writelog( "***Trying to CREATE TABLE %s.%s" % ( odbc_dsn, table_name ) )
				create_table( db_con, db_cur, table_create_file, debug )
				db_cur.execute( test_query )	#retry test just in case
			except Exception as err:
				writelog( "***ERROR: unable to create table %s.%s %s" % ( odbc_dsn, table_name, err), True )
				sys.exit(2)
		else:
			writelog( "\tNo table_create_file given, exiting: %s" % e, True )
			sys.exit(2)
	db_con.close()
	writelog( "\t%s.%s READY" % ( odbc_dsn, table_name ) )

 def __init__(self):
     self.con_str = 'Driver={SQL Server};' \
                    'Server=192.168.11.9 ;' \
                    'Database=i_collect;' \
                    'Uid=sa;Pwd=12121212;'
     self.con = p.connect(self.con_str, autocommit=True)
     self.cur = self.con.cursor()

def getIndexData(indexName,rate):
	riseOrDown=[]
	afterChange=[]
	theData=[]
	try:
		connect = pypyodbc.connect('driver=freetds;server=;port=1433;uid=;pwd=;database=record_database_daily;timeout=2')
		cur = connect.cursor()
		for inf in cur.execute("select riseOrDown,dateT from %s"%(indexName)):
			theData.append(inf)      
	except pypyodbc.Error:
		print ("Database link error")
	finally:
		cur.close()
		connect.close()
	for everyrow in theData:
		riseOrDown.append(everyrow[0])
	stderror=numpy.std(riseOrDown)*rate
	for everyrow in theData:
		if abs(everyrow[0])>=stderror:
			if everyrow[0]>0:
				afterChange.append((everyrow[1],0))#up
			else:
				afterChange.append((everyrow[1],2))#down
		else:
			afterChange.append((everyrow[1],1))#unchange
	return afterChange

 def __init__(self, resrcID, parameter):
     DataSource.__init__(self, resrcID, parameter)
     
     connStr = self.getConnStr()
     conn = pypyodbc.connect(connStr)
     self.cur = conn.cursor()
     return

 def connectdb(self):
     pypyodbc.lowercase = False
     conn = pypyodbc.connect(
         r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
         r"Dbq=Database21.accdb;")
     cur = conn.cursor()
     return cur

    def get_statistic_tagevents(self, user_id=0):
        """
        Takes user_id as parameter
        Get all tagevents used for the statistic page

        :param user_id: User id
        :type user_id: integer
        :return: Array of Statistic tagevents
        """
        connection_string = self.get_connection_string()
        try:
            my_connection = pypyodbc.connect(connection_string)
            cursor = my_connection.cursor()

            cursor.execute("{call GetStatisticTagevents('" + str(user_id) + "')}")
            value = cursor.fetchall()

            cursor.close()
            my_connection.close()

            return_array = []
            # [:-8] is to remove unnecessary decimals
            for statistics_tagevent in value:
                return_array.append(statistic_tagevent.SQLStatisticTagevent(statistics_tagevent[0],
                                                                            statistics_tagevent[1][:-8],
                                                                            statistics_tagevent[2],
                                                                            statistics_tagevent[3]))
            return return_array

        except pypyodbc.DatabaseError as error:
            print(error.value)

def add():
    if request.method == 'GET':
        try:
            return render_template("Add.html");
        except:
            return "<h2>Error in the get</h2>"
    elif request.method == 'POST':
        try:
            myConnection = pypyodbc.connect('Driver={SQL Server};'
                                    'Server=SIMON-HP\SQLEXPRESS;'
                                    'Database=AlkimiiAdmin;'
                                    'uid=sa;pwd=12345')
            myCursor = myConnection.cursor()

            name = request.form['AddName'];
            email = request.form['AddEmail'];
            password = request.form['AddPassword'];

            SQLCommand = ("INSERT INTO Users "
                        "(Name, Email, Pword) "
                        "VALUES (?,?,?)")
            values = [name, email, password]

            myCursor.execute(SQLCommand,values)
            myConnection.commit();
            myConnection.close();
            return redirect(url_for('display'));
        except:
            return "<h2>Error Occurred</h2>"

def edit():
    if request.method == 'GET':
        return render_template("Edit.html");
    elif request.method == 'POST':
        try:
            myConnection = pypyodbc.connect('Driver={SQL Server};'
                                'Server=SIMON-HP\SQLEXPRESS;'
                                'Database=AlkimiiAdmin;'
                                'uid=sa;pwd=12345')
            myCursor = myConnection.cursor()
            Name = request.form['EditName'];
            Email = request.form['EditEmail'];
            Password = request.form['EditPassword'];
            EditId = request.form['EditId'];

            SQLCommand = ("UPDATE Users "
                      "SET Name = '" + Name +
                      "', Email = '" + Email +
                      "', Pword = '" + Password +
                      "' WHERE UsererId = "
                      + EditId)

            myCursor.execute(SQLCommand)
            myConnection.commit();
            myConnection.close();
            return redirect(url_for('display'));
        except:
            return "<h2>Error Occurred</h2>"

 def __init__(self):
     __connectionString = (
         "Driver={SQL Server Native Client 11.0};Server=localhost;Database=QuizDB;uid=sa;[email protected]"
     )
     self.connection = pypyodbc.connect(__connectionString)
     self.cursor = self.connection.cursor()
     return

    def update_tenant_general_information(self, tenant):
        """
        Takes Tenant representation as parameter.
        Update the Tenants general information

        :param tenant: A Tenant representation
        :type tenant: Tenant class
        :return: True
        """
        connection_string = self.get_connection_string()
        try:
            my_connection = pypyodbc.connect(connection_string)
            cursor = my_connection.cursor()

            cursor.execute("{call UpdateGymInfo('" + str(tenant['id']) + "','" + tenant['password'] + "','" +
                           tenant['gym_name'] + "','" + tenant['address'] + "','" + tenant['phone'] + "','" +
                           tenant['zip_code'] + "','" + tenant['city'] + "','" + tenant['email'] + "')}")

            cursor.commit()
            cursor.close()
            my_connection.close()
            return True

        except pypyodbc.DatabaseError as error:
            print(error.value)

    def update_tenant_information(self, tenant):
        """
        Takes Tenant representation as parameter.
        Update the Tenants information

        :param tenant: A Tenant representation
        :type tenant: Tenant class
        :return: True
        """
        connection_string = self.get_connection_string()
        try:
            my_connection = pypyodbc.connect(connection_string)
            cursor = my_connection.cursor()
            # If the Tenant don't want to update password, don't send the password.
            if tenant['new_password'] is '':
                cursor.execute("{call UpdateTenant('" + str(tenant['id']) + "','" + tenant['password'] + "','" +
                               tenant['active_fortnox'] + "','" + tenant['image'] + "','" +
                               tenant['background_color'] + "')}")
            else:
                cursor.execute("{call UpdateTenant('" + str(tenant['id']) + "','" + tenant['password'] + "','" +
                               tenant['active_fortnox'] + "','" + tenant['image'] + "','" + tenant['background_color']
                               + "','" + tenant['new_password'] + "')}")

            cursor.commit()
            cursor.close()
            my_connection.close()
            return True

        except pypyodbc.DatabaseError as error:
            print(error.value)

def sqlconnect(driver=driver, server=server, database=database, uid=uid, password=password):
    ### This establishes the connection to the SQL Server using an ODBC connection ###
    global conn
    global cursor
    conn = pypyodbc.connect(driver=driver, server=server, database=database, uid=uid, pwd=password)
    cursor = conn.cursor()
    return cursor

def loadglobalvars():
    global driver
    global server
    global database
    global uid
    global password
    global customerID
    global conn
    global cursor
    global encodedlp
    global encodedlpshort
    global lastreviewedapplicantworkflow
    global latestapplication

    driver = '{ODBC Driver 11 for SQL Server}'   #formerly {SQL Server}  {SQL Server Native Client 11.0}
    server = '382CFS1\SSESNEXGEN'     #formerly 382CFS1\SSESNEXGEN
    database = 'iCIMS'
    uid = 'Zac'
    password = 'ThisIsASecurePassword1234'
    customerID = '3786'
    #print(driver, server, database, uid, password)
    conn = pypyodbc.connect(driver=driver, server=server, database=database, uid=uid, pwd=password)
    cursor = conn.cursor()
    encodedlp = str(base64.standard_b64encode(b'SoteraAPI:Tester123'))
    encodedlpshort = re.findall("b'(.*)'", encodedlp)
    encodedlpshort = str(encodedlpshort[0])
    lastreviewedapplicantworkflow = ''
    latestapplication = []

    logging.basicConfig(filename='debug.log',level=logging.DEBUG)

def unlockhrs():
    cnxn = pyodbc.connect('Trusted_Connection=yes;DRIVER={SQL Server};SERVER=blabla;DATABASE=xxx;UID=domain1\xxx;PWD=xxx')
    username = e
    cursor = cnxn.cursor()
    cursor.execute("update tbl_UserActivity set UserBlock = 0 where UserName = %s, username")
    cursor.commit()
    cursor.execute("select * from tbl_UserActivity where UserName = %s, username")

 def _get_connection(self):
     if self._connection:
         return self._connection
     connection_string = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s' % \
                         self.rutamdb
     self._connection = pypyodbc.connect(connection_string)
     return self._connection

def collect_pairs(outfile):
    with open(outfile,'w', newline='') as f:
        csv_writer = csv.writer(f, delimiter=',')
        wrow = ('a_station', 'b_station', 'distance')
        csv_writer.writerow(wrow)
        with pypyodbc.connect("DRIVER={SQL Server};SERVER=miranda;DATABASE=bikeshare;Trusted_Connection=true") as conx:
            curr = conx.cursor()
            retrieved_data = curr.execute("""
                        select a.stationid A_station, b.stationid B_station,
                               a.lat a_lat, a.long a_long,
                               b.lat b_lat, b.long b_long
                        from stations a
                        cross join stations b
                        where a.stationid < b.stationid
                    """)
            for row in retrieved_data:
                a_lat = row['a_lat']
                a_long = row['a_long']
                b_lat = row['b_lat']
                b_long = row['b_long']
                a_station = row['a_station']
                b_station = row['b_station']
                a_pt = (a_lat, a_long)
                b_pt = (b_lat, b_long)
                distance = vincenty(a_pt, b_pt).miles
                wrow = (a_station, b_station, distance)
                csv_writer.writerow(wrow)