Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
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
Re: [sqlalchemy] performance vs. psycopg2
On Thu, Dec 15, 2011 at 19:52, Jon Nelson jnel...@jamponi.net wrote: On Thu, Dec 15, 2011 at 12:01 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 15, 2011, at 12:51 PM, Jon Nelson wrote: Up front, I'm not using the ORM at all, and I'm using SQLAlchemy 0.7.4 with psycopg2 2.4.3 on PostgreSQL 8.4.10 on Linux x86_64. I did some performance testing. Selecting 75 million rows (a straight up SELECT colA from tableA) from a 5GB table yielded some interesting results. psycopg2 averaged between 485,000 and 585,000 rows per second. Using COPY (via psycopg2) the average was right around 585,000. sqlalchemy averaged between 160,000 and 190,000 rows per second. That's a pretty big difference. Weird, IIRC, SA was much closer than raw psycopg2 (without using COPY), in the range of SA adding a 50% overhead, not a 200% overhead. I briefly looked into what the cause could be, but I didn't see anything jump out at me (except RowProxy, maybe). Thoughts? Performance tests like this are fraught with complicating details (such as, did you fully fetch each column in each row in both cases? Did you have equivalent unicode and numeric conversions in place in both tests ? ). In this case psycopg2 is written in pure C and SQLAlchemy's result proxy only partially (did you use the C extensions ?). You'd use the Python profiling module to get a clear picture for what difference there is in effort. But using any kind of abstraction layer, especially one written in Python, will always add latency versus a pure C program. I pretty much did this: for row in rows: count += 1 That test is probably flawed, as you don't fetch actual values. You should try to access individual elements (either by iterating over the row, or indexing it one way or another -- the speed difference can vary quite a bit depending on that). You might get even worse results with a proper test though ;-). -- Gaëtan de Menten -- 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.
Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
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
Re: [sqlalchemy] performance vs. psycopg2
On Fri, Dec 16, 2011 at 3:30 AM, Gaëtan de Menten gdemen...@gmail.com wrote: On Thu, Dec 15, 2011 at 19:52, Jon Nelson jnel...@jamponi.net wrote: On Thu, Dec 15, 2011 at 12:01 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 15, 2011, at 12:51 PM, Jon Nelson wrote: Up front, I'm not using the ORM at all, and I'm using SQLAlchemy 0.7.4 with psycopg2 2.4.3 on PostgreSQL 8.4.10 on Linux x86_64. I did some performance testing. Selecting 75 million rows (a straight up SELECT colA from tableA) from a 5GB table yielded some interesting results. psycopg2 averaged between 485,000 and 585,000 rows per second. Using COPY (via psycopg2) the average was right around 585,000. sqlalchemy averaged between 160,000 and 190,000 rows per second. That's a pretty big difference. Weird, IIRC, SA was much closer than raw psycopg2 (without using COPY), in the range of SA adding a 50% overhead, not a 200% overhead. I briefly looked into what the cause could be, but I didn't see anything jump out at me (except RowProxy, maybe). Thoughts? Performance tests like this are fraught with complicating details (such as, did you fully fetch each column in each row in both cases? Did you have equivalent unicode and numeric conversions in place in both tests ? ). In this case psycopg2 is written in pure C and SQLAlchemy's result proxy only partially (did you use the C extensions ?). You'd use the Python profiling module to get a clear picture for what difference there is in effort. But using any kind of abstraction layer, especially one written in Python, will always add latency versus a pure C program. I pretty much did this: for row in rows: count += 1 That test is probably flawed, as you don't fetch actual values. You should try to access individual elements (either by iterating over the row, or indexing it one way or another -- the speed difference can vary quite a bit depending on that). You might get even worse results with a proper test though ;-). Revised to use: for row in rows: dict(row) # throw away result count += 1 SQLAlchemy: 115,000 to 120,000 rows/s (vs. psycopg2 @ 480K - 580K, or psycopg2 COPY @ 620K). I suspect the issue is that I'm only selecting one column, so the per-row overhead is exaggerated. Thanks for the responses. -- Strange things are afoot at the Circle K. Jon -- 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.
Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
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__
Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
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
[sqlalchemy] Infer (and create) Schema from Example Object/Dictionary
My search skills are failing me, and I hope you all can help.
(Apologies that there is some heresy here)
Assumptions:
1) Suppose I have objects made from json (dicts of strings, lists of
dicts, etc.)
2) (for simplicity, assume these nestings don't go very deep)
3) getting this right 90% of the time is fine, and it can assume there are
only 1:1 and 1:many.
Question:
Is there a tool, or what is the easiest way to create / autogenerate a
sensible schema based on the object?
I am looking for a function with this sort of signature:
obj = {'id':1,
'name': 'Gregg',
'events': ['ts': 129292939392, 'what': 'keypress'},
{'ts': 129292939394, 'what': 'click'}]
}
def gen_schemas_and_create_table_statements(obj=obj, primary_key='id')
that would give something like:
* 2 (or maybe 3) tables: table1: pk id, string name ; table2
(events): foreign id, ts, what
along with the sql to create them.
(this is inspired by all the grossness I deal with in Hive).
Thanks!
GL
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/tuAozc5iqfEJ.
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.
[sqlalchemy] SQLA without the ORM?
Hi all! I have a few scenarios here that I believe are best solved without the ORM overhead. For example, various log tables that do not require a primary key, the rows are practically immutable, but are queried back for statistical analysis. It is my understanding that I cannot use the ORM without a primary key of some kind? I am looking through the docs and I believe I should look into SQL Expression Language section for that, am I correct? Which basically means I should be using the expressions directly on the connection object (connection.execute()) instead of using the sqlalchemy.orm.scoped_session object? Can you give me any pointers? Thanks! -- .oO V Oo. -- 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.
Re: [sqlalchemy] SQLA without the ORM?
On Fri, Dec 16, 2011 at 2:55 PM, Vlad K. v...@haronmedia.com wrote: Hi all! I have a few scenarios here that I believe are best solved without the ORM overhead. For example, various log tables that do not require a primary key, the rows are practically immutable, but are queried back for statistical analysis. It is my understanding that I cannot use the ORM without a primary key of some kind? I am looking through the docs and I believe I should look into SQL Expression Language section for that, am I correct? Which basically means I should be using the expressions directly on the connection object (connection.execute()) instead of using the sqlalchemy.orm.scoped_session object? I don't use scoped_session but I do use the sessionmaker Session instances from the .orm namespace, and I rarely use the ORM itself. My pattern usually goes like this: session_factory = sa.orm.sessionmaker() sess = session_factory() sess.begin() try: .. do stuff with sess except: sess.rollback() grump loudly raise else: sess.commit() # if appropriate, sometimes rollback sess.close() # probably unnecessary -- Strange things are afoot at the Circle K. Jon -- 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.
Re: [sqlalchemy] how add comment before query statement?
2011/12/12 Michael Bayer mike...@zzzcomputing.com we have select.prefix_with() which can stick it right after the SELECT, if that worksotherwise if it really has to be the first thing would need to work in some @compiles tricks. then as far as Query I thought we had added something for this but apparently not, you'd have to subclass that too for the moment... :/ Hello. I have related question. I need add sql_cache keywords into select query. There is way to do this on sql layer via prefixes keyword argument to select class or via method .prefix_with of the same select class. But I have orm.Query object... And I can't find way to add prefix on it. SA-0.6.7 On Dec 12, 2011, at 3:56 AM, lestat wrote: For our postgresql cluster we need sometime append comment before query statement. E.g. q = Comment.query.all() SELECT ... FROM comment How append comment like this? /*NO LOAD BALANCE*/ SELECT ... FROM comment I try change q.statement, but can't find right solution. -- 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.
Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
On Dec 16, 2011, at 10:31 AM, jo wrote:
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
OK so let me tell you how to do that - just leave out the DB part, and replace
host with tns name:
create_engine(oracle://user:password@SHELL)
-- 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
Re: [sqlalchemy] Infer (and create) Schema from Example Object/Dictionary
On Dec 16, 2011, at 1:42 PM, Gregg Lind wrote:
My search skills are failing me, and I hope you all can help.
(Apologies that there is some heresy here)
Assumptions:
1) Suppose I have objects made from json (dicts of strings, lists of dicts,
etc.)
2) (for simplicity, assume these nestings don't go very deep)
3) getting this right 90% of the time is fine, and it can assume there are
only 1:1 and 1:many.
Question:
Is there a tool, or what is the easiest way to create / autogenerate a
sensible schema based on the object?
I am looking for a function with this sort of signature:
obj = {'id':1,
'name': 'Gregg',
'events': ['ts': 129292939392, 'what': 'keypress'},
{'ts': 129292939394, 'what': 'click'}]
}
def gen_schemas_and_create_table_statements(obj=obj, primary_key='id')
that would give something like:
* 2 (or maybe 3) tables: table1: pk id, string name ; table2
(events): foreign id, ts, what
along with the sql to create them.
It's possible but a little involved since it has to traverse through the whole
structure, build up a tree of all the attribute names it finds, then apply
various geometries to known table structures. Step one would be to organize
a structure like this:
container:root
name:id; type:int
name:name; type: string; length:5 (based on the longest length you see in
the data)
name events; type:collection of event_obj
container:event_obj
name:ts; type:int;
name:what; type:string
then you go through that and generate two tables. the tables would probably
get surrogate primary keys added in, and you can also make a rule if the
container already has an integer name 'id', that's the pk.
the structure is essentially a tree (every node has one parent) so I don't
think you'd see any many-to-many relationships falling out of it.
--
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.
Re: [sqlalchemy] SQLA without the ORM?
On Dec 16, 2011, at 3:55 PM, Vlad K. wrote: Hi all! I have a few scenarios here that I believe are best solved without the ORM overhead. For example, various log tables that do not require a primary key, the rows are practically immutable, but are queried back for statistical analysis. It is my understanding that I cannot use the ORM without a primary key of some kind? I am looking through the docs and I believe I should look into SQL Expression Language section for that, am I correct? Which basically means I should be using the expressions directly on the connection object (connection.execute()) instead of using the sqlalchemy.orm.scoped_session object? If your dealings with data are already framed by a Session, you can work with the SQL expression language in that context using either Session.execute() or work with the Connection that's part of the current transactional scope by calling Session.connection() to get at it. This essentially maintains the usage of Session as your transaction management object, you just aren't using the object-relational aspects of it. -- 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.
Re: [sqlalchemy] how add comment before query statement?
On Dec 16, 2011, at 4:32 PM, bogun.dmit...@gmail.com wrote: 2011/12/12 Michael Bayer mike...@zzzcomputing.com we have select.prefix_with() which can stick it right after the SELECT, if that worksotherwise if it really has to be the first thing would need to work in some @compiles tricks. then as far as Query I thought we had added something for this but apparently not, you'd have to subclass that too for the moment... :/ Hello. I have related question. I need add sql_cache keywords into select query. There is way to do this on sql layer via prefixes keyword argument to select class or via method .prefix_with of the same select class. But I have orm.Query object... And I can't find way to add prefix on it. yeah it's a missing feature so do this: from sqlalchemy.orm.query import Query, _generative class MyQuery(Query): _prefixes = () def prefix_with(self, prefixes): self._prefixes += prefixes def _compile_context(self, **kw): ctx = super(MyQuery, self)._compile_context(**kw) if self._prefixes: ctx.statement = ctx.statement.prefix_with(self._prefixes) return ctx Session = sessionmaker(query_cls=MyQuery) SA-0.6.7 On Dec 12, 2011, at 3:56 AM, lestat wrote: For our postgresql cluster we need sometime append comment before query statement. E.g. q = Comment.query.all() SELECT ... FROM comment How append comment like this? /*NO LOAD BALANCE*/ SELECT ... FROM comment I try change q.statement, but can't find right solution. -- 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.
Re: [sqlalchemy] SQLA without the ORM?
So basically, if I'm understanding the docs correctly, and what you just wrote: Using the session object does not mean using the ORM. The ORM comes in play with Mapper and Mapped instances, which in turn require a primary key defined. So, I can use session.execute() to do non-ORM querying? And ResultProxy to work with returned data? How would I autocreate the tables if I don't use Mapping, with DDL events and pure SQL? Thanks. .oO V Oo. On 12/16/2011 10:03 PM, Jon Nelson wrote: On Fri, Dec 16, 2011 at 2:55 PM, Vlad K.v...@haronmedia.com wrote: Hi all! I have a few scenarios here that I believe are best solved without the ORM overhead. For example, various log tables that do not require a primary key, the rows are practically immutable, but are queried back for statistical analysis. It is my understanding that I cannot use the ORM without a primary key of some kind? I am looking through the docs and I believe I should look into SQL Expression Language section for that, am I correct? Which basically means I should be using the expressions directly on the connection object (connection.execute()) instead of using the sqlalchemy.orm.scoped_session object? I don't use scoped_session but I do use the sessionmaker Session instances from the .orm namespace, and I rarely use the ORM itself. My pattern usually goes like this: session_factory = sa.orm.sessionmaker() sess = session_factory() sess.begin() try: .. do stuff with sess except: sess.rollback() grump loudly raise else: sess.commit() # if appropriate, sometimes rollback sess.close() # probably unnecessary -- 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.
Re: [sqlalchemy] SQLA without the ORM?
On Dec 16, 2011, at 7:32 PM, Vlad K. wrote: So basically, if I'm understanding the docs correctly, and what you just wrote: Using the session object does not mean using the ORM. more or lessthough if I wrote a program that had no ORM usage whatsoever I'm not sure I'd have Session in it. But I could. The ORM comes in play with Mapper and Mapped instances, which in turn require a primary key defined. So, I can use session.execute() to do non-ORM querying? And ResultProxy to work with returned data? yeah execute() goes right to connection.execute and connection() will give you the same kind of Connection you get from an Engine. How would I autocreate the tables if I don't use Mapping, with DDL events and pure SQL? tables are generated using create_all() on MetaData you can pass the connection in: metadata.create_all(session.connection()) The tables/DDL/pure SQL represent structures within a database.To actually transmit these constructs as instructions to a DB you just need a data pipe (which in reality is some system that goes over TCP/IP to your database server). Session is essentially a pipe around the Connection, which is the most direct pipe in SQLAlchemy to the database. -- 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.
Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
Michael Bayer wrote:
On Dec 16, 2011, at 10:31 AM, jo wrote:
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
OK so let me tell you how to do that - just leave out the DB part, and replace
host with tns name:
create_engine(oracle://user:password@SHELL)
could you tell me how it becomes in sqlalchemy.dburi on tg prod.cfg ?
sqlalchemy.dburi=oracle://username:password@host:port/service_name
I tried in this way:
sqlalchemy.dburi=oracle://username:password@SHELL:1521/SHELL
but...
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12545: Connect failed
because target host or object does not exist
-- 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)
Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
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
