def createConn(self,profile):
     conn = None
     server_type = profile["servertype"]
     if server_type == "oracle":
         import cx_Oracle
         codepage = sys.getdefaultencoding()
         if codepage  ==  "gbk" : os.environ['NLS_LANG']="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
         dns_tns = cx_Oracle.makedsn(profile["host"],1521,profile["sid"])
         conn = cx_Oracle.connect(profile["user"], profile["password"], dns_tns)
     elif server_type == "mssql":
         import pyodbc
         if profile.get("database") != None:
             conn = pyodbc.connect(driver = '{SQL Server}',server=profile["host"],\
                     database=profile["database"], uid=profile["user"],  pwd = profile["password"] )
         else :
             conn = pyodbc.connect(driver = '{SQL Server}',server=profile["host"],\
                     uid=profile["user"],  pwd = profile["password"] )
     elif server_type == "mysql":
         import MySQLdb
         if profile.get("database") != None:
             conn = MySQLdb.connect (host = profile["host"] , user = profile["user"],\
                 db=profile["database"], passwd = profile["password"], charset = "utf8", use_unicode = True )
         else :
             conn = MySQLdb.connect (host = profile["host"] , user = profile["user"],\
                 passwd = profile["password"], charset = "utf8", use_unicode = True )
     elif server_type == "sqlite":
         import sqlite3 as sqlite
         conn = sqlite.connect(profile["file"])
     return conn

    def open(self) -> bool:
        if not self.check_for_dsn(self.dsn):
            return False

        self.logger.info("Attempting to connect to the dsn '%s'." % s)
        if self.integrated == 'Y':
            try:
                self.connection = pyodbc.connect(
                    'DSN=%s;Trusted_Connection=yes' % self.dsn
                    , timeout=5  # allow 5 seconds to connect
                )
                self.logger.info(
                    'connection to ' + self.dsn + ' opened at ' + timestr())
                return True
            except Exception as e:
                self.logger.error('Unable to connect to ' + self.dsn + '.')
                self.logger.error(str(e))
                self.status = str(e)
                return False
        else:
            # self.password = getpass.getpass(
            # 'Please enter your password for %s: \n' % self.dsn)
            try:
                self.connection = pyodbc.connect(
                    'DSN=%s;PWD=%s' % (self.dsn, self.password)
                    , timeout=5  # allow 5 seconds to connect
                )
                self.logger.info(
                    'connection to ' + self.dsn + ' opened at ' + timestr())
                return True
            except Exception as e:
                self.logger.error('Unable to connect to ' + self.dsn + '.')
                self.logger.error(str(e))
                self.status = str(e)
                return False

    def __init__(self, ms_con_str, pgres_con_str, log_file = None, 
        commit_freq = 1000):
        """initializer

        arguments:
        ms_con_str -- string to connect to SQL Server database
        pgres_con_str -- string to connect to PostgreSQL database
        log_file -- path of file to log transactions. Use None to turn off
           logging.
        commit_freq -- number of rows to move before committing and logging
            each transaction
        
        """      

        self.__cxn_ms = pyodbc.connect(ms_con_str)
        self.__cxn_pgres = pyodbc.connect(pgres_con_str)
        if log_file is not None:
            self.__log_file = open(log_file, 'a')
            self.__log_is_file = True
        self.__commit_freq = commit_freq
        self.__log('Connection initialized:')
        self.__log('SQL Server Connection String: {}'.format(ms_con_str))
        self.__log('PostgreSQL Connection String: {}'.format(pgres_con_str))
        self.__log('')
        self.__log('Commit Frequency: {}'.format(commit_freq))
        self.__log('')

    def get_new_connection(self, conn_params):
        """Opens a connection to the database."""
        conn_params = self.get_connection_params()
        autocommit = conn_params.get('autocommit', True)
        if 'connection_string' in conn_params:
            return Database.connect(conn_params, autocommit=autocommit)
        conn = []
        if 'dsn' in conn_params:
            conn.append('DSN=%s' % conn_params['dsn'])
        else:
            driver = conn_params['driver']
            if os.path.isabs(driver):
                conn.append('DRIVER=%s' % driver)
            else:
                conn.append('DRIVER={%s}' % driver)

        conn.append('SERVER=%s' % conn_params['host'])
        if 'port'in conn_params:
            conn.append('PORT=%s' % conn_params['port'])
        if conn_params['database']:
            conn.append('DATABASE=%s' % conn_params['database'])
        if conn_params['user']:
            conn.append('UID=%s;PWD=%s' % (conn_params['user'], conn_params['password']))
        else:
            conn.append('Integrated Security=SSPI')
        return Database.connect(';'.join(conn), autocommit=autocommit, unicode_results=True)

