Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.5
    • Component/s: Database
    • Labels:
      None

      Description

      Originally https://springpython.webfactional.com/ticket/45

      Add some code that allows connecting to an Oracle database.

      This was brought to my attention by Joe:
      =====================================
      Hello Greg,

      First, I want to express my admiration for SpringPython. I came across this gem a few days ago, and so far I am thrilled at what you have written. This is fantastic addition and I am slowly but surely trying to understand and use it.

      So, if you don't mind I have a few questions please. I use Oracle and use cx_Oracle to interface my python code with an Oracle database. Since cx_Oracle is compliant with the standard for database connectivity, I wrote a new Python script based on the mysql.py, except I called it cxora.py. I attached a copy of it.

      I have a test program I have wired up to talk to the database and it all worked perfectly with a simple query. However, I ran into a problem when passing parameters to a query. For example:

      return self.databaseTemplate.query("""

      SELECT impcarrcfg.paystat_work_dir,

      impcarrcfg.paystat_reload_dir, impcarrcfg.paystat_archive_dir, impcarrcfg.oid

      FROM impcarrcfg, carr, lklabelsys

      WHERE ( lklabelsys.oid = impcarrcfg.lklabelsys_oid) and

      ( carr.oid = impcarrcfg.carr_oid ) and ( carr.oid = ? and lklabelsys.oid = ? )

      """, (5, 5), ImpFilePropsRowCallbackHandler?())

      In the above example, I get an error message from Oracle and the python code halts on core.py with an exception at queryForList with a DataAccessException?(error). The error from oracle states the variable binding does not work. I did some research on this, and found this example: http://www.velocityreviews.com/forums/t333500-any-cxoracle-sample-code.html

      There they show a dictionary being passed as the argument. I'm not sure that is what is going on in the core.py code. The core.py code is trying to substitute the %s (converted from the ?) with the arguments and that is not working; or at least I don't think it is...

      The way around the dilemma is the following example:

      return self.databaseTemplate.query("""

      SELECT impcarrcfg.paystat_work_dir,

      impcarrcfg.paystat_reload_dir, impcarrcfg.paystat_archive_dir, impcarrcfg.oid

      FROM impcarrcfg, carr, lklabelsys

      WHERE ( lklabelsys.oid = impcarrcfg.lklabelsys_oid) and

      ( carr.oid = impcarrcfg.carr_oid ) and ( carr.oid = %s and lklabelsys.oid = %s )

      """ % (5, 5), None, ImpFilePropsRowCallbackHandler?())

      With this example, all works peachy. The substitution of the parameters is carried out just fine, and the select statement executes with no problems. I can live with this, no problem. But, just wanted to share this with you.

      Greg, I just wanted to bring this up and let you know. I am new to Python and the examples you have have helped me greatly. I just wanted to do my code based on some of your samples, and this error kinda threw me off. I hope you don't mind me writing you and letting you know about this. If this is a mistake on my part, please forgive me - we are all learning here! However, if this might be something of help that you might find worthy, then maybe this can make SpringPython? a little better...

      regards joe

      1. core.py
        7 kB
        Greg Turnquist
      2. cxora.py
        0.5 kB
        Greg Turnquist
      3. SpringSample.py
        0.7 kB
        Greg Turnquist
      4. SpringSampleController.py
        1 kB
        Greg Turnquist

        Activity

        Hide
        Greg Turnquist added a comment -

        Originally https://springpython.webfactional.com/ticket/45

        Change History
        == 04/08/08 12:01:16 changed by gregturn

        • attachment SpringSample.py added.

        == 04/08/08 12:01:28 changed by gregturn ¶

        Greg,

        I think I have found a solution to the problem with the parameterized queries in Oracle and I want to share all my findings with you. I also took some time, and read some of the information on how the MySQLdb works. I do not have MySQL installed on my laptop however, by reading their documentation I was able to figure out why you wrote your code the way you did.

        Specifically, by looking at the "Some Examples" section here: http://mysql-python.sourceforge.net/MySQLdb.html there is a sample bit of code that works just like your code. They are substituting the %s with the values in the tuple representing the arguments for the SQL Select statement. So far so good? I hope so.

        OK. In the Oracle cx_Oracle interface, things are a little different regarding the 'passing' of parameters. In cx_Oracle one must supply a DICTIONARY as the args (I think they do this so the binding of variables is more secure) instead of a TUPLE.

        I tried passing a dictionary to SpringPython? and immediately and rightfully so, I got an error. In the core.py script you have defined an exception to be raised if the 'argument' is NOT a tuple. In my case, the argument was a dictionary therefore it complained. Just for grins and giggles, I commented out the following two lines in the query() method in core.py:

        #if args and type(args) != types.TupleType?: # raise ArgumentMustBeNamed?(argumentName = "rowhandler")

        Then, I tried to run my script again and voila! It worked!! I was able to obtain my resultset with no problems.

        So, in summary this is what I have done: 1. I created a new connection script using cx_Oracle (I think this is what you call a ConnectionFactory, yes?). That is the cxora.py script and it is currently located in my C:\Python24\Lib\site-packages\springpython\database folder. This is new and is based on the script you wrote for MySQL. I attached that one in the prior email I sent you. It is pretty straightforward.

        2. The 'main' python example I wrote to test this out is called SpringSample?.py. It is the stub with a connectionFactory based on cxoraConnectionFactory defined in cxora.py. I have included a copy of my example as an attachment to this email.

        3. I wrote a 'controller' so to speak, called SpringSampleController?.py and I have also added that to this email. I realize this is not as fancy as it should be, but I just wanted something quick and dirty to test with. One of my co-workers is a Spring expert and I need to get with him to see if I can make this a little nicer. But for now, it works. So, if you open SpringSampleController?.py you will see first the declaration of the ImpFileProps? class. Then is the ImpFilePropsRowCallbackHandler? and finally the SampleController? with the getImpFilePropeties() method. That is where the select statement is issued. Ok - couple of things to notice: a) there are no ? or % placeholders for the parameters. They are bound by the :carr_oid and :lklabelsys_oid placeholders. This is the way cx_Oracle binds the variables. b) the use of the dictionary encased in {} as the argument.

        4. Finally, I commented the two lines of code in the query() method in core.py so the code would not bark at me when I passed the dictionary instead of the tuple - I have also attached a copy of my modified core.py.

        In summary: When I ran the code in it's current configuration all seems to work. I guess the trick now is to make the core.py code work so that if a tuple is not supplied for all the existing databases EXCEPT Oracle, then throw an error. Consequently if using Oracle, the argument MUST be a dictionary. See my point?

        I hope this helps. I will try to play around with this some more, as time permits. I can make a SELECT work; now I need to see what surprises exist with INSERT and UPDATE. I hope this makes sense...

        regards, joe
        == 04/08/08 12:01:42 changed by gregturn

        • attachment SpringSampleController.py added.

        == 04/08/08 12:02:05 changed by gregturn

        • attachment core.py added.

        Joe's edits to core.py, in order to make cx_Oracle worok
        == 04/08/08 12:03:03 changed by gregturn

        • attachment cxora.py added.

        cx_Oracle connection factory
        == 04/09/08 09:25:18 changed by gregturn ¶

        • status changed from new to assigned.

        Refactored all the connection factories into a common module, springpython.database.factory. Put Joe's new cxoraConnectionFactory in there as well. Created deprecation warnings and comparable test cases to verify we are backwards compatible, while also alerting users of Spring Python that things have moved through the Python warnings module.

        I moved the library import statements inside the connect method, so that loading of library ONLY happens if you really use that factory. For example, if you only installed MySQL, the system would fail a module level "import pgdb" unless you install that library also. Since import uses a cache, there is no risk of multiple loads, memory leaks, or anything like that. Test cases had to be updated to directly access sys.modules, the destination for imports, in order to properly mock things out.
        04/14/08 05:16:29 changed by gregturn ¶

        Finally, I updated ConnectionFactory's interface and the other concrete connection factory's to contain a validInput list of types. This list is compared against the type of the arguments fed to a query. For example MySQLConnectionFactory defines the list as [types.TupleType?], meaning the arguments must be a tuple. cxoraConnectionFactory defines the list as [types.DictType?], meaning the arguments must be a dictionary. DatabaseTemplate now throws a special exception if this is violated, printing out what are considered valid types. This will also support expansion. Down the road, MySQLdb may include support for both tuples and dictionaries, The list can be updated to [types.TupleType?, types.DictType?] in MySQLConnectionFactory. DatabaseTemplate will require no changes after that.
        == 04/18/08 15:26:08 changed by gregturn ¶

        • status changed from assigned to closed.
        • resolution set to fixed.

        == 05/30/08 08:23:51 changed by gregturn ¶

        • milestone changed from 0.4.1 to 0.5.
        Show
        Greg Turnquist added a comment - Originally https://springpython.webfactional.com/ticket/45 Change History == 04/08/08 12:01:16 changed by gregturn attachment SpringSample.py added. == 04/08/08 12:01:28 changed by gregturn ¶ Greg, I think I have found a solution to the problem with the parameterized queries in Oracle and I want to share all my findings with you. I also took some time, and read some of the information on how the MySQLdb works. I do not have MySQL installed on my laptop however, by reading their documentation I was able to figure out why you wrote your code the way you did. Specifically, by looking at the "Some Examples" section here: http://mysql-python.sourceforge.net/MySQLdb.html there is a sample bit of code that works just like your code. They are substituting the %s with the values in the tuple representing the arguments for the SQL Select statement. So far so good? I hope so. OK. In the Oracle cx_Oracle interface, things are a little different regarding the 'passing' of parameters. In cx_Oracle one must supply a DICTIONARY as the args (I think they do this so the binding of variables is more secure) instead of a TUPLE. I tried passing a dictionary to SpringPython? and immediately and rightfully so, I got an error. In the core.py script you have defined an exception to be raised if the 'argument' is NOT a tuple. In my case, the argument was a dictionary therefore it complained. Just for grins and giggles, I commented out the following two lines in the query() method in core.py: #if args and type(args) != types.TupleType?: # raise ArgumentMustBeNamed?(argumentName = "rowhandler") Then, I tried to run my script again and voila! It worked!! I was able to obtain my resultset with no problems. So, in summary this is what I have done: 1. I created a new connection script using cx_Oracle (I think this is what you call a ConnectionFactory, yes?). That is the cxora.py script and it is currently located in my C:\Python24\Lib\site-packages\springpython\database folder. This is new and is based on the script you wrote for MySQL. I attached that one in the prior email I sent you. It is pretty straightforward. 2. The 'main' python example I wrote to test this out is called SpringSample?.py. It is the stub with a connectionFactory based on cxoraConnectionFactory defined in cxora.py. I have included a copy of my example as an attachment to this email. 3. I wrote a 'controller' so to speak, called SpringSampleController?.py and I have also added that to this email. I realize this is not as fancy as it should be, but I just wanted something quick and dirty to test with. One of my co-workers is a Spring expert and I need to get with him to see if I can make this a little nicer. But for now, it works. So, if you open SpringSampleController?.py you will see first the declaration of the ImpFileProps? class. Then is the ImpFilePropsRowCallbackHandler? and finally the SampleController? with the getImpFilePropeties() method. That is where the select statement is issued. Ok - couple of things to notice: a) there are no ? or % placeholders for the parameters. They are bound by the :carr_oid and :lklabelsys_oid placeholders. This is the way cx_Oracle binds the variables. b) the use of the dictionary encased in {} as the argument. 4. Finally, I commented the two lines of code in the query() method in core.py so the code would not bark at me when I passed the dictionary instead of the tuple - I have also attached a copy of my modified core.py. In summary: When I ran the code in it's current configuration all seems to work. I guess the trick now is to make the core.py code work so that if a tuple is not supplied for all the existing databases EXCEPT Oracle, then throw an error. Consequently if using Oracle, the argument MUST be a dictionary. See my point? I hope this helps. I will try to play around with this some more, as time permits. I can make a SELECT work; now I need to see what surprises exist with INSERT and UPDATE. I hope this makes sense... regards, joe == 04/08/08 12:01:42 changed by gregturn attachment SpringSampleController.py added. == 04/08/08 12:02:05 changed by gregturn attachment core.py added. Joe's edits to core.py, in order to make cx_Oracle worok == 04/08/08 12:03:03 changed by gregturn attachment cxora.py added. cx_Oracle connection factory == 04/09/08 09:25:18 changed by gregturn ¶ status changed from new to assigned. Refactored all the connection factories into a common module, springpython.database.factory. Put Joe's new cxoraConnectionFactory in there as well. Created deprecation warnings and comparable test cases to verify we are backwards compatible, while also alerting users of Spring Python that things have moved through the Python warnings module. I moved the library import statements inside the connect method, so that loading of library ONLY happens if you really use that factory. For example, if you only installed MySQL, the system would fail a module level "import pgdb" unless you install that library also. Since import uses a cache, there is no risk of multiple loads, memory leaks, or anything like that. Test cases had to be updated to directly access sys.modules, the destination for imports, in order to properly mock things out. 04/14/08 05:16:29 changed by gregturn ¶ Finally, I updated ConnectionFactory's interface and the other concrete connection factory's to contain a validInput list of types. This list is compared against the type of the arguments fed to a query. For example MySQLConnectionFactory defines the list as [types.TupleType?] , meaning the arguments must be a tuple. cxoraConnectionFactory defines the list as [types.DictType?] , meaning the arguments must be a dictionary. DatabaseTemplate now throws a special exception if this is violated, printing out what are considered valid types. This will also support expansion. Down the road, MySQLdb may include support for both tuples and dictionaries, The list can be updated to [types.TupleType?, types.DictType?] in MySQLConnectionFactory. DatabaseTemplate will require no changes after that. == 04/18/08 15:26:08 changed by gregturn ¶ status changed from assigned to closed. resolution set to fixed. == 05/30/08 08:23:51 changed by gregturn ¶ milestone changed from 0.4.1 to 0.5.

          People

          • Assignee:
            Greg Turnquist
            Reporter:
            Greg Turnquist
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: