Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Fri, 16 Dec 2011 23:05:35 -0800 

Michael Bayer wrote:

is that a known bug in cx_Oracle ?

The latest version of cx_Oracle 5.1 is changed:
5) Added additional parameter service_name to makedsn() which can be used to 
   use the service_name rather than the SID in the DSN string that is
   generated.

makedsn(host = 'myhost', port = 1521, service_name = 'mydb')

j




On Dec 16, 2011, at 4:45 AM, jo wrote:


I solved the problem using this monkeypatch to makedsn as suggested me by 
Christoph Zwerschke.

makedsn = cx_Oracle.makedsn
cx_Oracle.makedsn = lambda *args, **kw: makedsn(*args, 
**kw).replace('SID','SERVICE_NAME')

Thaks any way to everyone.

j

Michael Bayer wrote:

yeah I dunno,   the problem is at the cx_Oracle / OCI level at this point, 
since you can illustrate cx_Oracle/makedsn() not working.  You might need to 
ask on their list at this point (only give them the init_db_conn() scripts, 
don't give them any SQLalchemy stuff):

https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

a workaround for now is you can put your successfully-connecting function to 
create_engine() using the "creator" argument:

e = create_engine("oracle://", creator=my_connect_function)



On Dec 14, 2011, at 10:49 AM, jose soares wrote:


Ok, I changed the file  $ORACLE_HOME/network/admin/tnsnames.ora but it still 
doesn't work.:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not 
currently know of SID given in connect descriptor
None None



# tnsnames.ora Network Configuration File: 
/usr/share/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_SHELL =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))

SHELL =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))
 (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = SHELL)
 )
)



Michael Bayer wrote:

so makedsn() will give you:


cx_Oracle.makedsn("oracapsul.net",  "1521", "SHELL")

'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracapsul.net)(PORT=1521)))(CONNECT_DATA=(SID=SHELL)))'


and that should match in your tnsnames.ora file  
($ORACLE_HOME/network/admin/tnsnames.ora).   would have an entry like:

SHELL =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsul.net)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = SHELL)
  )
)


maybe there's a discrepancy between the hostnames in use in the file vs. your 
URL.




On Dec 14, 2011, at 4:50 AM, jose soares wrote:


I also tried two different connection mode.
The first one works but the second one using makedsn doesn't.


def init_db_conn(parms): #this work
import cx_Oracle
dburi="%(user)s/%(password)s@%(host)s:%(port)s/%(sid)s" % parms
return cx_Oracle.connect(dburi)


def init_db_conn(parms): #this doesn't work
import cx_Oracle
dsn = cx_Oracle.makedsn(parms['host'],parms['port'],parms['sid'])
return cx_Oracle.connect(parms['user'], parms['password'], dsn)

jose soares wrote:

Hi Michael,
I tried your script.
the cx_Oracle.connect, works but
the create_engine doesn't...
---------------------------------------------------

import cx_Oracle
import sqlalchemy
c2 = cx_Oracle.connect("SFERA/p...@oracapusl.net:1521/SHELL")
cursor = c2.cursor();
print 'this one works'
print '-'*30
print cursor.execute("select 1 from dual").fetchone()
print
print 'this one does not:'
print '-'*30
e = sqlalchemy.create_engine("oracle://SFERA:p...@oracapusl.net:1521/SHELL")
c = e.connect()
c.scalar("select 1 from dual")
==========================================================



this one works
------------------------------
(1,)

this one does not:
------------------------------
Traceback (most recent call last):
File "/home/admin/buildout/bin/python", line 73, in <module>
execfile(__file__)
File "b.py", line 27, in <module>
c = e.connect()
File 
"/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py",
 line 1811, in connect
return self.Connection(self, **kwargs)
File 
"/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py",
 line 832, in __init__
self.__connection = connection or engine.raw_connection()
File 
"/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py",
 line 1874, in raw_connection