def return_count(mType,mat,rad,podl):

    if(mType=="pz"): 
        mirrorType="пьезо"
        radius="Is Null"
    
    if(mType=="p"): 
        mirrorType="плоское"
        radius="Is Null"
    
    if(mType=="s"): 
        mirrorType="сфера"
        radius=u"= "+rad

    if(mat=="z"): material="='z'"
    if(mat=="si"): material="Is Null"
        
    #db = pyodbc.connect('DSN=test',connect_args={'convert_unicode': True})
    

    if(podl==0):
        db = pyodbc.connect('DSN=zerki_current')
        sqlQuery=u"SELECT Count(Zerki.[нПодложки]) AS count FROM Zerki WHERE (((Zerki.[Вкомп])='свободно') AND ((Zerki.[МесХран]) Is Not Null) AND ((Zerki.[Ячейка]) Is Not Null) AND ((Zerki.[Тзер])='"+mirrorType+"') AND ((Zerki.[Материал подл]) "+material+") AND ((Zerki.[ГОтказ])='годен') AND ((Zerki.[Рсферы]) "+radius+"));"
    else:
        db = pyodbc.connect('DSN=podl_local')
        sqlQuery=u"SELECT count(PODL.[нПодл]) AS count FROM PODL WHERE ((Not (PODL.[Завод])='оптик пента') AND ((PODL.[Воз])='свободно') AND ((PODL.[Тзер])='"+mirrorType+"') AND ((PODL.[№кор]) Is Not Null) AND ((PODL.[Место]) Is Not Null) AND ((PODL.[Материал]) "+material+") AND ((PODL.[РсферыНом])"+radius+"));"
    #print(sqlQuery)
    cursor=db.cursor()
    cursor.execute(sqlQuery)
   
    data=cursor.fetchall()
    return data[0][0]

def importSEMSchedules(xlFile,DBFile):
    conn=pyodbc.connect('Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; Dbq='+xlFile, autocommit=True)
    cursor = conn.cursor()        
    xlsStore = []
    for row in cursor.execute('SELECT * FROM [Sheet1$]'):
        print (row.Description)
        xlsStore.append([row.Schedule,row.Description,row[2],row.FOLLOWS])
       
    cursor.close()
    conn.close()
    
    #Access
    conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+DBfile)
    cursor = conn.cursor()
    try:
        SQL = "DROP TABLE [SEM Schedules-Import]"
        cursor.execute(SQL)
        conn.commit()
    except: print('INFORMATION: All Master-Import table did not exist. As expected, attempt to delete it failed'+'\n')
    cursor.execute("""
    CREATE TABLE [SEM Schedules-Import] (id COUNTER CONSTRAINT C1 PRIMARY KEY, Schedule MEMO, description MEMO, RunsOn MEMO, FOLLOWS MEMO);
    """)
    conn.commit()
    #Create table from Excel in Access
    for row in xlsStore:
        cursor.execute("""
        INSERT INTO [SEM Schedules-Import] (Schedule,Description, RunsOn,FOLLOWS) 
        VALUES (?,?,?,?)
        """, [row[0],row[1],row[2],row[3]])    
    conn.commit()
    cursor.close()
    conn.close()        

	def __init__(self, server='rotr5ws1', database='omMeas', timeout=120, user=None, debug=False):
		if re.search('ELF', arch):			# Linux
			self.executemany_symbol = "%s"
			if user == None:		# assume pre-setup connection in ODBC settings
				self.db = MySQLdb.connect(host=server, user='scott', passwd='tiger', db=database, connect_timeout=timeout)
			else:
				password = getpass.getpass()
				self.db = MySQLdb.connect(host=server, user=user, passwd=password, db=database, connect_timeout=timeout)
		else:								# assume Windows
			self.executemany_symbol = "?"
			try:
				if user == None:		# assume pre-setup connection in ODBC settings
					self.db = pyodbc.connect("server="+server+";dsn="+database+";timeout="+str(timeout))
				else:
					password = getpass.getpass()
					self.db = pyodbc.connect("server="+server+";dsn="+database+";timeout="+str(timeout)+";uid="+user+";pwd="+password)
			except pyodbc.Error, e:
				print traceback.print_exc()
				if e.args[0] == 'IM002':		# no driver error
					print \
					"""
==========================================================================================
ERROR: ODBC Driver NOT FOUND. Please Install MySQL ODBC Connector to your local Computer.
==========================================================================================
					"""
				raise e

