Python interface to Oracle Database conforming to the Python DB API 2.0 specification.

Overview

cx_Oracle version 8.2 (Development)

cx_Oracle is a Python extension module that enables access to Oracle Database. It conforms to the Python database API 2.0 specification with a considerable number of additions and a couple of exclusions. See the homepage for a feature list.

cx_Oracle 8 has been tested with Python versions 3.6 through 3.9. You can use cx_Oracle with Oracle 11.2, 12c, 18c, 19c and 21c client libraries. Oracle's standard client-server version interoperability allows connection to both older and newer databases. For example Oracle 19c client libraries can connect to Oracle Database 11.2. Older versions of cx_Oracle may work with older versions of Python.

Installation

See cx_Oracle Installation.

Documentation

See the cx_Oracle Documentation and Release Notes.

Samples

See the /samples directory and the tutorial. You can also look at the scripts in cx_OracleTools and the modules in cx_PyOracleLib.

Help

Issues and questions can be raised with the cx_Oracle community on GitHub or on the mailing list.

Tests

See /test.

Contributing

See CONTRIBUTING

License

cx_Oracle is licensed under a BSD license which you can find here.

Comments
  • Error: DPI-1047 using Instant Client on macOS Catalina 10.15

    Error: DPI-1047 using Instant Client on macOS Catalina 10.15

    I recently upgrade my mac to Mac OS Catalina Beta version. After that cx-Oracle is not working. Fails with error message below.

    cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "dlopen(libclntsh.dylib, 1): image not found". See https://oracle.github.io/odpi/doc/installation.html#macos for help

    Version Python 3.7.4 cx-Oracle 7.2.0

    This looks to me due to Apple dropping support of 32-bit apps. Can you confirm?

    question 
    opened by nchillal 63
  • ORA-03124 Two-task internal error running Django migrations

    ORA-03124 Two-task internal error running Django migrations

    1. What is your version of Python? Is it 32-bit or 64-bit?

    Python 3.6.3 (64-bit) on CentOS 6.10. This is from CentOS SCL repository:

    $ rpm -q rh-python36-python 
    rh-python36-python-3.6.3-3.el6.x86_64
    

    2. What is your cx_Oracle version?

    cx-Oracle 6.4.1 - we have reproduced with both the manylinux1 build and a build created as follows:

    pip uninstall -y cx-Oracle
    pip install --no-cache --index https://pypi.org/simple --no-binary cx-Oracle cx-Oracle==6.4.1
    

    We found that this no longer occurs with home-built versions of cx-Oracle 5.2.1 and cx-Oracle 5.3.

    3. What exact command caused the problem (e.g. what command did you try to install with)?

    ./manage.py migrate

    Who were you logged in as?

    davisda4, but this is also occurring in our CI/CD environment.

    4. What error(s) you are seeing?

    During Django migrations, we see a traceback from django.db.utils.DatabaseError: ORA-03124: two-task internal error. This Django exception class is a wrapper for multiple database vendor specific errors. We are assuming the Oracle error number is of sufficient detail. See the attached file for full log information:

    fulldebug.log

    The pid/tid come from %(process)d and %(thread)d in the python logging formatter. Analyzing django/db/backends/utils.py confirms that the last SQL statement in this full log was the one on which the problem occurred. Note that there are no migrations, and so this is stock Django 1.11.20.

    5. What OS (and version) is Python executing on?

    CentOS 6.10

    6. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where is it installed?

    12.1.0.1

    Note that Django 1.11.20 was used. I cannot easily update to cx_Oracle 7+ without also updating to Django 2.2, which my organization is not yet ready to do.

    I am not sure how it was installed, but the instantclient rpms are not installed /usr/nlm/share/oracle/12.1.0.1 - this is a read-only NFS mount

    7. What is your Oracle Database version?

    Oracle 18c

    8. What is the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) set to? On macOS, what is in ~/lib?

    $ echo $LD_LIBRARY_PATH
    /opt/rh/rh-python36/root/usr/lib64:/usr/nlm/share/oracle/12.1.0.1/lib
    

    9. What Oracle environment variables did you set? How exactly did you set them?

    This is an expert from my .bashrc:

    ORACLE_HOME=/usr/nlm/share/oracle/12.1.0.1
    TNS_ADMIN=/usr/nlm/share/etc/tns_admin
    NLS_LANG="AMERICAN_AMERICA.UTF8"
    
    # Put oracle's pelr libraires into PERL5LIB
    if [ -z "$ORAPERL" ]; then
        ORAPERL=/usr/nlm/share/oracle/12.1.0.1/perl
        PERL5LIB="$ORAPERL/lib/5.14.1:$ORAPERL/lib/site_perl/5.14.1";
        export PERL5LIB
    fi
    export ORACLE_SID
    export ORACLE_HOME
    export ORACLE_PERL
    export PERL5LIB
    export TNS_ADMIN
    export NLS_LANG
    
    if [[ -z "$LD_LIBRARY_PATH" ]]; then
        LD_LIBRARY_PATH=$ORACLE_HOME/lib
    else
        [[ $LD_LIBRARY_PATH =~ $ORACLE_HOME ]] || LD_LIBRARY_PATH="$ORACLE_HOME/lib:$LD_LIBRARY_PATH"
    fi
    export LD_LIBRARY_PATH
    

    10. Do you have a small, single Python script that immediately runs to show us the problem?

    Not as yet.

    11. Additional Notes

    We also submitted through our vendor support channel as SR 3-20049808601, with title "Client connection failing on with ORA-03124 Two-task internal error".

    question 
    opened by danizen 38
  • cx_Oracle.DatabaseError: DPI-1047

    cx_Oracle.DatabaseError: DPI-1047

    Answer the following questions:

    What is your version of Python? Is it 32-bit or 64-bit? 3.6.1, 64-bit.

    What is your version of cx_Oracle? 5.0.3

    What is your OS and version? Windows 7.

    What environment variables did you set? How exactly did you set them? Just the environment variables to execute Python. How I set for cx_Oracle?

    What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as? When I tried to execute a simple script to check the Oracle connection.

    What error(s) you are seeing? cx_Oracle.DatabaseError: DPI-1047: 32 bit Oracle Client library cannot be loaded: Não foi possível encontrar o módulo especificado. See https://oracle.github.io/odpi/doc/installation.html#windows for help

    I'm trying to connect the Oracle in Python, I've installed the cx_Oracle (pip install cx_Oracle). Someone could help please? I read some threads here and googled this error information but nothing helps at the moment.

    install & configuration 
    opened by grazielegs 36
  • DPI-1049 symbol OCIClientVersion not found in OCI library

    DPI-1049 symbol OCIClientVersion not found in OCI library

    I've installed Python, Oracle Instant Client (12.2) and cx_Oracle (using pip) onto a 32-bit windows 7 machine and am trying to connect my python instance to my Oracle database. When I try to connect using the following code

    import cx_Oracle
    cx_Oracle.clientversion()
    
    db1 = cx_Oracle.connect('username/pw//hostURL:port/serviceName')
    

    , I receive the following error: "DatabaseError: DPI-1049: symbol OCIClientVersion not found in OCI library"

    I've installed the appropriate instant client containing the .dll files needed. I've set the PATH variable to point to this folder. Could you please advise how to connect?

    Thank you!

    install & configuration 
    opened by nmmassey2 33
  • Oracle.ProgrammingError: positional and named binds cannot be intermixed (==6.4)

    Oracle.ProgrammingError: positional and named binds cannot be intermixed (==6.4)

    Discovered in unit testing due to no version pinning in the project's Pipfile. Locally (6.3.1) remains functional, while all database set up tasks fail in our CI pipeline on cx_Oracle==6.4.

    Binding errors stating that positional and named binds cannot be intermixed when there are no positional binds being used.

    Answer the following questions:

    1. What is your version of Python? Is it 32-bit or 64-bit?
    $ python -V
    Python 3.6.5
    $ python -c 'import sys; print(sys.maxsize)'
    9223372036854775807  # 64bit
    
    1. What is your version of cx_Oracle?

    Locally:

    $ python -c 'import cx_Oracle; print(cx_Oracle.__version__)'
    6.3.1
    

    Remote (issue causing)

    $ python -c 'import cx_Oracle; print(cx_Oracle.__version__)'
    6.4
    
    1. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where is it installed?
    $ ls /opt/oracle/
    instantclient_12_2
    
    1. What is your version of the Oracle Database?
    $ docker ps
    CONTAINER ID        IMAGE                                            COMMAND                  CREATED             STATUS                    PORTS                                 NAMES
    ffe765d011a3        store/oracle/database-enterprise:12.2.0.1-slim   "/bin/sh -c '/bin/ba…"   29 minutes ago      Up 29 minutes (healthy)   5500/tcp, 127.0.0.1:XXXXX->1521/tcp   inspiring_shtern
    
    1. What is your OS and version?
    $ uname -a
    Darwin Jordens-MacBook-Pro.local 17.6.0 Darwin Kernel Version 17.6.0: Tue May  8 15:22:16 PDT 2018; root:xnu-4570.61.1~1/RELEASE_X86_64 x86_64
    $ system_profiler SPSoftwareDataType
    Software:
    
        System Software Overview:
    
          System Version: macOS 10.13.5 (17F77)
          Kernel Version: Darwin 17.6.0
          Boot Volume: Macintosh HD
          Boot Mode: Normal
          Computer Name: Jorden’s MacBook Pro
          User Name: Jorden Kreps (jordenkreps)
          Secure Virtual Memory: Enabled
          System Integrity Protection: Enabled
          Time since boot: 6 days 1:57
    
    1. What compiler version did you use? For example, with GCC, run gcc --version.
    $ gcc --version
    Configured with: --prefix=/Library/Developer/CommandLineTools/usr --with-gxx-include-dir=/usr/include/c++/4.2.1
    Apple LLVM version 9.1.0 (clang-902.0.39.2)
    Target: x86_64-apple-darwin17.6.0
    Thread model: posix
    InstalledDir: /Library/Developer/CommandLineTools/usr/bin
    
    1. What environment variables did you set? How exactly did you set them?

    N/A

    1. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?
    $ pytest --cov <package_name>
    
    1. What error(s) you are seeing?

    6.3.1

    ...
    <package_name>/tests/test_util.py ...............                            [100%]
    ...
    

    6.4

    ...
    <package_name>/tests/test_util.py EEEEEE.........                            [100%]
    ...
    
    self = <sqlalchemy.dialects.oracle.cx_oracle.OracleDialect_cx_oracle object at 0x7f977ca76550>
    cursor = <cx_Oracle.Cursor on <cx_Oracle.Connection to SYSTEM@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=XXXXX))(CONNECT_DATA=(SID=ORCLCDB)(SERVICE_NAME=ORCLPDB1.localdomain)))>>
    statement = 'SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name'
    parameters = {'name': 'MYTABLE', 'schema_name': 'MYUSER'}
    context = <sqlalchemy.dialects.oracle.cx_oracle.OracleExecutionContext_cx_oracle object at 0x7f977cb570b8>
    
        def do_execute(self, cursor, statement, parameters, context=None):
    >       cursor.execute(statement, parameters)
    E       cx_Oracle.ProgrammingError: positional and named binds cannot be intermixed
    

    Pinning to 6.3.1 resolves the issue.

    bug 
    opened by jakereps 28
  • Python3.9.1 connect Oracle10.2 using cx_Oracle8.2 can not display chinese in SELECT results (grabled chinese characters)

    Python3.9.1 connect Oracle10.2 using cx_Oracle8.2 can not display chinese in SELECT results (grabled chinese characters)

    1. What versions are you using?

    Oracle10.2

    Also run Python and show the output of:

    grabled chinese characters (I've already tried all kinds of methods I could found but I could not solve this problem)

    1. Describe the problem

    Python3.9.1 connect Oracle10.2 using cx_Oracle8.2 can not display chinese correctly in SELECT results (grabled chinese characters)

    1. Include a runnable Python script that shows the problem.

    import cx_Oracle

    db = cx_Oracle.connect(‘ygx’, 'password', '111.111.111.101:1521/orcl') cursor = db.cursor() SQL = """SELECT * FROM DEPT_DICT""" cursor.execute(SQL) result_list = cursor.fetchall() print (result_list) image

    question inactive 
    opened by Dinosaurlovetoeatgrass 26
  • setinputsizes(CLOB) is extremely slow, do we need to setinputsizes(CLOB)?

    setinputsizes(CLOB) is extremely slow, do we need to setinputsizes(CLOB)?

    Hey Anthony -

    today on setinputsizes(), apparently CLOB is incredibly slow. Omitting it, an INSERT seems to proceed without issue, and I've tried somewhat larger text sizes up to a few dozen megs in size and it still round trips correctly. The only thing setinputiszes(CLOB) seems to get me is that I can persist a blank string '' without it turning into NULL.

    Can I get some clarity on this one? test program below produces:

    0.05559845399693586
    11.789099370995245
    
    

    e.g., calling setinputsizes(CLOB) makes a modest insert of 1000 rows 200 times slower, with cx_Oracle 6.3.

    import cx_Oracle
    import decimal
    
    conn = cx_Oracle.connect(
        user="scott",
        password="tiger",
        dsn=cx_Oracle.makedsn(
            "oracle1120", 1521,
        )
    )
    
    def setup():
        cursor = conn.cursor()
        try:
            cursor.execute("drop table t")
        except:
            pass
    
        cursor.execute("""
        CREATE TABLE t (
            x CLOB
        )
        """)
        return cursor
    
    
    
    def go1(cursor):
        cursor.executemany(
            "insert into t (x) values (:x)",
            [
                {"x": "text %s" % i} for i in range(1000)
            ]
        )
    
    def go2(cursor):
        cursor.setinputsizes(x=cx_Oracle.CLOB)
        cursor.executemany(
            "insert into t (x) values (:x)",
            [
                {"x": "text %s" % i} for i in range(1000)
            ]
        )
    
    import timeit
    
    cursor = setup()
    print(timeit.timeit("go1(cursor)", "from __main__ import go1, cursor", number=5))
    
    cursor = setup()
    print(timeit.timeit("go2(cursor)", "from __main__ import go2, cursor", number=5))
    
    
    
    question 
    opened by zzzeek 26
  • Possible memory leak?

    Possible memory leak?

    1. What versions are you using?
    • Oracle database Version 19.4.0.0.0
    • Oracle instantclient version: 21.3.0.0.0
    • OS version: RHEL 7.9 x64
    >>> print("platform.platform:", platform.platform())
    platform.platform: Linux-3.10.0-1160.36.2.el7.x86_64-x86_64-with-glibc2.17
    >>> print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
    sys.maxsize > 2**32: True
    >>> print("platform.python_version:", platform.python_version())
    platform.python_version: 3.9.5
    >>> print("cx_Oracle.version:", cx_Oracle.version)
    cx_Oracle.version: 8.2.1
    >>> print("cx_Oracle.clientversion:", cx_Oracle.clientversion())
    cx_Oracle.clientversion: (21, 3, 0, 0, 0)
    
    1. Describe the problem

    First please take a look at the code snippet in (3).

    The issue is, the code can run, just it uses a LOT of memory. My guesstimate of the final resultant rows object should be no more than 2 GB in size but the script uses up all my 30 GB memory and gets terminated by OS (i.e., I don't know exactly how much memory the script would like to allocate since it is not given the chance). Before the script gets killed, it asks for about 200 MBytes of memory each second so my 30 GB memory does not last for too long...At the moment it is killed, usually it fetches no more than 50% of the rows needed.

    Here comes the really strange thing--I can avoid the above issue by commenting out rows.append(row). That is, if I don't save the fetched rows and just keeps reading and discarding, the script becomes totally fine...

    One may say: would it be possible that there is a mismatch between rows' actual size and cx_Oracle's idea of their size? This is unlikely, I tried printing cursor.description and the result is the following: [('Date', <class 'cx_Oracle.DATETIME'>, 23, None, None, None, 0), ('ID', <class 'cx_Oracle.STRING'>, 9, 9, None, None, 0), ('KeyName', <class 'cx_Oracle.STRING'>, 53, 53, None, None, 0), ('ValueName', <class 'cx_Oracle.NUMBER'>, 127, None, 126, -127, 0)]. Looks normal.

    1. Include a runnable Python script that shows the problem.
    
    dsn = cx_Oracle.makedsn(host=[host], port=[port], sid=[sid])
    con = cx_Oracle.connect(user=[username], password=[password], dsn=dsn)
    rows = []
    with con.cursor() as cursor:
        cursor.arraysize = 100_000 # Only effective when using cx_Oracle
        sql = """
                SELECT "Date", "ID", "KeyName", "ValueName"
                FROM "A_Table"
                WHERE ("A_Table"."Date" >= '07-SEP-20' AND 
                       "A_Table"."Date" <= '07-SEP-21')
        """
        # So I just to get everything from the last year
        cursor.execute(sql)
        while True:
            fetched_rows = cur.fetchmany(num_rows)
            if not fetched_rows:
                break
             rows.extend(fetched_rows)
    
    1. Extra Attempts

    What if I do this instead:

    while True:
        fetched_rows = cur.fetchmany(num_rows)
        if not fetched_rows:
            break
        dcfr = copy.deepcopy(fetch_rows) 
        fetch_rows = None # we keep a deepcopy of fetched_rows, hoping it can release the object reference and space.
        rows.extend(dcfr)
        gc.collect() # Manual garbage collection to avoid any delay
            
    

    No, it won't solve the issue. I also tried using SQL*PLUS: https://dba.stackexchange.com/questions/299426/the-expected-read-speed-of-sqlplus .It does NOT suffer from the same issue and it can reach a local read speed at 50MBytes/s.

    Also, I am suspecting this is not a cx_Oracle bug since pyodbc suffers from the same issue. Can check out my question on StackExchange (mostly the same as this one): https://dba.stackexchange.com/questions/299444/possible-memory-leak-of-cx-oracle-pyodbc-oracle-instant-client

    I canNOT rule out the possibility that it is some machine-specific settings that are wrong , feel free to ask me providing more info, such as content of sqlnet.ora, etc.

    Any idea?...

    question 
    opened by alex-lt-kong 25
  • cx_Oracle unaware DB server gone away

    cx_Oracle unaware DB server gone away

    For general questions:

    Connected to Primary Oracle RAC with Active DataGuard (secondary is Read-Only)

    If Primary listener goes away cx_Oracle will attempt a query, but time out after 180 seconds. Can we reduce that?

    In this case, a backhoe cut some fiber, but easily reproduced with: iptables -A OUTPUT -d xxx.xxx.xxx.xxx -j DROP

    Ultimately we'd like to react more quickly if a server is dead. When we do detect a reconnect scenario, SQLAlchemy's timeout does not appear to affect cx_Oracle connections so we perform a simple TCP connect first. This is fine, but the larger issue is when a connection is established, but the DB server is gone.

    Answer the following questions:

    1. What is your version of Python? Is it 32-bit or 64-bit?

    64-bit 3.5.3 and 3.6.1

    1. What is your version of cx_Oracle?

    cx-Oracle==5.2.1

    1. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where is it installed?
    > rpm -qa|grep instantclient12
    oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64
    oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64
    

    RPM defaults: /usr/lib/oracle/12.1/client64/

    1. What is your OS and version?

    cat /etc/system-release Oracle Linux Server release 6.5

    1. What compiler version did you use? For example, with GCC, run gcc --version.
    gcc --version
    gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17)
    Copyright (C) 2010 Free Software Foundation, Inc.
    This is free software; see the source for copying conditions.  There is NO
    warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
    
    1. What environment variables did you set? How exactly did you set them?
    > cat .bashrc
    # .bashrc
    
    # Source global definitions
    if [ -f /etc/bashrc ]; then
        . /etc/bashrc
    fi
    
    if [ -d ~/.bashrc.d ]; then
        for i in ~/.bashrc.d/*.sh ; do
            if [ -r "$i" ]; then
                if [ "${-#*i}" != "$-" ]; then
                    . "$i"
                else
                    . "$i" >/dev/null 2>&1
                fi
            fi
        done
    fi
    
    > cat .bashrc.d/oracle-instantclient.sh 
    #!/bin/bash
    
    export ORACLE_HOME=/usr/lib/oracle/12.1/client64/
    export DYLD_LIBRARY_PATH=$ORACLE_HOME
    export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
    export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH
    
    1. What error(s) you are seeing?
    • Connection sits and waits.
    • After 180 seconds, we get the error we expect. Can we get it sooner?
    2017-04-26 21:36:23,111 ERROR: (cx_Oracle.OperationalError) ORA-03135: connection lost contact\nProcess ID: 26655\nSession ID: 20 Serial number: 48108
    

    This is actually wrapped in sqlalchemy.exc.OperationalError, caught similar to this (simplified):

            try:
                    result = self.Session.read_session.query(self.model).filter(*_f).all()
    
            except (sqlalchemy.exc.OperationalError, sqlalchemy.exc.UnboundExecutionError, sqlalchemy.exc.InterfaceError, sqlalchemy.exc.DatabaseError, cx_Oracle.InterfaceError) as e:
    
                    emit(e, code=500, source='%s modelsearch.ModelSearch.run()' % (sid), extra="DB Query Failure.  Reconnecting.")
    
                    ... etc.
    
    enhancement question 
    opened by skywaymsn 25
  • Character garble problem

    Character garble problem

    Dear CX Oracle developer, hello, is there a function to distinguish between Chinese and English strings? For example, “你好??” comes from the database, and the final output is in Chinese: "你好??"

    question inactive 
    opened by Positever 24
  • Use UTF-8 as default encoding instead of ASCII

    Use UTF-8 as default encoding instead of ASCII

    All other major databases use UTF-8 as the default characterset encoding for connections while cx_Oracle is using ASCII. Given how limited and rarely useful ASCII is these days, it makes more sense to follow "common convention" and Oracle's default since 11g: UTF-8

    MySQL: https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html Postgres: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS (seems to determine the default from the server) SQL Server: https://pymssql.readthedocs.io/en/latest/ref/pymssql.html#connection-class

    enhancement patch available 
    opened by gvenzl 24
  • Can't insert/select NVARCHAR2 column with russian and chinese strings

    Can't insert/select NVARCHAR2 column with russian and chinese strings

    Can't insert and select NVARCHAR2 column with russian and chinese strings.

    I tried to use python, encoding/nencoding parameters but i can't insert/read NVARCHAR2 column with russian and chinese strings.

    These are my settings:

    • Platform: Linux-5.4.0-131-generic-x86_64-with-glibc2.28
    • Python version: 3.9.9
    • Maxsize > 2**32: True
    • Cx-Oracle version: 8.2.1
    • Oracle client version: (19, 11, 0, 0, 0)
    • Oracle db version: 11.2.0.1.0

    DB

    CREATE TABLE "DI_TEST_TABLE"
       (	
       "LAYER" NVARCHAR2(255), 
       "LINGUA" VARCHAR2(255), 
       "DES" NUMBER(*,0)
       )
    
    SELECT * FROM NLS_DATABASE_PARAMETERS
    WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET')
    
    PARAMETER                      VALUE
    ------------------------------ ------------------------------
    NLS_CHARACTERSET               WE8MSWIN1252
    NLS_NCHAR_CHARACTERSET         AL16UTF16
    
    INSERT INTO DI_TEST_TABLE VALUES (n'консэквюат', 'russian', 1)
    INSERT INTO DI_TEST_TABLE VALUES (n'面料', 'chinese', 2)
    
    SELECT * FROM DI_TEST_TABLE
    
    LAYER          LINGUA	  DES
    -------------- ---------- -------
    консэквюат     russian    1
    面料            chinese    2
    
    SELECT * FROM DI_TEST_TABLE WHERE LAYER = n'консэквюат'
    
    LAYER          LINGUA	  DES
    -------------- ---------- -------
    консэквюат     russian    1
    

    PYTHON

    import cx_Oracle
    
    conn_config =  {'user': 'my_user', 'password': 'my_password', 'dsn': 'my_dsn', 'encoding': 'UTF-8', 'nencoding': 'UTF-8'}
    
    connection = cx_Oracle.connect(**conn_config)
    cursor = connection.cursor()
    
    # INSERT INTO DB
    insert_query = "INSERT INTO DI_TEST_TABLE VALUES (:1, :2, :3)"
    chunk = [('консэквюат', 'russian', 3),('面料', 'chinese', 4)]
    
    cursor.executemany(insert_query, chunk)
    connection.commit()
    
    select_query = "SELECT * FROM DI_TEST_TABLE"
    cursor.execute(select_query)
    result = cursor.fetchall()
    print(result)
    
    # READ 
    select_query = "SELECT * FROM DI_TEST_TABLE WHERE LAYER = 'консэквюат'"
    cursor.execute(select_query)
    result = cursor.fetchall()
    print(result)
    
    select_query = "SELECT * FROM DI_TEST_TABLE WHERE LAYER = n'консэквюат'"
    cursor.execute(select_query)
    result = cursor.fetchall()
    print(result)
    
    connection.close()
    

    This is python script output. When I insert the records with python on the db the question marks (¿) are inserted. Similarly, when I execute the select, the correct records are not retrieved but those with question marks (¿).

    [('консэквюат', 'russian', 1), ('面料', 'chinese', 2), ('¿¿¿¿¿¿¿¿¿¿', 'russian', 3), ('¿¿', 'chinese', 4)]
    [('¿¿¿¿¿¿¿¿¿¿', 'russian', 3)]
    [('¿¿¿¿¿¿¿¿¿¿', 'russian', 3)]
    

    I tried to use binding params and calling cursor.setinputsizes(), but I need to execute queries without binding params because queries are read from file and chinese/russian strings are embedded into queries. Is there a way to execute these queries correctly?

    question 
    opened by AndreaPuccia 9
  • importing problem

    importing problem

    1. What versions are you using? 11g
    1. Describe the problem

    D:\DBS>python -m pip install cx_Oracle --upgrade Requirement already satisfied: cx_Oracle in c:\users\likhi\appdata\local\packages\pythonsoftwarefoundation.python.3.10_qbz5n2kfra8p0\localcache\local-packages\python310\site-packages (8.3.0)

    problem:

    import cx_Oracle Traceback (most recent call last): File "", line 1, in ModuleNotFoundError: No module named 'cx_Oracle'

    1. Show the directory listing where your Oracle Client libraries are installed (e.g. the Instant Client directory). Is it 64-bit or 32-bit? C:\oraclexe\app\oracle\product\11.2.0\server

    2. Show what the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) is set to? path :C:\oraclexe\app\oracle\product\11.2.0\server

    3. Show any Oracle environment variables set (e.g. ORACLE_HOME, ORACLE_BASE)

    C:\oraclexe\app\oracle\product\11.2.0\server

    install & configuration 
    opened by likhithpindi1 1
  • Getting

    Getting "cx_Oracle.DatabaseError: DPI-1037" when trying to query a long view definition as STRING

    1. What versions are you using?
    Database: 19.16.0.0.0
    platform.platform: Linux-3.10.0-1160.76.1-el7.x86_64-x86_64-with-glibc2.17
    sys.maxsize > 2**32: True
    platform.python_version: 3.9.13
    cx_Oracle.version: 8.1.0
    cx_Oracle.clientversion: (18, 3, 0, 0, 0)
    
    1. Is it an error or a hang or a crash? It's an error that is returned from the Database.

    2. What error(s) or behavior you are seeing? cx_Oracle.DatabaseError: DPI-1037: column at array position 0 fetched with error 1406 We have noticed a case where cx_Oracle is raising an error when the cursor.outputtypehandler is set with defaultType as cx_Oracle.STRING and works when it is set with cx_Oracle.LONG_STRING. The issue manifested in our codebase from a SQLAlchemy change, where setting coerce_to_unicode was causing the defaultType to change. Despite the change in defaultType, should this error manifest? We would expect cx_Oracle to handle it gracefully and show us the query result.

    3. Include a runnable Python script that shows the problem.

    def cx_conn():
        import cx_Oracle
        cx_dsn= cx_Oracle.makedsn("hostname", "12345", service_name="SERVICE_NAME")
        connection = cx_Oracle.connect(user="username", password="password1234", dsn=cx_dsn)
        cursor = connection.cursor()
        cursor.arraysize = 500
        def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
            # return cursor.var(cx_Oracle.LONG_STRING, arraysize=cursor.arraysize) # works
            return cursor.var(cx_Oracle.STRING, arraysize=cursor.arraysize)        # doesn't work
        cursor.outputtypehandler = OutputTypeHandler
        view_name = 'VW_TEST_CX_ORACLE'
        view_def = f"CREATE OR REPLACE VIEW {view_name} AS SELECT '{'A' * 4000}' AS a, '{'B' * 4000}' AS b, '{'C' * 4000}' AS c, '{'D' * 3950}' AS d FROM DUAL"
        cursor.execute(view_def)
        print(f'''This creates a long view definition. It is {len(view_def)} characters long.
            This is the threshold where "sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) DPI-1037: column at array position 0 fetched with error 1406" error starts.
            Our production view definitions are often much longer''')
        test_cmd = f"SELECT text FROM all_views WHERE view_name='{view_name}'"
        result = cursor.execute(test_cmd)
        x = result.fetchone()
        print(x)
    
    question inactive 
    opened by recognosco 5
  • ORA-01745 Error When using executemany() to insert

    ORA-01745 Error When using executemany() to insert

    1. What versions are you using?
    • platform.platform: Windows-10-10.0.19041-SP0
    • sys.maxsize > 2**32: True
    • platform.python_version: 3.7.3
    • cx_Oracle.version: 8.3.0
    • cx_Oracle.clientversion: (12, 1, 0, 2, 0)
    1. Describe the problem
    • When I try to use executemany() to insert into the database, the error occured.
      • File ".\test.py", line 52, in cur.executemany(sql,data) cx_Oracle.DatabaseError: ORA-01745: invalid host/bind variable name
    • the code is sql='''insert into "t_file" ("id","uuid","filename","path","size","type") values (:id,:uuid,:filename,:path,:size,:type)''' data=[{'id': 1, 'uuid': '9f0c23d', 'filename': '16574g', 'path': 'D:', 'size': 381.21,'type':'2'}] cursor.executemany(sql,data) And I found if I change the variable 'size' after ':' to another name like ':size1', and also change it in the dict, or just delete the variable named 'size' it works. It's so confused, I know 'size' is one of the reserved words of Oracle, but it works when I use the name 'type'.
    question inactive 
    opened by FoolPlayer 2
  • The arguement

    The arguement "python_requires" is not set in correct position

    Hello! I noticed that your "python_requires" has no effect you set that behind the section [metadata] in setup.cfg

    [metadata]
    name = cx_Oracle
    description = Python interface to Oracle
    long_description = file: README.md
    long_description_content_type = text/markdown
    keywords = Oracle, database
    ...
    python_requires = >=3.6
    ...
    

    However, it should belong be the [options] section, the more appropriatie way to declare "python_requires" is

    [metadata]
    name = cx_Oracle
    description = Python interface to Oracle
    long_description = file: README.md
    long_description_content_type = text/markdown
    ...
    [options]
    python_requires = >=3.6
    

    In this way, “pip install” can check python version on the fly during distribution selection , and " check minimum supported Python version" is needless

    # check minimum supported Python version
    if sys.version_info[:2] < (3, 6):
        raise Exception("Python 3.6 or higher is required. " +
                "For python 2, use 'pip install cx_Oracle==7.3'")
    

    Thanks for your attention. Best regrads, PyVCEchecker

    bug 
    opened by PyVCEchecker 2
  • Continuous fuzzing by way of OSS-Fuzz

    Continuous fuzzing by way of OSS-Fuzz

    1. Describe your new request in detail

    I was wondering if you would like to integrate continuous fuzzing by way of OSS-Fuzz? Fuzzing is a way to automate test-case generation and can be used to find unexpected exceptions in Python and memory corruption issues in native code. In this PR https://github.com/google/oss-fuzz/pull/8420 I did an initial integration into OSS-Fuzz. The fuzzer does a simple connection request following by a query execution. The goal is to find memory corruption issues that may exist, and I think there is room to extend the fuzzing and would be happy to do so down the line.

    OSS-Fuzz is a service run by Google for important open source projects. If you would like to integrate, the only thing I need is a list of email(s) that will get access to the data produced by OSS-Fuzz, such as bug reports, coverage reports and more stats. Notice the emails affiliated with the project will be public in the OSS-Fuzz repo, as they will be part of a configuration file.

    enhancement 
    opened by DavidKorczynski 2
Releases(8.3.0)
  • 8.3.0(Nov 4, 2021)

  • 8.2.1(Jun 1, 2021)

  • 8.2.0(May 19, 2021)

  • 8.1(Dec 8, 2020)

  • 8.0.1(Sep 1, 2020)

  • 8.0(Jun 26, 2020)

    cx_Oracle 8.0 is now released. This release is the first one to only support Python 3. The default encoding for all character data is now UTF-8. A new method for initialising the Oracle Client libraries is available. A number of other smaller enhancements and bug fixes were also made. See the full release notes for all of the details.

    Source code(tar.gz)
    Source code(zip)
  • 7.3(Dec 3, 2019)

    cx_Oracle 7.3 is now released. Support for Python 3.8 was added. The majority of the other changes are for sharding support. A number of other smaller enhancements and bug fixes were also made. See the full release notes for all of the details.

    Source code(tar.gz)
    Source code(zip)
  • 7.2.3(Oct 1, 2019)

  • 7.2.2(Aug 13, 2019)

  • 7.2.1(Jul 25, 2019)

  • 7.2(Jul 2, 2019)

    cx_Oracle 7.2 is now released. The biggest enhancements for this release are for advanced queuing with the addition of support for RAW queues and bulk enqueue/dequeue. Preview support for bulk insert with SODA was also added.

    A number of smaller enhancements and bug fixes were also made. See the full release notes for all of the details.

    Source code(tar.gz)
    Source code(zip)
  • 7.1.3(Apr 24, 2019)

  • 7.1.2(Mar 13, 2019)

  • 7.1.1(Feb 19, 2019)

  • 7.1(Feb 5, 2019)

    cx_Oracle 7.1 is now released. The biggest enhancement for this release is for session pools. Specifically, a session callback may now be specified when a pool is created. This allows an application to improve performance by reducing the number of round trips to the database needed in order to set session state.

    A number of smaller enhancements and bug fixes were also made. See the full release notes for all of the details.

    Source code(tar.gz)
    Source code(zip)
  • 7.0(Sep 13, 2018)

    cx_Oracle 7.0 is now released. The biggest enhancements for this release come with support for Oracle Client 18 libraries. This allows support for new features such as call timeouts and a preview of SODA (Simple Oracle Document Access).

    A number of smaller changes were also made to improve the experience of using cx_Oracle. These include re-enabling the pool pinging functionality for Oracle 12.2 and higher to handle classes of connection errors such as resource profile limits; improved error messages when a feature requires a particular version of Oracle Client libraries or Oracle Database; better handling of objects when the connection that created them is closed; better handling of connections when the pool that created them is destroyed; etc.

    For those upgrading from cx_Oracle 6, note that variables bound to DML returning statements now return an array when calling getvalue() and that using a connection as a context manager now closes the connection when the block is completed.

    See the release notes for more information.

    Source code(tar.gz)
    Source code(zip)
  • 6.4.1(Jul 9, 2018)

  • 6.4(Jul 2, 2018)

    This release adds support for grouping notifications to subscriptions and adds support for getting notifications when AQ messages are available to be dequeued. Support was also added for timed waits when acquiring sessions from a session pool and for setting the values of the timeout and maximum lifetime attributes of a pool when it is created. Cursors can now be used as context managers and a number of other enhancements and bug fixes were made. See the release notes for more information.

    Source code(tar.gz)
    Source code(zip)
  • 6.3.1(May 7, 2018)

  • 6.3(Apr 27, 2018)

    This release addresses a number of issues and bug fixes. The changes needed to support DML Returning with cursor.executemany() required a new point release since the attribute dml_ret_array_val in the cx_Oracle.__future__ object needs to be set in order for it to work correctly. Setting it changes the behavior for DML Returning statements with cursor.execute() as well and will become the new behavior in cx_Oracle 7. See the release notes for more information.

    Source code(tar.gz)
    Source code(zip)
  • 6.2.1(Mar 7, 2018)

  • 6.2(Mar 5, 2018)

    This release eliminates the error "DPI-1054: connection cannot be closed when open statements or LOBs exist". It also adds support for creating temporary LOBs and binding LOBs directly to a cursor. You can now also use a connection as a context manager to close the connection at the end of the block by using the new cx_Oracle.__future__ object. This will become the default and only behaviour of using a connection as a context manager unless an issue is raised requesting the original behaviour to be retained. The code was reorganised and simplified in order to streamline further maintenance and a number of bugs were fixed. See the full release notes for more information.

    Source code(tar.gz)
    Source code(zip)
  • 6.1(Dec 12, 2017)

    This release has a number of small enhancements intended to build upon the release of 6.0 made a few months ago. Topping the list is support for accessing sharded databases, a new feature in Oracle Database 12.2. Support for creating connections using the SYSBACKUP, SYSDG, SYDKM and SYSRAC roles was also added, as was support for identifying the id of the transaction which spawned a subscription message. For those on Windows, improved error messages were created for when the wrong architecture Oracle Client is in the PATH environment variable. Improvements were also made to the debugging infrastructure and a number of bugs were squashed. The test suite has also been expanded. See the full release notes for more information.

    Source code(tar.gz)
    Source code(zip)
Owner
Oracle
Open Source at Oracle
Oracle
MySQL database connector for Python (with Python 3 support)

mysqlclient This project is a fork of MySQLdb1. This project adds Python 3 support and fixed many bugs. PyPI: https://pypi.org/project/mysqlclient/ Gi

PyMySQL 2.2k Dec 25, 2022
MySQL database connector for Python (with Python 3 support)

mysqlclient This project is a fork of MySQLdb1. This project adds Python 3 support and fixed many bugs. PyPI: https://pypi.org/project/mysqlclient/ Gi

PyMySQL 2.2k Dec 25, 2022
A fast PostgreSQL Database Client Library for Python/asyncio.

asyncpg -- A fast PostgreSQL Database Client Library for Python/asyncio asyncpg is a database interface library designed specifically for PostgreSQL a

magicstack 5.8k Dec 31, 2022
Async database support for Python. 🗄

Databases Databases gives you simple asyncio support for a range of databases. It allows you to make queries using the powerful SQLAlchemy Core expres

Encode 3.2k Dec 30, 2022
The Database Toolkit for Python

SQLAlchemy The Python SQL Toolkit and Object Relational Mapper Introduction SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that giv

SQLAlchemy 6.5k Jan 1, 2023
A HugSQL-inspired database library for Python

PugSQL PugSQL is a simple Python interface for using parameterized SQL, in files. See pugsql.org for the documentation. To install: pip install pugsql

Dan McKinley 558 Dec 24, 2022
PostgreSQL database adapter for the Python programming language

psycopg2 - Python-PostgreSQL Database Adapter Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Its main fe

The Psycopg Team 2.8k Jan 5, 2023
A tiny python web application based on Flask to set, get, expire, delete keys of Redis database easily with direct link at the browser.

First Redis Python (CRUD) A tiny python web application based on Flask to set, get, expire, delete keys of Redis database easily with direct link at t

Max Base 9 Dec 24, 2022
A Relational Database Management System for a miniature version of Twitter written in MySQL with CLI in python.

Mini-Twitter-Database This was done as a database design course project at Amirkabir university of technology. This is a relational database managemen

Ali 12 Nov 23, 2022
Database connection pooler for Python

Nimue Strange women lying in ponds distributing swords is no basis for a system of government! --Dennis, Peasant Nimue is a database connection pool f

null 1 Nov 9, 2021
A tutorial designed to introduce you to SQlite 3 database using python

SQLite3-python-tutorial A tutorial designed to introduce you to SQlite 3 database using python What is SQLite? SQLite is an in-process library that im

null 0 Dec 28, 2021
Creating a python package to convert /transfer excelsheet data to a mysql Database Table

Creating a python package to convert /transfer excelsheet data to a mysql Database Table

Odiwuor Lameck 1 Jan 7, 2022
PostgreSQL database access simplified

Queries: PostgreSQL Simplified Queries is a BSD licensed opinionated wrapper of the psycopg2 library for interacting with PostgreSQL. The popular psyc

Gavin M. Roy 251 Oct 25, 2022
aiopg is a library for accessing a PostgreSQL database from the asyncio

aiopg aiopg is a library for accessing a PostgreSQL database from the asyncio (PEP-3156/tulip) framework. It wraps asynchronous features of the Psycop

aio-libs 1.3k Jan 3, 2023
aiomysql is a library for accessing a MySQL database from the asyncio

aiomysql aiomysql is a "driver" for accessing a MySQL database from the asyncio (PEP-3156/tulip) framework. It depends on and reuses most parts of PyM

aio-libs 1.5k Jan 3, 2023
The JavaScript Database, for Node.js, nw.js, electron and the browser

The JavaScript Database Embedded persistent or in memory database for Node.js, nw.js, Electron and browsers, 100% JavaScript, no binary dependency. AP

Louis Chatriot 13.2k Jan 2, 2023
Query multiple mongoDB database collections easily

leakscoop Perform queries across multiple MongoDB databases and collections, where the field names and the field content structure in each database ma

bagel 5 Jun 24, 2021
Class to connect to XAMPP MySQL Database

MySQL-DB-Connection-Class Class to connect to XAMPP MySQL Database Basta fazer o download o mysql_connect.py e modificar os parâmetros que quiser. E d

Alexandre Pimentel 4 Jul 12, 2021
Sample code to extract data directly from the NetApp AIQUM MySQL Database

This sample code shows how to connect to the AIQUM Database and pull user quota details from it. AIQUM Requirements: 1. AIQUM 9.7 or higher. 2. An

null 1 Nov 8, 2021