Skip to content

Python Code CAN Connect to an Oracle Database.


Despite the beaming praise for the simplicity of the cx_Oracle project on their SourceForge page, I had trouble using the Python module to create a connection to an Oracle database. It turns out that the module is quite nice once you get past a couple of problems that have nothing to do with the cx_Oracle module. In my case, my computer’s environment and some confusing information on the Internet were the cause of my troubles. So here I have organized some hints to help you if you are unfortunate and cannot immediately make a successful connection to your Oracle database using cx_Oracle.

Install the Correct Version of cx_Oracle

This was my biggest problem, though I didn’t realize it for a while. The cx_Oracle project has separate binary distributions for both OS and Oracle version, which support Windows and CentOS for Oracle 10.2, 11.1 and 11.2. On Windows for sure, installing the wrong binary will result in a broken installation. However, it’s not necessarily as simple as knowing which version your Oracle database is at.

Here’s what happened in my case. I have an Oracle ODBC driver installed on my Windows XP installation (OraClient10g) but I want to connect to an 11g database. I first assumed that I needed to install the 11g version of cx_Oracle and found that assumption to be wrong. I think because of the ODBC driver version, I had to install the 10g version of cx_Oracle. I simply could not connect to my database otherwise. I don’t have a way to confirm this ODBC complication because I don’t have authority to install different versions of drivers but it makes sense to me.

The indication I got that my connection was not working was the following error from my Python program:

cx_Oracle.DatabaseError: ORA-24315: illegal attribute type

This is a vague error that, I suppose, has something to do with the mismatch between the cx_Oracle code and the ODBC driver. Once I installed the cx_Oracle version that matched my ODBC driver version I was able to successfully connect.

Connection Strings

When you connect to an Oracle database using the connect method you have several ways to specify important parameters such as user, password and SID (refered to as Data Source Name, DSN, in cx_Oracle). The easiest way to connect is like this:

# Connect using the ordered parameters user, password and SID.
dbconn = cx_Oracle.connect('user', 'password' ,'SID')

You can also be more explicit by naming the parameters like this:

# Connect using named parameters. 
dbconn = cx_Oracle.Connection(user='user',password='password',dsn='SID')

I suggest just using one of the above methods as is used in the sample code that comes with the cx_Oracle module. There is another method though that utilizes the Oracle Easy Connect string. This string is purported by Oracle to be convenient but is not—especially if you have limited authority on the database. The Easy Connect string requires, instead of the easy to obtain and commonly known SID, that you know the service name for a database.  Assuming you have authority to do so, you can execute the command below against your database to obtain the service name:

select sys_context('userenv', 'service_name') from dual;

Once you have the service name, you can connect to the database like so:

# Connect using Oracle's Easy Connect connection string.
 dbconn = cx_Oracle.connect(u'user/password@db-server:1521/service.name')

Conclusion

The cx_Oracle module is a nice library to have around when you’re working with Oracle from Python code. You’ll probably not have the problems I did if you read the cx_Oracle documentation carefully and understand your OS environment properly. However, if you do have some problems, I hope this article helped solve them.

One thing to note is that when you install cx_Oracle it does install documentation. I think it’s in an odd place, but maybe this is common for Python modules. The documentation will be in <python-install-dir>/cx_Oracle-doc. This directory contains documentation, test cases and sample Python code.

Links and References

Here are links to materials that I’ve referenced and other useful links.

cx_Oracle project page – http://sourceforge.net/projects/cx-oracle/

cx_Oracle download page – http://cx-oracle.sourceforge.net/

Oracle whitepaper describing the EasyConnect string – http://www.oracle.com/technetwork/database/enterprise-edition/oraclenetservices-neteasyconnect-133058.pdf

Oracle tutorial on connecting to a database with cx_Oracle – http://www.oracle.com/technetwork/articles/dsl/python-091105.html

Some StackOverflow discussions on this topic that helped me:

http://stackoverflow.com/questions/3521692/how-can-i-access-oracle-from-python

http://stackoverflow.com/questions/6288122/checking-oracle-sid-and-database-name

Advertisements

M3Conf 2012 Slides


If you attended my talk called “Your First Enterprise App – From the Trenches” at M3Conf on October 26th, 2012, you can get the slides here: M3Conf2012-NickWatts

Thanks to everyone who attended for being so engaging. There were many great questions, which I thoroughly enjoyed answering. If you have any questions do not hesitate to comment below or tweet them to me @thewonggei.

Ant Task for Encoding Text Files – Reencode 0.1 Launched


I am announcing the initial launch of a tiny open source project called reencode.

Reencode is a small set of tools—aimed at developers in the Java ecosystem—that help with changing the character encoding of text files. Initially, the tools consist of an API (if you can call one class an API :)) and, far more useful, an Ant task. A Gradle task will be following shortly.

The code is licensed under the Apache License v2.0 and is hosted on GitHub here. You can obtain the .jar file for version 0.1 here.

Using the Ant task is simple. Detailed instructions are on the projects wiki but here is a quick example:

<?xml version="1.0" encoding="UTF-8"?>
<project name="sample" default="default">
    <taskdef name="ReEncode" 
             classname="org.reencode.tools.ant.CharEncodingConverter" 
             classpath="reencode-0.1.jar"
    />
    <target name="default">
        <ReEncode inputEncoding="UTF-8" outputEncoding="ISO-8859-1" todir="out">
          <fileset dir="samples" includes=".txt"/>
        </ReEncode>
    </target>