def main():
    # JYDB db
    cnxn_jydb = pyodbc.connect("""
        DRIVER={SQL Server};
        SERVER=172.16.7.229;
        DATABASE=jydb;
        UID=sa;
        PWD=sa123456""")
    # JRGCB db
    cnxn_jrgcb = pyodbc.connect("""
        DRIVER={SQL Server};
        SERVER=172.16.7.166;
        DATABASE=jrgcb;
        UID=sa;
        PWD=sa123456""")
    ########################################################
    # read index data for JYDB
    sql_mktbeta = """
        SELECT A.SecuCode, B.TradingDay, B.PrevClosePrice, B.ClosePrice,
        A.ChiName, A.InnerCode
        FROM [JYDB].[dbo].[SecuMain] A, [JYDB].[dbo].[QT_IndexQuote] B
        WHERE A.InnerCode = B.InnerCode AND A.SecuCode IN
        ('000300','000905','000852')
        AND B.ChangePCT is not null
        ORDER BY A.SecuCode, B.TradingDay"""
    data_mktbeta = pd.read_sql(sql_mktbeta, cnxn_jydb, index_col='TradingDay')
    data_mktbeta = indexdata_reshape(data_mktbeta)
    sql_indubeta = """
        SELECT A.SecuCode, B.TradingDay, B.PrevClosePrice, B.ClosePrice,
        A.ChiName, A.InnerCode
        FROM [JYDB].[dbo].[SecuMain] A, [JYDB].[dbo].[QT_IndexQuote] B
        WHERE A.InnerCode = B.InnerCode AND A.SecuCode IN
        ('CI005001','CI005002','CI005003','CI005004','CI005005',
        'CI005006','CI005007','CI005008','CI005009','CI005010',
        'CI005011','CI005012','CI005013','CI005014','CI005015',
        'CI005016','CI005017','CI005018','CI005019','CI005020',
        'CI005021','CI005022','CI005023','CI005024','CI005025',
        'CI005026','CI005027','CI005028','CI005029')
        AND B.ChangePCT is not null
        ORDER BY A.SecuCode, B.TradingDay"""
    data_indubeta = pd.read_sql(
        sql_indubeta, cnxn_jydb, index_col='TradingDay')
    data_indubeta = indexdata_reshape(data_indubeta)
    ########################################################
    ########################################################
    # sql to select distinct fund manager
    sql_allmng = """
        SELECT DISTINCT [ManagerID]
        FROM [jrgcb].[dbo].[FundAndManagerData]
        ORDER BY [ManagerID]
        """
    data_allmng = pd.read_sql(sql_allmng, cnxn_jrgcb)

    # call organize data
    ob_win = 180
    args = [(_id, data_mktbeta, data_indubeta, ob_win) for _id in
            data_allmng_.ManagerID]
    with Pool(2) as pool:
        pool.starmap(organize_data, args)

