;ς λν6c@sYdZdZdklZdfd„ƒYZdfd„ƒYZdefd„ƒYZd S( sΊ SQLDict: A helper class for the Python DB API. This creates a database interface which works pretty much like a Python dictionary (or shelve). It assumes the necessary tables already exist. First, open the underlying database, creating a connection object. mxODBC is used here, but any SQL-89+ DB API-compliant one should work. Then use that to create the SQLDict object: >>> import ODBC >>> from SQLDict import * >>> subdb = ODBC.Solid.connect('TCP/IP localhost 1313', 'test', 'test') >>> db = SQLDict(subdb) Next, define the tables and columns of interest. Note that is not necesary to define all columns of a particular table, only those you need. >>> db.People = db.Table('PEOPLE', [ 'Name', 'Address', 'City', 'State' ] ) This defines a new member People of db, which describes the table PEOPLE as having the columns NAME, ADDRESS, CITY, and STATE. Remember that SQL is case-insensitive and effectively converts all names to UPPER CASE, unless you use a delimited name, in which case you would want to specify the columns as ( '"Name"', '"Address"', ... ). The column specification must be a tuple or list. You can define multiple members which refer to the same table using a different combination of columns. Now, define some useful indices for accessing your data. >>> db.People.Name = Index(['Name']) This defines an index member to People called Name, which allows searching by Name. The second argument must be a tuple or list, so it is possible to key off of several fields simultaneously. Accessing your data is very similar to using dictionaries. To retrieve information: >>> db.People.Name['Bob Dobbs'].fetchone() ('Bob Dobbs', '42 Slack Ln', 'Cleveland', 'OH') Note that the [] (__getitem__) operation returns a special cursor with some extended properties; more on that latter. >>> db.People.loc = db.Index(('City','State')) >>> db.People.loc['Cleveland','OH'].fetchall() [('Bob Dobbs', '42 Slack Ln', 'Cleveland', 'OH') ('Drew Carey', '13 Pig St', 'Cleveland', 'OH')] Suppose you have some objects you'd like to directly load and store into the database. Assuming a pre-defined Person object: >>> db.People.dump = dump_person >>> db.People.load = load_person >>> db.People.loc['Cleveland','OH'].fetchall() [, ] Now you have full shelve-type functionality for retreival, aside from having to do the various fetch methods. The dump method must accept a single argument (the object to dump) and return a tuple containing the column data in the same order as when the Table was defined. The load method must do the same thing in reverse. This basically accomplishes the same thing as pickling. By default, dump and load simply return what is passed to them. Too lazy to write dump and load routines and define all that table and index stuff? Me too: >>> class Person(ObjectBuilder): ... table = "People" ... columns = ['Name', 'Address', 'City', 'State'] ... update_columns = columns ... indices = [ ('Name', ['Name']), ('loc', ['City', 'State']) ] ... >>> Person().register(db) >>> db.People.loc['Cleveland','OH'].fetchall() [Person(Name='Bob Dobbs',Address='42 Slack Ln',City='Cleveland',State='OH'), Person(Name='Drew Carey',Address='13 Pig St',City='Cleveland',State='OH')] This creates a new Person class with attributes Name, Address, City, and State. The constructor takes assigns the arguments to the attributes in the order in which they appear. It also accepts keyword arguments. The register(db) method creates the table and index members in db. Of course, you can add your own methods and such. If you need an __init__ method, make sure you call apply(ObjectBuilder.__init__, (self,)+args, kwargs) at some point. To reset the attributes from a dictionary: >>> p.set_keywords(dict=dict) # error if key not in columns >>> p.set_keywords(skim=1, dict=dict) # ignores keys not in columns Updating old information is easy: >>> p = db.People.Name['Drew Carey'] >>> p.Address = '100 Hog Mountain Rd' >>> db.People.Name['Drew Carey'] = p Inserting new info is also easy, but slightly different than what you might expect: >>> p2 = Person('Elvis Presley','Graceland','Memphis','TN') >>> db.People.insert(p2) An index is unneccesary (and useless) for inserting new records. Deleting stuff works like you'd think. >>> del db.People.Name['Elvis Presley'] Note that none of these methods ever commit any data to the database, so you will have to call db.commit() when appropriate, or otherwise set the transaction mode. Sub-queries: Suppose you want to do a sub-query, as in WHERE x IN ( select-statement ) or some other kind of special query conditions. You can specify this with a WHERE parameter when creating the index. Note that this string is simply appended onto the part of the clause generated by the indices. The indices can be left blank for these purposes. The SELECT member of an index can be useful for generating a sub-query on another index. The select() and update() methods are also available for doing weird things. s8$Id: SQLDict.py,v 1.2 1999/03/16 05:24:23 adustman Exp $(sjoinsSQLDictcBsQtZdZd„Zd„Zd„Zd„Zdfd„ƒYZgd„ZRS(sxSQLDict: An object class which implements something resembling a Python dictionary on top of an SQL DB-API database.cCs ||_dS(sJCreate a new SQLDict object. db: an SQL DB-API database connection objectN(sdbsself(sselfsdb((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys__init__‹scCs|iƒdS(N(sselfsclose(sself((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys__del__“scCsy|iiƒWnnXdS(N(sselfsdbsclose(sself((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysclose•scCst|i|ƒSdS(N(sgetattrsselfsdbsattr(sselfsattr((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys __getattr__™ss_TablecBsΝtZdZgd„Zd„Zd„Zd„Zd„Zfdd„Zd„Z fdd „Z fdd „Z d „Z d „Z d „Zdfd„ƒYZgdd„Zdfd„ƒYZd„ZRS(sWTable handler for a SQLDict object. These should not be created directly by user code.cCsš||_||_||_d|i|ƒ|if|_d|i|i|ƒ|i|ƒf|_|i|ƒd|i|_ |i|p|ƒdS(sgConstruct a new table definition. Don't invoke this directly. Use Table method of SQLDict instead.sSELECT %s FROM %s s$INSERT INTO %s (%s) VALUES (%s) sDELETE FROM %s N( sdbsselfstablescolumnss_columnssSELECTs_valuessINSERTsUpdatesDELETEs updatecolumns(sselfsdbstablescolumnss updatecolumns((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys__init__€s   + cCst|dƒSdS(Ns, (sjoinscolumns(sselfscolumns((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys_columns·scCstdt|ƒdƒSdS(Ns?s, (sjoinslenscolumns(sselfscolumns((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys_valuesΉscCsttd„|ƒdƒSdS(NcCsd|S(Ns%s = ?(sc(sc((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysΌss, (sjoinsmapscolumns(sselfscolumns((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys_set»scCs,d|i|i|ƒf|_||_dS(NsUPDATE %s SET %s (sselfstables_setscolumnssUPDATEs updatecolumns(sselfscolumns((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysUpdateΎsscCsJ|iƒ}|o|i|i||ƒn|i|i|ƒ|SdS(sExecute a SELECT command based on this Table and Index. The required argument i is a tuple containing the values to match against the index columns. A string containing a WHERE clause should be passed along, but this is technically optional. The WHERE clause must have the same number of value placeholders (?) as there are values in i. Returns a _Cursor object for the matched rows. Usually you don't need to call select() directly; this is done by the indexing operations (Index.__getitem__).N(sselfscursorscsisexecutesSELECTsWHERE(sselfsisWHEREsc((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysselectΔs  cCsldkl}|iƒ}t|ƒ|jot|i|ƒ}n|i|ƒ}|i |i |ƒ|SdS(s\Like select(), but performs an INSERT. Note that there is no WHERE clause on an INSERT.(sListTypeN( stypessListTypesselfscursorscstypesvsmapsdumpsdsexecutesINSERT(sselfsvscsdsListType((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysinsertΦs  cCs>|iƒ}|i|ƒ}|i|i|||ƒ|SdS(s¨Like select(), only it does an UPDATE. It is not usually necessary to call this method directly, as it is done by the indexing operations (Index.__setitem__).N( sselfscursorscs updatedumpsvsv0sexecutesUPDATEsWHEREsi(sselfsvsisWHEREscsv0((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysupdateδs  cCsJ|iƒ}|o|i|i||ƒn|i|i|ƒ|SdS(s¨Like select(), only it does an DELETE. It is not usually necessary to call this method directly, as it is done by the indexing operations (Index.__delitem__).N(sselfscursorscsisexecutesDELETEsWHERE(sselfsisWHEREsc((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysdeleteνs  cCs|SdS(sDefault method. May be overridden. Must take single value argument, return a tuple compatible with the columns defined for this table.N(sv(sselfsv((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysdumpφscCso|i|ƒ}g}xItt|iƒƒD]2}|i||i jo|i ||ƒq+q+Wt |ƒSdS(N( sselfsdumpsvstslsrangeslenscolumnssis updatecolumnssappendstuple(sselfsvsislst((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys updatedumpόscCs|SdS(s‹Default method. May be overridden. Must take a tuple compatible with the columns defined for this table, return something useful.N(sv(sselfsv((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysloadss_IndexcBs;tZdZd„Zfed„Zfd„Zd„ZRS(sZIndex handler for a _Table object. These should not be created directly by user code.cCs9td„|ƒ}||_dt|dƒ||_dS(NcCsd|S(Ns%s = ?(si(si((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysss WHERE s AND (smapsindicessistablesselfsjoinsWHERE(sselfstablesindicessWHEREsi((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys__init__s cBsjdkTe|ƒejoe|ƒ}n!e|ƒejo |f}n|ii||d|i ƒdS(s>Update the item in the database matching i with the value v.(s*sWHEREN( stypesstypesisListTypestuples TupleTypesselfstablesupdatesvsWHERE(sselfsisv((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys __setitem__s cBsgdkTe|ƒejoe|ƒ}n!e|ƒejo |f}n|ii|d|i ƒSdS(s(Select items in the database matching i.(s*sWHEREN( stypesstypesisListTypestuples TupleTypesselfstablesselectsWHERE(sselfsi((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys __getitem__s cBsgdkTe|ƒejoe|ƒ}n!e|ƒejo |f}n|ii|d|i ƒSdS(s(Delete items in the database matching i.(s*sWHEREN( stypesstypesisListTypestuples TupleTypesselfstablesdeletesWHERE(sselfsi((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys __delitem__#s (s__name__s __module__s__doc__s__init__sNones __setitem__s __getitem__s __delitem__(((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys_Index s   cCs|i|||ƒSdS(sCreate an index definition for this table. Usage: db.table.Index(indices) Where: indices = tuple or list of column names to key on WHERE = optional WHERE clause. If the WHERE is specified, this is used in conjunction with the other indices. N(sselfs_IndexsindicessWHERE(sselfsindicessWHERE((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysIndex*s s_CursorcBs;tZdZd„Zd„Zd„Zd„Zd„ZRS(s•A subclass (shadow class?) of a cursor object which knows how to load the tuples returned from the database into a more interesting object.cCs|iƒ|_||_dS(N(sdbscursorsselfsload(sselfsdbsload((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys__init__=scCs/|iiƒ}|o|i|ƒSn|SdS(s-Fetch one object from current cursor context.N(sselfscursorsfetchonesxsload(sselfsx((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysfetchoneAs cCst|i|iiƒƒSdS(s2Fetch all objects from the current cursor context.N(smapsselfsloadscursorsfetchall(sself((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysfetchallGscGs#t|it|ii|ƒƒSdS(soFetch many objects from the current cursor context. Can specify an optional size argument for number of rows.N(smapsselfsloadsapplyscursors fetchmanyssize(sselfssize((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys fetchmanyKscCst|i|ƒSdS(N(sgetattrsselfscursorsattr(sselfsattr((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys __getattr__Ps(s__name__s __module__s__doc__s__init__sfetchonesfetchalls fetchmanys __getattr__(((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys_Cursor7s     cCs|i|i|iƒSdS(sUReturns a new _Cursor object which is load-aware and otherwise behaves normally.N(sselfs_Cursorsdbsload(sself((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pyscursorTs(s__name__s __module__s__doc__s__init__s_columnss_valuess_setsUpdatesselectsinsertsupdatesdeletesdumps updatedumpsloads_IndexsIndexs_Cursorscursor(((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys_TableŸs"             cCs|i|i|||ƒSdS(sΐAdd a new Table member. Usage: db.Table(tablename, columns) Where: tablename = name of table in database columns = tuple containing names of columns of interest N(sselfs_Tablesdbstablescolumnss updatecolumns(sselfstablescolumnss updatecolumns((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysTableZs( s__name__s __module__s__doc__s__init__s__del__scloses __getattr__s_TablesTable(((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysSQLDict†s     »s ObjectBuildercBsqtZdZeZgZgZhZgZd„Z dhd„Z d„Z d„Z d„Z d„Zd„ZRS( sθThis class lets you build objects for use with SQLDict, and for other purposes. To use, define a new class, subclassing ObjectBuilder. Define the following items: table: Name of table in SQL database. columns: List of columns in table. updatecolumns: List of columns in table that are updateable. Usually it is necessary to define this to avoid referencial integrity problems, i.e. avoid updating columns which are used as foreign keys. special_types: A dictionary where the key is a column name and the value is a dbi type. Useful when working with date data. indices: A list of tuples. The first part of the tuple is the name of the index. The second part is a list of column names. cOspx!|iD]}t||tƒq Wx5tt|ƒƒD]!}t||i|||ƒq7W|i d|ƒdS(s΄ Constructor: Accepts an argument list of values, which are assigned in the order specified in columns. Also accepts keyword arguments, where the keys are from columns. sdictN( sselfscolumnsskssetattrsNonesrangeslensargssis set_keywordsskw(sselfsargsskwsisk((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys__init__s icCsZxS|iƒD]E\}}||ijot|||ƒq | o t|‚q q WdS(sŸ Assign attributes using keyword arguments. If skim=0 (default), keywords not present in columns raises AttributeError. Otherwise, the keyword is ignored. N( sdictsitemssksvsselfscolumnsssetattrsskimsAttributeError(sselfsskimsdictsvsk((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys set_keywords‹s cCsByt|d|ƒ|ƒWn tj o||i|ΈssdumpsloadN(sdbsTablesselfstablescolumnss updatecolumnssts __class__sloaderssetattrsdumpsindicess indexnamesIndex(sselfsdbs indexnamesloaderstscolumns((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysregister΅s (s__name__s __module__s__doc__sNonestablescolumnss updatecolumnss special_typessindicess__init__s set_keywordss __setattr__s__str__s__repr__sdumpsregister(((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys ObjectBuildergs      s MySQLDictcBs!tZdeifd„ƒYZRS(Ns_TablecBs6tZd„Zd„Zdeiifd„ƒYZRS(NcCstdgt|ƒdƒSdS(Ns%ss, (sjoinslenscolumns(sselfscolumns((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys_valuesΔscCsttd„|ƒdƒSdS(NcCsd|S(Ns%s = %%s(sc(sc((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysΗss, (sjoinsmapscolumns(sselfscolumns((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys_setΖss_IndexcBstZd„ZRS(NcCs9td„|ƒ}||_dt|dƒ||_dS(NcCsd|S(Ns%s = %%s(si(si((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pysΜss WHERE s AND (smapsindicessistablesselfsjoinsWHERE(sselfstablesindicessWHEREsi((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys__init__Λs (s__name__s __module__s__init__(((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys_IndexΙs(s__name__s __module__s_valuess_setsSQLDicts_Tables_Index(((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys_TableΒs  (s__name__s __module__sSQLDicts_Table(((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys MySQLDictΐsN(s__doc__s __version__sstringsjoinsSQLDicts ObjectBuilders MySQLDict(sSQLDicts __version__sjoins MySQLDicts ObjectBuilder((s7/mnt/gmirror/ports/databases/py-SQLDict/work/SQLDict.pys?€s  αY