</project>

The sample above shows how you can re-encode all the .txt files in the samples directory from their current, known encoding UTF-8 to ISO-8859-1. The re-encoded files are dumped into the directory named out.

If you put these tools to use, please don’t hesitate to provide feedback or ask questions as comments below or on GitHub. Pull requests are welcome too! Enjoy.

Will I See You at M3Conf?


One week from now, October 25th, the second edition of the midwestern US conference on mobile app development, M3Conf, will begin. It will be a two-day event, starting with half and full day sessions taught by regional industry luminaries. The second day, which is the main conference day, will consist of two keynote speakers and five one hour sessions. You can see the full schedule at www.m3conf.com.

The conference will be held at the Ohio State University’s student union building, which is a newly built and fantastically furnished place. The best part is that there will be WiFi available all through the building!

I will be delivering a talk entitled “Your First Enterprise App – From the Trenches” at 9:15am on the main conference day (October 26th). There are still some tickets left for the main conference day and are only $100 (a complete steal for the value), so I hope to see you there. Please stop by my talk, chat with me a bit, and ask plenty of questions.

How to Handle Heavily Nested XML Tags with XSLT


I’m new to using Extensible Stylesheet Language Transformations (XSLT). Because I’m new and I had a fairly complex (or so I thought) task to perform with XSLT, I was confused at first.

The task I had and, consequently, the issue that was tripping me up, was with transforming heavily nested tags in my source XML file. Here’s an example of the type of XML I’m talking about.

<?xml version="1.0" encoding="UTF-8"?>
<tag1>
  <tag1>
    <tag1>
      <tag1>
        <arbitrary></arbitrary>
        <tag2>
          <tag2>
            <tag1>
              <texttag></texttag>
            </tag1>
          </tag2>
        </tag2>
      </tag1>
    </tag1>
  </tag1>
</tag1>

The gist of this example is that tag1 and tag2 can be nested within each other to arbitrary depths.  At any level of this hierarchy, there can exist other various tags.

The task I had was to transform the XML above to the following:

<?xml version="1.0" encoding="UTF-8"?>
<tag_start/>
  <tag_start/>
    <tag_start/>
      <tag_start/>
        <arbitrary></arbitrary>
        <tag2_start/>
          <tag2_start/>
            <tag1_start/>
              <texttag></texttag>
            <tag1_end/>
          <tag2_end/>
        <tag2_end/>
      <tag1_end/>
    <tag1_end/>
  <tag1_end/>
<tag1_end/>

A little bit of an odd format, but that was, nonetheless, the requirement. The structure is basically the same as before except that there are explicit tags for the start and the end of the tag (for tag1 and tag2) and all other tags placement and content are preserved.

Because of the oddball structure, this seems like an immensely difficult task at first. I went down a lot of paths with my XSLT that tried to do what amounted to a recursive approach in programming, which never worked. It turned out that I misunderstood how an XSLT engine works. I finally stumbled on a great explanation on Stack Overflow that helped: http://stackoverflow.com/a/6199369.

With that explanation in hand, I was able to write this XSLT file to do the transformation:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml"/>

    <xsl:template match="//tag1" >
       <tag1_start/>
       <xsl:apply-templates/>
       <tag1_end/>
    </xsl:template>
    
    <xsl:template match="//tag2">
       <tag2_start/>
       <xsl:apply-templates/>
       <tag2_end/>
    </xsl:template>
  
    
    <!-- 
         standard copy template, this copies all nodes 
         and attributes from the original to the transformed XML.
     -->
    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*"/>
            <xsl:apply-templates/>
        </xsl:copy>
    </xsl:template> 

</xsl:stylesheet>

I really can’t state things better than the Stack Overflow user did, so I won’t try. Hopefully, if you’re having a similar problem, you will find this post and it will help cajole you into finding a solution.


Mashed Code Magazine

It’s that time of the year when software developers, mostly in Ohio, Michigan and the surrounding states but all throughout the country too, start getting ready for CodeMash. The call for talk abstracts has already opened and runs through September 15th and the conference organizers are now also taking on sponsors. So if you are endowed with either the talent and desire to speak at CodeMash or the capital to help underwrite it, we encourage you to do so, heartily. Otherwise, all of you eager would-be attendees (you know it’s going to be a mad rush at registration again) can get ready by watching the suite of videos from the 2012 conference that have been uploaded to InfoQ.

If you go looking on InfoQ for the videos you might have a little trouble being sure that you’ve found them all. Unfortunately, the site does not provide a…

View original post 152 more words


Mashed Code Magazine

The Summer issue is ready and, as always, this issue is free!

Obtaining the issue can be anywhere from a benign act to a feat of sheer geekery. Here are the methods at your disposal and the geek point boost you get for using each one:

+0 – Novice

Just click on a link….this one.

+10 – Novice, with account setup skills

Go to MagCloud to get the PDF as a download or on your iPad (with the MagCloud iPad app). Print versions are also available at MagCloud for $12.99.

+100 – Hip Git User

git clone https://github.com/mashedcodemag/issues

+1000 – Command Line Fu Master

wget --no-check-certificate --progress=bar \
https://github.com/mashedcodemag/issues/zipball/master \
--output-document=mcm-june-2012.zip

We sincerely hope that you enjoy the magazine and find it useful. If you have any comments or questions, or want to report errata, comment below or send an email to mashedcodemag@gmail.com.

View original post