def DW_connect(linux=False,DSN='DWMarketData'):
    '''Connect to the EA Datawarehouse from window and linux boxes:
       Note: DSN can also be: NZxDaily_LIVE, or HH'''
    if linux == True:
        con = pyodbc.connect('DSN=' + DSN + ';UID=linux_user;PWD=linux')
    else:
        con = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=eadwprod\live;DATABASE=' + DSN + ';UID=linux_user;PWD=linux')
    
    return con

 def getPsychPV(self):
     """
     Liefert eine Liste der PsychPV Einträge zurück,
     die nach Datum sortiert ist
     """
     PsychPVList = []
     if self.__CaseNodeChildID != '':
         # init SQL connections and cursors
         NodesSQLConn = pyodbc.connect(self.__connection_str)
         NodesSQLCursor = NodesSQLConn.cursor()
         NodeChildsSQLConn = pyodbc.connect(self.__connection_str)
         NodeChildsSQLCursor = NodeChildsSQLConn.cursor()
         PropertiesSQLConn = pyodbc.connect(self.__connection_str)
         PropertiesSQLCursor = PropertiesSQLConn.cursor()
         # fetch nodes
         sqlquery = """
             select * from id_scorer.dbo.NODES
             where NODETYPEID='3' and PARENTID=?
             """
         for node in NodesSQLCursor.execute(sqlquery, self.__CaseNodeChildID):
             newPsychPV = PsychPV()
             sqlquery = """
                 select * from id_scorer.dbo.PROPERTIES
                 where NodeID=?
                 """
             for property in PropertiesSQLCursor.execute(sqlquery, node.ID):
                 if property.PROPERTYNAME == 'Finished':
                     if property.PROPERTYVALUE == 'true':
                         newPsychPV.Finished = True
                 if property.PROPERTYNAME == 'Date':
                     newPsychPV.Date = datetime.datetime.strptime(
                         property.PROPERTYVALUE.split('T')[0],
                         "%Y-%m-%d").date()
             sqlquery = """
                 select * from id_scorer.dbo.NODES
                 where ParentID=?
                 and NODETYPEID='7'
                 """
             for ChildNode in NodeChildsSQLCursor.execute(sqlquery, node.ID):
                 sqlquery = """
                     select * from id_scorer.dbo.PROPERTIES
                     where NodeID=?
                     """
                 for ChildNodeProperty in PropertiesSQLCursor.execute(sqlquery, ChildNode.ID):
                     if ChildNodeProperty.PROPERTYNAME == 'Value':
                         newPsychPV.StatusStr = ChildNodeProperty.PROPERTYVALUE
             PsychPVList.append(newPsychPV)
             del newPsychPV
         # close SQL connections and cursors
         NodesSQLCursor.close()
         NodesSQLConn.close()
         NodeChildsSQLCursor.close()
         NodeChildsSQLConn.close()
         PropertiesSQLCursor.close()
         PropertiesSQLConn.close()
         PsychPVList.sort(key = lambda x: x.Date)
     return PsychPVList