return self.pool.unique_connection()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 142, in unique_connection
return _ConnectionFairy(self).checkout()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 369, in __init__
rec = self._connection_record = pool.get()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 213, in get
return self.do_get()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 732, in do_get
con = self.create_connection()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 147, in create_connection
return _ConnectionRecord(self)
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 253, in __init__
self.connection = self.__connect()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 319, in __connect
connection = self.__pool._creator()
File 
"/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/strategies.py",
 line 82, in connect
return dialect.connect(*cargs, **cparams)
File 
"/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/default.py",
 line 249, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not 
currently know of SID given in connect descriptor
None None




Michael Bayer wrote:

On Dec 13, 2011, at 11:16 AM, jose soares wrote:



Hi all,


I'm trying to connect to an oracle db using sqlalchemy with turbogears1 and I 
get this error:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not 
currently know of SID given in connect descriptor

I tried making the connection using cs_Oracle and it works:

import cx_Oracle as cx

 conn = "USERNAME/passw...@myserver.net/SID"
 cc = cx.connect(conn)
 cc.version
 '11.2.0.3.0'
 cc.close()



You aren't showing us how you're connecting with SQLAlchemy, not even the URL 
you're using, so it's difficult to say what the problem is.

The URL for the above would be:

oracle://username:passw...@myserver.net/sid

if you still get an error then ensure ORACLE_HOME and such are set correctly 
when the program runs.

Here is an actual demonstration:

Python 2.7 (r27:82500, Sep 16 2010, 18:03:06) [GCC 4.5.1 20100907 (Red Hat 
4.5.1-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.


import cx_Oracle
import sqlalchemy
e = sqlalchemy.create_engine("oracle://scott:tiger@localhost/xe")
c = e.connect()
c2 = cx_Oracle.connect("scott/tiger@localhost/xe")
c.scalar("select 1 from dual")

1


cursor = c2.cursor();  cursor.execute("select 1 from dual"); cursor.fetchall()

<__builtin__.OracleCursor on <cx_Oracle.Connection to scott@localhost/xe>>
[(1,)]






but sa ...

File "./start-sicer.py", line 14, in main
from sicer.BASE.controller import Root
File "/home/admin/buildout/release/sicer/BASE/controller/__init__.py", line 2, in 
<module>
from sicer.BASE.model.varie.sessione import Sessione
File "/home/admin/buildout/release/sicer/BASE/model/varie/sessione.py", line 1, in 
<module>
from sicer.BASE.model.domain import DomainObject
File "/home/admin/buildout/release/sicer/BASE/model/domain.py", line 7, in 
<module>
from sicer.BASE.model.sql import tbl, session
File "/home/admin/buildout/release/sicer/BASE/model/sql.py", line 2468, in 
<module>
createdb() # crea lo schema del db
File "/home/admin/buildout/release/sicer/BASE/model/sql.py", line 692, in 
createdb
if dbtools.exist_table('ruolo_permesso'): #creazione virtuale della foreign key 
permesso.codice
File "/home/admin/buildout/release/sicer/lib/dbtools.py", line 67, in 
exist_table
if engine.execute(sql).fetchone()[0]:
File 
"/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py",
 line 1787, in execute
connection = self.contextual_connect(close_with_result=True)
File 
"/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py",
 line 1829, in contextual_connect
self.pool.connect(),
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 182, in connect
return _ConnectionFairy(self).checkout()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 369, in __init__
rec = self._connection_record = pool.get()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 213, in get
return self.do_get()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 732, in do_get
con = self.create_connection()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 147, in create_connection
return _ConnectionRecord(self)
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 253, in __init__
self.connection = self.__connect()
File "/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py", 
line 319, in __connect
connection = self.__pool._creator()
File 
"/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/strategies.py",
 line 82, in connect
return dialect.connect(*cargs, **cparams)
File 
"/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/default.py",
 line 249, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not 
currently know of SID given in connect descriptor
None None

Any ideas?

thanks for any help.
j

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



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

Reply via email to