def _make_logbook_connection(self):
        self._credentials = LOGBOOK_CREDENTIALS[self._name]
        self._table = self._credentials['table']

        self._shotlist_query_prefix = (
            'SELECT DISTINCT rundate, shot, xp, voided '
            'FROM {} WHERE voided IS null').format(self._table)
        self._shot_query_prefix = (
            'SELECT dbkey, username, rundate, shot, xp, topic, text, entered, '
            'voided FROM {} WHERE voided IS null').format(self._table)

        try:
            self._logbook_connection = pymssql.connect(
                server=self._credentials['server'],
                user=self._credentials['username'],
                password=self._credentials['password'],
                database=self._credentials['database'],
                port=self._credentials['port'],
                as_dict=True)
        except:
            print('Attempting logbook server connection as drsmith')
            try:
                self._logbook_connection = pymssql.connect(
                    server=self._credentials['server'],
                    user='drsmith',
                    password=self._credentials['password'],
                    database=self._credentials['database'],
                    port=self._credentials['port'],
                    as_dict=True)
            except:
                txt = '{} logbook connection failed. '.format(self._name.upper())
                txt = txt + 'Server credentials:'
                for key in self._credentials:
                    txt = txt + '  {0}:{1}'.format(key, self._credentials[key])
                raise FdpError(txt)

 def sqlManager(dbHost, dbUser, dbPass):
     print "::.SQL Manager.::"
     try:
         conn = pymssql.connect(host=dbHost, user=dbUser, password=dbPass, database="master")
         cur = conn.cursor()
         cur.execute("SELECT * FROM master.dbo.SysDatabases")
         row = cur.fetchone()
         while row:
             print row[0]
             row = cur.fetchone()
     except:
         print "Connection faulier"
         exit()
     dbName = raw_input("Choise a database: ")
     conn = pymssql.connect(host=dbHost, user=dbUser, password=dbPass, database=dbName)
     cur = conn.cursor()
     command = "y"
     command = raw_input("SQL Manager~$ ")
     while command != "exit":
         if command == "SHOW TABLES":
             cur.execute("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'")
             row = cur.fetchone()
             while row:
                 print row[2]
                 row = cur.fetchone()
         try:
             cur.execute("%s" % command)
             row = cur.fetchone()
             while row:
                 print row
                 row = cur.fetchone()
         except:
             print ""
         command = raw_input("SQL Manager~$ ")

 def dbConnection(self,dbname):
     if(dbname=='work'):
         con=ms.connect(host="10.31.201.123", user="sa", password="Y2iaciej",
                                        database="IBdata")
         return con
     if(dbname=='home'):
         con=ms.connect(host="192.168.1.5", user="sa", password="Y2iaciej",
                                        database="Quant2")
         return con

def getCursorFlight(for_update = False):
    if DEBUG:
        con = pymssql.connect(host='ppp-utilities\\testing', user='sa', password='xxx', database='Flight')
    else:
        con = pymssql.connect(host='ppp-sql01', user='sa', password='xxx', database='Flight')
    if for_update:
        return con, con.cursor()
    else:
        return con.cursor()

def exec_sql(sql, filial):
    """ executa o sql """
    if filial == 'SAO':
        conn = pymssql.connect(db_server_sp, db_user_sp, db_password_sp, db_database_sp)
    else: 
        conn = pymssql.connect(db_server_rj, db_user_rj, db_password_rj, db_database_rj)
    cursor = conn.cursor()
    cursor.execute(sql)
    rows = cursor.fetchone()
    conn.close()
    return rows