def test_odbc(database):
	#-----------------------------------------------------------------------------------------------
	#   Tests connection to the specified database, the name passed is the ODBC name in Windows
	#-----------------------------------------------------------------------------------------------
	
	try: pyodbc.connect('DSN='+database)
	except:
		return 0
	return 1

    def connect_to_source(self, connection_alias, connection_type, connection_source,username,password,args):
        """
        Establishes a connection to a data source and removes the alias.

        :param connection_alias: the plain english name to associate with this connection
        :param connection_type: the type of connection to use
        :param connection_source: the name of an an ODBC DSN or connection string
        :param username: the username to use for a connection (optional)
        :param password: connection_source: the password to use for a connection (optional)
        :returns: formatted string

        """

        logging.debug(" -- Working with connection '%s' of type '%s'" % (connection_alias, connection_type))

        if self.is_registered(connection_alias):
            raise Exception(stack()[0][3], \
                " --- There is already a connection with the alias %s" % connection_alias)

        logging.debug(" --- Working with connection '%s' of type '%s'" % (connection_alias, connection_type))

        if connection_type.lower() == 'odbc':

            try:

                logging.debug(" ---- Attempting to connect to '%s' " % (connection_alias))

                # connect to the ODBC source

                if (len(username) > 0 and len(password) > 0):
                    new_cnxn = pyodbc.connect(connection_source,uid=username,pwd=password)
                else:
                    new_cnxn = pyodbc.connect(connection_source)

                new_cursor = new_cnxn.cursor()

                newSource = ODBCSource(args)

                # store the results and make this the active connection
                self._conn_info[connection_alias] = \
                        {'alias' : connection_alias, \
                        'object' : newSource, \
                        'cursor' : new_cursor, \
                        'connection' :new_cnxn, \
                        'type' : connection_type }

            except pyodbc.Error, err:
                logging.error("The connection is '%s' the available connections are " % connection_alias)
                logging.error(self._conn_info)
                logging.error(err[1])
                raise err


            self._default_conn_alias = connection_alias

            logging.debug(" --- Connected data source '%s' as type '%s'"  % (connection_alias,connection_type))

 def __get_connection_object__(self):
     try:
         if self.__trusted__:
             return pyodbc.connect(server=self.__server__, driver=self.__provider__, database=self.__database__,
                                   trusted_connection=self.__trusted__, port=self.__port__, sslmode=self.__sslmode__)
         else:
             return pyodbc.connect(server=self.__server__, driver=self.__provider__, database=self.__database__,
                                   uid=self.__user__, pwd=self.__password__, port=self.__port__, sslmode=self.__sslmode__)
     except:
         raise

 def _connect(self):
     if self._conf_defs['connection_type'] == DSN:
         conn_string = 'DSN=%s' % self._conf_defs['connection_string']
         self.logger.debug('Connect to {0}'.format(conn_string))
         self._conn = pyodbc.connect(conn_string, unicode_results=True)
     elif self._conf_defs['connection_type'] == CONNECTION_STRING:
         self._conn = pyodbc.connect(self._conf_defs['connection_string'], unicode_results=True)
     else:
         #TODO need to be implemented based on connection string
         raise HydroException("Vertica connection string connection is not implemented")

