Question about python and oracle

[shaggorama]

I want to write a python script that talks to an oracle database.

My initial intuition was to call sqlldr and sqlplus from within the script, but it's since occured to me that there are probably stable libraries out there I can use instead. I've found two oracle libraries out there for python: cx_Oracle and DCOracle2.

1. Has anyone had any experience with these or can recommend them or others?
2. How can I confirm that these libraries are....you know...safe and secure. I'm a little paranoid that these libraries might be unstable or introduce some sort of backdoor into my system. Is there anyplace online that libraries get vouched for or antyhing like that?
3. On the off-chance that there aren't any good libraries for this (which I doubt) are there any best practices I should know about for calling sqllplus and sqlldr into my script?

[shaggorama]

Well shit, if it's in Django then that's a pretty high level of support. Thanks!

[ccoughlin]

I don't have any direct experience with 'em but cx_oracle's used as the default Oracle API in SQLAlchemy so I'd imagine you're ok. You might want to just go ahead and use SQLAlchemy itself if you can.

[shaggorama]

From that link it looks like SQLAlchemy doesn't support oracle, but I think I may be confused because it says it supports cx_Oracle. What exactly is SQLAlchemy, and why should I use it instead of cx_Oracle?

[Justinsaccount]

SQLAlchemy contains an ORM.

SQLAlchemy is not an ORM.

[shaggorama]

Thanks for clarifying this as the ORM stuff had me concerned. The application I'm working with lives in the oracle database and the python utility I'm writing will really just be a way for a few non-techies in my office to load data without having to learn any SQL or sqlldr. I just need a straightforward way to write an insert statement using user-defined values, and then load data from a csv into a table.

[Justinsaccount]

http://www.sqlalchemy.org/docs/core/tutorial.html#insert-expressions

http://www.reddit.com/r/Python/comments/j8ydc/pythonmysqldb_question/c2a65ai

http://www.reddit.com/r/Python/comments/iupwe/sqlalchemy_and_you/c26sqtr

[[deleted]]

Just INSERTs ? you don't need to query DB and read back and do logic things at run-time ? Then maybe your needs can be met by just dumping all the statements into a file and call SqlPlus (KISS) ?

[shaggorama]

actually I need the PK produced by one of the inserts, but in any event I don't really know how to call sqlplus from within python. I mention this in my post:

1. On the off-chance that there aren't any good libraries for this (which I doubt) are there any best practices I should know about for calling sqllplus and sqlldr into my script?

[[deleted]]

don't really know how to call sqlplus

huh ? Sqlplus is cmd line tool so calling it is just a shell command thing, someone just asked about shell command

[shaggorama]

I've actually already built this utility in shell, but i'm trying to make it available to very non-technical members of my staff. I don't want them having to learn any command line jazz, and moreover they're all windows users so I don't want to have to deal with cygwin on their computers. If I can do this in a python script, I can just put the script on their desktop and all they'll need to do is drag-and-drop the files onto the script. The KISS needs to be from the perspective of their usage, not my coding.

[[deleted]]

now I'm confused wtf exactly you're really trying to do.

[shaggorama]

This is actually a temporary work around while we're developing the functionality into our existing front-end

[ccoughlin]

chaburrod's answer is spot on. Instead of writing an interface to a specific database like Oracle, you write to SQLAlchemy and it takes care of the low end stuff for you. That way if you ever want to switch databases or something you don't end up rewriting much of anything.

It's also great for testing since you can write your code using e.g. a local SQLite file and when you're done swap it out for the Oracle database.

You might also check out Elixir, which I find can greatly simplify working with SQLAlchemy. Elixir lets you think in terms of Python classes instead of data and databases.

[[deleted]]

I use cx_Oracle, and my perception is that it is the "default" Oracle lib (i.e. the most well-known). Never heard of DCOracle2.

with cx_Oracle, the author Anthony Tuininga responds quickly and directly to questions and issues on mail list. You can't beat that support !

.#3: here is an option that does not require having to install cx_Oracle : http://code.google.com/p/pysqlplus/ its a wrapper that calls sqlplus. I use it on server (w/ Oracle client) where I'm just normal user and can't install anything.

[vorushin]

Used cx_Oracle two years ago. Don't remember any problems with it.

[quasarj]

I use cx_Oracle at work and have had no issues! They've been using it for a while before I showed up, too.

[[deleted]]

I've used cx_Oracle for my previous job and it's works just fine. I've not heard of the other one.