In cx_oracle mailing list, they suggested me, this:
... write your own equivalent of makedsn, which really ought not be too hard.
You'd want to emit something like this:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=your-db-hostname)(PORT=yourport)))(CONNECT_DATA=(SERVICE_NAME=your-service-name)))
vs. what makedsn emits, which is stuff like this:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=your-db-hostname)(PORT=yourport)))(CONNECT_DATA=(SID=your-dbname)))
I tried to replace 'SID' with 'SERVICE_NAME' in this string returned by
makedsn like this:
dsn =
cx_Oracle.makedsn(parms['host'],parms['port'],parms['dbname']).replace('SID','SERVICE_NAME')
and IT WORKS, but I don't know how to apply this to tg+sqlalchemy in
the following context:
from turbogears import database
from sqlalchemy.orm import class_mapper
database.bind_meta_data()
engine = database.get_engine()
session = database.session
def mapper(klass, *args, **kw):
session.mapper(klass, *args, **kw)
class_mapper(klass).compile()
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.
