I don't know Michael, only that Craig Hagan in oracl...@freelists.org,
suggested me this workaround...
"...However, I'm pretty sure that the problem is that you're depending
upon service names for your connection to succeed (that should be how
the url in your working example behaves), but the dsn you're
constructing via make_dsn is specifying SID= which is failing your
connection.
A possible simple solutions for you would be to either
1) 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)))
2) use tnsnames, which would then let you avoid generating the dsn in
software
-- craig"
Michael Bayer wrote:
is that a known bug in cx_Oracle ?
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.
--
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.
--
Jose Soares
Sferacarta Net Via Bazzanese 69
40033 Casalecchio di Reno
Bologna - Italy
Ph +39051591054
fax +390516131537
web:www.sferacarta.com
Le informazioni contenute nella presente mail ed in ogni eventuale file
allegato sono riservate e, comunque, destinate esclusivamente alla persona o
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza,
l’integrità e la sicurezza della presente mail non possono essere garantite. Se
avete ricevuto questa mail per errore, Vi preghiamo di contattarci
immediatamente e di eliminarla. Grazie.
This communication is intended only for use by the addressee, pursuant to
legislative decree 30 June 2003, n. 196. It may contain confidential or
privileged information. You should not copy or use it to disclose its contents
to any other person. Transmission cannot be guaranteed to be error-free,
complete and secure. If you are not the intended recipient and receive this
communication unintentionally, please inform us immediately and then delete
this message from your system. Thank you.
--
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.
--
Jose Soares
Sferacarta Net
Via Bazzanese 69
40033 Casalecchio di Reno
Bologna - Italy
Ph +39051591054
fax +390516131537
web:www.sferacarta.com
Le informazioni contenute nella presente mail ed in ogni eventuale file
allegato sono riservate e, comunque, destinate esclusivamente alla persona o
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza,
l’integrità e la sicurezza della presente mail non possono essere garantite. Se
avete ricevuto questa mail per errore, Vi preghiamo di contattarci
immediatamente e di eliminarla. Grazie.
This communication is intended only for use by the addressee, pursuant to
legislative decree 30 June 2003, n. 196. It may contain confidential or
privileged information. You should not copy or use it to disclose its contents
to any other person. Transmission cannot be guaranteed to be error-free,
complete and secure. If you are not the intended recipient and receive this
communication unintentionally, please inform us immediately and then delete
this message from your system. Thank you.
--
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.