def getconnection(host, user, password, db_name, instaneName=None):
    if instaneName is None:
        conn = pymssql.connect(host, user, password, db_name)
        return conn
    else:
        conn = pymssql.connect(
            host + '\\' + instaneName,
            user,
            password,
            db_name)
        return conn

 def __init__(self, path, tablename, log_type='w3c'):
     self.filePath = path
     self.tableName = tablename
     self.logType = log_type
     self.conn = pymssql.connect(host=read_settings('settings.cfg', 'db', 'db_host'),
                                 database=read_settings('settings.cfg', 'db', 'db_name'),
                                 user=read_settings('settings.cfg', 'db', 'db_user'),
                                 password=read_settings('settings.cfg', 'db', 'db_pass'))
     self.cur = self.conn.cursor()
     self.field_list = {0: 'date',               # 发出请求时的日期
                        1: 'time',               # 发出请求时的时间
                        2: 's-sitename',         # 服务名
                        3: 's-computername',     # 服务器名
                        4: 's-ip',               # 服务器IP地址
                        5: 'cs-method',          # 请求中使用的HTTP方法，比如GET/POST
                        6: 'cs-uri-stem',        # URI资源，记录做为操作目标的统一资源标识符，即访问的页面
                        7: 'cs-uri-query',       # URI查询，记录客户尝试执行的查询
                        8: 's-port',             # 服务器提供服务的端口号
                        9: 'cs-username',        # 用户名
                        10: 'c-ip',              # 客户端的IP地址
                        11: 'cs-version',        # 客户端使用的协议版本，如HTTP/FTP等
                        12: 'cs(User-Agent)',    # 用户代理、浏览器、操作系统等情况
                        13: 'cs(Cookie)',        # 记录发送或接受的Cookies内容
                        14: 'cs(Referer)',       # 引用站点，即访问来源
                        15: 'cs-host',           # 记录主机头名称
                        16: 'sc-status',         # 协议状态，如200，403，404，500等
                        17: 'sc-substatus',      # 协议子状态
                        18: 'sc-win32-status',   # Win32状态
                        19: 'sc-bytes',          # 服务器发送的字节数
                        20: 'cs-bytes',          # 服务器接受的字节数
                        21: 'time-taken'         # 记录操作所花的时间，单位毫秒
                        }

def getDetail(serial_id):
    """
    获取已使用券明细
    :param serial_id:批次ID
    :return: 列表
    """
    conn = pymssql.connect(host=Constants.KGGROUP_DB_SERVER,
                           port=Constants.KGGROUP_DB_PORT,
                           user=Constants.KGGROUP_DB_USER,
                           password=Constants.KGGROUP_DB_PASSWORD,
                           database=Constants.KGGROUP_DB_DATABASE,
                           charset='utf8',
                           as_dict=True)
    cur = conn.cursor()
    sql = u"select ClearShopID as used_shop, " \
          u"       ClearSDate as used_date, " \
          u"       ClearPOSID as used_name, " \
          u"       CouponNO as voucher " \
          u"  from MyShop_Coupon " \
          u" where ClearFlag = 1" \
          u"   and SerialID = '{serial_id}'" \
          u" order by ClearShopID, ClearSDate".format(serial_id=serial_id)
    cur.execute(sql)
    list = cur.fetchall()
    cur.close()
    conn.close()
    return list

    def res_department_import(self,cr,uid,ids,context=None):
      obj=self.pool.get('res.department')
      server='192.168.10.2'
      user='sa'
      password='719799'
      database='mtlerp-running'
      
      conn=pymssql.connect(server=server,user=user,password=password,database=database)
     
      cur=conn.cursor()
      sql=''' select isnull(a.departmentname,'') as departmentname ,isnull(a.address,'')as address,isnull(a.telephone,'') as telephone,isnull(b.employeename,'') from TBdepartment a left join TBemployee b on  a.DepartmentManager=b.employeecode '''
      cur.execute(sql)
      s=cur.fetchall()
     
      b=[]
      for row in s:
           b.append([(''.join(map(lambda x: "%c" % ord(x), list(row[i]))).decode('gbk')) for i in range(len(row))])
           print row,'row'
      for row in b:  
           info_id=obj.create(cr,uid,{                                                                                                                 
                'name':row[0],
                'department_address':row[1],
                'link_phone':row[2],
                'responsiblename':row[3],
                    })

      conn.close()
                
      return True 

    def get_mom_connection(self):
        """ Gets SQL Server connection to MOM """
        config = SafeConfigParser()
        config.read(os.path.join(os.path.dirname(__file__), 'config.ini'))
        try:
            momdb_host = config.get("momdb", "host")
            momdb_user = config.get("momdb", "user")
            momdb_password = config.get("momdb", "password")
            momdb_db = config.get("momdb", "db")
        except Error as error:
            msg = "Config section [momdb] bad or missing: %s" % error.message
            logging.error(msg)
            raise Exception(msg)

        try:
            logging.info('Connecting to MOM...')
            if self.conn is None:
                self.conn = connect(host=momdb_host, user=momdb_user,
                                    password=momdb_password,
                                    database=momdb_db, as_dict=True)
            return self.conn
        except InterfaceError as error:
            msg = "Error connecting to SQL Server: %s" % error.message
            logging.error(msg)
            raise Exception(msg)
        except Error as error:
            logging.error(error.message)
            raise

def run(options):
    socket.setdefaulttimeout(2)

    ip = options['ip']
    username = options['username']
    password = options['password']

    test = random.choice(config.MSSQL_QUERIES)
    expected = test['response']

    try:
        conn = pymssql.connect(ip, '{}\\{}'.format(config.DOMAIN, username), password, test['db'])
        cursor = conn.cursor()
        cursor.execute(test['query'])
        response = ' '.join([str(row[0]) for row in cursor.fetchall()])
    except pymssql.Error as e:
        name = e.__class__.__name__
        if name in ERROR_STRINGS:
            error_string = ERROR_STRINGS[name]
            logger.debug(error_string % e)
        else: 
            logger.debug('%s: %s' % (name, e))
        return False

    if response == expected:
        return True
    else:
        logger.debug('Check failed: output: %s | expected: %s' % (response, expected))
        return False

def connect_sql_server():
  try:
    conn = pymssql.connect(host ="10.0.185.138",database ="JYDB",user="js_dev",password="js_dev",charset = "utf8")
    print "sql_server 数据库连接成功".decode("utf8")
    return conn
  except Exception, e:
	print "sql_server 数据库连接失败".decode("utf8"),e

def get_jwst_ephemeris():
    '''
    extracts predicted JWST ephemeris from DMS database (the whole thing for now)
    and returns a numpy 2d array
    '''
    if 'METRICS_SERVER' in os.environ:
        eserver = os.environ['METRICS_SERVER']
    else:
        eserver = 'JWDMSDEVDBVM1'
    if 'METRICS_DB' in os.environ:
        edb = os.environ['METRICS_DB']
    else:
        edb = 'jwdpmetrics5'
    logger.info(
        'Ephemeris connect info:'
        ' eserver={}'
        ' edb={}'.format(eserver, edb)
    )
    logger.debug(
        'Ephemeris connect info:'
        ' eserver={}'
        ' edb={}'.format(eserver, edb)
    )
    conn = pymssql.connect(server=eserver, database=edb)
    cur = conn.cursor()
    cur.execute('select * from predictephemeris')
    etab = np.array(cur.fetchall())
    return etab

