def executeSql(sql): data = pkgutil.get_data(__package__, 'database.dat') values = re.split("\W+", data) try: db = sybpydb.connect(servername=values[0], user=values[1], password=values[2]) c = db.cursor() c.execute("use us") # print("executeSql. SQL: "+sql) # logger.info("executeSql. SQL: "+sql) c.execute(sql) c.execute("SELECT @@rowcount") count = c.fetchall()[0][0] c.close() db.close() return count except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: for err in c.connection.messages: logger.error("Exception %s, Value %s" % (err[0], err[1])) except Exception: logger.error('executeSql Failed', exc_info=False) return 0
def getOneValue(sql): # logger.info("getOneValue") data = pkgutil.get_data(__package__, 'database.dat') values = re.split("\W+", data) try: db = sybpydb.connect(servername=values[0], user=values[1], password=values[2]) c = db.cursor() c.execute("use us") # print("executeSql. SQL: "+sql) # logger.info("executeSql. SQL: " + sql) c.execute(sql) # print(c.rowcount) data = c.fetchall() # c.execute("SELECT @@rowcount") # count = c.fetchall()[0][0] # print(count) c.close() db.close() return data[0][0] # first row, first column #return (string.join([row[0] for row in c.fetchall()], '\n')) except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: for err in c.connection.messages: logger.error("Exception %s, Value %s" % (err[0], err[1])) except Exception: logger.error('getOneValue Failed', exc_info=False) return ''
def exec_cmd(sql): conn = sybpydb.connect(user='******',password='******',servername="PDSQTS1") cur=conn.cursor() cur.execute(sql) result=cur.fetchall() cur.close() conn.close() return result
def _cursor(self): """ Get new cursor """ new_conn = False settings_dict = self.settings_dict db_str, user_str, passwd_str, port_str, interfaces_path, charset, connection_timeout = None, None, "", None, None, None, None options = settings_dict['OPTIONS'] # rewrite using dsn if settings_dict['NAME']: db_str = settings_dict['NAME'] if settings_dict['USER']: user_str = settings_dict['USER'] if settings_dict['PASSWORD']: passwd_str = settings_dict['PASSWORD'] """ api 2.0 start using dsn so get additional parametrs """ if 'Interfaces' in options: interfaces_path = options['Interfaces'] if 'Charset' in options: charset = options['Charset'] else: # use default utf8 charset = 'UTF8' if 'Timeout' in options: connection_timeout = options['Timeout'] if self.connection is None: new_conn = True # from sybpydb 2.0 support normal dsn # if Database.apilevel == '2.0': # self.connection = Database.connect(dsn=self._create_dsn(user=user_str, pwd=passwd_str, server=db_str, interfaces=interfaces_path, charset=charset, timeout=connection_timeout)) # else: self.connection = Database.connect(servername=db_str, user=user_str, password=passwd_str) connection_created.send(sender=self.__class__) cursor = self.connection.cursor() if new_conn: # Set date format for the connection. Also, make sure Sunday is # considered the first day of the week (to be consistent with the # Django convention for the 'week_day' Django lookup) if the user # hasn't told us otherwise cursor.execute("SET CHAINED OFF") cursor.execute("SET DATEFORMAT ymd") self.connection.commit() # cursor.execute("SET OPTION Date_format = 'yyyy-mm-dd'") cursor.execute("SET DATEFIRST %s" % self.datefirst) self.connection.commit() # set database auto convert to client charset(utf-8) cursor.execute("SET char_convert UTF8") self.connection.commit() # change default database if self.server_database is not None: cursor.execute('use %s' % self.server_database) self.connection.commit() return CursorWrapper(cursor, self.database_encoding)
def connect_to_db(cls, db_type, server, db_name): db_conn = '' if db_type == 'sybase': try: db_conn = sybpydb.connect(dsn="servername=%s;database=%s" % (server, db_name)) break except Exception as e: logging.info('Could NOT connect to db') elif db_type == 'db2': try: db_conn = db2.connect(server) break except Exception as e: logging.info('Could NOT connect to db') return db_conn
def conn_db(conn_data): ''' 连接数据库 :param conn_data:数据库连接配置,在config.py文件中 :return: 返回数据库连接cur ''' try: if conn_data['SERVER'] == 'mysql': cur, conn = connect_mysql(conn_data) return cur, conn elif conn_data['SERVER'] == 'sybase': import sybpydb conn = sybpydb.connect(user=conn_data['user'], password=conn_data['passwd'], servername=conn_data['db']) cur = conn.cursor() return cur, conn except Exception as e: print(e.__str__() + "\nUsing default :mysql-database...")
def main(): conn = sybase.connect(user=db_UN, password=db_PW, servername=db_SN) initial_Check() query = get_Keys missing_BMCs = get_Missing_BMCs file_Name = find_File() stored_Value = store_Values(file_Name) if use_File == True: querydb = query_DB(query, conn) frm_File = get_Missing_From_File() results = match_Values(stored_Value, querydb, frm_File) else: querydb = query_DB(query, conn) queryformissing = query_DB(missing_BMCs, conn) results = match_Values(stored_Value, querydb, queryformissing) conn.close() generate_Insert(results)
def connectDB(db_type, db_name, connection_strings=['NA', 'NA', 'NA']): logMsg('connectDB(db_type,db_name,connection_strings=[\'NA\'])') varValidate('string', 'db_type', db_type) varValidate('string', 'db_name', db_name) varValidate('list', 'connection_strings', connection_strings) try: db_type = db_type.lower() if db_type == 'mysql': import MySQLdb db = MySQLdb.connect(host=connection_strings[0], user=connection_strings[1], password=connection_strings[2], database=db_name) elif db_type == 'sybase': import sybpydb db = sybpydb.connect(host=connection_strings[0], user=connection_strings[1], password=connection_strings[2], database=db_name) elif db_type == 'oracle': import cx_Oracle db = cx_Oracle.connect(connection_strings[1], connection_strings[2], db_name) elif db_type == 'msaccess': import pypyodbc db = pypyodbc.connect( 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=' + db_name) else: logMsg('Invalid DB type: ' + str(db_type), 'ERROR') return 'ops_auto_error' # db.close() logMsg('DB connection made to: ' + str(connection_strings[0]), 'INFO') return db except Exception as e: logMsg('DB connection failed:\n' + str(e), 'ERROR') return 'ops_auto_error'
def __init__(self, username, password, servername) : self._username = username self._password = password self._servername = servername self.conn = sybase.connect(user=self._username, password=self._password, servername=self._servername)
# INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, # BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS # OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND # ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR # TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE # USE OF THE SAMPLE CODE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH # DAMAGE. # # Description # ----------- # This example demonstrates connecting to a server. # It can be used as an aid in checking a successful installation. # If it runs to completion, then the SAP ASE Python extension module # was found and successfully loaded. And it also verifies that a # connection to the database can be established. # # Inputs # ------ # None. # # Server Tables # ------------- # None. # import sybpydb conn = sybpydb.connect(user='******', password='') print("Successfully established connection, exiting.") conn.close()
len = lob.readinto(chunk) if (len == None): break outarr.extend(chunk[:len]) print(outarr.decode()) # Get two file names if len(sys.argv) < 3: sys.exit("Usage: %s file1 file2" % sys.argv[0]) if not os.path.exists(sys.argv[1]): sys.exit('ERROR: file %s not found!' % sys.argv[1]) if not os.path.exists(sys.argv[2]): sys.exit('ERROR: file %s not found!' % sys.argv[2]) conn = sybpydb.connect(dsn="user=sa;bulklogin=true;chainxacts=off") cur = conn.cursor() cur.execute("if exists (select name from sysdatabases where name = \"mydb\") \ drop database mydb") cur.execute("create database mydb") cur.execute("exec sp_dboption mydb, \"select into/bulkcopy\", true") cur.execute("use mydb") cur.execute("create table mytable (id int, t text, i image)") cur.close() # Start the copy in operation blk = conn.blkcursor() blk.copy("mytable", direction="in") # First transfer text and image data using a bytearray. arr1 = bytearray(b"hello this is some text data")
def checkDBtables(tmPrefsOnly): okay = False data = pkgutil.get_data(__package__, 'database.dat') values = re.split("\W+", data) db = sybpydb.connect(servername=values[0], user=values[1], password=values[2]) c = db.cursor() try: c.execute("use us") c.execute("SELECT name FROM tm_prefs") data = c.fetchall() # name = data[0][0] # first row, first column okay = True logger.info("tm_prefs exists") except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: logger.info("tm_prefs is missing") okay = False except Exception: okay = False if not okay: sql = "create table tm_prefs (name varchar(64) not null, param varchar(64) not null, value varchar(255) null, primary key (name, param) )" try: c = db.cursor() c.execute(sql) okay = True except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: # for err in c.connection.messages: # logger.error("Exception %s, Value %s" % (err[0], err[1])) logger.info("failed to create tm_prefs") except Exception: logger.error('Failed', exc_info=True) if tmPrefsOnly: c.close() db.close() return okay okay2 = False try: c = db.cursor() c.execute("SELECT privilege FROM user_privileges") data = c.fetchall() # name = data[0][0] # first row, first column okay2 = True logger.info("user_privileges exists") except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: logger.info("user_privileges is missing") okay2 = False except Exception: okay2 = False if not okay2: sql = "create table user_privileges (privilege varchar(30) not null, availability int not null, group_id varchar(30) null, user_id varchar(30) null, description varchar(128) null )" try: c = db.cursor() c.execute(sql) okay2 = True except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: # for err in c.connection.messages: # logger.error("Exception %s, Value %s" % (err[0], err[1])) logger.info("failed to create user_privileges") except Exception: logger.error('Failed', exc_info=True) okay3 = False try: c = db.cursor() c.execute("SELECT indx FROM user_privileges_lc") data = c.fetchall() # name = data[0][0] # first row, first column okay3 = True logger.info("user_privileges_lc exists") except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: logger.info("user_privileges_lc is missing") okay3 = False except Exception: okay3 = False if not okay3: sql = "create table user_privileges_lc (indx int not null, privilege varchar(30) not null, description varchar(128) null, licensed int not null )" try: c = db.cursor() c.execute(sql) okay3 = True except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: # for err in c.connection.messages: # logger.error("Exception %s, Value %s" % (err[0], err[1])) logger.info("failed to create user_privileges_lc") except Exception: logger.error('Failed', exc_info=True) okay4 = False try: c = db.cursor() c.execute("SELECT * FROM groups") data = c.fetchall() # name = data[0][0] # first row, first column okay4 = True logger.info("groups exists") except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: logger.info("groups is missing") okay4 = False except Exception: okay4 = False if not okay4: sql = "create table groups (group_name varchar(30) not null, user_id varchar(30) null )" try: c = db.cursor() c.execute(sql) okay4 = True except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: # for err in c.connection.messages: # logger.error("Exception %s, Value %s" % (err[0], err[1])) logger.info("failed to create groups") except Exception: logger.error('Failed', exc_info=True) sql = "insert into groups (group_name, user_id) values ('everyone', '')" try: c = db.cursor() c.execute(sql) okay4 = True except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: # for err in c.connection.messages: # logger.error("Exception %s, Value %s" % (err[0], err[1])) logger.info("failed to insert groups") except Exception: logger.error('Failed', exc_info=True) c.close() db.close() return okay and okay2 and okay3 and okay4
def updatePrivBasedOnLicensing(): data = pkgutil.get_data(__package__, 'database.dat') values = re.split("\W+", data) try: db = sybpydb.connect(servername=values[0], user=values[1], password=values[2]) c = db.cursor() c.execute("use us") sql = "SELECT distinct user_privileges_lc.privilege, user_privileges_lc.description FROM user_privileges, user_privileges_lc where availability = 0 and user_privileges.privilege = user_privileges_lc.privilege and licensed = 0 " c.execute(sql) data = c.fetchall() for row in data: privilege = row[0] description = row[1] sql = "delete FROM user_privileges where privilege = '" + privilege + "' and availability = 0" c.execute(sql) sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('" + privilege + "', 1, '', '', '" + description + "')" c.execute(sql) bEveryoneCheck = False sql = "SELECT distinct user_privileges_lc.privilege, user_privileges_lc.description FROM user_privileges, user_privileges_lc where availability = 1 and user_privileges.privilege = user_privileges_lc.privilege and licensed = 1 " " and user_privileges_lc.privilege in ('omusl_manage_patients', 'omusl_manage_studies', 'omusl_push_monitor', 'omusl_run', 'omusl_study_status'," " 'omv_add_report', 'omv_edit_report', 'omv_email', 'omv_push', 'omv_save_anno', 'omv_search', 'omv_show_anno', 'omv_view', 'omx_multy', 'omx_run', 'omusl_vcd'," " 'allpro_images', 'omusl_wklst_scu', 'omusl_scanner', 'omusl_attach', 'omusl_non_dicom', 'omusl_lightscribe', 'omusl_cd_import', 'omusl_jpeg_export'," " 'omv_adv_anno', 'omv_https', 'omusl_radviewer')" c.execute(sql) data = c.fetchall() for row in data: privilege = row[0] description = row[1] sql = "delete FROM user_privileges where privilege = '" + privilege + "' and availability = 1" c.execute(sql) sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('" + privilege + "', 0, 'everyone', '', '" + description + "')" c.execute(sql) bEveryoneCheck = True if (bEveryoneCheck): sql = "SELECT group_name FROM groups WHERE group_name = 'everyone'" c.execute(sql) data = c.fetchall() if (data[0][0] != ""): sql = "insert into groups (group_name) values ('everyone', '')" c.execute(sql) bAdminCheck = False sql = "SELECT distinct user_privileges_lc.privilege, user_privileges_lc.description FROM user_privileges, user_privileges_lc where availability = 1 and user_privileges.privilege = user_privileges_lc.privilege and licensed = 1 " " and user_privileges_lc.privilege in ('omacm_add_priv', 'omacm_admin', 'omadmin_cc', 'omadmin_console', 'omadmin_db_check', 'omadmin_dict', 'omadmin_distr'," " 'omadmin_erpr', 'omadmin_file_audit', 'omadmin_flex', 'omadmin_hp', 'omadmin_kds', 'omadmin_push', 'omadmin_run', 'omadmin_utils', 'omsdm_power_on'," " 'omstm_run', 'omstm_admin', 'omusl_profile', 'omv_vitrea', 'pacs_hl7_adv', 'omv_push_adv', 'pacs_ipad', 'pacs_android', 'omx_publishing'," " 'omusl_vcd_import', 'omusl_oncall_caching', 'rsvw_dictation', 'omv_print', 'omv_dicom_print', 'omv_multi_monitor', 'autoupdate_run', 'omusl_adv_demo'," " 'omusl_adv_filters', 'pacs_wklst_scp', 'pacs_report_activity', 'pacs_backup', 'pacs_backup_adv', 'pacs_hl7', 'pacs_hl7_adv')" for row in data: privilege = row[0] description = row[1] sql = "delete FROM user_privileges where privilege = '" + privilege + "' and availability = 1" c.execute(sql) sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('" + privilege + "', 0, '', 'admin', '" + description + "')" c.execute(sql) bAdminCheck = True if (bAdminCheck): sql = "SELECT user_id FROM users where user_id = 'admin'" c.execute(sql) data = c.fetchall() if (data[0][0] != ""): sql = "insert into users (user_id, name, last_name, first_name, password) values ('admin', 'PACSimple Admin', 'Admin', 'PACSimple', 'admin!')" c.execute(sql) sql = "SELECT privilege FROM user_privileges where user_id = 'admin' and privilege = 'omadmin_run'" c.execute(sql) data = c.fetchall() if (data[0][0] != ""): c.execute( "SELECT description FROM user_privileges where privilege = 'omadmin_run'" ) sql = "delete FROM user_privileges where privilege = 'omadmin_run' and availability = 1" c.execute(sql) data = c.fetchall() sDescription = data[0][0] sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('omadmin_run', 0, '', 'admin', '" + sDescription + "')" c.execute(sql) sql = "SELECT privilege FROM user_privileges where user_id = 'admin' and privilege = 'omacm_admin'" c.execute(sql) data = c.fetchall() if (data[0][0] != ""): c.execute( "SELECT description FROM user_privileges where privilege = 'omacm_admin'" ) sql = "delete FROM user_privileges where privilege = 'omacm_admin' and availability = 1" c.execute(sql) data = c.fetchall() sDescription = data[0][0] sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('omacm_admin', 0, '', 'admin', '" + sDescription + "')" c.execute(sql) sql = "SELECT privilege FROM user_privileges where user_id = 'admin' and privilege = 'omacm_add_priv'" c.execute(sql) data = c.fetchall() if (data[0][0] != ""): c.execute( "SELECT description FROM user_privileges where privilege = 'omacm_add_priv'" ) sql = "delete FROM user_privileges where privilege = 'omacm_add_priv' and availability = 1" c.execute(sql) data = c.fetchall() sDescription = data[0][0] sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('omacm_add_priv', 0, '', 'admin', '" + sDescription + "')" c.execute(sql) c.close() db.close() except (SystemExit, KeyboardInterrupt): raise except sybpydb.Error: for err in c.connection.messages: logger.error("Exception %s, Value %s" % (err[0], err[1])) except Exception: logger.error('updatePrivBasedOnLicensing Failed', exc_info=False)
len = lob.readinto(chunk); if (len == None): break outarr.extend(chunk[:len]) print(outarr.decode()) # Get two file names if len(sys.argv) < 3: sys.exit("Usage: %s file1 file2" % sys.argv[0]) if not os.path.exists(sys.argv[1]): sys.exit('ERROR: file %s not found!' % sys.argv[1]) if not os.path.exists(sys.argv[2]): sys.exit('ERROR: file %s not found!' % sys.argv[2]) conn = sybpydb.connect(user='******', password='', dsn='chainxacts=off') cur = conn.cursor() cur.execute("if exists (select name from sysdatabases where name = \"mydb\") \ drop database mydb") cur.execute("create database mydb") cur.execute("use mydb") cur.execute("create table mytable (i int, t1 text, i1 image)") # First transfer text and image data using a bytearray. arr1 = bytearray(b"hello this is some text data") lob1 = sybpydb.Lob(sybpydb.TEXT, arr1) arr2 = bytearray(b"hello this is some image data") lob2 = sybpydb.Lob(sybpydb.IMAGE, arr2) cur.execute("insert into mytable values (?, ?, ?)", [1, lob1, lob2])
# # Description # ----------- # This example demonstrates connecting to a server using a dsn # # Inputs # ------ # None. # # Server Tables # ------------- # import os import sybpydb server = os.getenv("DSQUERY") user = "******" dsn_str = "servername=%s;user=%s" % (server, user) conn = sybpydb.connect(dsn=dsn_str) cur = conn.cursor() cur.execute("select suser_name()") row = cur.fetchone() print ("We're logged in as '%s'" % row[0]) cur.execute("select db_name()") row = cur.fetchone() print ("The current database is '%s'" % row[0]) cur.close() conn.close()