def dbconn(db):
    if os.name == 'nt':
        cnxn =pyodbc.connect(''.join(('DRIVER={SQL Server};\
            SERVER=;DATABASE=', db,';UID=;\
            PWD=')))
    if os.name == 'posix':
        cnxn =pyodbc.connect(''.join(('DRIVER=freetds;port=1433;\
            SERVER=;DATABASE=', db,';UID=;\
            PWD=;tds_version=7.3')))
    return cnxn.cursor()

 def get_new_connection(self, conn_params=None):
     # assert False, "JAMI: I think this method is never used" # bad assumption
     assert conn_params is None, "JAMI: We don't use conn_params that come from outside... should we?"
     connstr = self._get_connection_string()
     options = self.settings_dict['OPTIONS']
     autocommit = options.get('autocommit', False)
     if self.unicode_results:
         connection = Database.connect(connstr, autocommit=autocommit, unicode_results='True')
     else:
         connection = Database.connect(connstr, autocommit=autocommit)
     return connection

 def connect(self):
     try:
         if self.DSN:
             self.db = pyodbc.connect("DSN=%s;UID=%s;PWD=%s;charset=%s"%(self.DSN, self.UID, self.PWD, self.charset) )
     except Exception,e:
         print e
         if self.DRIVER:
             print "use driver"
             self.db = pyodbc.connect('DRIVER={%s};HOST=%s:%s;UID=%s;PWD=%s;charset=UTF-8'%(self.DRIVER, self.HOST, str(self.PORT), self.UID, self.PWD))
         else:
             raise ValueError("Need DSN or DRIVER&&HOST&&PORT")

 def get_cursor():
     if servertype == "MSSQL":
         conn = pyodbc.connect('DRIVER={SQL Server};%s' % connection)
     elif servertype == "MYSQL":
         conn = pyodbc.connect('DRIVER={MySQL};%s' % connection)
     elif servertype == "CUSTOM":
         conn = pyodbc.connect(connection)
     else:
         print "Unsupported database type"
         return None
     return conn.cursor()

 def SetHandle(self, databasename):
     self.DatabaseDB = get_path() + '/TestResult/Database/' + str(databasename) + ".mdb"
     self.BasicinfoDB = get_path() + "/Data/BasicInfo.mdb"
     self.DBHandle = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;' % self.DatabaseDB
     self.DBBasicHandle = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;' % self.BasicinfoDB
     try:
         self.DBBasicConnection = pyodbc.connect(self.DBBasicHandle)
         self.BasicCursor = self.DBBasicConnection.cursor()
         self.TestConnection = pyodbc.connect(self.DBHandle)
         self.Cursor = self.TestConnection.cursor()
     except:
         tkinter.messagebox.showinfo(title='提示', message="数据库初始化连接错误")

 def getBIScore(self):
     """
     Liefert eine Liste aller Einträge zur Behandlungsintensität zurück,
     die nach Datum sortiert ist
     """
     BIScoreList = []
     if self.__CaseNodeChildID != '':
         # init SQL connections and cursors
         NodesSQLConn = pyodbc.connect(self.__connection_str)
         NodesSQLCursor = NodesSQLConn.cursor()
         NodeChildsSQLConn = pyodbc.connect(self.__connection_str)
         NodeChildsSQLCursor = NodeChildsSQLConn.cursor()
         PropertiesSQLConn = pyodbc.connect(self.__connection_str)
         PropertiesSQLCursor = PropertiesSQLConn.cursor()
         ## fetch all CareIntensityERW2013 Nodes
         sqlquery = """
             select ID from id_scorer.dbo.NODES
             where NODETYPEID='16'
             and PARENTID=?
             """
         for node in NodesSQLCursor.execute(sqlquery, self.__CaseNodeChildID):
             sqlquery = """
                 select * from id_scorer.dbo.PROPERTIES
                 where NODEID=?
                 """
             newBIScore = BI_Score()
             for property in PropertiesSQLCursor.execute(sqlquery, node.ID):
                 if property.PROPERTYNAME == "SomaScore":
                     newBIScore.SomaScore = int(property.PROPERTYVALUE)
                 if property.PROPERTYNAME == 'PsyScore':
                     newBIScore.PsyScore = int(property.PROPERTYVALUE)
                 if property.PROPERTYNAME == 'SocialScore':
                     newBIScore.SocialScore = int(property.PROPERTYVALUE)
                 if property.PROPERTYNAME == 'totalScore':
                     newBIScore.TotalScore = int(property.PROPERTYVALUE)
                 if property.PROPERTYNAME == 'Finished':
                     if property.PROPERTYVALUE == 'true':
                         newBIScore.Finished = True
                 if property.PROPERTYNAME == 'Date':
                     newBIScore.Date = datetime.datetime.strptime(
                         property.PROPERTYVALUE.split('T')[0],
                         "%Y-%m-%d").date()
             BIScoreList.append(newBIScore)
             del newBIScore
         # close SQL connections and cursors
         NodesSQLCursor.close()
         NodesSQLConn.close()
         NodeChildsSQLCursor.close()
         NodeChildsSQLConn.close()
         PropertiesSQLCursor.close()
         PropertiesSQLConn.close()
         BIScoreList.sort(key = lambda x: x.Date)
     return BIScoreList