[sqlalchemy] Oracle-Tg2 Combination,sqlalchemy error

[dhanil anupurath]

Hi,
I need help in setting up Oracle under TG/sqlalchemy environment.
I believe the issue is more of a SQLAlchemy issue and that is why i
am posting here.

Here's what i have done.

1.Installed TG2 and Sqlalchemy.
2.Installed Oracle Express 10g.
3.confirmed that I am able to access oracle using Sqlplus.
4.Installed cx_Oracle.
5.Wrote short test program in python to access my database using
cx_Oracle.The program worked fine.
Here's the test program.

import pkg_resources
pkg_resources.require("cx-Oracle>=5.0.3")
import cx_Oracle

connstr='<username>/<password>@localhost'
conn = cx_Oracle.connect(connstr)
curs = conn.cursor()

curs.execute('select * from tabs')
print curs.description
print curs.fetchone()

conn.close()
6.started my application after configuring it for Oracle Express.
7.Here's the error I got.
File "/root/tg2env/lib/python2.4/site-packages/
SQLAlchemy-0.5.6-py2.4.egg/sqlalchemy/engine/base.py", line 1229, in
contextual_connect
return self.Connection(self, self.pool.connect(),
close_with_result=close_with_result, **kwargs)
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/pool.py", line 142, in connect
return _ConnectionFairy(self).checkout()
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/pool.py", line 304, in __init__
rec = self._connection_record = pool.get()
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/pool.py", line 161, in get
return self.do_get()
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/pool.py", line 639, in do_get
con = self.create_connection()
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/pool.py", line 122, in create_connection
return _ConnectionRecord(self)
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/pool.py", line 198, in __init__
self.connection = self.__connect()
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/pool.py", line 261, in __connect
connection = self.__pool._creator()
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/strategies.py", line 80, in connect
raise exc.DBAPIError.instance(None, None, e)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener
does not currently know of SID given in connect descriptor
None None

8. Here are the .ORA files.
TNSNAMES.ora
# tnsnames.ora Network Configuration File:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
localhost.localdomain)(PORT = 1521))
CONNECT_DATA = (SID = XE))
)

sqlnet.authentication_services = (NONE)

LISTENER.ORA
# listener.ora Network Configuration File:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/lib/oracle/xe/app/
oracle/product/10.2.0/server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = mydb@localhost)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/
product/10.2.0/server)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST =
localhost.localdomain)(PORT = 1521))
)
)

DEFAULT_SERVICE_LISTENER = (XE)

SQLNET.ORA
This is the uncommented line in the sqlnet.ora



9.On googling ,I saw someone indicate that sql.authentication should
be set to none.
I did that but no avail.

I would really appreciate any help in resolving this problem.

Thanks.




--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On Apr 30, 2010, at 3:31 AM, dhanil anupurath wrote:

         import pkg_resources
         pkg_resources.require("cx-Oracle>=5.0.3")
          import cx_Oracle

        connstr='<username>/<password>@localhost'
        conn = cx_Oracle.connect(connstr)

I just want to go over how this is documented.  From the cx_oracle documentation at http://cx-oracle.sourceforge.net/html/module.html#cx_Oracle.connect , we see that the above is taken as the format "user/pass@dsn", where "dsn" is the TNS name from your tnsnames.ora file - ideally it would be the string "xe", but it seems to correlate the hostname of "localhost" as well.

Onto SQLAlchemy's, the document at http://www.sqlalchemy.org/docs/reference/dialects/oracle.html?highlight=cx_oracle#connecting states :

Connecting with create_engine() uses the standard URL approach of oracle://user:pass@host:port/dbname[?key=value&key=value...]. If dbname is present, the host, port, and dbname tokens are converted to a TNS name using the cx_oracle makedsn() function. Otherwise, the host token is taken directly as a TNS name.

So the equivalent connect string is:

create_engine('oracle://<user>:<password>@localhost')

meaning, '/dbname' is not present, so the 'host' portion, in this case 'localhost', is sent to cx_Oracle.connect as the TNS name.

The usual way to connect looks like:

create_engine('oracle://<user>:<password>@localhost/xe')

the advantage to this approach is that SQLAlchemy uses cx_Oracle.makedsn() so that no TNS entry is required.

Here are all the ways to connect to a localhost XE server:

import cx_Oracle

print cx_Oracle.connect('scott/tiger@localhost')

print cx_Oracle.connect('scott/tiger@xe')

print cx_Oracle.connect('scott', 'tiger', 'localhost')

print cx_Oracle.connect('scott', 'tiger', 'xe')

print cx_Oracle.connect('scott', 'tiger', cx_Oracle.makedsn('localhost', 1521, 'xe'))

from sqlalchemy import create_engine

print create_engine('oracle://scott:tiger@localhost').connect().connection.connection

print create_engine('oracle://scott:tiger@xe').connect().connection.connection

print create_engine('oracle://scott:tiger@localhost/xe').connect().connection.connection

output of this looks like:

<cx_Oracle.Connection to scott@localhost>

<cx_Oracle.Connection to scott@xe>

<cx_Oracle.Connection to scott@localhost>

<cx_Oracle.Connection to scott@xe>

<cx_Oracle.Connection to scott@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=xe)))>

<cx_Oracle.Connection to scott@localhost>

<cx_Oracle.Connection to scott@xe>

<cx_Oracle.Connection to scott@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=xe)))>

note the two forms of connection that use makedsn() have a locally-generated TNS entry present.

Also note that SQLAlchemy is not using pkg_resources to import cx_Oracle.  It just calls "import cx_Oracle".  If the above pkg_resources call is required (extremely unlikely), you need to call it before create_engine().

hope this helps.

[dhanil anupurath]

THANK YOU