2 Replies Latest reply: Mar 14, 2013 7:32 PM by cj RSS

    Re: Python Dictionary to Oracle table & vice versa

    jstem1177
      Hello All,

      I've looked at some of the tutorials available fro python and Oracle. However I cannot seem to find any reference on how to convert a python dictionary to a database table.

      Here is my scenario:
      Complex parsing of text files. The break down of keys:values is stored into a python dictionary.
      How can I then convert this dictionary into a database table. --> keys need to become table columns(varchar2 or number) and the values need to added accordingly.
      By the same token, I would then need to output my table to a python dictionary.
      Been looking for a solution for some time and have not come across anything.


      Thanks in advance for any assistance.

      Jan S.
        • 1. Re: Python Dictionary to Oracle table & vice versa
          jstem1177
          Hello,

          Can anybody offer some assistance as to the best route to take?

          Thanks in advance

          Jan S.
          • 2. Re: Python Dictionary to Oracle table & vice versa
            cj
            For inserting numbers & varchars from a dictionary, you can try a custom solution along the lines of:
            con = cx_Oracle.connect('hr/welcome@localhost/orcl')
            cur = con.cursor()
            
            mydict = {'name' : 'Chris', 'postcode' : 3000, 'city' : 'Melbourne'}
            
            fields   = mydict.keys()
            values   = mydict.values()
            bindvars = ":" + ",:".join(fields)
            sql = "insert into t (%s) values (%s)" % (",".join(fields), bindvars)
            
            cur.execute(sql, values)
            con.commit()
            If you wanted to create the INSERT DDL you would need to do more string manipulation, such as quoting any string data. Since this is error prone (and can be a security), stick to executing with bind variables.