def formconnection():
    """
    Connect to the MS SQL server and execute a query to get the data 
    which contains trees with DBH > 200 cm.
    """

    # Connect to MSSQL Server
    conn = pymssql.connect(server="stewartia.forestry.oregonstate.edu:1433",
                            user="petersonf",
                            password="D0ntd1sATLGA!!",
                            database="FSDBDATA")
 
    # Create a database cursor
    cursor = conn.cursor()
 
    # Replace this nonsense with your own query :)
    query = """SELECT fsdbdata.dbo.tp00101.treeid, fsdbdata.dbo.tp00101.psp_studyid,
            fsdbdata.dbo.tp00101.species, fsdbdata.dbo.tp00101.standid, fsdbdata.dbo.tp00102.treeid, 
            fsdbdata.dbo.tp00102.dbh, fsdbdata.dbo.tp00102.tree_vigor FROM fsdbdata.dbo.tp00101 
            LEFT JOIN fsdbdata.dbo.tp00102 
            ON fsdbdata.dbo.tp00101.treeid = fsdbdata.dbo.tp00102.treeid
            WHERE fsdbdata.dbo.tp00102.dbh > 150 
            ORDER BY fsdbdata.dbo.tp00102.treeid ASC"""
 
    # Execute the query
    cursor.execute(query)
 
    return cursor

def crack(target, port, user, passwd, outfile, timeo, i):
    user = user.replace("\n", "")
    passwd = passwd.replace("\n", "")
    db = None
    try:
        db = pymssql.connect(user=user, password=passwd, database="production", server=target, port=port, timeout=timeo)
        login = "[+] login found for %s | %s:%s\n" % (target, user, passwd)
        print login
        if outfile:
            write_to_file(outfile, login + "\n")
        db.close()
        os._exit(0)
    except pymssql.Error as err:
        exception = str(err)
        if "['publickey']" in exception:
            print "[-] key authentication only - " "stopped attack against %s" % (target)
            os._exit(1)
        elif "'keyboard-interactive'" in exception:
            print "[-] %s requires 'keyboard-interactive' handler" % (target)
            os._exit(1)
    except:
        # after 3 timeouts per request the attack against $target will stopped
        if i < 3:
            i += 1
            # reconnect after random seconds (between 0.6 and 1.2 sec)
            randtime = random.uniform(0.6, 1.2)
            time.sleep(randtime)
            crack(target, port, user, passwd, outfile, timeo, i)
        else:
            print "[-] too many timeouts - stopped attack against %s" % (target)
            os._exit(1)

 def connect(self):
     """Attempts to establish connection to SQL Server database."""
     conn = pymssql.connect(host=self.server+'\\'+self.instance,
                            user=self.user,
                            password=self.password,
                            database=self.database)
     return conn

def insert(id_si8, regName, value, now_date=datetime.datetime.now()):
    conn = pymssql.connect(server=server, user=user, password=password, database=database)
    cursor = conn.cursor()
    cursor.execute("INSERT INTO si8_value (id_si8, regName, value, Date) VALUES ( %d, %s, %d, %s)",
                   (id_si8, regName, value, now_date))
    conn.commit()
    conn.close()

def testcon(server, user, pwd, db, sqlstr):
    conn = pymssql.connect(server, user, pwd, db,sqlstr)
    cursor = conn.cursor()
    cursor.execute(sqlstr)
    row = cursor.fetchone()
    conn.close()
    return row

def pymssql_delete_table(cfg, tablename):
    import pymssql
    server = cfg['ipv4host']
    def get_username(cfg):
        username = cfg.get('username', None)
        if username:
            return username

        if 'meta' not in cfg:
            return None

        return cfg['meta'].get('user', None)

    def get_password(cfg):
        password = cfg.get('password', None)
        if password:
            return password

        if 'meta' not in cfg:
            return None

        return cfg['meta'].get('password', None)

    user = get_username(cfg)
    password = get_password(cfg)
    dbname = cfg['meta']['databaseName']
    conn = pymssql.connect(server, user, password, dbname, tablename)
    cursor = conn.cursor()
    # TODO:
    cursor.execute("DELETE FROM %s" % tablename)
    conn.commit()

def get_sql_connection(conn_string):
    """
    Returns a pymssql connection object based on the connection string in settings.
    """
    conn_settings = get_conn_settings(conn_string)
    return pymssql.connect(conn_settings['SERVER'], conn_settings['UID'],
                           conn_settings['PWD'], conn_settings['DATABASE'])