python 实现无DSN处理sybase数据

攻城狮挖的坑只有运维知道，只能自己想办法解决一些重复性修正工作。目标数据库sybase，鲜少的但是被SAP收购了。通过python实现客户自助数据修正，确保数据正常流通。先安装sybase的odbc驱动，大家可以百度下，虽然不用配置DSN但是ODBC还是需要的。如下完整代码，因涉及到数据安全敏感信息***代替 # -*- coding:utf-8 -*- __Author__ = '秦岭' import pypyodbc class DatadbaseService: ''' 提供sybase数据库操作类 ''' __sql='' __item=() __cnn='' def __init__(self,driver,network_address,database,name,pwd): self.__driver=driver self.__network_address=network_address self.__database=database self.__name=name self.__pwd=pwd @property def sys_network_address(self): return self.__network_address @sys_network_address.setter def sys_network_address(self,network_address): if network_address!='': self.__network_address=network_address return True else: print('服务器地址不存在') return False @property def sys_driver(self): return self.__driver @sys_driver.setter def sys_driver(self,driver): if driver!='': self.__driver=driver return True else: print('driver不能为空') return False @property def sys_name(self): return self.__name @sys_name.setter def sys_name(self,name): if name!='': self.__name=name return True else: print('数据库登录代号不能为空') return False @property def sys_pwd(self): return self.__pwd @sys_pwd.setter def sys_pwd(self,pwd): if pwd!='': self.__pwd=pwd return True else: print('密码不能为空') return False @property def sys_database(self): return self.__database @sys_database.setter def sys_database(self,database): if database!='': self.__database=database return True else: print('数据库名不能为空') return False @property def sys_sql(self): return self.__sql @sys_sql.setter def sys_sql(self,sql): if sql!='': self.__sql=sql return True else: print('sql不能为空') return False @property def sql_item(self): return self.__item @sql_item.setter def sql_item(self,item): if item!='': self.__item=item return True else: print('条件参数不能为空') return False def connet_database(self): '''建立数据库连接''' try: self.__cnn=pypyodbc.connect('driver={driver};networkaddress={networkaddress};database={database};charset=utf8;UID={name};PWD={pwd}' .format(driver=self.__driver,networkaddress=self.__network_address,database=self.__database,name=self.__name,pwd=self.__pwd)) return self.__cnn except Exception as e: print(e) print('请检查数据库连接参数') def qury_databse(self,sql): '''查询数据库''' self.sys_sql=sql try: self.connet_database() cur=self.__cnn.cursor() cur.execute(self.__sql) return cur.fetchall() except Exception as e: print(e) print('查询失败') finally: self.__cnn.close() def update_database(self,sql): # self.sql_item=item self.sys_sql=sql try: cnn=self.connet_database() cur=cnn.cursor() # cur.execute(sql,self.__item) cur.execute(self.__sql) cnn.commit() return True except Exception as e: cnn.rollback() print('修复失败') print(e) return False finally: cnn.close() def vrify_user(self,codes,password): # 用户权限验证 if codes !='' or password !='': try: result=self.qury_databse('select * from ****** where codes=\'{codes}\' and password=\'{password}\''.format(codes=codes,password=password)) if result: print('验证成功') return True else: print('登录代号或密码不对，请请注意大小写') return False except Exception as e: print(e) return False else: print('用户密码和密码不能为空') return False codes=input('请输入登录代号') pwd=input('请输入密码') service1=DatadbaseService('{Sybase ASE ODBC Driver}','**.**.***.**,****','***','*****','*****') verify_re=service1.vrify_user(codes,pwd) def update(): '''如下进行修复权限认证并进行修复''' if verify_re: in_id=input('请输入系统单号,多个系统单号请使用半角逗号隔开：') if len(in_id)<5: print('请输入正确的单号') else: for id in in_id.split(','): if id!='': update_sql='update ***_**** set nos=left(nos,32) where id in={id}'.format(id=int(id)) qury_sql='select 1 from ***_**** where id={id}'.format(id=id) if len(service1.qury_databse(qury_sql))!=0: if service1.update_database(update_sql): print('单号{id}修复完毕，请重新传单'.format(id=id)) else: print('单号{id}修复失败，请联系管理员'.format(id=id)) else: print('输入的系统单号不存在，请查证后输入') '''递归调用，修复完成后进入待修复状态''' update() else: print('无权操作') update()