2

Hi I am new to python (programming and stackoverflow). First let me start by saying a little bit about what I am doing and trying to do.

I am using an internal XML API to pull data from an internal database

I parse/format xml result into a txt document (automated this to occur at a set interval)

I want to write or import the contents of this document to an oracle database

How would i go about importing or writing this document into an existing oracle database? I can't seem to find much in the way of documentation with regards to the cx_Oracle module that i am using to establish a connection with my database. Could any of you kind folk point me in a direction / resource to accomplish this?

Improve this question

2 Answers 2

Reset to default
1

SHORT ANSWER:

query = """
insert into TABLE(FIELD1, FIELD2, ...) values (VAL1, VAL2, ...)
"""
cur = con.cursor()
cur.execute(query)
cur.commit()

I strongly suggest you to use prepared statements.

LONG ANSWER:

This is for Linux, in particular for Red Hat. Only the Python code at the end can be used on every OS. Try to adapt these steps to your OS.

0: Install the packages libaio and python-dev (or python-devel, check your distro)

1: If you don't have pip, install it

2: Install oracle instantclient-basic and instantclient-sdk (or instantclient-devel) from Oracle site

3: Launch these commands using bash. If you don't have /etc/profile.d/, check your distro.

echo 'ORACLE_HOME="/usr/lib/oracle/12.1/client64"' | \
sudo tee /etc/profile.d/cx_oracle.sh

pip install cx_Oracle

4: Logout and login again

5: Before using cx_Oracle, you have to set LD_LIBRARY_PATH I recommend you to NOT set it globally:

export LD_LIBRARY_PATH="$ORACLE_HOME/lib"

6: Finally, the Python code:

import cx_Oracle

os.environ["NLS_LANG"] = "AMERICAN_AMERICA.UTF8"
con_str = "USERNAME/PASSWORD@HOST:PORT/DBNAME"
con = cx_Oracle.connect(con_str)

query = """
select 1 from dual
"""
cur = con.cursor()
cur.execute(query)
rows = cur.fetchall()

for row in rows:
    print(row) # it should print "1"

con.close()

You have to change the con_str with your username, password etc. The line that sets utf-8 encoding is optional and adaptable to your needs, but recommended.

7: If you want to insert rows:

query = """
insert into TABLE(FIELD1, FIELD2, ...) values (VAL1, VAL2, ...)
"""
cur = con.cursor()
cur.execute(query)
cur.commit()

I strongly suggest you to use prepared statements if you can't trust the source of the data.

Sources:

  1. http://chilipuppy.blogspot.it/2008/10/purpose-im-working-on-building-python.html
  2. http://agiletesting.blogspot.it/2005/05/installing-and-using-cxoracle-on-unix.html
  3. http://cx-oracle.readthedocs.org/en/latest/index.html
  4. Personal hassle
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Great thanks! I forgot to mention that i was already able to connect to the DB prior to making my post.
@shannona2013: can you also provide an example of your txt data?
sure, let me know if this is what you meant. i replaced the user name and other things with more general info. Also i should mention that the source is trustworthy as i made it myself. is it possible to insert the document into the table directly like i would within toad? 867916136,john,Save,0,server,Web Interface,HTML (UI),15,2015-09-09T12:31:53-04:00
0

Your question is basically "how do I get started with cx_Oracle?"

There's some snippets here:

http://markharrison.net/cx-oracle-demos

and your simplest cx_Oracle program is something like this:

import cx_Oracle
conn = cx_Oracle.connect('scott/tiger')
curs = conn.cursor()

curs.execute('select 2+2 from dual')
print curs.fetchall()

curs.execute('insert into mytable(x) values(3)')
conn.commit()

curs.execute('select * from mytable')
for row in curs:
    print row

conn.close()
Improve this answer

1 Comment

thank you so much! this link is exactly the type of stuff i was looking for :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.