0

I am trying to connect to an oracle database in Python using create_engine. This database does not have a username or password.

I see this is the format now:

oracle_db = sqlalchemy.create_engine('oracle://user:pass@server').

However, if this connection has NO username or password, how would the connection string look? I've tried DMIT_connection = create_engine('oracle+cx_oracle://@....) with no luck. When I go to write a pandas df to the database using to_sql I get the error below because I cannot get the connection right give that there is no username or password.

The error occurs because this database has no username (picked up from the localhost machine) and there is no password.

The error I get is this: DatabaseError: (cx_Oracle.DatabaseError) ORA-12545: Connect failed because target host or object does not exist (Background on this error at: http://sqlalche.me/e/14/4xp6)

Jordan P.
  • 19
  • 5
  • 1
    What do you mean by *does not have a username or password"? What is your authentication method? – astentx Jul 21 '21 at 20:36
  • I keep being told by our Oracle administrator this database has no password and that the username is picked up by the localhost machine....when I go to the database properties in Oracle, the username and password fields are blank. I am able to connect to it using ````oracle.connect```` but cannot use ````to_sql```` to write a df to the database. Seems like an odd issue. – Jordan P. Jul 23 '21 at 14:16
  • He also said this: You should not need a username or password to connect. Depending upon the syntax of the connection interface the syntax may change slightly. Using your network password will NOT work. – Jordan P. Jul 23 '21 at 14:23
  • This may be wallet or Kerberos authentication or any proxy server that authenticates you. It is hard to tell what is the issue unless you provide more details and describe your DB setup. – astentx Jul 23 '21 at 15:18
  • *but cannot use to_sql to write a df to the database* - What does it mean? You may write anything you want, really. But should it work or not is a different question. If it doesn't work, you need to provide an error text or some details – astentx Jul 23 '21 at 15:20
  • The error is the connection string as stated above. It is due to the fact the there is no username or password and therefore I get this error when using ````to_sql````. ````DatabaseError: (cx_Oracle.DatabaseError) ORA-12545: Connect failed because target host or object does not exist (Background on this error at: http://sqlalche.me/e/14/4xp6)```` – Jordan P. Jul 23 '21 at 15:53
  • Here is what I am trying now: ````connection = create_engine('oracle+cx_oracle://@//.')```` The ````username```` and ````password```` go right after the FIRST two ````//```` normally. But I have nothing to enter there. – Jordan P. Jul 23 '21 at 15:55
  • https://stackoverflow.com/questions/37471892/using-sqlalchemy-dburi-with-oracle-using-external-password-store – astentx Jul 23 '21 at 16:17
  • I think I am getting closer. Now I get this error: ````DatabaseError: (cx_Oracle.DatabaseError) ORA-12154: TNS:could not resolve the connect identifier specified (Background on this error at: http://sqlalche.me/e/14/4xp6)```` – Jordan P. Jul 23 '21 at 16:41
  • It'll be best to work with your administrator on this, since they should know what kind of authentication you are using, and what the configuration is. If you've confirmed you are using the right configuration then you need to work out why your computer cannot do a dns lookup etc of the db computer. – Christopher Jones Jul 25 '21 at 23:15

1 Answers1

1

Let me know authentication type used. If its external authentication , picking credentials from wallet, you can try sample code mentioned here

How to use SqlAlchemy to connect Database similar to cx_oracle when we use external authorization like wallets with TNS(net service name)

Sudarshan Soma
  • 68
  • 6
  • I keep being told by our Oracle administrator this database has no password and that the username is picked up by the localhost machine....when I go to the database properties in Oracle, the username and password fields are blank. I am able to connect to it using ````oracle.connect```` but cannot use ````to_sql```` to write a df to the database. Seems like an odd issue. – Jordan P. Jul 23 '21 at 14:13
  • Update your question with details about why you "cannot use `to_sql`". Are you getting an error? – Christopher Jones Jul 25 '21 at 09:13
  • The error is the connection string as stated above. It is due to the fact the there is no username or password and therefore I get this error when using to_sql. ````DatabaseError: (cx_Oracle.DatabaseError) ORA-12545: Connect failed because target host or object does not exist (Background on this error at: http://sqlalche.me/e/14/4xp6)```` I updated the question as well. – Jordan P. Jul 25 '21 at 21:58
  • FWIW https://www.thatjeffsmith.com/archive/2014/09/ora-12545-12541-12514-01017/ – Christopher Jones Jul 25 '21 at 23:12
  • I got it to work using the connection string. I had it wrong. Thank you all for your help! – Jordan P. Jul 26 '21 at 13:22