How do I connect to Oracle?

[abel-diaz]

I am unable to connect to Oracle. I am using Connector X v. 0.2.1a1
I have the following creds
host
port
servicename
username
password

According to the connectorx instructions i need to have
(postgres|postgressql|mysql|mssql|sqlite)://username:password@addr:port/dbname

Using my above creds, I have
cstr = 'oracle://username:password@host:port/servicename'
query ' 'select * from table'

When I run
df = cx.read_sql(cstr,query)

I receive RuntimeError: timed out waiting for connection: OCI Error: ORA-01017: invalid username/password; logon denied
I am able to connect with sqlalchemy using my creds

How do I connect conncetorx with Oracle?
Thank you,
Abel

[wangxiaoying]

Hi @abel-diaz ,

I believe your connection string and the way you invoke cx.read_sql is correct.

Our connection string should look like:

oracle://${ORACLE_USER}:${ORACLE_PASSWORD}@${ORACLE_HOST}:${ORACLE_PORT}/${ORACLE_DB}

, which is different from using sqlplus.

Do you have any special characters in your username or password? I'm wondering maybe the issue is in the url parsing and some strings need to be quoted.

[abel-diaz]

Good Morning,
If my creds have the following ( I changed the words but kept special characters)
host - hostname.hostsite.com
port - 1234
servicename - dbprod
username - myname[text]
password - zoo^123

I am currently writing the connection as
cstr = 'oracle://myname[text]:zoo^1234@hostname.hostsite.com:1234/dbprod'

How would I correct the above connection string to make it work?
Thank you for your help
Abel

[wangxiaoying]

Can you try to encode your username and password like this and see whether it works?

import urllib.parse

cstr = f"oracle://{urllib.parse.quote_plus('myname[text]')}:{urllib.parse.quote_plus('zoo^1234')}@hostname.hostsite.com:1234/dbprod"

[abel-diaz]

Hi,
I tried (with actual creds)
cstr = f"oracle://{urllib.parse.quote_plus('myname[text]')}:{urllib.parse.quote_plus('zoo^1234')}@hostname.hostsite.com:1234/dbprod"

and the output for cstr is similar to below with example creds
'oracle://myname%5Btext%5D:zoo%5E1234@hostname.hostsite.com:1234/dbprod'

When i try
df = cx.read_sql(cstr,query)

I am met with
Error: ORA-01017: invalid username/password; logon denied

I am really hoping this gets connected. It takes me 1hr and 40min to pull 3M rows of SQL to DF using pd.read_sql.

Thanks for all the help. I have confirmed all my creds are correct through my companies DBA team and thru successful connections in TOAD, cx_oracle, and sqlAlchemy.

Thank you again
Abel

[abel-diaz]

PS - What is the max number of partitions I can assign? ... safely?

Thanks again

[wangxiaoying]

That's great! Also, thanks for reporting this bug!

PS - What is the max number of partitions I can assign? ... safely?

It is recommended to be the number of free cores you have on your client machine.

[abel-diaz]

I have 4 cores and 8 logical processors, my max would be 4 , do logical processors play into this? If so, how
I have hyper-threading so 4 cores, acts as 8 .

Questions

1. Are you recommending to not use 8 as a max but stick to the true count of cores that is 4?
2. Can connector x handle hyper threading? If so, can I set a max from 4 to 8?
3. Does the same apply to virtual processors in a VM?
4. How would I know if I over extended my max core allotment to connector x? Will it fail, what could I look out for to know?

Thanks
Abel

[wangxiaoying]

Oh, sorry I misunderstood your question before. I believe you could assign values even larger than 8, but the performance might not improved or even introduce overhead when you make it too large, so I suggested to use the number of cores you have. But in a specific case the optimal value might also be affected by other factors like the network condition and database server's performance.

[abel-diaz]

Ok thank you for your help!