insert a row

don123

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))

don123

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

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

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

How to get unreleased version of cx_Oracle that you mentioned ?

Anthony Tuininga-Oracle

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

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

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

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

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

>>> import cx_Oracle

>>> print cx_Oracle.buildtime

Jan 18 2016 21:44:37

>>>