Forum Stats

  • 3,770,452 Users
  • 2,253,116 Discussions
  • 7,875,461 Comments

Discussions

insert a row

don123
don123 Member Posts: 815
edited Mar 31, 2017 11:08PM in Python

table test has following structure.

test (id number, geom mdsys.sdo_geometry)

I am able to insert a row with below sql. It works fine.

insert into test values (10, SDO_UTIL.FROM_WKTGEOMETRY(('POLYGON ((4 5, 6 7, 9 7, 4 5))')));

How to write the above sql statement in python and cx_Oracle.

I want to use variable as below.

x = POLYGON ((4 5, 6 7, 9 7, 4 5))

«1

Answers

  • don123
    don123 Member Posts: 815
    edited Jan 1, 2017 11:35PM

    In addition to above, I tried the following. I get sdo_util not defined error.

    import cx_Oracle

    con = cx_Oracle.connect('scott/[email protected]')

    cur = con.cursor()

    statement = 'insert into testing(id, geom) values (:a, :b)'

    cur.execute(statement, (220, sdo_util.from_wktgeometry('POLYGON((10 23, 34 56, 44 45, 10 23))')))

    con.commit()

    con.close()

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jan 2, 2017 10:10AM

    In the unreleased version of cx_Oracle there is direct support for inserting objects like the one you are trying to insert. A sample script is provided called "InsertGeometry.py" which I will replicate below. This script works in both Python 2.x and Python 3.x.

    from __future__ import print_function

    import cx_Oracle

    # create and populate Oracle objects

    connection = cx_Oracle.Connection("cx_Oracle/[email protected]/orcl")

    typeObj = connection.gettype("SDO_GEOMETRY")

    elementInfoTypeObj = connection.gettype("SDO_ELEM_INFO_ARRAY")

    ordinateTypeObj = connection.gettype("SDO_ORDINATE_ARRAY")

    obj = typeObj.newobject()

    obj.SDO_GTYPE = 2003

    obj.SDO_ELEM_INFO = elementInfoTypeObj.newobject()

    obj.SDO_ELEM_INFO.extend([1, 1003, 3])

    obj.SDO_ORDINATES = ordinateTypeObj.newobject()

    obj.SDO_ORDINATES.extend([1, 1, 5, 7])

    print("Created object", obj)

    # create table, if necessary

    cursor = connection.cursor()

    cursor.execute("""

            select count(*)

            from user_tables

            where table_name = 'TESTGEOMETRY'""")

    count, = cursor.fetchone()

    if count == 0:

        print("Creating table...")

        cursor.execute("""

                create table TestGeometry (

                    IntCol number(9) not null,

                    Geometry SDO_GEOMETRY not null

                )""")

    # remove all existing rows and then add a new one

    print("Removing any existing rows...")

    cursor.execute("delete from TestGeometry")

    print("Adding row to table...")

    cursor.execute("insert into TestGeometry values (1, :obj)", obj = obj)

    connection.commit()

    print("Success!")

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jan 2, 2017 10:13AM

    You should also be able to use the following:

    cursor.execute("insert into test values (10, SDO_UTIL.FROM_WKTGEOMETRY(:inValue))", inValue = x)

    The error about SDO_UTIL not found implies that you are using a different user from the one you were using with SQL*Plus. Ensure that the user you are connecting to with cx_Oracle is the same or has the same privileges.

  • don123
    don123 Member Posts: 815
    edited Jan 3, 2017 4:29AM

    How to get unreleased version of cx_Oracle that you mentioned ?

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jan 3, 2017 2:15PM

    You can get the source code directly from the repository at:

    https://bitbucket.org/anthony_tuininga/cx_oracle

    At the top right of the page is the URL you can use with Mercurial to get the source.

    If you don't have Mercurial or just want to download a zip package containing the source, you can also go directly to this page:

    https://bitbucket.org/anthony_tuininga/cx_oracle/downloads

    Hope that helps!

  • don123
    don123 Member Posts: 815
    edited Jan 4, 2017 1:42AM

    Thanks for that.

    I have tried to run the sample script InsertGeometry.py after changing suitable connection parameters.

    But i got error.

    typeObj = connection.gettype("SDO_GEOMETRY")

    AttributeError: 'cx_Oracle.Connection' object has no attribute 'gettype'

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jan 4, 2017 10:23AM

    You're welcome. That error implies that you are not using the module built using the unreleased source code on BitBucket. You can confirm by looking at the version (cx_Oracle.version) which should say 5.3 if you are using the latest source.

  • don123
    don123 Member Posts: 815
    edited Jan 5, 2017 1:12AM

    I have downloaded and extracted as below.

    C:\anthony_tuininga-cx_oracle-1a6c77089c45\anthony_tuininga-cx_oracle-1a6c77089c45

    Then run below command to install. It is successful.

    python setup.py install

    When i import the version is 5.2.1 but not 5.3

    >>> import cx_Oracle

    >>> print cx_Oracle.version

    5.2.1

    >>>

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jan 5, 2017 11:28AM

    Ok. I just confirmed that the version number reported in the public repository is indeed still 5.2.1. So that should be fine and I apologise for the confusion! Just make sure you are using your compiled version and not something else! The easiest way to do that is to check the build time on your module as in:

    print(cx_Oracle.buildtime)

    Anthony

  • don123
    don123 Member Posts: 815
    edited Jan 5, 2017 11:07PM

    >>> import cx_Oracle

    >>> print cx_Oracle.buildtime

    Jan 18 2016 21:44:37

    >>>

This discussion has been closed.