couchdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Couchdb Wiki] Update of "How to import your SQL dump to CouchDB" by DougShawhan
Date Fri, 13 Nov 2009 20:35:06 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Couchdb Wiki" for change notification.

The "How to import your SQL dump to CouchDB" page has been changed by DougShawhan.
http://wiki.apache.org/couchdb/How%20to%20import%20your%20SQL%20dump%20to%20CouchDB?action=diff&rev1=1&rev2=2

--------------------------------------------------

- Cro-Magnon simple python script which will take the contents of an SQL table and place it
into CouchDB.
+ DumpOracleDbToCouchDbPython
  
- This particular example depends on the SQL table having one unique field. The values in
the unique field become individual documents in the CouchDB database.
- 
- This is probably not what you want. :-)
- 
- {{{#!python
- #!/usr/bin/env python
- import couchdb, cx_Oracle, ConfigParser, os, datetime
- 
- # Convert a list of (sql tables, unique identifiers) into couchDB documents
- # Simply figure out which db you wish to import from, and a series of tables,
- # with unique fields you'd like to have them
- # indexed by, put them into the list of tuples below and let fly.
- # I store all my config stuf in an ini file locally.  you'll have to handle
- # your own connection to your db.
- # This is pretty quick-and-dirty and I don't reccomend it for any sort of 
- # production anything at all! :-)
- # Should work for any db api 2 compliant sql datablase. Holler if I'm wrong
- # Script guaranteed 100% slower than christmas.
- 
- db_name = 'mydatabase'
- table_names = [ ('TABLE_NAME0', 'UNIQUE_FIELD'),
-                 ('TABLE_NAME1', 'UNIQUE_FIELD'),
-                 ('TABLE_NAME2', 'UNIQUE_FIELD') ]
- 
- class GrabbyMitts(object):
-     def __init__( self, db_name ):
-         config = ConfigParser.ConfigParser()
-         config.optionxform = str
-         config.read( [ "sqlgen.ini", os.path.expanduser("~/.sqlgen.ini" ) ] )
- 
-         # oracle connection
-         self.connection = cx_Oracle.Connection( config.get("Oracle", "login") )
- 
-         # couchdb location
-         self.couch = couchdb.Server( "http://localhost:5984/" )
-         try:
-             self.db = self.couch.create( db_name )
-         except:
-             self.db = self.couch[ db_name ]
- 
-     def description( self ):
-         # get a description of a given table
-         # returns the "header" information in list
-         query = "select * from %s where 1=0"%self.table_name
-         cursor = cx_Oracle.Cursor( self.connection )
-         cursor.execute( query )
-         description = [ i[0] for i in cursor.description ]
-         cursor.close()
-         return description
- 
-     def uniques( self ):
-         # unique value in sql table to create couchdb document ID
-         cursor = cx_Oracle.Cursor( self.connection )
-         query = "select %s from %s"%( self.mykey, self.table_name )
-         cursor.execute( query )
-         myuniques = [ i[0] for i in cursor.fetchall() if i[0] ]
-         cursor.close()
-         return myuniques
- 
-     def updateCouch( self, table_and_key ):
-         # populate or update couchdb documents using sql table and unique
-         # identifier
-         self.table_name, self.mykey = table_and_key
-         cursor = cx_Oracle.Cursor( self.connection )
-         documents = []
-         header = self.description()
- 
-         query = """
-             select %s
-             from %s 
-             where %s=:myunique"""""%( ", ".join( header ),
-                                     self.table_name,
-                                     self.mykey ) 
-         cursor.prepare( query )
-         
-         for myunique in [ { "myunique": i } for i in self.uniques() ]:
-             cursor.execute( None, myunique )
-             entry = dict( [ (k, v) for k, v in zip( header, cursor.fetchone() ) ] )
-             # mop up datetime objects as they occour, since json cries foul.
-             # will probably need to convert to unix epochal time
-             for k in entry:
-                 if isinstance( entry[k], datetime.datetime ):
-                     entry[ k ] = "%s"%entry[ k ] 
- 
-             if not str( myunique[ 'myunique' ] ) in self.db:
-                 self.db[ str( myunique[ 'myunique' ] ) ] = entry
-             else:
-                 doc = self.db[ str( myunique[ 'myunique' ] ) ]
-                 for k in entry:
-                     doc[ k ] = entry[ k ]
-                 self.db[ str( myunique[ 'myunique' ] ) ] = doc
- 
-         cursor.close()
- 
- if __name__ == "__main__":
-     gm = GrabbyMitts( db_name )
-     for table_name in table_names:
-         gm.updateCouch( table_name )
- 
- }}}
- 

Mime
View raw message