def __init__(self, connectionString, debug=False): if debug: print "You're using Oracle Client Tools v" + ".".join(map(str,cx_Oracle.clientversion())) self._conn = cx_Oracle.connect(connectionString) self._conn.autocommit = True self._curs = self._conn.cursor() self.fetchall = self._curs.fetchall self.description = self._curs.description self.columns = dict()
def client_version(): """ Oracle Client Version CLI Example: .. code-block:: bash salt '*' oracle.client_version """ return ".".join((str(x) for x in cx_Oracle.clientversion()))
def client_version(): ''' Oracle Client Version CLI Example: .. code-block:: bash salt '*' oracle.client_version ''' return '.'.join((str(x) for x in cx_Oracle.clientversion()))
def getSodaDatabase( self, minclient=(18, 3), minserver=(18, 0), message="not supported with this client/server combination"): client = cx_Oracle.clientversion()[:2] if client < minclient: self.skipTest(message) server = tuple(int(s) for s in self.connection.version.split("."))[:2] if server < minserver: self.skipTest(message) if server > (20, 1) and client < (20, 1): self.skipTest(message) return self.connection.getSodaDatabase()
def ConnDb(db_type='ora', alias_db=None): if db_type == 'ora': try: new_dsn = cx_Oracle.makedsn(host=alias_db['host'], port=alias_db['port'], service_name=alias_db['sid']) connection = cx_Oracle.connect(alias_db['user'], alias_db['passw'], new_dsn) connection.autocommit = False logger.info("> connected to Oracle, client {0}".format('.'.join( [str(i) for i in cx_Oracle.clientversion()]))) return connection except cx_Oracle.DatabaseError as exc: error, = exc.args logger.error('{0}\n'.format(error.message.strip())) return False else: logger.error("Only ORACLE connection available in this version") return False
def testPLSQLSessionCallbacks(self): "test PL/SQL session callbacks" clientVersion = cx_Oracle.clientversion() if clientVersion < (12, 2): self.skipTest("PL/SQL session callbacks not supported before 12.2") pool = TestEnv.GetPool( min=2, max=8, increment=3, getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT, sessionCallback="pkg_SessionCallback.TheCallback") tags = [ "NLS_DATE_FORMAT=SIMPLE", "NLS_DATE_FORMAT=FULL;TIME_ZONE=UTC", "NLS_DATE_FORMAT=FULL;TIME_ZONE=MST" ] actualTags = [None, None, "NLS_DATE_FORMAT=FULL;TIME_ZONE=UTC"] # truncate PL/SQL session callback log conn = pool.acquire() cursor = conn.cursor() cursor.execute("truncate table PLSQLSessionCallbacks") conn.close() # request sessions with each of the first two tags for tag in tags[:2]: conn = pool.acquire(tag=tag) conn.close() # for the last tag, use the matchanytag flag conn = pool.acquire(tag=tags[2], matchanytag=True) conn.close() # verify the PL/SQL session callback log is accurate conn = pool.acquire() cursor = conn.cursor() cursor.execute(""" select RequestedTag, ActualTag from PLSQLSessionCallbacks order by FixupTimestamp""") results = cursor.fetchall() expectedResults = list(zip(tags, actualTags)) self.assertEqual(results, expectedResults)
def connectNoDSN(self, user, pswd, serviceName, host, port=1521): ''' Allows for the creation of a database connection when the client does not have a valid TNS file. Allows you to connect using port and host name. :param user: schema that you are using to connnect to the database :type user: str :param pswd: password that goes with the schema :type pswd: str :param serviceName: The database serviceName (service_name) that is being connected to. :type serviceName: str :param host: The host that the serviceName resides on :type host: str :param port: the port that the database listener is attached to. :type port: int ''' try: clientVer = cx_Oracle.clientversion() self.logger.debug("client version: %s", clientVer) cxOraPath = os.path.abspath(cx_Oracle.__file__) self.logger.debug("cx_oracle path is: %s", cxOraPath) # dsn = cx_Oracle.makedsn(host, port, service_name=serviceName) dsn = cx_Oracle.makedsn(host, port, service_name=serviceName) # pylint: disable=no-member self.logger.info("successfully connected to host/sn %s/%s", host, serviceName) except Exception as e: # pylint: disable=broad-except msg = u'Got an error trying to create the dsn using the ' + \ u'service_name keyword. Trying with no keywords' self.logger.debug(msg) self.logger.debug(repr(e)) msg = u'input params are, host: {0}, port: {1}, inst {2}' msg = msg.format(host, port, serviceName) dsn = cx_Oracle.makedsn(host, port, serviceName).replace( u'SID', u'SERVICE_NAME') # pylint: disable=no-member self.logger.debug(u'dsn returned is: %s', dsn) self.connectParams(user, pswd, dsn)
def get_app(): info = [ ('Startup time', startup_time.strftime(app.config['DATETIME_FORMAT'])), ('Oracle client version', '.'.join((str(x) for x in clientversion()))), ('OS version ', platform()) ] with lock: if target_pool: info.append(('Session pools ', ', '.join(target_pool.keys()))) info.append(('Task worker', 'ON' if worker.is_alive() else 'OFF')) info.append(('Chat bot', 'ON' if bot.is_alive() else 'OFF')) if app.config['DND_HOURS']: info.append(('Do not disturb hours', f"from {app.config['DND_HOURS'][0]}:00" f" to {app.config['DND_HOURS'][1]}:00")) t = render_template('administration.html', info=info, active_connections=active_connections, task_pool=task_pool, task_id=request.args.get('task_id', '')) return t
import os import platform LOCATION = r"instantclient-basic-windows.x64-11.2.0.4.0\instantclient_11_2" print("ARCH:", platform.architecture()) print("FILES AT LOCATION:") for name in os.listdir(LOCATION): print(name) os.environ["PATH"] = LOCATION + ";" + os.environ["PATH"] import cx_Oracle print(cx_Oracle.version) cx_Oracle.clientversion() connection = cx_Oracle.connect(user="******", password="******", dsn="localhost/orcl3") print("Successfully connected to Oracle Database") cursor = connection.cursor() def test(cursor): for row in cursor.execute('select * from tablenames'): print(row)
def ConnDb(db_type='ora', **kwargs): if db_type=='ora': try: new_dsn = cx_Oracle.makedsn(host=kwargs['host'], port=kwargs['port'], service_name=kwargs['sid']) connection = cx_Oracle.connect(kwargs['user'], kwargs['passw'], new_dsn) connection.autocommit = False logging.info("> connected to Oracle, client {0}".format('.'.join([str(i) for i in cx_Oracle.clientversion()]))) return connection except cx_Oracle.DatabaseError as exc: error, = exc.args logging.error('{0}\n'.format(error.message.strip())) return False # elif db_type=='sqlite': # connection = sqlite.connect(":memory:") # logging.info(" > connected to SQLITE {0}".format(sqlite.sqlite_version)) # logging.info(' > ...OK\n') # return connection else: logging.error("Only ORACLE connection available in this version") return False
def GetClientVersion(): return cx_Oracle.clientversion()
# coding:utf-8 import cx_Oracle import os import json os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' print("cx_Oracle版本:", cx_Oracle.clientversion()) u'''Oracle数据库相关操作 连接数据库名称如:xxx 查询一组数据:oracle_getrows 查询某个字段对应的字符串:oracle_getstring 执行sql语句:oracle_sql 关闭oracle连接:oracle_close ''' dbname = {"user": "******", "pwd": "123456", "dsn": "127.0.0.1:1521/ORCL"} class OracleUtil(): def __init__(self): ''' 连接池方式''' self.db_info = dbname self.conn = OracleUtil.__getConnect(self.db_info) @staticmethod def __getConnect(db_info): ''' 静态方法,从连接池中取出连接''' try: con = cx_Oracle.connect(db_info['user'], db_info['pwd'], db_info['dsn'])
def client_version(): return cx_Oracle.clientversion()
class BackUp: '''class used to back up projects''' # use a separate config file to store these values # ------------------------------------------------------------------------------------ # set the connection properties TNS/DB connection # ------------------------------------------------------------------------------------ src_host = Settings.DB_HOST src_service = Settings.DB_SERVICE_NAME port = Settings.DB_PORT db_username = Settings.DB_USERNAME db_passwd = Settings.DB_PASSWORD dsn_tns = cx_Oracle.makedsn(src_host, port, service_name=src_service) # --------------------------------------------------------------------------------------- # Get Connection properties # --------------------------------------------------------------------------------------- client_version = cx_Oracle.clientversion() # --------------------------------------------------------------------------------------- # Folders and file locations # --------------------------------------------------------------------------------------- backup_path = Settings.BACKUP_PATH log_filepath = Settings.LOG_FILEPATH batch_file = Settings.BATCH_FILE log_filename = r'log.txt' # count of projects project_count = 0 def write_to_file(self, line, filename, path): ''' Helper function for writing lines into a file. Appends to an existing or creates a new one. Takes string to be written, the file name, and absolute path as arguments ''' if path and path[-1] != '\\': path = path + '\\' if line.strip() and filename: file = open(path + filename, 'a') # write to file file.write(str(line) + '\n') # print to console print(line) file.close() else: print('No valid file name or path provided.') def run_cmdline(self, file): ''' Run the .bat file that does the actual back up process. Takes the absolute path/file name as argument''' msg = '' try: if file.strip(): file_details = file.split('\\') filename = file_details[len(file_details) - 1] BackUp.write_to_file(self, f"\nRunning {filename} .....", self.log_filename, self.log_filepath) # run the subroutine subprocess.CompletedProcess = subprocess.run( [file], text=True, capture_output=True) # get the results from the run command ret_code = subprocess.CompletedProcess.returncode ret_error = subprocess.CompletedProcess.stderr ret_sdtout = subprocess.CompletedProcess.stdout if ret_code == 0: # successfully ran .bat file msg = f"End running {filename} file.....\n{ret_sdtout}" else: msg = f'\nReturn code: {ret_code}\n{ret_error}' else: msg = "Please provide a complete file location" except: msg = "There was an error running the .bat file" finally: BackUp.write_to_file(self, msg, self.log_filename, self.log_filepath) # ------------------------------------------------------------------------------------ # Connect to the database # get the list of projects # write it to a file # ------------------------------------------------------------------------------------ def conntect_to_db(self, username, passwd): self.username = username self.password = passwd msg = '' try: conn = cx_Oracle.connect(user=self.username, password=self.password, dsn=self.dsn_tns) msg = '\nSuccessfully connected to ' + self.src_service return conn except cx_Oracle.DatabaseError as ex: msg = '\nDatabase Error:\n ' + str(ex) except cx_Oracle.InterfaceError as ix: msg = '\nInterface Error:\n ' + str(ix) finally: BackUp.write_to_file(self, msg, self.log_filename, self.log_filepath) def get_projects(self): ''' Queries the list of changed/new projects and writes it to a file ''' self.project_count = 0 conn = BackUp.conntect_to_db(self, self.db_username, self.db_passwd) if conn: cursor = conn.cursor() cursor.execute(""" SELECT projectname, projectdescr, lastupddttm, lastupdoprid FROM SYSADM.PSPROJECTDEFN WHERE lastupddttm > (SELECT CREATED FROM V$DATABASE)""") for projectname, projectdescr, lastupddttm, lastupdoprid in cursor: projectname = projectname projectdescr = projectdescr lastupddttm = lastupddttm lastupdoprid = lastupdoprid # create projectlist.txt file BackUp.write_to_file(self, projectname, 'projectlist.txt', BackUp.backup_path) self.project_count += 1 # close thecursor object and connection cursor.close() conn.close() return self.project_count else: msg = 'No project list created. Connection was not established to the database' BackUp.write_to_file(self, msg, self.log_filename, self.log_filepath) return self.project_count # Copy projects to a file def backup_to_file(self): ''' Copy projects to file. Calls the run_cmdline() method only if project files were successfully created by the get_projects() method.''' BackUp.write_to_file( self, f'\n' + ('*' * 20) + 'BEGIN - ' + str(dttm.now(tz=None)) + ' ' + ('*' * 20), self.log_filename, self.log_filepath) queried_projects = BackUp.get_projects(self) BackUp.write_to_file(self, f'{queried_projects} projects to back up.', self.log_filename, self.log_filepath) if queried_projects and queried_projects >= 0: BackUp.run_cmdline(self, self.batch_file) BackUp.write_to_file( self, f'\n' + ('*' * 20) + 'END - ' + str(dttm.now(tz=None)) + ' ' + ('*' * 20), self.log_filename, self.log_filepath) # To implemented later def backup_to_database(self): ''' Copy projects to a database. To be implemented ''' pass
def main(argv): global totalFileSize # Parameter 1 - Import Directory # Parameter 2 - Subfolder Level # Remember the start time of the program start_time = time.clock() usage_string = " DocumentLoader.py -s <src> -r <recursive Level> -d <oracle_directory_path> -c <config_path>" path_name = '-' dest_name = '-' recursiveLevel = 0 config_path = 'dataLoader.conf' # -- Parameter from the config file oracle_port = '-' oracle_host = '-' oracle_service = '-' oracle_user = '******' oracle_pwd = '-' magicFilePath = '-' ingoreFileExt = [] # Path of the oracle Info Archive ora_dir_path = '-' try: opts, args = getopt.getopt(argv, "hs:d:r:d:c:", ["src=", "rec=", "dir=", "config="]) except getopt.GetoptError: print("usage: {0}".format(usage_string)) sys.exit(2) # read the parameter for opt, arg in opts: if opt == '-h': print("usage: {0}").format(usage_string) sys.exit() elif opt in ("-s", "--src"): path_name = arg elif opt in ("-d", "--dir"): ora_dir_path = arg elif opt in ("-c", "--config"): config_path = arg elif opt in ("-r", "--rec"): recursiveLevel = int(arg) # read the config file config = configparser.ConfigParser() # check if the file exits if os.path.exists(config_path) == False: # use normal configparser to write the template print("--" + 80 * "!") print("-- Error to read file {0}".format(config_path)) print("-- Error usage: {0}".format(usage_string)) config['DEFAULT'] = {'MagicFile': 'd:\\tools\\file\\bin\\file.exe', 'ignoreFileExt': '.iso'} config['ORACLE_DB_CONNECT'] = {'Host': 'localhost', 'Port': '1521', 'Service': 'ORCL', 'DB_User': '******', 'DB_Password': '******'} with open(config_path, 'w') as configfile: config.write(configfile) print("--" + 80 * "!") print("-- Info create Configuration Template :: {0}".format(config_path)) print("-- Info fillout the configuration file with your personal values and start again!") print("--" + 80 * "!") sys.exit(2) else: print("-- Info read config file {0}".format(config_path)) config.read(config_path) # Parameter of the application general_configuration = config['DEFAULT'] magicFilePath = general_configuration['MagicFile'] ingoreFileExtString = general_configuration['ignoreFileExt'] ingoreFileExt = str.split(str.replace(ingoreFileExtString, ' ', ''), ',') # Oracle DB Connect oracle_db_configuration = config['ORACLE_DB_CONNECT'] oracle_port = oracle_db_configuration['Port'] oracle_host = oracle_db_configuration['Host'] oracle_service = oracle_db_configuration['Service'] oracle_user = oracle_db_configuration['DB_User'] oracle_pwd = oracle_db_configuration['DB_Password'] # check if Directory exists and if the * is necessary # BUG ! if more then 1 then the * not match the documents on root level?? # FIX IT! # Source if os.path.isdir(path_name): if path_name.endswith(os.path.sep): path_name += ("*" + os.path.sep) * recursiveLevel path_name += "*.*" else: path_name += os.path.sep path_name += ("*" + os.path.sep) * recursiveLevel path_name += "*.*" else: print("-- Error :: 05 Source Directory (-s) {0} not found".format(path_name)) print("usage: {0}").format(usage_string) sys.exit(2) # connect to the database print("--" + 40 * "=") print("-- Info :: Oracle Client Library Version :: {0}".format(cx_Oracle.clientversion())) # get the connection to the database print("-- Info :: oracle_host {0} oracle_port {1} oracle_service {2} oracle_user {3} oracle_pwd ********".format( oracle_host, oracle_port, oracle_service, oracle_user, oracle_pwd)) connection = cx_Oracle.connect( oracle_user + '/' + oracle_pwd + '@' + oracle_host + ':' + oracle_port + '/' + oracle_service) # Version der DB ausgeben print("-- Info :: Oracle Database Version :: {0}".format(connection.version)) print("--" + 40 * "=") print("-- Info :: Environment Settings :: Language :: {0} - Char Set ::{1}".format(locale.getdefaultlocale()[0], locale.getdefaultlocale()[1])) print("--" + 40 * "=") print("-- Info :: Read all files from {0}".format(path_name)) print("-- Info :: Copy files to {0}".format(dest_name)) print("-- Info :: Not index this file types ::" + str(ingoreFileExt)) print("--" + 40 * "=") fileCount = 0 fileExistsCount = 0 dirCount = 0 dirPathList = [] totalFileSize = 0 # Get the list of all Files fileList = glob.glob(path_name) # remove Thumbs.db if exist from the list thumbsDBFile = "Thumbs.db" for file in fileList: if file.endswith(thumbsDBFile): fileList.remove(file) # Loop one read files in Import Directory for file in fileList: fileCount += 1 #read only some files if fileCount > 20: exit # do the work try: # check if directoy if ntpath.isdir(file): print("-- Info :: found dirctory {0} ::".format(file)) dirCount += 1 else: # get only the filename without the path filename = ntpath.basename(file) # get directory fileDirectoryName = ntpath.dirname(file) # get Create date fileAccessDate = datetime.datetime.fromtimestamp(ntpath.getatime(file)) fileModDate = datetime.datetime.fromtimestamp(ntpath.getmtime(file)) fileCreateDate = datetime.datetime.fromtimestamp(ntpath.getctime(file)) # get md5 hash md5checkSum = getMD5(file) # get File Type over file from external, Python Lib magic not working, error with magic file! # now I implement this stupid solution fileType = getFileType(file, " ", magicFilePath) # Call file with -i to get the mime Type fileMimeType = getFileType(file, "-i", magicFilePath) # get Extenstion fileExt = getFile_ext(filename) # getFileSize fileSize = os.path.getsize(file) # Remember for statistic setStatisticTotalSize(fileSize) # not add url files to the index # endswith(".url") if fileExt in ingoreFileExt: # encode the output with UTF-8 to avoid errors with stange things in filenames print("-- Info :: Not index this file types ::" + str(ingoreFileExt)) print("-- Info :: Not index this file::{0}".format(repr(filename.encode('utf-8')))) print("-- Info :: Not index this Dir::{0}".format(repr(fileDirectoryName.encode('utf-8')))) print("-- --") else: # record fileInfo = dict(filename=str(filename), filepath=str(fileDirectoryName), fileADate=fileAccessDate, fileMDate=fileModDate, fileCDate=fileCreateDate, md5=md5checkSum, fileBType=fileType, fileMtype=fileMimeType, fileExtention=fileExt, fileSize=fileSize) ##print("-- Index this Dir::{0}".format(repr(fileDirectoryName.encode('utf-8')))) # encode the output with UTF-8 to avoid errors with stange things in filenames ##print("-- Index this file ::{0}".format(repr(filename.encode('utf-8')))) # write to DB insertFileInfo(fileInfo, ora_dir_path, connection) except OSError as exception: if exception.errno != errno.EEXIST: print("-- Error :: Error read file :: see error {1}".format(file, sys.exc_info()[0])) # print statistics print("--" + 40 * "=") print("-- Finish with :: {0} files in {1} new directories".format(fileCount, dirCount)) print("-- The run needs :: {0:5.4f} seconds".format(time.clock() - start_time)) print("-- Read size :: {0:5.3f} MB".format(totalFileSize / 1024 / 1024)) print("--" + 40 * "=") # Close the DB Connection connection.close()
#------------------------------------------------------------------------------ # json_direct.py # Shows some JSON features of Oracle Database 21c. # See https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=ADJSN # # For JSON with older databases see json_blob.py #------------------------------------------------------------------------------ import sys import json import cx_Oracle as oracledb import sample_env connection = oracledb.connect(sample_env.get_main_connect_string()) client_version = oracledb.clientversion()[0] db_version = int(connection.version.split(".")[0]) # this script only works with Oracle Database 21 if db_version < 21: sys.exit("This example requires Oracle Database 21.1 or later. " "Try json_blob.py") # Create a table cursor = connection.cursor() cursor.execute(""" begin execute immediate 'drop table customers'; exception when others then
# Portions Copyright 2001-2007, Computronix (Canada) Ltd., Edmonton, Alberta, # Canada. All rights reserved. #------------------------------------------------------------------------------ """Runs all defined unit tests.""" import cx_Oracle import os import sys import TestEnv import unittest # display version of cx_Oracle and Oracle client for which tests are being run print("Running tests for cx_Oracle version", cx_Oracle.version, "built at", cx_Oracle.buildtime) print("File:", cx_Oracle.__file__) print("Client Version:", ".".join(str(i) for i in cx_Oracle.clientversion())) sys.stdout.flush() # verify that we can connect to the database and display database version connection = TestEnv.GetConnection() print("Server Version:", connection.version) sys.stdout.flush() # define test cases to run moduleNames = [ "Module", "Connection", "Cursor", "CursorVar", "DateTimeVar", "DbTypes", "DMLReturning", "Error", "IntervalVar", "LobVar", "LongVar", "NCharVar", "NumberVar", "ObjectVar", "SessionPool", "StringVar", "TimestampVar", "AQ", "BulkAQ", "Rowid", "Subscription" ] clientVersion = cx_Oracle.clientversion()
def create_connection(self): service_check_tags = ['server:%s' % self._server] service_check_tags.extend(self._tags) try: # Check if the instantclient is available cx_Oracle.clientversion() except cx_Oracle.DatabaseError as e: # Fallback to JDBC use_oracle_client = False self.log.debug( 'Oracle instant client unavailable, falling back to JDBC: %s', e) connect_string = self.JDBC_CONNECT_STRING.format( self._server, self._service) else: use_oracle_client = True self.log.debug('Running cx_Oracle version %s', cx_Oracle.version) connect_string = self.CX_CONNECT_STRING.format( self._user, self._password, self._server, self._service) try: if use_oracle_client: connection = cx_Oracle.connect(connect_string) elif JDBC_IMPORT_ERROR: self.log.error( "Oracle client is unavailable and the integration is unable to import JDBC libraries. You may not " "have the Microsoft Visual C++ Runtime 2015 installed on your system. Please double check your " "installation and refer to the Datadog documentation for more information." ) raise JDBC_IMPORT_ERROR else: try: if jpype.isJVMStarted( ) and not jpype.isThreadAttachedToJVM(): jpype.attachThreadToJVM() jpype.java.lang.Thread.currentThread( ).setContextClassLoader( jpype.java.lang.ClassLoader.getSystemClassLoader()) connection = jdb.connect(self.ORACLE_DRIVER_CLASS, connect_string, [self._user, self._password], self._jdbc_driver) except Exception as e: if "Class {} not found".format( self.ORACLE_DRIVER_CLASS) in str(e): msg = """Cannot run the Oracle check until either the Oracle instant client or the JDBC Driver is available. For the Oracle instant client, see: http://www.oracle.com/technetwork/database/features/instant-client/index.html You will also need to ensure the `LD_LIBRARY_PATH` is also updated so the libs are reachable. For the JDBC Driver, see: http://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html You will also need to ensure the jar is either listed in your $CLASSPATH or in the yaml configuration file of the check. """ self.log.error(msg) raise self.log.debug("Connected to Oracle DB") self.service_check(self.SERVICE_CHECK_NAME, AgentCheck.OK, tags=service_check_tags) except Exception as e: self.service_check(self.SERVICE_CHECK_NAME, AgentCheck.CRITICAL, tags=service_check_tags) self.log.error(e) raise self._connection = connection
import cx_Oracle if __name__ == "__main__": name = raw_input("Enter Lan ID ") HC_ID = raw_input("Enter HC_ID ") BATCH = raw_input("Enter BATCH_ID ") #os.startfile('"C:\TEMP\putty\putty.exe"') #putty = putty.exe #if putty: # print "etlt1.bankofthewest.com" SQL = "SELECT HC_ID, BATCH_ID, HC_REPORT FROM AUDITDATA.HC_RUN WHERE HC_ID = 5510501 AND BATCH_ID = 551 ;" print("Python version: " + platform.python_version()) print("cx_Oracle version: " + cx_Oracle.version) print("Oracle client: " + str(cx_Oracle.clientversion()).replace(', ', '.')) pwd = getpass.getpass() connection = cx_Oracle.connect('' + name + '@TEDW/etlt1.bankofthewest.com') print("Oracle DB version: " + connection.version) print("Oracle client encoding: " + connection.encoding) print 'sqlplus' print SQL import conval time.sleep(60) connection.close()
import cx_Oracle as ora import os print(ora.clientversion()) if __name__ == '__main__': # 文字コードの指定を環境変数で行う os.environ["NLS_LANG"] = "JAPANESE_JAPAN.JA16SJISTILDE" try: tns = ora.makedsn("localhost", 1521, "XE") con = ora.connect("SAMPLE", "SAMPLE", tns) cur = con.cursor() # SELECT文 cur.execute("select * from tbl_sample") rows = cur.fetchall() for r in rows: print(r) print(r[0], ":", r[1]) # INSERT文 cur.execute("insert into tbl_sample values (:id, :name)", id=11, name="user11") con.commit() # SELECT文 cur.execute("select * from tbl_sample")
# PART 1 - connecting to Oracle with cx_Oracle # Let code speak for itself! # ==================================================== import sys import getpass import platform import cx_Oracle # ----------------------------------------------------- # Display versions of python, cx_Oracle module # and Oracle client being used... # ----------------------------------------------------- print ("Python version: " + platform.python_version()) print ("cx_Oracle version: " + cx_Oracle.version) print ("Oracle client: " + str(cx_Oracle.clientversion()).replace(', ','.')) connection = cx_Oracle.connect("tom/oracle@XENITH") print ("Oracle DB version: " + connection.version) print ("Oracle client encoding: " + connection.encoding) connection.close() """ #----------------------------------------------------------------------- # How to connect to database #----------------------------------------------------------------------- # Basic connection using Oracle tns alias (specified in # in tnsnames.ora, ldap or in obsolete Oracle Names server) connection = cx_Oracle.connect("tom/oracle@XENITH") # By asking user for the password (suitable for
# myscript.py import cx_Oracle # Use following command to initialize lib dir. cx_Oracle.init_oracle_client(lib_dir="/Users/xuansong/.bin/instantclient_19_8") # http://www.dominicgiles.com/blog/files/a4698a719e20e8b3b9542ed43d51e0a2-178.html # dsn_tns = cx_Oracle.makedsn('Host Name', 'Port Number', service_name='Service Name') # conn = cx_Oracle.connect(user=r'User Name', password='******', dsn=dsn_tns) # Read how to find SERVER_NAME with sql # select value from v$parameter where name like '%service_name%'; # https: // www.stechies.com/difference-between-oracle-sids-and-oracle-service-names/ print("Client version: " + str(cx_Oracle.clientversion())) # cx_Oracle.makedsn(host, port, sid=None, service_name=None, region=None, sharding_key=None, super_sharding_key=None) # Either use server name or sid # Option 1: # dsn_tns = cx_Oracle.makedsn('localhost', '1521', service_name='ORCLCDB.localdomain') # Option 2: dsn_tns = cx_Oracle.makedsn('localhost', '1521', sid='ORCLCDB') conn = cx_Oracle.connect(user=r'hr', password='******', dsn=dsn_tns) print("Connection version:"+ conn.version) # Connect as user "hr" with password "welcome" to the "orclpdb1" service running on this computer. # connection = cx_Oracle.connect("hr", "hr", "localhost/ORCLCDB") # cursor()??
#------------------------------------------------------------------------------ # JSON.py # Shows some JSON features of Oracle Database 21c. # See https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=ADJSN # # For JSON with older databases see JSONBLOB.py #------------------------------------------------------------------------------ import sys import json import cx_Oracle import sample_env connection = cx_Oracle.connect(sample_env.get_main_connect_string()) client_version = cx_Oracle.clientversion()[0] db_version = int(connection.version.split(".")[0]) # this script only works with Oracle Database 21 if db_version < 21: sys.exit("This example requires Oracle Database 21.1 or later. " "Try JSONBLOB.py") # Create a table cursor = connection.cursor() cursor.execute(""" begin execute immediate 'drop table customers'; exception when others then
"uLobVar", "LongVar", "uLongVar", "NCharVar", "NumberVar", "uNumberVar", "ObjectVar", "uObjectVar", "SessionPool", "uSessionPool", "StringVar", "uStringVar", "TimestampVar", "uTimestampVar" ] if cx_Oracle.clientversion()[0] >= 12: moduleNames.insert(0, "uArrayDMLBatchError") moduleNames.insert(0, "ArrayDMLBatchError") class BaseTestCase(unittest.TestCase): def setUp(self): global cx_Oracle, TestEnv self.connection = cx_Oracle.connect(TestEnv.USERNAME, TestEnv.PASSWORD, TestEnv.TNSENTRY) self.cursor = self.connection.cursor() self.cursor.arraysize = TestEnv.ARRAY_SIZE def tearDown(self): del self.cursor del self.connection
Scrive quanto contenuto in p_data nella tabella oracle UT_QREADER """ # Apertura del cursore e scrittura nella tabella di lavoro fatta con nome utente e il dato appena letto v_oracle_cursor = v_oracle_db.cursor() # Controllo se record utente esiste già v_istruzione_sql = "select count(*) from UT_QREADER where USER_CO = '" + getpass.getuser( ) + "' and CODIC_CO = '" + p_data + "'" v_oracle_cursor.execute(v_istruzione_sql) # Se esiste aggiorno if v_oracle_cursor.fetchone()[0] > 0: v_istruzione_sql = "update UT_QREADER set CODIC_CO = '" + p_data + "'" # altrimenti aggiungo else: v_istruzione_sql = "insert into UT_QREADER values('" + getpass.getuser( ) + "','" + p_data + "')" print(v_istruzione_sql) v_oracle_cursor.execute(v_istruzione_sql) # Committo v_oracle_db.commit() # Chiudo il cursore v_oracle_cursor.close() print('Avvio test....') print(cx_Oracle.clientversion()) connect_string = "SMILE/SMILE@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(" "COMMUNITY=TCP)(PROTOCOL=TCP)(Host=10.0.4.11)(Port=1521)))(" "CONNECT_DATA=(SID=SMIG)))" print(connect_string) v_oracle_db = cx_Oracle.connect(connect_string) scrivi_in_ut_qreader('test qreader') print('Fine test premere invio per continuare...') a = input()
def check_version(self): '''查看oracle版本''' print(cx_Oracle.clientversion())
#!/usr/bin/python # encoding: utf-8 # -*- coding: utf8 -*- """ Created by PyCharm. File: LinuxBashShellScriptForOps:pyConnectOracle.py User: Liuhongda Create Date: 2016/11/24 Create Time: 10:39 """ import cx_Oracle as orcl print(orcl.clientversion()) username = "******" password = "******" host = "localhost" port = "1521" sid = "xe" dsn = orcl.makedsn(host, port, sid) con = orcl.connect(username, password, dsn) cursor = con.cursor() sql = "SELECT * FROM HELP" cursor.execute(sql) result = cursor.fetchall() print("Total: " + str(cursor.rowcount)) for row in result: print(row) cursor.close() con.close()
from __future__ import print_function import cx_Oracle import imp import os import sys import TestEnv import unittest inSetup = (os.path.basename(sys.argv[0]).lower() == "setup.py") print("Running tests for cx_Oracle version", cx_Oracle.version, "built at", cx_Oracle.buildtime) print("File:", cx_Oracle.__file__) print("Client Version:", ".".join(str(i) for i in cx_Oracle.clientversion())) sys.stdout.flush() connection = cx_Oracle.Connection(TestEnv.MAIN_USER, TestEnv.MAIN_PASSWORD, TestEnv.CONNECT_STRING, encoding=TestEnv.ENCODING, nencoding=TestEnv.NENCODING) print("Server Version:", connection.version) sys.stdout.flush() if len(sys.argv) > 1 and not inSetup: moduleNames = [os.path.splitext(v)[0] for v in sys.argv[1:]] else: moduleNames = [ "Connection", "Cursor", "CursorVar", "DateTimeVar", "DMLReturning",
import TestEnv inSetup = (os.path.basename(sys.argv[0]).lower() == "setup.py") if len(sys.argv) > 1 and not inSetup: moduleNames = [os.path.splitext(v)[0] for v in sys.argv[1:]] else: moduleNames = [ "Connection", "uConnection", "Cursor", "uCursor", "CursorVar", "uCursorVar", "DateTimeVar", "uDateTimeVar", "IntervalVar", "uIntervalVar", "LobVar", "uLobVar", "LongVar", "uLongVar", "NCharVar", "NumberVar", "uNumberVar", "ObjectVar", "uObjectVar", "SessionPool", "uSessionPool", "StringVar", "uStringVar", "TimestampVar", "uTimestampVar" ] if cx_Oracle.clientversion()[0] >= 12: moduleNames.insert(0, "uArrayDMLBatchError") moduleNames.insert(0, "ArrayDMLBatchError") moduleNames.append("BooleanVar") class BaseTestCase(unittest.TestCase): def setUp(self): global cx_Oracle, TestEnv self.connection = cx_Oracle.connect(TestEnv.USERNAME, TestEnv.PASSWORD, TestEnv.TNSENTRY) self.cursor = self.connection.cursor() self.cursor.arraysize = TestEnv.ARRAY_SIZE def tearDown(self): del self.cursor
import cx_Oracle print 'cx_Oracle client version:', cx_Oracle.clientversion() connection = cx_Oracle.connect('gins_user', 'report', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=seaputmsdb03.amer.gettywan.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TEAMSREP)))') cursor = connection.cursor() cursor.execute(""" select ra.* from report_asset_alias raa left join report_assets ra on (raa.UOI_ID = ra.UOI_ID) where raa.ALIAS_TYPE='iStockphoto' and raa.PORTAL_ALIAS = 'Y' and raa.ALIAS = :asset_id""", {'asset_id': str(44)}) values = list(cursor.fetchone()) columns = [x[0] for x in cursor.description] pairs = zip(columns, values) for pair in pairs: print pair #print zip(columns, values) print 'done'
if sys.version_info[0] < 3: moduleNames.extend([ "uConnection", "uCursor", "uCursorVar", "uDateTimeVar", "uIntervalVar", "uLobVar", "uLongVar", "uNumberVar", "uObjectVar", "uSessionPool", "uStringVar", "uTimestampVar" ]) clientVersion = cx_Oracle.clientversion() if clientVersion[:2] >= (12, 1): moduleNames.append("BooleanVar") moduleNames.append("Features12_1") class BaseTestCase(unittest.TestCase): def setUp(self): import cx_Oracle import TestEnv self.connection = cx_Oracle.connect(TestEnv.USERNAME, TestEnv.PASSWORD, TestEnv.TNSENTRY, encoding = TestEnv.ENCODING, nencoding = TestEnv.NENCODING) self.cursor = self.connection.cursor() self.cursor.arraysize = TestEnv.ARRAY_SIZE
#!/usr/bin/env python #-*- coding: UTF-8 -*- # import cx_Oracle as orcl if __name__ == "__main__": print(orcl.clientversion()) # conn = cx_Oracle.connect('uopsett_b_xz/[email protected]/xz_test') username = "******" passwd = "uopsett" host = "192.168.109.2" port = "1521" sid = "xzttest" dsn = orcl.makedsn(host, port, sid) con = orcl.connect(username, passwd, dsn) cursor = con.cursor() sql = "SELECT * FROM tp_dealtime" cursor.execute(sql); result = cursor.fetchall() print("Total: " + str(cursor.rowcount)) for row in result: print(row) print row[1] cursor.close() con.close() print(orcl.Date(2015,3,13))
#------------------------------------------------------------------------------ # versions.py (Section 1.6) #------------------------------------------------------------------------------ #------------------------------------------------------------------------------ # Copyright (c) 2017, 2018, Oracle and/or its affiliates. All rights reserved. #------------------------------------------------------------------------------ from __future__ import print_function import cx_Oracle import db_config con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn) print(cx_Oracle.version) print("Database version:", con.version) print("Client version:", cx_Oracle.clientversion())
# coding:utf-8 import cx_Oracle print cx_Oracle.clientversion() username = "******" #oracle 用户名 passwd = "demo" #oracle密码 host = "192.168.31.159" port = "1521" sid = "dbsrv2" dns = cx_Oracle.makedsn(host,port,sid) con = cx_Oracle.connect(username,passwd,dns) cur = con.cursor() sq = '''select upper(f.tablespace_name) "表空间名", d.tot_grootte_mb "表空间大小(m)", d.tot_grootte_mb - f.total_bytes "已使用空间(m)", to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100, 2), '990.99') "使用比", f.total_bytes "空闲空间(m)", f.max_bytes "最大块(m)" from (select tablespace_name, round(sum(bytes) / (1024 * 1024), 2) total_bytes, round(max(bytes) / (1024 * 1024), 2) max_bytes from sys.dba_free_space group by tablespace_name) f, (select dd.tablespace_name, round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb from sys.dba_data_files dd group by dd.tablespace_name) d where d.tablespace_name = f.tablespace_name order by 4 desc; '''