Re: [sqlalchemy] Oracle wallets + proxy authentication with arguments encoded as strings in URL

[Chris Stephens]

"oracle://[proxy_to_user]@my_wallet" worked!

Thanks again.

On Thu, Nov 14, 2019 at 2:14 PM Mike Bayer  wrote:

>
>
> On Thu, Nov 14, 2019, at 2:52 PM, Chris Stephens wrote:
>
> I'm attempting to make use of an Oracle wallet with proxy authentication
> passed as strings in URL.
>
> The documentation states:
>
> "*Changed in version 1.3: *the cx_oracle dialect now accepts all argument
> names within the URL string itself, to be passed to the cx_Oracle DBAPI. As
> was the case earlier but not correctly documented, the
> create_engine.connect_args
> <https://docs.sqlalchemy.org/en/13/core/engines.html#sqlalchemy.create_engine.params.connect_args>
>  parameter
> also accepts all cx_Oracle DBAPI connect arguments."
>
> The following works:
>
> db = sqlalchemy.create_engine('oracle+cx_oracle://my_wallet_credential',
> connect_args={"user":"[prox_to_user]"}
> )
>
> However, I can't seem to get the strings/URL to work. I tried the
> following but always receive "ORA-12154: TNS:could not resolve the
> connect identifier specified". Is this possible? If so, what is the proper
> syntax?
>
> db =
> sqlalchemy.create_engine('oracle+cx_oracle://my_wallet_credential?user=proxy_to_user')
> db =
> sqlalchemy.create_engine('oracle+cx_oracle://my_wallet_credential?user=[proxy_to_user]')
>
>
> "user" is the first argument after the //.oracle://proxy_to_user@
> /my_wallet
>
>
>
>
> Thanks in advance for any help!
>
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAEFL0syDWBTjXKkRLViQSeTiOEphfsSdQG6KSwwSo7%3DEB1R3mQ%40mail.gmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/CAEFL0syDWBTjXKkRLViQSeTiOEphfsSdQG6KSwwSo7%3DEB1R3mQ%40mail.gmail.com?utm_medium=email_source=footer>
> .
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/0ead4a70-1a28-42ce-be86-4cd4dbd338d1%40www.fastmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/0ead4a70-1a28-42ce-be86-4cd4dbd338d1%40www.fastmail.com?utm_medium=email_source=footer>
> .
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAEFL0szX_fVrsRXWd3%2BYbkGVoHsVa88PfYGL-8JUZx8Tvob0qg%40mail.gmail.com.

[sqlalchemy] Oracle wallets + proxy authentication with arguments encoded as strings in URL

[Chris Stephens]

I'm attempting to make use of an Oracle wallet with proxy authentication
passed as strings in URL.

The documentation states:

"Changed in version 1.3: the cx_oracle dialect now accepts all argument
names within the URL string itself, to be passed to the cx_Oracle DBAPI. As
was the case earlier but not correctly documented, the
create_engine.connect_args

parameter
also accepts all cx_Oracle DBAPI connect arguments."

The following works:

db = sqlalchemy.create_engine('oracle+cx_oracle://my_wallet_credential',
connect_args={"user":"[prox_to_user]"}
)

However, I can't seem to get the strings/URL to work. I tried the following
but always receive "ORA-12154: TNS:could not resolve the connect identifier
specified". Is this possible? If so, what is the proper syntax?

db =
sqlalchemy.create_engine('oracle+cx_oracle://my_wallet_credential?user=proxy_to_user')
db =
sqlalchemy.create_engine('oracle+cx_oracle://my_wallet_credential?user=[proxy_to_user]')

Thanks in advance for any help!

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAEFL0syDWBTjXKkRLViQSeTiOEphfsSdQG6KSwwSo7%3DEB1R3mQ%40mail.gmail.com.

Re: [sqlalchemy] Proper way to handle new 128 character identifier limit in Oracle >= 12.2

[Chris Stephens]

I think you would actually want to query v$parameter since "compatible" can 
be set to a lower version than server which disables 128 lengths.

SQL> select value from v$parameter where name = 'compatible';

VALUE
--
12.2.0

On Thursday, November 8, 2018 at 10:30:19 AM UTC-6, Mike Bayer wrote:
>
> On Thu, Nov 8, 2018 at 11:23 AM 'Van Klaveren, Brian N.' via 
> sqlalchemy > wrote: 
> > 
> > Hi, 
> > 
> > Oracle 12.2 now allows 128 character length identifiers: 
> > 
> > 
> https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/new-features.html#GUID-64283AD6-0939-47B0-856E-5E9255D7246B
>  
> > 
> > It'd be great if sqlalchemy knew about this, but what's the proper way 
> of handling this? Just use the existing dialect and monkey patch 
> max_identifier_length or create a new dialect? 
>
> for now monkeypatch, and for the fix, we would need to look at the 
> server version info and modify the variable accordingly, here: 
>
>
> https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/oracle/base.py#L1087
>  
>
> Pull requests welcome on this or at least we can create an issue in 
> bitbucket to track this. 
>
>
> > 
> > Thanks, 
> > Brian 
> > 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description. 
> > --- 
> > You received this message because you are subscribed to the Google 
> Groups "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an email to sqlal...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/41e601e2-d077-4d5c-93ea-4302d02df591%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Oracle Bulk inserts returning array of IDs possible?

[Chris Stephens]

Is is possible to implement the following behavior in SQLAlchemy against an 
Oracle database? (Bulk inserts returning PK IDs generated by 
trigger/sequence on PK column in database)

#database ddl


DROP TABLE INS_RET_TEST;

CREATE TABLE INS_RET_TEST
   (ID NUMBER NOT NULL ENABLE,
COL1 NUMBER NOT NULL ENABLE,
CONSTRAINT INS_RET_TEST_PK PRIMARY KEY (ID)
USING INDEX);

DROP SEQUENCE INS_RET_TEST_SEQ;

CREATE SEQUENCE  INS_RET_TEST_SEQ  MINVALUE 1 MAXVALUE 9
INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER INS_RET_TEST_TRG
BEFORE INSERT ON INS_RET_TEST
FOR EACH ROW
BEGIN
<>
BEGIN
IF INSERTING AND :NEW.ID IS NULL THEN
SELECT INS_RET_TEST_SEQ.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
/

ALTER TRIGGER INS_RET_TEST_TRG ENABLE; 

 

#Python
##
 

#!/usr/bin/env python

import cx_Oracle
import time

db = cx_Oracle.connect(dsn="connect_info)
cur = db.cursor()

data = [[1],[2],[3],[4],[5]]

IdArr = cur.var(int, arraysize = len(data))
cur.setinputsizes(None, IdArr)
cur.executemany("insert into ins_ret_test(col1) values (:1) returning id 
into :2", data)

for ix, datum in enumerate(data):
print("Data: " , datum[0], "ID: ", IdArr.getvalue(ix))


If so, can someone point me to the documentation or provide an example? 
I've been unable to find anything. 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.